Trang chủ‎ > ‎IT‎ > ‎Programming‎ > ‎VBA Visual Basic for Word, Excel‎ > ‎

Excel VBA Cheat Sheet

VBA BASICS

Basic VBA syntax, loops, variables, arrays, classes and more

Record a Macro in Excel – Excel Macro Recorder

How to record a macro in Excel? The best way to learn Excel VBA is exactly by recording 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.

VBA Macro Recorder

Go to your Developer tab and click Record Macro

To start recording go to the Developer tab:

Excel VBA Tutorial: Click the Record Macro buttonClick 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 FormRecord 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 MacroExcel VBA Tutorial: Stop Recording VBA MacroStop 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 MacrosClick on Macros

    Next select your Macro and hit Run.

    Excel VBA Tutorial: Execute Excel MacroExecute 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 MacrosClick 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 moduleClick on the newly created module

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

Visual Basic
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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


How to Debug VBA. Debugging VBA in Excel

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

Writing Visual Basic for Applications code is hard, but what about writing VBA code that works and to write it fast? Often I found many colleges struggling to get a few simple procedures to work. I was amazed that most of them preferred to keep at it, trying to pin down the one line of code the causes their issues, rather then spend a few minutes learning how to properly debug Excel code and get the job done much faster! Introducing today’s post on how to debug VBA code!

Debugging is essentially a methodical process of locating and fixing bugs (or defects as some prefer). Basically find that bug and kill it! Excel VBA compared to other programming languages / environments has one significant advantage – you can debug code on the fly without having to recompile the code. This makes life much easier and debugging some much more pleasant! So let’s jump right to it.

Debug VBA: Basic terms

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:
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 when you debug VBA. 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!

Visual Basic
x = 100 y = 10 Debug.Assert y <> 0 'Condition met: Continue! x = 120 y = 0 Debug.Assert y <> 0 'Condition false!: Pause!
1
2
3
4
5
6
7
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:
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).

Visual Basic
Debug.Print "Hello there!"
1
Debug.Print "Hello there!"

The output:

The Immediate windowThe Immediate window

Summary

Debugging is an easy skill to learn. Knowing how to skillfully debug VBA code with benefit your coding experience and efficiency! Let me know what you think!

VBA Variables – declaring using VBA Dim and VBA Data Types

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

In this tutorial we will learn about VBA Variables. We will start with understanding the VBA Dim statement needed to declare a VBA Variable. Next we will dive into the Data Types available in Excel VBA. We will continue with diving into more advanced topics.

Declare and Define VBA Variable

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

Visual Basic
Dim myVar As Integer myVar = 10
1
2
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
Interesting fact – you you can declare and define a variable in one line using the colon symbol:
Visual Basic
Dim myVar as Long: myVar = 10
1
Dim myVar as Long: myVar = 10

Let’s distinguish the two steps:

Visual Basic
Dim myVar As Integer 'Explicit Declaration using VBA Dim statement myVar = 10 'Definition
1
2
Dim myVar As Integer 'Explicit Declaration using VBA Dim statement
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:

Visual Basic
myVar = 10 'Implicit Declaration & Definition
1
myVar = 10 'Implicit 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 using the VBA Dim statement.

Visual Basic
Dim myVar1 As Integer, myVar2 as String, someDate as Date
1
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:

Visual Basic
myVar = 10 'myVar = 10 res = myVa 'res = 0
1
2
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:

Visual Basic
Option Explicit Sub Run() myVar = 10 'ERROR! myVar not defined! End Sub
1
2
3
4
Option Explicit
Sub Run()
   myVar = 10 'ERROR! myVar not defined!
End Sub
Visual Basic
Option Explicit Sub Run() Dim myVar As Integer myVar = 10 'OK! End Sub
1
2
3
4
5
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:

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

Private and Public VBA variables

Private VBA Variable

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).

Visual Basic
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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 VBA Variable

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.

Visual Basic
'---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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
'---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 VBA Variables

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.

Excel VBA If Statement – If…Then…Else VBA Conditions

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

