Excel VBA Tutorial Introduction
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 Excel – it’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.
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:
Next select the Developer tab to add it to the Excel ribbon as shown below:
Click Ok. You should now see a new tab in your Excel ribbon called Developer as shown below:
Click the Visual Basic button on the Developer ribbon
Click the button as shown below:
This will open the Visual Basic editor as shown below:
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:
Great! You have are now ready to add VBA to your Excel VBA project! Go to the next step of this tutorial.
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:
Congratulations! You should see the following pop-up MessageBox with your message:
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.
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:
Now hit the Record Macro button to start recording:
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:
Next hit OK.
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:
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:
- Use the shortcut CTRL+SHIFT+T
- Go to the Developer tab on the Excel ribbon and click Macros:
Next select your Macro and hit Run.
- 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.
Now look for a new module in your VBA Project and click on that item:
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
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
- 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.
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.
To 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
- 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
- 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!"
Procedures vs. Functions
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.
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.
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.
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 SubProcedure() Call MsgBox("Hello!") End Sub
Sub procedures DO NOT return any values and are used only to execute code.
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
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
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:
- Declare the variable – declare the symbolic variable name (and sometimes data type)
- 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
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|
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.
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
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
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 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 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.
|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.
|=||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.
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
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:
|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
'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
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)
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
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"
Let’s now focus on formatting Excel cells. Below find multiple examples of different formatting options:
'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
'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
'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 borders (Index):
'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.
- 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")
Align message and Question Icon
Call MsgBox("Align this to the right hand side", vbQuestion + vbMsgBoxRight, "Some Message")
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:
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
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:
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 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:
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!
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
Reference the object library you want to include
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