excel vba tutorial

Excel VBA Tutorial

1 Star2 Stars3 Stars4 Stars5 Stars (5 votes, average: 5.00 out of 5)
Loading...

Welcome to the ultimate Excel VBA Tutorial (Visual Basic for Applications)! You will walk the journey from being an VBA Zero to becoming an VBA Hero. This VBA tutorial is not aimed only at teaching you how to write VBA macros in Excelit’s a general introduction to VBA. However, some sections as you will notice are indeed aimed at Excel (e.g. the Workbooks and Worksheets section).
Click on the images below to skip to the various steps of this tutorial.

vba basicsVBA Basics

Basic VBA syntax, loops, variables. Excel VBA basics like working with Worksheet/Workbooks/Cells and Ranges etc.

How to open the VBA Editor?

The Developer tab in the Excel ribbon is required to work with Excel VBA. Apart from enabling you to access your VBA Project, it also contains other useful features and shortcuts which we will dive into later e.g. Macro recording, Form Controls (buttons, checkboxes etc.).

Add the Developer tab to your Excel ribbon

To open the options window go to:
File->Options->Customize Ribbon
Next select the Developer tab to add it to the Excel ribbon as shown below:

Excel VBA Tutorial: Select the
Select the “Developer” tab to add it to the Excel ribbon

Click Ok. You should now see a new tab in your Excel ribbon called Developer as shown below:

Excel VBA Tutorial: The Developer tab in the Excel ribbon
The Developer tab in the Excel ribbon

Click the Visual Basic button on the Developer ribbon

Click the button as shown below:

Excel VBA Tutorial: Click the Visual Basic button
Click the Visual Basic button

This will open the Visual Basic editor as shown below:

Excel VBA Tutorial: The VBA editor window
The VBA editor window

Congratulations! Now you can begin your journey with Excel VBA!

Add a new module for your VBA code

To add a new module click on any item (Worbook/Worksheet/Module etc.) in your VBA project Project-VBAProject panel and select:
Insert->Module as shown below:

Excel VBA Tutorial: Add VBA module to your project
Add VBA module to your project

Great! You have are now ready to add VBA to your Excel VBA project! Go to the next step of this tutorial.

Hello World!

In this step you will learn how to create a simple VBA procedure to open a MessageBox with a “Hello World!” message.

Insert the code below to any module (or Worksheet)

As mentioned copy the code below to your module (or Worksheet):

Sub HelloWorld()
    Call MsgBox("Hello World")
End Sub

Hit the F5 button or Run Sub/UserForm button

To run the procedure hit the F5 button or click the Run Sub/UserForm (F5) button to run the procedure:

Excel VBA Tutorial: The menu ribbon - hit F5 or the
” button to run the procedure” class /> The menu ribbon – hit F5 or the “>” button to run the procedure

Congratulations! You should see the following pop-up MessageBox with your message:

Excel VBA Tutorial: Your first VBA procedure: Hello World!
Your first VBA procedure: Hello World!

Understand the code

Let pause for a second to understand the code. The code that is actually executed is the MsgBox command as highlighted below. This command executes and shows the MessageBox.

Sub HelloWorld()
    Call MsgBox("Hello World")
End Sub

Now notice that the MessageBox command is wrapped in a procedure Sub and End Sub clause. You will learn more about procedures in the Procedures vs. Functions section of this Excel VBA Tutorial. For now it is enough you understand that any code you develop that can be executed needs to be wrapped in a Sub and End Sub clause.

Sub HelloWorld()
    Call MsgBox("Hello World")
End Sub

Add a comment to your code

Comments are one of many important things you need to learn. Comments are preceded with a ‘ apostrophe. Comments are not compiled nor executed and are meant just to describe or explain your code. See below an example comment:

Sub HelloWorld()
    'This is a comment! Hello!
    Call MsgBox("Hello World")
End Sub

Now proceed to learning how you can boost your journey with Excel VBA by recording macros instead of creating them from scratch.

Recording Macros

The best way to learn Excel VBA is record macros. This is especially useful if you want to automate mundane Excel tasks e.g. formatting selected cells etc. In this case you can record you series of steps/clicks/typed text etc. and Excel will translate that into VBA code creating a new module into your VBA project.

Go to your Developer tab and click Record Macro

To start recording go to the Developer tab:

Excel VBA Tutorial: Click the Record Macro button
Click the Record Macro button

Now hit the Record Macro button to start recording:

