How to CallByName a sub in another module?

[Renny Bosch]

I have a form on which I can enter a number, and a "Run" button. I would
like to call the procedure indicated by the number, so I have tried to
calculate the procedure name and the name of the module in which that
procedure is found.

I tried various things, such as:

Dim myModule As Module

Set myModule = Application.Modules("Euler" & pn \ 25)
CallByName myModule, "Euler" & pn, VbMethod

On the statement I get error 438, Object doesn't support this property or
method.

When the program stops there (having entered 11 for the number), I find that
myModule correctly resolves to Euler0, which is the name of the module that
contains the Sub Euler11(). So why do I get error 438?

Windows XP fully updated, MS Office 2007, VBA 6.5.

 

[Arvin Meyer [MVP]]

You call procedures like this from standard modules. For instance, a module
named:

basUtilities

might have a function called FileExists(strPath As String) As Boolean

which you'd call like:

If FileExists("C:\MyFile.txt") Then
'Do something
End If

 

[Gina,ccie cert. and mcse cert. email me]

I have a form on which I can enter a number, and a "Run" button. I would
like to call the procedure indicated by the number, so I have tried to
calculate the procedure name and the name of the module in which that
procedure is found.

I tried various things, such as:

Dim myModule As Module

Set myModule = Application.Modules("Euler" & pn \ 25)
CallByName myModule, "Euler" & pn, VbMethod

On the statement I get error 438, Object doesn't support this property or
method.

When the program stops there (having entered 11 for the number), I find
that myModule correctly resolves to Euler0, which is the name of the
module that contains the Sub Euler11(). So why do I get error 438?

Windows XP fully updated, MS Office 2007, VBA 6.5.

 

[Renny Bosch]

Gina, your message didn't show up.

Renny

 

[Renny Bosch]

Thank you, Arvin, but in my case the challenge is:

1. The procedure to be called is in a different module than the caller.

2. I don't want to hard-code the procedure name nor the module name. I
want to be able to generate both names in code.

The sample you showed doesn't address either if these issues. If I need to
explain my problem better, please let me know.

 

[Arvin Meyer [MVP]]

The module name doesn't matter as long as it isn't the same as a procedure,
i.e. you can't name both a module and a procedure FileExists. Further, if
the module is a standard module (which it should be if you can call the
functions in it from anywhere) you don't use the module name, just the
procedure name. The example that you showed wouldn't work in Access VBA,
which is why you are getting an error.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Renny Bosch]

It turns out my procedure was in a Class Module (my error, I didn't know
what I was doing). I created a new module, making sure to select "Module",
and moved all the procedures into it. So now I can call it from my
Form_Form1 module without compile error. But the remaining problem is that
I have many different procedures and I want to be able to call the one
corresponding to an input entered by the user. So I read the input from the
Text Box in the Form, and then in my sub Run_Click() I would like to be able
to create the name of the procedure by using VBA code, such as name =
"Euler" & pn. I was told that to call a procedure using a text string
containing its name requires CallByName. Is that wrong? How should I do
it? I am trying to avoid an If-ElseIf-ElseIf-....-EndIf construct that will
become 300 steps long.

Thanks for your help.

Renny

 

[Arvin Meyer [MVP]]

You call a function by using the keyword call, or just the function name
like:

Call FileExists()
or:
FileExists()

a sub is called by just using the name:

FileExists

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Clif McIrvin]

Have you read the help text associated with CallByName? From the VBA
editor code window, place your cursor on CallByName and press F1. From
my read, CallByName applies to methods of an object, not to functions or
subs. If you really need to do what you describe, then you will need a
class module and make your procedures methods of that class object.
(Whew! I just said more than I really understand -- but I'm gaining on
it bit by bit just by lurking in this room <grin>)

You know your situation and we don't ..... but a question:

Are your different procedures similar enough that you can devise a way
to use that user entered value in the code to accomplish your purpose?

Possibly the simplest approach would be to take your multitude of
procedures and re-cast them as Select Case statement groups:

sub Euler10()
code ...
end sub

sub Euler11()
code ...
end sub

etc

becomes

Select Case "Euler" & pn
Case Euler10
'sub Euler10()
code ...
'end sub

Case Euler11
'sub Euler11()
code ...
'end sub

etc

Case Else
code to handle the unexpected exception

End Select

Now you have one procedure with many "clauses" instead of many
procedures ... really no more code than you have now, just organized a
bit differently.

As an aside ... your question prompts me to wonder if you might be
trying to handle non-normalized data ....

HTH!

 

[John Spencer]

If you are calling public functions (NOT subs) you might take a look at the
EVAL function. You can change subs to functions even though you may not be
returning any value.

Eval("PARSEWORD(""Sight for sore Eyes"",2)")

The function ParseWord returns the indicated word in the string.

Other than that I don't know of a way to do what you want. I wonder why you
think you need to do this at all. If your procedures are similarly named, I
would assume that they are doing similar things. And that implies to me that
you would need to run the same procedure with differing arguments.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Armen Stein]

But the remaining problem is that
I have many different procedures and I want to be able to call the one
corresponding to an input entered by the user. So I read the input from the
Text Box in the Form, and then in my sub Run_Click() I would like to be able
to create the name of the procedure by using VBA code, such as name =
"Euler" & pn. I was told that to call a procedure using a text string
containing its name requires CallByName. Is that wrong? How should I do
it? I am trying to avoid an If-ElseIf-ElseIf-....-EndIf construct that will
become 300 steps long.

What you're doing is quite unusual. If you have many procedures
(which need to be built by a developer), and yet you want to call them
based on user input, then you have a potential for errors, mismatches
and unhandled values. As Clif suggested, there might be a better way,
but we'd need to understand more of the details.

