Solved: Displaying results of stringA by calling stringB

[erdansch]

I have written 4 basic excel macros and am working on my first Word macro. My problem is that I have a database of values such as:
Dim aaa$, aab$, aac$....
aaa=car
aab=boat
abc=plane
through
ccc=train

If I get the first character from one place, the second from another and the third also and put it together in a string called "test", how can i call on test to get the desired word? For example:

x = a
y = b
z = c
test = a & b & c
Selection.TypeText test (I want the result to be plane)

I know I can get the result I want with If statements and do loops but I am looking for a simple one liner. Any ideas?

[Frosty]

It sounds like you're asking if you can, essentially, dim a variable name, and then concatenate a series of values to come up with the variable name you want to check the value of? Don't think that's possible. In fact, I'm a little curious how you'd use do loops and if statements to do it. Maybe if you showed that, people might weigh in on how to optimize.

No matter how I think of it, it seems like you'll need some kind of intermediary to hold the string name of your variable associated with the value... but that's not really utilizing a dimensioned variable.

Of course, there are more efficient and less efficient intermediaries... obviously an array is one way. But the key field of a collection may suit... although key fields have to be unique.

Something like the following:

Sub demo()
Dim colValues As Collection
Dim sWhichVar As String
Dim sWhichVal As String

Set colValues = New Collection

colValues.Add "car", "aaa"
colValues.Add "boat", "aab"

sWhichVar = "aab"

sWhichVal = colValues(sWhichVar)
MsgBox sWhichVal

End Sub

[erdansch]

Thanks for your reply Frosty! I have never use collections before so I will play with your idea and see if I can get that to work for me.

First I should point out that my test equation should have looked like:
test = x & y & z (which results in abc but I want it to be plane) also i don't know if you can edit your original post?

Second, my solution with if statements is to skip dimensioning up front altogether and put the database into the if statements, such as:

if test = "aaa" then Selection.TypeText "car"
if test = "aab" then Selection.TypeText "boat"...etc.

Clearly I can speed this up by nesting the if statements based on first, second and third characters, but since this whole thing will be inside a do loop and could happen hundreds of times, this will considerably slow down my code.

[Frosty]

I still think you're being too abstract in the framing of your question. I don't know how you're collecting your data, etc.

But boiled down, it seems like you're asking if it is possible to evaluate a variable name as a string value. And I don't think it is. Maybe someone else has a method.

As for optimizing code speed... you should post a full mockup of your code from start to finish, rather than abstract questions about bits and pieces.

You can probably do any number of different logical constructs and your processing speed will be minimally impacted.

However, your use of the selection object instead of a range object (and a variable to build whatever full insert you want to do) will have very large (negative) impacts on your processing speed.

Think of it this way: each item in your undo list (after all is said and done) "costs" time far more than your logic constructs.

In general, it is better to make your code readable and get in and out of the document in as few actions as possible... even if a single line if statement saves you .0000000001 second.