excel - Constant Declaration Conventions - Stack Overflow

admin管理员组

文章数量:1353256

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?

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?

• excel
• vba
• constants

Share Improve this question Follow edited Mar 31 at 20:21 mkcoehoorn asked Mar 31 at 19:57 mkcoehoornmkcoehoorn 533 bronze badges 7

• Side note: your second snippet doesn't compile. – BigBen Commented Mar 31 at 20:07
• 1 Public Const Example as Integer = 1 placement is invalid in the 2nd snippet. – BigBen Commented Mar 31 at 20:29
• 1 It is a compile error: i.sstatic/fdLX146t.png. It will compile after removing the Public ... but your question is about public constants. – BigBen Commented Mar 31 at 20:33
• 1 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 Commented Mar 31 at 20:43
• 4 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/en-us/office/vba/language/concepts/… – Tim Williams Commented Mar 31 at 21:27

 |  Show 2 more comments

1 Answer 1

Sorted by: Reset to default 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

本文标签： excelConstant Declaration ConventionsStack Overflow