Tag Archives: array

The VBA ParamArray for a dynamic list of VBA arguments

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

Today’s VBA Tip of the Day concerns dynamic parameter declaration via the VBA ParamArray. When declaring Functions/Procedures in some cases you may need to specify Optional arguments. Let’s say we want to write a procedure that works similarly as other procedures you know natively from Excel like SUM:

Excel SUM
Excel SUM takes any number of arguments separated by the locale array delimiter (; or ,)

Now how would you create such a Function in VBA? Well certainly NOT like THIS!!!:
SUM Excel example
Optional arguments are nice to use, only when you know there is going to be only a few. But in the above example we may as well get 20 different Excel Ranges!

Interestingly, this totally non-sense approach is used by Microsoft Excel WorksheetFunctions. See for yourself:
WorksheetFunctions.Sum declaration
WorksheetFunctions.Sum declaration
If only the guy writing this knew about ParamArrays…

Use the VBA ParamArray

In the above example, when dealing with a large amount of optional parameters use the VBA ParamArray statement in your Function/Procedure declaration:

Public Function MySUM(ParamArray args())
    For Each arg In args
        MySUM = MySUM + arg
    Next arg
End Function

Notice that I replaced Optional arg1, Optional arg2, Optional arg3, Optional arg4, Optional arg5 with ParamArray args(). Cool huh? Well that’s basically it! Nothing hard there!

Remember! The ParamArray statement works only with Variant variables. So don’t try the following

ParamArray args() as String

Also the ParamArray statement can’t be used with the following variable statements:

  • ByVal
  • ByRef
  • Optional

That’s it for today’s VBA Tip of the Day!

Next steps

Be sure to read my other posts on:
VBA Array Tutorial
VBA Collection Tutorial
VBA Dictionary and other data structures

The VBA Array tutorial

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

The VBA Array is a very convenient and efficient for storing multiple items of usually the same data type. The size of a VBA Array can be either fixed or dynamic depending on how it is declared. VBA Arrays can also be 1 or multi-dimensional.

linkNavigate
one dimensional VBA array
multi dimensional vba arrays
vba fixed array
vba dynamic array
sizing and moving VBA array
vba array functions

One-dimensional VBA Array

A one-dimensional VBA Array contains a sequence of elements within a single dimension. The default numbering sequence of these elements starts at 0. You can redefine the starting and ending index of the sequence of elements using the X to Y statement. See an example below:

One-dimensional VBA Array
One-dimensional VBA Array

Let’s start by introducing a simple one-dimensional VBA Array of 10 items:

Notice that I have sized the array for indices 1 to 10. This is not a required statements as I can also declare the VBA Array size without the index range.

Another example of 10 item array indexed from 2 to 11.

Multi-dimensional VBA Array

A multi-dimensional VBA Array contains a sequence of elements within multiple dimensions (as many as defined). The default numbering sequence of these elements, within each dimension, starts at 0. You can redefine the starting and ending index of the sequence of elements using the X to Y statement. See an example below:

Two-dimensional VBA Array
Two-dimensional VBA Array

In some cases you might not want to limit yourself to just 1 dimension. VBA Arrays can have multiple dimensions. See a simple example below:

In case of dynamic arrays (read on) multidimentional arrays in VBA have proven problems with using the Preserve statement to preserve the arrays contents when resizing it. For more information on how to workaround this problem see this post here.

Fixed VBA array

The VBA Arrays above were all of fixed size i.e. their size was defined when declaring the VBA Array using the Dim statement. Let’s quickly compare fixed and dynamic sized arrays.

Fixed size arrays should be used when you know before execution how many items your VBA Array should contain. Fixed arrays are equivalent in usage to dynamic arrays although simply have the limit of being un-resizable.

Dynamic VBA array

Dynamic sized VBA Arrays are arrays that can be sized and re-sized even multiple times. This is useful when you are either not sure before execution what the required size of your VBA Array should be or you want to optimize memory by allocating a large sized array for only a short period of time. Let’s start with a simple example:

If you want to set your own index range feel free to use the ReDim statement as such:

ReDim Statement – Resizing a Dynamic VBA Array

The ReDim statement is used to set the size of your VBA Array or to resize it if needed. See an example below.

The problem with a regular ReDim is that your previous items will get erased. When you want to resize an VBA Array of existing items you need to use ReDim with Preserve. See an example below:

Erasing Dynamic VBA Arrays

Dynamic VBA Arrays can contain large amounts of data that can use a lot memory space. You can erase (free memory from) a Dynamic VBA Array by using the Erase statement. It is important therefore to wipe out the Array in a proper manner. For this use the Erase procedure like so:

Sizing and moving through Arrays

Sizing VBA Arrays

VBA Arrays can be sized by either specifying the size of the array (dimension) or by specifying the index range of the VBA Array. I can definitely recommend the first approach as being more practical and less error prone.

VBA Array size

To get the size of your VBA Array use the following function:

Getting the size of VBA Arrays has always caused some confusion. First let’s understand the UBound and LBound functions:

LBound vs. UBound

From the example above we see that the UBound function alone might not always provide correct results.
Let’s summarize this with an example

Traversing a VBA Array

To traverse (iterate) through a VBA Array you can either use the For or For Each loops.

For Each example

For example

VBA Array Functions

