Help With Updating a Global Variable from another Module

[JoshuaD]

Good Afternoon,

I am having some trouble getting something to work. The below code works flawlessly if they are all in the same module. However what I am trying to do is to have the subs for Module 2-4 be in separate modules. But still update the global variable (ShtName) in the sub DispShtName. I have tried adding the Public Shtname As String to the top of every module but it only returns blanks. The full code has over 50 different modules that are 150 rows+ so do not want to have them all on one module. Any help would be more than appreciated!

Public ShtName As String

Sub DispShtname()
Call Module2
MsgBox (ShtName)
Call Module3
MsgBox (ShtName)
Call Module4
MsgBox (ShtName)
End Sub

 '*****************************************************************
Sub Module2()
ShtName = Sheets("Contact List").Range("A5").Value
 

End Sub

 '*****************************************************************
Sub Module3()
ShtName = Sheets("Contact List").Range("A6").Value
 

End Sub

 '*****************************************************************
Sub Module4()
ShtName = Sheets("Contact List").Range("A7").Value
 

End Sub

 

[Fluff]

Is that code in a standard module?
Or is it in a Sheet, ThisWorkbook, or Userform module?

 

[JoshuaD]

just a standard module. I added the * to show separate modules in the forum.

 

[Fluff]

Are your macros actually called Module2 etc?

 

[JoshuaD]

Are your macros actually called Module2 etc?

Yes I just made something similar real fast as an example. Sorry for the confusion, they would normally be Row1, Row2, Etc... I have a lot more lines In the real modules but there will be a line in each module to update the global variable. I hope the below will clarify!

Module1:

Public ShtName As String

Sub DispShtname()

Call Row1()
MsgBox (ShtName)

Call Row2()
MsgBox (ShtName)

Call Row3()
MsgBox (ShtName)

End Sub

Module2:

Public ShtName As String

Sub Row1()

ShtName = Sheets("Contact List").Range("A5").Value
 

End Sub

Module3:

Public ShtName As String

Sub Row2()

ShtName = Sheets("Contact List").Range("A6").Value
 

End Sub

Module4:

Public ShtName As String

Sub Row3()

ShtName = Sheets("Contact List").Range("A7").Value
 

End Sub

 

[Fluff]

Remove this

Public ShtName As String

From all but one module & that should work.
It wouldn't work with the macros called Moule2 etc as VBA doesn't know if you are referring to the module or a macro.
It's always best to avoid using VBA keywords either as the name of a macro, or a variable, because VBA can get "confused", causing errors or unexpected results.

 

[JoshuaD]

Remove this

Public ShtName As String

From all but one module & that should work.
It wouldn't work with the macros called Moule2 etc as VBA doesn't know if you are referring to the module or a macro.
It's always best to avoid using VBA keywords either as the name of a macro, or a variable, because VBA can get "confused", causing errors or unexpected results.

Thank you! I will give it a try.

 

[JoshuaD]

Thank you for your help I have it working now! Turns out I just needed to add it to the "ThisWorkbook" Module and the one standard module that called the others and it is working!

 

[Fluff]

Glad you got it sorted