Excel VBA Tutorial: Record Macro Icon

Complete the Record Macro Form and click OK

Now you will see the following Form appear. Provide a Name for your recorded procedure (no spaces). You can also associate a Excel Shortcut with your Recorded Macro. To do that input a letter or number in the Shortcut Key textbox e.g. type “T” which should generate the following shortcut:
CTRL+SHIFT+T
Next hit OK.

Excel VBA Tutorial: Record Macro Form
Record Macro Form

Do something in Excel that you would like to record

Now almost every mouseclick and keyboard press will be recorded and translated to an Excel VBA macro. For this tutorial I would recommend testing a typical useful scenario:

  • Click on a single cell
  • Change the background color
  • Change the font to Bold / Italic etc.

Stop recording the Macro

Hit the Stop Recording button to stop recording the macro:

Excel VBA Tutorial: Stop Recording VBA Macro
Stop Recording VBA Macro

Execute the recorded macro

Assuming the recorded macro can now be reused, let’s try executing it. You can execute the macro in 3 ways:

  1. Use the shortcut CTRL+SHIFT+T
  2. Go to the Developer tab on the Excel ribbon and click Macros:
    Excel VBA Tutorial: Click on Macros
    Click on Macros

    Next select your Macro and hit Run.

    Excel VBA Tutorial: Execute Excel Macro
    Execute Excel Macro
  3. Go to your VBA project, click on the Macro procedure and hit F5

View the generated code recorded by the Record Macro button

Let’s now open up our VBA Project and take a look into the recorded macro. Open your VBA Project by leveraging the Visual Basic button on the Developer tab.

Excel VBA Tutorial: Click on Macros
Click on Visual Basic

Now look for a new module in your VBA Project and click on that item:

Excel VBA Tutorial: Click on the newly created module
Click on the newly created module

The code generated by the Record Macro button should look similarly as below:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
    Selection.Font.Bold = True
    Selection.Font.Italic = True
    Selection.Font.Underline = xlUnderlineStyleSingle
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Debugging VBA

First we need to introduce some basic terms to facilitate the remaining part of this post:

  • Executing/Running code – the process of running a macro
  • Debugging code– the process of finding and fixing bugs/defects
  • Breakpoint – a line of code at which the execution of the macro will pause

Running / Breaking / Reseting

Let’s start with the tool bar at the top of the VBA Project Viewer window. You should find 3 buttons as shown below:
Excel VBA Tutorial: debug_button
The buttons allow you to do the following:

  • Run – run your macro (Sub) or UserForm. This is equivalent to the key shortcut F5
  • Break – pause a running macro CTRL+Break. You can also stop a running macro by hitting the Esc button
  • Reset – reset a running/paused macro

These are the basic commands for running macros.

Break points

Breakpoints specify lines of code at which the execution of your macro should pause. They are convenient when you want to be sure your code does run through a certain loop of If statement.
debug_breakpointTo add/remove a breakpoint simply left-click on the left gray bar in your VBA Project View next to your code. A red dot should appear indicating that you have specified a new breakpoint. Click on the dot again to remove the breakpoint.

Assertions – the right way to breakpoint errors

Often breakpoints are specified in places where error might occur. This may be cumbersome when you have loop running and are not sure when the error will occur or if you are aware of a condition that causes the error but are unable to catch it at the right moment. This is where you will want to use Debug.Assert.

How does Debug.Assert work? Say you are dividing to numbers and want to make sure the denominator is non-zero. Otherwise you want the code to pause. Consider the example below. In the first example the code will continue to execute normally, in the second example however the macro will immediately pause at the assertion as if a breakpoint was defined!

x = 100
y = 10
Debug.Assert y <> 0 'Condition met: Continue!

x = 120
y = 0
Debug.Assert y <> 0 'Condition false!: Pause!

Stepping through code

The key to debugging is to skillfully step through your code either by line or an entire function/procedure. Here are the basic commands found in the Debug menu toolbar:
Excel VBA Tutorial: debug

  • Step Into F8 – step into each procedure/function
  • Step Over SHIFT+F8 – step over every procedure/function (run just the current procedure)
  • Step Out CTRL+SHIFT+F8 – step out of the current
    running procedure
  • Run to Cursor CTRL+F8 – execute and break at the line pointed by the cursor

Usually this is enough although you might want to get familiar with the other commands in the Debug menu toolbar.

The Immediate window and Debug.Print