Below a list of various VBA Functions useful when dealing with VBA Arrays (click to expand):

Basic functions

Function Details
LBound (Array, Rank) – return the lowest subscript (index range) of the VBA Array within a certain dimension (Rank). E.g.
UBound (Array, Rank) – return the highest subscript (index range) of the VBA Array within a certain dimension (Rank). E.g.
ReDim Reallocate storage space for the array (set new array size). Applies to dynamic sized arrays only
Erase Release array variable and deallocate the memory used. Applies to dynamic sized arrays only

Batch define VBA Array

VBA allows to define array elements in a batch like so using the VBA Array function:

For String Arrays it might be more convenient to use the VBA Split function:

Other custom VBA Array functions

Merging 2 VBA Arrays

Comparing two arrays (1 Dimensional)

Sorting an array (1 Dimensional) – Quick Sort

Original source here.

VBA Array Limits and Errors

VBA Array Limits

What are the limits of VBA Arrays? For one thing the maximum length of every dimension is defined by an Integer meaning that each dimension can have a maximum size of (2^31)-1 (equal to 2’147’483’647). Although, I would assume you could sooner reach the limit of the actual memory used by the array (about 500MB for 32-bit VBA and about 4GB for 64-bit VBA).

Typical VBA Array errors

Typical errors listed below:

  • Runtime Error 9: Subscript out of range – you are trying to access/set an item of the array outside its range e.g. if you array is of size 10 setting the item of index 11 will give you an out of range error

Other data structures

The VBA Array has its limits and is certainly not as versatile as you may need. In some cases it is better to replace a VBA Array with a VBA Dictionary, ArrayList, Stack, Queue or other data structure objects. Read more here:
The VBA Dictionary and other data structures

F.A.Q.

Below a list of Frequent Asked Questions on VBA Array usage:

I want to to store items in my VBA Array by Key name instead of index number. Is this possible?
No this is not possible using a VBA Array. Use a Dictionary Object.

Should I use the UBound function to get the size of an VBA Array?
No! This in case of some arrays will provide a correct result HOWEVER in some cases will provide incorrect. See this section for more.

My VBA Array will keep expanding. But I don’t want to keep using ReDim to correct the Array size. What to do?
Use the ArrayList object. It allows you to add items without needing to resize it either by upsizing or downsizing your VBA Array.

Can I pass an array as an argument to a Function or Sub?
Sure. See the example below where I pass a VBA Array by it’s reference to modify it within a separate procedure.

VBA Dictionary – Using the VBA Dictionary. Key Value pairs

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

Visual Basic for Applications (VBA) facilitates many useful data structures – its not just VBA Arrays you can use. COM libraries provide additional data structures such as the VBA Dictionary, ArrayList, Queue, Stack or SortedList.
But before we jump into explaining how these more complex data structures work and how to use them, let’s start with the basics – VBA array. Often you needn’t reach out for more complex structures, so it’s important to understand first the limits of VBA Arrays.

VBA Dictionary example

VBA DictionaryThe VBA Dictionary is probably the second most often used data structure in VBA. Dictionaries are great at storing key-value pairs of data similarly as the VBA Collection object does.

The VBA Dictionary, however, offers some functionality that is not available with the VBA Collection object e.g. the option to specify a comparison method for Keys, which I will demonstrate below. This allows for a case-sensitive Key.

Ok, so let’s start with a simple example of how the VBA Dictionary can be used.

VBA Dictionary Late Binding

VBA Dictionary Early Binding

In case you prefer to want to declare variables as the Dictionary object you need to reference the Microsoft Scripting Runtime library. To do this go to the Tools menu and select References. From the pop-up window scroll down and select the library mentioned above. This will allow you to use the VBA Dictionary like this:

Both methods are ok with the difference that with Early Binding if you share your Excel file with another user he/she may have to first reference the Microsoft Scripting Runtime otherwise will be getting errors.

Hence I personally prefer Late Binding limiting any necessary actions from anyone I would be sharing my files with.For learning purposes, however, it may be easier to reference the library and use Early Binding allowing you to see the properties of the Dictionary using the suggestions (CTRL+Space).

Traversing items and keys

Below methods for traversing either all keys or items in a VBA Dictionary

Removing items

You can remove either a single specific item by key using the Remove property or RemoveAll to remove all items in the Dictionary:

Similar data structures

  • Hashtable — similar to the Dictionary class. Represents a collection of key/value pairs that are organized based on the hash code of the key. If you want to learn when it’s efficient to use Hashtables read here. More here.
  • SortedList — Similar to the Hashtable. Represents a collection of key/value pairs that are sorted by the keys and are accessible by key and by index. More here.

Creating your own structures (VBA Class)

If none of the above data structures are suitable for your needs you can always create your own VBA Class. A Class is a VBA Module that can be instantiated as an object (similarly as the objects above), have its own properties etc. Classes are a good way of encapsulating abstraction and complexity.

Alternatively, consider also a stack of any of the mentioned data structures. For example let’s say we want to manage a structure which would store words in different languages. Later we will want to quickly check if there is a word in a certain language in our multi-dictionary. How to do this? A Dictionary of Dictionaries should do the trick. The first Dictionary will have keywords mapping to different languages. The keyword will be e.g. “English” while the value will be an object of type Dictionary in which you can store the word in the “English” language. Cool right?