So far I've been declaring my constants in the "Main" module for my workbook, below the "Option Explicit" but above the "Sub DoTheWork()."
Do public constants have to be declared outside the sub like this or can they be declared at any point in the script?
Examples:
Current method:
Option Explicit
Public Const Example as Integer = 1
Private Sub DoTheWork(ByVal Target as Range)
Questionable method:
Option Explicit
Private Sub DoTheWork(ByVal Target as Range)
Public Const Example as Integer = 1
Dim Example2 as Integer
Example2 = 2
Is the second method viable? Is it bad practice? Is it worth trying?
(1) You need to understand the scope of constants (and variables).
If you want to define a constant that is shared among several routines, you need to declare it at the top of the code as you do in your first example.
Declaring it as Public or Global makes a constant available for every routine in any module within a VBA project (=Workbook in Excel or document in Word). Declaring it without makes them available only inside that module.
Declaring a constant inside a routine (function or sub) makes it local to that routine. The keywords public or global are not allowed, the compiler will complain with "invalid attribute"
(2) If you are working on a larger project and define several global constants (and/or variables), consider to put them into a separate module.
(3) In VBA, you can't use a function call to declare a constant value. The following statements are not allowed:
Const quoteChar = chr(34)
Const warningColor = RGB(255, 192, 0)
Instead, you need to write
Const quoteChar = """"
Const warningColor = 49407 ' (or &HC0FF&)
As an alternative, you could define a One-Liner function
Public Function warningColor As Long
warningColor = RGB(255, 192, 0)
End Function