Use VBA to create required number of variables | Member | | Join Date: Feb 2007
Posts: 51
| | |
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
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,224
| | | re: Use VBA to create required number of variables
Quote:
Originally Posted by 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 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: | | Member | | Join Date: Feb 2007
Posts: 51
| | | re: Use VBA to create required number of variables
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
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,224
| | | re: Use VBA to create required number of variables
Quote:
Originally Posted by 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 You are quite welcome.
|  | Moderator | | Join Date: Nov 2006 Location: The Netherlands
Posts: 2,232
| | | re: Use VBA to create required number of variables
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)
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,224
| | | re: Use VBA to create required number of variables
Quote:
Originally Posted by 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) 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?
| | Moderator | | Join Date: Nov 2006 Location: The Netherlands
Posts: 2,232
| | | re: Use VBA to create required number of variables
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)
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,224
| | | re: Use VBA to create required number of variables
Quote:
Originally Posted by 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) Thanks for the explanation Nico, always a learning experience around here (LOL).
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,537 network members.
|
