PDA

View Full Version : Variable question



stranno
01-26-2012, 07:13 AM
Hi,

Sub Vari()
Dim A As Integer

'initialize
A = 1
Sheets(1).Cells(1, 1) = "A"

'output
MsgBox A
MsgBox Sheets(1).Cells(1, 1)

End Sub


How to manipulate MsgBox Sheets(1).Cells(1, 1) to become 1 as answer?

mikerickson
01-26-2012, 07:33 AM
I think this is the change that you are looking for
Sheets(1).Cells(1, 1) = A

As you've seen, "A" is interpreted as the literal string A.
A is interpreted as the value that you've assigned to the variable A

stranno
01-26-2012, 12:49 PM
Yes you're right. But take a look at the next code:

Sub vari()
Dim A3 As Integer
'initialize
A3 = 3
Sheets(1).Cells(1, 1) = "A" & 3
'output
MsgBox A3
MsgBox Sheets(1).Cells(1, 1)
End Sub


Isn't it possible to become 3 as an answer after msgbox sheets(1).cells(1,1)

mikerickson
01-26-2012, 01:10 PM
You cant' use concatenation to create a string that will be interpreted as a variable. (i.e. the
Sheets(1).Cells(1, 1) = "A" & 3 is putting the string "A3" in the cell)

To do somethign like that you would use an array

Sub vari()
Dim A3(1 To 10 As Integer
'initialize
A(3) = 3
Sheets(1).Cells(1, 1) = a(3)
'output
MsgBox A(3)
MsgBox Sheets(1).Cells(1, 1)
End Sub

stranno
01-26-2012, 01:49 PM
Thanks Mike,

This might be helpful.
Actually, I used variables like A_20, A_201 etc. in my real sub.
(Public const A_20 as integer = 20 or someting like that)

Now i know that A_20 in cells(1,1) never yields 20 as resullt of
msgbox cells(1,1). At least if A_20 in cells(1,1) is compoesed of "A_" & 20

Bob Phillips
01-26-2012, 05:14 PM
You cannot reference variables indirectly as you are trying, but you can use arrays as Mike showed.