Newbie request on "variable substitution"

[sts023]

Hi guys....

What I'd like to achieve is to have a calling module (let's call it FROMModule), and a called module (lets call it TOModule), and in FROMModule to set up the name of a Public Variable, and call TOModule with the name of the variable, and have TOModule determine the value of the variable who's name was passed.

It's probably simple, but I've now spent so long searching for a "how to" example I'm in danger of forgetting why I wantted to know in the first place!

Sample code would be something like -

Dim OtherVal    As String
Sub FROMModule()
Dim VarName     As String
  OtherVal = "Hello"
  VarName = "OtherVal"
  TOModule (VarName)
End Sub
Sub TOModule(txt As String)
Dim SomethingMagicHappensHere As String
'*
'** Get the VALUE of the Variable received as txt
'** into the variable SomethingMagicHappensHere. Like -
'** SomethingMagicHappensHere = somefunction(txt)
'*
  Debug.Print "I want the next bit to say Hello - " & SomethingMagicHappensHere
End Sub

Can anyone spare a moment to give me a clue?

 

[Brianwarnock]

Since. and I quote,

Variables declared using the Public statement are available to all procedures in all modules in all applications unless Option Private Module is in effect; in which case, the variables are public only within the project in which they reside.

I don't understand what it is you are trying to do.

Brian

 

[sts023]

Hi Brian....

I'm not sure how else I can explain it, but here goes -
In other languages with which I am (much more) familiar there is the concept of "variable substitution".
When executing variable substitution, the value of a variable is the name of another variable.
What I'm trying to achieve is to get at the value of a variable, when all I have is another variable containing the name of the first variable.

For example in one of the languages I used to use (RPF) the character "?" informs the interpreter that variable substitution is about to happen, and that a variable named between the next two occurrences of "?" will be subjected to variable substitution.

In this language

LET JOE = 27
LET FRED = "JOE"
? PRINT "The value of " FRED " is " ?FRED?

would result in the following line of print :-

The value of JOE is 27

What I'm trying to achieve in VBA is something similar.

I'd like to call a subroutine with a variable which contains the name of another variable; then within the subroutine, extract the value in the variable who's name was passed.

Hoping that helps....???

 

[vbaInet]

The closest you will get to achieving something like this is using the Eval() function. Look into that.

 

[sts023]

OK guys, I guess it's one of those "back to the drawing board" times.
Thanks anyway for trying to help!

 

[The_Doc_Man]

The only way to do what you are seeking in VBA would occur with the EVAL() function OR if the item in question was part of a collection, which would allow you to name the item using a syntax such as collectionname("itemname") to get the value. Since variables are NOT part of a collection (that I know of), that syntax doesn't work. If EVAL() doesn't do it for you, the only OTHER case is to constrain the range of the possible variables and then do something like

public function switchme( selector as string ) as integer

if isnull(selector) then 
  switchme = -1
  goto wedone
end if

if len(selector) = 0 then
  switcme = 0
  goto wedone
end if

select case selector
  case "JOE"
    switchme = joe
  case "FRED"
    switchme = fred
  case "GAVIN"
    switchme = gavin
  etc etc etc
  case else
    switchme = 0 
end select

wedone:

end function

 

[sts023]

Thanks, Doc Man.

I've already recoded using a version of your "Case" example (thanks).

I suspect that this form of "indirect addressing" is probably a feature of very few languages, although even now through the mists of time I can still hear my ICL PLAN tutor in the late 60's intoning "Never SMO a SMO!".
(SMO = Supplementary MOdifier)
The apparent lack of this facility just means using alternative techniques; it's not a "Show Stopper".

Thanks to all those who took the time to post!

P.S. - Doc Man, being "certified", can mean something quite different in the U.K., often involving drugs, restraints, and padded cells! (Don't ask how I know!)