Getting started with excel-vba
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):
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://www.wikiod.com/excel-vba/vba-best-practices#ALWAYS Use “Option Explicit” ).
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 [email protected] '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:
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://www.wikiod.com/excel-vba/getting-started-with-excel-vba#Opening the Visual Basic Editor (VBE) )
- 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 :
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.