Does VBA have macro substitution like FoxPro?

[Ronald W. Roberts]

Does VBA have a command that will allow me to substitute a value for a
name in a command?

In Visual FoxPro I can do what is known as macro substitution.
You place an ampersand (&) before a variable to use the value of the
variable as a name.

The format of the command below is "REPLACE FieldName1 WITH
eExpression1".
eExpression1 can be a literal, a memory variable, an expression, or a
field from a table.

An example of the RPLACE command could be written like:

A literal: REPLACE Student.First_Name WITH "Joe"
A memory variable: REPLACE Student.First_Name WITH Hold_First_Name
An expression: REPLACE Student.Amount WITH (Hold_Qty * Hold_Price)
A table name: REPLACE Student.First_Name WITH Inputtable.fName

In this example below, I do not know the names of the input tables when
I write the
program. The program determines the input table names from logic that
loads an array
from an input directory. The program determines the month the data
represents, who sent
the file, and the data type by looking at the alpha and numeric
characters in the input file
name. I have another table that maps the field names for the input
table type to the output table.

cField1=ALLTRIM(cInputFileName)+"."+ALLTRIM(gaFldArray(X,3))
cField2=cOutputFileName+"."+ALLTRIM(gaFldArray(X,2))
REPLACE &cField2 WITH &cField1

So, the REPLACE command when it executes would look like:

REPLACE Student.First_Name With H1234A01.SU_Fname

TIA

Ron

--
/)*----------------------------------------*(\
/ / * "But what ... is it good for?" * \ \
( ( * Engineer at the Advanced Computing * ) )
(((\ \>* /) Systems Division of IBM, 1968, (\ *</ /)))
(\\\\ \*// commenting on the microchip. \\*/ ////)
\ /------------------------------------- \ /
\ / \ /

[Abbot Cooper]

The answer is "yes" - but how you would accomplish this depends on the
context in which you are going to use it. For instance, if you are going to
be using SQL, it is as simple as setting your control/object name to as
string and then sticking it into your SQL statement:

Dim strSomeField as String, strSQL as String
strSomeField = MyField
strSQL = "SELECT blah blah blah FROM blah." & strSomeField & " blah blah
blah"
DoCmd.RunSQL strSQL

Outside of the realm of SQL statements, you would use the "Eval" function.
The following example is taken directly from help:

Dim ctl As Control, strCtl As String
Set ctl = Forms!Employees!LastName
strCtl = "Forms!Employees!LastName"
MsgBox ("The current value of " & ctl.Name & " is " & Eval(strCtl))

HTH.

Abbot Cooper
cooper_N...@mediaone.net
(remove "_NoSpam_" from address when sending)

Ronald W. Roberts <r...@robcom.com> wrote in article
<36BB0C0A...@robcom.com>...

[Michael Kaplan]

The best way to think of both Eval and Application.Run is as direct routes
to the expression service that is used by Access and Jet... its the same ES
that lets you have functions in queries and such.... and they follow the
same rules (must be public functions, cannot use variables, etc.).

Michael

Scott Treseder (DataBlox) <sco...@datablox.com> wrote in message
news:36bdcf28...@news.cet.com...
> On Fri, 05 Feb 1999 10:19:39 -0500, "Ronald W. Roberts" <r...@robcom.com>
wrote:

>
> >Does VBA have a command that will allow me to substitute a value for a
> >name in a command?
> >
>

> Look at the Eval function. It's very powerful in that it parses and
interprets
> it's string argument as code - then executes it.
>
>
> Scott Treseder, Office/Q Developer
> 100% QuickBooks to VBA data extraction
> Clone your QuickBooks file to an Access 97 database
> http://www.datablox.com Spokane, WA

[Scott Treseder (DataBlox)]

[Kallal]

Actually, the answer is NO. While some expressions can be evaluated using
Eval etc, Access does not have a runtime interpretation of code, or
variables. For example in FoxPro you can do the following:

myavar = "this is some text"
b = "myavar"
print &b

The above will display "this is some text". Macro substitution allows you to
ask a user what variable to display, and then display it......Access does
not allow this.

a = 5
b = "a"
print &b

Will result in 5 being displayed. Or better:

textvar = inputbox(" what variable to display")
print &textvar

That will display the contents of what var name the user types in.

Or even better yet.......

subtocall = input("what sub to call")

call &subtocall

That will call the subroutine of your choice. We cannot do a call &varname
in Access to call a subroutine. Again, you can see the effects of run-time
interpretation...the above will call whatever subroutine name the user types
in. In fact, macro substitution can be used anywhere in any part of a line
of code....really cool here! (ie: whatever you would have typed in can be
put in a variable)..

(Ps....above code examples ...not 100% correct syntax)

Albert D. Kallal
Edmonton, Alberta Canada
kal...@msn.com