quick vba question

  • Thread starter Thread starter tmb
  • Start date Start date
T

tmb

How do I do this...

RecLen1 = 5
RecLen2 = 10
RecLen3 = 8

For i = 1 to 3
MsgBox RecLen & i '<--- no workie
Next i

I want RecLen and "i" put together like RecLen1 or RecLen2 or RecLen3

thanks for any help on this...
 
"RecLen" is text and is not a variable as you have written your code. Do
this:
MsgBox "RecLen" & i '<--- no workie
HTH Otto
 
I abriveated the code for posting... real code looks more like this...

Const RecLen1 As long = 5
Const RecLen2 As long = 10
Const RecLen3 As long = 8

For i = 1 to 3
MsgBox RecLen & i '<--- this doesn't work
Next i

What I want MsgBox to display is the contents of RecLen1 or 2 or 3... In
other words to display 5 or 10 or 8

of course, MsgBox is only used here to verify that I have the code right and
is not an actual part of the application.

thanks for any additional help.
 
Dim Reclen, i

Reclen = Array(5, 10, 8)
For i = 1 To 3
MsgBox Reclen(i - 1)
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
That works Bob. Thanks!

I guess there is no way to concatenate a variable for a loop... so thats why
they gave us Array's

tmb
 
I concatenate a variable for a loop many times. Just put the text part of
the variable in quotes so that Excel knows that is text and not another
variable. HTH Otto
 
Oops, I was wrong on that. Otto
Otto Moehrbach said:
I concatenate a variable for a loop many times. Just put the text part of
the variable in quotes so that Excel knows that is text and not another
variable. HTH Otto
Click to expand...
 
Send me an example Otto.


Otto Moehrbach said:
I concatenate a variable for a loop many times. Just put the text part of
the variable in quotes so that Excel knows that is text and not another
variable. HTH Otto
Click to expand...
 
Does something like this look like what you want?


For i = 1 To 4
MsgBox ("RecLen" & Trim(Str$(i)))
Next i
 
Just to add. Excel can not "evaluate" its own vba variables. However, it
does look at workbook names. This is the only "workaround" that I know of
if you wanted to go this route. Not the best, as the other examples are
probably better. Just handy for certain complicated evaluations:

Sub TestIt()
Dim p
ActiveWorkbook.Names.Add "RecLen1", 5
ActiveWorkbook.Names.Add "RecLen2", 10
ActiveWorkbook.Names.Add "RecLen3", 8

For p = 1 To 3
MsgBox Evaluate("RecLen" & p)
Next p
End Sub


Not sure, but you may be interested in making small arrays 1-based:

Sub TestIt2()
Dim v, p

' 0 Based
v = Array(5, 10, 8)
With WorksheetFunction
' 1 Based
v = .Transpose(.Transpose(v))
End With

For p = 1 To UBound(v)
Debug.Print v(p)
Next p
End Sub

or even just adding a dummy variable to the zero index.
v = Array("n/a", 5, 10, 8)
 
No, because he wants to get the value of the variable RecLenn, not just
concatenate a string into the variable name.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
You can convert strings to doubles, integers to strings, etc. But there is
no way to convert a string to a VariableName in any of the Microsoft
languages that I have seen. Sort of upstream and downstream of the
compiler. VariableNames are upstream of the compiler and execution
(concatenating the name) is downstream.
 
I know, and I think the OP knows that now, but that is what he wanted which
is why your suggestion is off-track.

Bob
 
Back
Top