Global Variables

[Francis Brown]

Could some one explain how to set up some global variables.

I Have code that uses application.username to test who is using my
application.

The code then accesses a userfile.xls sheet to find what management group
the person belongs too and detects there manager etc. This is then used to
setup where the users data is saved etc.

I can get all of the above to work but the variables are only good for the
module.

How to I say make a variable good for all code in the project.

eg if the users team is retrived from my code to a variable called Team. how
to I keep that variable valid after the module closes.

 

[Joe Fish]

Francis,
You need to declare the variables as Public outside of a procedure. For
example:

Public rng As Range

Sub MooCow ()
'blah blah blah
End Sub

Hae fun,
Fish

 

[Joe Fish]

Now that I'm thinking of it, I'm pretty certain it has to be before the
first procedure in a module. So actually, it's more like:

Public rng As Range

Set rng = Range("A1")

Sub MooCow ()
'blah blah blah
End Sub

 

[JE McGimpsey]

Try it!

You'll find that your Set statement is invalid outside a procedure.

Instead, use

Public rng As Range

Public Sub MooCow()
'blah blah blah
Set rng = Range("A1")
End Sub

 

[Gary''s Student]

Joe is correct. If you just DIM them outside the procedures of a module,
then they are by default private and only the procedures within that module
can "see" them.

By Declaring the PUBLIC, all procedures in all modules can see them.

 

[Joe Fish]

Francis,
He's right, the Set statement needs to be inside a module, but the
Public declaration needs to be before the first statement in a module.
Have Fun,
Fish

 

[Francis Brown]

I'M still having a little trouble. Here is my code

Public name As String
Public team As String
Public TeamLeader As String
Public CommandN As String



Public Sub FindUser()
currentuser = Application.UserName
Workbooks.Open "Z:/Systemdown/Userfile.xls"
lastrow =
Workbooks("Userfile.xls").Sheets("UserData").Range("A65536").End(xlUp).Row

For Each person In Workbooks("Userfile.xls").Sheets("UserData").Range("A2:A"
& lastrow)
If person.Value = currentuser Then
Set name = person.Offset(0, 1).Value
Set team = person.Offset(0, 2).Value

lastrow2 =
Workbooks("Userfile.xls").Sheets("UserData").Range("A65536").End(xlUp).Row
For Each Teamgroup In
Workbooks("Userfile.xls").Sheets("Command").Range("A2:A" & lastrow2)
If Teamgroup.Value = team Then
Set TeamLeader = Teamgroup.Offset(0, 1).Value

Set CommandN = Teamgroup.Offset(0, 2).Value
Else
End If
Next Teamgroup
Else
End If
Next person
Workbooks("Userfile.xls").Close

End Sub

The user file has two sheets.

Userdata with colums ID, Name and Team
Command with Team, Team Leader and Command.

The Macro is meant to use application.UserName to find who is currently
runing the program. It is then meant to look up from the tables there name,
Team, Team Leader and Command. These are then going to used to set up the
save directory on a shared drive for the user. That's why I need the
variables to go Global as I need them in other modules.

When I run the code as above I get mismatch errors when trying to set the
data to the variable.

Can someone suggest where i'm going wrong.

 

[Chip Pearson]

Get rid of the 'Set' keyword wherever you have it. 'Set' is used
only for object type variables, not simple variables.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

message

 

[Francis Brown]

Done this but now have other problem. Please see newer thread with name
Global Variable.

 

[David McRitchie]

Hi Francis,
You should continue in same thread.

For those looking in the Google archives, you can beam on over to the other thread, at
http://groups.google.com/groups?threadm=#[email protected]

making multiple posts (/threads) makes it hard to follow in archives, and
usually means at least one person will waste time answering a question, and
many people had to read the same question twice.