Can an array be declared as a constant?

Question

Is it possible to either:

Declare an array as a constant

OR
Use a workaround to declare an array that is protected from adding, deleting or changing elements, and therefore functionally constant during the life of a macro?

Of course I could do this:

Const myConstant1 As Integer = 2
Const myConstant2 As Integer = 13
Const myConstant3 As Integer = 17
Const myConstant4 ...and so on

...but it loses the elegance of working with arrays. I could also load the constants into an array, and reload them each time I use them, but any failure to reload the array with those constant values before use could expose the code to a "constant" value that has changed.

Any workable answer is welcome but the ideal answer is one that can be setup once and not require any changes/maintenance when other code is modified.

ChrisB · Accepted Answer · 2025-03-19 17:17:40Z

43

You could use a function to return the array and use the function as an array.

Function ConstantArray()
    ConstantArray = Array(2, 13, 17)
End Function

edited Mar 19 at 17:17

ChrisB

3,2735 gold badges43 silver badges68 bronze badges

answered Dec 8, 2016 at 18:32

user6432984

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

12 Comments

Mathieu Guindon Over a year ago

Leveraging the fact that the syntax for function calls and array calls is identical/ambiguous... lovely! Should probably be ConstantArray though (typo!). (PS - that's evil!)

user6432984 Over a year ago

@ChrisB getRateMultipliers would be better. get implies a function and the plural implies an array.

SlowLearner Over a year ago

Brilliant (and I had no idea that it is possible to run a For Loop in the Immediate window)!

PC_Goldman - SE is rotting Over a year ago

Came here to find out about constant arrays and discovered join() and Rubberduck!

Jens Over a year ago

@GiovanniDiToro I just compared using a function as a constant array with just declaring the array in the sub. I performed some maths and functions with the array and looped the code 5 000 000 times. The result: Using a function as an array took 122 seconds, declaring the array in the sub took 3 seconds. So it's quite a lot slower. But in the right context it could work. Looping 500 times the difference is 0.0122 vs 0.0003.

|

A.S.H · Accepted Answer · 2016-12-08 18:37:15Z

12

How about making it a function? Such as:

Public Function myConstant(ByVal idx As Integer) As Integer
    myConstant = Array(2, 13, 17, 23)(idx - 1)
End Function

Sub Test()
    Debug.Print myConstant(1)
    Debug.Print myConstant(2)
    Debug.Print myConstant(3)
    Debug.Print myConstant(4)
End Sub

Nobody can change it, resize it, or edit its content... Moreover, you can define your constants on just one line!

edited Dec 8, 2016 at 18:37

answered Dec 8, 2016 at 18:22

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Comments

S Meaden · Accepted Answer · 2018-04-17 22:43:08Z

10

If the specific VBA environment is Excel-VBA then a nice syntax is available from the Excel Application's Evaluate method which can be shortened to just square brackets.

Look at this

Sub XlSerialization1()
    Dim v
    v = [{1,2;"foo",4.5}]

    Debug.Assert v(1, 1) = 1
    Debug.Assert v(1, 2) = 2
    Debug.Assert v(2, 1) = "foo"
    Debug.Assert v(2, 2) = 4.5

    '* write all cells in one line
    Sheet1.Cells(1, 1).Resize(2, 2).Value2 = v
End Sub

answered Apr 17, 2018 at 22:43

S Meaden

8,3203 gold badges39 silver badges74 bronze badges

3 Comments

Excel Hero Over a year ago

This is the correct answer to this Excel VBA question. It should have the most votes and be the accepted answer.

johny why Over a year ago

I love this formula-style syntax! But, sadly, it can't be used for a constant const x = [{1,2,3}]

ChrisB Mar 19 at 17:21

The downside to this method is that it uses a variable that can be changed. There is no protection from adding, deleting or changing elements.

Soulfire · Accepted Answer · 2016-12-08 18:21:38Z

8

I declared a String constant of "1,2,3,4,5" and then used Split to create a new array, like so:

Public Const myArray = "1,2,3,4,5"

Public Sub createArray()

        Dim i As Integer
        A = Split(myArray, ",")

        For i = LBound(A) To UBound(A)
                Debug.Print A(i)
        Next i

End Sub

When I tried to use ReDim or ReDim Preserve on A it did not let me. The downfall of this method is that you can still edit the values of the array, even if you can't change the size.

answered Dec 8, 2016 at 18:21

Soulfire

4,3161 gold badge25 silver badges34 bronze badges

2 Comments

David Zemens Over a year ago

If you Dim A() As String you can ReDim or ReDim Preserve it later. This requires that you strongly type your Const myArray As String = "1,2,3,4,5".

SixSigmaGuy Over a year ago

Genius solution to a problem I've had for years. Thank you!! I'm disappointed in myself for not thinking of it. :-( Everyone suggests using a function instead of a const, but I want to be able to maintain my arrays like I can in C/C++ at design time.

Pankaj Jaju · Accepted Answer · 2016-12-08 18:20:14Z

4

Can an array be declared as a constant? No.

Workarounds - Simplest one I can think of is to define a constant with delim and then use Split function to create an array.

Const myConstant = "2,13,17"

Sub Test()
    i = Split(myConstant, ",")

    For j = LBound(i) To UBound(i)
        Debug.Print i(j)
    Next
End Sub

answered Dec 8, 2016 at 18:20

Pankaj Jaju

5,5112 gold badges29 silver badges43 bronze badges

Comments

Pragmateek · Accepted Answer · 2019-01-30 18:52:41Z

4

If you don't need a new instance each time you can use a Static local variable to avoid multiple objects creation and initialization:

Private Function MyConstants()
    Static constants As Variant

    If IsEmpty(constants) Then
        constants = Array(2, 13, 17)
    End If

    MyConstants = constants
End Function

answered Jan 30, 2019 at 18:52

Pragmateek

13.6k9 gold badges80 silver badges112 bronze badges

Comments

DaveyDaveDave · Accepted Answer · 2019-03-03 21:53:48Z

3

Is this too simplistic?

PUBLIC CONST MyArray = "1,2,3,4"

then later in a module:

Dim Arr as Variant

SET Arr = split(MyArray,",")

edited Mar 3, 2019 at 21:53

DaveyDaveDave

10.7k14 gold badges69 silver badges82 bronze badges

answered Mar 3, 2019 at 21:29

Webrohm

314 bronze badges

1 Comment

Tony M Over a year ago

This simple solution works for me. Could also define Arr as a string array (Dim Arr() as string).

pdanes · Accepted Answer · 2021-03-21 23:17:30Z

2

I know this is an old question, but these archives are often scanned for many years after being posted, so I don't see a problem with adding things long after the origin date.

How about creating a class, with a read-only property returning the 'array' value? You can specify a parameter using the same syntax as an array index, and defining only a GET property effectively makes it read-only. Define the constant values inside the class and it will work just like a constant array, even though the actual construction is different.

answered Mar 21, 2021 at 23:17

pdanes

211 bronze badge

Comments

Tragamor · Accepted Answer · 2016-12-08 18:21:13Z

0

No - arrays can't be declared as constant but you can use a workaround.

You can create a function that returns the array you want

http://www.vbaexpress.com/forum/showthread.php?1233-Solved-Declare-a-Constant-Array

answered Dec 8, 2016 at 18:21

Tragamor

3,6893 gold badges18 silver badges33 bronze badges

2 Comments

Andrew Morton Over a year ago

If you had shown such a function in your answer, preferably using the sample data in the question, as well as linked to that thread, I would have given you an upvote ;)

Tragamor Over a year ago

It's not my work; it probably doesn't deserve an upvote :-). 30 seconds with google!

