• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA: How do you Reference a Variable with a Variable?

C

Cruiser

Member
' I have 20 instances of this argument:

If Category1 = "Tea" Then
Teas = Trange1
End If

' Through

If Category20 = "Tea" Then
Teas = Trange20
End If

' Where each Category (1-20) is a variable resulting from a Vlookup based on the value in
' each Trange (1-20), and each Trange is a variable derived from the changing contents
' of 20 ranges. This code works perfectly to place the type of tea, say, “English Breakfast”, in the
' named range Teas.

' I have several similar arguments which are also repeated 20 times, and the code has become rather
' long. I tried to shorten the code by looping, but apparently I need to learn a little something about
' working with variables as my best efforts have failed. What is missing from the code below?:

Dim i As Integer
Dim Va As String
Dim Vb As String
For i = 1 To 20
Va = "Category" & i
Vb = "Trange" & i
If Va = "Tea" Then
Teas = Vb
End If
Next

‘MsgBox Category1 results in "Teas", but, MsgBox Va results in “Category1” (through Category20),
' rather than its stored result "Teas".

' Likewise, MsgBox Trange1 results in "English Breakfast" while MsgBox Vb results in “Trange1” rather
' than its stored result “English Breakfast”. How do I get the variable to reference the result stored in
' the other variable, rather than the name of the other variable?
 
Last edited:
Hi

In your little loop VA will never equal "Tea" because you have assigned it to Category(i) so your loop will never achieve the result you wish.

Code: 
Sub Test()
Dim i As Integer
Dim Va As String
Dim Vb As String
Dim teas As String

    For i = 1 To 20
        Va = "Category" & i
        Vb = "Trange" & i
        If Va = "Tea" Then teas = Vb
    Next i
End Sub

So

If Va = "Tea" Then teas = Vb

will always be false. I can't really work out what you are trying to achieve from your post. A workbook with before and after even if it is a dummy workbook may help.

Take care

Smallman
 
Thank you for your reply, Smallman. I do understand that it will always be false because the result of [Va = "Category" & i] is "Category1" rather than "Tea". That was the whole reason for my post. To recap: Category1 is a variable. The string stored in that variable is "Tea". So the question is: How do I get Va to return the string ("Tea") stored in the variable instead of just the name of the variable ("Category1")? Does that make sense? You're right, I should have included a dummy workbook. I assumed that someone in the huge talent pool on this site would recognize the problem in the code and know the answer. Perhaps referencing variables with other variables is not a very common practice. I'll put a dummy workbook together and post it when i have time, unless somebody posts a solution first. Hopefully someone on the Chandoo forums has dealt with this before. It is all rather complicated, certainly for me, anyway...
 
Maybe imbedding the code properly will help attract some answers... Sorry for the novice mistake!

So, I have 20 instances of this argument:
Code: 
If Category1 = "Tea" Then
Teas = Trange1
End If

' Through

If Category20 = "Tea" Then
Teas = Trange20
End If

Where each Category (1-20) is a variable resulting from a Vlookup based on the value in each Trange (1-20), and each Trange is a variable derived from the changing contents of 20 ranges. This code works perfectly to place the type of tea, say, “English Breakfast”, in the named range Teas.

I have several similar arguments which are also repeated 20 times, and the code has become rather long. I tried to shorten the code by looping, but apparently I need to learn a little something about working with variables as my best efforts have failed. What is missing from the code below?:
Code: 
Dim i As Integer
Dim Va As String
Dim Vb As String
For i = 1 To 20
Va = "Category" & i
Vb = "Trange" & i
If Va = "Tea" Then
Teas = Vb
End If
Next

Message Box reveals the problem:
Code: 
MsgBox Category1
results in "Teas", but,
Code: 
MsgBox Va
results in “Category1”.

Likewise,
Code: 
MsgBox Trange1
results in "English Breakfast" while
Code: 
MsgBox Vb
results in “Trange1” rather than its stored result “English Breakfast”. How do I get the variable to reference the result stored in the other variable, rather than the name of the other variable?
 
Hi ,

You are trying to do the impossible or the unnecessary.

What you are trying to do is a pointer mechanism , which does not exist in VBA ; languages like C do have this ; in fact system programming relies on this.

Using the API , you can come close ; see this link :

http://www.codeproject.com/Articles/712/How-to-do-pointers-in-Visual-Basic

VBA does have an AddressOf operator , but this is effective in a different situation.

If you can upload your complete workbook , with an explanation of what you want to do , a solution which does not involve this kind of addressing can be worked out.

Narayan
 
Thanks, Narayan. Maybe it is unnecessary... It works just fine without looping. I am not looking for another solution, just a way to do the "impossible"! It was just getting rather long and I thought looping might be a good idea. Thanks for the link also. Unfortunately, I am Visual Basic illiterate...
 
Smallman & Narayan,
Here is the dummy workbook in a one sheet example form. So as not to waste your time, please be aware that this was created solely to illustrate the problem when trying to reference a variable from another variable.

Thanks,

Cruiser
 
NARAYANK991 said:
Hi ,

You are trying to do the impossible or the unnecessary.

What you are trying to do is a pointer mechanism , which does not exist in VBA ; languages like C do have this ; in fact system programming relies on this.

Using the API , you can come close ; see this link :

http://www.codeproject.com/Articles/712/How-to-do-pointers-in-Visual-Basic

VBA does have an AddressOf operator , but this is effective in a different situation.

If you can upload your complete workbook , with an explanation of what you want to do , a solution which does not involve this kind of addressing can be worked out.

Narayan
Click to expand...
Narayan,
I had a chance to look over the link you recommended. While it's never fun to hear that what you want to do cannot be done in the environment you are trying to do it in - I do really appreciate you for setting me straight. I had wasted quite a bit of time, as you say, "trying to do the impossible".
 
You must log in or register to reply here.
Back
Top