Excel VBA Class Module: Is it possible to create a "personalized" Range Object?-CodePudding

Is it possible to create a class module in Excel VBA, called, say, myRange, which behave exactly like an Excel.Range object, but in which we can add new methods and properties?

For example

dim rg1 as Excel.Range, rg2 as myRange
set rg1 = ActiveSheet.Cells(1,1)
set rg2 = ActiveSheet.Cells(2,1)

With both objects, we could use all methods and properties of the Range object, using VBE Intellisense.

But, with rg2, we would have additional melhods and properties, created inside the myRange class module, and those new methods and properties, as well, could be seen in the Intellisense VBE drop-down list.

Examples of new properties:

TotWords = rg2.TotalOfWordsInMyCells
if Color = 2 then rg2.PaintMeYellow

Does Excel VBA have such capabilities?

CodePudding user response：

You can't really "extend" Range - you can create a "wrapper" class which holds an internal reference to a range, but if you wanted all existing Range methods exposed via that class you'd need to replicate them in your class. Or have your class expose the contained range via its own Range property, so you could use that for existing methods. i.e. you can use rg2.PaintMeYellow (custom method) and rg2.Range.ClearContents (existing Range method).

Basic example:

Class myRange:

Option Explicit

Private m_rng As Range

Sub Init(rng As Range)
    Set m_rng = rng
End Sub

Public Sub PaintMeYellow()
    Me.Range.Interior.Color = vbYellow
End Sub

Property Get Range() As Range
    Set Range = m_rng
End Property

Example usage:

Sub Tester()
    
    Dim rng As New myRange
    
    rng.Init ActiveSheet.Range("A1:B10") 'set the range reference
    
    rng.PaintMeYellow          'call a custom method
    
    rng.Range.ClearContents    'call built-in method on contained range
    
End Sub