CSohu · Accepted Answer · 2021-10-04 18:42:07Z

-1

Using above information, I came to following working solution for comparing short text information of a month, independent from Excel using German language:

Const MONATE = ",Jän,Feb,März,Apr,Mai,Jun,Jul,Aug,Sep,Okt,Nov,Dez"

.. and later in the code:

    If StringToCompare = Split(MONATE, ",")(Month(dt)) Then

NOTE: as the Split-Array starts with index 0 I added the comma in the beginning.

answered Oct 4, 2021 at 18:42

CSohu

11 bronze badge

Comments

z32a7ul · Accepted Answer · 2020-06-18 20:11:26Z

-2

Don't know when this changed, but in Excel 365, this works (or, at least, does not generate a compiler error):

Const table1Defs As Variant = Array("value 1", 42, Range("A1:D20"))

edited Jun 18, 2020 at 20:11

z32a7ul

3,8574 gold badges24 silver badges49 bronze badges

answered Sep 4, 2019 at 16:22

BillR

1

2 Comments

Tom the Toolman Over a year ago

Unfortunately, even without the Range reference, this does not work as recently as Office 2016 (mine is 32-bit) (tested with Excel, Outlook, and Access).

PravyNandas Over a year ago

I'm On Office 365 (32 bit). Unfortunately this doesn't work.

Collectives™ on Stack Overflow

Can an array be declared as a constant?

11 Answers 11

12 Comments

Comments

3 Comments

2 Comments

Comments

Comments

1 Comment

Comments

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

12 Comments

Comments

3 Comments

2 Comments

Comments

Comments

1 Comment

Comments

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related