|
New form of application.run
Hello all.
One of my colleagues has a series of macros. In these macros he has a series of calls-- application.run-- that call to activate a secondary, or series of tertiary macros. However, I keep getting the argument not optional error449. This has lead me to think that Excel 2007 no longer uses that object event handler. Does anyone know the replacement, or new version of that command? As always, your responses are deeply appreciated. Thank you in advance. |
New form of application.run
Ok, it appears that this is either too vague, or not understood.
Let me see if I can revise it some to clarify. I have three macros. MacroA, MacroB, and MacroC. MacroA, and MacroB each perform a unique task, and work well. MacroC has one job-- to access, and perform the task of both MacroA, and MacroB, in order. Sample below. Sub MacroC(control As IRibbonControl) Application.run "MacroA" Application.run "MacroB" Range("c:5").select end Sub When I access MacroC, I get an error window, as stated before-- Argument not optional error449. When I click debug, it takes me to the first application.run "MacroA". What is it that I've done wrong, and how can I correct it because I have at least 6 or so macros that are similar to this one. As always, in advance, thank you for your responses. Best. "SteveDB1" wrote: Hello all. One of my colleagues has a series of macros. In these macros he has a series of calls-- application.run-- that call to activate a secondary, or series of tertiary macros. However, I keep getting the argument not optional error449. This has lead me to think that Excel 2007 no longer uses that object event handler. Does anyone know the replacement, or new version of that command? As always, your responses are deeply appreciated. Thank you in advance. |
New form of application.run
Maybe it's time to share what MacroC looks like.
And maybe you should look for macros with duplicate names (in different modules). SteveDB1 wrote: Ok, it appears that this is either too vague, or not understood. Let me see if I can revise it some to clarify. I have three macros. MacroA, MacroB, and MacroC. MacroA, and MacroB each perform a unique task, and work well. MacroC has one job-- to access, and perform the task of both MacroA, and MacroB, in order. Sample below. Sub MacroC(control As IRibbonControl) Application.run "MacroA" Application.run "MacroB" Range("c:5").select end Sub When I access MacroC, I get an error window, as stated before-- Argument not optional error449. When I click debug, it takes me to the first application.run "MacroA". What is it that I've done wrong, and how can I correct it because I have at least 6 or so macros that are similar to this one. As always, in advance, thank you for your responses. Best. "SteveDB1" wrote: Hello all. One of my colleagues has a series of macros. In these macros he has a series of calls-- application.run-- that call to activate a secondary, or series of tertiary macros. However, I keep getting the argument not optional error449. This has lead me to think that Excel 2007 no longer uses that object event handler. Does anyone know the replacement, or new version of that command? As always, your responses are deeply appreciated. Thank you in advance. -- Dave Peterson |
New form of application.run
Ok, an update.
I've gone through this more closely, and have finally found the Application.Run function explanation in the help file. Geez they hide it well. I printed it out for future reference. The initial part of our error is: Application.Run "MacroA" should be Application.run (MacroA, arg1, ..., arg30) However, when I click debug, it then tells me that the argument is not optional (which makes me think it's required), and the help file states that it is optional. What argument would I need? Again, thank you for your help. "SteveDB1" wrote: Hello all. One of my colleagues has a series of macros. In these macros he has a series of calls-- application.run-- that call to activate a secondary, or series of tertiary macros. However, I keep getting the argument not optional error449. This has lead me to think that Excel 2007 no longer uses that object event handler. Does anyone know the replacement, or new version of that command? As always, your responses are deeply appreciated. Thank you in advance. |
New form of application.run
Dave,
Thanks for your time. MacroC is: ------------------------------------------------------------------- Sub AllSetFileProps(control As IRibbonControl) Application.Run (ClearFileProps) Application.Run (FillFilePropsComments) Range("C5").Select End Sub ----------------------------------------------------------------------- "Dave Peterson" wrote: Maybe it's time to share what MacroC looks like. And maybe you should look for macros with duplicate names (in different modules). SteveDB1 wrote: Ok, it appears that this is either too vague, or not understood. Let me see if I can revise it some to clarify. I have three macros. MacroA, MacroB, and MacroC. MacroA, and MacroB each perform a unique task, and work well. MacroC has one job-- to access, and perform the task of both MacroA, and MacroB, in order. Sample below. Sub MacroC(control As IRibbonControl) Application.run "MacroA" Application.run "MacroB" Range("c:5").select end Sub When I access MacroC, I get an error window, as stated before-- Argument not optional error449. When I click debug, it takes me to the first application.run "MacroA". What is it that I've done wrong, and how can I correct it because I have at least 6 or so macros that are similar to this one. As always, in advance, thank you for your responses. Best. "SteveDB1" wrote: Hello all. One of my colleagues has a series of macros. In these macros he has a series of calls-- application.run-- that call to activate a secondary, or series of tertiary macros. However, I keep getting the argument not optional error449. This has lead me to think that Excel 2007 no longer uses that object event handler. Does anyone know the replacement, or new version of that command? As always, your responses are deeply appreciated. Thank you in advance. -- Dave Peterson |
New form of application.run
First, I haven't worked with xl2007 enough to be useful.
But AllSetFileProps is looking for a control to be passed to it. But it doesn't look like you're using that control in that procedure. Maybe: Sub AllSetFileProps() would be sufficient or Sub AllSetFileProps(Optional control as IRibbonControl) But these are just guesses! Lack of knowledge about xl2007 is a beautiful thing <vvbg. SteveDB1 wrote: Dave, Thanks for your time. MacroC is: ------------------------------------------------------------------- Sub AllSetFileProps(control As IRibbonControl) Application.Run (ClearFileProps) Application.Run (FillFilePropsComments) Range("C5").Select End Sub ----------------------------------------------------------------------- "Dave Peterson" wrote: Maybe it's time to share what MacroC looks like. And maybe you should look for macros with duplicate names (in different modules). SteveDB1 wrote: Ok, it appears that this is either too vague, or not understood. Let me see if I can revise it some to clarify. I have three macros. MacroA, MacroB, and MacroC. MacroA, and MacroB each perform a unique task, and work well. MacroC has one job-- to access, and perform the task of both MacroA, and MacroB, in order. Sample below. Sub MacroC(control As IRibbonControl) Application.run "MacroA" Application.run "MacroB" Range("c:5").select end Sub When I access MacroC, I get an error window, as stated before-- Argument not optional error449. When I click debug, it takes me to the first application.run "MacroA". What is it that I've done wrong, and how can I correct it because I have at least 6 or so macros that are similar to this one. As always, in advance, thank you for your responses. Best. "SteveDB1" wrote: Hello all. One of my colleagues has a series of macros. In these macros he has a series of calls-- application.run-- that call to activate a secondary, or series of tertiary macros. However, I keep getting the argument not optional error449. This has lead me to think that Excel 2007 no longer uses that object event handler. Does anyone know the replacement, or new version of that command? As always, your responses are deeply appreciated. Thank you in advance. -- Dave Peterson -- Dave Peterson |
New form of application.run
lol....
well, that's part of my problem too. Ignorance of the new requirements. According to the help file, Application.run (Macro, arg1, ....., arg30) is the structure of the function. Oddly enough it further states that both the macro, and the arg# are optional. Which is strange, because the error I get states the argument is not optional. the (Control As IRibbonControl) is required to make it work in the new ribbon menus. I've been working with Ron deBruin for the past few weeks on that part. what do you know about application.run() from previous versions of Excel? I.e., if the argument is required, what would it have been? All I'd like to do is to have the macro perform its requisite task, for each application.run I have. Best. Steve "Dave Peterson" wrote: First, I haven't worked with xl2007 enough to be useful. But AllSetFileProps is looking for a control to be passed to it. But it doesn't look like you're using that control in that procedure. Maybe: Sub AllSetFileProps() would be sufficient or Sub AllSetFileProps(Optional control as IRibbonControl) But these are just guesses! Lack of knowledge about xl2007 is a beautiful thing <vvbg. SteveDB1 wrote: Dave, Thanks for your time. MacroC is: ------------------------------------------------------------------- Sub AllSetFileProps(control As IRibbonControl) Application.Run (ClearFileProps) Application.Run (FillFilePropsComments) Range("C5").Select End Sub ----------------------------------------------------------------------- "Dave Peterson" wrote: Maybe it's time to share what MacroC looks like. And maybe you should look for macros with duplicate names (in different modules). SteveDB1 wrote: Ok, it appears that this is either too vague, or not understood. Let me see if I can revise it some to clarify. I have three macros. MacroA, MacroB, and MacroC. MacroA, and MacroB each perform a unique task, and work well. MacroC has one job-- to access, and perform the task of both MacroA, and MacroB, in order. Sample below. Sub MacroC(control As IRibbonControl) Application.run "MacroA" Application.run "MacroB" Range("c:5").select end Sub When I access MacroC, I get an error window, as stated before-- Argument not optional error449. When I click debug, it takes me to the first application.run "MacroA". What is it that I've done wrong, and how can I correct it because I have at least 6 or so macros that are similar to this one. As always, in advance, thank you for your responses. Best. "SteveDB1" wrote: Hello all. One of my colleagues has a series of macros. In these macros he has a series of calls-- application.run-- that call to activate a secondary, or series of tertiary macros. However, I keep getting the argument not optional error449. This has lead me to think that Excel 2007 no longer uses that object event handler. Does anyone know the replacement, or new version of that command? As always, your responses are deeply appreciated. Thank you in advance. -- Dave Peterson -- Dave Peterson |
New form of application.run
after further attempts to make this work, I'm still at an impasse.
According to MS, the help file states that this function should be as follows: application.run(Macro, arg1, ...., arg30) Where Macro is the name of the macro to be performed, and "arg#" is the argument to be passed to the function. Also according to the help file, both the Macro, and the argument are optional. Since however I'm actually interested in performing a specific task with a macro, I need to have it there. On the other hand however, I do not need an argument, unless it really is necessary. Hence my problem-- I keep getting errors that state the argument is not optional. However, I don't know if it's the number of arguments, or the fact that I've omitted the argument, and what the argument is that I need. And since I'm only just learning this, I really don't know if the terminology I'm using is correct. I just want the thing to work, correctly, consistently, and continually. Each of the macros that I'm calling to activate work fine-- as stand alones. It's only when I call to them from the tertiary macro by means of application.run that I have the problem. And no, I can't just copy the macros into the third, and dump the others. I need those others as well. While I'm willing to accept that perhaps application.run is not the best choice, I need to know what is my best choice to perform these tasks. Again, as awalys, in advance, thank you for your time. |
New form of application.run
I still didn't see anything in that AllSetFileProps routine that needs anything
passed to it. But in general, you can call that routine with: application.run "macronamehere", arg1, arg2 The same thing as you saw. But looking at your sample code, it looks like you could drop the application.run and just use: Call AllSetFileProps or Call AllSetFileProps(whatevercontrolyouwanttopass) ====== Application.run is very useful if you're calling a routine in a different workbook. Or calling a routine whose name you don't know when you're writing the code: Dim myMacName as string if something then mymacname = "mac1" else mymacname = "mac2" end if application.run mymacname But that doesn't look like anything you're doing. ==== Maybe Ron will jump in with more info about xl2007 and your code. SteveDB1 wrote: lol.... well, that's part of my problem too. Ignorance of the new requirements. According to the help file, Application.run (Macro, arg1, ....., arg30) is the structure of the function. Oddly enough it further states that both the macro, and the arg# are optional. Which is strange, because the error I get states the argument is not optional. the (Control As IRibbonControl) is required to make it work in the new ribbon menus. I've been working with Ron deBruin for the past few weeks on that part. what do you know about application.run() from previous versions of Excel? I.e., if the argument is required, what would it have been? All I'd like to do is to have the macro perform its requisite task, for each application.run I have. Best. Steve "Dave Peterson" wrote: First, I haven't worked with xl2007 enough to be useful. But AllSetFileProps is looking for a control to be passed to it. But it doesn't look like you're using that control in that procedure. Maybe: Sub AllSetFileProps() would be sufficient or Sub AllSetFileProps(Optional control as IRibbonControl) But these are just guesses! Lack of knowledge about xl2007 is a beautiful thing <vvbg. SteveDB1 wrote: Dave, Thanks for your time. MacroC is: ------------------------------------------------------------------- Sub AllSetFileProps(control As IRibbonControl) Application.Run (ClearFileProps) Application.Run (FillFilePropsComments) Range("C5").Select End Sub ----------------------------------------------------------------------- "Dave Peterson" wrote: Maybe it's time to share what MacroC looks like. And maybe you should look for macros with duplicate names (in different modules). SteveDB1 wrote: Ok, it appears that this is either too vague, or not understood. Let me see if I can revise it some to clarify. I have three macros. MacroA, MacroB, and MacroC. MacroA, and MacroB each perform a unique task, and work well. MacroC has one job-- to access, and perform the task of both MacroA, and MacroB, in order. Sample below. Sub MacroC(control As IRibbonControl) Application.run "MacroA" Application.run "MacroB" Range("c:5").select end Sub When I access MacroC, I get an error window, as stated before-- Argument not optional error449. When I click debug, it takes me to the first application.run "MacroA". What is it that I've done wrong, and how can I correct it because I have at least 6 or so macros that are similar to this one. As always, in advance, thank you for your responses. Best. "SteveDB1" wrote: Hello all. One of my colleagues has a series of macros. In these macros he has a series of calls-- application.run-- that call to activate a secondary, or series of tertiary macros. However, I keep getting the argument not optional error449. This has lead me to think that Excel 2007 no longer uses that object event handler. Does anyone know the replacement, or new version of that command? As always, your responses are deeply appreciated. Thank you in advance. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
New form of application.run
Ok, another update.
I've placed Optional in the (control As IRibbonControl) to now be (Optional control As IRibbonControl). While I now get past the 449 error, I now have the following error. Expected function or variable. One of the listed solutions was for public, and private functions. However, since this macro grouping is all within a single module, that doesn't seem to apply. |
New form of application.run
Bingo!!!!!!!!!
And a few more exclamations to boot! Thank you. My solution was as follows: 1- all of the macros that call to other macros require-- and I do mean requi (Optional control As IRibbonControl) to work as a ribbon menu item what I can only assume is double duty-- internal and through the ribbon. 2- Call works, where application.run did not. Don't forget to remove the parenthesis. I.e., what was application.run(MacroName) now becomes Call MacroName. Dave, Thank you very much for your time, and patience. I just ran through compile vba project and I received no errors. As you might imagine, I'm sighing a huge sigh and of relief and gratitude. Have a great weekend. Best. SteveB. "Dave Peterson" wrote: I still didn't see anything in that AllSetFileProps routine that needs anything passed to it. But in general, you can call that routine with: application.run "macronamehere", arg1, arg2 The same thing as you saw. But looking at your sample code, it looks like you could drop the application.run and just use: Call AllSetFileProps or Call AllSetFileProps(whatevercontrolyouwanttopass) ====== Application.run is very useful if you're calling a routine in a different workbook. Or calling a routine whose name you don't know when you're writing the code: Dim myMacName as string if something then mymacname = "mac1" else mymacname = "mac2" end if application.run mymacname But that doesn't look like anything you're doing. ==== Maybe Ron will jump in with more info about xl2007 and your code. SteveDB1 wrote: lol.... well, that's part of my problem too. Ignorance of the new requirements. According to the help file, Application.run (Macro, arg1, ....., arg30) is the structure of the function. Oddly enough it further states that both the macro, and the arg# are optional. Which is strange, because the error I get states the argument is not optional. the (Control As IRibbonControl) is required to make it work in the new ribbon menus. I've been working with Ron deBruin for the past few weeks on that part. what do you know about application.run() from previous versions of Excel? I.e., if the argument is required, what would it have been? All I'd like to do is to have the macro perform its requisite task, for each application.run I have. Best. Steve "Dave Peterson" wrote: First, I haven't worked with xl2007 enough to be useful. But AllSetFileProps is looking for a control to be passed to it. But it doesn't look like you're using that control in that procedure. Maybe: Sub AllSetFileProps() would be sufficient or Sub AllSetFileProps(Optional control as IRibbonControl) But these are just guesses! Lack of knowledge about xl2007 is a beautiful thing <vvbg. SteveDB1 wrote: Dave, Thanks for your time. MacroC is: ------------------------------------------------------------------- Sub AllSetFileProps(control As IRibbonControl) Application.Run (ClearFileProps) Application.Run (FillFilePropsComments) Range("C5").Select End Sub ----------------------------------------------------------------------- "Dave Peterson" wrote: Maybe it's time to share what MacroC looks like. And maybe you should look for macros with duplicate names (in different modules). SteveDB1 wrote: Ok, it appears that this is either too vague, or not understood. Let me see if I can revise it some to clarify. I have three macros. MacroA, MacroB, and MacroC. MacroA, and MacroB each perform a unique task, and work well. MacroC has one job-- to access, and perform the task of both MacroA, and MacroB, in order. Sample below. Sub MacroC(control As IRibbonControl) Application.run "MacroA" Application.run "MacroB" Range("c:5").select end Sub When I access MacroC, I get an error window, as stated before-- Argument not optional error449. When I click debug, it takes me to the first application.run "MacroA". What is it that I've done wrong, and how can I correct it because I have at least 6 or so macros that are similar to this one. As always, in advance, thank you for your responses. Best. "SteveDB1" wrote: Hello all. One of my colleagues has a series of macros. In these macros he has a series of calls-- application.run-- that call to activate a secondary, or series of tertiary macros. However, I keep getting the argument not optional error449. This has lead me to think that Excel 2007 no longer uses that object event handler. Does anyone know the replacement, or new version of that command? As always, your responses are deeply appreciated. Thank you in advance. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 07:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com