In the bottom left corner of VBA editor you should find the Immediate window. This panel can be used to execute immediately pieces of code (even your code is paused). Simply start typing and hit ENTER! Additionally the Immediate window is the default output of the Debug.Print VBA command which prints a certain provided string (similarly like the MsgBox but does not display any pop-up). The Debug.Print command is very convenient for outputting VBA execution messages / statuses or execution progress (e.g. number of processed items).

Debug.Print "Hello there!"

The output:

The Immediate window
The Immediate window

Procedures vs. Functions

Definitions

Let’s start by defining what is a Procedure and a Function:

What is a Procedure?
A procedure is a block of VBA statements enclosed by a declaration statement (Function, Sub, Operator, Get, Set) and a matching End declaration. All executable statements in Visual Basic must be within some procedure.
Source: Link

What is a Function?
A Function procedure is a series of VBA statements enclosed by the Function and End Function statements. The Function procedure performs a task and then returns control to the calling code. When it returns control, it also returns a value to the calling code.
Source: Link

As you probably see now Functions are actually a type of Procedure. However, in real life most often you will see the usage of Sub procedures and Function procedures.

What is a Sub procedure?
A Sub procedure is a series of VBA statements enclosed by the Sub and End Sub statements. The Sub procedure performs a task and then returns control to the calling code, but it does not return a value to the calling code.
Source: Link

Structure of Procedures & Functions

Now let’s look into the structure and differences between the 2 main various types of procedures (Functions and Sub procedures):

Sub procedure

Sub SubProcedure()
   Call MsgBox("Hello!")
End Sub

Sub procedures DO NOT return any values and are used only to execute code.

Function procedure

Function FunctionProcedure() as String
   Call MsgBox("Hello!")
   FunctionProcedure = "Hello!"
End Function

Function procedures (or just Functions) DO RETURN VALUES as opposed to Sub procedures. I will omit for now Get and Set procedures as they are only used in VBA Classes.

The structure of a Procedures & Function

Excel VBA Tutorial: Structure of VBA Sub procedure
Structure of VBA Sub procedure
Excel VBA Tutorial: Structure of a VBA Function
Structure of a VBA Function

Calling Procedures & Functions

First let’s clarify that Function Procedures cannot be executed directly. This is because they are meant to return values. If you want to call a Procedure directly use a Sub Procedure instead of a Function Procedure. Similarly, you cannot execute directly a Sub Procedure with argument. See some useful examples of Calling Procedures & Functions below:

Example 1: How to call a Sub and Function

Function GetMessage() as String
   GetMessage = "Hello!"
End Function
Sub ShowMessage()
   Call MsgBox("Hello!")
End Sub
Sub Run()
   Call ShowMessage 'Run the ShowMessage Sub Procedure
   msg = GetMessage 'Get the result of the GetMessage Function
End Sub

Example 2: Calling Procedures

Sub ShowMessage()
   Call MsgBox("Hello!")
End Sub
Sub Run()
   Call ShowMessage 
   ShowMessage 'Same as Call ShowMessage
End Sub

Example 3: Calling Procedures with arguments

Sub ShowMessage(msg as String)
   Call MsgBox(msg)
End Sub
Sub Run()
   Call ShowMessage("Hello") 'If "Call" then wrapped in "()"
   ShowMessage "Hello" 'If no "Call" then no "()"
End Sub

Example 4: Calling Procedures & Functions with multiple arguments

Sub ShowMessage(msg1 as String, msg2 as String)
   Call MsgBox(msg1 & msg2)
End Sub
Sub Run()
   'Output: "Hello World"
   Call ShowMessage("Hello ","World") 
   ShowMessage "Hello ", "World" 
End Sub

Variables

First let’s start with a simple example of using a variable:

Dim myVar As Integer 
myVar = 10         

So what is a variable? Is it basically a symbolic name for a storage location, for storing data or pointers to data. Without variables you would be able to process any data. When using variables there are 2 steps you need to complete:

  1. Declare the variable – declare the symbolic variable name (and sometimes data type)
  2. Define the variable – set a value to the variable

Let’s distinguish the two steps:

Dim myVar As Integer 'Declaration
myVar = 10 'Definition

VBA usually (w/o the Option Explicit macro) allows you to simply define a new variable with any explicit declaration. This means that this is equally valid:

myVar = 10 'Declaration & Definition

When declaring variables remember precede them with the Dim statement.

