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

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

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

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

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

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

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

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