Constant in VBA referencing a label

Question

New to VBA. Was trying to create a constant that references a named column in my worksheet and am getting an error. Is this something you can do in VBA or is my syntax just wrong?

Example:

Public Const ColNum As Integer = [SomeColumn].Column

possible duplicate of [Assigning the value of a worksheet cell to a constant](http://stackoverflow.com/questions/23951214/assigning-the-value-of-a-worksheet-cell-to-a-constant) — L42, Jan 23 '15 at 01:50

score 2 · Accepted Answer · answered Jan 23 '15 at 01:50

2

A constant must be able to be evaluated at the time the code is compiled (ie. before it runs)

This is OK:

Const A as Long = 10 'constant value

and this:

Const B As Long = A 'from another constant

or even

Const B As Long = A * 10 'constant expression

but not this:

Const B As Long = ActiveSheet.Columns.Count 'errors

because ActiveSheet.Columns.Count can only be determined at runtime

answered Jan 23 '15 at 01:50

Tim Williams

154,628
8
97
125

OK, that's what I was thinking. Thanks for clearing that up. – KingKong Jan 23 '15 at 01:59

score 0 · Answer 2 · answered Jan 23 '15 at 01:41

The compile error tells you what's wrong: Constant expression required

In other words, as @roryap mentions, you can only use a literal value for a Constant expression, you can't assign it anything that must be evaluated at runtime. A possible workaround is to use constant strings (i.e., your range's Name) and assign elsewhere as needed

From your parent/main procedure, call on another procedure which will assign to the module-level or public variables

Option Explicit
Const MyColumnName as String = "Dave_Column"
Dim ColNum as Integer

Sub main()

    Call InitializeVariables

    'The rest of your code ...
    MsgBox ColNum

End Sub

Sub InitializeVariables()
    'Use this procedure to assign public/module scope variables if needed
    ColNum = Range(MyColumnName).Column

End Sub

Alternatively, ColNum can be a function with optional parameters, which when left blank would return the range based on the Constant string, or you could specify a different range name/address to return another column number:

Option Explicit
Const MyColumnName as String = "Dave_Column"
Sub main()

    MsgBox ColNum

    MsgBox ColNum("H1")

End Sub
Function ColNum(Optional name$) As Integer

    If name = vbNullString Then 
        name = MyColumnName
    End If

    ColNum = Range(name).Column


End Function

Note: this will fail if the named range doesn't exist :)

Constant in VBA referencing a label

2 Answers2