Thread: Problem declaring an array

Hi LydiasDad (When my son turned 12 I became TylersDad, knowing nobody cared about me anymore)

In the back of my mind is a little bird which says Arrays need to be declared as type variant. I wonder if it could be that easy?

http://msdn.microsoft.com/en-us/libr...(v=VS.60).aspx

Can you give some more code, perhaps the whole Sub of Function procedure?

Also, what is the value and its format at

Dividend = ActiveCell.Value

?

Hi,

It looks like the compiler didn't like a variable in the Dim Statement. You have:

Dim Divisors(1 To Dividend) As Variant

Change this above line to

Dim Divisors(1 To 500) As Variant

and it runs fine for me.

I'm reading http://msdn.microsoft.com/en-us/libr...ffice.10).aspx to see if it ever says they must be constants.

The answer is on your computer: VBEditor F1

You can't dimension an array using a variable. With Redim you can.
you can use:

    x = 12
    ReDim sn(x)

but you can't

x=12
    Dim sq(x)

Hey snb

These dim redims are a little discombobulating in my mind.

Thanks for the edited explanation. This helps me understand them better.

The only problem arises in the subscript in combination with 'To'.
But you can tackle that using 'option base'.

Sub snb()
    x = 12
    Dim sr(1)
    ReDim Preserve sr(x + 1, x + 2, x + 3)
    
    MsgBox UBound(sr) & "_" & UBound(sr, 2) & "_" & UBound(sr, 3)
    
    ReDim sn(x)
    MsgBox UBound(sn)
    
    ReDim sq(20, x)
    MsgBox UBound(sq) & "_" & UBound(sq, 2)
End Sub

Originally Posted by MarvinP

These dim redims are a little discombobulating in my mind.

I don't know what it is about that line that cracks me up (perhaps the word discombobulating [and yes I've heard the word before]).

Originally Posted by LydiasDad

Hey, thanks for the response, but I don't understand your post. What is snb and msgbox and ubound, and what do they have to do with declaring an array? I just need to declare an array that has up to "Dividend" elements.

Hi LydiasDad,

snb is snb, he's trying to help you!
UBound finds the upper bound or last index of the array. So, the the array is 1 to 3, 3 would be the UBound.
To declare up to 'Dividend',

Sub LydiasDadsArray()
   Dim LydiasArray() as Long, aCnt as Long  
   ReDim LydiasArray(Dividend)
   For aCnt = 1 to Dividend
      LydiasArray(aCnt) = '''What ever you want it to equal!!'''
   next aCnt
End Sub

Adapt this to your actual code and you're golden

If you are getting a subscript out of range error, then either Dividend is less than 1 (you can't declare an array as 1 to 0 for example) or you referring to an element of the array that doesn't exist in a later piece of code.