Microsoft Excel includes a comprehensive macro programming language called VBA. This programming language provides you with at least three additional resources:
- Automatically drive Excel from code using Macros. For the most part, anything that the user can do by manipulating Excel from the user interface can be done by writing code in Excel VBA.
- Create new, custom worksheet functions.
- Interact Excel with other applications such as Microsoft Word, PowerPoint, Internet Explorer, Notepad, etc.
VBA stands for Visual Basic for Applications. It is a custom version of the venerable Visual Basic programming language that has powered Microsoft Excel’s macros since the mid-1990s.
Please ensure any examples or topics created within the excel-vba tag are specific and relevant to the use of VBA with Microsoft Excel. Any suggested topics or examples provided that are generic to the VBA language should be declined in order to prevent duplication of efforts.
✓ Creating and interacting with worksheet objects
WorksheetFunctionclass and respective methods
✓ Using the
xlDirectionenumeration to navigate a range
✗ How to create a ‘for each’ loop
MsgBoxclass and how to display a message
✗ Using WinAPI in VBA
To explicitly declare variables in VBA, use the
Dim statement, followed by the variable name and type. If a variable is used without being declared, or if no type is specified, it will be assigned the type
Option Explicit statement on first line of a module to force all variables to be declared before usage (see https://stackoverflow.com/documentation/excel-vba/1107/vba-best-practices/3554/always-use-option-explicit#t=201607231503377559602 ).
Option Explicit is highly recommended because it helps prevent typo/spelling errors and ensures variables/objects will stay their intended type.
Option Explicit Sub Example() Dim a As Integer a = 2 Debug.Print a 'Outputs: 2 Dim b As Long b = a + 2 Debug.Print b 'Outputs: 4 Dim c As String c = "Hello, world!" Debug.Print c 'Outputs: Hello, world! End Sub
Multiple variables can be declared on a single line using commas as delimiters, but each type must be declared individually, or they will default to the
Dim Str As String, IntOne, IntTwo As Integer, Lng As Long Debug.Print TypeName(Str) 'Output: String Debug.Print TypeName(IntOne) 'Output: Variant <--- !!! Debug.Print TypeName(IntTwo) 'Output: Integer Debug.Print TypeName(Lng) 'Output: Long
Variables can also be declared using Data Type Character suffixes ($ % & ! # @), however using these are increasingly discouraged.
Dim this$ 'String Dim this% 'Integer Dim this& 'Long Dim this! 'Single Dim this# 'Double Dim this@ 'Currency
Other ways of declaring variables are:
Static CounterVariable as Integer
When you use the Static statement instead of a Dim statement, the declared variable will retain its value between calls.
Public CounterVariable as Integer
Public variables can be used in any procedures in the project. If a public variable is declared in a standard module or a class module, it can also be used in any projects that reference the project where the public variable is declared.
Private CounterVariable as Integer
Private variables can be used only by procedures in the same module.
Source and more info:
Opening the Visual Basic Editor (VBE)
Step 1: Open a Workbook
Step 2 Option A: Press Alt + F11
This is the standard shortcut to open the VBE.
Step 2 Option B: Developer Tab —> View Code
First, the Developer Tab must be added to the ribbon. Go to File -> Options -> Customize Ribbon, then check the box for developer.
Then, go to the developer tab and click “View Code” or “Visual Basic”
Step 2 Option C: View tab > Macros > Click Edit button to open an Existing Macro
All three of these options will open the Visual Basic Editor (VBE):
Adding a new Object Library Reference
The procedure describes how to add an Object library reference, and afterwards how to declare new variables with reference to the new library class objects.
The example below shows how to add the PowerPoint library to the existing VB Project. As can be seen, currently the PowerPoint Object library is not available.
Note: PowerPoint 14.0 means that Office 2010 version is installed on the PC.
PowerPoint and pressing
., another menu appears with all objects options related to the PowerPoint Object Library.
This example shows how to select the PowerPoint’s object
Step 4: Now the user can declare more variables using the PowerPoint object library.
Code version of this tutorial:
Option Explicit Sub Export_toPPT() Dim ppApp As PowerPoint.Application Dim ppPres As PowerPoint.Presentation Dim ppSlide As PowerPoint.Slide ' here write down everything you want to do with the PowerPoint Class and objects End Sub
- Open the Visual Basic Editor ( see https://stackoverflow.com/documentation/excel-vba/777/introduction-to-excel-vba/2801/opening-the-visual-basic-editor#t=201607252035449856939 )
- Click Insert —> Module to add a new Module :
Copy and Paste the following code in the new module :
Sub hello() MsgBox “Hello World !” End Sub
To obtain :
- Click on the green “play” arrow (or press F5) in the Visual Basic toolbar to run the program:
- Select the new created sub “hello” and click
- Done, your should see the following window:
Getting Started with the Excel Object Model
This example intend to be a gentle introduction to the Excel Object Model for beginners.
Open the Visual Basic Editor (VBE)
Click View —> Immediate Window to open the Immediate Window (or ctrl + G):
- You should see the following Immediate Window at the bottom on VBE:
This window allow you to directly test some VBA code. So let’s start, type in this console :
VBE has intellisense and then it should open a tooltip as in the following figure :
Select .Count in the list or directly type
.Cout to obtain :
- Then press Enter. The expression is evaluated and it should returns 1. This indicates the number of Worksheet currently present in the workbook. The question mark (
?) is an alias for Debug.Print.
Worksheets is an Object and Count is a Method. Excel has several Object (
Chart ..) and each of one contains specific methods and properties. You can find the complete list of Object in the Excel VBA reference. Worksheets Object is presented here .
This Excel VBA reference should become your primary source of information regarding the Excel Object Model.
Now let’s try another expression, type (without the
Worksheets.Add().Name = “StackOveflow”
Press Enter. This should create a new worksheet called
To understand this expression you need to read the Add function in the aforementioned Excel reference. You will find the following:
Add: Creates a new worksheet, chart, or macro sheet. The new worksheet becomes the active sheet. Return Value: An Object value that represents the new worksheet, chart, or macro sheet.
Worksheets.Add() create a new worksheet and return it. Worksheet(without s) is itself a Object that can be found in the documentation and
Name is one of its property (see here). It is defined as :
Worksheet.Name Property: Returns or sets a String value that represents the object name.
So, by investigating the different objects definitions we are able to understand this code
Worksheets.Add().Name = "StackOveflow".
Add() creates and add a new worksheet and return a reference to it, then we set its Name property to “StackOverflow”
Now let’s be more formal, Excel contains several Objects. These Objects may be composed of one or several collection(s) of Excel objects of the same class. It is the case for
WorkSheets which is a collection of
Worksheet object. Each Object has some properties and methods that the programmer can interact with.
The Excel Object model refers to the Excel object hierarchy
At the top of all objects is the
Application object, it represents the Excel instance itself. Programming in VBA requires a good understanding of this hierarchy because we always need a reference to an object to be able to call a Method or to Set/Get a property.
The (very simplified) Excel Object Model can be represented as,
Application Workbooks Workbook Worksheets Worksheet Range
A more detail version for the Worksheet Object (as it is in Excel 2007) is shown below,
The full Excel Object Model can be found here.
Finally some objects may have
Workbook.WindowActivate) that are also part of the Excel Object Model.