Category Archives: Arrays

The ReDim Statement in VBA

The ReDim statement is used to declare the size of a Dynamic array in VBA.  It can later be used to re-declare the size of the array as many times as you need.  It can only be used for Dynamic VBA Arrays (where the size

Subscript Out of Range: How Do You Fix It?

When running a VBA procedure, you might receive this error message “Run-time error ‘9’, Subscript out of range”. In this article, we will talk about the possible reasons for the occurrence of this error and the ways to fix it.  According to our recent review,

Ubound example

The VBA Ubound function

Description The VBA UBound (Upper Bound) function returns the upper limit of an array as opposed to the LBound (Lower Bound) that returns the lower limit of an array. Both functions (UBound and LBound) can be combined to determine the size of an array. Syntax

No Thumbnail

Excel VBA, Arrays and Ranges

When modifying worksheets and ranges,  one option would be to modify the worksheet directly using the commands I’v explained in the article Referencing Ranges In Excel Using VBA but that would be very inefficient. Every time you read or write something to an excel sheet, you will be consuming considerable

No Thumbnail

Read Data from Table, Access VBA

In this article I will explain how you can read data from an Access table using VBA. This article uses the ADODB.Recordset object. In order to use this object you may need to add reference to ADODB object library. For more information on this topic

No Thumbnail

VBA Arrays and Userdefined Functions and Subs

In this article I will cover the following topics: Passing Arrays as Input Parameters to Functions and Subs ByRef Passing Arrays as Input Parameters to Functions and Subs ByVal Getting the dimension of the Input Array to Function or Sub: Declaring Functions With an Array

No Thumbnail

VBA, Working With Arrays

In this article I will explain some of the main concepts of working with arrays. Using arrays is a great way of working with lists or groups of variables. Jump To: Arrays, Static Vs Dynamic Declaring Static Arrays 1 Dimensional Static Arrays 2 Dimensional Static