Global Variables
 

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.
--
Regards and Thanks for any assistance.

Francis Brown.

Global Variables
 

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

Global Variables
 

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

Global Variables
 

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

In article .com,
"Joe Fish" wrote:

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

Global Variables
 

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.
--
Gary''s Student


"Joe Fish" wrote:

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

Global Variables
 

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

Global Variables
 

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.


--
Regards and Thanks for any assistance.

Francis Brown.


"Gary''s Student" wrote:

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.
--
Gary''s Student


"Joe Fish" wrote:

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

Global Variables
 

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

"Francis Brown" wrote in
message
...
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.


--
Regards and Thanks for any assistance.

Francis Brown.


"Gary''s Student" wrote:

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.
--
Gary''s Student


"Joe Fish" wrote:

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

Global Variables
 

Done this but now have other problem. Please see newer thread with name
Global Variable.
--
Regards and Thanks for any assistance.

Francis Brown.


"Chip Pearson" wrote:

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

"Francis Brown" wrote in
message
...
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.


--
Regards and Thanks for any assistance.

Francis Brown.


"Gary''s Student" wrote:

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.
--
Gary''s Student


"Joe Fish" wrote:

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

Global Variables
 

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?thre...GP09.p hx.gbl

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.


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