VBA Class Module as WorkSheet

Question

Please consider the following "code":

Sub MySub()
  Dim MySheet As Worksheet
  Set MySheet = ActiveSheet
  MySheet.DeleteAllRedWords 'This is a Sub
  MsgBox MySheet.NumberOfChangesThisWeek 'This is a function
  MySheet.ActiveOwner = "Sam" 'This is a property
End Sub

Is this possible? Would class modules do the trick? I tried the code below, but I got an error 438 (Object doesn't support this property or method). Is it possible somehow?

'CLASS MODULE CODE: MyWorkingSheet Class
Private Sub class_initialize()
  Me = ActiveSheet
End Sub

'NORMAL MODULE CODE
Sub MySub()
  Dim MyTodaySheet As MyWorkingSheet
  Set MyTodaySheet = New MyWorkingSheet
End Sub

Tim Williams · Accepted Answer · 2018-04-15T22:10:43.293

1

Sub MySub()

  Dim MySheet As New MyWorkingSheet

  Set MySheet.Sheet = ActiveSheet

  MySheet.DeleteAllRedWords

  'etc

End Sub

Class:

'CLASS MODULE CODE: MyWorkingSheet Class
Private m_sht As WorkSheet

'set a reference to the worksheet you want to "wrap" with your class
Property Set Sheet(sht As WorkSheet)
    Set m_sht = sht
End Property

Sub DeleteAllRedWords()
    'in all your class methods reference m_sht
    With m_sht.UsedRange
        'code to delete all red words
    End With
End Sub

'other methods/functions

edited Apr 15 '18 at 22:10

answered Apr 15 '18 at 22:08

Tim Williams

154,628
8
97
125

Tim, since my object is a worksheet, would it be possible to the intellisense recognize my created methods and the standard excel worksheet methods at the same time and show all of them to me on the dropdown list? – sergio trajano Apr 18 '18 at 12:34
1

Your object isn't a worksheet though, it just contains one. If you want the Intellisense for the sheet object, you'll need to create a Get `Sheet` property (of return type Worksheet) and use `MySheet.Sheet.UsedRange` (for example) – Tim Williams Apr 18 '18 at 14:54

VBA Class Module as WorkSheet

1 Answers1