passing a variable to an array declaration

Hello labogola,

Here is an example of passing in the variable to set the array's size.

Sub SetArraySize(ByVal cnNUMCOLS As Long)

    Static nColorIndices() As Variant
    
        ReDim nColorIndices(1 To cnNUMCOLS)
        
End Sub

Hi Leith,
I'm still having some issues with it.

There is a public variable declared in a module.

Public APLastCol As Long
Public NPLastCol As Long

Upon startup these variables are both defined by another script which counts rows and columns. I have tested it, and the output is fine.

However, when I run this script below it starts spitting out subscript out of range errors and the public variables are no longer defined as they were.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
       Static nColorIndices As Variant
       ReDim nColorIndices(1 To PLastCol)
.....

End Sub

I don't get why that changes the public variables as I'm not asking it to change anything.

Hello labogola,

The Public variables should not change because of a run-time error. I think it best if you post your workbook for review.

If you have unhandled errors, it tends to cause state loss and your variables will get reset to their default values.