The Excel VBA If Statement is one of 2 basic Conditional Statements in VBA (including the VBA Select Case statement. It allows to conditionally execute sections of code based on whether a certain condition is met or not.

We will start with a simple example of

Visual Basic
Dim sales as Long sales = 100 'The example If...Then...Else Statment If sales > 50 Then Debug.Print "Your sales exceeded 50$" Else Debug.Print "Your sales did not exceed" End if
1
2
3
4
5
6
7
8
9
Dim sales as Long
sales = 100
 
'The example If...Then...Else Statment
If sales > 50 Then
  Debug.Print "Your sales exceeded 50$"
Else
  Debug.Print "Your sales did not exceed"
End if

VBA If Then Else

The syntax of the VBA If Then Else Statement is as follows:

Visual Basic
If Condition1 Then 'Runs if Condition1 is True ElseIf Condition2 Then 'Runs if Condition2 is True '..... Else 'Runs if neither Conditions above are True End if
1
2
3
4
5
6
7
8
If Condition1 Then
  'Runs if Condition1 is True
ElseIf Condition2 Then
  'Runs if Condition2 is True
'.....
Else
  'Runs if neither Conditions above are True
End if

VBA Boolean operators

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)

VBA 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:

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

This is an example usage:

1
2
3
4
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.

VBA For Loop vs For Each Loop

Loops are the basis of VBA automation. The VBA For and For Each loops are the most frequently used types of loops for repetitive procedures. In this post let’s learn all there is to know about the VBA For Loop and the VBA For Each Loop, the advantages and the disadvantages of both.

Want to learn about Do While / Do Until loops? Read this post

VBA For Loop

The For Loop is scope that defines a list of statements that will be executed repeatably for a certain number of times. The For Loop is the most often used loop for situations when the number of iterations is know before the loop is executed (as compared to the While and Do Until Loops).
How to declare a For Loop:

Visual Basic
For Counter = Start To End [ Step StepIncrement ] '...Code here... Next [ Counter ]
1
2
3
For Counter = Start To End [ Step StepIncrement ]
   '...Code here...
Next [ Counter ]

Items Description
Counter Is a numeric variable, the counter index for the loop. This can be only of VBA Native data types (e.g. Long, Integer, Double etc.)
Start and End The starting value of the Counter and the ending value of the Counter
Step Statement indicating that the StepIncrement between increments will be defined
StepIncrement Optional, defaults to 1. A defined step between Counter values. E.g. for Step 2 the Counter value will be incremented by 2 instead of 2

VBA For Example

Below a simple VBA For example loop:

Visual Basic
Dim i as Long For i = 1 To 5 Debug.Print i Next i 'Result: 1,2,3,4,5
1
2
3
4
5
6
Dim i as Long
 
For i = 1 To 5
   Debug.Print i
Next i
'Result: 1,2,3,4,5

It is also possible to loop backwards by providing a start value higher than an end value:

Visual Basic
Dim i as Long For i = 3 To 1 Debug.Print i Next i 'Result: 3,2,1
1
2
3
4
5
6
Dim i as Long
 
For i = 3 To 1
   Debug.Print i
Next i
'Result: 3,2,1

VBA For Example with Step

Below a simple VBA For example with a defined Step loop:

Visual Basic
Dim i as Long For i = 1 To 5 Step 3 Debug.Print i Next i 'Result: 1,4
1
2
3
4
5
6
Dim i as Long
 
For i = 1 To 5 Step 3
   Debug.Print i
Next i
'Result: 1,4

A Step value can also be negative hence providing you with the possibility to loop backward:

Visual Basic
Dim i as Long For i = 5 To 1 Step -3 Debug.Print i Next i 'Result: 5,2
1
2
3
4
5
6
Dim i as Long
 
For i = 5 To 1 Step -3
   Debug.Print i
Next i
'Result: 5,2

VBA For Each Loop

The VBA For Each loop is a scope that defines a list of statments that are to be repeated for all items specified within a certain collection/array of items. The For Each loop, as compared to the For loop, can’t be used to iterate from a range of values specified with a starting and ending value.
How to declare a For Each Loop:

Visual Basic
For Each Iterator in Items '...Code here... Next [ iterator ]
1
2
3
For Each Iterator in Items
   '...Code here...
Next [ iterator ]
Items Description
Iterator The iterating variable. Used to iterate through the elements of the collection or array
Items A collection or array of items
Next Closing statement for the loop. Optionally you can specify the Iterator variable

VBA For Each example

Below a simple For Each example:

Visual Basic
Dim x(3) as Long, xIterator as Variant x(0) = 1: x(1) = 2: x(2) = 3 For Each xIterator in x Debug.Print x Next xIterator 'Result: 1,2,3
1
2
3
4
5
6
7
Dim x(3) as Long, xIterator as Variant
x(0) = 1: x(1) = 2: x(2) = 3
 
For Each xIterator in x
   Debug.Print x
