Array Builder

Use the Array Builder to create Arrays, Dictionaries, and Collections and manipulate them.

array builder

VBA: Intro to Arrays, Dictionaries, and Collections

Arrays, dictionaries, and collections are used to store information. Dictionaries and collections are like lists of data that can have a key. For example, you create a list of all items a store sells and the # of items in the store’s inventory. The item name (ex. socks) would be the key and the # of items would be the value.

Think of Arrays like ranges of cells in an Excel spreadsheet. They can be any size and you can access values by referencing their positions in the array (ex. arr(1,4)), just like cells (ex. cells(1,4)).

Automate Excel has a brief introduction to Arrays here.

Array Builder Walkthrough

Arrays

Define the Array

array builder part 1
  1. Name – Name the array
  2. Value Type – What type of values (integer, text, etc.) will the array store? Variant (default) arrays accept all values.
  3. Declare Type – Static arrays can not change size. Dynamic arrays can change size.
  4. Populate – Do you want to use the Array Builder to populate your array?
    1. Read Excel Range – Will program VBA to populate the array from a range of cells each time the code is ran. Use this option when the Excel range will always be used to populate the array.
    2. Read Excel Range and Hard-code Values – Tells the Array Builder to read a range of cells, and hard-code those values into the VBA code. Use this option to save time when populating the array.
    3. Do not populate – Only create the array, do not populate it yet.

Populate the Array

array builder part 2
  1. Range – Define the Excel Range to populate the array (not needed if Do Not Populate is selected).
  2. Dimensions and Size – Unless Do Not Populate is selected, these values will populate automatically.
    1. Dimension decides whether the array is 1-dimensional or 2-dimensional. 1-dimensional is just a list. 2-dimensional is like a range of cells.
    2. Lower and Upper Bound determines the size of the array. How many items will it contain? The Lower Bound indicates the starting value. It can be 0,1, or whatever you’d like.

Code For Working With Arrays

array builder part 3

These buttons generate code to work with your newly created array. Mouse over the button to see the generated code.

Collections & Dictionaries

The Collections and Dictionaries Builders have the same settings.

Define the Collections & Dictionaries

collections builder part 1
  1. Name – Name the collection
  2. Populate – Do you want to use the Collection Builder to populate your collection?
    1. Read Excel Range – Will program VBA to populate the collection from a range of cells each time the code is ran. Use this option when the Excel range will always be used to populate the collection.
    2. Read Excel Range and Hard-code Values – Tells the Collection Builder to read a range of cells, and hard-code those values into the VBA code. Use this option to save time when populating the collection.
    3. Do not populate – Only create the collection, do not populate it yet.

Populate the Collection & Dictionaries

collection builder part 2
  1. Range – Define the Excel Range to populate the array (not needed if Do Not Populate is selected).

Code For Working With Collections & Dictionaries

Code For Working With Collections

collection builder part 3

Code For Working With Dictionaries

dictionaries builder part 3

These buttons generate code to work with your newly created collection. Mouse over the button to see the generated code.

< Previous

Text and Text Formatting Builders

Next >

Procedure Builder

Start Automating Excel

automacro
Get AutoMacro