How to extract R InstallPath from Registry using Excel VBA
I am not able to extract the R path anymore from the registry. I have a 64 bit machine and Excel 2016 32 bit as well. The small R functions below used to work well with R 4.1.3 MS 365 but now that I updated to R 4.4.2 the function returns an empty path even though R exists in the registry. Could someone give me a hint? See screen shot below:
'Function to get the R installation directory path
Function GetR_HOME() As String
'GetRegistryValue to retrieve the R "InstallPath" from the registry
GetR_HOME = GetRegistryValue("SOFTWARE\R-core\R", "InstallPath")
End Function
'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
'Create WScript.Shell to interact with the registry
Set Reg = CreateObject("WScript.Shell")
'Try to read the registry value
On Error Resume Next
Value = Reg.RegRead("HKEY_LOCAL_MACHINE\" & keyPath & "\" & valueName)
On Error GoTo 0
'Return the result (the registry value)
GetRegistryValue = Value 'EMPTY VALUE RETURNED'
End Function
Solution
In case you have not resolved your issue, I have put together the following code which will read both 32-bit and 64-bit registry keys when running under 32-bit VBA. The code currently caters for string values and DWORD values but can be expanded to handle the other data types found in the registry. Simply create a new module and then copy and paste the code into it:
Option Explicit
#If VBA7 Then
Private Declare PtrSafe Function RegOpenKeyEx _
Lib "advapi32.dll" _
Alias "RegOpenKeyExA" _
(ByVal hKey As LongPtr, _
ByVal lpSubKey As String, _
ByVal ulOptions As Long, _
ByVal samDesired As Long, _
phkResult As LongPtr) As Long
Private Declare PtrSafe Function RegQueryValueEx _
Lib "advapi32.dll" _
Alias "RegQueryValueExA" _
(ByVal hKey As LongPtr, _
ByVal lpValueName As String, _
ByVal lpReserved As LongPtr, _
lpType As Long, _
lpData As Any, _
lpcbData As Long) As Long
Private Declare PtrSafe Function RegCloseKey _
Lib "advapi32.dll" _
(ByVal hKey As LongPtr) As Long
#Else
Private Declare Function RegOpenKeyEx _
Lib "advapi32.dll" _
Alias "RegOpenKeyExA" _
(ByVal hKey As Long, _
ByVal lpSubKey As String, _
ByVal ulOptions As Long, _
ByVal samDesired As Long, _
phkResult As Long) As Long
Private Declare Function RegQueryValueEx _
Lib "advapi32.dll" _
Alias "RegQueryValueExA" _
(ByVal hKey As Long, _
ByVal lpValueName As String, _
ByVal lpReserved As Long, _
lpType As Long, _
lpData As Any, _
lpcbData As Long) As Long
Private Declare Function RegCloseKey _
Lib "advapi32.dll" _
(ByVal hKey As Long) As Long
#End If
Private Const ERROR_SUCCESS = 0&, _
HKEY_CLASSES_ROOT = &H80000000, _
HKEY_CURRENT_USER = &H80000001, _
HKEY_LOCAL_MACHINE = &H80000002, _
HKEY_USERS = &H80000003, _
HKEY_CURRENT_CONFIG = &H80000005, _
STANDARD_RIGHTS_ALL = &H1F0000, _
KEY_QUERY_VALUE = &H1, _
KEY_SET_VALUE = &H2, _
KEY_CREATE_SUB_KEY = &H4, _
KEY_ENUMERATE_SUB_KEYS = &H8, _
KEY_NOTIFY = &H10, _
KEY_CREATE_LINK = &H20, _
KEY_WOW64_64KEY = &H100, _
KEY_WOW64_32KEY = &H200, _
SYNCHRONIZE = &H100000, _
ERROR_NO_MORE_ITEMS = 259&, _
KEY_READ_32 = (&H20019 Or KEY_WOW64_32KEY) And (Not SYNCHRONIZE), _
KEY_READ_64 = (&H20019 Or KEY_WOW64_64KEY) And (Not SYNCHRONIZE)
Private Const KEY_ALL_ACCESS_64 = ((STANDARD_RIGHTS_ALL Or _
KEY_QUERY_VALUE Or _
KEY_ENUMERATE_SUB_KEYS Or _
KEY_NOTIFY Or _
KEY_CREATE_SUB_KEY Or _
KEY_SET_VALUE Or _
KEY_WOW64_64KEY Or _
KEY_CREATE_LINK) And _
(Not SYNCHRONIZE)), _
KEY_ALL_ACCESS_32 = ((STANDARD_RIGHTS_ALL Or _
KEY_QUERY_VALUE Or _
KEY_ENUMERATE_SUB_KEYS Or _
KEY_NOTIFY Or _
KEY_CREATE_SUB_KEY Or _
KEY_SET_VALUE Or _
KEY_WOW64_32KEY Or _
KEY_CREATE_LINK) And _
(Not SYNCHRONIZE))
Private Const REG_NONE = 0, _
REG_SZ = 1, _
REG_EXPAND_SZ = 2, _
REG_BINARY = 3, _
REG_DWORD = 4, _
REG_DWORD_BIG_ENDIAN = 5, _
REG_DWORD_LITTLE_ENDIAN = 4, _
REG_LINK = 6, _
REG_MULTI_SZ = 7, _
REG_RESOURCE_LIST = 8, _
REG_FULL_RESOURCE_DESCRIPTOR = 9, _
REG_RESOURCE_REQUIREMENTS_LIST = 10, _
REG_QWORD = 11
Public Function GetR_HOME() As String
'GetRegistryValue to retrieve the R "InstallPath" from the registry
GetR_HOME = GetRegistryValue(HKEY_LOCAL_MACHINE, "SOFTWARE\R-core\R", "InstallPath")
End Function
'Function to get the value from the registry
#If VBA7 Then
Public Function GetRegistryValue(lngSection As LongPtr, _
keyPath As String, _
strValueName As String) As String
Dim hKey As LongPtr
#Else
Public Function GetRegistryValue(lngSection As Long, _
keyPath As String, _
strValueName As String) As String
Dim hKey As Long
#End If
Dim lngResult As Long, _
lngValueType As Long, _
strBuf As String, _
lngDataBufSize As Long, _
lngDWORD As Long
If RegOpenKeyEx(lngSection, keyPath, 0&, KEY_READ_64, hKey) <> ERROR_SUCCESS Then
If RegOpenKeyEx(lngSection, keyPath, 0&, KEY_READ_32, hKey) <> ERROR_SUCCESS Then
MsgBox "Error opening registry key"
Exit Function
End If
End If
'retrieve information about the key
If RegQueryValueEx(hKey, strValueName, 0&, lngValueType, ByVal 0&, lngDataBufSize) = ERROR_SUCCESS Then
Select Case lngValueType
Case REG_SZ
'Create a buffer
strBuf = String(lngDataBufSize, Chr(0))
'retrieve the key's content
If RegQueryValueEx(hKey, strValueName, 0&, 0&, ByVal strBuf, lngDataBufSize) = ERROR_SUCCESS Then
'Remove trailing chr(0)'s
GetRegistryValue = Replace(strBuf, Chr(0), vbNullString)
End If
Case REG_DWORD
'retrieve the key's value
If RegQueryValueEx(hKey, strValueName, 0&, 0&, lngDWORD, lngDataBufSize) = ERROR_SUCCESS Then
GetRegistryValue = lngDWORD
End If
End Select
End If
RegCloseKey lngSection
End Function
- Retrieving expected data.frame for testthat expectation
- How to use an anonymous function with pipe operator?
- R passing RSelenium driver environment as function argument
- Keep file names merging list with lapply?
- replace strings that contains one another
- Stop parsing out zeros after decimals in ggplot2's annotate
- Plot data.tree coloring and labelling by level
- Extracting the date and time out of a date and time format
- How to reshape a dataframe into wide format with specified column pairs
- Show partial segments removed after limit scales for `geom_pointrange`
- Control ggplot2 legend look without affecting the plot
- Concatenate strings over all rows in a single column of a data frame
- Is there some way to keep variable names from.SD+.SDcols together with non .SD variable names in data.table?
- How to create mutually dependent checkbox columns in a DT table?
- A problem with an stacked, grouped and faceted graph y gg plot
- Installing R on Linux: configure: error: libcurl >= 7.28.0 library and headers are required with support for https
- Create a column conditional on multiple other character and numerical columns
- data.frame to array (2 columns)
- Changing options() in a function environment without changing options() in global environment in R?
- pivot(), group_by() and summarise() with nested data
- Why legend is not showing up in plot layout using patchwork in R?
- in dplyr::mutate, dplyr::starts_with works for .before but not .after?
- Finding optimal cut-off points for dividing a variable into equal groups considering ties in which quantiles do not work
- How to add a legend to hline?
- R Shiny Suppress slider handle for sliderInput until click
- brmultinom brglm2 vifs and dredge dont work
- R, how to get current time miliseconds, without extra information ? (Sys.time(), format("%X"))
- Installing multiple versions of R
- Command to see 'R' path that RStudio is using
- Fast pairwise longest common substring from start