Constants & Enumerations || Excel VBA Master Class || 3.2

Note: This webpage is designed to accompany the Youtube video posted above. The video offers detailed explanation on all topics; while this webpage will serve as a repository for the code presented in the video. Source code will be presented in the order of the sections within the video.

System-Defined Constants

Sub ChangeCellColor()
    wsSales.Range("A1").Interior.Color = vbBlue
    'wsSales.Range("A2").Interior.Color = RGB(0, 0, 255)
    'Debug.Print RGB(0, 0, 255)
    'wsSales.Range("A2").Interior.Color = 16711680
End Sub

User-Defined Constants

Private Const vbOrange As Long = 42495
'Public Const RED_OFFSET As Long = 1
'Public Const GREEN_OFFSET As Long = 256
'Public Const BLUE_OFFSET As Long = 65536
''Orange = RGB(255,165,0)
'Public Const vbOrange As Long = (255 * RED_OFFSET) + (165 * GREEN_OFFSET) + (0 * BLUE_OFFSET)


Sub ChangeCellColour()
    wsData.Range("C3").Interior.Color = vbOrange
End Sub

Sub OpenFile()
    Const FILE_PATH As String = "C:\Youtube\Current\VBA_Masterclass\Demo\TestFile.xlsx"
    Workbooks.Open FILE_PATH
End Sub

Enums (System-Defined)

Sub ArchiveRunStatuses()
' Copy the data set excluding the top header row
    wsData.Range("A1").CurrentRegion.Offset(1) _
                    .Resize(wsData.Range("A1").CurrentRegion.Rows.Count - 1).Copy
' Determine the next available row in the archive sheet
    Dim lrow As Long
    lrow = wsArchive.Range("A1").CurrentRegion.Rows.Count + 1
' Add the code for pasting here
    wsArchive.Range("A" & lrow).PasteSpecial xlPasteValues
End Sub

Enums (User-Defined)

Public Enum AutomationStatus
    Passed = 65280 ' RGB Green
    Failed = 255 ' RGB Red
    Paused = 16711680  'RGB Blue
End Enum

Sub ChangeCodeStatus()
    'wsData.Range("C3").Interior.Color = AutomationStatus.Failed
    Dim i As Long
    For i = 2 To wsData.Range("A1").CurrentRegion.Rows.Count
        Select Case wsData.Range("C" & i).Value
            Case "Passed": wsData.Range("C" & i).Interior.Color = AutomationStatus.Passed
            Case "Failed": wsData.Range("C" & i).Interior.Color = AutomationStatus.Failed
            Case "Paused": wsData.Range("C" & i).Interior.Color = AutomationStatus.Paused
        End Select
    Next i
End Sub