VBA Compiler Flakiness

[Steve Drenker]

PowerBook G4, 2 GB RAM, Excel 2004.

I want to set auto calculation to ON when I activate a worksheet and then
restore it to its original state when deactivating the worksheet. Very
simple, right?

Dim OrigCalc As Integer ' Global to save/restore state

Private Sub Worksheet_Activate()
OrigCalc = Application.Calculation
Application.Calculation = xlAutomatic
End Sub

Private Sub Worksheet_deactivate()
Application.Calculation = OrigCalc
End Sub

This worked fine several times. Then, all at once, the compiler refused to
recognize the "End Sub" in the deactivate routine even though it is right
there in plain sight ("Compiler Error: Expected End Sub").

1. I restarted Excel, but no change.
2. I rebooted the computer, but no change.
3. Erased and retyped the last "End Sub," no change.
4. Erased the entire last sub and used the VBA drop-down menus to insert a
brand new "Sub Worksheet_deactivate()" sub. Now the compiler cannot find the
"End Sub" for the first sub Worksheet_Activate().

Anybody else seen this flaky compiler behavior? Are there any solutions?
This is in a big model and I can't rebuild it from scratch if the file is
corrupt.

Steve

 

[Steve Drenker]

I found one error. xlCalculation constants require a Long data type for
saving. Saving a Long to Integer data type caused a flaky conversion.
' xlAutomatic = -4105
' xlSemiautomatic = -2
' xlManual = -4135

Things seem better so far.

 

[Dave Peterson]

I've never seen the VBE act that flakey.

But maybe you have stuff to the far right (if you scroll you might see it) or
even down.

Maybe just ctrl-a (to select the whole thing), paste into notepad.

Then clean up that module (ctrl-a followed by the delete key), then copy from
notepad and paste back.

 

[George Nicholson]

Glad to hear that things are better. Still, you might consider using Rob
Bovey's free "VBA Code Cleaner" Utility, especially if its a big project.

http://www.appspro.com/Utilities/Utilities.htm

HTH,