Adding new method or property to existing Classes (like Range)

Hello Dear Excel Help Forum

Is it possible to extend default classes?
I know I can extend ThisWorkbook class.

Workbook: Declare variables and add methods and properties in the workbook code module

Objects like range: Create a class named, say, ExtRng, declare its public variable, say, rng As Range, and write its methods and properties. When you want to work with an "extended range object", do it this way:

Please Login or Register  to view this content.

and you can use methods and properties of eR; your original range is called as eR.rng.

Jan thank you. This would suffice. Though being able to extend default classes without the need of declaring new class would be more convenient.

I agree it would be more convenient. Maybe, simple tricks exist but I don't know any. The first thing to come in mind is to use inheritance but it is sooo awful in VBA...

The new-class trick possesses some advantages:

1. If you frequently use, say, Range.Interior.ColorIndex, you can shorten it in the ExtRng class with your own property in the class code body:

Please Login or Register  to view this content.

and wite
eR.rClrI = ...
instead of
the_range.Interior.ColorIndex = ...

2. You can override the range default property (Value) with the property you like. For instance, you simply add a line to the upper code:

Please Login or Register  to view this content.

which makes the property (both Get and Let parts) the default one, and you can write eR=... instead of eR.rClr=... then. The best idea is not to make a property, but the variable rng default:

Please Login or Register  to view this content.

and use simple eR instead of eR.rng then.

The lines 'Attribute...' must be written in an external editor; if you write them in VBA, they won't be recognized. Export the class from workbook, add the 'Attribute...' line, say, via the NotePad, save it and import it back into the workbook.

Nice, maybe someday I will write a routine for exporting adding line and importing it again.