Import unknown sized array from worksheet into VBA

Question

Hi:

Assume one has a worksheet filled with data that one doesn't know the size of (i.e., rows and columns). How can you both import this into a VBA array for processing and also pull out the size (rows and columns)?

I see lots of examples regarding how to import from a worksheet between, say, A2 and D25. But what if there is contiguous data in the worksheet, and you wish to write VBA code to import it into a VBA array, and define NumRows and NumColumns from this import. Is there a way to do this without knowing the array size a priori?

Thank you very much!

Answer 1

For example, range A2 is one of the cells of your data.

You can put the data into a variable like this:

Dim tmp

Dim h, w

tmp = range("A2").currentregion.value

h = range("A1").currentregion.rows.count

w =range("A1").currentregion.columns.count

h and w are height and width of the data.

another approach is to check the dimensions of the variable:

h = ubound(tmp,1)

w = ubound(tmp,2)

To read the data from the variable, you can do this:

a = tmp(1,1)

In the above line, a will be data in the 1st row, 1st column.

Regards,

Edwin Tam

Excel Power Expander - http://www.vonixx.com

Answer 2

Thanks much for the very detailed answer!

However, it is likely that I did not make myself clear enough; apologies. Basically, I'm not looking to primarily redimension an array, but rather to import arbitrary (arbitrary is the sense that a worksheet will be filled with data continuously from Cell A1 in two dimensions to another cell, with the coordinates of that final cell being unknown a priori) data from a worksheet into an array in VBA. If this is possible, I would like to also know the # of rows and the # of columns once the import has been completed. Does this make sense?

Again, thank you for your help and time!

Answer 3

Multi-demensional arrays supported in VBA (version 6.5) only recognize fixed dimensions meaning for example an array predetermined to be 3 x 3 or 8 x 10 in size. There exists a VBA command called ReDim that will dynamically alter the size of a declared array, but it will only do so for the last outer dimension. Thus for an array of 8 (rows) x 10 (columns) in size, the ReDim statement can alter that array to a size of let's say 8 x 11 or 8 x 20 but it cannot alter the number of rows the array contains.

This makes working with dynamic arrays in VBA rather difficult.

I came across a very elegant solution to this VBA (version 6.5) limitation posted on StackOverflow by a gentlemen named IIya Kurnosov and his sample code, posted below, works quite nicely.  

Hope it helps

Option Explicit

Public Sub TestMatrixResize()

Const MAX_D1 As Long = 2

Const MAX_D2 As Long = 3

Dim arr() As Variant

InitMatrix arr, MAX_D1, MAX_D2

PrintMatrix "Original array:", arr

ResizeMatrix arr, MAX_D1 + 1, MAX_D2 + 1

PrintMatrix "Resized array:", arr

End Sub

Private Sub InitMatrix(a() As Variant, n As Long, m As Long)

Dim i As Long, j As Long

Dim StringArray() As String

ReDim a(n)

For i = 0 To n

ReDim StringArray(m)

For j = 0 To m

StringArray(j) = i * (m + 1) + j

Next j

a(i) = StringArray

Next i

End Sub

Private Sub PrintMatrix(heading As String, a() As Variant)

Dim i As Long, j As Long

Dim s As String

Debug.Print heading

For i = 0 To UBound(a)

s = ""

For j = 0 To UBound(a(i))

s = s & a(i)(j) & "; "

Next j

Debug.Print s

Next i

End Sub

Private Sub ResizeMatrix(a() As Variant, n As Long, m As Long)

Dim i As Long

Dim StringArray() As String

ReDim Preserve a(n)

For i = 0 To n - 1

StringArray = a(i)

ReDim Preserve StringArray(m)

a(i) = StringArray

Next i

ReDim StringArray(m)

a(n) = StringArray

End Sub