Category Archives: VBA

VBA

VBA environment variables (ENVIRON)

To see a list of variables, create a sub:
[vb]

Sub ListEnvironmentVariables()

‘each environment variable in turn
Dim EnvironmentVariable As String

‘the number of each environment variable
Dim EnvironmentVariableIndex As Integer

‘get first environment variables
EnvironmentVariableIndex = 1
EnvironmentVariable = Environ(EnvironmentVariableIndex)

‘loop over all environment variables till there are no more
Do Until EnvironmentVariable = ""

‘get next e.v. and print out its value
Debug.Print EnvironmentVariableIndex, EnvironmentVariable

‘go on to next one
EnvironmentVariableIndex = EnvironmentVariableIndex + 1
EnvironmentVariable = Environ(EnvironmentVariableIndex)

Loop

End Sub
[/vb]

click menu “View” > “Immediate Window” and run it
Doesn’t work on macintosh

[vb]
Private Sub Workbook_Open()
‘Generates a GUID:
Sheet1.Cells(8, 3).Value = Mid$(CreateObject("Scriptlet.TypeLib").GUID, 2, 36)

‘environment variables:
Sheet1.Cells(11, 3).Value = Environ$("computername")
Sheet1.Cells(12, 3).Value = Environ$("username")
Sheet1.Cells(13, 3).Value = Environ$("USERPROFILE")

MsgBox "Hello " & Environ$("username")

End Sub
[/vb]

on Macintosh Excel you can try a AppleScript, something like:
[vb]
Function GetUserNameMac() As String
Dim sMyScript As String

sMyScript = "set userName to short user name of (system info)" & vbNewLine & "return userName"

GetUserNameMac = MacScript(sMyScript)
End Function
[/vb]