R: Run VBA function from R

I am working with the R programming language.

I have the following dataset:

df = structure(list(var1 = c(4.39524353447787, 7.6982251051672, 25.5870831414912, 
10.7050839142458, 11.2928773516095, 27.1506498688328, 14.609162059892, 
-2.65061234606534, 3.13147148106474, 5.54338029900042), var2 = c(22.2408179743946, 
13.5981382705736, 14.0077145059405, 11.1068271594512, 4.44158865245925, 
27.8691313680308, 14.9785047822924, -9.66617156629638, 17.0135590156369, 
5.27208592272066), var3 = c(-0.678237059868451, 7.82025085341705, 
-0.260044483072397, 2.7110877070886, 3.74960732150743, -6.86693310742413, 
18.3778704449452, 11.5337311783652, -1.38136937011948, 22.5381492106993
), var4 = c(14.2646422147681, 7.04928517007729, 18.9512566104502, 
18.7813348753304, 18.2158108163749, 16.8864025410009, 15.5391765353759, 
9.38088289423278, 6.94037336260083, 6.19528998987617)), row.names = c(NA, 
-10L), class = "data.frame")

write.csv(df, "df.csv")

Here is a VBA function I have that I am running from the VBA editor within Excel:

Sub ColorCellsRowWise()
    Dim myRange As Range
    Set myRange = Range("A2:D11")

    Dim row As Range
    For Each row In myRange.Rows
        Dim cell As Range
        For Each cell In row.Cells
            If IsNumeric(cell.Value) Then
                If cell.Value = Application.WorksheetFunction.Max(row) Then
                    cell.Interior.Color = RGB(253, 127, 127)
                ElseIf cell.Value = Application.WorksheetFunction.Large(row, 2) Then
                    cell.Interior.Color = RGB(252, 181, 128)
                ElseIf cell.Value = Application.WorksheetFunction.Large(row, 3) Then
                    cell.Interior.Color = RGB(253, 247, 127)
                Else
                    cell.Interior.Color = RGB(199, 254, 126)
                End If
            End If
        Next cell
    Next row
End Sub

My Question: I am wondering, is it possible to run this VBA function directly in R?