Declaring multiple variables
You can also use the Dim statement to declare multiple variables in one line.

Dim myVar1 As Integer, myVar2 as String, someDate as Date

Data Types

Below the list of data types available in VBA. For a more detailed list see here (MSDN).

Data Type Bytes Value Range
Boolean Depends on implementing platform True or False
Byte 1 byte 0 through 255 (unsigned)
Date 8 bytes 0:00:00 (midnight) on January 1, 0001 through 11:59:59 PM on December 31, 9999
Double (double-precision floating-point) 8 bytes -1.79769313486231570E+308 through -4.94065645841246544E-324 † for negative values;4.94065645841246544E-324 through 1.79769313486231570E+308 † for positive values
Integer 4 bytes -2,147,483,648 through 2,147,483,647 (signed)
Long (long integer) 8 bytes -9,223,372,036,854,775,808 through 9,223,372,036,854,775,807 (9.2…E+18 †) (signed)
Object 4 bytes on 32-bit platform; 8 bytes on 64-bit platform Any type can be stored in a variable of type Object
Single (single-precision floating-point) 4 bytes -3.4028235E+38 through -1.401298E-45 † for negative values;1.401298E-45 through 3.4028235E+38 † for positive values
String (variable-length) Depends on implementing platform 0 to approximately 2 billion Unicode characters
Type variable A custom data type containing one or more elements (variables). Read more here. Can’t contain Subs or Functions like the Class data type, however, is useful in Binary Writing/Reading files.
User-Defined(structure) Depends on implementing platform Each member of the structure has a range determined by its data type and independent of the ranges of the other members

Option Explicit

I personally prefer explicit variable declaration as you will learn this will help reduce any errors resulting from mistyping variables names. Let me give you an example:

myVar = 10 'myVar = 10
res = myVa 'res = 0

As you can see above, I have defined a variable named myVar with value 10. In the second line I defined a new variable res with the value of myVa – mistyping the name of my myVar variable. Now as myVa is a new variable without a defined value, VBA will assign a default null value to this new variable equal to 0.

To prevent such errors use the Option Explicit statement in the first line of you Module as show below:

Option Explicit
Sub Run()
   myVar = 10 'ERROR! myVar not defined!
End Sub
Option Explicit
Sub Run()
   Dim myVar As Integer
   myVar = 10 'OK!
End Sub

As you can see adding the Option Explicit statement will generate and error when you use/define a variable that was not previously declared.

Constants

In VBA you can also declare constants – values that can be defined only once and cannot be modified (will throw an exception). This is an example of a constant declaration:

   Const myVar As Integer = 10
   Dim x as Integer
   x = myVar + 1 'OK!
   myVar = 11 'ERROR! Constants cannot be redefined

Private and Public variables

Private
A variable that is not accessible only within the scope in which it was declared. Below are some examples of Private variables. In VBA additionally Prviate variables can be declared in ALL scopes except for Subs and Functions (use the Dim statement instead – which is the equivalent).

Private var as String
Sub SomeSub()
  'var is Private within this module
  var = "Hello there!"
  'These variables are also Private in scope of the Sub
  Dim subVar as String
  subVar = "Hello!"
End Sub

Sub TestSub()  
   SomeSub 

   'Will show a message "Hello There!"
   MsgBox var
  'Will show an empty MsgBox - as subVar is not available in this scope
   MsgBox subVar 
End Sub

Public
A variable that is accessible within ALL scopes in contrast to Private variables. Below are some examples of Public variables. In VBA additionally Public variables can be declared in ALL scopes except for Subs and Functions.

'---Module 1---
Public var as String
Sub SomeSub()
  'var is Public within this module
  var = "Hello there!"
End Sub

'---Module 2---
Sub TestSub()  
   SomeSub 

   'Will show a message "Hello There!"
   MsgBox var
End Sub

Global
Global variables are basically equivalent to Public variables. The difference is that Global variables can only be used in modules, whereas Public can be used in all contexts/scopes e.g. modules, classes, forms etc.
Global variables are most likely kept for backwards compatibility (older versions of Excel) so I recommend you stick to using only Public variables.

Conditions

Conditions allow you to make a decision based on the evaluation of a boolean expression (logical expression).

VBA identifies the IF-THEN-ELSE-STATEMENT. See the structure of an IF-THEN-ELSE-STATEMENT below:

If [CONDITION] Then 
   'Code if true
Else
   'Code if false
