Learn X in Y minutes where X = VBA

clsPerson.cls

Option Explicit

Public FirstName As String
Public LastName As String
Private p_yob As Integer
Event YobChanged(ByVal newValue As Integer)



Public Property Get FullName() As String
    ' Me is the reference to the current instance
    FullName = Me.FirstName & " " & Me.LastName
End Property

Public Property Get Age() As Integer
    Age = Year(Now()) - Me.Yob
End Property

Public Property Get Yob() As Integer
    Yob = p_yob
End Property

Public Property Let Yob(ByVal val As Integer)
    If val < 0 Then
        Call err.Raise(vbObjectError + 9000, "person", "Invalid year of birth")
    End If
    p_yob = val
    
    RaiseEvent YobChanged(val)
End Property


Public Sub PrintInfo()
    Debug.Print (Me.FullName & " is " & Me.Age & " years old")
End Sub

LEARN_X_IN_Y_MINUTES_VBA.md

Learn X In Y minutes where X = VBA

Start with modMain.bas

Full repo: https://github.com/mihai-vlc/learnxinyminutes-vba

modFactory.bas

Option Explicit

' We don't have static methods/constructors so we use a factory module for this purpose
Public Function CreatePerson(ByVal FirstName As String, ByVal LastName As String, ByVal Yob As Integer) As clsPerson
    Set CreatePerson = New clsPerson
    CreatePerson.FirstName = FirstName
    CreatePerson.LastName = LastName
    CreatePerson.Yob = Yob
End Function

modFileSystem.bas

Option Explicit
' TODO error handling
Public Sub WriteTextFile(ByVal filePath As String, ByVal content As String)
    Dim txt_file As Integer
    ' Determine the next available file number to be used by the FileOpen function
    txt_file = FreeFile
    
    Open filePath For Output As txt_file
    Print #txt_file, content
    Close txt_file
End Sub

Public Sub AppendTextFile(ByVal filePath As String, ByVal content As String)
    Dim txt_file As Integer
    ' Determine the next available file number to be used by the FileOpen function
    txt_file = FreeFile
    
    Open filePath For Append As txt_file
    Print #txt_file, content
    Close txt_file
End Sub

Public Function ReadTextFile(ByVal filePath As String) As String
    Dim txt_file As Integer
    Dim content As String

    ' Determine the next available file number to be used by the FileOpen function
    txt_file = FreeFile
    
    Open filePath For Input As txt_file
    content = Input(LOF(txt_file), txt_file)
    Close txt_file
    
    ' Return
    ReadTextFile = content
End Function

modLogger.bas

Option Explicit

Public Enum LogLevel
    LL_DEBUG = 0
    LL_INFO = 1
    LL_WARN = 2
    LL_ERROR = 3
    LL_FATAL = 4
End Enum

Public ActiveLogLevel As LogLevel

Public Sub LogMsg(ByVal msg As String, Optional ByVal level As LogLevel)

    If level >= ActiveLogLevel Then
        Debug.Print (GetLogLevelName(level) & " : " & msg)
    End If

    If level = LL_FATAL Then
        End ' End execution of ALL instructions and clear variables
    End If

End Sub

Private Function GetLogLevelName(ByVal level As LogLevel)
    Dim result As String

    Select Case level
        Case LL_DEBUG
            result = "DEBUG"
        Case LL_INFO
            result = "INFO"
        Case LL_WARN
            result = "WARN"
        Case LL_ERROR
            result = "ERROR"
        Case LL_FATAL
            result = "FATAL"
        Case Else
            result = "INVALID"
    End Select
    
    GetLogLevelName = result
End Function

modMain.bas

Option Explicit ' Forces all variables to be defined

' Comments start with a quote

