Use VBA to create required number of variables

[NDayave]

How do,

I want to be able to make a certain number of variables depending on the number of data items i have to be used.

For example, i would need 3 variables defined when i have 3 numbers and 5 variables when i have 5 numbers.

Is there any way in the VBA that I can define variables with variables in the name (ie: Dim Number & x as Double within a For Next loop). I can do this with the text boxes on the form, so I was wondering if I could do it with defining variables.

I want to do it this way as the numbers are the overall Lap Time in a race and need to be assigned points (eg: 1st = 5 points 2nd = 3 points etc) and there is the possibility of any number of Race Times to be compared.

Obviously if you have any other ideas they are much appreciated.

Thanks in advance,

NDayave

[ADezii]

@NDayave
Don't think that this can be done, but what about a Dynamic Array to store the values, something like:

Dim lngCounter As Long
Const conNUM_OF_RACES As Long = 4
 
Dim alngRaces() As Long
 
ReDim alngRaces(1 To 4)
 
For lngCounter = 1 To conNUM_OF_RACES
  alngRaces(lngCounter) = lngCounter ^ 3
Next
 
'Playback the elements in the Array
For lngCounter = LBound(alngRaces) To UBound(alngRaces)
  Debug.Print alngRaces(lngCounter)
Next

OUTPUT:

1 
8 
27 
64

[NDayave]

Thanks for the input,

This appears to do what I need, the ReDim statement is what I was after.

These kind of things are impossible to google.

Thanks again,

NDayave

[ADezii]

@NDayave
You are quite welcome.

[nico5038]

ReDim is a rather "costly" statement.
An alternative to using arrays is to switch to using collections. These don't require a ReDim and can be processed using a For Next loop.

Nic;o)

[ADezii]

@nico5038
Hello Nico, just for curiosity, are you saying that it would be faster to dynamically Add/Remove Items from a Collection as opposed to dynamically Resizing an Array?

[nico5038]

The ReDim is "costly" because memory has to be re-allocated. A Collection holds coded methods to add/remove items and the FOR NEXT will make processing easy.
Saw a nice piece of loop time testing by you in the "Insights" section, perhaps worthwhile to re-use <LOL>

Nic;o)

[ADezii]

@nico5038
Thanks for the explanation Nico, always a learning experience around here (LOL).