Loop Builder


What does it do?

The Loop Builder generates code to loop through objects (ex. cells, sheets, or shapes), arrays, or numbers, performing action(s) on some or all of the items in the loop.

loop builder

How to Access it?

loop builder where

VBA: Intro to Loops

Loops allow you to perform the same action on multiple items without re-writing the code to perform the action:

Dim Cell as Range
For Each Cell in Range("a1:a10")
    if cell.value = FALSE then cell.offset(0,1).value = 0
Next Cell

Dim i as integer
For i = 1 to 10
    if range("a" & i).value = FALSE then range("b" & i).value = 0
next i

Both of these examples will loop through cells A1:A10, checking to see if the value = FALSE, and if so, output 0 in column B.

Loop Builder Walkthrough

Loop Through

The Loop Builder can generate code to loop through the following items:

  • Cells
  • Sheets
  • Workbooks
  • Shapes
  • Charts
  • PivotTables
  • Tables
  • Numbers
  • Array Items

loop generator

Each type of loop has it's own unique settings:

Cells

Loops through all cells in a range.

vba cells loop builder

Define Range - Define the range to loop through (ex. A1:A100)

Define Sheet - Define the sheet where the loop range is located Three Options:

  1. Activesheet - Uses the ActiveSheet
    1. vba loop writer
  2. All Sheets in Workbook - Loops through all sheets in a workbook
    1. vba loops generator
  3. Specific Sheets - Specify a sheet name
    1. vba loop through sheets

Sheets

Loops through all sheets in the workbook.

vba loop builder sheets

Workbooks

Loops through all open workbooks.

vba loop builder workbook

Shapes, Charts, PivotTables, and Tables

Loops through all objects of the specified type in a worksheet or all worksheets in a workbook.

vba loop builder shapes

Define Sheet - Define the sheet where the objects are located (same options as 'Cells' above).

Numbers

Loops through numbers. Define a start number, an end number, and the increment by which to loop (default = 1).

vba loop builder numbers

  • Start Number The first number
  • End Number The last number
  • Step The increment.
    • "1" will loop through the numbers one at a time (1,2,3, etc.). "2" skips a number (1,3,5, etc.). Negative numbers (make sure to define your start and end numbers properly) and decimal values are allowed.

vba loop builder steps

Array Items

Loops through items in an array.

vba loop through array

Arrays are groups of items defined within VBA. These items can be numbers, text, objects, etc.

Array Items does not have any additional settings.

Actions

Defines the action(s) to be performed on the items in the loop. There are too many actions to cover each one individually. Hopefully they are self-explanatory! If not, a simple online search should tell you what you need to know.

vba loop builder actions

Below the "Actions" box, are two checkboxes:

vba loop builder actions 2

No Criteria? - Determines if actions are performed on all items in the loop or only on items meeting certain criteria. This is checked by default, once unchecked, the Criteria section will appear below.

Match Action with Criteria - When unchecked (default), each action is performed on ALL items that meet ALL criteria. When unchecked, each action corresponds to it's corresponding criteria below (Action 1 is linked to Criteria 1). So if Criteria 1 is met, then action 1 is performed. If Criteria 1 is not met, then Criteria 2 is checked, etc.

Note: When "Match Action with Criteria" is checked, if an item meets multiple criteria, only the action from the first criteria match will be performed. In other words, if an item matches criteria 1, the code will not check if the item matches criteria 2 or criteria 3. This is because the If Statement uuses an ELSEIF condiion. Instead, if you desire that not desired, you will need to manually adjust the code (create separate IF statements, instead of one large If block).

Criteria

vba code builder criteria

Note: This is only visible when "No Criteria?" is unchecked above.

Here define the criteria that determine whether to perform the Action(s) above. These criteria allow wildcards (?, *) to increase criteria flexibility.