Next xIterator
'Result: 1,2,3

The For Each Loop is easier to use in the sense that you need not specify a starting and ending variable value. However, the For Each loop is a treacherous loop! Using the For Each Loop is some cases will come at a high performance cost. Skip to For vs For Each to understand the dangers of using For Each.

Continue and Exit For Loops

Continue For Loop

A Continue statement in loops is a statement that allows you to skip all remaining statements in your current loop iteration and proceed to the next loop iteration. Compared to Visual Basic, however, VBA (Visual Basic for Applications) does not have an equivalent of a Continue For statement. Fortunately there are at least two ways you can simulate a Continue For statement.

Option 1: Use If

Visual Basic
For i = 1 to 10 '...Code here... If Not(continue_boolean_statement) then '...Non-Continue code here... End if Next i
1
2
3
4
5
6
For i = 1 to 10
   '...Code here...
   If Not(continue_boolean_statement) then
       '...Non-Continue code here...
   End if
Next i

The continue_boolean_statement is meant to be a boolean condition to skip to the next iteration like “i > 10”.
Option 2: Jump using Goto

Visual Basic
For i = 1 to 10 '...Code here... If continue_boolean_statement then Goto ContinueForLoop '...Non-Continue code here... ContinueForLoop: Next i
1
2
3
4
5
6
For i = 1 to 10
   '...Code here...
   If continue_boolean_statement then Goto ContinueForLoop
   '...Non-Continue code here...  
ContinueForLoop:
Next i

The ContinueForLoop is a goto label statement. Both approaches are equivalent, however, the Goto statement is widely discouraged in the software development world as it is very error prone.

Want to learn more on the goto statment. Read this MSDN post

Exit For Loop

The Exit statement can be used not only for loops (including the For loop) but for other scopes like Subs and Functions. The Exit statement will immediately exit the current scope of execution.

Exit For Loop example:

Visual Basic
For i = 1 to 10 '...Code here... If continue_boolean_stament) then Goto EndForLoop '...Non-Continue code here... EndForLoop: Next i
1
2
3
4
5
6
For i = 1 to 10
   '...Code here...
   If continue_boolean_stament) then Goto EndForLoop
   '...Non-Continue code here...  
EndForLoop:
Next i

Performance

It is important to remember that the For and the For Each loop are to be usually used in different circumstances. There are also several limits to using both. Let us first list some of these key differences and similarities:

  • For and For Each loops can both be used to iterate through collections and arrays
  • Only the For loop can be used to iterate through a range of specified values e.g. 1 to 10
  • Only the For loop can be used to replace items of an iterated collection or array

Usually the For loop is capable or replacing any For Each loop, but not the other way round. On the other hand, when dealing with collections of items it is more convenient to use a For Each loop without having to index through the collection of items. But that’s just looking at the capabilities of both approaches. What about performance? Is there any performance penalty for using the For or the For Each loop? Which one is faster and when?

Performance

There are several situations where you need to vary your usage of For vs For Each when looking at performance (execution time). Let’s look at some common scenarios:

Looping through Arrays

To test the performance of For vs For Each let’s consider a simple scenario: we want to calculate the division remainder (Mod) of 2 arrays of data type Long. As both arrays will be 10k elements in size this will require 10’000^2 operations = 100 million.

For Loop

Initialization omitted on purpose.

Visual Basic
Dim x(10000) As Long, y(10000) As Long '... For i = LBound(x) To UBound(x) For j = LBound(y) To UBound(y) res = x(i) Mod y(j) Next j Next i
1
2
3
4
5
6
7
Dim x(10000) As Long, y(10000) As Long
'...
For i = LBound(x) To UBound(x)
  For j = LBound(y) To UBound(y)
    res = x(i) Mod y(j)
  Next j
Next i

For Each Loop

Initialization omitted on purpose.

Visual Basic
Dim x(10000) As Long, y(10000) As Long, tmpX, tmpY '... For Each tmpX In x For Each tmpY In y res = tmpX Mod tmpY Next tmpY Next tmpX
1
2
3
4
5
6
7
Dim x(10000) As Long, y(10000) As Long, tmpX, tmpY
'...
For Each tmpX In x
  For Each tmpY In y
    res = tmpX Mod tmpY
  Next tmpY
Next tmpX

The results

Arrays: VBA For Loop vs For Each LoopArrays: VBA For Loop vs For Each LoopArrays: For vs For Each
From the results above it seems the VBA For Each loop was much slower than the For loop. The For Loop was almost 3xfaster than the equivalent For Each loop! Hopefully this proves that the For Each loop is not recommended for looping through VBA Arrays. So the winner of this round is…

