Referring to a sequentially created variable

[JGalletta]

I've declared the following:

Dim C(12) As String

I have a loop that takes the number part of a control name using:

Right(controlname, Len(controlname) - 1)

(Controls are named with a single letter followed by sequential numbers.)

How can I refer to the corresponding variable from within this loop?

For example when the control loops through control G10, I'd like to refer to C10.

I'm having trouble building the variable reference:
I know this would be possible by creating another variable to refer to a string created by concatenating C and the Number like this:

Dim CandNumber As String
CandNumber = "C" & Right(controlname, Len(controlname) - 1)
'This will build the control name, but how can I reference its contents?

I would like to skip this silliness completely and do it the right way - how is this accomplished? I trust you can understand what I'm trying to get at.. I just don't exactly know how to describe it clearly.

 

[tehNellie]

dim C(12) as string 

msgbox = C(Right(controlname, Len(controlname) - 1))

You could stick the number in an integer variable first if you want to.

 dim i as integer
 dim C(12) as string 

i = Right(ControlName, Len(ControlName) - 1)
msgbox c(i)

 

[tehNellie]

dim c(12) actually gives you a 13 element array from 0 to 12 so the subtraction of the number of your control might not be necessary

 

[JGalletta]

This is actually exactly what I've been doing and

MsgBox C(1)

Shows a blank message box.

C1 is declared and contains a value.

However

MsgBox C1

Works fine.

 

[JGalletta]

Dim C(12) As String
C0 = "0"
C1 = "1"
C2 = "2"
MsgBox C(1)

This returns a blank msgbox - try it??

 

[tehNellie]

it will do

c(1) <> C1

You're not using option explicit so VBA is creating those variables on the fly and your data is now in a variant type Variable called C0 rather than in element 0 of the array c().

Add

"OPTION EXPLICIT" under Option Compare Database in your modules and run it again, see what happens.

To have it set by default go to Tools, options, editor and check the box require variable declaration in the VBA editor.

 

[JGalletta]

No, is that the problem? It's using Option Compare Database.

 

[JGalletta]

OK, that sounds good. Do I need to use parenthesis when setting the variable's value?

Like c(1) = "tblName.FieldName"

 

[tehNellie]

The problem is that without Option Explicit VBA is interpreting your typo as a need for new variables called C0, C1 etc etc

You meant to type

c(1) = "1"

msgbox c(1)

But you've typed:

c1 = "1"

msgbox c(1)

hence c(1) is empty because you've put the value in a new variable called C1 instead.

If you add option explicit when you try to run the code again you'll get a compile error with c0 highlighted and the message "Variable not declared", because it isnt.