VBA Constant Expression Required

We covered arrays, static arrays and dynamic arrays in a previous tutorial. We are going to look at a common error associated with static arrays called Constant Expression Required. This error is generated when you try to use a static array instead of a dynamic array as shown in the code below:

Static array instead of dynamic array

The static array needs to have constants used to set it since it is fixed.
The way to resolve this error is to use a Dynamic array variable instead. You would use the ReDim keyword every time you want to resize the array. This is shown in the code below:

Sub UsingReDim()

Dim value1 As Integer
Dim value2 As Integer
Dim value3 As Integer

value1 = 3
value2 = 9
value3 = 15

Dim listofvalues() As Integer

ReDim listofvalues(value1)

End Sub

Read more about Dynamic array variables in our Array variable tutorial.