VBA – Abstract classes. Classes, that cannot be instantiated.

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:

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:

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:

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:

Noone takes the built-in VBE library interface seriously. Still, it looks nice, once you hit F2.

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:

JustSeeTimeCreated written in the immediate window calls the public sub with the same name.

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.

VBA – Abstract classes. Classes, that cannot be instantiated.

Related posts: