Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling on Methods and Properties within Worksheet Modules
From within a class modules, is there a viable way to call on a public
method and/or property that is setup within a worksheet module? I have setup a procedure with the following signatu Public Sub pcdInitializeWorksheet(ByRef l_objScheduleRangeNames As clsScheduleRangeNames) and within the class module that is attempting to call on it, it's setup as: l_wsh.pcdInitializeWorksheet m_objScheduleRangeNames The object variable "l_wsh" is within a For Each...Next loop, which is a worksheet object. At the time it's compiling, it's erroring out stating the above method, "pcdInitializeWorksheet", is not found. If I comment out that one line of code, everything compiles just fine. If it's not feasible to use worksheet modules as such, then I will be left with no choice but to emulate the worksheets. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling on Methods and Properties within Worksheet Modules
Since you are calling a public Sub, the l_wsh is inappropriate. The Public
Sub should be in a public module, not a sheet module. If the procedure pcdInitializeWorksheet is in the public module, the drop the l_wsh from the call and it should respond. "Ronald R. Dodge, Jr." wrote: From within a class modules, is there a viable way to call on a public method and/or property that is setup within a worksheet module? I have setup a procedure with the following signatu Public Sub pcdInitializeWorksheet(ByRef l_objScheduleRangeNames As clsScheduleRangeNames) and within the class module that is attempting to call on it, it's setup as: l_wsh.pcdInitializeWorksheet m_objScheduleRangeNames The object variable "l_wsh" is within a For Each...Next loop, which is a worksheet object. At the time it's compiling, it's erroring out stating the above method, "pcdInitializeWorksheet", is not found. If I comment out that one line of code, everything compiles just fine. If it's not feasible to use worksheet modules as such, then I will be left with no choice but to emulate the worksheets. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling on Methods and Properties within Worksheet Modules
I'm attempting to have properties put into each worksheet, so as when I call
on which ever worksheet, I can get the proper value or set of values from the code. Also, why the prequalified object, that's to avoid ambiguity issues, as I will not introduce ambiguity into my code. Based on what you are saying, I will have to emulate the worksheets. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "JLGWhiz" wrote in message ... Since you are calling a public Sub, the l_wsh is inappropriate. The Public Sub should be in a public module, not a sheet module. If the procedure pcdInitializeWorksheet is in the public module, the drop the l_wsh from the call and it should respond. "Ronald R. Dodge, Jr." wrote: From within a class modules, is there a viable way to call on a public method and/or property that is setup within a worksheet module? I have setup a procedure with the following signatu Public Sub pcdInitializeWorksheet(ByRef l_objScheduleRangeNames As clsScheduleRangeNames) and within the class module that is attempting to call on it, it's setup as: l_wsh.pcdInitializeWorksheet m_objScheduleRangeNames The object variable "l_wsh" is within a For Each...Next loop, which is a worksheet object. At the time it's compiling, it's erroring out stating the above method, "pcdInitializeWorksheet", is not found. If I comment out that one line of code, everything compiles just fine. If it's not feasible to use worksheet modules as such, then I will be left with no choice but to emulate the worksheets. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling on Methods and Properties within Worksheet Modules
If I follow what you are trying to say, and it's a highly dubious if!
Declare l_wsh As Object ' note not as worksheet Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... From within a class modules, is there a viable way to call on a public method and/or property that is setup within a worksheet module? I have setup a procedure with the following signatu Public Sub pcdInitializeWorksheet(ByRef l_objScheduleRangeNames As clsScheduleRangeNames) and within the class module that is attempting to call on it, it's setup as: l_wsh.pcdInitializeWorksheet m_objScheduleRangeNames The object variable "l_wsh" is within a For Each...Next loop, which is a worksheet object. At the time it's compiling, it's erroring out stating the above method, "pcdInitializeWorksheet", is not found. If I comment out that one line of code, everything compiles just fine. If it's not feasible to use worksheet modules as such, then I will be left with no choice but to emulate the worksheets. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling on Methods and Properties within Worksheet Modules
Why is that? I am a strong believer of good programming practice, which
includes avoiding ambiguities whenever possible, which should be nearly 100% of the time, if not 100% of the time. About the only time I can see when ambiguity may not be avoided would be dealing with late binding due to other limitations and the lack of being able to bind at compile time. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... If I follow what you are trying to say, and it's a highly dubious if! Declare l_wsh As Object ' note not as worksheet Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... From within a class modules, is there a viable way to call on a public method and/or property that is setup within a worksheet module? I have setup a procedure with the following signatu Public Sub pcdInitializeWorksheet(ByRef l_objScheduleRangeNames As clsScheduleRangeNames) and within the class module that is attempting to call on it, it's setup as: l_wsh.pcdInitializeWorksheet m_objScheduleRangeNames The object variable "l_wsh" is within a For Each...Next loop, which is a worksheet object. At the time it's compiling, it's erroring out stating the above method, "pcdInitializeWorksheet", is not found. If I comment out that one line of code, everything compiles just fine. If it's not feasible to use worksheet modules as such, then I will be left with no choice but to emulate the worksheets. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling on Methods and Properties within Worksheet Modules
Why is that?
Try it, I'm sure you will then work out why. Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... Why is that? I am a strong believer of good programming practice, which includes avoiding ambiguities whenever possible, which should be nearly 100% of the time, if not 100% of the time. About the only time I can see when ambiguity may not be avoided would be dealing with late binding due to other limitations and the lack of being able to bind at compile time. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... If I follow what you are trying to say, and it's a highly dubious if! Declare l_wsh As Object ' note not as worksheet Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... From within a class modules, is there a viable way to call on a public method and/or property that is setup within a worksheet module? I have setup a procedure with the following signatu Public Sub pcdInitializeWorksheet(ByRef l_objScheduleRangeNames As clsScheduleRangeNames) and within the class module that is attempting to call on it, it's setup as: l_wsh.pcdInitializeWorksheet m_objScheduleRangeNames The object variable "l_wsh" is within a For Each...Next loop, which is a worksheet object. At the time it's compiling, it's erroring out stating the above method, "pcdInitializeWorksheet", is not found. If I comment out that one line of code, everything compiles just fine. If it's not feasible to use worksheet modules as such, then I will be left with no choice but to emulate the worksheets. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling on Methods and Properties within Worksheet Modules
The only reason why I can think of doing that, the Object data type is
similar to the Variant data type except it does have to refer to a class module as an object. With that, instead of the variable being bound at compile time, it would be bound at run time. While this may be of benefit, if there comes a time when MS Office goes to VB.NET base code (Not sure of MS Office 2007 is already like that or not), then I would like to spend as little time converting code over to VB.NET format. While VB.NET does allow for the Object data type, it's very restrictive on what is and what is not allowed. It's already bad enough that error trapping codes [among other adjustments] would have to be modified in so many places when going from the VB6 base code to VB.NET base code. If this happens, a lot of people will be abruptly awakened by the various restrictions of VB.NET such as can't use the Variant data type, and must explicitly declare all variables. Wouldn't be able to imply which parent object such coded variable is refering to nearly as easily as done in the VB6 base code. When stepping through code, rather than it compiling on demand, when one makes an adjustment to code while debugging, program is using the compiled code and the change the develop makes wouldn't take effect until the code is compiled again. GoTo's and other similar statements are no longer allowed in the .NET environment. Error trapping is done via the Try...Catch...Final blocks. MS did this stuff to force people to use more of the good programming practices, so as when debugging, it's much easier to catch things. Of course, not all of the good programming practice rules can be enforced like this as people can still use names that's not so easy to tell what they are, formatting issues, and what ever else there may be. A couple of the benefits though of VB.NET would be that it would be compiled into MSIL, so as multiple programming languages can be used for the same code (in some cases, VB wouldn't be able to be used, but more so C#), and instead of having to create multiple methods/properties, each method and property can have multiple signatures. These are just a few of the several differences between the 2 base codes. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... Why is that? Try it, I'm sure you will then work out why. Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... Why is that? I am a strong believer of good programming practice, which includes avoiding ambiguities whenever possible, which should be nearly 100% of the time, if not 100% of the time. About the only time I can see when ambiguity may not be avoided would be dealing with late binding due to other limitations and the lack of being able to bind at compile time. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... If I follow what you are trying to say, and it's a highly dubious if! Declare l_wsh As Object ' note not as worksheet Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... From within a class modules, is there a viable way to call on a public method and/or property that is setup within a worksheet module? I have setup a procedure with the following signatu Public Sub pcdInitializeWorksheet(ByRef l_objScheduleRangeNames As clsScheduleRangeNames) and within the class module that is attempting to call on it, it's setup as: l_wsh.pcdInitializeWorksheet m_objScheduleRangeNames The object variable "l_wsh" is within a For Each...Next loop, which is a worksheet object. At the time it's compiling, it's erroring out stating the above method, "pcdInitializeWorksheet", is not found. If I comment out that one line of code, everything compiles just fine. If it's not feasible to use worksheet modules as such, then I will be left with no choice but to emulate the worksheets. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling on Methods and Properties within Worksheet Modules
Ronald, if I may say you are making this excessively complicated :-)
In your OP you said you had - "public method and/or property that is setup within a worksheet module" also you want to loop these worksheets. If your code is in worksheet modules it is "VBA", and if you want to use a worksheet Object it is part of Excel's object model. Whether thereafter you want your loop in VBA, VB6 or ..Net or any other language is irrelevant. Try this simple test at the top of EACH worksheet module Public a as Long in a normal module Sub Test() dim i as long Dim obj as Object For each obj in Worksheets i = i + 10 obj.a = i debug.? obj.a next End Sub Run test and you should see 10, 20, 30 (assuming 3 worksheets) in the immediate window. . Now change 'As Object' to 'As Worksheet'. It'll fail for the same reason your code fails. Simply because ' a ' is not a property of a worksheet as defined in the relevant typelib An alternative approach, and perhaps a better one, would be to subclass your worksheets using WithEvents. Then you can include whatever additional methods and properties you wish and get the intellisense, no binding issues etc. You could maintain these classes in whatever app you are working with, eg outside workbook or even outside Excel depending on your app. Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... The only reason why I can think of doing that, the Object data type is similar to the Variant data type except it does have to refer to a class module as an object. With that, instead of the variable being bound at compile time, it would be bound at run time. While this may be of benefit, if there comes a time when MS Office goes to VB.NET base code (Not sure of MS Office 2007 is already like that or not), then I would like to spend as little time converting code over to VB.NET format. While VB.NET does allow for the Object data type, it's very restrictive on what is and what is not allowed. It's already bad enough that error trapping codes [among other adjustments] would have to be modified in so many places when going from the VB6 base code to VB.NET base code. If this happens, a lot of people will be abruptly awakened by the various restrictions of VB.NET such as can't use the Variant data type, and must explicitly declare all variables. Wouldn't be able to imply which parent object such coded variable is refering to nearly as easily as done in the VB6 base code. When stepping through code, rather than it compiling on demand, when one makes an adjustment to code while debugging, program is using the compiled code and the change the develop makes wouldn't take effect until the code is compiled again. GoTo's and other similar statements are no longer allowed in the .NET environment. Error trapping is done via the Try...Catch...Final blocks. MS did this stuff to force people to use more of the good programming practices, so as when debugging, it's much easier to catch things. Of course, not all of the good programming practice rules can be enforced like this as people can still use names that's not so easy to tell what they are, formatting issues, and what ever else there may be. A couple of the benefits though of VB.NET would be that it would be compiled into MSIL, so as multiple programming languages can be used for the same code (in some cases, VB wouldn't be able to be used, but more so C#), and instead of having to create multiple methods/properties, each method and property can have multiple signatures. These are just a few of the several differences between the 2 base codes. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... Why is that? Try it, I'm sure you will then work out why. Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... Why is that? I am a strong believer of good programming practice, which includes avoiding ambiguities whenever possible, which should be nearly 100% of the time, if not 100% of the time. About the only time I can see when ambiguity may not be avoided would be dealing with late binding due to other limitations and the lack of being able to bind at compile time. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... If I follow what you are trying to say, and it's a highly dubious if! Declare l_wsh As Object ' note not as worksheet Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... From within a class modules, is there a viable way to call on a public method and/or property that is setup within a worksheet module? I have setup a procedure with the following signatu Public Sub pcdInitializeWorksheet(ByRef l_objScheduleRangeNames As clsScheduleRangeNames) and within the class module that is attempting to call on it, it's setup as: l_wsh.pcdInitializeWorksheet m_objScheduleRangeNames The object variable "l_wsh" is within a For Each...Next loop, which is a worksheet object. At the time it's compiling, it's erroring out stating the above method, "pcdInitializeWorksheet", is not found. If I comment out that one line of code, everything compiles just fine. If it's not feasible to use worksheet modules as such, then I will be left with no choice but to emulate the worksheets. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling on Methods and Properties within Worksheet Modules
Yes, this is VBA, but remember, even though it's VBA, VBA in 2000, 2002, and
2003 (not sure about 2007) is still based on VB6, though obviously not the full set of VB6 codes. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... Ronald, if I may say you are making this excessively complicated :-) In your OP you said you had - "public method and/or property that is setup within a worksheet module" also you want to loop these worksheets. If your code is in worksheet modules it is "VBA", and if you want to use a worksheet Object it is part of Excel's object model. Whether thereafter you want your loop in VBA, VB6 or .Net or any other language is irrelevant. Try this simple test at the top of EACH worksheet module Public a as Long in a normal module Sub Test() dim i as long Dim obj as Object For each obj in Worksheets i = i + 10 obj.a = i debug.? obj.a next End Sub Run test and you should see 10, 20, 30 (assuming 3 worksheets) in the immediate window. . Now change 'As Object' to 'As Worksheet'. It'll fail for the same reason your code fails. Simply because ' a ' is not a property of a worksheet as defined in the relevant typelib An alternative approach, and perhaps a better one, would be to subclass your worksheets using WithEvents. Then you can include whatever additional methods and properties you wish and get the intellisense, no binding issues etc. You could maintain these classes in whatever app you are working with, eg outside workbook or even outside Excel depending on your app. Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... The only reason why I can think of doing that, the Object data type is similar to the Variant data type except it does have to refer to a class module as an object. With that, instead of the variable being bound at compile time, it would be bound at run time. While this may be of benefit, if there comes a time when MS Office goes to VB.NET base code (Not sure of MS Office 2007 is already like that or not), then I would like to spend as little time converting code over to VB.NET format. While VB.NET does allow for the Object data type, it's very restrictive on what is and what is not allowed. It's already bad enough that error trapping codes [among other adjustments] would have to be modified in so many places when going from the VB6 base code to VB.NET base code. If this happens, a lot of people will be abruptly awakened by the various restrictions of VB.NET such as can't use the Variant data type, and must explicitly declare all variables. Wouldn't be able to imply which parent object such coded variable is refering to nearly as easily as done in the VB6 base code. When stepping through code, rather than it compiling on demand, when one makes an adjustment to code while debugging, program is using the compiled code and the change the develop makes wouldn't take effect until the code is compiled again. GoTo's and other similar statements are no longer allowed in the .NET environment. Error trapping is done via the Try...Catch...Final blocks. MS did this stuff to force people to use more of the good programming practices, so as when debugging, it's much easier to catch things. Of course, not all of the good programming practice rules can be enforced like this as people can still use names that's not so easy to tell what they are, formatting issues, and what ever else there may be. A couple of the benefits though of VB.NET would be that it would be compiled into MSIL, so as multiple programming languages can be used for the same code (in some cases, VB wouldn't be able to be used, but more so C#), and instead of having to create multiple methods/properties, each method and property can have multiple signatures. These are just a few of the several differences between the 2 base codes. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... Why is that? Try it, I'm sure you will then work out why. Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... Why is that? I am a strong believer of good programming practice, which includes avoiding ambiguities whenever possible, which should be nearly 100% of the time, if not 100% of the time. About the only time I can see when ambiguity may not be avoided would be dealing with late binding due to other limitations and the lack of being able to bind at compile time. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... If I follow what you are trying to say, and it's a highly dubious if! Declare l_wsh As Object ' note not as worksheet Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... From within a class modules, is there a viable way to call on a public method and/or property that is setup within a worksheet module? I have setup a procedure with the following signatu Public Sub pcdInitializeWorksheet(ByRef l_objScheduleRangeNames As clsScheduleRangeNames) and within the class module that is attempting to call on it, it's setup as: l_wsh.pcdInitializeWorksheet m_objScheduleRangeNames The object variable "l_wsh" is within a For Each...Next loop, which is a worksheet object. At the time it's compiling, it's erroring out stating the above method, "pcdInitializeWorksheet", is not found. If I comment out that one line of code, everything compiles just fine. If it's not feasible to use worksheet modules as such, then I will be left with no choice but to emulate the worksheets. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling on Methods and Properties within Worksheet Modules
I don't get the point of that, I know it's VBA.
Whatever, does the suggested solution work for you and is the explanation understood. Regards, Peter T PS I should make it clear all of what I have written and suggested is based on my interpretation of your OP, which may not be correct) "Ronald R. Dodge, Jr." wrote in message ... Yes, this is VBA, but remember, even though it's VBA, VBA in 2000, 2002, and 2003 (not sure about 2007) is still based on VB6, though obviously not the full set of VB6 codes. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... Ronald, if I may say you are making this excessively complicated :-) In your OP you said you had - "public method and/or property that is setup within a worksheet module" also you want to loop these worksheets. If your code is in worksheet modules it is "VBA", and if you want to use a worksheet Object it is part of Excel's object model. Whether thereafter you want your loop in VBA, VB6 or .Net or any other language is irrelevant. Try this simple test at the top of EACH worksheet module Public a as Long in a normal module Sub Test() dim i as long Dim obj as Object For each obj in Worksheets i = i + 10 obj.a = i debug.? obj.a next End Sub Run test and you should see 10, 20, 30 (assuming 3 worksheets) in the immediate window. . Now change 'As Object' to 'As Worksheet'. It'll fail for the same reason your code fails. Simply because ' a ' is not a property of a worksheet as defined in the relevant typelib An alternative approach, and perhaps a better one, would be to subclass your worksheets using WithEvents. Then you can include whatever additional methods and properties you wish and get the intellisense, no binding issues etc. You could maintain these classes in whatever app you are working with, eg outside workbook or even outside Excel depending on your app. Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... The only reason why I can think of doing that, the Object data type is similar to the Variant data type except it does have to refer to a class module as an object. With that, instead of the variable being bound at compile time, it would be bound at run time. While this may be of benefit, if there comes a time when MS Office goes to VB.NET base code (Not sure of MS Office 2007 is already like that or not), then I would like to spend as little time converting code over to VB.NET format. While VB.NET does allow for the Object data type, it's very restrictive on what is and what is not allowed. It's already bad enough that error trapping codes [among other adjustments] would have to be modified in so many places when going from the VB6 base code to VB.NET base code. If this happens, a lot of people will be abruptly awakened by the various restrictions of VB.NET such as can't use the Variant data type, and must explicitly declare all variables. Wouldn't be able to imply which parent object such coded variable is refering to nearly as easily as done in the VB6 base code. When stepping through code, rather than it compiling on demand, when one makes an adjustment to code while debugging, program is using the compiled code and the change the develop makes wouldn't take effect until the code is compiled again. GoTo's and other similar statements are no longer allowed in the .NET environment. Error trapping is done via the Try...Catch...Final blocks. MS did this stuff to force people to use more of the good programming practices, so as when debugging, it's much easier to catch things. Of course, not all of the good programming practice rules can be enforced like this as people can still use names that's not so easy to tell what they are, formatting issues, and what ever else there may be. A couple of the benefits though of VB.NET would be that it would be compiled into MSIL, so as multiple programming languages can be used for the same code (in some cases, VB wouldn't be able to be used, but more so C#), and instead of having to create multiple methods/properties, each method and property can have multiple signatures. These are just a few of the several differences between the 2 base codes. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... Why is that? Try it, I'm sure you will then work out why. Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... Why is that? I am a strong believer of good programming practice, which includes avoiding ambiguities whenever possible, which should be nearly 100% of the time, if not 100% of the time. About the only time I can see when ambiguity may not be avoided would be dealing with late binding due to other limitations and the lack of being able to bind at compile time. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... If I follow what you are trying to say, and it's a highly dubious if! Declare l_wsh As Object ' note not as worksheet Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... From within a class modules, is there a viable way to call on a public method and/or property that is setup within a worksheet module? I have setup a procedure with the following signatu Public Sub pcdInitializeWorksheet(ByRef l_objScheduleRangeNames As clsScheduleRangeNames) and within the class module that is attempting to call on it, it's setup as: l_wsh.pcdInitializeWorksheet m_objScheduleRangeNames The object variable "l_wsh" is within a For Each...Next loop, which is a worksheet object. At the time it's compiling, it's erroring out stating the above method, "pcdInitializeWorksheet", is not found. If I comment out that one line of code, everything compiles just fine. If it's not feasible to use worksheet modules as such, then I will be left with no choice but to emulate the worksheets. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling on Methods and Properties within Worksheet Modules
Also, as far as using the Public keyword for declaring variables within a
class module, that doesn't work too well, if even at all. Variables within a class modules not within a method or property are declared as private variables and only exposed via public property codes (if done properly). The only type of module that I know of for public declared variables to work half way decently, but then can't use it to listen for events among other issues are within standard modules. Standard modules do have their uses though. As for your explanation of why to use Object instead of Worksheet, I understand that, but at the same time, you still go from compiling (binding) at design time to compiling (binding) the variable at run time simply cause the compiler doesn't know what it's going to be bound to when it compiled at design time, so that variable is skipped. I guess part of the reason why I tend to shy away from such issues is cause of various issues I have ran into in the past, thus why I have incorporated various programming rules, some even stricter than what the industry has. I have picked up various programming rules from various places, and even some self created. There has been times when I even broke my own self created programming rules only to run into the various issues why I had created the self created rules, thus one such reason why I'm reluctant to breaking them unless there's a very good reason for breaking them. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... I don't get the point of that, I know it's VBA. Whatever, does the suggested solution work for you and is the explanation understood. Regards, Peter T PS I should make it clear all of what I have written and suggested is based on my interpretation of your OP, which may not be correct) "Ronald R. Dodge, Jr." wrote in message ... Yes, this is VBA, but remember, even though it's VBA, VBA in 2000, 2002, and 2003 (not sure about 2007) is still based on VB6, though obviously not the full set of VB6 codes. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... Ronald, if I may say you are making this excessively complicated :-) In your OP you said you had - "public method and/or property that is setup within a worksheet module" also you want to loop these worksheets. If your code is in worksheet modules it is "VBA", and if you want to use a worksheet Object it is part of Excel's object model. Whether thereafter you want your loop in VBA, VB6 or .Net or any other language is irrelevant. Try this simple test at the top of EACH worksheet module Public a as Long in a normal module Sub Test() dim i as long Dim obj as Object For each obj in Worksheets i = i + 10 obj.a = i debug.? obj.a next End Sub Run test and you should see 10, 20, 30 (assuming 3 worksheets) in the immediate window. . Now change 'As Object' to 'As Worksheet'. It'll fail for the same reason your code fails. Simply because ' a ' is not a property of a worksheet as defined in the relevant typelib An alternative approach, and perhaps a better one, would be to subclass your worksheets using WithEvents. Then you can include whatever additional methods and properties you wish and get the intellisense, no binding issues etc. You could maintain these classes in whatever app you are working with, eg outside workbook or even outside Excel depending on your app. Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... The only reason why I can think of doing that, the Object data type is similar to the Variant data type except it does have to refer to a class module as an object. With that, instead of the variable being bound at compile time, it would be bound at run time. While this may be of benefit, if there comes a time when MS Office goes to VB.NET base code (Not sure of MS Office 2007 is already like that or not), then I would like to spend as little time converting code over to VB.NET format. While VB.NET does allow for the Object data type, it's very restrictive on what is and what is not allowed. It's already bad enough that error trapping codes [among other adjustments] would have to be modified in so many places when going from the VB6 base code to VB.NET base code. If this happens, a lot of people will be abruptly awakened by the various restrictions of VB.NET such as can't use the Variant data type, and must explicitly declare all variables. Wouldn't be able to imply which parent object such coded variable is refering to nearly as easily as done in the VB6 base code. When stepping through code, rather than it compiling on demand, when one makes an adjustment to code while debugging, program is using the compiled code and the change the develop makes wouldn't take effect until the code is compiled again. GoTo's and other similar statements are no longer allowed in the .NET environment. Error trapping is done via the Try...Catch...Final blocks. MS did this stuff to force people to use more of the good programming practices, so as when debugging, it's much easier to catch things. Of course, not all of the good programming practice rules can be enforced like this as people can still use names that's not so easy to tell what they are, formatting issues, and what ever else there may be. A couple of the benefits though of VB.NET would be that it would be compiled into MSIL, so as multiple programming languages can be used for the same code (in some cases, VB wouldn't be able to be used, but more so C#), and instead of having to create multiple methods/properties, each method and property can have multiple signatures. These are just a few of the several differences between the 2 base codes. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... Why is that? Try it, I'm sure you will then work out why. Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... Why is that? I am a strong believer of good programming practice, which includes avoiding ambiguities whenever possible, which should be nearly 100% of the time, if not 100% of the time. About the only time I can see when ambiguity may not be avoided would be dealing with late binding due to other limitations and the lack of being able to bind at compile time. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... If I follow what you are trying to say, and it's a highly dubious if! Declare l_wsh As Object ' note not as worksheet Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... From within a class modules, is there a viable way to call on a public method and/or property that is setup within a worksheet module? I have setup a procedure with the following signatu Public Sub pcdInitializeWorksheet(ByRef l_objScheduleRangeNames As clsScheduleRangeNames) and within the class module that is attempting to call on it, it's setup as: l_wsh.pcdInitializeWorksheet m_objScheduleRangeNames The object variable "l_wsh" is within a For Each...Next loop, which is a worksheet object. At the time it's compiling, it's erroring out stating the above method, "pcdInitializeWorksheet", is not found. If I comment out that one line of code, everything compiles just fine. If it's not feasible to use worksheet modules as such, then I will be left with no choice but to emulate the worksheets. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling on Methods and Properties within Worksheet Modules
For the time being, I have put in a temporary fix, at least enough for me to
get on by the issue, which I do have a deadline to meet. I will need to go back and put in a permanent fix. The temporary fix is using the "ThisWorkbook" module to expose the various elements though with some minor work arounds that I have to use in the mean time for this to work I still have not resorted to anything that could lead to ambiguities. The compiler didn't have any issues and everything ran fine at run-time. I still have other work to do, but the long-term solution to this will be to create a totally separate entity (Some may call it COM or Component Object Model), then have the workbooks reference to that separate entity. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... I don't get the point of that, I know it's VBA. Whatever, does the suggested solution work for you and is the explanation understood. Regards, Peter T PS I should make it clear all of what I have written and suggested is based on my interpretation of your OP, which may not be correct) "Ronald R. Dodge, Jr." wrote in message ... Yes, this is VBA, but remember, even though it's VBA, VBA in 2000, 2002, and 2003 (not sure about 2007) is still based on VB6, though obviously not the full set of VB6 codes. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... Ronald, if I may say you are making this excessively complicated :-) In your OP you said you had - "public method and/or property that is setup within a worksheet module" also you want to loop these worksheets. If your code is in worksheet modules it is "VBA", and if you want to use a worksheet Object it is part of Excel's object model. Whether thereafter you want your loop in VBA, VB6 or .Net or any other language is irrelevant. Try this simple test at the top of EACH worksheet module Public a as Long in a normal module Sub Test() dim i as long Dim obj as Object For each obj in Worksheets i = i + 10 obj.a = i debug.? obj.a next End Sub Run test and you should see 10, 20, 30 (assuming 3 worksheets) in the immediate window. . Now change 'As Object' to 'As Worksheet'. It'll fail for the same reason your code fails. Simply because ' a ' is not a property of a worksheet as defined in the relevant typelib An alternative approach, and perhaps a better one, would be to subclass your worksheets using WithEvents. Then you can include whatever additional methods and properties you wish and get the intellisense, no binding issues etc. You could maintain these classes in whatever app you are working with, eg outside workbook or even outside Excel depending on your app. Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... The only reason why I can think of doing that, the Object data type is similar to the Variant data type except it does have to refer to a class module as an object. With that, instead of the variable being bound at compile time, it would be bound at run time. While this may be of benefit, if there comes a time when MS Office goes to VB.NET base code (Not sure of MS Office 2007 is already like that or not), then I would like to spend as little time converting code over to VB.NET format. While VB.NET does allow for the Object data type, it's very restrictive on what is and what is not allowed. It's already bad enough that error trapping codes [among other adjustments] would have to be modified in so many places when going from the VB6 base code to VB.NET base code. If this happens, a lot of people will be abruptly awakened by the various restrictions of VB.NET such as can't use the Variant data type, and must explicitly declare all variables. Wouldn't be able to imply which parent object such coded variable is refering to nearly as easily as done in the VB6 base code. When stepping through code, rather than it compiling on demand, when one makes an adjustment to code while debugging, program is using the compiled code and the change the develop makes wouldn't take effect until the code is compiled again. GoTo's and other similar statements are no longer allowed in the .NET environment. Error trapping is done via the Try...Catch...Final blocks. MS did this stuff to force people to use more of the good programming practices, so as when debugging, it's much easier to catch things. Of course, not all of the good programming practice rules can be enforced like this as people can still use names that's not so easy to tell what they are, formatting issues, and what ever else there may be. A couple of the benefits though of VB.NET would be that it would be compiled into MSIL, so as multiple programming languages can be used for the same code (in some cases, VB wouldn't be able to be used, but more so C#), and instead of having to create multiple methods/properties, each method and property can have multiple signatures. These are just a few of the several differences between the 2 base codes. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... Why is that? Try it, I'm sure you will then work out why. Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... Why is that? I am a strong believer of good programming practice, which includes avoiding ambiguities whenever possible, which should be nearly 100% of the time, if not 100% of the time. About the only time I can see when ambiguity may not be avoided would be dealing with late binding due to other limitations and the lack of being able to bind at compile time. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... If I follow what you are trying to say, and it's a highly dubious if! Declare l_wsh As Object ' note not as worksheet Regards, Peter T "Ronald R. Dodge, Jr." wrote in message ... From within a class modules, is there a viable way to call on a public method and/or property that is setup within a worksheet module? I have setup a procedure with the following signatu Public Sub pcdInitializeWorksheet(ByRef l_objScheduleRangeNames As clsScheduleRangeNames) and within the class module that is attempting to call on it, it's setup as: l_wsh.pcdInitializeWorksheet m_objScheduleRangeNames The object variable "l_wsh" is within a For Each...Next loop, which is a worksheet object. At the time it's compiling, it's erroring out stating the above method, "pcdInitializeWorksheet", is not found. If I comment out that one line of code, everything compiles just fine. If it's not feasible to use worksheet modules as such, then I will be left with no choice but to emulate the worksheets. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calling Excel methods from a different thread (COM add-in) | Excel Programming | |||
Available methods and properties through OLE interface | Excel Discussion (Misc queries) | |||
Hidden workbook properties/methods | Excel Programming | |||
HELP! Object, Properties & Methods | Excel Programming | |||
Ctrl-J list of properties and methods | Excel Programming |