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

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 <1133051666.580828.105420@g14g2000cwa.googlegroups.com>,
"Joe Fish" <joefish534@yahoo.com> 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

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

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" <FrancisBrown@discussions.microsoft.com> wrote in
message
news:B47F619D-3C4C-4E85-AB73-44B81D5DB8D4@microsoft.com...
> 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" <FrancisBrown@discussions.microsoft.com> wrote in
> message
> news:B47F619D-3C4C-4E85-AB73-44B81D5DB8D4@microsoft.com...
> > 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" <FrancisBrown@discussions.microsoft.com> wrote...
> Done this but now have other problem. Please see newer thread with name
> Global Variable.