storing a public variable for use in all modules

[gordsky]

I have a workbook with several modules in it some which run the same sections of code. I think I could probably create a function so that the code is only written once and then refer to that function on each sheet but what would really be useful would be if I could run the section of code once via workbook open and then store the result as MyValue which could then be used across any module. I have done lots of searches but cant find how to do it. I must confess I dont understand a lot of the results I have looked at so anyhelp with code or an explanation would be much appreciated

 

[EFANYoutube]

Just in any one of your modules at the top outside of a sub put

Public SomeVariableName as Variant

Variant can be any data type
Then on open

Private Sub Workbook_Open()
SomeVariableName = "Ha it worked"
End Sub

Please be aware I have had issues with this in the past where it doesn't hold the reference
This should be used in conjunction with the code being run on open, everywhere you run a new code you will have to set this again because VBA kills in when code ends