Global Variables

Patrick O'Dea
Patrick O'Dea used Ask the Experts™
on
Hi,
I really should know this.

Public Sub GlobalVars()
GUser = Replace(Environ("Username"), "'", "")
End Sub

I want to be able to display the variable GUser anywhere , anytime.  I do not want to have to call the sub "GlobalVars".

So, for instance, how do I simply ;
msgbox GUser
without calling the sub "GlobalVArs"
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
You want a function:

Public Function GetUser() as string
   
    On Error Goto GetUser_Error

     GetUser= NZ(Environ("Username"),"")
   
 GetUser_Exit:
    Exit Function

GetUser_Error:
   Getuser = ""
   Resume GetUser_Exit

End Sub

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007
Commented:
Define the Global in any regular VBA module - see image.

Then, possibly in the Load event of a Form, or some other code ... initialize the variable
Private Sub Form_Load()
    GUser = Environ("UserName")
End Sub

GUser is now ready to use anywhere in code.

For example

msgbox GUser



mx
Capture1.gif

Author

Commented:
THanks folks!
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Hey, FWIW, couple of things:

1. Environ is not a great way to get the username as it can be easily spoofed.  Instead, use the following:

Private Declare Function GetComputerNameA Lib "kernel32" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function GetUserNameA Lib "advapi32.dll" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function WhoAmI(bReturnUserName As Boolean) As String

        ' Function returns either user name or computer name

        Dim strName As String * 255

10      If bReturnUserName = True Then
20        GetUserNameA strName, Len(strName)
30      Else
40        GetComputerNameA strName, Len(strName)
50      End If

60      WhoAmI = left$(strName, InStr(strName, vbNullChar) - 1)

End Function

2. If you wanted more flexibility in that function and the ability to return any environ variable, you could do this:

Public Function GetEnvrionVar(strVariableName as string) as string
   
    On Error Goto GetEnvrionVar_Error

     GetEnvrionVar= NZ(Environ(strVariableName),"")
   
 GetEnvrionVar_Exit:
    Exit Function

GetEnvrionVar_Error:
   Getuser = ""
   Resume GetEnvrionVar_Exit

End Sub

 and call by:

=GetEnvrionVar("username")

Jim.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial