Dynamic Variables

L

Lawlera

How do you create a variable variable? Consider this code
dim x as strin
x = "Whatever
the character 'x' may be different (for later use) and needs to be dynamically defined in code from a cell value

TIA
 
R

Rob van Gelder

For what purpose? In most cases you wont need to do that.


It is possible. I know you can dynamically alter the code in a module using
VBComponent object. I have some examples on my website - but as I said, I
think it's unnecessary. Just tell us the reason behind it. Perhaps there's a
better way.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


Lawlera said:
How do you create a variable variable? Consider this code:
dim x as string
x = "Whatever"
the character 'x' may be different (for later use) and needs to be
Click to expand...
dynamically defined in code from a cell value.
 
L

Lawlera

Hi Rob

Just for the sake of it if I'm honest. The idea was born out of need to loop through 100 or so sheets and returning a value for each one after a series of calculations, hence the variable could have a letter and the sheets number added
 
F

Frank Kabel

Hi
for this use an array variable. e.g. try something like
the following:


Sub get_sheetnames()
Dim sname()
Dim wks_index
Dim wks_count
wks_count = ActiveWorkbook.Worksheets.Count
ReDim sname(1 To wks_count)

For wks_index = 1 To wks_count
sname(wks_index) = ActiveWorkbook.Worksheets
(wks_index).Name
Next

For wks_index = 1 To wks_count
MsgBox sname(wks_index)
Next
End Sub
-----Original Message-----
Hi Rob,

Just for the sake of it if I'm honest. The idea was born
Click to expand...
out of need to loop through 100 or so sheets and returning
a value for each one after a series of calculations, hence
the variable could have a letter and the sheets number
added.
 
R

Rob van Gelder

It sounds like you want a dynamic array.

Sub test()
Dim arr()
Dim i As Long

ReDim arr(100)

For i = 1 To 100
arr(i) = i * 10
Next

For i = 1 To 100
Debug.Print arr(i)
Next
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


Lawlera said:
Hi Rob,

Just for the sake of it if I'm honest. The idea was born out of need to
Click to expand...
loop through 100 or so sheets and returning a value for each one after a
series of calculations, hence the variable could have a letter and the
sheets number added.
 
Top