Microsoft Excel includes a comprehensive macro programming language called VBA. This programming language provides you with at least three additional resources:
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
WorksheetFunction class and respective methods
✓ Using the
xlDirection enumeration to navigate a range
✗ How to create a 'for each' loop
MsgBox class and how to display a message
✗ Using WinAPI in VBA
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.
Step 1: Select Menu Tools --> References…
Step 2: Select the Reference you want to add. This example we scroll down to find “Microsoft PowerPoint 14.0 Object Library”, and then press “OK”.
Note: PowerPoint 14.0 means that Office 2010 version is installed on the PC.
Step 3: in the VB Editor, once you press Ctrl+Space together, you get the autocomplete option of PowerPoint.
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.
Declare a variable that is referencing the
Presentation object of the PowerPoint object library.
Declare another variable that is referencing the
Slide object of the PowerPoint object library.
Now the variables declaration section looks like in the screen-shot below, and the user can start using these variables in his code.
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
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 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 this@ 'Currency
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:
Type Characters (Visual Basic)
This example intend to be a gentle introduction to the Excel Object Model for beginners.
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 :
?) 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.
Worksheets.Add().Name = "StackOveflow"
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.
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:
Done, your should see the following window:
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):