excel dimensionalizing values for an array post compile vba

i want to be able to initialize an array to store the values of a conver开发者_开发知识库sion. but i don't know how many values there will be until compile time so i want the range to go from

pseudo-code:

1 to <some expression>

namely i want it as

Dim Colvals(1 To Len(EndColumn)) As Integer

but when the code is sent to compile it throws

Constant Expression required

any idea how to do it? i really don't want to limit the versatility of the program by forcing the specification of the range.

---

You want to redimension your array using a Redim statement:

Dim Colvals() As Integer
ReDim Colvals(1 To Len(EndColumn))

It seems strange that it requires a Constant for the Dim and not the Redim, but it's true.