R: Run VBA function from R

Question

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 (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!

It seems that the whole gist of that VBA code is to color the text. Have you considered `openxlsx` and changing cell styles programmatically, wholly from R? — r2evans, Jun 05 '23 at 00:25
@ r2evans: thank you for your reply! I have not considered this option - do you think it might be worth it? If you have time, could you please show me how to use it? Thank you so much! — stats_noob, Jun 05 '23 at 01:56

score 3 · Answer 1 · answered Jun 05 '23 at 02:14

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")

@ r2evans: thank you so much for this cool approach! i appreciate it! — stats_noob, Jun 05 '23 at 03:51

score 1 · Answer 2 · answered Jun 04 '23 at 22:50

1

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.

answered Jun 04 '23 at 22:50

hello_friend

5,682
1
11
15

@ hello_friend: thank you so much for your answer! If you have time, can you please show me how to do this? Thank you so much! – stats_noob Jun 04 '23 at 23:14
VBS Script to Run VBA Macro: https://stackoverflow.com/a/10232925/9203158 – hello_friend Jun 04 '23 at 23:15
Bat File Run R Script: https://stackoverflow.com/questions/6788928/how-to-run-a-r-language-r-file-using-batch-file – hello_friend Jun 04 '23 at 23:16
There are some tweeks you will want to make to the above that ensure your scripts are blocking processes etc. You will learn more as you branch out your research from the above. Happy automation ! – hello_friend Jun 04 '23 at 23:22
@ hello_friend : thank you so much for your links! I am still new to computer programming and am not sure if I am understanding everything correctly. Here is what I understand so far: – stats_noob Jun 05 '23 at 01:58
Step 1: copy this R code into a notepad file and then save this notepad file with a ".bat" extension: df = structure(list(var1 = c(4.39524353447787, 7.6982251051672, 25.5870831414912, 10.7050839142458, 11.2928773516095, 27.1.....), row.names = c(NA, -10L), class = "data.frame") write.csv(df, "df.csv") – stats_noob Jun 05 '23 at 01:59
Step 2: Where do I save the VBA macro? – stats_noob Jun 05 '23 at 02:01
@stats_noob no, your RScript is your RScript, a .R file, a batch file is an orchestration layer that calls the RScript. The VBA Macro is in excel, the VBScript is a stand alone VBS file. – hello_friend Jun 05 '23 at 02:55

Parfait · Accepted Answer · 2023-06-06T00:50:25.630

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

@ Parfait: thank you for your answer! Your code produced a few errors- here is the first one: — stats_noob, Jun 05 '23 at 02:03
+ if(cell_value == xlApp$WorksheetFunction().Max(rowRng)) { Error: unexpected symbol in: " if(is.integer(cell_value) | is.numeric(cell_value)) { if(cell_value == xlApp$WorksheetFunction().Max" — stats_noob, Jun 05 '23 at 02:03
With slight modifications and able to test, solution works great on my end! — Parfait, Jun 06 '23 at 00:51
@ Parfait: Wow! Thank you so much for your edits! This works perfectly now! — stats_noob, Jun 06 '23 at 00:53
one question I had about your answer: I noticed that you modified the structure of my VBA macro. For example: — stats_noob, Jun 06 '23 at 00:55
Your VBA macro: cellRng[["Interior"]][["Color"]] = RGBtoINT(199, 254, 126) — stats_noob, Jun 06 '23 at 00:55
Would my VBA macro have also worked? Or would only the way you re-wrote the VBA macro work? If so, is there some logic I can follow? Thank you so much! — stats_noob, Jun 06 '23 at 00:57
I did not write a VBA macro! But what is the issue? What re-structuring? Both return the same integer value. Check in VBA Window (Ctrl+G) `Debug.Print RGB(199, 254, 126)`. R does not have `RGB`, so we create a user-defined method, `RGBtoINT()` — Parfait, Jun 06 '23 at 01:00

R: Run VBA function from R

3 Answers3