How to get username in VBA?

Question

I have a VBA code that calls a Python code:

Sub Macro1()

    Dim Ret_Val
    Dim args as String
    args = """F:\Asset Management\Global Equity\Better Interface new.py"""
    Ret_val = Shell("C:\Users\MRay\anaconda3\python.exe" & args, vbNormalFocus)

End Sub

However, soon this code is going to be circulated around hence the username 'MRay' will change for each user. For the purposes of maintainability, we want to automate this process rather than have it be changed manually. This is what I've tried so far:

Sub Macro1()

    Dim Ret_Val
    Dim args As String
    Dim full_id As String
    Dim user_id(0 to 2) As String
    user_id(0) = "C:\Users\"
    user_id(1) = Application.UserName
    user_id(2) = "\anaconda3\python.exe"
    full_id = Join(user_id)
    args = """F:\Asset Management\Global Equity\Better Interface new.py"""
    Ret_val = Shell(full_id & args, vbNormalFocus)

End Sub

However, this leads to an error in the last line of Ret_val.I am really new to VBA so I just used the logic I would in Python. Any help is really appreciated:)

https://stackoverflow.com/questions/935160/environ-function-code-samples-for-vba perhaps? — BigBen, Aug 19 '19 at 15:44
No need for `Join`, just concatenate it like so: `Ret_val = Shell("C:\Users\" & Application.Username & "\anaconda3\python.exe" & args, vbNormalFocus)` - but might you be looking for `Environ("username")`? I wouldn't use `Application` here... — dwirony, Aug 19 '19 at 15:46
Can you not just use `%UserProfile%`? E.g. `%UserProfile%\anaconda3\python.exe` — Zack, Aug 19 '19 at 15:46
This requires that EVERY user have anacoda3 python installed in the expected location. Why not just copy 'python.exe' in to the same directory with your python code? Then you have one copy of the interpreter that you control. You know everyone is using the correct version. This simplifies deployment too. — HackSlash, Aug 19 '19 at 16:24

Daniel · Accepted Answer · 2019-08-19T15:53:12.430

6

I'd use enviromental variables:

full_id = "C:\Users\" & Environ("username") & "\anaconda3\python.exe"

or

full_id = Environ("userprofile") & "\anaconda3\python.exe"

/e: as Zack suggested in comments, "%UserProfile%\anaconda3\python.exe" is a good idea too!

edited Aug 19 '19 at 15:53

answered Aug 19 '19 at 15:48

Daniel

814
6
12

Good call on `Environ("userprofile")`, I often forget that exists! – dwirony Aug 19 '19 at 15:52
I use Environ("USERNAME") – SomeNerdAtWork Aug 19 '19 at 18:24

How to get username in VBA?

1 Answers1