VBA Array Size

Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM
VBA Array Size

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Array Size (wallstreetmojo.com)

This article provides a step-by-step guide to finding the array size using VBA Code.

How to Find the Size of an Array Using VBA Code?

Follow the steps to find the array size using Excel VBA Code.

Code:

Sub Array_Size()

 Dim MyArray As Variant

End Sub
VBA Size Array Example 1

Code:

Sub Array_Size()

Dim MyArray As Variant
MyArray = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul")

End Sub
VBA Size Array Example 1-1
  • Step 3: We assigned some values to the array “MyArray.”

If we need to write a loop to store these array values to cells, we must decide how many times the loop has to run. It depends on the number of values the array has.

Look at the number of values assigned to the array “MyArray.” There are a total of 7 values assigned to the array, so now we know how many times the loop has to run to store values of an array to cells.

Code:

Sub Array_Size()

Dim MyArray As Variant
MyArray = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul")

Dim k As Integer
For k = 1 To 7
Next k

End Sub
VBA Size Array Example 1-2
  • Step 5: We have opened the FOR loop starting from 1 to 7. Inside the loop, write CELLS property to store as shown below.

Code:

Sub Array_Size()

 Dim MyArray As Variant
 MyArray = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul")

 Dim k As Integer

 For k = 1 To 7
  Cells(k, 1).Value = MyArray(k)
 Next k

End Sub
  • Step 6: Now, execute the code line by line by pressing an F8 function key upon pressing the F8 key for the first time.
VBA Size Array Example 1-4
  • Step 7: Press F8 now. It will jump to the array value assigning line.
VBA Size Array Example 1-5
  • Step 8: As of now array name “MyArray” has no values. Press F8. It will assign all the mentioned values to the array variable.
VBA Size Array Example 1-6
  • Step 9: Now, the loop will start to run. Press the F8 key twice to see what value we get in cell A1.
VBA Size Array Example 1-7
  • Our first value in the array variable is “Jan,” but we have the result as the second value, “Feb,” when the first value loop is still running.
  • Step 10: When your array values count starts from zero, not 1, we need to include the starting loop as zero.
Example 1-8
  • Step 11: Once the starting position of the loop is decreased by one. Similarly, the ending should be decreased by 1, so make the ending 6 instead of 7.
Example 1-9
  • Step 12: Once the loop starts and ends, we decide on one more tweak we need to do, i.e., in the CELLS property. We have used the “k” variable as the dynamic cell picker, but since our loop starts from zero, there is no cell that starts with zero, so add plus 1 to the variable “k.”

Code:

Sub Array_Size()

Dim MyArray As Variant
  MyArray = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul")
Dim k As Integer

For k = 0 To 6
  Cells(k + 1, 1).Value = MyArray(k)
Next k

End Sub
Example 1-10
  • Step 13: Now, upon running the loop, the first-time “k” value is zero, and since we have added plus 1, the “k” value will be 1, so it refers to cell A1.
  • Step 14: Now run the code, and it will store all the array values in cells.
Example 1-11

However, in this instance, we have manually decided the loop’s starting and ending size, but the array size can be determined easily using LBOUNDLBOUNDLBound in VBA or “Lower Bound” extracts the lowest number of an array. For example, if the array says “Dim ArrayCount (2 to 10) as String” then using LBound function we can find the least number of the array length i.e. 2.read more & UBOUND functionsUBOUND FunctionsUBOUND, also known as Upper Bound, is a VBA function that is used in conjunction with its opposite function, LBOUND, also known as Lower Bound. This function is used to determine the length of an array in a code, and as the name suggests, UBOUND is used to define the array's upper limit.read more.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Find Size of an Array Automatically

  • Step 1: When we about included loop starting and ending point in the above, we have manually counted the number of values the array has, but to start the array, use the LBOUND function. For this, pass the “MyArray” variable name.
Example 1-12
  • Step 2: To determine the last array size, use the UBOUND function and enter the array name “MyArray.”

Code:

Sub Array_Size()

Dim MyArray As Variant
 MyArray = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul")
Dim k As Integer

For k = Lboubd(MyArray) To UBound(MyArray)
 Cells(k + 1, 1).Value = MyArray(k)
Next k

End Sub
Example 1-13
  • Step 3: Start the line-by-line code and press the F8 key until it reaches the starting point.
Example 1-14
  • Step 4: First, place your cursor on “LBound(MyArray)”and see what it says..
Example 1-15
  • Step 5: Starting point number it says is zero. Now, place the cursor on “UBound(MyArray)” and see what it says.
Example 1-16

It says array size as 6. So, as we mentioned starting and ending manually, UBOUND and LBOUND automatically pick the numbers for us.

You can download this VBA Array Size Excel Template here – VBA Array Size Excel Template

Recommended Articles

This article has been a guide to VBA Array Size. Here, we discuss how to find the size of an array using the VBA LBOUND and UBOUND function, along with practical examples and a downloadable Excel template. Below are some useful articles related to VBA: –