Thread: arrays in VBA

The quickest easiest way to populate an array from a sheet is

Code:

Dim vArr As Variant
vArr = Range("a1:a10")

change rows & columns to suit
this gives a 1 dimension array

If you need a 2 dimenstion array with the 2nd dimention blank have your data in column A & column B blank

Code:

vArr = Range("a1:b10")

jindon

Thanks for pointing out my error that my code gives a 1 dimension array which is incorrect.


This line of your code will cause incorrect

Code:

a = Range("a1" Range("a" & Rows.Count).End(xlUp)).Value

It should be

Code:

a = Range("a1", Range("a" & Rows.Count).End(xlUp)).Value

Some links on arrays

http://puremis.net/excel/code/053.shtml

http://www.dailydoseofexcel.com/arch...nge-using-vba/

http://www.exceltip.com/st/Array_var...Excel/509.html

Variant array to hold two arrays. Step throught this sample subroutine with the immediate window open

Code:

Sub Test() 
  Dim X As Long 
  Dim Arr1 As Long 
  Dim Arr2 As Long 
  Dim VarArray As Variant 
  '    Array function arrays are zero based 
  '    unless Option Base 1 is used. 
  Arr1 = 0 
  Arr2 = 1 
  '    We use VarArray to hold two elements, 
  '    each of which is an array. 
  VarArray = Array(Array("A", "B"), Array("C", "D", "E", "F")) 
  '    Note the syntax to address each array... a double pair of 
  '    parentheses... the first set address which array member of 
  '    VarArray we want, the second set addresses the element 
  '    of the member array we want. 
  Debug.Print "******** Arr1 member elements ********" 
  For X = LBound(VarArray(Arr1)) To UBound(VarArray(Arr1)) 
    Debug.Print VarArray(Arr1)(X) 
  Next 
  Debug.Print "******** Arr2 member elements ********" 
  For X = LBound(VarArray(Arr2)) To UBound(VarArray(Arr2)) 
    Debug.Print VarArray(Arr2)(X) 
  Next 
  Debug.Print "******** DONE ********" 
End Sub

VBA Noob

To qualify Jindon's observation that "when you convert range value to vb array, it will be always 1 based 2D array, no exception", it must however be noted that when range transposition is involved, you can sort of convert range values to a 1D Array as the following codes illustrate.

1. The simple transposition of one-column range:

Code:

Sub TestForDimensionsOfVariantArray1()
Dim v As Variant

'populate A1:A5
For i = 1 To 5
Cells(i, 1).Value = Randbetween(1, 10)
Next

v = Range("a1:a5")
v = Application.Transpose(Range("a1:a5"))
MsgBox "Test for v dimensions"

MsgBox "Test for v dimensions"
MsgBox LBound(v, 1) 'ans=1
MsgBox UBound(v, 1) 'ans=5
MsgBox LBound(v, 2) 'ans="Subscript out 0f range error"
MsgBox UBound(v, 2) 'ans="Subscript out 0f range error"
'Conclusion: Variant array is 1-D
'of size v(1 To 5)
End Sub

2. The double transposition scenarios

Code:

Sub TestForDimensionsOfVariantArray2()
Dim v As Variant, v2 As Variant, v3 As Variant


'populate A1:E5
For i = 1 To 5
Cells(1, i) = Randbetween(2, 10)
Next

'CASE #1
v = Range("a1:e1")
MsgBox "Test for v dimensions"
MsgBox LBound(v, 1) 'ans=1
MsgBox UBound(v, 1) 'ans=1
MsgBox LBound(v, 2) 'ans=1
MsgBox UBound(v, 2) 'ans=5
'conclusion: Variant array is 2-D
'of size v(1 To 1, 1 To 5)


'CASE #2
v2 = Application.Transpose(Range("a1:e1"))
MsgBox "Test for v2 dimensions"
MsgBox LBound(v2, 1) 'ans=1
MsgBox UBound(v2, 1) 'ans=5
MsgBox LBound(v2, 2) 'ans=1
MsgBox UBound(v2, 2) 'ans=1
'Conclusion: Variant array is 2-D
'of size v(1 To 5, 1 To 1)

'CASE #3
v3 = Application.Transpose(Application.Transpose(Range("a1:e1")))
MsgBox "Test for v3 dimensions"
MsgBox LBound(v3, 1) 'ans=1
MsgBox UBound(v3, 1) 'ans=5
MsgBox LBound(v3, 2) 'ans="Subscript out 0f range error"
MsgBox UBound(v3, 2) 'ans="Subscript out 0f range error"
'Conclusion: Variant array is 1-D'
of size v(1 To 5)

End Sub

There is no doubt that it is this property of Array dimension changing by transposition from one-column range (2D) to putatively a one-row range (ID) that gives the illusion that "horizontal single row range" gives rise to a 1-D Array.

Outside the context of transposition, Jindon is perfectly right. I was only a bit uneasy about the rider "no exception". Again, as he pointed out, Variant arrays are by definition typecast to Base1.