Global (Public) Variable Help

[screech]

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!

 

[Norie]

If you actually want to pass something to/between subs why not actually do so using a parameter.

Sub Macro1()
Dim x As String
     x = "An Example"
     Macro2 x
End Sub

Sub Macro2(y)
      Msgbox y
End Sub

 

[screech]

I'm not quite sure what you just did there, but your example itself works. One question: I would also like to be able to run these routines separately as an option. For example, if the variable SheetName is already defined when I call Macro2, then just use the existing definition. Otherwise, if Macro2 is run separately, then ask what SheetName should be. In your example, I can't seem to run Macro2 by itself.

I hope that makes sense.

 

[Norie]

No you won't 'really' be able to run the 2nd macro on it's own, it won't even appear on the macro list.

To give you further help it really would help if you gave us a little bit more information on what you are actually trying to do.

 

[screech]

I .. uh... found a duplicate declaration in one of my many modules for the same variable, and now the Public declaration of the variable, and defining it works across all modules. Isn't that one of the things that is always suggested when Excel says "ambiguous name detected"? Sloppy checking on my part earlier today.

Norie, I apologize, but still sincerely thank you for your time assisting me with this problem today.

 

[Hoozits]

So, if I am reading this post correctly, if you simply declare your variables as public in one routine, you can use those same variable in another routine and they will contain the values assigned to them in the previous routine?

 

[erik.van.geit]

So, if I am reading this post correctly, if you simply declare your variables as public in one routine, you can use those same variable in another routine and they will contain the values assigned to them in the previous routine?

Yes, that's about it.
Note that you should declare them in "normal" modules, not in sheetmodule or workbook module.
Note also that you may encounter unexpected results with those public variables, if you do not use them with care.

kind regards,
Erik

 

[Hoozits]

Well, already I just got an unexpected result.

After I added

Public Count as Integer

I got the error "Invalid attribute in Sub or Function"

All I'm trying to do is count the occurences of something using one routine and then have other routines use variables that have the value found in the previous routine.

 

[erik.van.geit]

That was not a problem I would expect.

please explain a bit more

What line of code is highlighted (yellow or blue) when te error occurs?
You might need to display your code.

 

[tusharm]

Yes, but don't do it. Using global variables is very, very, very bad programming practice. Pass information from one routine to another through appropriate arguments.

In almost every add-in I develop, be it for my own use, for a paying client, or for download from my website, there might -- I emphasize might -- be 1 global variable. Almost without exception such variables are references to an object that I have to persist across multiple user actions. Other than that, I will not use global variables.

So, if I am reading this post correctly, if you simply declare your variables as public in one routine, you can use those same variable in another routine and they will contain the values assigned to them in the previous routine?