End If

This is an example usage:

Dim val1 as Integer, val2 as Integer
val1 = 1
val2 = 10
If val1 > val2 Then 
   Debug.Print "Val1 is bigger than Val2"
Else
      Debug.Print "Val2 is bigger or equal than Val1"
End If

The IF-THEN in the IF-THEN-ELSE-STATEMENT does not necessary need to be followed by an ELSE. This example below is totally acceptable:

If val1 > val2 Then 
   Debug.Print "Val1 is bigger than Val2"
End If

Before we jump into the different boolean operators we need to focus on a moment to explain what a boolean value actually is.

Boolean value are either TRUE or FALSE. Similarly a condition can either evaluate to being TRUE (as in met) or FALSE (as in the condition is not met). VBA as other languages has a boolean variable type which you can equally use if your IF-THEN-ELSE-STATEMENT.

Boolean operators

Operator Example Description
AND [CONDITION_1] AND [CONDITION_2] Return TRUE if both CONDITION_1 and CONDITION_2 are met (if they both evaluate to TRUE)
OR [CONDITION_1] OR [CONDITION_2] Return TRUE if EITHER CONDITION_1 or CONDITION_2 are met (if either one evaluates to TRUE)
NOT NOT([CONDITION]) Return TRUE if CONDITION is NOT met (if it evaluates to false)

Now let’s focus on the conditions.

Operator Description
= is equal to (val1 = val2)
<> is not equal to (val1 <> val2)
< is less than (val1 < val2)
> is greater than (val1 < val2)
<= is less than or equal (val1 <= val2)
>= is greater than or equal (val1 >= val2)

The Iif function

Something that very often comes in handy, especially when wanting to reduce the amount of code is the IIF function. If works similarly as the IF function in Excel formulas. Based on the condition it will return either one of 2 values. See the structure of the IIF statement below:

Iif([CONDITION] , [VALUE1], [VALUE2]) 
'Return [VALUE1] if [CONDITION] is met
'Return [VALUE2] if [CONDITION] is NOT met

This is an example usage:

Dim val1 as Integer, val2 as Integer, maxOfTwo as Integer
val1 = 1
val2 = 10
maxOfTwo = Iif(val1 > val2, val1, val2)

The IIF statement is often forgotten although it is very powerful and will make your code so much more clear.

ElseIf

The ElseIf statement enhances the IF-THEN-ELSE-STATEMENT statement by adding the possibility of specifying additional conditional IFs in between. See an example below:

Dim val1 as Integer, val2 as Integer
val1 = 1
val2 = 10
If val1 > val2 Then 
   Debug.Print "Val1 is bigger than Val2"
ElseIf val1 < val2  then
   Debug.Print "Val2 is bigger or equal than Val1"
Else
   Debug.Print "Val1 is equal to Val2"
End If

Select – Case

In some situation rather than create conditions you simply want to enumerate a set of values and carry out different tasks. In these cases the IF-THEN-ELSE-STATEMENT statement seems very excessive.

Select – Case with specific values

You can enumerate each case of a value.

Dim intVal as Integer
intVal = 10
Select Case intVal
  Case 6 
   Debug.Print "The value is 6"
  Case 10       
   Debug.Print "The value is 10"
  Case 11       
   Debug.Print "The value is 11"
  Case Else         
   Debug.Print "Another value"
End Select

Select – Case with ranges of values

Alternatively you can express certain ranges of values.

Dim intVal as Integer
intVal = 10
Select Case intVal
  Case 1 to 5
   Debug.Print "The value is between 1 and 5"
  Case 6 to 10       
   Debug.Print "The value is between 1 and 10"
  Case 11, 12, 13       
   Debug.Print "The value is either 11, 12 or 13"
  Case Else         
   Debug.Print "Another value"
End Select

Select – Case with conditions

Lastly you can also use the Select-Case statement with conditions by using the Is operator:

Dim intVal as Integer
intVal = 10
Select Case intVal
  Case Is <= 10
   Debug.Print "The value is less than or equal 10"
  Case Is <= 20,        
   Debug.Print "The value is less than or equal 20"
  Case Is <= 30, Is > 50        
   Debug.Print "The value is less than or equal 30 or higher than 50"
  Case Else         
   Debug.Print "Another value"
End Select

Loops

First let’s explain what a loop is. Loops are a common programming concept allowing you to repeat a set of code until you exit the loop. A common example could be looping through a list of grocery items or employees etc.

