2 D array assignment

[JKwan]

if I have a variant call ary


ary = Array("a","b","c","d")


ary will be a 1 D array

how do I assign ary as a 2 D array, so that I will get ary(0,0)=a, ary(0,1)=b, ary(1,0)=c and ary(1,1)=d?

[Aflatoon]

If you want it 0 based, you'd have to loop I think. If 1 based is OK, you can use:

ary = [{"a","b";"c","d"}]

[JKwan]

Thank you for the quick reply, your solution works, however, not in my case.
When I put in my proper content into the array, it complaints about the line being too long. But if I chop up the line using "_", it complaints missing "]"...

[Paul_Hossler]

these two ways work, but there is a 255 char line limit.

If you have that many elements, you might have to do it in multiple loads



Option Explicit
Sub Arrays()
Dim One As Variant, Two As Variant


One = Array("A1", "B1", "C1", "D1")
MsgBox LBound(One) & " -- " & UBound(One)
MsgBox One(2)
Two = Array(Array("A11", "A12"), Array("B11", "B12"), Array("B11", "B12"), Array("B11", "B12"))
MsgBox LBound(Two) & " -- " & UBound(Two)
MsgBox Two(2)(0) & " -- " & Two(2)(1)
End Sub

Sub Arrays_1()
Dim One As Variant, Two As Variant


One = Array("A1", "B1", "C1", "D1")
MsgBox LBound(One) & " -- " & UBound(One)
MsgBox One(2)
Two = [{"A11", "A22"; "B11", "B22"; "C11", "C22"; "D11", "D22"}]
MsgBox LBound(Two, 1) & " -- " & UBound(Two, 1)
MsgBox LBound(Two, 2) & " -- " & UBound(Two, 2)
MsgBox Two(2, 1) & " -- " & Two(2, 2)
End Sub

[JKwan]

Thanks Paul, I will see what I can do with your solution. I only have 4 x 2 array, however, it is the text that I am trying to load into the array. They are long text, I think I hit that 255 limit.

[Kenneth Hobs]

Sub Main()
Dim ary() As Variant, i As Long

ary() = Array("a", "b", "c", "d", "e", "f")
MsgBox Join(ary, vbLf)

ary() = vA1dto2d(ary, 1, 2, 1, 2)
Show2DArray ary()

ary() = Array("a", "b", "c", "d", "e", "f")
ary() = vA1dto2d(ary, 1, 2, 1, 3)
Show2DArray ary()

ary() = Array("a", "b", "c", "d", "e", "f")
ary() = vA1dto2d(ary, 1, 3, 1, 2)
Show2DArray ary()
End Sub


Function vA1dto2d(a1d() As Variant, dimColl As Long, dimColU As Long, _
dimRowl As Long, dimRowU As Long) As Variant
Dim i As Long, j As Long, z As Long, a() As Variant

On Error Resume Next
z = LBound(a1d)
ReDim a(dimColl To dimColU, dimRowl To dimRowU)
For j = dimRowl To dimRowU
For i = dimColl To dimColU
a(i, j) = a1d(z)
z = z + 1
Next i
Next j

vA1dto2d = a()
End Function


'similar to, http://www.cpearson.com/excel/vbaarrays.htmhttp://www.cpearson.com/excel/vbaarrays.htm
Public Sub Show2DArray(ByRef myArry() As Variant) '<-- Note ByRef modifier.
Dim x As Long
Dim y As Long
Dim s As String


s = ""
For y = LBound(myArry, 2) To UBound(myArry, 2)
For x = LBound(myArry, 1) To UBound(myArry, 1)
s = s & myArry(x, y) & ", "
Next x
If Mid(s, Len(s) - 1, 1) = "," Then s = Left(s, Len(s) - 2)
s = s & vbNewLine
Next y
MsgBox s
End Sub

[snb]

Sub M_snb()
sn = Array("aa", "bb", "cc", "dd")

With CreateObject("scripting.dictionary")
.Item(1) = Array(sn(0), sn(1))
.Item(2) = Array(sn(2), sn(3))
sp = Application.Index(.items, 0, 0)
End With

MsgBox UBound(sp) & vbTab & UBound(sp, 2) & vbLf & sp(1, 1) & vbTab & sp(1, 2) & vbLf & sp(2, 1) & vbTab & sp(2, 2)
End Sub

[SamT]

Dim txt1 As String
txt1 = "255 character string"
txt1 = txt1 & "another 255 character String" 'IIRC, up to 32k characters
ary(0,0) = txt1

