Code termination resets public variable

R

rogge

A variable, boolRenameSheets, is declared in a module at the module (General)
level. Speudo Code is below with the important declarations...

boolRenameSheets is being Watched (Break When Value Changes); the code never
breaks after modPublicMacros.runSimulationQuestion has begun.

Thank you all in advance for helping me with this conundrum.


Here is the process...

A) Set boolRenameSheets = True (occurs when ribbon is first accessed (ie
open workbook) and when checkbox is ticked) this module the only place that
contains 'boolRenameSheets ='. elsewhere "if boolRenameSheets then" is
used... this make it very evident the my code does not change
boolRenameSheets.
B) ReDimensions (not-preserve by design) populates some arrays
C) Runs code in a DLL that uses the arrays
D) deletes sheets meeting a specified criteria
E) creates new sheets and populates cells on these sheets
F) MsgBox indicating the process completed (and also boolRenameSheets)

THIS IS WHAT HAPPENS:
0 - ribbonCallBacks.cbSetRenameSheets) Set boolRenameSheets = True using the
ribbon

1 - modPublicMacros.runSimulationQuestion) Run the special code...

2 - modSpecialCode.runSimulation) Display boolRenameSheets immediately
before code execution stops
boolRenameSheets = True

3 - Immediate Window) Use the immediate window to verify boolRenameSheets
immediately after step 2 finishes
? boolRenameSheets
False



_________________________________________________
'modPublicMacros

Option Compare Binary
Option Explicit
Option Base 0

Public boolPrintDerivatives As Boolean
Public boolRenameSheets As Boolean

Public Sub runSimulationQuestion
'Pseudo Code....
if msgBox() = vbYes Then modSpecialCode.runSimulation
End Sub

_________________________________________________
'ribbonCallBacks

Option Private Module
Option Compare Binary
Option Explicit
Option Base 0

'Callback for chkRenameSheets getPressed
Public Sub gpRenameSheets(ByVal control As IRibbonControl, ByRef returnedVal)
'Set default value to True
returnedVal = True
boolRenameSheets = True
End Sub

'Callback for chkRenameSheets onAction
Public Sub cbSetRenameSheets(ByVal control As IRibbonControl, ByVal pressed
As Boolean)
'Set value to checkbox value
boolRenameSheets = pressed
End Sub

_________________________________________________
'modSpecialCode

Option Private Module
Option Compare Binary
Option Explicit
Option Base 0

Public Sub runSimulation()
runSimulationPartA
End Sub


Private Sub runSimulation
deleteSheets
GetInput
runSimulationDLL
populateOutput
MsgBox boolRenameSheets & " complete" 'Shows true when
runSimulationPartA begins execution with boolRenameSheets = true
End Sub


Private Sub deleteSheets
'delete old sheets
End Sub


Private Sub GetInput
'populate arrays
End Sub


Private Sub runSimulationDLL
'Execute the dll
End Sub


Private Sub populateOutput
'create new sheets
End Sub
 
D

Dave Peterson

I don't know about the ribbon, but does any of your code use End (not End Sub,
End Function, End if), just plain old End.

This will reset those variables.
 
R

rogge

No instances of "End" exist. (no one here had tough of that, thank you for
this tip).

Here is the last part of "Private Sub runSimulation"
....
....
....
' Clear variables declared by solomonrunSPSLFeed
xlCalcType = Empty

' Error handler
On Error GoTo 0: MsgBox "A) " & boolRenameSheets & vbTab &
boolPrintDerivatives: Exit Sub

runSPSL_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
runSPSL of Module modSpyroSPSL", vbCritical + vbOKOnly, "Error"

End Sub


When the whole process begins with boolRenameSheets set to True, the message
box shows:
"A) True"

So I *know* that boolRenameSheets has kept the value up until this dialog
box is displayed. After "OK" is clicked; boolRenameSheets returns false in
the immediate window.


(Can one access the check box i have in the ribbon? maybe I can get its
value rather than use boolRenameSheets.)
 
R

rogge

As i study this issue today, my "fix" to the non-trappable "<application
error>" still occurs. This is my fix..

For i = wkbActive.Worksheets.Count To 1 Step -1
If (wkbActive.Worksheets(i).CodeName Like strDeleteSheet & "*") Then
Debug.Print "before " & boolRenameSheets
wkbActive.Worksheets(i).Delete
Debug.Print "after " & boolRenameSheets
End If
Next i

Ok, this is NOT where boolRenameSheets is reset. I ran the code
back-to-back after setting boolRenameSheets to True... Here is the output
from the code.

before True
after True
before False
(watch causes break)
after False

The watch causes code to break on the line: Debug.Print "after " &
boolRenameSheets as indicated above. HOWEVER, the value did not change.

Does this help any one help me? <shakes his cloudy head>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top