Get length of array?

excel vba ms-access
lisovaccaro picture lisovaccaro · Jun 1, 2015 · Viewed 271.2k times · Source

I'm trying to get the length of an array, yet I keep getting this error:

Object required

Am I doing something wrong?

Dim columns As Variant
columns = Array( _
"A", "ID", _
"D", "Name")
Debug.Print columns.Length  ' Error: Object required

Answer

AnalystCave.com picture AnalystCave.com · Jun 1, 2015

Length of an array:

UBound(columns)-LBound(columns)+1

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.

Related questions

Error: "Could Not Find Installable ISAM"

I've written some VBA code in an Excel workbook to retrieve data from an Access database in the same directory on a desktop. It works fine on my machine and several other machines running Windows XP, but when we tested …

Read More
Export tables to an excel spreadsheet in same directory

I have two tables in my access database that I want to be able to export to excel. I can do it by opening the table and then doing File->Export... and then choosing the format and typing in …

Read More
Using Excel as front end to Access database (with VBA)

I am building a small application for a friend and they'd like to be able to use Excel as the front end. (the UI will basically be userforms in Excel). They have a bunch of data in Excel that they …

Read More