PDA

View Full Version : Solved: Bulk assignment to VBA arrays



nameuser321
03-13-2012, 07:11 AM
Having some trouble bulk assigning values to an array. I know that you can assign values to an array individually, but what I would really like is to assign all values in one statement like in other programming languages.

Here is my code. Any assistance would be greatly appreciated.


Dim aArray(3) As Integer
Dim index As Integer
aArray = {1, 2, 3}

For index = LBound(aArray) To UBound(aArray)
MsgBox aArray(index)
Next


btw. this is my first post and i'm not a 100 percent sure if i'm in the right section. sorry if i'm not....

mancubus
03-13-2012, 07:41 AM
hi.
try:


Dim aArray
Dim index As Integer

aArray = Array(1, 2, 3)

For index = LBound(aArray) To UBound(aArray)
MsgBox aArray(index)
Next



or

Dim aArray(2) As Integer
Dim index As Integer

aArray(0) = 1
aArray(1) = 2
aArray(2) = 3

For index = LBound(aArray) To UBound(aArray)
MsgBox aArray(index)
Next



or

Dim aArray(2) As Integer
Dim index As Integer

For index = 0 To 2
aArray(index) = index + 1
Next

For index = LBound(aArray) To UBound(aArray)
MsgBox aArray(index)
Next



or

Dim aArray(1 To 3) As Integer
Dim index As Integer

For index = 1 To 3
aArray(index) = index
Next

For index = LBound(aArray) To UBound(aArray)
MsgBox aArray(index)
Next

nameuser321
03-13-2012, 08:00 AM
Thank you for your quick reply. Unfortunately what I really needed, but failed to explain, was bulk assignment of an multidimensional array.

I have code like this.


Dim stringArray(0 To 5, 0 To 1) As String

stringArray(0, 0) = "C2"
stringArray(0, 1) = "14"
stringArray(1, 0) = "C19"
stringArray(1, 1) = "3"
stringArray(2, 0) = "F2"
stringArray(2, 1) = "12"
stringArray(3, 0) = "F17"
stringArray(3, 1) = "2"
stringArray(4, 0) = "C25"
stringArray(4, 1) = "6"
stringArray(5, 0) = "C34"
stringArray(5, 1) = "4"


Is there an easier way to assign values that have no pattern, quickly into an array. I think your use of variant type, along with your array(1, 2...) may do the trick in simplifying this assignment, but i would really like to have this type of the bracket bulk type of assignment. Is there a way?

Bob Phillips
03-13-2012, 08:21 AM
Dim stringArray() As Variant

stringArray = [{"C2",14;"C19",3; "F2",12;"F17",2; "C25", 6;"C34", 4}]

nameuser321
03-14-2012, 11:32 AM
Great! Thanks xld. This does exactly what I needed. Now I just have two follow up questions. Why wont it let me write...

Dim stringArray() As String
stringArray = [{"C2","14";"C19","3"; "F2","12";"F17","2"; "C25", "6";"C34", "4"}]

and why is this array base 1 even when choose option base 0?

nameuser321
03-14-2012, 11:33 AM
the variant array you wrote.

Bob Phillips
03-14-2012, 02:29 PM
You need to declare it as type variant, VBA will sub-type it.

It is always base 1 because you are effectively doing a Range dump into an array, and this is always base 1, regardless of any option.

nameuser321
03-15-2012, 07:01 AM
Oh ok xld. Thanks for the help!