vba dynamic value assignment

Hi,

I have a combobox. Which has 4 values
North, West, South, East.

In a macro I want to get other values based on the selection.
for example i have variables for flow.
It is named as Flow_N, Flow_W, Flow_S, Flow_E.

i want to assign the flow value to a temporary variable based on the selection.

i did like this before for ole objects. but i dont know how to do it for variables.
ActiveSheet.OLEObjects("Approach" & k).Object.Value


Can anyone help me?

Thanks

Maybe this? It's unclear what you want to store in the variables, a string? boolean?

Please Login or Register  to view this content.

Your code is fine.
But do you have any other easy solution like the example i have given for ole objects.
because i have 15 scenarios and 4 approaches.
If i write your code i have to write 60 scenarios.
I don't want that.

It's very difficult to visualize what you are trying to do. You need to explain more, what do you want to do with the variables? As for your example of OLE objects, it seems to me you are looping 'k' with numbers.

Your initial question was, how to set a value into a variable based on the selection in the combo box.

You need to explain now what are these "scenarios" or "approaches", or even better, post a sample workbook (with sensitive information removed) to explain what you want to achieve.

'If I_Count > 0 Then
'
' If t <= I_End(1) And t >= I_Start(1) Then
'
' Flow_N(i) = Static_Input_Flow_Per_Lane
'
'
' If Down_block = 1 And Up_block = 0 Then
' 'Scenario = 1
' Speed_N(t, i) = Speed_N(t, i) * 5 / 6
' Flow_N(i) = Flow_N(i) * 1
' Cells(20, 14) = Flow_N(i)
'
' ElseIf Down_block = 0 And Up_block = 1 Then
' 'Scenario = 2
' Speed_N(t, i) = Speed_N(t, i) * 2 / 3
' Flow_N(i) = Flow_N(i) * 3 / 2
' Cells(20, 14) = Flow_N(i)
'
' ElseIf Down_block = 1 And Up_block = 1 Then
' 'Scenario = 3
' Speed_N(t, i) = Speed_N(t, i) * 2 / 3
' Flow_N(i) = Flow_N(i) * 3 / 2
' Cells(20, 14) = Flow_N(i)


As You can see above,
this is for for approach. That is for North.
It has has 15 scenarios.

I need to write the same for West, South and East.
I can write it and use case or if/else and run.

What I exactly want to do is, with the same code I want to run all.
for example
If North is selected,

"Flow_" & Incident_Location.Value & "(" & i & ")" = Static_Input_Flow_Per_Lane
similarly for speed
"Speed_" & Incident_Location.Value & "(t,i)"= "Speed_" & Incident_Location.Value & "(t,i)" * 2/3

Where Incident_Location.Value is value from the combobox selected.

Since you are using arrays, maybe add another dimension to it to represent the approach?

Please Login or Register  to view this content.

In Speed_N, the N represent North. Its like Spped_N, Speed_W, Speed_S and Speed_E.
I cannot change the array as it is used in many other places.

Is there any other way to do it?

Somthing like this

"Speed_" & Left(Incident_Location.Value,1) & "(t, i)" = "Something"

I looked up a little, and found this:

The simple answer is, No.

You will have to change your variables into an array or a collection for this to work. Or hidden named ranges in Excel, or properties of an object, or text files... lots of alternatives, except the simplest one that you want.

I'm afraid that's not possible with variable names. Or maybe someone else can shed some light on this.

Is it possible for me to get the address of array?

That is where Speed_N is stored?

like pointers in C.

As far as I know, it's not possible. Maybe some hardcore programming wizard might be able to help though. Anyway, good luck with that.

Can't you just use a collection?

What is collection? Can u explain?