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
- grid.arrange's relative height argument didn't works well in RMarkdown
- How apply a function to every element of a matrix?
- Polar plot in R using ggplot2
- Find total length of time in multiple values of a dataset that overlaps with a given period of time
- Faster than R's `simplify2array`?
- What is the most simple way to label a data.table frequency chart in R?
- How to show dates in a secondary x-axis while the primary x-axis is numerical?
- online compiler for r-markdown document
- Unecessarily large column widths in HTML tables published by R Quarto when the table header contains special characters
- Bootstrapping a vector of results, by group in R
- Speeding Up Monte Carlo Simulations for Weather-Dependent Claims
- Is there an R class which allows to list 2 elements but shows only the first of them by default when called?
- Convert day of week number to weekday name in R
- How do you use "<<-" (scoping assignment) in R?
- Using reactivePoll to monitor and process file changes
- How to find out which graph is most similar to another graph?
- ggplot2: rectangular background behind axis text in circular bar plot
- How to simulate button click using rvest
- CMIP6, automating by month
- Covert dummy variables to single categorical in R?
- Animated reducing bar chart with gganimate
- Create an object class matrix/array from data frame
- How to add a vector of names to `sort_by()` in base R
- Among many subfolders, find CSVs starting with a given string and within ZIPs or not, and merge them while adding their names in a new column with R
- How to add a greek letter to an axis label to a 3D plot in plotly?
- bigrquery + dplyr error when using functions and sourcing from separate R script
- Shiny App Error: /v1/applications/ 400 - Validation Error Execution halted
- Rupee Unicode replacement not working in R
- Mutate multiple variable to create multiple new variables
- How to use the Stata equivalent of a foreach loop in R with mutate()