How to find R_HOME with VBA Environ

I can find the path to R by using Sys.getenv() like so:

Sys.getenv("R_HOME")
[1] "C:/PROGRA~1/R/R-41~1.1"

However, I am trying to find the R_HOME with the Environ function from Excel VBA with a subroutine something like:

Sub findR()
Dim a As String
a = Environ$("R_HOME")
MsgBox a
End Sub

The message box returns empty. R_HOME is in the environment but for some reason Environ can't find it. See a screenshot attached showing R_HOME in the environment. Could someone point me in the right direction to find R_HOME with Environ VBA?

This VBA function, GetREnviron, is designed to retrieve environment variables from an R installation, such as R_HOME, by running a small R script through the Windows command line.

Parameter EnvName: The function takes one parameter, EnvName, which is the name of the environment variable you want to retrieve (e.g., "R_HOME").

R Script Construction: The function dynamically creates a small R script (Rscript -e "cat(Sys.getenv('EnvName'))") to retrieve the value of the specified environment variable using R's Sys.getenv function.

Shell Execution: It uses the WScript.Shell object to execute the R script and capture the output (StdOut.ReadAll). This output is the value of the environment variable.

Trimming Output: Any extra whitespace or newline characters are trimmed from the output.

Error Handling: If something goes wrong (e.g., R is not installed, or the variable doesn't exist), the function returns a generic error message, "Error: Unable to retrieve EnvName".

This approach allows for dynamically retrieving any R environment variable by passing its name as an argument to the function.

Function GetREnviron(Name As String, Optional TrimResult As Boolean = True) As String
    Dim RScript As String
    Dim ShellOutput As String
    On Error GoTo ErrHandler
        
    Rem R script to print the specified environment variable
    RScript = "Rscript -e ""cat(Sys.getenv('" & Name & "'))"""
        
    Rem Create a Shell object
    With VBA.CreateObject("WScript.Shell")
        Rem Run the R script and capture the output
        ShellOutput = .Exec(RScript).StdOut.ReadAll
    End With
        
    Rem Trim any extra whitespace from the output
    GetREnviron = IIf(TrimResult, Trim(ShellOutput), ShellOutput)
        
    Exit Function
    
ErrHandler:
    GetREnviron = "Error: Unable to retrieve " & Name
End Function

I've updated the code to store the environmental variable. This will avoid having to use WScript.Shell more than once per variable per machine.

Function GetREnviron(Name As String, Optional UpdateSetting As Boolean = False, Optional TrimResult As Boolean = True) As String
    Dim RScript As String
    Dim ShellOutput As String
    Dim SavedSettings As Variant
    Dim StoredValue As String
    Dim AppName As String
    Dim Section As String
    On Error GoTo ErrHandler
    
    AppName = "REnvironSettings"  ' Application name for SaveSetting
    Section = "EnvironmentVars"    ' Section to store environment variables
    
    ' Retrieve all saved settings
    SavedSettings = GetAllSettings(AppName, Section)
    
    ' Check if the variable is already saved and UpdateSetting is False
    If Not UpdateSetting And Not IsEmpty(SavedSettings) Then
        On Error Resume Next
        StoredValue = GetSetting(AppName, Section, Name, "")
        On Error GoTo ErrHandler
        If Len(StoredValue) > 0 Then
            GetREnviron = StoredValue
            Exit Function
        End If
    End If

    ' If not saved or UpdateSetting is True, run the R script to get the environment variable
    RScript = "Rscript -e ""cat(Sys.getenv('" & Name & "'))"""
        
    ' Create a Shell object and capture the output
    With VBA.CreateObject("WScript.Shell")
        ShellOutput = .Exec(RScript).StdOut.ReadAll
    End With
        
    ' Trim any extra whitespace from the output
    ShellOutput = IIf(TrimResult, Trim(ShellOutput), ShellOutput)
    
    ' Save the retrieved environment variable value
    SaveSetting AppName, Section, Name, ShellOutput
    
    ' Return the result
    GetREnviron = ShellOutput
    
    Exit Function
    
ErrHandler:
    GetREnviron = "Error: Unable to retrieve " & Name
End Function

Addendum

It turns out we can get R_HOME by reading the Windows Registry.

Rem Function to get the value from the registry
Function GetRegistryValue(keyPath As String, valueName As String) As String
    Dim Reg As Object
    Dim Key As String
    Dim Value As String

    Rem Create WScript.Shell to interact with the registry
    Set Reg = CreateObject("WScript.Shell")

    Rem Try to read the registry value
    On Error Resume Next
    Value = Reg.RegRead("HKEY_LOCAL_MACHINE\" & keyPath & "\" & valueName)
    On Error GoTo 0

    Rem Return the result (the registry value)
    GetRegistryValue = Value
End Function

Rem Function to get the R installation directory path
Function GetR_HOME() As String
    Rem Call GetRegistryValue to retrieve the R "InstallPath" from the registry
    GetR_HOME = GetRegistryValue("SOFTWARE\R-core\R", "InstallPath")
End Function