Excel VBA command (4)

Calculation

Command	Description
Application.Calculation = xlManual	Set calculation option to Manual (by F9)
Application.Calculation = xlAutomatic	Set calculation option to Automatic
Activesheet.Calculate	Calculate on activesheet only

CutCopyMode

Command	Description
Application.CutCopyMode=False	Clear data in clip board

Quit

Command	Description
Application.Quit	Quit Excel

ScreenUpdating

Command	Description
Application.ScreenUpdating = False	Result of commands between both will not display on screen
Application.ScreenUpdating = True

StatusBar

Command	Description
Application.DisplayStatusBar = False	Not display status bar
Application.DisplayStatusBar = True	Display status bar
Application.StatusBar = "Test"	Set to show "Test" on status bar

DisplayAlert

Command	Description
Application.DisplayAlerts = False	Not display any alert such as dialog box
Application.DisplayAlerts = True	Display alert

Excel VBA command (3)

Workbook

Command	Description
1. Workbooks.Open "Book1.xls"	Open "Book1.xls"
2. Workbooks.Open Sheets("Sheet1").Range("A1").Value	Open file by referring to file name in cell "A1" of "Sheet1"
3. Workbooks.Open Filename:= "C:\Book1.xls"	Open "C:\Book1.xls"
4. ThisWorkbook.Close	Close workbook which is coded
5. Workbooks("Book1.xls").Close	Close file "Book1.xls"
6. Workbooks(Range("A1").Value).Close	Close file by referring to file name in cell "A1"
7. ActiveWorkbook.Save	Save active workbook
8. Workbooks("Book1.xls").Save	Save "Book1.xls"
9. Workbooks(Range("A1").Value).Save	Save file by referring to file name in cell "A1"
10. ActiveWorkbook.SaveAs "C:\Book1.xls"	SaveAs active workbook to "C:\Book1.xls"
11. Workbooks("Book1.xls").SaveAs "C:\Book1.xls"	SaveAs "Book1.xls" to "C:\Book1.xls"
12. Workbooks(1).Close SaveChanges:=False	Close workbook index 1 without save
13. Workbooks(1).Close SaveChanges:=True	Close workbook index 1 with save
14. Kill "C:\Book1.xls"	Kill file "C:\Book1.xls"
15. Workbooks.Add	Add new workbook
16. ActiveWorkbook.Name	Return name of active workbook
17. Difference between ThisWorkBook and ActiveWorkBook	ThisWorkBook object refers to the workbook that the code is contained in ActiveWorkBook object refers to the workbook that is currently on top (Active) in Excel

Excel VBA command (2)

Worksheet

Command	Description
1. Sheets(“Sheet1”).Select	Select “Sheet1”
2. Sheets(1).Select	Select sheet index no. 1 (count from left hand side)
3. Sheets(Array("Sheet1","Sheet2")).Select	Select “Sheet1” and “Sheet2” together
4. Sheets("Sheet1").Visible=xlVeryHidden	Hide “Sheet1”, not found in menu Format > Sheet
5. ActiveWindow.SelectedSheets.Visible= False	Hide selected sheet together
6. Sheets(“Sheet1”).Delete	Delete “Sheet1”
7. Sheets.Count	Return number of sheets in this workbook
8. Sheets.Add	Insert 1 sheet
9. Sheets.Add Before:=Sheets("Sheet1")	Insert 1 sheet before “Sheet1”
10. Sheets.Add After:=Sheets(“Sheet1”)	Insert 1 sheet after “Sheet1”
11. Sheets.Add After:=Sheets(Sheets.Count)	Insert 1 sheet at last sheet
12. Sheets("Sheet1").Copy Before:=Sheets(2)	Copy “Sheet1” and insert before sheet index no. 2
13. Sheets("Sheet1").Copy After:=Sheets(2)	Copy “Sheet1” and insert after sheet index no. 2
14. Sheets(“Sheet1”).Name = “Test”	Rename sheet name from “Sheet1” to “Test”
15. Sheets(1).Name = “Test”	Rename sheet index no. 1 to “Test”
16. ActiveSheet.UsedRange.Columns.count	Return number of columns used in active sheet
17. ActiveSheet.UsedRange.Rows.count	Return number of rows used in active sheet
18. ActiveSheet.Protect	Protect active sheet from any changes in active sheet
19. ActiveSheet.Unprotect	Unprotect active sheet from any changes in active sheet

Excel VBA command (1)

Cell and Range

1. Cells.Select -> select all cells in worksheet
2. Range("A1").Select, Cells(1,1).Select -> select cell A1
3. Range("A1").Value, Cells(1,1).Value -> return value of cell A1
4. Rows(1).EntireRow.Select -> select entire row number 1
5. Columns("A:B").EntireColumn.Select -> select entire column A and B
6. Selection.CurrentRegion.Select -> select current cell region
7. ActiveCell.Row, ActiveCell.Column -> return row or column number of active cell
8. Selection.Rows.Count, Selection.Columns.Count -> return quantity of row or column of selected area
9. Selection.CurrentRegion.Rows.Count, Selection.CurrentRegion.Columns.Count -> return quantity of row or column of current cell region
10. Range("A1").Formula = "=B1+B2" -> create a formula =B1+B2 in cell A1
11. Range("A1").FormulaR1C1 = "=RC[1]+R[1]C[1]" -> create a formula = B1+B2 in cell A1