Array Syntax

[dbay]

Hi,

I'm not sure if this is even possible, but I am trying to simply set these variables using an array and am having no luck. This syntax in the For Loop is not correct and I am not having any luck figuring it out today.

I have cut this code short for a demo.

Dim i as Integer
Dim ARR_List () As Variant

Dim INT_611_MIN As Integer
Dim INT_612_MIN As Integer
Dim INT_613_MIN As Integer
Dim INT_614_MIN As Integer
Dim INT_615_MIN As Integer

ARR_List = Array("611", "612", "613", "614", "615")

For i = 0 to 4

INT_" & ARR_List(i) & "_MIN = 10

next i

 

[jzwp22]

INT_" & ARR_List(i) & "_MIN

You cannot create the name of a variable in the manner you show.

Why bother assigning the value in an array to another variable, just reference the array value you need directly.

Perhaps a more detailed explanation of what you are trying to do with the code overall will help.

 

[DJkarl]

Hi,

I'm not sure if this is even possible, but I am trying to simply set these variables using an array and am having no luck. This syntax in the For Loop is not correct and I am not having any luck figuring it out today.

I have cut this code short for a demo.

Dim i as Integer
Dim ARR_List () As Variant

Dim INT_611_MIN As Integer
Dim INT_612_MIN As Integer
Dim INT_613_MIN As Integer
Dim INT_614_MIN As Integer
Dim INT_615_MIN As Integer

ARR_List = Array("611", "612", "613", "614", "615")

For i = 0 to 4

INT_" & ARR_List(i) & "_MIN = 10

next i

You cannot assign variables in this way, the better question might be why would you want to? You can store all the values in an integer array and just use that, or if you need to cross reference stings and integers look at using a collection or dictionary object.

 

[dbay]

You cannot assign variables in this way, the better question might be why would you want to? You can store all the values in an integer array and just use that, or if you need to cross reference stings and integers look at using a collection or dictionary object.

Maybe I simpified it too much....

Dim i as Integer
Dim ARR_List () As Variant

Dim INT_611_MIN As Integer
Dim INT_612_MIN As Integer
Dim INT_613_MIN As Integer
Dim INT_614_MIN As Integer
Dim INT_615_MIN As Integer

ARR_List = Array("611", "612", "613", "614", "615")

For i = 0 to 4

INT_" & ARR_List(i) & "_MIN = Nz(DMin("[VDA_Score]", "TBL_Scores", "[VDA_Index] = " & LNG_Index & " AND [VDA_Element] = '" & STR_VDA & "'"), 10)

next i

I wanted to do it with a For next Loop versus this way....

INT_611_MIN = Nz(DMin("[VDA_Score]", "TBL_Scores", "[VDA_Index] = " & LNG_Index & " AND [VDA_Element] = '" & STR_VDA & "'"), 10)

INT_612_MIN = Nz(DMin("[VDA_Score]", "TBL_Scores", "[VDA_Index] = " & LNG_Index & " AND [VDA_Element] = '" & STR_VDA & "'"), 10)

INT_613_MIN = Nz(DMin("[VDA_Score]", "TBL_Scores", "[VDA_Index] = " & LNG_Index & " AND [VDA_Element] = '" & STR_VDA & "'"), 10)

INT_614_MIN = Nz(DMin("[VDA_Score]", "TBL_Scores", "[VDA_Index] = " & LNG_Index & " AND [VDA_Element] = '" & STR_VDA & "'"), 10)

INT_615_MIN = Nz(DMin("[VDA_Score]", "TBL_Scores", "[VDA_Index] = " & LNG_Index & " AND [VDA_Element] = '" & STR_VDA & "'"), 10)

 

[vbaInet]

Create a two-dimensional array. Arrays can be multi-dimensional.

To help you in your conquest, have a look here:

http://www.cpearson.com/excel/vbaarrays.htm

 

[tehNellie]

I think again that the suggestion remains that rather than use an array to try and build dynamic variables, which I don't believe that you can, why not just hold whatever it is you're trying to put in those variables directly in a multidimensional array?

  dim arrList(4,2) 

For i = 0 to 4

 arrlist(i,1) = LNG_Index 
 arrlist(i,2) = Nz(DMin("[VDA_Score]", "TBL_Scores", "[VDA_Index] = " & LNG_Index & _ 
                " AND [VDA_Element] = '" & STR_VDA & "'"), 10)

LNG_Index = 'whatever you use to change that value'

next

First column has your index (assuming that's the 611 etc) the second element contains your values. One variable, no faffing around.

 

[dbay]

Thanks for the replys. I will look into multideminsional arrays and see if they can do what I need.