Evaluate a Variable Name from a String

[Guest]

Is it possible in VBA to determine a variable name from a string? For
example -

- - - - - -
Dim VariableName as String
Dim FavoriteNumber as Integer

FavoriteNumber = 55
VariableName = "FavoriteNumber"

msgbox {*}
- - - - - -

* indicating an expression that will return the value of the FavoriteNumber
variable, using the value of VariableName ("FavoriteColor") to evaluate the
FavoriteColor variable, and return the value of 55.

I know the example seems overly complicated (why not just use the
FavoriteNumber directly in the code?)

I have a class module with 65 variables, and I would like to place a
reference to them all in an Array, so that I can loop through them all and
assign values, yet I would like the values to still be updated if any of the
variables are updated outside of the array.

In other words, I would like an object or expression that references a
variable.

 

[Leith Ross]

Hello Developer of the Caribbean,

From what you describe your problem is more of a design problem. Havin
written many Class modules myself, the only reason I can see you woul
want to do this is beacuse you don't know enough about the language an
its structure yet to accomplish your goal. Why not post the code s
others can guide you in resolving these issues? If it is lengthy
attach the code in a zip file and upload it.

Sincerely,
Leith Ros

 

[Mark H. Shin]

Since your variables are contained in a class module, you can use CallByName
method. Example below:

Class1:
Public FavoriteNumber As Integer

Module1:
Sub test()
Dim C As New Class1
Dim VariableName As String

C.FavoriteNumber = 55

VariableName = "FavoriteNumber"

MsgBox CallByName(C, VariableName, VbGet)
End Sub


"Developer of the Caribbean"

 

[Guest]

Leith,

Thank you for responding!

Unfortunately, my class is over 500 lines of code long. Essentially, the 65
variables represent metadata fields (mostly strings and integers) that
together represent a job listing ("Client Name", "Job Type", "Work Order
Number", "Shipping Company".) The fields are public so other modules can
read and write directly into individual fields of the object.

The methods of the class perform group actions on all of the fields, such as
clearing them all, writing them all to a worksheet (using user customizable
columns stored in an array), updating corresponding fields in an
enterprise-wide database, or filling the the fields in the object from the
database.

All of the code works fine. It is just bulky. Each method hard codes all
of the field names - some methods clear all the fields, other fill all of the
fields. Since many fields in each method use the same logic, it would be
cleaner if there is a way to loop through the variables, but I would need
some sort of reference list or collection to loop through.

I am experienced with VBA, but my thinking on this issue may be complicated
by my knowledge of Java, where it is relatively easy to create an array of
variable references.

Any advice you can give would be very much appreciated! I am open to a
complete rethink.

 

[Guest]

Mark,

I will test this in the morning, but this looks like what I was searching for.

Thank you so much!