Types of loops in VBA

VBA supports the following types of loops:

Type Description Example
For Loop The basic type of loop. Executes the loop, increments the index until the index does not exceed the maximum value. You can modify the increment by adding the Step statement
'Will display 1,2,3,4,5,6,7,8,9,10
For i = 1 to 10
   MsgBox i
Next i

With Step:

'Will display 1,3,5,7,9
For i = 1 to 10 Step 2
   MsgBox i
Next i
For Each Loop Similar to the standard For Loop but works only for arrays of variables. Does not permit the Step statement
'An array of Long values
Dim arr(0 To 2) As Long 
arr(0) = 0
arr(1) = 1
arr(2) = 2
'Will display 0,1,2
For Each longVar In arr
    MsgBox longVar
Next longVar
Do Until Loop Will loop until the condition in the Until statement is met (true)
i = 0
'Will display 0,1,2,3,4,5,6,7,8,9,10
Do Until i > 10
   MsgBox i
   i = i + 1
Loop

Or you can push the Until statement to the end of the loop:

i = 0
'Will display 0,1,2,3,4,5,6,7,8,9,10,11
Do 
   MsgBox i
   i = i + 1
Loop Until i > 10
Do While Loop Will loop while the condition in the While statement is true
i = 0
'Will display 0,1,2,3,4,5,6,7,8,9
Do While i < 10
   MsgBox i
   i = i + 1
Loop

Or you can push the While statement to the end of the loop:

i = 0
'Will display 0,1,2,3,4,5,6,7,8,9
Do 
   MsgBox i
   i = i + 1
Loop While i < 10
The For loop and For Each loop should be used in different situations! Be sure to read this post

Ranges & Cells

To work with data in Excel files you need to know how to work with Excel cells. When referencing You can reference Excel cells in two ways:

  • Using the Range object – by the name of the range e.g. “A1:B10” (string argument)
  • Using the Cells object – by the coordinates of the cells e.g. 10,5 – as in 10th row and 5th column

The distinction between the 2 may seem obvious nevertheless:
An Excel Cell is a single cell in an Excel worksheet assigned with a row and column number.
An Excel Range is a range of Excel Cells in an Excel worksheet that do not have to be necessarily adjacent e.g. “A1;A10”
When you use the Range and Cell object to obtain a reference to a cell or a range of cells these will always return a Range object.
Important!: Remember that the Ranges and Cells are objects therefore when defining a variable with a Range or Cell you need to precede the definition with the “Set” statement e.g.

Set rng = Range("A1:A2")

Ranges vs. Cells

The Range object can:

  • Reference cells by their name or the name of the range e.g. Range(“A1:B10”)
  • Reference 1 or multiple cells within your worksheet

The Cell object is in fact represented by a Range object with the difference that it can:

  • Reference only a singe cell by it’s row and column number e.g. Cells(1,2)

Examples

Below are some examples of how to use the Range and Cell objects:

Referencing a single cell

Set rng = Range("B1")
Set cll = Cells(1,2)
rng.Value = 2 'Set B1 to 2
cll.Value = 3 'Sets B1 to 3

Referencing a range of cells

Set rng = Range("A1:A2") 'Get the range of cells A1 and A2
rng.Value = 10 'Set cells A1 and A2 to "10"

Referencing Named Ranges

Set rng = Range("CELL_NAME") 'Get the range of a Named Range named "CELL_NAME"
rng.Value = "Hello!" 'Set named range's value to "Hello!"

Worksheets & Workbooks

To work with data residing on other Worksheets and Workbooks you need to know your way around them. Now we will focus on navigating between various Worksheets and Workbooks in Excel VBA.

The current (Active) Workbook and Worksheet

This is how to refer to the active / current / top-most Workbook or Worksheet you are working on:

'Refer to the first worksheet in the ActiveWorkbook
ActiveWorkbook.Worksheets(1).Range("A1").Value = 10 
'Refer to your active / current / top-most Worksheet within the ActiveWorkbook
ActiveSheet.Range("A2").Value = 20 
Want to learn the difference between ActiveWorkbook and ThisWorkbook? Read this post.

How to reference a specific Worksheet or Workbook by name or index

dim wb as Workbook, ws as Worksheet
'---Workbooks---
Set wb = Application.Workbooks(1) 'Get first Workbook
Set wb = Application.Workbooks("Book1") 'Get Workbook by name "Book1"
'---Worksheets---
Set ws = wb.Worksheets(1) 'Get first Worksheet
Set ws = wb.Worksheets("Sheet1") 'Get Worksheet by name "Sheet1"

