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.
