CODEWITHSUNDEEP
vbaconstants
KingKong
KingKong

Reputation: 421

Constant in VBA referencing a label

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

Upvotes: 0

Views: 403

Answers (2)

Tim Williams
Tim Williams

Reputation: 166351

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

Upvotes: 2

David Zemens
David Zemens

Reputation: 53623

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 :)

Upvotes: 0

Related Questions