Interesting problem when restoring a public variable's lost value

ACommandLineKindaGuy

Active Member
Joined
May 11, 2002
Messages
378
Office Version
  1. 365
Platform
  1. Windows
I have a number of public variables.
VBA Code:
Option Private Module
Public Y, TF, SysAccess, RowsHidden, ColsHidden, PrintNotesAlso, DisplayNotesAlso, LastSelectionAllowEdit As Boolean
Some set the state of a custom control on a ribbon, EG
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
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
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
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
No concrete idea why your variables lose their values but can say you should always indicate which line raises the error. 1004 is about the most useless number, given that it must represent about 8 or more different messages that you can get. One thing that you may not know is that in that line, all variables are variants except for LastSelectionAllowEdit. Perhaps this raises an error and your code disables error handling (e.g. by using Resume Next) and you don't know it. Errors often cause variables to lose their value and/or go out of scope. You should always explicitly declare every variable.
 
Upvote 0

Forum statistics

Threads
1,223,959
Messages
6,175,647
Members
452,663
Latest member
MEMEH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top