' Each sub is considered a macro and it can be assigned to a button/ui element
' It doesn't return any value
Public Sub Main()
    ' Press F5 to run the code from the current sub

    ' View -> Immediate Window to see the results
    Debug.Print ("Hello World")
    ' Select all and Delete to clear the immediate window
    
    ' Strings use double quotes, use & for concatenation
    Debug.Print ("4 + 7 = " & Add(4, 7))

    modLogger.ActiveLogLevel = modLogger.LL_WARN

    ' Call modLogger.LogMsg("Stop the program", LL_FATAL)
    Call modLogger.LogMsg("Hello", LL_DEBUG)
    Call modLogger.LogMsg("Hello", LL_INFO)
    Call modLogger.LogMsg("Hello", LL_WARN)
    Call modLogger.LogMsg("Hello", LL_ERROR)


    ' using call makes it clear it's a sub
    Call DeclareVariables
    Call Operators
    Call EarlyReturn
    Call HandleErrors
    Call ControlStructures
    Call CollectionAndDictionary
    Call WorkingWithFiles
    
    ' For 3rd party libraries see https://github.com/sancarn/awesome-vba

End Sub

' functions return values, use ByVal until you need ByRef
Private Function Add(ByVal a As Integer, ByVal b As Integer) As Integer
    Dim result As Integer
    
    result = a + b

    ' Return uses the name of the function
    Add = result
End Function


Private Sub DeclareVariables()
    Debug.Print ("---- DECLARE VARIABLES ----")
    Dim x As Integer
    x = 42
    
    ' intialized as false
    Dim flag As Boolean
    flag = True ' or False

    Debug.Print (x & " " & flag)

    Dim p1 As clsPerson
    ' For objects use Set
    Set p1 = modFactory.CreatePerson("Mihai", "Vilcu", 1990)
    
    Debug.Print (p1.FullName & " is " & p1.Age & " years old")

    ' For all data types see https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary

End Sub

Private Sub Operators()
    Debug.Print ("---- OPERATORS ----")
    
    Debug.Print ("7 = 10 " & (7 = 10)) ' equal
    Debug.Print ("7 <> 10 " & (7 <> 10)) ' not equal
    Debug.Print ("Not 7 = 10 " & (Not 7 = 10)) ' negation
    Debug.Print ("7 < 10 " & (7 < 10))
    Debug.Print ("7 > 10 " & (7 > 10))
    Debug.Print ("7 <= 10 " & (7 <= 10))
    Debug.Print ("7 >= 10 " & (7 >= 10))

    Debug.Print ("7 + 12 " & (7 + 12))
    Debug.Print ("7 - 12 " & (7 - 12))
    Debug.Print ("7 * 12 " & (7 * 12))
    Debug.Print ("7 / 12 " & (7 / 12))
    Debug.Print ("70 \ 12 " & (70 \ 12)) ' integer division
    Debug.Print ("7 Mod 12 " & (7 Mod 12))
    Debug.Print ("2 ^ 4 " & (2 ^ 4)) ' exponential
    Debug.Print ("-3 " & (-3)) ' negation

    Debug.Print ("7 < 12 And 12 > 40 " & (7 < 12 And 12 > 40))
    Debug.Print ("7 < 12 Or 12 > 40 " & (7 < 12 Or 12 > 40))

    Debug.Print ("7 < 12 Or DoesNotHaveShortCircuit() " & (7 < 12 Or DoesNotHaveShortCircuit())) ' no short circuit, use guard clauses instead (see EarlyReturn)

End Sub

Private Function DoesNotHaveShortCircuit()
    Debug.Print ("DoesNotHaveShortCircuit called")
    DoesNotHaveShortCircuit = True
End Function

Private Sub EarlyReturn()
    Debug.Print ("---- EARY RETURN/GUARD CLAUSES ----")

    Dim n As Integer
    n = modRandom.RandInt(1, 50)

    If n < 25 Then
        Debug.Print ("Early return for " & n)
        Exit Sub ' Works for functions as well
    End If

    Debug.Print ("No early return for " & n)
End Sub

Private Sub HandleErrors()
    Debug.Print ("---- HANDLE ERRORS ----")
    On Error GoTo ProcessOnError
    
    Dim p2 As clsPerson
    ' For objects use Set, you can pass parameters by name
    Set p2 = modFactory.CreatePerson(FirstName:="John", LastName:="Doe", Yob:=-6)
    Call p2.PrintInfo
    
    Exit Sub

ProcessOnError:
    Call MsgBox("modMain: Number = " & err.Number & " " & err.Description)
End Sub

Private Sub ControlStructures()
    Debug.Print ("---- CONTROL STRUCTURES ----")
    
    Dim n As Integer
    n = modRandom.RandInt(1, 500)
    
    If n <> 3 Then ' not equal
        Debug.Print ("n is not 3")
    Else
        Debug.Print ("n is something else " & n)
    End If
    
    Dim i As Integer
    For i = 0 To 6 Step 2
        Debug.Print ("i = " & i) ' 0, 2, 4, 6
    Next
    
    ' List of primitives
    Dim myList As New Collection
    myList.Add ("A")
    myList.Add ("B")
    myList.Add ("C")
    
    Dim Item As Variant
    For Each Item In myList
        Debug.Print ("item = " & Item)
    Next
    
    ' List of objects
    Dim allPersons As New Collection
    Call allPersons.Add(modFactory.CreatePerson("John", "Doe", 1900))
    Call allPersons.Add(modFactory.CreatePerson("Michael", "Smith", 1950))
    Call allPersons.Add(modFactory.CreatePerson("Maria", "Doe", 1980))

    Dim currentPerson As clsPerson
    For Each currentPerson In allPersons
        Call currentPerson.PrintInfo
    Next
    
End Sub

Private Sub CollectionAndDictionary()
    Debug.Print ("---- COLLECTION AND DICTIONARY ----")
    
    Dim nums As New Collection

    Call nums.Add(100)
    Call nums.Add(150)
    Call nums.Add(200)

    Dim n As Variant
    For Each n In nums
        Debug.Print ("n = " & n)
    Next

    ' Need to add microsoft scripting runtime in references
    ' For cross platform support use https://github.com/VBA-tools/VBA-Dictionary
    Dim codeToName As New Dictionary

    Call codeToName.Add(100, "INFO")
    Call codeToName.Add(200, "OK")
    Call codeToName.Add(300, "REDIRECT")
    Call codeToName.Add(400, "CLIENT ERROR")
    Call codeToName.Add(500, "SERVER ERROR")
    
    If codeToName.Exists(200) Then
        Call codeToName.Add(202, "CREATED")
    End If

    Dim key As Variant
    For Each key In codeToName
        Debug.Print ("key = " & key & " = " & codeToName.Item(key))
    Next
    
    
End Sub


Private Sub WorkingWithFiles()
    Debug.Print ("---- FILES AND FOLDERS ----")
    Call modFileSystem.WriteTextFile("C:\tmp\result.txt", "Hello from VBA")
    Call modFileSystem.WriteTextFile("C:\tmp\result.txt", "Hello from VBA2") ' overwrite the existing content
    
    Call modFileSystem.AppendTextFile("C:\tmp\result.txt", "This text is appended") ' new line is added automatically
    Call modFileSystem.AppendTextFile("C:\tmp\result.txt", "More appended text")
    
    Dim content As String
    content = modFileSystem.ReadTextFile("C:\tmp\result.txt")

    Debug.Print (content)
End Sub

modRandom.bas

Option Explicit

Public Function RandInt(ByVal min As Integer, ByVal max As Integer)
    ' Click inside a word an press F1 to open the documentation
    Call Randomize

    RandInt = Int((max - min + 1) * Rnd() + min)
End Function

Sheet1.cls

Private WithEvents activeUser As clsPerson

Private Sub Worksheet_Activate()
    Set activeUser = modFactory.CreatePerson("John", "Doe", 1990)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    activeUser.Yob = modRandom.RandInt(1990, 2010)
End Sub

Private Sub activeUser_YobChanged(ByVal newValue As Integer)
    Debug.Print ("Yob changed to " & newValue)
End Sub