Stumped on Array

[Otto Moehrbach]

Excel 2002, WinXP
I've shortened the code to show my problem. The object here is to move some
sheets to the far left. The code is:
Option Explicit
Public ExemptList() As String

Sub Macro1
Call BuildExemptArray
Call MoveExemptSheets
End Sub

Sub BuildExemptArray()
ReDim ExemptList(1 To 3)
ExemptList(1) = "AddrList"
ExemptList(2) = "ChkList"
ExemptList(3) = "ClientShtsList"
End Sub

Sub MoveExemptSheets()
Dim c As Long
For c = UBound(ExemptList) To LBound(ExemptList) Step -1 '**Problem
line
Sheets(ExemptList(c)).Move Before:=Sheets(1)
Next
End Sub

This code is straightforward and I've used it before, but now I am getting a
"Subscript out of range'" error on the "For c =" line.
I can see I might get this error with the subsequent line if one of the
array sheets didn't exist or is hidden, but I can't see a reason for this
error with this line.
Thanks for your help. Otto

 

[Bob Phillips]

Works fine for me Otto.

Just the old chestnut, MISSING references in the Tools>References in the
VBIDE.

 

[Jim Rech]

The code works for me too, Otto. I'd be curious what ExemptList(3)
evaluates to when you hit the error and go into debug. The only way I can
understand the error is if the array populated by the second sub is a
different array than the public one. That can't be with the code you posted
but I wonder if the real code it different in some way.

--
Jim
| Excel 2002, WinXP
| I've shortened the code to show my problem. The object here is to move
some
| sheets to the far left. The code is:
| Option Explicit
| Public ExemptList() As String
|
| Sub Macro1
| Call BuildExemptArray
| Call MoveExemptSheets
| End Sub
|
| Sub BuildExemptArray()
| ReDim ExemptList(1 To 3)
| ExemptList(1) = "AddrList"
| ExemptList(2) = "ChkList"
| ExemptList(3) = "ClientShtsList"
| End Sub
|
| Sub MoveExemptSheets()
| Dim c As Long
| For c = UBound(ExemptList) To LBound(ExemptList) Step -1 '**Problem
| line
| Sheets(ExemptList(c)).Move Before:=Sheets(1)
| Next
| End Sub
|
| This code is straightforward and I've used it before, but now I am getting
a
| "Subscript out of range'" error on the "For c =" line.
| I can see I might get this error with the subsequent line if one of the
| array sheets didn't exist or is hidden, but I can't see a reason for this
| error with this line.
| Thanks for your help. Otto
|
|

 

[George Nicholson]

The code worked for me.
(Are you *sure* the correct workbook is active? but, to your point, if this
were the case it wouldn't be this line causing the error)

Otherwise, the only thing I can tell you is to try the things you do when
things go inexplicably buggy: reboot & try again. If still an problem,
delete the offending line and retype it (hey, it works for me...sometimes)

....or maybe run Rob Bovey's Code Cleaner.
http://www.appspro.com/Utilities/Utilities.htm

Good luck,

 

[Bob Phillips]

. If still an problem,
delete the offending line and retype it (hey, it works for me...sometimes)

Too often for our sanity .

 

[George Nicholson]

Too often for our sanity .

oh, any claim I may have had to sanity expired circa Excel 4

 

[Otto Moehrbach]

Thanks to all. I'll beat on it some more in the morning. Otto

 

[Otto Moehrbach]

To all
I'm embarrassed to say this, but my real code had the calls for the two
macros switched, so that the code was trying to use the array before it
built the array. Up until now I've always been proud that I was almost
sane. Now that's gone.
Thanks for your help and for taking the time. Otto

 

[George Nicholson]

Sometimes you just have to settle for next-day sane. At least you still
have that.