How to CallByName a sub in another module?
in [General]
|
From: Arvin Meyer [MVP] on 9 Jan 2010 16:27 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" <noname(a)nospam.com> wrote in message news:%23D84UrWkKHA.5604(a)TK2MSFTNGP04.phx.gbl... > 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]" <arvinm(a)mvps.invalid> wrote in message > news:%23E6Je6NkKHA.1652(a)TK2MSFTNGP05.phx.gbl... >> 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" <noname(a)nospam.com> wrote in message >> news:OQLpyTNkKHA.2164(a)TK2MSFTNGP02.phx.gbl... >>>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. >>> >>> >>> >>> >>> >> >> > >
From: Renny Bosch on 9 Jan 2010 21:07 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]" <arvinm(a)mvps.invalid> wrote in message news:eIH2EKXkKHA.1656(a)TK2MSFTNGP04.phx.gbl... > 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" <noname(a)nospam.com> wrote in message > news:%23D84UrWkKHA.5604(a)TK2MSFTNGP04.phx.gbl... >> 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]" <arvinm(a)mvps.invalid> wrote in message >> news:%23E6Je6NkKHA.1652(a)TK2MSFTNGP05.phx.gbl... >>> 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" <noname(a)nospam.com> wrote in message >>> news:OQLpyTNkKHA.2164(a)TK2MSFTNGP02.phx.gbl... >>>>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. >>>> >>>> >>>> >>>> >>>> >>> >>> >> >> > >
From: Arvin Meyer [MVP] on 9 Jan 2010 21:12 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" <noname(a)nospam.com> wrote in message news:O7TZ4mZkKHA.4872(a)TK2MSFTNGP05.phx.gbl... > 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]" <arvinm(a)mvps.invalid> wrote in message > news:eIH2EKXkKHA.1656(a)TK2MSFTNGP04.phx.gbl... >> 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" <noname(a)nospam.com> wrote in message >> news:%23D84UrWkKHA.5604(a)TK2MSFTNGP04.phx.gbl... >>> 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]" <arvinm(a)mvps.invalid> wrote in message >>> news:%23E6Je6NkKHA.1652(a)TK2MSFTNGP05.phx.gbl... >>>> 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" <noname(a)nospam.com> wrote in message >>>> news:OQLpyTNkKHA.2164(a)TK2MSFTNGP02.phx.gbl... >>>>>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. >>>>> >>>>> >>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
From: Clif McIrvin on 9 Jan 2010 23:04 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" <noname(a)nospam.com> wrote in message news:O7TZ4mZkKHA.4872(a)TK2MSFTNGP05.phx.gbl... > 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]" <arvinm(a)mvps.invalid> wrote in message > news:eIH2EKXkKHA.1656(a)TK2MSFTNGP04.phx.gbl... >> 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" <noname(a)nospam.com> wrote in message >> news:%23D84UrWkKHA.5604(a)TK2MSFTNGP04.phx.gbl... >>> 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]" <arvinm(a)mvps.invalid> wrote in message >>> news:%23E6Je6NkKHA.1652(a)TK2MSFTNGP05.phx.gbl... >>>> 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" <noname(a)nospam.com> wrote in message >>>> news:OQLpyTNkKHA.2164(a)TK2MSFTNGP02.phx.gbl... >>>>>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
From: John Spencer on 10 Jan 2010 10:22 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
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 5 Prev: "Not find the object MSysDB, what to do ???? access 2007 Next: where is my contacts |