If you’re looking to automate your Excel spreadsheets, macros are a good way to do so. Macros are automated scripts, typically written using Visual Basic for Applications (VBA), to help you perform certain actions in Excel, such as automate a button press or perform a calculation.
VBA is a useful programming language for new data analysts to learn as it supercharges the functionality of Excel, making it easier to perform certain tasks automatically (and repeatedly) with custom-made macro scripts. For instance, if you want a macro that repeats an action by a certain number of times, you can use a VBA For Loop.
If you’re new to VBA programming and you’re looking to create a basic (or even advanced) macro using a VBA For Loop, this guide should help you.
You can learn more Excel analysis skills with CareerFoundry’s free 5-day data short course.
- What are VBA For Loops in Microsoft Excel
- What are VBA For Loops used for?
- How do VBA For Loops work in Excel?
- Things to consider before using a VBA For Loop in Excel
- How to add a VBA For Loop in Excel using the Visual Basic Editor
How do VBA For Loops work in Excel? Let’s get familiar with the basics.
1. What are VBA For Loops in Microsoft Excel?
VBA for Loops are a programming concept that allows a program to repeat itself. They help to refract your code, reducing the number of specifically-coded actions that are written to help improve the speed and efficiency of your applications.
Loops are flexible tools, giving you the option to repeat a certain action (such as changing cell values) a set number of times. A loop could also be combined with other statements, such as For and If, that help to determine how often, and for how long, a script should run.
2. What are VBA For Loops used for?
Using a VBA For Loop is essential for creating macros that will run continuously as you work through your spreadsheet. Loops like this aren’t unique to VBA—indeed, they’re a common feature in most programming languages, allowing a program or script to run continuously in a sequence with a start and end point.
In VBA, a For Loop repeats an action (or set of actions) for a set number of times in a sequence. For instance, if you had a macro (written in VBA) that inserted values into a column, you could use a For Loop to do so, filling each cell sequentially (eg. A1, A2, A3, etc) until an end value is reached.
For instance, the VBA code snippet shown previously demonstrates such an action. In this example, the VBA macro is designed to insert values into cells in column A, from 1 to 10, and increase in single-digit increments. Once 10 is reached, the script stops.
This is a basic example, but For Loops are powerful enough to perform almost any action you desire in Excel repeatedly. While this guide isn’t a full VBA tutorial (and it assumes you have a certain level of basic VBA experience already), it should allow you to create basic VBA loops using For to repeat an action in sequence.
3. How do VBA For Loops work in Excel?
Let’s assume that you already have an idea in mind as to how your VBA macro should work. Introducing a For Loop into the mix allows you to set your code (or part of your code) to repeat itself a certain number of times.
We’ll explain the process using a simple-to-understand example. As VBA For Loops are useful for finite repetitive actions, let’s assume you want a pop-up to appear when you press a button (with the class name Button).
Pressing the button causes a pop-up box to appear a set number of times, in sequence, using the variable varButton as the end number (in this case, 10). This pop-up box displays the current variable, starting with zero. It then repeats this 9 more times until the varButton variable (10) is reached.
The action of pressing the button begins the loop. As the test has a start and end variable, the loop has a finite number of runs before it finishes. By default, the values increase by 1 (starting with 0, 1, 2, etc) but this can be changed by adding a Step value. For instance, changing this to 5 would mean only three pop-ups appear.
Once the end value is reached, the loop exits and the macro stops. You could, however, add additional actions to perform at this point, such as changing another cell’s value or creating a different pop-up message. You could nest the For Loop within other logical tests, such as a Do While or If statement.
This example contains all of the typical criteria needed to complete a For Loop using VBA, however. The code identifies how many times the action will be performed in a sequence (varButton) and the increment value used to iterate through the sequence (Step).
The only optional part of this example is the button used to start the macro. You don’t necessarily need to link your macro code to a button press, as you could easily begin this loop and activate the macro manually by pressing Developer > Macros > Run instead.
4. Things to consider before using a VBA For Loop in Excel
A VBA For Loop is a flexible and wide-ranging method that allows your code to perform an action sequentially and only stopping if (and when) certain criteria are met.
To help you create this kind of macro in Excel, there are some pointers you’ll need to consider. These include:
- If you want to create a way for your For Loop to end before the final value is reached, you’ll need to add an Exit For statement to your code. This exits the loop and moves on to the next line of code outside of the loop (if your code continues). For instance, you could stop the loop when you press a button.
- A VBA For Loop can be used to cycle through a set number of numerical values in sequence in a basic way using a For Next Loop, but you can also cycle through more complex objects (such as workbooks in your spreadsheet) in sequence using a more complex VBA For Each Loop.
- VBA For Loops can be nested with other logical statements, such as an If or Do While statement. This allows you to integrate more complex decision-making into your code.
- A VBA For Loop will iterate in a sequence, but by changing the Step value, you can change how much the loop counter increases by during each iteration. For instance, if you want your loop to move ahead in a sequence that increases the value by 2, you’d need to use a Step value of 2. If you don’t provide a Step value, the loop counter will increase in increments of 1.
- If your VBA For Loop can’t be performed, or if your code has errors, VBA will exit into debug mode with an error. You’ll need to troubleshoot your code to fix this problem in Excel’s built-in VBA Editor.
While these examples are important, you should also consider any known VBA limitations that aren’t listed here (such as a lack of an undo function when using VBA) before you write your code.
5. How to add VBA For Loops in Excel using the Visual Basic Editor
You can create, test, and run a VBA For Loop in Excel by using the Visual Basic Editor. This is Excel’s built-in VBA editor that allows you to create your own macros using VBA, or edit existing macros created using the Macro Recorder tool.
To begin creating a VBA macro using a For Loop in the Visual Basic Editor, you can follow these steps:
Step 1: Open the VBA Editor
You’ll need to start by opening the Visual Basic Editor in your Excel workbook.
You can do this by pressing the Alt + F11 keys on your keyboard (or Option + F11 on Mac). If you’ve already enabled the Developer tab on the ribbon bar in your Excel’s settings menu, you can also press Developer > Visual Basic to open the editor instead.
Step 2: Create a new VBA Module
The VBA Editor will open in a new window at this point. The next step is to insert your VBA For Loop (and additional code) as a new VBA module. Modules are used to organize VBA code into groups.
To do this, right-click your workbook listed in the tree menu on the left. From the drop-down menu, select Insert > Module.
Step 3: Insert your VBA For Loop code
A new window for your code will appear on the right. This is where you can type or insert the VBA code containing your For Loop. For instance, the following code will insert sequential values into cells A1 to A20 on any active worksheet:
Sub LoopVal()
Dim i As Integer
For i = 1 To 10
Range(“A” & i).Value = i
Next i
End Sub
Step 4: Rename your module and test your code
Once you’ve inserted your code, you’ll need to rename the module which contains the code in order to make it easy to refer to later. To do this, select the module name in the tree menu on the left, then rename it by typing a new module name into the Properties box underneath.
When you’re ready to test your code, press the Run Sub/User Form button. This will run the code in your active worksheet, allowing you to see the macro in action.
Assuming your VBA code worked as intended, you can then proceed to save your workbook with the macro included.
If it doesn’t work, a debug message will appear, and you’ll need to troubleshoot the issue further.
Step 5: Save your workbook
With your VBA macro ready, you’ll need to save your Excel workbook as a Macro-enabled workbook in the XLSM format.
To do this, press Ctrl + S on your keyboard, or press File > Save As.
Step 6: Run your VBA code
Your macro (containing a VBA For Loop) is ready to run once you’ve saved your workbook.
To do this, press Alt + F8 on your keyboard to open the Macro window (or Option + F8 on Mac).
Select your macro (matching the Module name) from the list provided, then press Run.
Alternatively, if your VBA For Loop is scheduled to activate based on another action (such as a cell value being reached, a button being pressed, etc), you’ll need to perform this action to begin the process.
Final thoughts
Once you’ve mastered VBA For Loops, you can take things further by experimenting with Do Until Loops, custom worksheet events, and more. VBA tricks like these allow data analysts to create complex applications inside Excel workbooks that can automate tasks or speed up calculations, but you’ll need to master VBA first.
If you’re new to Excel or you’re interested in a career in data analysis, CareerFoundry’s 5-day short course can help you learn more about the fundamentals. If you’re curious to learn more about Excel, you can check out these articles next: