#Excel: Creating a Environmental Variables Table with the VBA ENVIRON Function UDF by David Hager

Is it useful to obtain information about the various aspects of your PC operating system. This can easily be done with the VBA ENVIRON function. Since it is a VBA function, it cannot be used directly in a worksheet cell. The following UDF encapsulates the ENVIRON function, and this function can be used on the worksheet.

Function Env(Lposition As Variant)

Env = Environ(LPosition)

End Function

It is important to note that the variable Lposition is declared as a Variant so that both numeric and built-in Excel strings can be used by this UDF. I could not find a list of the strings on the net, but in this example I will show you how to make a list. The values listed below are for my computer.

A1: =Env(ROW()) returns ALLUSERSPROFILE=C:\ProgramData

B1: =LEFT(A1,FIND(“=”,A1)-1) returns ALLUSERSPROFILE

C1: =Env(B1) returns C:\ProgramData

Note that in B1 is the built-in string argument used by C1.

You can download the file here.

Envir

When you do, fill A1:C1 down to row 40 on the worksheet to make the environmental variable table for your computer. Column B will contain the built-in strings. I hope that you find this useful.

Advertisements

2 thoughts on “#Excel: Creating a Environmental Variables Table with the VBA ENVIRON Function UDF by David Hager

  1. Pingback: #Excel For You Blog Posts Having Downloadable Workbook Files by David Hager | Excel For You

  2. Pingback: #Excel Super Links #150 – Special Edition | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s