Using a string as part of a variable name

[cryolokk]

I have a series of complex variable names (which can't simply be changed to 1,2,3,4 etc.) in the following form:

G0A1
G0A2
G0A3

G1A1
G1A2
G1A3

and so on,
(roughly 200 variables in each of 8 different worksheets of similar format).

All defined as:

Dim G0A1 as single
etc.

In my VBA code I calculate all of these variables, and now want to write the results to a worksheet. Currently I am just using the basic:

Dim j as integer
j=1
for j=1 to 50
sheets("sheet1").cells(1,j)=G0A1
sheets("sheet1").cells(2,j)=G0A2
etc.
next j

however I was wondering if it is possible to use a for loop and a string attachment to save me from typing all of these variables again. Below is basically what I'm looking for but can't seem to figure out how to implement.

Dim i as integer
Dim x as integer

x=1

For i=1 to 6
sheets("sheet1").cells(x,i)=G0A" & i
x=x+1
next i

The part in bold is where I'm trying to change the last character of the G0A1, G0A2,... as the variable i changes, but I can't figure out a way, and frankly I'm not even sure that it is possible.

Any ideas?

 

[PA HS Teacher]

I believe you want to use an array. Did you want something like this:

Dim GoA(1 To 200) As Single
For i = 1 To 200
  GoA(i) = Cells(i, 1).Value
Next j
MsgBox GoA(5)

 

[NateO]

Hello, welcome to the board.

I definitely agree on using an array versus having a ton of variables as such.

If you were to, you'd want your Variables in a class module. Taking the following:

http://groups.google.com/group/microsoft.public.excel.programming/msg/3a50ece883c60f4a

And tweaking it a bit (missing the Set Statement?):

Class Module Code:

Public G1 As Long, G2 As Long, G3 As Long
Public G4 As Long, G5 As Long, G6 As Long
Public G7 As Long, G8 As Long, G9 As Long
Public G10 As Long

Normal Module Code:

Sub foo()
Dim i As Long
Dim myVars As CVars
Set myVars = New CVars
For i = 1 To 10
    'Write Var
    CallByName myVars, "G" & i, VbLet, i * 4
    'Read Var
    Debug.Print CallByName(myVars, "G" & i, VbGet);
Next
Debug.Print
End Sub

Having said that, if you can, use an array.


Edited by Nate: Er, whoops, I get it; he used New in his Public Declaration.

 

[cryolokk]

Thanks for the information, an array is exactly what I should have been using and now my declarations section is considerably (199 lines) shorter, and you saved me a good amount of typing.

I used the code posted by PA HS Teacher, with minor modifications.

Thanks