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
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
Why is that?
Try it, I'm sure you will then work out why.
Regards,
Peter T
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
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
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 signature:
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