Category Archives: VBA

VBA

VBA environment variables (ENVIRON)

To see a list of variables, create a sub:

 
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
 

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

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

on Macintosh Excel you can try a AppleScript, something like:

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