Formatting

Let’s now focus on formatting Excel cells. Below find multiple examples of different formatting options:

Formatting Code
Interior
'Alternative ways to set the cell background color
With Range("A1").Interior
'Sets the background color to an RGB value (Red,Green,Blue)
 .Color = RGB(255,0,0) 'Green
'Sets the background color to the Theme color accent 1 color
 .ThemeColor = xlThemeColorAccent1 
'Sets the background color to the ColorIndex
 .ColorIndex = 1 '1=Black, 2=White, 3=Red ..etc. Check link below for more
End With

Read more on the ColorIndex here: link

Font
'Alternative ways to set the cell background color
With Range("A1").Font
'Sets the font color to RGB value. See Interior row above for more color options
 .Color = RGB(0,255,0) 
'Sets font as Italic
 .Italic = true
'Sets font as bold
 .Bold = true
'Sets the name of the font e.g. Arial, Times New Roman
 .Name = "Agency FB"
'Set the size of the font
 .Size = 11
'Sets strikethrough
.Strikethrough = True
'Sets as either super or subscript (mutually exclusive)
.Superscript = False
.Subscript = True
End With
Borders
'Set all borders to continuous and thin
 With Range("A1").Borders
 .LineStyle = xlContinuous
 .Weight = xlThin
'Sets the border color to RGB value. See Interior row above for more color options
 .Color = RGB(0,255,0) 
End With
'Set only top border as continuos and thin
With Range("A1").Borders(xlEdgeTop)
 .LineStyle = xlContinuous
 .Weight = xlThin
End With

Possible LineStyles:

  • xlContinuous
  • xlDash
  • xlDashDot
  • xlDashDotDot
  • xlDot
  • xlDouble
  • xlSlantDashDot
  • xlLineStyleNone

Possible borders (Index):

  • xlDiagonalDown
  • xlDiagonalUp
  • xlEdgeBottom
  • xlEdgeLeft
  • xlEdgeRight
  • xlEdgeTop
  • xlInsideHorizontal
  • xlInsideVertical
Alignment
'Increase or decrease cell indent
Range("A1").InsertIndent 1
Range("A1").InsertIndent -1

'Align to left, bottom
With Range("A1").
 .HorizontalAlignment = xlLeft 'or xlRight
 .VerticalAlignment = xlBottom 'or xlTop
End With

'Set wrap text to true
Range("A1").WrapText = True

'Set cell orientation to 45 degrees
Range("A1").Orientation = 45

MessageBox and User Forms

In this section we will focus on using MessageBoxes and UserForms to interact with users.

MsgBox: Messages, Notifications, Questions

The common VBA message box can be well used to interact with users to show messages (information, errors, notifications etc.) or just as well ask simple questions (Ok/Cancel, Retry/Cancel). Let us start by analyzing the parameters of the MsgBox function.

Function: MsgBox
Parameters:

  • Prompt – the message you want to show in the message box
  • Buttons – Optional. One of the following set of buttons below. You can sum them up to get different kinds of message boxes like this vbRetryCancel + vbCritical :
    • vbOKCancel – Display OK and Cancel buttons
    • vbAbortRetryIgnore – Display Abort, Retry, and Ignore buttons
    • vbYesNoCancel – Display Yes, No, and Cancel buttons
    • vbYesNo – Display Yes and No buttons
    • vbRetryCancel – Display Retry and Cancel buttons
    • vbCritical – Display Critical Message icon
    • vbQuestion – Display Warning Query icon
    • vbExclamation – Display Warning Message icon
    • vbInformation – Display Information Message icon
    • vbDefaultButton1 – First button is default
    • vbDefaultButton2 – Second button is default
    • vbDefaultButton3 – Third button is default
    • vbDefaultButton4 – Fourth button is default
    • vbApplicationModal – Application modal; the user must respond to the message box before continuing work in the current application
    • vbSystemModal – System modal; all applications are suspended until the user responds to the message box
    • vbMsgBoxHelpButton – Adds Help button to the message box
    • vbMsgBoxSetForeground – Specifies the message box window as the foreground window
    • vbMsgBoxRight – Text is right aligned
    • vbMsgBoxRtlReading – Specifies text should appear as right-to-left reading on Hebrew and Arabic systems
  • Title – Optional. The title of the Message Box
  • Help – Optional. String expression that identifies the Help file to use to provide context-sensitive Help for the dialog box. If helpfile is provided, context must also be provided.
  • Context – Optional. Numeric expression that is the Help context number assigned to the appropriate Help topic by the Help author. If context is provided, helpfile must also be provided.

