There are parts of #VBA, that can scare any VBA developer, who has not bumped into other languages. Although, the “abstract class” term is quite popular in C# and other languages, and is quite easy to be created, in VBA, this is not the fact.
Let’s first start explaining what Abstract Class is – with 1 sentence – this is a class, that cannot be instantiated. Then why do we need it? In C# and other “fancy” languages, it is quite useful, because it can be inherited from, without being instanced and this is useful, because the declared methods are virtual. Which is quite fine, because later these could be overriden and implemented in the derived class. Which is one of the cool stuff about OOP and inheritance.
What about VBA? Well, VBA has a class that cannot be in instantiated. This is the way to build it.
- Build a class with some methods and properties. Like this one:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 |
Option Explicit Private m_dtTimeCreated As Date Private Sub Class_Initialize() Debug.Print "Class is initalized" TimeCreated = Now End Sub Public Property Get TimeCreated() As Date TimeCreated = m_dtTimeCreated End Property Private Property Let TimeCreated(ByVal dtNewValue As Date) m_dtTimeCreated = dtNewValue End Property Public Function LastCol(wsName As String, Optional rowToCheck As Long = 1) As Long Dim ws As Worksheet Set ws = Worksheets(wsName) LastCol = ws.Cells(rowToCheck, ws.Columns.Count).End(xlToLeft).Column End Function Public Function LastRow(wsName As String, Optional columnToCheck As Long = 1) As Long Dim ws As Worksheet Set ws = Worksheets(wsName) LastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row End Function Public Function LastUsedColumn() As Long Dim lastCell As Range Set lastCell = ActiveSheet.Cells.Find(What:="*", _ After:=ActiveSheet.Cells(1, 1), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False) LastUsedColumn = lastCell.Column End Function Public Function LastUsedRow() As Long Dim rLastCell As Range Set rLastCell = ActiveSheet.Cells.Find(What:="*", _ After:=ActiveSheet.Cells(1, 1), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False) LastUsedRow = rLastCell.Row End Function Public Function LocateValueRow(ByVal textTarget As String, _ ByRef wksTarget As Worksheet, _ Optional col As Long = 1, _ Optional moreValuesFound As Long = 1, _ Optional lookForPart = False, _ Optional lookUpToBottom = True) As Long Dim valuesFound As Long Dim localRange As Range Dim myCell As Range LocateValueRow = -999 valuesFound = moreValuesFound Set localRange = wksTarget.Range(wksTarget.Cells(1, col), wksTarget.Cells(Rows.Count, col)) For Each myCell In localRange If lookForPart Then If textTarget = Left(myCell, Len(textTarget)) Then If valuesFound = 1 Then LocateValueRow = myCell.Row If lookUpToBottom Then Exit Function Else Decrement valuesFound End If End If Else If textTarget = Trim(myCell) Then If valuesFound = 1 Then LocateValueRow = myCell.Row If lookUpToBottom Then Exit Function Else Decrement valuesFound End If End If End If Next myCell End Function Public Function LocateValueCol(ByVal textTarget As String, _ ByRef wksTarget As Worksheet, _ Optional lngRow As Long = 1, _ Optional moreValuesFound As Long = 1, _ Optional lookForPart = False, _ Optional lookUpToBottom = True) As Long Dim valuesFound As Long Dim localRange As Range Dim myCell As Range LocateValueCol = -999 valuesFound = moreValuesFound Set localRange = wksTarget.Range(wksTarget.Cells(lngRow, 1), wksTarget.Cells(lngRow, Columns.Count)) For Each myCell In localRange If lookForPart Then If textTarget = Left(myCell, Len(textTarget)) Then If valuesFound = 1 Then LocateValueCol = myCell.Column If lookUpToBottom Then Exit Function Else Decrement valuesFound End If End If Else If textTarget = Trim(myCell) Then If valuesFound = 1 Then LocateValueCol = myCell.Column If lookUpToBottom Then Exit Function Else Decrement valuesFound End If End If End If Next myCell End Function Private Sub Increment(ByRef valueToIncrement As Variant, Optional incrementWith As Double = 1) valueToIncrement = valueToIncrement + incrementWith End Sub Private Sub Decrement(ByRef valueToDecrement As Variant, Optional decrementWith As Double = 1) valueToDecrement = valueToDecrement - decrementWith End Sub |
- Then export the class to a text file (sorry for the German, my Excel is not in English, but “Export” is quite understandable):
- Open the text file and change the VB_PredeclaredId = True. It should look like this:
|
1 2 3 4 5 6 7 8 9 10 11 |
VERSION 1.0 CLASS BEGIN MultiUse = -1 'True END Attribute VB_Name = "xl" Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = False Attribute VB_PredeclaredId = True Attribute VB_Exposed = True Option Explicit 'Public Function LastCol()... |
- Now save and import back the txt file to the VBEditor. Override the changes. And voila, you are allowed to use the class without instancing it, in a module like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Option Explicit Public Sub Main() Debug.Print xl.LastCol(Tabelle1.Name) Debug.Print xl.LastCol(Tabelle1.Name) Debug.Print xl.LastUsedColumn Debug.Print xl.LastUsedRow Debug.Print xl.LocateValueCol("b", Tabelle1) Debug.Print xl.LocateValueRow("c", Tabelle1) Debug.Print xl.TimeCreated End Sub Public Sub JustSeeTimeCreated() Debug.Print xl.TimeCreated End Sub |
This is how the library looks like in the VBEditor:
And because of the nice usage of Private and Public, some of the internal functins (like Dearement and Increment) are not visible when we are calling the class:
Although the class cannot be instantiated, it gets created and it has a constructor (in VBA the constructor is only one, built-in, unless some factory method is used). Thus, in our class we have the Class_Initialize() constructor, that prints “Class is initialized” and gets the time. If we do not kill the VBA by closing Excel or writing “END” in the direct window, this time would stay the same, even if we call the class after 1 hour:
Creating a class, that cannot be instantiated is quite useful. With a simple “xl.LastRow(nameOfWorksheet)” you would always know the name of the worksheet. And things may get well sorted this way. Your way!
Pretty much, this is all. The code is mainly in the article and here.