VBA Common Commands

This is also related to below queries:

1. excel vba commands list
2. useful vba codes for excel
3. visual basic programming commands

Most frequently used VBA Commands by Programmers:

1. Application.ScreenUpdating → Turn screen updating off to speed up your macro code. You won't be able to see what the macro is doing, but it will run faster. Remember to set the ScreenUpdating property back to True when your macro ends. 


• Application.ScreenUpdating = False 
• Application.ScreenUpdating = True 


2. Application.DisplayAlerts → The default value is True. Set this property to False to suppress prompts and alert messages while a macro is running; when a message requires a response, Microsoft Excel chooses the default response. If you set this property to False, Microsoft Excel sets this property to True when the code is finished, unless you are running cross-process code. 


• Application.DisplayAlerts = False
• Application.DisplayAlerts = True


3. Application.AskToUpdateLinks → True if Microsoft Excel asks the user to update links when opening files with links. False if links are automatically updated with no dialog box.


• Application.AskToUpdateLinks = False
• Application.AskToUpdateLinks = True


4. Application.CutCopyMode → Returns or sets the status of Cut or Copy mode. Can be True, False. Application.CutCopyMode = False clears the clipboard. Without that line you will get the warning 'There is a large amount of information on the Clipboard' when you close the workbook with a large amount of data on the clipboard.


• Application.CutCopyMode = False
• Application.CutCopyMode = True


5. expression.Outline.ShowLevels → Displays the specified number of row and/or column levels of an outline.


• ActiveSheet.Outline.ShowLevels RowLevels:=8, ColumnLevels:=8 ''' Expand all levels row and columns
• ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 ''' Collapse all levels row and columns


6. expression .Hidden → Set this property to True to hide a row or column. The specified range must span an entire column or row.


• Cells.EntireColumn.Hidden = False '''' Unhide all columns
• Range("D:G,AF:AG,AJ:AO").EntireColumn.Hidden = True '''' Hide columns "D:G,AF:AG,AJ:AO"

expression → A variable that represents a Range object.
Option Explicit → Forces declaration of all variables in a module
Option Private → Indicates that an entire module is Private
On Error → Gives specific instructions for what to do in the case of an error
Kill → Deletes a file

VBA To Open Workbooks

This is also related to below queries:

1. excel vba open file dialog
2. vba workbook open event
3. excel vba open files in folder
4. vba open workbook from path
5. excel vba open workbook read only

The Code:

YourWorkbook = "C\Desktop\Book1.xlsx"
'''' I always set all required workbooks in my module so easier for me to call later. To open or close anytime

Workbooks.Open Filename:=YourWorkbook
'''' Open

Workbooks.Open Filename:=YourWorkbook , ReadOnly:=True
'''' Open as read-only in case other people is opening the workbook

Workbooks.Open Filename:=YourWorkbook , Password:="123", ReadOnly:=True
''' Open workbook with password, and read-only in case other people is opening the workbook

Tips:

1. There are other methods. It depends on your creativity and requirements
2. Above codes are meeting almost all projects

VBA To Close Workbooks

This is also related to below queries:

1. excel vba close workbook without saving
2. close active workbook vba
3. save and close workbook vba
4. excel vba save and close workbook without prompt
5. close specific workbook vba

The Code:

Workbooks("Book1.xlsx").Close
'''' Close Book1.xlsx only

YourWorkbook = "C\Desktop\Book1.xlsx"
Workbooks(YourWorkbook).Close
'''' Close YourWorkbook which is named in the your module

ActiveWorkbook.Close SaveChanges:=False
'''' Close current workbook on display only. Set SaveChanges:=True if you need to save

Sub SaveAndCloseOpenWorkbooks()
    Dim wb As Workbook
 
    With Application
        .ScreenUpdating = False
     
        For Each wb In Workbooks
         
            With wb
                If Not wb.ReadOnly Then
                    .Save
                End If
                If .Name <> ThisWorkbook.Name Then
                    .Close
                End If
        End With

        Next wb
        .ScreenUpdating = True
        .Quit
    End With
End Sub
'''' Close all open workbooks

Note:

1. Just repeat Workbooks("Book1.xlsx").Close and change Book1 with Book2, Book3, etc if you need to close multiple known workbooks at the same time
2. If you want to close the workbook that you are working on, use ActiveWorkbook.Close
3. You can also use ActiveWorkbook.Save to save the active workbook

VBA To Save As XLSX

This is also related to below queries:

1. excel vba save as xlsm
2. excel vba save copy as xlsx
3. fileformat:=xlopenxmlworkbook
4. fileformat:=xlnormal
5. activeworkbook.saveas filename
6. vba savecopyas

The Code:

---

ActiveWorkbook.SaveAs Filename:="Path\FileName.xlsx", FileFormat:=xlOpenXMLWorkbook, Password:="123", WriteResPassword:="", ReadOnlyRecommended:=True, CreateBackup:=False

---

Where:

• FileFormat:='xlOpenXMLWorkbook' or 'xlOpenXMLWorkbookMacroEnabled'
• Password:="YourPassword" or "" if no password
• WriteResPassword:="YourPassword" or "" if no password
• ReadOnlyRecommended:=True or False
• CreateBackup:=True or False

VBA To Activate Worksheet or Workbook or Windows

This is also related to below queries:

1. activate open workbook vba
2. activate current workbook vba
3. vba activate workbook by variable name
4. switch between workbooks vba
5. active workbook vba
6. vba activate workbook by filename
7. this workbook vba

The Code:

---

Windows("Book1.xlsx").Activate

Workbooks("Book1.xlsx").Activate

Sheets("Sheet1").Activate

Workbooks("Book1.xlsx").Sheets("Sheet1").Activate

---