I have read a lot of help files and posts about global variables. I think I am doing this correctly but I continue to get the "ambiguous name" error. In short, I have several modules of code that each do something different. I am attempting to write another routine in another module to link all of these other routines in separate modules together. Naturally, I want to pass a defined variable from one routine in one module to another routine in another module. Declaring a variable as Public at the top of the first procedure should work, but that error keeps coming up.
This is a over-simplified breakdown of my code:
'' Module 1
Option Explicit
Public SheetName as String
Sub Macro1()
SheetName = Sheet1
Call Macro2
Call Macro3
End Sub
'' Module 2
Sub Macro2
[Using SheetName as a variable produces an error, it's been redefined as ""]
End Sub
'' Module 3
Sub Macro3
[Will use SheetName but never get this far as code breaks]
I have read somewhere that having code that opens another sheet, pulls data, and then closes it can clear out global variables. Is this what is happening here? Are there other options available to keep the variable defined across multiple routines in multiple modules?
Thanks for all the guidance!
This is a over-simplified breakdown of my code:
'' Module 1
Option Explicit
Public SheetName as String
Sub Macro1()
SheetName = Sheet1
Call Macro2
Call Macro3
End Sub
'' Module 2
Sub Macro2
[Using SheetName as a variable produces an error, it's been redefined as ""]
End Sub
'' Module 3
Sub Macro3
[Will use SheetName but never get this far as code breaks]
I have read somewhere that having code that opens another sheet, pulls data, and then closes it can clear out global variables. Is this what is happening here? Are there other options available to keep the variable defined across multiple routines in multiple modules?
Thanks for all the guidance!