VBA Constant Expression Required

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on July 19, 2021

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.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as

Learn More!

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples