VBA Tips, Tricks and Best Practices, Part One of Four
July 8, 2009 • JP • No Comments • Rate This Article
• Links to this article
Using VBA can be a huge time saver, but can also lead to bad coding practices. In this series of posts, I'll show you a few tricks I use to maximize my productivity in VBA and avoid errors.
Use Environ() function to get system information
I've seen some very complicated API calls to get information that can be returned in 25 characters of code. And I've been guilty of hardcoding filepaths in my macros, effectively limiting them (without editing) to use on Windows XP (or Vista) only. It really comes down to knowing what functions are available to you (or a well-worded Bing search). And that is my real message here: try to find a built-in function (even if it's in another library) before writing your own (or turning to external functions like APIs).
Here's a short procedure that returns the value from a few select environment properties.
COMPUTERNAME
ComSpec
OS
SystemDrive
SystemRoot
TEMP
USERDNSDOMAIN
USERDOMAIN
username
USERPROFILE
End Enum
Function GetEnviron(environType As environType) As String
' return specific environ type
Dim environName As String
Select Case environType
Case 0
environName = "COMPUTERNAME"
Case 1
environName = "ComSpec"
Case 2
environName = "OS"
Case 3
environName = "SystemDrive"
Case 4
environName = "SystemRoot"
Case 5
environName = "TEMP"
Case 6
environName = "USERDNSDOMAIN"
Case 7
environName = "USERDOMAIN"
Case 8
environName = "username"
Case 9
environName = "USERPROFILE"
End Select
GetEnviron = Environ$(environName)
End Function
The Enum section should be placed at the top of a standard module, but the GetEnviron code can go anywhere.
To use, simply call GetEnviron and select the environment variable from the dropdown list. If you want the user's OS, use GetEnviron(OS). The OS variable uses underscores so you'll probably want to replace them with spaces to display a friendly name, i.e.
UserOS = Replace(GetEnviron(OS), "_", " ")
To get a full list of environment variables you can use with the above code snippet, run this code:
' adapted from
' http://www.vbaexpress.com/kb/getarticle.php?kb_id=217
Dim i As Integer
'Creates a list of environ arguments,
'in the form ARGUMENT=EnvironString
i = 1
Do Until Environ(i) = ""
Debug.Print Environ(i)
i = i + 1
Loop
End Sub
Got any "Best of the best" VBA coding tips and tricks of your own to share?
VBA ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Advanced Excel Conference Recap
Next Post: VBA Tips, Tricks and Best Practices, Part Two of Four
[...] VBA Tips, Tricks and Best Practices, Part One of Four » Code For Excel And Outlook Blog (tags: excel tips) [...]