Visit my Youtube Channel
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