I tried to follow the instructions here (https://stackoverflow.com/questions/19404270/run-vba-script-from-r):

library("devtools")
install_github('omegahat/RDCOMClient')


current_dir <- getwd()
file_path <- file.path(current_dir, "df.csv")
df <- read.csv(file_path)

xlApp <- COMCreate("Excel.Application")
xlWbk <- xlApp$Workbooks()$Open(file_path)

But from here I do not know what to do.

I am working on an R script where "df" is created in R, then saved as a CSV - and then I want to run the VBA script all in one shot. The approach in the above link seems like this might not be possible.

Can someone please show me how to do this?

Thanks!

Perhaps you don't need VBA?

library(openxlsx)

wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")
writeData(wb, 1, df)

rowranks <- t(apply(-df, 1, rank))
stylecolors <- c(
  rgb(253, 127, 127, maxColorValue = 255),
  rgb(252, 181, 128, maxColorValue = 255),
  rgb(253, 247, 127, maxColorValue = 255),
  rgb(199, 254, 126, maxColorValue = 255)
)
rowranks[] <- stylecolors[rowranks]
rowranks
#       var1      var2      var3      var4     
#  [1,] "#FDF77F" "#FD7F7F" "#C7FE7E" "#FCB580"
#  [2,] "#FDF77F" "#FD7F7F" "#FCB580" "#C7FE7E"
#  [3,] "#FD7F7F" "#FDF77F" "#C7FE7E" "#FCB580"
#  [4,] "#FDF77F" "#FCB580" "#C7FE7E" "#FD7F7F"
#  [5,] "#FCB580" "#FDF77F" "#C7FE7E" "#FD7F7F"
#  [6,] "#FCB580" "#FD7F7F" "#C7FE7E" "#FDF77F"
#  [7,] "#C7FE7E" "#FDF77F" "#FD7F7F" "#FCB580"
#  [8,] "#FDF77F" "#C7FE7E" "#FD7F7F" "#FCB580"
#  [9,] "#FDF77F" "#FD7F7F" "#C7FE7E" "#FCB580"
# [10,] "#FDF77F" "#C7FE7E" "#FD7F7F" "#FCB580"

for (i in 1:nrow(df)) {
  for (j in 1:ncol(df)) {
    addStyle(wb, 1, style = createStyle(fgFill = rowranks[i, j]), rows = i+1, cols = j)
  }
}

saveWorkbook(wb, "/tmp/quux.xlsx")

I would change your architecture a little, and run the R script from a .bat file (assuming you are running windows) and also run the VBA in your workbook via a VBS script, which is also being run by the same .bat file. In this way you can orchestrate your process in the order you desire. That might seem a bit cumbersome, but that's a fairly standard approach to on-prem automation tasks.

Essentially, you need to translate that VBA function to R and RDCOMClient calls. However, some methods you call are VBA functions (e.g., RGB) and others are part of the Excel object library (e.g., Application.*).

Because R for loops cannot iterate on Excel objects like VBA's For Each, consider integer iteration which entails use of Cells and Range.Cells properties.

Finally, as best practice in dealing with COM objects, always clean out the background process whether you face an error or not which you can handle with R's tryCatch.

library(RDCOMClient)

csvData <- file.path(getwd(), "df.csv", fsep="\\")
RGBtoINT <- function(R, G, B) (65536 * B + 256 * G + R)

tryCatch({
    # INITIALIZE COM OBJECT
    xlApp <- COMCreate("Excel.Application")
    xlApp[["DisplayAlerts"]] <- FALSE

    # CREATE WORKBOOK
    xlWbk <- xlApp$Workbooks()$Add()

    xlWks <- xlWbk$Worksheets(1)
    xlWks[["Name"]] <- "DATAFRAME"

    # IMPORT CSV DATA
    xlQt <- xlWks$QueryTables()$Add(
      Connection=paste0("TEXT;", csvData),
      Destination=xlWks$Range("A1")
    )
    xlQt[["TextFileParseType"]] <- 1
    xlQt[["TextFileCommaDelimiter"]] <- TRUE
    xlQt$Refresh(BackgroundQuery=FALSE)
    xlQt$Delete()

    # ITERATE THROUGH ROWS AND CELLS
    for(row in 1:11) {
        rowRng <- xlWks$Range(xlWks$Cells(row, 1), xlWks$Cells(row, 4))

        for(col in 1:4) {
            cellRng <- xlWks$Cells(row, col)
            cell_value <- cellRng$Value()

            if(is.integer(cell_value) | is.numeric(cell_value)) {
                if(cell_value == xlApp$WorksheetFunction()$Max(rowRng)) {
                    cellRng[["Interior"]][["Color"]] = RGBtoINT(253, 127, 127)
                } else if(cell_value == xlApp$WorksheetFunction()$Large(rowRng, 2)) {
                    cellRng[["Interior"]][["Color"]] = RGBtoINT(252, 181, 128)
                } else if(cell_value == xlApp$WorksheetFunction()$Large(rowRng, 3)) {
                    cellRng[["Interior"]][["Color"]] = RGBtoINT(253, 247, 127)
                } else {
                    cellRng[["Interior"]][["Color"]] = RGBtoINT(199, 254, 126)
                }
            }
        }
    }

    # SHOW BACKGROUND APP
    xlApp[["Visible"]] <- TRUE

}, warning = identity

, error = function(e) {
    identity(e)
    
    # CLOSE OBJECTS
    if(exists("xlQt")) xlQt$Delete()
    if(exists("xlWbk")) xlWbk$Close(FALSE)
    if(exists("xlApp")) xlApp$Quit()

}, finally = {
    # RELEASE COM RESOURCES
    rowRng <- NULL; cellRng <- NULL; 
    xlQt <- NULL; xlWks <- NULL; xlWbk <- NULL; xlApp <- NULL
    rm(rowRng, cellRng, xlWks, xlWbk, xlApp)
})

Aside: Do note VBA is NOT part of Microsoft Excel but an external component (by default connected to the software). It is just another language to connect via COM to Excel's object library as most general purpose programming languages can such as R.

Input

df <- structure(list(var1 = c(4.39524353447787, 7.6982251051672, 25.5870831414912, 
10.7050839142458, 11.2928773516095, 27.1506498688328, 14.609162059892, 
-2.65061234606534, 3.13147148106474, 5.54338029900042), var2 = c(22.2408179743946, 
13.5981382705736, 14.0077145059405, 11.1068271594512, 4.44158865245925, 
27.8691313680308, 14.9785047822924, -9.66617156629638, 17.0135590156369, 
5.27208592272066), var3 = c(-0.678237059868451, 7.82025085341705, 
-0.260044483072397, 2.7110877070886, 3.74960732150743, -6.86693310742413, 
18.3778704449452, 11.5337311783652, -1.38136937011948, 22.5381492106993
), var4 = c(14.2646422147681, 7.04928517007729, 18.9512566104502, 
18.7813348753304, 18.2158108163749, 16.8864025410009, 15.5391765353759, 
9.38088289423278, 6.94037336260083, 6.19528998987617)), row.names = c(NA, 
-10L), class = "data.frame")

write.csv(df, "ColorFormatCell.csv", row.names=FALSE)

Output