Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event macro does not run a Private sub
Excel XP & Win XP
I have a simple BeforeSave macro: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call UpdateAll End Sub The UpdateAll macro is a Private macro as: Private Sub UpdateAll() 'Stuff End Sub The event macro errored out as not being able to find the sub. When I removed the "Private" in the first line of the UpdateAll macro, it worked fine. Why is that? Thanks for your time. Otto |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event macro does not run a Private sub
The Private declaration indicates that the procedure is not visible to
code outside the module in which the procedure is defined. So, if you have UpdateAll in a module other than the ThisWorkbook module and it is marked Private, it won't be found. When you use neither Public nor Private in a regular code module, Public is the default. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 18 Oct 2008 17:44:40 -0400, "Otto Moehrbach" wrote: Excel XP & Win XP I have a simple BeforeSave macro: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call UpdateAll End Sub The UpdateAll macro is a Private macro as: Private Sub UpdateAll() 'Stuff End Sub The event macro errored out as not being able to find the sub. When I removed the "Private" in the first line of the UpdateAll macro, it worked fine. Why is that? Thanks for your time. Otto |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event macro does not run a Private sub
Chip
Thanks for that. There is no end to this learning thing. I thought "Private" simply hid that macro from the Tools - Macro - Macros list. I'll go back to passing a dummy value to hide the macro. Thanks again. Otto "Chip Pearson" wrote in message ... The Private declaration indicates that the procedure is not visible to code outside the module in which the procedure is defined. So, if you have UpdateAll in a module other than the ThisWorkbook module and it is marked Private, it won't be found. When you use neither Public nor Private in a regular code module, Public is the default. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 18 Oct 2008 17:44:40 -0400, "Otto Moehrbach" wrote: Excel XP & Win XP I have a simple BeforeSave macro: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call UpdateAll End Sub The UpdateAll macro is a Private macro as: Private Sub UpdateAll() 'Stuff End Sub The event macro errored out as not being able to find the sub. When I removed the "Private" in the first line of the UpdateAll macro, it worked fine. Why is that? Thanks for your time. Otto |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event macro does not run a Private sub
Whether a variable or procedure is visible to other procedures and/or module is called "scope". See http://www.cpearson.com/Excel/Scope.aspx for details about scope as it relates to procedure and variable declarations. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sun, 19 Oct 2008 10:59:29 -0400, "Otto Moehrbach" wrote: Chip Thanks for that. There is no end to this learning thing. I thought "Private" simply hid that macro from the Tools - Macro - Macros list. I'll go back to passing a dummy value to hide the macro. Thanks again. Otto "Chip Pearson" wrote in message .. . The Private declaration indicates that the procedure is not visible to code outside the module in which the procedure is defined. So, if you have UpdateAll in a module other than the ThisWorkbook module and it is marked Private, it won't be found. When you use neither Public nor Private in a regular code module, Public is the default. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 18 Oct 2008 17:44:40 -0400, "Otto Moehrbach" wrote: Excel XP & Win XP I have a simple BeforeSave macro: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call UpdateAll End Sub The UpdateAll macro is a Private macro as: Private Sub UpdateAll() 'Stuff End Sub The event macro errored out as not being able to find the sub. When I removed the "Private" in the first line of the UpdateAll macro, it worked fine. Why is that? Thanks for your time. Otto |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event macro does not run a Private sub
Thanks Chip. Otto
"Chip Pearson" wrote in message ... Whether a variable or procedure is visible to other procedures and/or module is called "scope". See http://www.cpearson.com/Excel/Scope.aspx for details about scope as it relates to procedure and variable declarations. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sun, 19 Oct 2008 10:59:29 -0400, "Otto Moehrbach" wrote: Chip Thanks for that. There is no end to this learning thing. I thought "Private" simply hid that macro from the Tools - Macro - Macros list. I'll go back to passing a dummy value to hide the macro. Thanks again. Otto "Chip Pearson" wrote in message . .. The Private declaration indicates that the procedure is not visible to code outside the module in which the procedure is defined. So, if you have UpdateAll in a module other than the ThisWorkbook module and it is marked Private, it won't be found. When you use neither Public nor Private in a regular code module, Public is the default. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 18 Oct 2008 17:44:40 -0400, "Otto Moehrbach" wrote: Excel XP & Win XP I have a simple BeforeSave macro: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call UpdateAll End Sub The UpdateAll macro is a Private macro as: Private Sub UpdateAll() 'Stuff End Sub The event macro errored out as not being able to find the sub. When I removed the "Private" in the first line of the UpdateAll macro, it worked fine. Why is that? Thanks for your time. Otto |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event macro does not run a Private sub
Or add
Option Private Module to the module declaration, it will then be exposed to all modules, but not 'seen' from Excel. -- __________________________________ HTH Bob "Otto Moehrbach" wrote in message ... Chip Thanks for that. There is no end to this learning thing. I thought "Private" simply hid that macro from the Tools - Macro - Macros list. I'll go back to passing a dummy value to hide the macro. Thanks again. Otto "Chip Pearson" wrote in message ... The Private declaration indicates that the procedure is not visible to code outside the module in which the procedure is defined. So, if you have UpdateAll in a module other than the ThisWorkbook module and it is marked Private, it won't be found. When you use neither Public nor Private in a regular code module, Public is the default. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 18 Oct 2008 17:44:40 -0400, "Otto Moehrbach" wrote: Excel XP & Win XP I have a simple BeforeSave macro: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call UpdateAll End Sub The UpdateAll macro is a Private macro as: Private Sub UpdateAll() 'Stuff End Sub The event macro errored out as not being able to find the sub. When I removed the "Private" in the first line of the UpdateAll macro, it worked fine. Why is that? Thanks for your time. Otto |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event macro does not run a Private sub
Bob
Do you have that right? I have the following in my HowTo file: "You don't need to put private in front of each sub. You can type Option Private Module at the top of the module and all the macros within that module will be Private." What you are saying seems to be the opposite except for the "visible" part. Thanks for your time. Otto "Bob Phillips" wrote in message ... Or add Option Private Module to the module declaration, it will then be exposed to all modules, but not 'seen' from Excel. -- __________________________________ HTH Bob "Otto Moehrbach" wrote in message ... Chip Thanks for that. There is no end to this learning thing. I thought "Private" simply hid that macro from the Tools - Macro - Macros list. I'll go back to passing a dummy value to hide the macro. Thanks again. Otto "Chip Pearson" wrote in message ... The Private declaration indicates that the procedure is not visible to code outside the module in which the procedure is defined. So, if you have UpdateAll in a module other than the ThisWorkbook module and it is marked Private, it won't be found. When you use neither Public nor Private in a regular code module, Public is the default. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 18 Oct 2008 17:44:40 -0400, "Otto Moehrbach" wrote: Excel XP & Win XP I have a simple BeforeSave macro: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call UpdateAll End Sub The UpdateAll macro is a Private macro as: Private Sub UpdateAll() 'Stuff End Sub The event macro errored out as not being able to find the sub. When I removed the "Private" in the first line of the UpdateAll macro, it worked fine. Why is that? Thanks for your time. Otto |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event macro does not run a Private sub
Hi,
Just one thing for future consideration, you don't need to use Call in this kind of situation. You can simply enter the name of the subroutine. There is also a third way to call code and that is with the Run command. Each of these approaches has its uses. Now a little more on calling code in other modules - If you put code in the thisWorkbook object or Sheet1 object for example as Public (not Private) you can't call it from a another module by using its name instead you must qualify it that means if the code is call Sub myMessage you must call it with Sheet1.myMessage. Then it will be executed. Note this same approach can be used to run code in user forms from outside the user form. UserForm1.Message would run the Public Sub Message() or the Sub Message() coded on the user form. Add the word Private Sub Message() and you won't be able to run it even when you qualify the name. If any of this is helpful, please click the Yes button. -- Thanks, Shane Devenshire "Otto Moehrbach" wrote: Excel XP & Win XP I have a simple BeforeSave macro: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call UpdateAll End Sub The UpdateAll macro is a Private macro as: Private Sub UpdateAll() 'Stuff End Sub The event macro errored out as not being able to find the sub. When I removed the "Private" in the first line of the UpdateAll macro, it worked fine. Why is that? Thanks for your time. Otto |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event macro does not run a Private sub
"ShaneDevenshire" wrote in message ... Hi, Just one thing for future consideration, you don't need to use Call in this kind of situation. You can simply enter the name of the subroutine. That is true, but it is a far better practice IMO to use Call to show that the code is calling another procedure. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event macro does not run a Private sub
Bob
I agree and that's one of the reasons I do that. Otto "Bob Phillips" wrote in message ... "ShaneDevenshire" wrote in message ... Hi, Just one thing for future consideration, you don't need to use Call in this kind of situation. You can simply enter the name of the subroutine. That is true, but it is a far better practice IMO to use Call to show that the code is calling another procedure. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event macro does not run a Private sub
Shane
Thanks for the help. Otto "ShaneDevenshire" wrote in message ... Hi, Just one thing for future consideration, you don't need to use Call in this kind of situation. You can simply enter the name of the subroutine. There is also a third way to call code and that is with the Run command. Each of these approaches has its uses. Now a little more on calling code in other modules - If you put code in the thisWorkbook object or Sheet1 object for example as Public (not Private) you can't call it from a another module by using its name instead you must qualify it that means if the code is call Sub myMessage you must call it with Sheet1.myMessage. Then it will be executed. Note this same approach can be used to run code in user forms from outside the user form. UserForm1.Message would run the Public Sub Message() or the Sub Message() coded on the user form. Add the word Private Sub Message() and you won't be able to run it even when you qualify the name. If any of this is helpful, please click the Yes button. -- Thanks, Shane Devenshire "Otto Moehrbach" wrote: Excel XP & Win XP I have a simple BeforeSave macro: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call UpdateAll End Sub The UpdateAll macro is a Private macro as: Private Sub UpdateAll() 'Stuff End Sub The event macro errored out as not being able to find the sub. When I removed the "Private" in the first line of the UpdateAll macro, it worked fine. Why is that? Thanks for your time. Otto |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Improve method of calling a private function in a private module | Excel Programming | |||
Private Sub Workbook_Open() event won't fire if workbook is hidden? | Excel Programming | |||
Public, Private, Event modules, Forms modules,,, | Excel Programming | |||
re : Possible to run private sub macros by writing another private | Excel Programming | |||
Private Sub Running Other Private Sub Inadvertently | Excel Programming |