0

I want to use a variable to define another variable, hopefully I can explain this well enough. I'm using VBA in MS Access 2010.

Lets say I have Var1, Var2, Var3, and Var4 and I start with this

Dim Var1 As String
Dim Var2 As String
Dim Var3 As String
Dim Var4 As String
Var1 = "Var"
Var2 = "4"
Var3 = "Hello"

Now I want to set the value of Var4, but I want to do it using the values of Var1 and Var2 combined to produce Var4, so instead of

Var4 = "Hello"

I want something like this to result in Var4 equaling "Hello":

Var1 & Var2 = "Hello"

I also want to compare the values of Var3 and Var4 by using Var1 and Var2

If Var1 & Var2 = Var3 Then
MsgBox ("Hello World!")
End If

What should happen in this If statement is it should compare the value of the variable Var4 with the value of Var3 and display the message box. I realize what I typed would compare the values "Var4" with "Hello" but hopefully you get the point because I'm not sure how to elaborate any better.

[edit] The link Barranka posted seems like it might do what I want. I'll see if I can figure out how to use that but I'm a really big noob at this so if anyone has an easier solution I'm still listening :)

[edit] I was going about this COMPLETELY wrong. After enough Googling, I stumbled across arrays. I was asking the wrong question because I didn't realize there was something else that would do what I need so easily.

user3154383
  • 23
  • 7
  • check out [Creating and using collections](http://www.wiseowl.co.uk/blog/s239/collections.htm) – Barranka Jan 05 '15 at 18:31
  • You could do this via a dictionary, but not with simple variables. – crashmstr Jan 05 '15 at 18:31
  • You cannot do this in VBA. However, you may use Classes in VBA and this will allow you to treat a property (whichever) of a class as your variable name. It will be very combersome but doable nonetheless. – Gene Skuratovsky Jan 05 '15 at 18:31
  • 1
    I am very confused about what you are trying to achieve. You want Var4 to be "Hello" and you want Var1 & Var2 = "Hello". The only way to achieve this is by setting Var1 and Var2 to the corresponding substrings. There are many combinations that would make both conditions true (Var1 = "Hello" & Var2 = "", Var1 = "Hell" & Var2 = "o", ...) Could you please elaborate on the background of your question? – silentsurfer Jan 05 '15 at 18:31

2 Answers2

2

The Eval Function might be what you're looking for.

Eval(Var1 & Var2 & " = Hello")

Also, you'll need to add some escaped quotes around Hello. This link might help.

Community
  • 1
  • 1
justinjhendrick
  • 426
  • 3
  • 4
  • `Eval()` uses the expression service which means single quotes can be used in its argument. `Eval("'" & Var1 & Var2 & "' = 'Hello'")` – HansUp Jan 05 '15 at 21:37
  • This is exactly what I am looking for! I'm having a bit of trouble getting it to work for some reason but I'll probably figure it out. I'll edit my OP when I've got something that works. – user3154383 Jan 07 '15 at 15:20
0

If I understand your question, you are almost there. You just need to add Var4 = Var1 & Var2. Did you want all for Variables to come from an excel sheet? Let me know whether this does it:

Private Sub Hello()

Dim Var1 As String
Dim Var2 As String
Dim Var3 As String
Dim Var4 As String
Var1 = "Var"
Var2 = "4"
Var3 = "Hello"

Var4 = Var1 & Var2

If Var1 & Var2 = Var3 Then
MsgBox ("Hello World!")
End If

End Sub
E. A. Bagby
  • 824
  • 9
  • 24
  • Sorry, not quite what I want to do. Var1 = Var, and Var2 = 4, and I want to combine those to get Var4. I want to do it so that when combined, they are used as if they are the variable Var4 which equals "Hello" instead of the string "Var4". The if statement would come out true. – user3154383 Jan 05 '15 at 19:03
  • I should add, since you mentioned excel, what I'm trying to do isn't get variable values from any text boxes or set the value of cells or anything like that. I have a variable set in the vba that is being used in the same vba script so this part of what I'm doing is completely application independent. Presumably it would work in anything at all that supports vba. – user3154383 Jan 05 '15 at 19:27