dynamically calling subroutines

Note: I'm not sure if the subject line of this post uses the correct
terminology, so apologies if the subject line turns out to be
misleading.

I think it's probably easier to provide a trivial example to
illustrate the question rather than to try to ask the question in
general terms.

Assume there is a table with these records

column1 column2 column3
------------ ------------ ------------
"abc" "def" "PopulateXYZ"
"abc" "ghi" "Populate123"

Assume there is a module somewhere that has subroutines:

Sub PopulateXYZ
' some code here
End sub

Populate123
' some code here
End sub

Assume
- there is code that can position to a record in the table above.
- that code reads the string in column3

Is there some way for the code that has read the string in column3
(e.g. "PopulateXYZ") to then call the subroutine that is named in the
string object? I realize it would be possible to construct a SELECT
CASE statement or an if/else statement to accomplish this, e.g.

' code here populates strColunm3

If strColumn3 = "PopulateXYZ" Then
PopulateXYZ
Else
Populate123
End If

but my question is wondering whether it's possible to more or less
dynamically tell VB, "here's a string, the contents of which is the
name of a subroutine...go execute the subroutine named in that
string".

BTW, I'm using Access 97...if this capability does not exist in
earlier (e.g. Access 97) versions but it exists in later versions, I'd
be interested to know that as well.

Thank you.

Re: dynamically calling subroutines

On Fri, 21 Dec 2007 08:45:14 -0800 (PST), mirandacascade@yahoo.com wrote:

>Note: I'm not sure if the subject line of this post uses the correct
>terminology, so apologies if the subject line turns out to be
>misleading.
>
>I think it's probably easier to provide a trivial example to
>illustrate the question rather than to try to ask the question in
>general terms.
>
>Assume there is a table with these records
>
>column1 column2 column3
>------------ ------------ ------------
>"abc" "def" "PopulateXYZ"
>"abc" "ghi" "Populate123"
>
>Assume there is a module somewhere that has subroutines:
>
>Sub PopulateXYZ
>' some code here
>End sub
>
>Populate123
>' some code here
>End sub
>
>Assume
> - there is code that can position to a record in the table above.
> - that code reads the string in column3
>
>Is there some way for the code that has read the string in column3
>(e.g. "PopulateXYZ") to then call the subroutine that is named in the
>string object? I realize it would be possible to construct a SELECT
>CASE statement or an if/else statement to accomplish this, e.g.
>
>' code here populates strColunm3
>
>If strColumn3 = "PopulateXYZ" Then
> PopulateXYZ
>Else
> Populate123
>End If
>
>but my question is wondering whether it's possible to more or less
>dynamically tell VB, "here's a string, the contents of which is the
>name of a subroutine...go execute the subroutine named in that
>string".
>
>BTW, I'm using Access 97...if this capability does not exist in
>earlier (e.g. Access 97) versions but it exists in later versions, I'd
>be interested to know that as well.
>
>Thank you.
>

You can use Eval to to do this.

If strColumn3 = "PopulateXYZ" Then
Eval ("PopulateXYZ")
Else
Eval ("Populate123")
End If

NOTE: The routines you are calling MUST be functions not subs.
If they are currently subs, change them to functions.


Wayne Gillespie
Gosford NSW Australia

Re: dynamically calling subroutines

You can use CallByName(), but from memory, it works only in Access 2000 and
later.

This example fires the AfterUpdate event procedure of Surname text box of
the current form:
Call CallByName(Me, "Surname_AfterUpdate", vbMethod)

You need to remove the Private keyword from the procedure declaration.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
news:837e28cf-0ece-404b-8c6e-e2e94f219f27@b40g2000prf.google groups.com...
> Note: I'm not sure if the subject line of this post uses the correct
> terminology, so apologies if the subject line turns out to be
> misleading.
>
> I think it's probably easier to provide a trivial example to
> illustrate the question rather than to try to ask the question in
> general terms.
>
> Assume there is a table with these records
>
> column1 column2 column3
> ------------ ------------ ------------
> "abc" "def" "PopulateXYZ"
> "abc" "ghi" "Populate123"
>
> Assume there is a module somewhere that has subroutines:
>
> Sub PopulateXYZ
> ' some code here
> End sub
>
> Populate123
> ' some code here
> End sub
>
> Assume
> - there is code that can position to a record in the table above.
> - that code reads the string in column3
>
> Is there some way for the code that has read the string in column3
> (e.g. "PopulateXYZ") to then call the subroutine that is named in the
> string object? I realize it would be possible to construct a SELECT
> CASE statement or an if/else statement to accomplish this, e.g.
>
> ' code here populates strColunm3
>
> If strColumn3 = "PopulateXYZ" Then
> PopulateXYZ
> Else
> Populate123
> End If
>
> but my question is wondering whether it's possible to more or less
> dynamically tell VB, "here's a string, the contents of which is the
> name of a subroutine...go execute the subroutine named in that
> string".
>
> BTW, I'm using Access 97...if this capability does not exist in
> earlier (e.g. Access 97) versions but it exists in later versions, I'd
> be interested to know that as well.
>
> Thank you.