The above works because txt1 "is" a pointer to the memory that holds the very long string

[Paul_Hossler]

@JKwan --


however, it is the text that I am trying to load into the array. They are long text, I think I hit that 255 limit.

Using the [ ... ] notation is short hand for Application.Evaluate, i.e. like a worksheet formula (sort of). IIRC the 255 (actually I think it's 251) limit is a carry over from the worksheet side.

So you could put the extra long text into a variable, and put the variable into the array, BUT ...

I'd just Dim a 4x2 array and put the text into each element: A(1,1) = " asdfasdfasdfasdfasdsadasdf", A(1,2) = "qwerqwerqwerqwerqwerqwerqwrwer", or

A(1,1) = " asdfasdfasdfasdfasdsadasdf"
A(1,1) = A(1,1) & "qwerqwerqwerqwerqwerqwerqwrwer"

[Kenneth Hobs]

This is another example as I did before but uses Split() to easily build a one dimensional array from a concatenated delimited string.

Sub Main2()
Dim a() As Variant, a2() As Variant, i As Long
Dim s() As String, s1 As String, s2 As String, s3 As String
Dim s4 As String, s5 As String, s6 As String

s1 = Replace(Space(10), " ", "a") & ","
s2 = Replace(Space(10), " ", "b") & ","
s3 = Replace(Space(10), " ", "c") & ","
s4 = Replace(Space(10), " ", "d") & ","
s5 = Replace(Space(10), " ", "e") & ","
s6 = Replace(Space(10), " ", "f")
s() = Split(s1 & s2 & s3 & s4 & s5 & s6, ",")

a() = sA1dtovA1d(s)
MsgBox Join(a, vbLf)

a2() = vA1dto2d(a, 1, 2, 1, 2)
Show2DArray a2

a2() = vA1dto2d(a, 1, 2, 1, 3)
Show2DArray a2

a2() = vA1dto2d(a, 1, 3, 1, 2)
Show2DArray a2
End Sub


Function sA1dtovA1d(strArray() As String) As Variant
Dim varArray() As Variant, i As Long
ReDim varArray(LBound(strArray) To UBound(strArray))
For i = LBound(strArray) To UBound(strArray)
varArray(i) = CVar(strArray(i))
Next i
sA1dtovA1d = varArray()
End Function

Function vA1dto2d(a1d() As Variant, dimColl As Long, dimColU As Long, _
dimRowl As Long, dimRowU As Long) As Variant
Dim i As Long, j As Long, z As Long, a() As Variant

On Error Resume Next
z = LBound(a1d)
ReDim a(dimColl To dimColU, dimRowl To dimRowU)
For j = dimRowl To dimRowU
For i = dimColl To dimColU
a(i, j) = a1d(z)
z = z + 1
Next i
Next j

vA1dto2d = a()
End Function

'similar to, http://www.cpearson.com/excel/vbaarrays.htmhttp://www.cpearson.com/excel/vbaarrays.htm
Public Sub Show2DArray(ByRef myArry() As Variant) '<-- Note ByRef modifier.
Dim x As Long
Dim y As Long
Dim s As String

s = ""
For y = LBound(myArry, 2) To UBound(myArry, 2)
For x = LBound(myArry, 1) To UBound(myArry, 1)
s = s & myArry(x, y) & ", "
Next x
If Mid(s, Len(s) - 1, 1) = "," Then s = Left(s, Len(s) - 2)
s = s & vbNewLine
Next y
MsgBox s
End Sub

[snb]

@KH

to easily build a one dimensional array from a concatenated delimited string ;)


sn = Split(String(10, "a") & "," & String(10, "b") & "," & String(10, "c") & "," & String(10, "d") & "," & String(10, "e") & "," & String(10, "f") , ",")
sn = Split([rept("a",10)&","&rept("b",10)& ","&rept("c",10)&","&rept("d",10)&","&rept("e",10)& ","&rept("f",10)], ",")

Isn't sA1dtovA1d a little farfetched?


' dim a as variant
sn = Split([rept("a",10)&","&rept("b",10)& ","&rept("c",10)&","&rept("d",10)&","&rept("e",10)& ","&rept("f",10)], ",")
a=sn
msgbox join(a,vblf)

[JKwan]

Thank you all for the help. I will take a look at the code from snb and Kenneth. I used Paul's method to load into my array (posting 4). I don't know what to call it, but I don't think it is a 2 D array. The bottom line, got it to work nicely and cleanly.