Now some examples:

Retry with Critical Error Icon

Call MsgBox("Try to run the VBA code again?", vbRetryCancel + vbCritical, "Error")
Excel VBA Tutorial: MsgBox example: Critical Error and Retry
MsgBox example: Critical Error and Retry

Align message and Question Icon

Call MsgBox("Align this to the right hand side", vbQuestion + vbMsgBoxRight, "Some Message")
Excel VBA Tutorial: MsgBox example: Information and align text
MsgBox example: Information and align text

User interaction with the MsgBox
The message box is a great tool for asking simple Yes/No questions within your VBA code. See some examples below:

If MsgBox("Do you agree?", vbYesNo, "Question") = vbYes Then
    'If Yes...put some code here
Else
    'If No...then do this
End If

This will show the following question and pause the code until the user clicks on one of the buttons:

MsgBox example: Yes/No question
MsgBox example: Yes/No question

Similarly you can create a retry question which is convenient for error handling:

  On Error Goto ErrorHandl:
Retry:
  'Some code here..
ErrorHandl:
  If MsgBox("Retry?", vbYesNo, "Question") = vbRetry Then
     Goto Retry  
  End If

Custom User Forms

User Forms fill the gap where a Message Box won’t do. UserForms allow you to create custom forms with Buttons, Combo-boxes, Scrollbars and many more controls.

Open the VBA Project to add a new UserForm

Excel VBA Tutorial: Inserting a new UserForm
Inserting a new UserForm

A new UserForm will be inserted into your VBA Project under a new branch called Forms.

Open the new UserForm and the Toolbox to view controls

Let’s create a simple form. First we need to see what form controls can we add to it. To view the Toolbox go to the View menu and select Toolbox:

Excel VBA Tutorial: Open the UserForm Toolbox
Open the UserForm Toolbox

Add a Button

Now let’s add a simple button to our new form. To do that we simply need to drag it from the Toolbox. Hit the Button icon and drag it to the new form:

Add a Button to your UserForm
Add a Button to your UserForm

Add code to your new Button

To add code to your Button – double-click the button on the UserForm. This will navigate you to the code section of the UserForm. You can also go to the View code section as shown below:

Excel VBA Tutorial: View code on the UserForm
View code on the UserForm

This should create the following code stub:

Private Sub CommandButton1_Click()

End Sub

Let’s just add a simple MessageBox to the new form to see that the button works:

Private Sub CommandButton1_Click()
 MsgBox "Hello this is CommandButton1!"
End Sub

Using the new User Form

Now as we have created our new UserForm let’s put it to the test. Below a simple example of how to execute your UserForm:

Sub TestUserForm()
 Dim uf as UserForm1
 Set uf = New UserForm1
 uf.Show
End Sub

Try clicking on the button to see that it works!

Objects

To explain it simply Objects are complex data types that can contain multiple properties and procedures. Some typical examples of Objects in Excel are e.g. Workbooks, Ranges, Classes.

Objects need special treating! To create a new Object you need to use the New statement like this:

Dim dict As New Scripting.Dictionary 

To use the New statement you need to be sure that the object is defined within your VBA Project or the library in which it is defined is referenced like shown below:

Go to Tools->References

Excel VBA Tutorial: Add reference to a object library
Excel VBA Tutorial: Add reference to a object library

Reference the object library you want to include

Excel VBA Project: Add the Microsoft Scripting Runtime library
Excel VBA Project: Add the Microsoft Scripting Runtime library

If you don’t want to include a reference to a object library in your project you may also use the CreateObject procedure. See example below:

Dim dict
Set dict = CreateObject("Scripting.Dictionary")

This is equivalent to the New operator, however, the object will only be created if the library is available in Excel’s Available References.

When defining your object variables you need to remember to use the Set statement.

Dim dict As Scripting.Dictionary, dict2 as Scripting.Dictionary
Set dict = Scripting.Dictionary 'Defining a new Dictionary object
Set dict2 = dict 'Set dict2 to reference object dict

Classes

Arrays, Dictionaries and other structures

What next?

Related Posts