By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,224 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,224 IT Pros & Developers. It's quick & easy.

Dynamically referencing a variable in VBA code in Access

P: 38
Hello everyone,

Is there a way to dynamically construct a variable name (which is previously defined) in VBA and then reference it at run-time to access/update its value? I should add that my example below assumes the Sub and the Call are in different modules. I know that if I’m working in the same module I can do this if I create and instantiate as an object.

For example assume I have a sub Test as follows:

Sub Test (VarName as String)
Dim A as String
Dim B as String
%VarName% = “Testing”
‘% is my assumption – bear with me…
End Sub

Now I call the Sub Test with:

Dim VarName as String
VarName = “B”
Call Test (VarName)

What will happen is in Test – the Value of variable B is set to “Testing”

Is it possible to do something like this?

Thank you in advance for your help.
Salzan
Feb 3 '08 #1
Share this Question
Share on Google+
6 Replies


Delerna
Expert 100+
P: 1,134
Don't know if you can do that in VBA but here is an alternative

Expand|Select|Wrap|Line Numbers
  1. TestSub Test (VarName as String)
  2.     Dim A as String
  3.     Dim B as String
  4.     If Varname="A" then A = “Testing”
  5.     If Varname="B" then B = “Testing”
  6. End Sub
  7.  
If I put my mind to it I could probably come up with other ways.
There is always more than 1 way to achieve your goal
Feb 3 '08 #2

P: 38
Thanks but that's exactly what I'm trying to avoid. I have a situation that results in 52 If statements and each block will contain about 20 lines of code. This makes a huge section of code.
Feb 4 '08 #3

ADezii
Expert 5K+
P: 8,679
Hello everyone,

Is there a way to dynamically construct a variable name (which is previously defined) in VBA and then reference it at run-time to access/update its value? I should add that my example below assumes the Sub and the Call are in different modules. I know that if I’m working in the same module I can do this if I create and instantiate as an object.

For example assume I have a sub Test as follows:

Sub Test (VarName as String)
Dim A as String
Dim B as String
%VarName% = “Testing”
‘% is my assumption – bear with me…
End Sub

Now I call the Sub Test with:

Dim VarName as String
VarName = “B”
Call Test (VarName)

What will happen is in Test – the Value of variable B is set to “Testing”

Is it possible to do something like this?

Thank you in advance for your help.
Salzan
Just for curiosity, what exactly are you trying to accomplish with this logic?
Feb 4 '08 #4

P: 38
Just for curiosity, what exactly are you trying to accomplish with this logic?
I have a situation that results in 52 If statements and each block will contain about 20 lines of code. This makes a huge section of code 20X52 lines of code. Instread if I can construct a variable name dynamically, I'll get a way with only 20 some lines of code.
Feb 6 '08 #5

ADezii
Expert 5K+
P: 8,679
I have a situation that results in 52 If statements and each block will contain about 20 lines of code. This makes a huge section of code 20X52 lines of code. Instread if I can construct a variable name dynamically, I'll get a way with only 20 some lines of code.
I don't think that you can dynamically create a Variable in the manner in which you are describing in VBA, and if you could it would probably open up a host of potential problems somewhat similar to Circular Referencing. Why not describe in detail exactly what you are trying to accomplish, along with a subset of code involving the If..End If constructs. Hopefully, we can arrive at a more practical and efficient solution.
Feb 6 '08 #6

Delerna
Expert 100+
P: 1,134
I agree with ADezii, as i said earlier there is always more than 1 way to accomplish a task.
Feb 6 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.