But if you move forward with your plan, the Eval function (as John
pointed out) is definitely what you need. It allows you to build a
function name from any string and then invoke it.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Renny Bosch]

I am not a developer, just a retired programmer who is trying to do some
programming for the fun of it. There is a website called ProjectEuler.com,
which publishes little programming challenges (like what is the 10,001th
prime number?). I created an Access project that has one simple form, with
a text box into which I can enter the problem number, a Run command button,
and another text box in which the answer is supposed to appear.

In the Run_Click() procedure I read the problem number (pn), and then invoke
the procedure that I have written to solve that problem, using code like

If pn = 1 Then
Euler1
ElseIf pn = 2 Then Euler2
ElseIf pn = 3 Then Euler3
ElseIf pn = 4 Then Euler2
etc.

where Euler1 etc. are the procedures that I write to solve each problem.

This worked fine as long there were only a few problems. Now I would like
to do something like

Eval ("Euler" & pn & "()")

(following John and Armen's suggestion). But even this attempt doesn't work
for me. I get Error 2425, "The expression you entered has a function name
that Microsoft Office Access can't find." At first I thought that was
because the procedure was in a different Module, so I copied it into the
main Form_Form1 module, but got the same result. Then I thought it's because
the procedure is a Sub, so I changed it to a Function, but same result. Am
I hexed?

Does this explain what I am trying to do enough to get some more help?
Thank you all.

 

[John Spencer]

Dim return as Variant
Dim strFunction as String

strFunction = "EULER" & PN & "()"

Debug.Print strFunction 'Temporary to see what you are trying to execute.

Return = Eval(strFunction)

And Eval ONLY works with FUNCTIONS and not SUBS. AND the functions must be
public functions (as far as I know). As a matter of fact the VBA help has an
example that looks like the following (of course the help examples aren't
always correct, but they usually are):

Sub CallSeries()

Dim intI As Integer

For intI = 1 To 50
Eval("A" & intI & "()")
Next intI

End Sub



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Renny Bosch]

John, I saw that example in the Help window and followed it exactly, like
this:

Eval("Euler" & pn & "()")

The first thing I noticed was that VB put a space between Eval and (. That
made me suspicious that the Help wasn't entirely up to date. In any event
it didn't work, it still gave me Error 2425, "The expression you entered has
a function name that Microsoft Office Access can't find." I hadn't tried
putting the whole function name into a separate string and then calling
Eval, but now I tried that and it gives me exactly the same error.

So am I stuck with a gigantic IfElse (or Case) sequence?

Renny

 

[Douglas J. Steele]

Are you saying that you didn't have

var =

in front? That's necessary.

 

[Renny Bosch]

I tried it both ways, Doug, and it gave the same error both times. The Help
window has an example that uses Eval(...) without a var = in front, as John
mentioned. I am learning not to trust the Help all that much.

 

[Clif McIrvin]

Renny -- I just created a new module in a test db (mdb - A2003) with
this code:

Option Compare Database
Option Explicit

Dim x As String

Public Function Euler11() As Variant

MsgBox "Public Function Euler11"

End Function


Then entered

?eval("Euler" & 11 & "()")

in the immediate window .... and the message box appeared as expected.

(Oh -- your explanation of what you are about was quite helpful. Thank
you.)

...
Clif

 

[John Spencer]

If Access can't find the function then I would suspect that you have the
function in a class module or in an object module (form/report) or you have
somehow made the function unavailable. Also could you post

In the VBA immediate window, try typing the following.
?Euler1()

What happens when you do that? Could you post the actual code of Euler1 or
one of the other functions you are attempting to call?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Renny Bosch]

Hey I think we are beginning to get to the bottom of it. I copied your code
into a new Module (created by using Insert in the VBA window and selecting
Module), then entered your test line into the Immediate window. I got an
error message, because I had another Euler11() in another module, so I
changed 11 to 111 in both places. Then it worked. I got the right MsgBox.

I said "How come? I've tried this before." It then occurred to me that I
was always running my Eval code in the Form_Form1 Module that contains the
Run_Click procedure. So I tested putting your code into the Run_Click sub
in that module and tried to run it in there, and it failed. So it appears
that the Eval needs to be in a Standard Module, not a Class Module. I'll do
some more testing, but as of now I see light at the end of the tunnel.
Thanks a lot.

Renny

 

[Clif McIrvin]

Thank you.

So it appears that the Eval needs to be in a Standard Module, not a
Class Module.

The target of the Eval needs to be in a standard module, not the Eval
statement itself.

I just created a form with a textbox and a command button, and a macro
.... all three of which successfully executed the Eval("Euler" & 11 &
"()") statement.

I entered

=Eval("Euler" & 11 & "()")

as the OnClick property of the command button and as the control source
of the text box, and

Eval("Euler" & 11 & "()")

as a condition in a macro step.


...
Clif

Hey I think we are beginning to get to the bottom of it. I copied
your code into a new Module (created by using Insert in the VBA window
and selecting Module), then entered your test line into the Immediate
window. I got an error message, because I had another Euler11() in
another module, so I changed 11 to 111 in both places. Then it
worked. I got the right MsgBox.

I said "How come? I've tried this before." It then occurred to me
that I was always running my Eval code in the Form_Form1 Module that
contains the Run_Click procedure. So I tested putting your code into
the Run_Click sub in that module and tried to run it in there, and it
failed. So it appears that the Eval needs to be in a Standard Module,
not a Class Module. I'll do some more testing, but as of now I see
light at the end of the tunnel. Thanks a lot.

Renny