Constant Declaration Conventions

Question

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?

Public Const Example as Integer = 1 placement is invalid in the 2nd snippet. — BigBen
– BigBen, Commented Mar 31 at 20:29
It is a compile error: i.sstatic.net/fdLX146t.png. It will compile after removing the Public ... but your question is about public constants. — BigBen
– BigBen, Commented Mar 31 at 20:33
I am asking if the second method is viable. The error you are getting leads me to believe the error is the answer I'm looking for - the second option is not viable and I should not attempt it. — mkcoehoorn
– mkcoehoorn, Commented Mar 31 at 20:43
Module-level Constants always go at the top of the module, before any methods. You cannot mark a constant inside a method as either Public or Private - the compiler will not allow it. Any constant declared within a method is restricted in scope to only that method. learn.microsoft.com/en-us/office/vba/language/concepts/… — Tim Williams
– Tim Williams, Commented Mar 31 at 21:27

FunThomas · Accepted Answer · 2025-04-01 09:25:37Z

1

(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

answered Apr 1 at 9:25

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mkcoehoorn Apr 1 at 12:30

Thank you. I'm 20 years out of practice at programming so I remembered to put the constants at the beginning, I just didn't remember why I needed to do it.

Collectives™ on Stack Overflow

Constant Declaration Conventions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related