VBA variable used in multiple places?

[TheWennerWoman]

Hello,

I have some code that runs when a workbook is open

Private Sub Workbook_Open()

Const thisfilename As String = "Credit Note Uploader"
Const thisfileversion As String = "v21.1.0"
etc etc
End Sub

This code is passed into a SQL script which interrogates a database to ensure that the user is using the correct version of the file. All ok and works perfectly.

I want to do some other things with these two constants in at least two other modules. At the moment, I am declaring the constants at the start of each module, my concern is if I need to update any of the values, I need to remember to do it in three places.

Is there a way I can declare them once and they get picked up wherever in the VBA they are referenced?

Sorry, I have googled and declaring them publicly seems to be a popular answer except I haven't managed to get it to work.

As always, all help greatly appreciated.

 

[dmt32]

Hi,
you can place your constants at TOP of a STANDARD module & declare them Public

Public Const thisfilename As String = "Credit Note Uploader"
Public Const thisfileversion As String = "v21.1.0"

more info here:Declaring constants (VBA)

Dave

 

[TheWennerWoman]

Hi,
you can place your constants at TOP of a STANDARD module & declare them Public

Public Const thisfilename As String = "Credit Note Uploader"
Public Const thisfileversion As String = "v21.1.0"

more info here:Declaring constants (VBA)

Dave

Thanks for that; so when the workbook is opened, the variables can be "seen" by the code in the Workbook_Open event?

 

[dmt32]

Thanks for that; so when the workbook is opened, the variables can be "seen" by the code in the Workbook_Open event?

Should do just that - you can do a simple check by placing a msgbox in the event to test it

Private Sub Workbook_Open()


    MsgBox thisfilename & Chr(10) & thisfileversion



End Sub

Dave