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.

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
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Renny Bosch" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.
>
>
>
>
>

Gina, your message didn't show up.

Renny

"Gina,ccie cert. and mcse cert. email me" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>
> "Renny Bosch" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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.
>>
>>
>>
>>
>>
>

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]" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
>
>
> "Renny Bosch" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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.
>>
>>
>>
>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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]" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>> --
>> Arvin Meyer, MCP, MVP
>> http://www.datastrat.com
>> http://www.mvps.org/access
>> http://www.accessmvp.com
>>
>>
>> "Renny Bosch" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>>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.
>>>
>>>
>>>
>>>
>>>
>>
>>
>
>

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]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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]" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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
>>> --
>>> Arvin Meyer, MCP, MVP
>>> http://www.datastrat.com
>>> http://www.mvps.org/access
>>> http://www.accessmvp.com
>>>
>>>
>>> "Renny Bosch" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>>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.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

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

"Renny Bosch" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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]" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)...
>>>> 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
>>>> --
>>>> Arvin Meyer, MCP, MVP
>>>> http://www.datastrat.com
>>>> http://www.mvps.org/access
>>>> http://www.accessmvp.com
>>>>
>>>>
>>>> "Renny Bosch" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>>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.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

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!

--
Clif

"Renny Bosch" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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]" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)...
>>>> 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
>>>> --
>>>> Arvin Meyer, MCP, MVP
>>>> http://www.datastrat.com
>>>> http://www.mvps.org/access
>>>> http://www.accessmvp.com
>>>>
>>>>
>>>> "Renny Bosch" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>>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.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>



--
Clif

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

Clif McIrvin wrote:
> 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