Sunday, January 22, 2012

Important Functions in VBA

!...Some Important Functions in Excel-VBA....!

Debug.Print StrReverse("abcdefghijklmnopqrstuvwxyz") '''' Reverse a String
Debug.Print UCase("amit verma") '' Print String in Upper Case
Debug.Print LCase("AMIT VERMA") '' Print String in Upper Case
Debug.Print Replace("amit verma", " ", "") '' Replace Space with null Values
Debug.Print IsNumeric(12305) ''' Function True if given value is numerice either false
Debug.Print MonthName(10) '' Print the Monthname of the given value
Debug.Print Format(Now, "dd-mmm-yyyy") '' Function display only Date in the given format
Debug.Print Format(Now, "hh:mm:ss") '' Function display only Time in the given format
Debug.Print Asc("a") '' Displays Ascii Value of the give character
Debug.Print Chr(85) '' Displays character from the basis of given number
Debug.Print CDate("12 feb 2013") '' Converts a String into Date
Debug.Print IsObject(Object) '' print true if the given values is object either false
Debug.Print IsEmpty(abc) ''print true if the given values is empty either false
Debug.Print IsError("#N/A") ''print true if the given values is error either false
Debug.Print InputBox("Enter Any Value") '' To Take user's Input
x = MsgBox("Do U Want to Run Again", vbYesNoCancel) '' Message Box & Properties
Debug.Print x
Debug.Print LTrim("     akshay") '' Remove spaces from the left
Debug.Print RTrim("akshay     ") '' Remove spaces from the left
Debug.Print Trim("    akshay     ") '' Remove leading & trailing spaces from the left
Shell "notepad" '' To open installed applications on your system
MkDir "E:\abc" '' Creates directory in E: drive with the name ABC
RmDir "E:\abc" '' Removes directory in E: drive with the name ABC
Kill "e:\*.xls" '' Delete All the file in the E: directory with ".xls" extension
FileCopy "E:\abc\xyz.xls", "E:\" '' Copy "xyz.xls" file to E: drive
Name "D:\VIKAS DATA\VBA PROGRAMMING\excel macros\testing.xls" As "D:\VIKAS DATA\VBA PROGRAMMING\testing.xls" '' Move/Rename file
Range("a1:z20").Select '' Function Selects the cells A1:Z20 in the activesheet
Workbooks.Open "E:\abc.xls" '' To Open another Excel File
Cells(1, 1).Select '' Selects 1st cell and 1st column in the activesheet
Sheets("abc").Select ''' Selects the sheet whose name "abc"
Worksheets("abc").Select ''' Selects the sheet whose name "abc"
ActiveWorkbook.Sheets("abc").Select ''' Selects the sheet whose name "abc"
ActiveWorkbook.Worksheets("abc").Select ''' Selects the sheet whose name "abc"
result = Application.WorksheetFunction.CountIf(Range("a:a"), ">10") '' Use Excel worksheetfunction like this in VBA
Cells.ClearContents '' This Will Clear All the Contents in Current Sheet
Sheets.Add.Name = "New Workbook" '' Add new sheet with the Name="New Workbook"

No comments:

Post a Comment