VBA Array Length (Size)
In VBA, to get the length of an array means to count the number of elements you have in that array. For this, you need to know the lowest element and the highest element. So, to get this you can use the UBOUND and LBOUND functions that return the upper bound and lower bound, respectively.
Apart from that, you can also use the COUNTA which is a worksheet function. And in this tutorial, we will see both of the methods so you can use any of them at your convenience.
Steps to Get the Size of an Array
Here we have an array that contains a list of months and sales quantity for each month.
- Make sure to have an array declared properly with rows and columns.
- After that, two more variables (as we have a two-dimensional array) to store the bounds of the array.
- Next, you need to use a formula where you have to use the Ubound function to get the upper bound and then Lbound to get the lower bound of the array.
- As you have a two-dimensional array you need to get bound for both of the dimensions and set that value to the variables.
Dim yearSales(1 To 12, 1 To 2) As Integer Dim iCount1 As Integer, iCount2 As Integer iCount1 = UBound(yearSales, 1) — LBound(yearSales, 1) + 1 iCount2 = UBound(yearSales, 2) — LBound(yearSales, 2) + 1 MsgBox iCount1 * iCount2
Note: You must be wondering that we have a total of 13 rows in the array that I shared with you at the start of the post.
But we have used an array with 13 rows because the first row was a heading. And here we have used an IF STATEMENT and ISEMPTY function to check if the declared array has zero elements.
Using COUNTA to get the Length of the Array
As you know that an array is a bunch of elements that are structured in a single or multi-dimensional way and you can use the COUNTA function (worksheet function) to count these elements in one go.
In the following code, you have used the same array that you have declared earlier and then used a variable to store the element count returned by the function.
And as you can see the result it has returned is 24 that’s the count of the total number of elements that we have in the array.
There’s one thing that you need to take care of that this method won’t be ideal to use in all situations, so it’s always good to use the method that we have discussed earlier.
You can also write a code to check first if the declared array is not blank.
VBA Array Length / Size
In order to get the length of an Array, you need to know the array’s start and end positions. You can do this with the VBA’s UBound and LBound Functions.
LBound and UBound Functions
This procedure demonstrates how to use the UBound and LBound Functions on a a single dimension array:
Subtracting the two will give you the array length (UBound – LBound +1).
Get Array Length Function
This function will calculate the size (length) of a single-dimensional Array:
Get 2D Array Size
This function will calculate the number of positions in a two-dimensional array:
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! 
VBA Code Examples Add-in
Easily access all of the code examples found on our site.
Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.
(No installation required!)
VBA Code Generator
AutoMacro: VBA Add-in with Hundreds of Ready-To-Use VBA Code Examples & much more!
What is AutoMacro?
AutoMacro is an add-in for VBA that installs directly into the Visual Basic Editor. It comes loaded with code generators, an extensive code library, the ability to create your own code library, and many other time-saving tools and utilities that add much needed functionality to the outdated VBA Editor.
Get length of array?
I’m trying to get the length of an array, yet I keep getting this error:
Am I doing something wrong?
7 Answers 7
Length of an array:
UBound alone is not the best method for getting the length of every array as arrays in VBA can start at different indexes, e.g Dim arr(2 to 10)
UBound will return correct results only if the array is 1-based (starts indexing at 1 e.g. Dim arr(1 to 10) . It will return wrong results in any other circumstance e.g. Dim arr(10)
More on the VBA Array in this VBA Array tutorial.
Function
Usage
If the variant is empty then an error will be thrown. The bullet-proof code is the following:
Compilating answers here and there, here’s a complete set of arr tools to get the work done:
UBound and LBound do not work when we have an uninitialized dynamic array.
I found no solutions for it, so, I handled the error. Now It works for all my script situations:
Copy/Pasta Solution:
The most common answer is this:
UBound(myItems) — LBound(myItems) + 1
While it works +90% of the time, that other 10% fails with nasty unplanned errors when a client/user is running it. That is because there are a number of edge cases which this solution does not cover.
Generic Solution:
The solution below covers all the edge cases I have found thus far. And it eliminates all the run-time failures when a client/user is running it.
Caveat:
While the above "Generic" solution works and is robust, it is not the most performant. IOW, if one knows one is working with Dim strings() As String , then a more specific solution can be many times faster.
Much Faster Solution:
The Array of String solution below is many times faster than the "Generic Solution" above. Why? Because the extra instructions (defaulting to SIZE_NOT_ARRAY , IsArray , IsEmpty , etc.) and the conversions around from Variant to Array appear to carry considerable cost. In my testing, the solution below can be over 10 times faster.
VBA Excel. Массивы (одномерные, многомерные, динамические)
Объявление одномерных (линейных) статических массивов в VBA Excel:
В первом случае публичный массив содержит 10 элементов от 0 до 9 (нижний индекс по умолчанию — 0, верхний индекс — 9), а во втором случае локальный массив содержит 9 элементов от 1 до 9.
По умолчанию VBA Excel считает в массивах нижним индексом нуль, но, при желании, можно сделать нижним индексом по умолчанию единицу, добавив в самом начале модуля объявление «Option Base 1». Вместо верхнего индекса можно использовать переменную.
Многомерные массивы
Объявление многомерных статических массивов в VBA Excel аналогично объявлению одномерных массивов, но с добавлением размерностей дополнительных измерений через запятую:
Третий массив состоит из 10000 элементов — 10×10×10×10.
Динамические массивы
Динамические массивы в VBA Excel, в отличие от статических, объявляются без указания размерности:
Такие массивы используются, когда заранее неизвестна размерность, которая определяется в процессе выполнения программы. Когда нужная размерность массива становится известна, она в VBA Excel переопределяется с помощью оператора ReDim:
Переопределять размерность динамических массивов в процессе работы программы можно неоднократно, как по количеству измерений, так и по количеству элементов в измерении.
С помощью оператора ReDim невозможно изменить обычный массив, объявленный с заранее заданной размерностью. Попытка переопределить размерность такого массива вызовет ошибку компиляции с сообщением: Array already dimensioned (Массив уже измерен).
При переопределении размерности динамических массивов в VBA Excel теряются значения их элементов. Чтобы сохранить значения, используйте оператор Preserve:
Максимальный размер
Размер массива – это произведение длин всех его измерений. Он представляет собой общее количество элементов, содержащихся в данный момент в массиве.
По информации с сайта разработчиков, максимальный размер массивов зависит от операционной системы и доступного объема памяти. Использование массивов, размер которых превышает объем доступной оперативной памяти компьютера, приводит к снижению скорости, поскольку системе необходимо выполнять запись данных и чтение с диска.
Использование массивов
Приведу два примера, где не обойтись без массивов.
1. Как известно, функция Split возвращает одномерный массив подстрок, извлеченных из первоначальной строки с разделителями. Эти данные присваиваются заранее объявленному строковому (As String) одномерному динамическому массиву. Размерность устанавливается автоматически в зависимости от количества подстрок.
2. Данные в массивах обрабатываются значительно быстрее, чем в ячейках рабочего листа. Построчную обработку информации в таблице Excel можно наблюдать визуально по мерцаниям экрана, если его обновление (Application.ScreenUpdating) не отключено. Чтобы ускорить работу кода, можно значения из диапазона ячеек предварительно загрузить в динамический массив с помощью оператора присваивания (=). Размерность массива установится автоматически. После обработки данных в массиве кодом VBA полученные результаты выгружаются обратно на рабочий лист Excel. Обратите внимание, что загрузить значения в диапазон ячеек рабочего листа через оператор присваивания (=) можно только из двумерного массива.
Функции Array, LBound, UBound
Функция Array
Функция Array возвращает массив элементов типа Variant из первоначального списка элементов, перечисленных через запятую. Нумерация элементов в массиве начинается с нуля. Обратиться к элементу массива можно, указав в скобках его номер (индекс).