ACommandLineKindaGuy
Active Member
- Joined
- May 11, 2002
- Messages
- 378
- Office Version
- 365
- Platform
- Windows
I have a number of public variables.
Some set the state of a custom control on a ribbon, EG
I then store the variable's value in a RowsHidden named range.
All good. Now, for some reason, there are occasions where the variable loses its value
So I have created the following sub as a temporary measure to find the problem
When I see the message, I'm prepared to know that the code triggers an error, which I now have forgotten and of course cannot replicate but I do know that when I hover over the variable,the tooltip returns "Empty"
First Question: How do I fix this error?
2nd Question: Can my little sub be optimized to run faster?
TIA John
VBA Code:
Option Private Module
Public Y, TF, SysAccess, RowsHidden, ColsHidden, PrintNotesAlso, DisplayNotesAlso, LastSelectionAllowEdit As Boolean
VBA Code:
Sub getPressedRow(control As IRibbonControl, ByRef pressed)
'This sets the checkbox status on startup since by default checkboxes are unchecked
pressed = RowsHidden
End Sub
All good. Now, for some reason, there are occasions where the variable loses its value
So I have created the following sub as a temporary measure to find the problem
VBA Code:
Sub CheckIfPublicVariableLostValue() 'Called from:
'SystemAccess
'Worksheet_SelectionChange on Proposal, Schedule, and Scratch sheets
'Sometime these variables get cleared when maintaining/troubleshooting workbook
With Worksheets("Proposal")
If IsEmpty(SysAccess) Then MsgBox "SysAccess variable lost value"
If IsEmpty(SysAccess) Then SysAccess = .Range("InSysAccess")
If IsEmpty(RowsHidden) Then RowsHidden = .Range("RowsHidden")
If IsEmpty(ColsHidden) Then ColsHidden = .Range("ColumnsHidden")
If IsEmpty(PrintNotesAlso) Then PrintNotesAlso = .Range("PrintNotes")
If IsEmpty(DisplayNotesAlso) Then DisplayNotesAlso = .Range("DisplayNotes")
End With
End Sub
First Question: How do I fix this error?
2nd Question: Can my little sub be optimized to run faster?
TIA John