How to extend a standard class (like Worksheet)

  • Thread starter Thread starter mike
  • Start date Start date
M

mike

Hi to all,

After I tried to implement by myself ... I realize that is
a bit of more complicated so I ask anyone who knows how to
do'it just to tell me:
How can be implemented a subclass of a built-in class like
Worksheet, if it can be? This, in order to add new events,
for example!

Thanks in advance
Mike
 
Have you tried creating a new Class and making
Worksheet one of its properties? E.g., with code
such as follows in the Class Module:

Private m_WS As Worksheet

Public Property Let WS(ByVal vData As Worksheet)
Set m_WS = vData
End Property

Public Property Get WS() As Worksheet
Set WS = m_WS
End Property

HTH,
Merjet
 
Hi Merjet,
No, I didn't try to do this.
As it seems to me, this won't be a real extension (e.g.
adding new events and properties to a class). You get only
access to an object of Worksheet type. What I want to do
is to add mouse events to the Worksheet class. But, again,
maybe I'm wrong.
I've tried to use "implements ..." with Sheet1 object, but
I've got stuck to the definition of "_CodeName" property,
which I don't know how to do it. VBA gets me "Character
not valid" for "_".

Thanks anyway for help.
mike
 
mike said:
Hi to all,

After I tried to implement by myself ... I realize that is
a bit of more complicated so I ask anyone who knows how to
do'it just to tell me:
How can be implemented a subclass of a built-in class like
Worksheet, if it can be? This, in order to add new events,
for example!

Thanks in advance
Mike
Click to expand...

Hey, I was going to post exactly the same question here!
I cross-posted to because
perhaps some of the Word- or Outlook-gurus can help us out here?
 
Hi Mike,
No, I didn't try to do this.
As it seems to me, this won't be a real extension (e.g.
adding new events and properties to a class). You get only
access to an object of Worksheet type. What I want to do
is to add mouse events to the Worksheet class. But, again,
maybe I'm wrong.
Click to expand...

What I suggested trying was only a start. You could add
any other properties or methods you want to the Class.

HTH,
Merjet
 
...
As it seems to me, this won't be a real extension (e.g.
adding new events and properties to a class).
Click to expand...

You can add Public (or Friend) properties and methods to the Sheet1
code module e.g.

Public Property Get MyProperty() As String
MyProperty = "Blah"
End Property

You can even add and raise a public event in the Sheet1 code module
(but who sees it?)

It seems to have a hidden line:

Private WithEvents Worksheet As AMuchMoreComplexAnimal

Elsewhere, there seems to be a single Sheet1 object variable. You can
call e.g. the property added above wherever this object variable is in
scope.

Sheet1 is a bit like UserForm1 in that it of type UserForm but also a
class in its own right i.e. you can add custom properties, methods and
events. Unlike UserForm1, however, you can't create an instance of
Sheet1:

Dim oSheet1 As Sheet1
Set oSheet1 As New Sheet1

The second line (note *only* the second line) gives a compile error.
So its instancing type must be GlobalMultiUse. But it has no
Initialize nor Terminate event, so it isn't even a class.

So what is Sheet1? It's not a Worksheet, because this fails:

Public Function SayBlah(ByVal oSheet As Worksheet)
MsgBox oSheet.MyProp
End Function

But change to:

Public Function SayBlah(ByVal oSheet As Object)
MsgBox oSheet.MyProperty
MsgBox TypeName(oSheet)
End Function

Public Sub test()
SayBlah Sheet1
End Sub

And this time it works. But now it says the object type is Worksheet!

I trust this clarifies things <g>.

Jamie.

--
 
Ok,
I tried to add some property to sheet1 (through Implements
Sheet1 in a class module) but I've been asked to define
everything, even properties as _CodeName, which definition
is an enigma for me. How to do'it? I cannot define a
property with that name in my class module because it
gives me an error (VBA doesn't know how to handle
properties with underscore at the beginning of the prop-
name).

So I'm stuck!

Thanks anyway for help. If you have any other idea please
let me know!

mike
 
...
Ok,
I tried to add some property to sheet1 (through Implements
Sheet1 in a class module) but I've been asked to define
everything, even properties as _CodeName, which definition
is an enigma for me.
Click to expand...

From the help for the Implements statement, "An interface is a
collection of prototypes representing the members (methods and
properties) the interface encapsulates; that is, it contains only the
declarations for the member procedures." Why do you think Sheet1 is a
suitable interface?

Jamie.

--
 
Well,
because it's the only one which I've found available to
implement. I tried also with Worksheet but it doesn't see
it.
So, in my lack of documentation, I tried to implement what
was of more interest to me. That's because I intend to add
some mouse events to the Worksheet level.

But this doesn't ask to my question: "How can be (if it
can) implemented a property like _CodeName".
Could be that you know another one to implement, more
suitable to this.
 
...
Well,
because it's the only one which I've found available to
implement. I tried also with Worksheet but it doesn't see
it.
So, in my lack of documentation, I tried to implement what
was of more interest to me. That's because I intend to add
some mouse events to the Worksheet level.
Click to expand...

Documentation is on MSDN e.g.

http://msdn.microsoft.com/library/d...l/vbconhowvisualbasicprovidespolymorphism.asp

"Most object-oriented programming systems provide polymorphism through
inheritance... Visual Basic doesn't use inheritance to provide
polymorphism. Visual Basic provides polymorphism through multiple
ActiveX interfaces."

Jamie.

--
 
Ok,
I've already understood this but, again, I've tried to
implement (not to extend) an interface like Sheet1's.
Arrived to the member _CodeName I was in impossibility of
implementing it in my class (which, again, was
implementing (!) the Sheet1 interface) because VBA says
that "cannot use _ as the first character in variable
name".
There is any workaround? Worksheet cannot be implemented
as an interface.
mike
 
...
I've tried to
implement (not to extend) an interface like Sheet1's.
Arrived to the member _CodeName I was in impossibility of
implementing it in my class (which, again, was
implementing (!) the Sheet1 interface) because VBA says
that "cannot use _ as the first character in variable
name".
There is any workaround? Worksheet cannot be implemented
as an interface.
Click to expand...

I'm now not clear on what you are using as an interface: Sheet1 or "an
interface like Sheet1's"?

My interfaces are VB class modules containing just the declarations of
the properties and methods.

I've never tried implementing an interface created any other way. I
assume the Sheet1 object/class is not a VB class module containing
just member declarations, I doubt it is even a type library, so I
assume it cannot be used with the Implements statement.

Perhaps you should post your code so we can see what you are
attempting.

Jamie.

--
 
Back
Top