Archive for Microsoft Office
Function to get logged in user name
Posted by: | CommentsThis code is from Charles Maxson’s blog (http://blogs.officezealot.com/charles/archive/2004/12/10/3574.aspx) and is posted here for my reference.
Excel VBA: Function to get logged in user name (plus the Environ Function)
One commonly asked task is how can you get the name of the current user of an Excel spreadsheet into a cell in the spreadsheet. A lot of people quickly stumble across the UserName property in VBA and create a function similar to this:
Function UserNameOffice() As String
UserNameOffice = Application.UserName
End Function
But as you know, that only returns the name of the user according to the registration information of Office. A lot of companies set that at something generic like “User”or “Registered Owner”. That’s not what you really want though right? You really want the user’s name based on their Windows login. How do you get that ?…. well it’s a little complicated with an API call from VBA as shown here is below:
Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA"(ByVal lpBuffer As String, nSize As Long) As Long
Function UserNameWindows() As String
Dim lngLen As Long
Dim strBuffer As String
Const dhcMaxUserName = 255
strBuffer = Space(dhcMaxUserName)
lngLen = dhcMaxUserName
If CBool(GetUserName(strBuffer, lngLen)) Then
UserNameWindows = Left$(strBuffer, lngLen - 1)
Else
UserNameWindows = ""
End If
End Function
Then all you have to do in the cell of choice is enter the formula:
=UserNameWindows()
But as *Mike* reminded me in a comment on my original post (this is the updated version)….
There is the Environ Function in VBA that makes this a walk in the park without the API hassles:
Function UserNameWindows() As String
UserName = Environ("USERNAME")
End Function
Thanks *Mike* for bringing that up….
I remember using Environ to get the current location of the “My Documents“ folder for the current user:
MsgBox Environ("USERPROFILE") + "\My Documents"
So having my memory jarred on the Environ function, I thought I would check VBA help to see what else this Little gem provided. And boy, how disappointing Help was… here is what it looks like: Environ Help. Not too useful I thought… So I decided to figure it out on my own and loop thru all the arguments possible with Environ. Copy and run this little routine to see all that Environ offers:
MsgBox Environ("USERPROFILE") + "\My Documents"Public Sub EnvironFunction()
Dim nCount As Integer
nCount = nCount + 1
Do Until Environ(nCount) = ""
Debug.Print Environ(nCount)
nCount = nCount + 1
Loop
End Sub
There are lots of useful things in there including APPDATA, COMPUTERNAME, HOMEDRIVE, HOMEPATH, OS, USERDOMAIN and more… Hopefully you will find it useful and I won’t forget about it again.
****Nice to see blogging helps you remember what you forgot and that readers often help writers more than the other way around
Here’s a complete list (that I know of) of the named arguments for the Environ Function:
| Environ arguments |
| ALLUSERSPROFILE |
| APPDATA |
| AVENGINE |
| CLIENTNAME |
| CommonProgramFiles |
| COMPUTERNAME |
| ComSpec |
| FP_NO_HOST_CHECK |
| HOMEDRIVE |
| HOMEPATH |
| INCLUDE |
| INOCULAN |
| LIB |
| LOGONSERVER |
| NUMBER_OF_PROCESSORS |
| OS |
| Path |
| PATHEXT |
| PROCESSOR_ARCHITECTURE |
| PROCESSOR_IDENTIFIER |
| PROCESSOR_LEVEL |
| PROCESSOR_REVISION |
| ProgramFiles |
| SESSIONNAME |
| SystemDrive |
| SystemRoot |
| TEMP |
| TMP |
| USERDOMAIN |
| USERNAME |
| USERPROFILE |
| VS71COMNTOOLS |
| WecVersionForRosebud.FF0 |
| windir |
Powerpoint Backgrounds
Posted by: | CommentsI use TechSmith’s SnagIt screen capture utility for all of my screen c
aptures. Today, I went to their site to see if there was an update and found that they have a nice collection of PowerPoint backgrounds (scroll to near the bottom of the page).

