Keeping a Public variable always available

[chazrab]

I have this

Public colnum As Long
Sub Callcolnum()
colnum = Round((Date - DateValue("12/20/2017")) / 7, 0)
MsgBox colnum
End Sub

in a modole renamed to COLNUMDECLARATIONCODE
and this

Private Sub Workbook_Open()
Callcolnum

at the beginning of ThisWorkbook code. Everything works fine - until this issue...

Issue: as long as the application is live and running colnum is correct and available to all code modules.

But if I have to make an editing change or something else, which causes the application to be put in the DESIGN mode, and then be put back to the live app from the Developer tab, the variable's value no longer exists and defaults to 0.

Is there a way to always keep the correct value of colnum available when popping in and out of Design without having to initiate a Save -> Close ->Reopen the app to regenerate the correct value of colnum?


Thanks for anyone's help

cr

 

[John_w]

That is one of the problems with Public/Global variables. In your case I would get rid of the global variable colnum and the Callcolnum line in Workbook_Open and change Sub Callcolnum to Function colnum, like this:

Function colnum()
colnum = Round((Date - DateValue("12/20/2017")) / 7, 0)
MsgBox colnum
End Function

Then any other instances of colnum in your code will call the above function, instead of referring to the global variable.

 

[RoryA]

Which is exactly what I suggested in his original thread...

 

[chazrab]

I did exactly what you said - and it works! I have you and RoryA, the Moderator to thank. As per his comment below, he suggested I do this yesterday.
One last comment:
It seems to me that, when you have a complete understanding of everything VBA is capable of doing, like you and RoryA do, it can greatly reduce the amount of coding and eliminate redundancy into fewer lines in a compact, neat and simple package when building an application.

Thanks again to you both.
Merry Christmas and Happy New Year
cr
Kingwood, Texas