A List of Excel VBA Commands [Free PDF Download]

Excel is a powerful tool that is used by millions of people worldwide to analyze, organize and present data. While Excel offers many built-in functions and features, sometimes users need more flexibility and customization to meet their specific needs. This is where Visual Basic for Applications (VBA) comes in. VBA is a programming language that is integrated into Excel and allows users to automate tasks, customize the application, and create custom solutions. In this article, I will provide a comprehensive list of VBA commands that are available in Excel.

Download Excel VBA Commands List

What are VBA Commands in Excel?

Excel VBA commands are the building blocks of VBA programming in Excel. These commands allow you to automate repetitive tasks, customize the Excel interface, and create custom solutions that are tailored to your specific needs. Here are some examples of Excel VBA commands:

A. Range and Cell Commands

  • Range(“A1”).Value = “Hello World” – assigns the value “Hello World” to cell A1.
  • Range(“A1:B5”).ClearContents – clears the contents of cells A1 to B5.
  • Offset(1, 0).Select – selects the cell one row below the current active cell.

B. Worksheet and Workbook Commands

  • Worksheets(“Sheet1”).Activate – activates the worksheet named “Sheet1”.
  • Open(“C:\My Documents\Book1.xlsx”) – opens the workbook located at the specified file path.
  • SaveAs(“C:\My Documents\New Book.xlsx”) – saves the active workbook with a new file name and location.

C. PivotTable and PivotChart Commands

  • PivotTables(“PivotTable1”).RefreshTable – refreshes the data in PivotTable1.
  • PivotCharts(“PivotChart1”).ChartType = xlLine – changes the chart type of PivotChart1 to a line chart.
  • PivotFields(“Category”).Orientation = xlRowField – adds the Category field to the row area of the Pivot Table.

D. Chart Commands

  • SeriesCollection(1).XValues = “=Sheet1!$A$1:$A$10” – sets the X-axis values of the first series in the active chart to cells A1 to A10 in Sheet1.
  • ChartTitle.Text = “Sales by Region” – sets the chart title to “Sales by Region”.
  • ChartType = xlColumnClustered – changes the chart type to a clustered column chart.

These are just a few examples of the many Excel VBA commands that are available. By mastering these commands, you can create powerful macros and solutions that automate tasks, analyze data, and customize the Excel interface.

List of Commonly VBA Commands in Excel

1. Range and Cell Selection

Command Description
Range() Selects a range of cells in a worksheet.
Cells() Returns a specific cell or a range of cells.
ActiveCell Returns the active cell, which is the currently selected cell in a worksheet.
Offset() Returns a range of cells that is offset from a given range.

2. Clipboard

Command Description
Copy() Copies the selected range to the Clipboard.
Paste() Pastes the contents of the Clipboard into the selected range.

3. Worksheets and Sheets

Command Description
Worksheets() Returns a collection of all the worksheets in a workbook.
ActiveSheet Returns the active worksheet, which is the currently displayed worksheet in a workbook.
Sheets() Returns a collection of all the sheets in a workbook, including chart sheets and other sheet types.
Protect Protects a worksheet or workbook structure with a password.
Unprotect Removes password protection from a worksheet or workbook structure.

4. Workbooks

Command Description
Workbooks() Returns a collection of all the open workbooks.
ActiveWorkbook Returns the active workbook, which is the currently displayed workbook.
SaveAs() Saves a workbook with a different file name or format.
Open() Opens a workbook from a file path.
Save Saves changes to a workbook or a copy of a workbook with a new name or location.
Close Closes a workbook.
ThisWorkbook Returns a reference to the workbook that contains the VBA code that is currently running.

5. PivotTables

Command Description
PivotTables() Returns a collection of all the Pivot Tables in a worksheet.
PivotCharts() Returns a collection of all the Pivot Charts in a worksheet.
PivotFields() Returns a collection of all the Pivot Fields in a Pivot Table.
RefreshTable() Refreshes the data in a Pivot Table.
PivotSelect() Selects a range in a Pivot Table.
PivotLayout() Sets the layout of a Pivot Table.
PivotTableWizard() Displays the Pivot Table and Pivot Chart Wizard dialog box.

6. Charts

Command Description
ActiveChart Returns the active chart, which is the currently selected chart in a worksheet.
ChartObjects() Returns a collection of all the chart objects in a worksheet.
SeriesCollection() Returns a collection of all the series in a chart.
ChartTitle Represents the title of a chart.
ChartType Sets or returns the chart type for a chart.
SetElement Sets the appearance of a chart element, such as the chart title or axis labels.

7. Data Validation

Command Description
DataValidation Adds data validation to a range of cells in a worksheet.
ValidationType Sets or returns the type of data validation to apply to a range of cells.
InputTitle Sets or returns the title of the input message that appears when a user selects a cell with data validation.
InputMessage Sets or returns the text of the input message that appears when a user selects a cell with data validation.
ErrorTitle Sets or returns the title of the error message that appears when a user enters invalid data in a cell with data validation.
ErrorMessage Sets or returns the text of the error message that appears when a user enters invalid data in a cell with data validation.
ShowInput Determines whether to display the input message when a user selects a cell with data validation.
ShowError Determines whether to display the error message when a user enters invalid data in a cell with data validation.
InCellDropdown Determines whether to display a dropdown arrow in cells with data validation that allow a list.
IgnoreBlank Determines whether to allow blank cells when validating data.
Formula1 Sets or returns the first value or expression to use in a data validation rule.
Formula2 Sets or returns the second value or expression to use in a data validation rule, if applicable.
Add Adds a data validation rule to a range of cells.
Modify Modifies an existing data validation rule for a range of cells.
Delete Deletes the data validation rule for a range of cells.

8. Miscellaneous

Command Description
MsgBox Displays a message box with a message and optional buttons and icons.
InputBox Displays an input box to get input from the user.
CreateObject Creates a reference to an external object or application, such as a Word document or an Outlook email message.
Shell Runs an external program or command.
Timer Returns the number of seconds since midnight.
Environ Returns the value of an environment variable, such as the user’s username or the path to the Windows directory.
ClearContents() Clears the contents of the selected range.

Conclusion

Excel VBA commands offer a powerful toolset for users who need to automate tasks, customize the application, and create custom solutions. While mastering VBA can take time and practice, the benefits are well worth the effort. Whether you’re an Excel power user or a beginner, learning VBA commands can help you take your skills to the next level and unlock new levels of productivity and creativity.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *