In Excel VBA, count the elements in an array with UBound(array) - LBound(array) + 1 for a one-dimensional array. Because arrays do not always start at zero, subtracting the lower bound is essential; for a multidimensional array, count each dimension separately or multiply the dimension counts to get the total number of elements.
What “array count” means in VBA
The word “count” can refer to several different things: the number of positions in an array, the number of values that are populated, or the number of elements along a particular dimension. VBA does not provide a universal array.Count property. Instead, the LBound and UBound functions report an array’s lowest and highest subscripts, and you calculate how many positions lie between them.
For example, an array with subscripts 0 through 4 has five elements, not four. An array with subscripts 1 through 5 also has five. The count is the difference between the endpoints plus one. This distinction matters because VBA arrays can have different lower bounds, and a valid array is not necessarily zero-based.
Also decide whether you need the number of available slots or the number of slots containing meaningful data. A six-element array still has six elements if two entries are blank. Counting only populated entries requires an additional rule for what “populated” means, such as excluding only Empty values or excluding empty strings as well.
Count a one-dimensional array with LBound and UBound
Use the following expression when the array has one dimension and is initialized:
UBound(items) - LBound(items) + 1
Here, items is the array variable. LBound(items) returns its lowest valid index, while UBound(items) returns its highest valid index. The added one includes both endpoints. For instance, if the lower bound is 1 and the upper bound is 4, the calculation is 4 − 1 + 1, or 4 elements.
This complete example creates a Variant array, calculates its count, and prints the result in the Immediate window. In the Visual Basic Editor, open the Immediate window with Ctrl+G on Windows; on Mac, use the editor’s View menu to show it.
Option Explicit
Sub CountOneDimensionalArray()
Dim items As Variant
Dim itemCount As Long
' Create an array containing three values.
items = Array("North", "South", "West")
' Count positions, including both array bounds.
itemCount = UBound(items) - LBound(items) + 1
Debug.Print itemCount
End Sub
The result is 3. This approach works whether the array’s lower bound is 0 or 1, so it is safer than assuming the number of elements is simply UBound(items) + 1.
Why the lower bound matters
VBA can use arrays with different starting indexes. A declaration can specify both bounds, as in an array numbered from 1 to 10. The Option Base statement can also set the default lower bound for certain array declarations that do not specify one. If a project or module uses a different default than expected, an assumption that indexing begins at zero can produce an incorrect count or cause an “Subscript out of range” error.
Use LBound instead of guessing the starting index. For a particular dimension, the syntax is LBound(items, dimension), and the corresponding upper-bound call is UBound(items, dimension). The dimension number starts at 1. For example, dimension 1 is the first dimension; it does not mean that the array’s first index is 1.
When you control the declaration, explicit bounds can make the intent clearer. A declaration such as Dim scores(1 To 5) As Long makes the valid indexes easy to read. Even then, use the bound functions when calculating a count, especially if the code may later work with arrays supplied by another procedure.
Count elements in a multidimensional array
A two-dimensional array has a count for each dimension, rather than one single bound pair. Suppose a grid has three rows and four columns. Its first-dimension count is three and its second-dimension count is four, so it contains 12 positions in total. In general, the total number of positions in a rectangular array is the product of the counts for all its dimensions.
Sub CountTwoDimensionalArray()
Dim grid(1 To 3, 1 To 4) As Variant
Dim rowCount As Long
Dim columnCount As Long
Dim totalCount As Long
' Count each dimension using its own bounds.
rowCount = UBound(grid, 1) - LBound(grid, 1) + 1
columnCount = UBound(grid, 2) - LBound(grid, 2) + 1
totalCount = rowCount * columnCount
Debug.Print rowCount, columnCount, totalCount
End Sub
The output represents 3 rows, 4 columns, and 12 total elements. If you need the number of rows in a worksheet data array, use dimension 1; if you need the number of columns, use dimension 2. This is useful when reading a multi-cell range into a Variant array, because the returned array is commonly arranged by row and column. A one-cell range is a special case: its Value can be a scalar rather than an array, so code that accepts arbitrary ranges should check whether its value is an array before calling LBound or UBound.
For an array with more dimensions, calculate each dimension’s size and multiply the results. Do not multiply the upper bounds alone: bounds might start at 0, 1, or another index. The size of each dimension is always its upper bound minus its lower bound plus one.
Handle uninitialized and erased arrays
A dynamic array declared with empty parentheses has no usable bounds until it is allocated with ReDim or assigned an array value. Calling UBound on an uninitialized dynamic array raises a runtime error. Erasing a dynamic array also removes its allocated contents, so its bounds cannot be used as if it still held elements. Check or manage the array’s initialized state before calculating its size.
For reusable code, the following function returns zero if the input is not an array, is uninitialized, or does not have the requested dimension. A zero result therefore means “no count was available” as well as “zero elements”; VBA does not provide an ordinary allocated array with a zero-length dimension in the same way some languages do.
Public Function ArrayElementCount(ByVal items As Variant, _
Optional ByVal arrayDim As Long = 1) As Long
Dim lowerIndex As Long
Dim upperIndex As Long
On Error GoTo NoCount
' Reject scalar values before asking for array bounds.
If Not IsArray(items) Then Exit Function
lowerIndex = LBound(items, arrayDim)
upperIndex = UBound(items, arrayDim)
ArrayElementCount = upperIndex - lowerIndex + 1
Exit Function
NoCount:
' Includes an uninitialized array or an invalid dimension.
ArrayElementCount = 0
End Function
This is a convenience function, not a substitute for tracking program state. If zero elements and an uninitialized array must be treated differently in your application, store an initialization flag or handle the state explicitly where the array is created. The error handler is deliberately narrow in purpose, but as with any error handling, review it if you expand the function to perform other operations.
Array positions versus populated values
Bounds count positions, not nonblank entries. If an array has six positions and only four contain values you consider useful, the bounds calculation still returns six. To count entries under a particular definition, loop through the array and apply that definition to each item.
This example counts values that are not Empty. It does not treat a zero-length string as Empty, and it does count zero, False, and strings containing spaces. Those choices may or may not match what your data means.
Sub CountNonEmptyValues()
Dim items As Variant
Dim item As Variant
Dim populatedCount As Long
' Two values are Empty; the empty string is not Empty.
items = Array("Oak", Empty, 0, "", Empty)
For Each item In items
If Not IsEmpty(item) Then
populatedCount = populatedCount + 1
End If
Next item
Debug.Print populatedCount
End Sub
The result is 3: “Oak,” zero, and the empty string are all not Empty. If you want to exclude empty strings as well, add a condition suited to the possible data types. Be cautious about converting every item to text with CStr: values such as Null or Excel error values need separate handling, and converting them blindly can raise an error. Define whether spaces, zero, False, Null, and worksheet error values count before choosing a test.
Arrays compared with Collections and Dictionaries
Arrays are useful when you need indexed storage, compact data, or a block of values read from a worksheet. Their bounds describe valid index positions, but there is no built-in Count property. A Collection, by contrast, exposes a Count property, so a collection’s item count can be read directly. A Dictionary also has a Count property and is useful when values are associated with unique keys.
Choose the structure that fits the operation rather than converting everything to an array just to count it. A fixed or dynamic array is a natural fit for indexed data and bulk range processing. A Collection can suit a list whose size changes as items are added or removed. A Dictionary is designed for key-based lookup as well as storing entries. Their behavior and available features differ, so a Count property on a Collection or Dictionary should not be confused with an array bound.
Common mistakes and practical safeguards
- Forgetting the plus one: The difference between the upper and lower bounds is one less than the number of positions. Include
+ 1. - Assuming a zero-based array: Use
LBoundrather than hard-coding zero or one. - Leaving out the dimension: For multidimensional arrays, specify dimension 1, 2, and so on when asking for bounds.
- Counting a scalar as an array: Values from a one-cell range or a scalar variable do not have array bounds. Use
IsArraywhen the input might be either. - Using bounds as a nonblank count: Bounds report allocated positions, even when some values are Empty.
- Ignoring allocation state: An uninitialized or erased dynamic array can cause an error when its bounds are requested.
When writing macros that alter a workbook, save a copy of the workbook before running them. VBA macros cannot be undone with Ctrl+Z; the undo history is not a reliable way to reverse macro changes. The examples here only print counts, but the same precaution matters when adapting code to edit cells or workbook data.
Frequently Asked Questions
Is there an Array.Count function in VBA?
No. Use UBound(array) - LBound(array) + 1 for an initialized one-dimensional array. Collections and Dictionaries have a Count property, but VBA arrays do not.
How do I count the rows in a two-dimensional array?
Calculate UBound(array, 1) - LBound(array, 1) + 1. Dimension 1 is the first dimension, which is normally the row dimension for an array read from a multi-cell Excel range.
How do I count all elements in a two-dimensional array?
Calculate the count of dimension 1 and the count of dimension 2, then multiply them. For a 3-by-4 array, the total is 12 positions. Use the bounds for each dimension rather than multiplying upper bounds.
Why does UBound give me an error?
Common causes include asking for bounds on an uninitialized or erased dynamic array, passing a scalar instead of an array, or requesting a dimension the array does not have. Check that the value is an array and has been initialized before calling UBound.
Does UBound return the number of array elements?
Not by itself. It returns the highest valid subscript. For an array whose indexes run from 1 to 5, UBound returns 5 and the element count is also 5; for an array indexed from 0 to 5, the count is 6. Use both bounds to avoid that ambiguity.
How can I count nonblank values rather than array slots?
Loop through the array and test each item using the meaning of “nonblank” required by your data. Decide whether an empty string, spaces, zero, False, Null, or an Excel error should count; there is no single test that fits every dataset.
Related
- Excel Table Structured References: Rules, Examples & Tips
- Excel Take Data From a Table: Methods and Examples
- Excel Table Data Filtering: How to Narrow Rows
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Array Maximum Size: Limits and Practical TipsThere is no single, guaranteed maximum number of elements for an Excel VBA array: its usable size depends…
- VBA Delete Rows in a Range: Safe Methods and ExamplesTo delete entire worksheet rows that intersect a range, use the range’s EntireRow property and call Delete, for…
- Excel VBA MsgBox Alternatives for Better User AlertsExcel VBA alternatives to MsgBox include the status bar for brief updates, a worksheet cell for persistent messages,…
- Excel VBA Macro Best Practices for Reliable WorkbooksFor reliable Excel VBA macros, make each procedure do one clear job, declare variables with Option Explicit, qualify…
- VBA Code to Remove an Excel Workbook Password SafelyVBA can remove a password from Excel worksheet protection or workbook-structure protection when you already know the password;…
- Excel VBA Input Box With a Drop-Down ListExcel’s built-in VBA InputBox cannot display a drop-down list. To let someone choose from fixed options, create a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.