Subscript out of range Multidimensional Array

[fredlo2010]

Hello guys,

I have a problem and I cannot solve it's super puzzling.

I have a macro that's been working fine and today it started giving me a Subscript out of Range error right before looping through an array.

My array is global and declared:

    Public Arr(1 To 1000, 0 To 8) As Variant    Dim sh(1 To 1000) As Variant
    
    i = 1
    x = 1
    Do Until Arr(i, 0) = Empty ' I am getting the error here
        sh(x) = Arr(i, 0)
        x = x + 1
        i = i + 1
    Loop

Funny thing is that the array does hold a value at Arr(1,0). If I change it to Arr(1,1) move one line down and then change it back to what it was before the code runs with no problems.

I have tried restarting Excel, deleting the temp files and the Appdata.

No luck any suggestions?

Thanks a lot for the help guys.

[fredlo2010]

Never mind guys. The same way it appeared its gone now. So strange.

Still curious to know what this was about. Also in case it comes back again.

Thanks

[p45cal]

If no member of Arr(1 to 1000,0) is empty then i will reach 1001 (and so will x), which will give the error.
What is the value of i when it errors? (hover over the variable in the code or look atthe Locals pane in the vbe)?
Perehaps something along these lines to ensure the loop doesn't go around too many times:

For i = LBound(Arr) To UBound(Arr)
If IsEmpty(Arr(i, 0)) Then Exit For
    sh(i) = Arr(i, 0)
Next i

[fredlo2010]

HI,

Thanks for the reply,

The Value of i=1 and the value of arr(i,0)= "Valid text"

it never reaches the boundaries of the array i will go up to 20 maybe.

Thanks

[p45cal]

Hmmm.. difficult one.
When the error occurs, have a look at the specific member of Arr in the locals pane. Does that member exist? Does Arr have the dimensions you expect?
I ask this because I expect that you've simpified the code/left bits out etc. for posting here. Are you working with multiple workbooks? What kind of module is the code in? Is the Public statement in the same module? If not, where is it?
Could you duplicate the error in a workbook (difficult I know) and post it here?

[fredlo2010]

Hmmm.. difficult one.

I know.

I double check the array in the watch window and it looks the way its supposed to look.

The public array is dimensioned in a Home Module and the loop is in a different module.

There is only one workbook open or involved at the moment of the error.

No I cannot replicate the error in a different workbook. Actually I cannot replicate the error again at all. So strange.

Thanks for the replies p45cal

[p45cal]

What is meant here by the Home module?
And the loop code is in a standard module?.. a sheet code module?

and re:"There is only one workbook open or involved at the moment of the error."
Does this mean there are other workbooks opened/closed by the code?
Are those workbooks properly closed (not just not visible).
Do you see more workbooks in the project explorer part of the vbe than there should be?

[fredlo2010]

The public array is dimensioned in a Home Module and the loop is in a different module

lol way to go... my bad the home module is a regular module. All is happening is between regular modules. The opening and closing o other workbooks will not happen until later on in the code.
There is no hidden workbooks.
Besides the workbook in question, there is my personal workbook and come addin workbooks(unrelated to the code)

Thanks sorry for the Home Module lol

[Bob Phillips]

VBA has a tendency to do strange things like this. I tend to declare a constant at the start of each procedure with the procedure name, and rarely, but often enough to bug me, it throws a compilation error.

Get a copy of Rob Bovey's CodeCleaner addin (it's free), and run it regularly on important projects.

[fredlo2010]

Thanks for the replies guys.

I have tried the Code Cleaner before and I am not sure what it actually does. I don't see anything being changed.

Thanks a gain.

[Bob Phillips]

It doesn't change anything. All it does is to export all code modules, then re-import them. VBA has a tendency to bloat as you change it, and this often causes problems, exporting and re-importing the code modules removes the bloat. You could do it manually, Rob's addin just automates it for you, plus adding a couple of other features.

[fredlo2010]

I see.

Good to know I will make sure I use it frequently then.

Thanks a lot for the info xld