VBA: Can a set of "constants" be configured at runtime?

Question

When writing macros for processing spreadsheets, I like to set constants at the top of the module, corrosponding to the various column numbers I'll have to use. I recently had a case where I would need to perform the exact same task on two slightly different file layouts. My solution was to turn the constants into variables, and call a configuration sub to set them depending on the file to be processed. The only thing I don't like about that solution is that what was constant, and protected against careless user (or developer(!)) tweaks to the code, is now in a variable.

Is there any way to make these configured values unchangeable in the main sub?

Original style:

Const iColUser = 1
Const iColColor = 2
Const iColPet = 3

Sub Main()
iColUser = 3 '<--This line will prevent sub from running.

New style:

Dim iColUser As Integer
Dim iColColor As Integer
Dim iColPet As Integer

Sub Config()
Select Case SpreadsheetType
    Case a
        iColUser = 1
        iColColor = 2
        iColPet = 3
    Case b
        iColUser = 3
        iColColor = 2
        iColPet = 1
End Select
End Sub
Sub Main()
iColUser = 2 '<--This line will run, and cause major damage.

Answer 1

Encapsulate them.

Add a class module, and make an abstraction over these. Abstract away the column numbering logic, exose Property Get accessors for your columns, and then another property with Get and Let accessors for the "mode", which determines the value returned by the properties.

Public Enum SpreadsheetType
    A
    B
End Enum

Private columnMode As SpreadsheetType

Public Property Get Mode() As SpreadsheetType
    Mode = columnMode
End Property

Public Property Let Mode(ByVal value As SpreadsheetType)
    columnMode = value
End Property

Public Property Get UserColumn() As Long
    Select Case Mode
        Case A
            UserColumn = 1
        Case B
            UserColumn = 3
    End Select
End Property

Public Property Get ColorColumn() As Long
    Select Case Mode
        Case A
            ColorColumn = 2
        Case B
            ColorColumn = 2
    End Select
End Property

Public Property Get PetColumn() As Long
    Select Case Mode
        Case A
            PetColumn = 3
        Case B
            PetColumn = 1
    End Select
End Property

Now to use it, you need an instance of the class. Assuming you called it Class1 (gosh don't do that!), using it would look like this:

Sub Test()

    Dim sheetColumns As New Class1

    sheetColumns.Mode = A
    Debug.Print sheetColumns.UserColumn 'outputs 1

    sheetColumns.Mode = B
    Debug.Print sheetColumns.UserColumn 'outputs 3

End Sub

The code using this object can only ever read the values, not write to them - unless you implemented a Property Let accessor for the mutable values.