Populating a multidimensional array 4

[redapples]

Access gives the following example for populating an array in VBA.

Dim MyWeek, MyDay
MyWeek = Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun&quot
' Return values assume lower bound set to 1 (using Option Base
' statement).
MyDay = MyWeek(2) ' MyDay contains "Tue".
MyDay = MyWeek(4) ' MyDay contains "Thu".


In VB.Net you can use the following

Dim rectangulararray as Integer(,) = {{0,1,2},{3,4,5},{6,7,8},{9,10,11}}


Is it possible in VBA to populate an multidimensional (in this case 2D) array in a similar fashion?

I am not looking for a loop statement as I have fixed values that I need to calculate relative locations of postcodes. I am attempting to Hard code in the values but this might not be the best way. Would storing them in a table and populating the array from there be better??

any help welcome.

Redapples

Want the best answers? See FAQ181-2886

 

[hwkranger]

Never really thought about this, so I tried it, yes you can.

Public Function test()
Dim myarry(3, 5) As Integer
myarry(1, 1) = 7
myarry(1, 2) = 9
myarry(2, 1) = 15
myarry(2, 2) = 18
MsgBox (myarry(1, 1) & " " & myarry(1, 2) & " " & myarry(2, 1) & " " & myarry(2, 2))

End Function

I wasn't sure if multi dimensional arrays were supported, but using a little bit of intuition i gave it a peck and it worked out. Hope this helps!

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[hwkranger]

you know, after I posted my post I realized I answered the entirely the wrong question. You wanted to know if you could Populate the entire array with a single initialization.

yeah, I don't know the answer to that - honestly I didn't know that vba even supported multi-dimensional arrays.

Sorry about my post. But if I were to guess, you'll probalby have to end up using a table to populate your array.

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[redapples]

Thanks, yes I was asking if you can populate it in one go or line by line at best. hopefully I will get an answer. Am considering a .net solution in any case. Would a hard coded array that is initalized when needed use less memory than a table that is always there? any one?

Want the best answers? See FAQ181-2886

 

[CajunCenturion]

Yes, you can make such as assignment. The following should work, but only if TestArray is declared as a Variant.

Dim TestArray

TestArray = Array(Array(0, 1, 2), Array(3, 4, 5), Array(6, 7, 8), Array(9, 10, 11))

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[redapples]

Excellent an array of arrays. will let you know how I get on with that approach.

Want the best answers? See FAQ181-2886

 

[redapples]

CajunCenturion, this method works but has a peculiar calling method for members of this array. Rather than the standard 2D array which would have

Debug.Print TestArray(0,2)

the access to the members are gained by using

Debug.Print TestArray(0)(2)

still and all it does work.
Next task to determine which aproach will be the least heavy. I'll let you know.

Oh and have a star.

Redapples


Want the best answers? See FAQ181-2886

 

[CajunCenturion]

That's interesting, and worth looking into.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[redapples]

Had a look at this, in terms of wieght of the application the difference is neglible about 20KB in terms of speed, not too sure how to measure this except by timing it. One thing though reading a table into an array is eaiser in VBA as it makes a true 2D array rather than an array of array which is clumsy, difficult to iterate through and potentially slower. So have plumbed for the table option but might choose not to do this with a VB.net version if I make that step up.

Want the best answers? See FAQ181-2886

 

[CajunCenturion]

To confuse the issue, you might want to look at the following:

Dim TheArray() As Variant
TheArray = BuildArray("1,2,3", "1,2,3", "1,2,3,4", "1,2,3,6,8", "4,5,6")

and the BuildArray function is as follows:

Function BuildArray(ParamArray ArrayData() As Variant) As Variant

   Dim TheArray() As Variant
   Dim Idx1       As Integer
   Dim Idx2       As Integer
   Dim RowSub     As Integer
   Dim ColSub     As Integer
   Dim TheCols()  As String
   Dim AString    As String
   
   ColSub = -1
   RowSub = UBound(ArrayData)
   For Idx1 = 0 To RowSub
      AString = ArrayData(Idx1)
      TheCols = Split(AString, ",")
      If (UBound(TheCols) > ColSub) Then
         ColSub = UBound(TheCols)
      End If
   Next Idx1
   
   ReDim TheArray(RowSub, ColSub)

   For Idx1 = 0 To RowSub
      AString = ArrayData(Idx1)
      TheCols = Split(AString, ",")
      For Idx2 = 0 To UBound(TheCols)
         TheArray(Idx1, Idx2) = TheCols(Idx2)
      Next Idx2
   Next Idx1
   
   BuildArray = TheArray
   
End Function

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[MichaelRed]

It IS interesting. I can't quite get my head into how I might pratically use it, as I haven't seen information structured like the input args. Worth a star for sheer inventiveness.






MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[redapples]

Cajun, thanks for this, I have now done tests on this additional method and similiary the file size is still only about 20kb difference but now the array hard coded works and runs over 2 seconds faster than the method which populates the array by Reading table data to it. Fantastic stuff, thank you very much.

Redapples

Want the best answers? See FAQ181-2886

 

[CajunCenturion]

That's great news, and it was fun to put together.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein