Jak używać zmiennych środowiskowych w ścieżkach do plików i folderów?

Ostatnio spotkałem się z pewnym problemem. Otóż napisałem dla Excela prostą aplikację generującą mini raport. W ustawieniach arkusza podałem ścieżkę do folderu, w którym ma być zapisywany raport. I tutaj pojawia się mały problem – ponieważ pracuję na dwóch różnych komputerach to najwygodniej zapisać raport w folderze, który się synchronizuje. Dla mnie takim folderem jest folder w usłudze OneDrive. Ale aby nie było za łatwo, na dwóch różnych komputerach ścieżka do takiego folderu jest inna (np. na pierwszym komputerze mam c:\Users\Damian\OneDrive a na drugim c:\Users\Trener\OneDrive). No więc wpadłem na pomysł: zmienna środowiskowa %OneDrive% zawiera ścieżkę do folderu OneDrive. No więc wpisałem taki oto kod:

Sub TestOneDrive()
    Open "%onedrive%\1.txt" For Output As #1
    Print #1, "test"
    Close #1
End Sub

Niestety, ale wyskoczył błąd Path not found. Jak widać instrukcje plikowe w VBA nie obsługują zmiennych środowiskowych.

Aby kod poprawnie zadziałał należałoby użyć instrukcji Environ, która zwraca zmienną środowiskową o danej nazwie. Ten kod już zadziała:

Sub TestOneDrive2()
    Open Environ("onedrive") & "\1.txt" For Output As #1
    Print #1, "test"
    Close #1
End Sub

Ale to nie do końca rozwiązuje mój problem. Otóż nie chcę na sztywno w kodzie makra wpisywać ścieżki do folderu. Chciałbym, aby użytkownik w arkuszu z opcjami mógł wpisać tę ścieżkę i używać dowolnych zmiennych środowiskowych (nie tylko %onedrive%). Przykładowo, aby zapisać plik na pulpicie mogę wpisać następującą ścieżkę:

c:\users\%USERNAME%\Desktop lub bezpieczniej (nie zawsze folder z użytkownikami to c:\users\): %HOMEPATH%\Desktop

Tutaj na szczęście nie ma problemu. Otóż zmienna środowiskowa to ciąg znaków zawarty pomiędzy znakami procentów np. %USERNAME% Można więc napisać prostą funkcję, która wyciągnie ze ścieżki takie ciągi znaków i zastąpi je zawartością zwróconą przez funkcję Environ. Tutaj aż się prosi o wykorzystanie wyrażeń regularnych do tego celu! Oto prosta funkcja, która w ścieżce podmieni zmienne środowiskowe na ich zawartość:

Function ReplaceEnvVariables(text As String) As String
    Dim regex As Object
    Dim matches As Object
    Dim match As Object
    Dim varName As String
    Dim envValue As String
    
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "%(.*?)%"
    regex.Global = True
    Set matches = regex.Execute(text)
    For Each match In matches
        varName = match.SubMatches(0)
        envValue = Environ(varName)
        If envValue <> "" Then
            text = Replace(text, "%" & varName & "%", envValue)
        End If
    Next match
    
    ReplaceEnvVariables = text
End Function

Zwróć uwagę na wzorzec wyrażenia regularnego:

%(.*?)%

Ten pytajnik po gwiazdce mówi nam, że wyrażenie regularne nie jest zachłanne. Tzn mając np. taki ciąg znaków:

%SystemDrive%\Users\%Username%\Desktop

Wyrażenie regularne %(.*)% (bez pytajnika po gwiazdce, czyli zachłanne) zwróciłoby ciąg znaków:

%SystemDrive%\Users\%Username%