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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.