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.

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

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

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 <[email protected]>,
"Joe Fish" <[email protected]> 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

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
>
>

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

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
> >
> >

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" <[email protected]> wrote in
message
news:[email protected]...
> 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
>> >
>> >

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" <[email protected]> wrote in
> message
> news:[email protected]...
> > 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
> >> >
> >> >
>
>
>

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...TNGP09.phx.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" <[email protected]> wrote...
> Done this but now have other problem. Please see newer thread with name
> Global Variable.