How to CallByName a sub in another module?
in [General]
|
From: Armen Stein on 10 Jan 2010 12:51 On Sat, 9 Jan 2010 18:07:51 -0800, "Renny Bosch" <noname(a)nospam.com> wrote: > 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
From: Renny Bosch on 10 Jan 2010 19:34 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. "Armen Stein" <ArmenStein(a)removethisgmail.com> wrote in message news:dj4kk558n9gcrbeq4csm13vm3gmq8oaen0(a)4ax.com... > On Sat, 9 Jan 2010 18:07:51 -0800, "Renny Bosch" <noname(a)nospam.com> > wrote: > >> 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 >
From: John Spencer on 11 Jan 2010 09:17 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 wrote: > 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. > > > "Armen Stein" <ArmenStein(a)removethisgmail.com> wrote in message > news:dj4kk558n9gcrbeq4csm13vm3gmq8oaen0(a)4ax.com... >> On Sat, 9 Jan 2010 18:07:51 -0800, "Renny Bosch" <noname(a)nospam.com> >> wrote: >> >>> 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 >> > >
From: Renny Bosch on 11 Jan 2010 13:39 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 "John Spencer" <spencer(a)chpdm.edu> wrote in message news:%23XmDYjskKHA.1648(a)TK2MSFTNGP05.phx.gbl... > 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 wrote: >> 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. >> >> >> "Armen Stein" <ArmenStein(a)removethisgmail.com> wrote in message >> news:dj4kk558n9gcrbeq4csm13vm3gmq8oaen0(a)4ax.com... >>> On Sat, 9 Jan 2010 18:07:51 -0800, "Renny Bosch" <noname(a)nospam.com> >>> wrote: >>> >>>> 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 >>> >>
From: Douglas J. Steele on 11 Jan 2010 13:48 Are you saying that you didn't have var = in front? That's necessary. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Renny Bosch" <noname(a)nospam.com> wrote in message news:eiRw71ukKHA.2164(a)TK2MSFTNGP02.phx.gbl... > 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 > > "John Spencer" <spencer(a)chpdm.edu> wrote in message > news:%23XmDYjskKHA.1648(a)TK2MSFTNGP05.phx.gbl... >> 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 wrote: >>> 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. >>> >>> >>> "Armen Stein" <ArmenStein(a)removethisgmail.com> wrote in message >>> news:dj4kk558n9gcrbeq4csm13vm3gmq8oaen0(a)4ax.com... >>>> On Sat, 9 Jan 2010 18:07:51 -0800, "Renny Bosch" <noname(a)nospam.com> >>>> wrote: >>>> >>>>> 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 >>>> >>> >
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 |