Looping through Collections

Again we will consider the same example to test the performance of For vs For Each. Each collection of objects will contain 1000 items. Hence in total there will be 1000*1000 = 1 million Mod operations.

For Loop

Initialization omitted on purpose.

Visual Basic
Dim x as Collection, y as Collection '... For i = 1 To 1000 For j = 1 To 1000 res = x.Item(i) Mod y.Item(j) Next j Next i
1
2
3
4
5
6
7
Dim x as Collection, y as Collection
'...
For i = 1 To 1000
  For j = 1 To 1000
    res = x.Item(i) Mod y.Item(j)
  Next j
Next i

For Each Loop

Initialization omitted on purpose.

Visual Basic
Dim x as Collection, y as Collection, tmpX as Variant, tmpY as Variant '... For Each tmpX In x For Each tmpY In y res = tmpX Mod tmpY Next tmpY Next tmpX
1
2
3
4
5
6
7
Dim x as Collection, y as Collection, tmpX as Variant, tmpY as Variant
'...
For Each tmpX In x
  For Each tmpY In y
    res = tmpX Mod tmpY
  Next tmpY
Next tmpX

The results

Collection: VBA For Loop vs VBA For Each LoopCollection: VBA For Loop vs VBA For Each LoopCollection: For vs For Each
From the results above it seems the VBA For loop was much slower than the For Each loop. The For Each loop definitely rules when it comes to looping through collections. This time the difference in performance is even more substantial as the For Each loops was almost 27x faster than the For loop! So the winner of this round is…

Conclusions

The VBA For loop and the VBA For Each loop are not interchangeable in any circumstances and have to be used carefully. What you need to remember is the following:

Rule #1: Always use the For loop when looping through VBA Arrays
Rule #2: Always use the For Each loop when looping through a collection of objects such as the VBA Collection, VBA Dictionary and other collections

To make it more simple consider using the For Each loop only when looping through a collection of objects.

VBA Do While Loop – Using Do While…Loop in VBA

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

In this VBA Do While / VBA Do Until tutorial I will demonstrate how to use conditional loops i.e. loops that run While or Until a condition is met. Do While loops should not be used to iterate through arrays or collections. Instead they are met to be run only if a specific condition needs to be met during execution (e.g. run my code while my condition is met). If you want to learn how to iterate through VBA Arrays or VBA Collections read my VBA For loop tutorial.

VBA Do While

Below is an example of a VBA Do While loop. The loop will run While the condition i<10 in the statement below is true.

Visual Basic
i = 0 'Will display 0,1,2,3,4,5,6,7,8,9 Do While i < 10 MsgBox i i = i + 1 Loop
1
2
3
4
5
6
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:

Visual Basic
i = 0 'Will display 0,1,2,3,4,5,6,7,8,9 Do MsgBox i i = i + 1 Loop While i < 10
1
2
3
4
5
6
i = 0
'Will display 0,1,2,3,4,5,6,7,8,9
Do
   MsgBox i
   i = i + 1
Loop While i < 10

Why push the While statement to the back. This makes sense if you don’t want the condition to be tested in the first run of the loop.

Do Until Loop

Will loop until the condition in the Until statement is met (true).

Visual Basic
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
1
2
3
4
5
6
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:

Visual Basic
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
1
2
3
4
5
6
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
The For loop and For Each loop should be used in different situations! Be sure to read this post

.

Exit Do While / Do Until loop

Sometimes it makes sense to leave a VBA Do While or a VBA Do Until loop earlier than the condition specified. We can do this using the Exit Do statement. Below usage:

Visual Basic
Do While ... '... Exit Do 'Exit the VBA Do While loop immediately '... Loop
1
2
3
4
5
Do While ...
  '...
  Exit Do 'Exit the VBA Do While loop immediately
  '...
Loop

See this example below of how Exit Do is used:

Visual Basic
i = 0 Do While i < 10 If i Mod 5 Then Exit Do 'Exit the VBA Do While loop if i modulo 5 equals 0 End If Debug.Print n i = i + 1 Loop
1
2
3
4
5
6
7
8
9
10
i = 0
Do While i < 10
  
  If i Mod 5  Then
     Exit Do 'Exit the VBA Do While loop if i modulo 5 equals 0
  End If
 
  Debug.Print n
  i = i + 1
Loop

Comments