Re: dynamically calling subroutines

wrote in message
news:837e28cf-0ece-404b-8c6e-e2e94f219f27@b40g2000prf.google groups.com...
> Note: I'm not sure if the subject line of this post uses the correct
> terminology, so apologies if the subject line turns out to be
> misleading.
>
> I think it's probably easier to provide a trivial example to
> illustrate the question rather than to try to ask the question in
> general terms.
>
> Assume there is a table with these records
>
> column1 column2 column3
> ------------ ------------ ------------
> "abc" "def" "PopulateXYZ"
> "abc" "ghi" "Populate123"
>
> Assume there is a module somewhere that has subroutines:
>
> Sub PopulateXYZ
> ' some code here
> End sub
>
> Populate123
> ' some code here
> End sub
>
> Assume
> - there is code that can position to a record in the table above.
> - that code reads the string in column3
>
> Is there some way for the code that has read the string in column3
> (e.g. "PopulateXYZ") to then call the subroutine that is named in the
> string object? I realize it would be possible to construct a SELECT
> CASE statement or an if/else statement to accomplish this, e.g.
>
> ' code here populates strColunm3
>
> If strColumn3 = "PopulateXYZ" Then
> PopulateXYZ
> Else
> Populate123
> End If
>
> but my question is wondering whether it's possible to more or less
> dynamically tell VB, "here's a string, the contents of which is the
> name of a subroutine...go execute the subroutine named in that
> string".
>
> BTW, I'm using Access 97...if this capability does not exist in
> earlier (e.g. Access 97) versions but it exists in later versions, I'd
> be interested to know that as well.
>
> Thank you.

In addition to Wayne and Allen's suggestions, you can also use
Application.Run:

With Application
If strColumn3 = "PopulateXYZ" Then
.Run "PopulateXYZ"
Else
.Run "Populate123"
End If
End With

The named procedure can be either a function or a sub, although the Eval
function can return a value, whereas .Run and CallByName cannot.
The choice is yours..

Re: dynamically calling subroutines

Correction:
> The named procedure can be either a function or a sub, although the Eval
> function can return a value, whereas .Run and CallByName cannot.
> The choice is yours..

That ought to read:

The named procedure can be either a function or a sub, although Eval and
CallByName can return values, whereas .Run cannot.

Re: dynamically calling subroutines

On Dec 22, 2:17 am, "Stuart McCall" wrote:

> The named procedure can be either a function or a sub, although Eval and
> CallByName can return values, whereas .Run cannot.

That's what I thought until a few months ago.

Try:

Function temp3$(ByVal vS$)
temp3 = UCase(vS)
End Function

Sub temp4()
Dim s$
s = "temp3"
MsgBox Application.Run("temp3", "abc")
End Sub

Re: dynamically calling subroutines

On Dec 22, 7:35 am, lyle wrote:
> On Dec 22, 2:17 am, "Stuart McCall" wrote:
>
> > The named procedure can be either a function or a sub, although Eval and
> > CallByName can return values, whereas .Run cannot.
>
> That's what I thought until a few months ago.
>
> Try:
>
> Function temp3$(ByVal vS$)
> temp3 = UCase(vS)
> End Function
>
> Sub temp4()
> Dim s$
> s = "temp3"
> MsgBox Application.Run("temp3", "abc")
> End Sub

Yikes ...

Maybe ...

Function temp3$(ByVal vS$)
temp3 = UCase(vS)
End Function

Sub temp4()
Dim s$
s = "temp3"
MsgBox Application.Run(s, "abc")
End Sub

Re: dynamically calling subroutines

> Function temp3$(ByVal vS$)
> temp3 = UCase(vS)
> End Function
>
> Sub temp4()
> Dim s$
> s = "temp3"
> MsgBox Application.Run(s, "abc")
> End Sub

Well well. Who'd a thunk it? I've never seen mention of this anywhere till
today. This'll come in very handy next time I automate Access. What I've
been doing till now to communicate with a calling app, is to write a file
which the caller polls the folder for. Receiving a return value is much
cleaner (plus no extra code required in the automated app).

Thanks v much for setting me straight.