VBA’s Environ function

This tutorial covers how to get system information using VBA. The information available ties into the Windows Environment variables and you will recognise them from the Environment variables section of the More Windows batch files tutorial. I have used Excel in this tutorial; the function will work in other Office applications.

^_01. Syntax

The basic syntax is as follows:

VBA.Environ(property)

^_02. Alternative syntax

You will often see VBA.Environ$(property). The $ is a shorthand method for declaring a variable, however, I see no way of using this.

There is apparently a small performance benefit in using this syntax.

^_03. Properties

Below are some of the properties available. They are case-insensitive.

• COMPUTERNAME
• LOGONSERVER
• OS
• PATH
• ProgramFiles
• PUBLIC
• USERNAME
• USERDOMAIN

^_04. Examples

The following two code examples are entered as Subs in the Workbook Object.

Private Sub Workbook_Open()
    MsgBox (VBA.Environ("COMPUTERNAME"))
End Sub

Private Sub Workbook_Open()
    MsgBox (VBA.Environ("USERNAME") & "@" & VBA.Environ("USERDOMAIN"))
End Sub

You could just as easily do the same thing in a Word document (*.docm):

Private Sub Document_Open()
    MsgBox (Environ("PATH"))
End Sub

^_05. Next steps

Use the Environ function in the Writing data to a file with VBA tutorial.

References:

1. o365devx (no date) Environ function (Visual Basic for Applications), (Visual Basic for Applications) | Microsoft Learn. Available at: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/environ-function (Accessed: 1 July 2024).
2. Code VBA. (no date) Getting username, special folders and more using the Environ function. Available at: https://www.codevba.com/office/environ.htm (Accessed: 1 July 2024).