By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,129 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

Use VBA to create required number of variables

P: 92
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
Dec 27 '08 #1
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,624
@NDayave
Don't think that this can be done, but what about a Dynamic Array to store the values, something like:
Expand|Select|Wrap|Line Numbers
  1. Dim lngCounter As Long
  2. Const conNUM_OF_RACES As Long = 4
  3.  
  4. Dim alngRaces() As Long
  5.  
  6. ReDim alngRaces(1 To 4)
  7.  
  8. For lngCounter = 1 To conNUM_OF_RACES
  9.   alngRaces(lngCounter) = lngCounter ^ 3
  10. Next
  11.  
  12. 'Playback the elements in the Array
  13. For lngCounter = LBound(alngRaces) To UBound(alngRaces)
  14.   Debug.Print alngRaces(lngCounter)
  15. Next
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. 27 
  2. 64
Dec 27 '08 #2

P: 92
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
Dec 27 '08 #3

ADezii
Expert 5K+
P: 8,624
@NDayave
You are quite welcome.
Dec 28 '08 #4

nico5038
Expert 2.5K+
P: 3,072
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)
Dec 28 '08 #5

ADezii
Expert 5K+
P: 8,624
@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?
Dec 29 '08 #6

nico5038
Expert 2.5K+
P: 3,072
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)
Dec 29 '08 #7

ADezii
Expert 5K+
P: 8,624
@nico5038
Thanks for the explanation Nico, always a learning experience around here (LOL).
Dec 29 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.