Excel vba: constant expression required in for cycle

Question

I have a function that returns a Dictionary with pairs key-value. Then I proceed to use on such pair to create an array: I get a value for key "DATA_ITEMS_NUMBER" to determine arrays' max length. However it results in an error...

   Function getGlobalVariables()
          Dim resultDict As Object
          Set resultDict = CreateObject("Scripting.Dictionary")

          resultDict.Add "DATA_ITEMS_NUMBER", _
                    ThisWorkbook.Worksheets("setup").Cells(25, 5).value

          Set getGlobalVariables = resultDict
    End Function

    Function getBudgetItemInfos(infoType As String, year As Integer)
          Dim globals As Object
          Set globals = getGlobalVariables()

          Dim DATA_ITEMS_NUMBER As Integer
          DATA_ITEMS_NUMBER = globals("DATA_ITEMS_NUMBER")



          Dim resultArray(1 To DATA_ITEMS_NUMBER) As String

    ...
    End Function

I think you need to dim the array first: Dim resultArray() and then resize it after you get DATA_ITEMS_NUMBER : Redim resultArray(1 To DATA_ITEMS_NUMBER) — KacireeSoftware

Mathieu Guindon Mathieu Guindon · Accepted Answer · 2017-10-23T14:52:42

The Dim statement isn't executable; you can't put a breakpoint on a Dim statement, it "runs" as soon as the local scope is entered, in "static context", i.e. it doesn't (and can't) know about anything that lives in "execution context", like other local variables' values.

Hence, Dim foo(1 To SomeVariable) is illegal, because SomeVariable is not a constant expression that's known at compile-time: without the execution context, SomeVariable has no value and the array can't be statically sized.

If you want a dynamically-sized array, you need to declare a dynamic array - the ReDim statement is executable:

ReDim resultArray(1 To DATA_ITEMS_NUMBER) As String

Note that a Dim resultArray() statement isn't necessary, since ReDim is going to perform the allocation anyway: you won't get a "variable not declared" compile-time error with a ReDim foo(...) without a preceding Dim foo and Option Explicit specified.

For good form your Function procedures should have an explicit return type though:

'returns a Scripting.Dictionary instance
Function getGlobalVariables() As Object

And

'returns a Variant array
Function getBudgetItemInfos(infoType As String, year As Integer) As Variant

Otherwise (especially for the Object-returning function), you're wrapping your functions' return values in a Variant, and VBA needs to work harder than it should, at the call sites.

Excel vba: constant expression required in for cycle

1 Answers