Introduction

Hey everyone,

Today, I want to share a neat way to bridge the gap between R and Excel using VBA. Specifically, we’ll look at how to run VBA code in Excel directly from R. This can be incredibly useful if you’re looking to automate repetitive tasks or leverage the power of VBA while working within the R environment.

We’ll use the RDCOMClient library, which allows R to control COM (Component Object Model) objects, such as an Excel application. If you’ve ever found yourself toggling between R and Excel, this method will streamline your workflow significantly.

What We’ll Do

We’ll write a VBA macro that populates cells A1:A10 with random numbers and then run this macro from R.

Step-by-Step Guide

1. Install and Load RDCOMClient

First, you’ll need to install the RDCOMClient package. It’s not available on CRAN, so you have to install it from the omegahat repository.

install.packages("RDCOMClient", repos = "http://www.omegahat.net/R")
library(RDCOMClient)

2. Set Up Excel and VBA Macro

Open Excel and press ALT + F11 to open the VBA editor. Insert a new module and add the following VBA code:

Sub FillRandomNumbers()
    Dim i As Integer
    For i = 1 To 10
        Cells(i, 1).Value = Rnd()
    Next i
End Sub

This macro fills cells A1 to A10 with random numbers.

3. Run the VBA Macro from R

Now, let’s write the R code to open Excel, run the macro, and then close Excel.

# Load the RDCOMClient library
library(RDCOMClient)

# Create a new instance of Excel application
excel_app <- COMCreate("Excel.Application")

# Make Excel visible (optional)
excel_app[["Visible"]] <- TRUE

# Add a new workbook
wb_path <- "C:\\Users\\steve\\Documents\\GitHub\\steveondata\\posts\\2024-06-28\\vba_rand_from_r.xlsm"
workbook <- excel_app[["Workbooks"]]$Open(wb_path)

# Reference the first sheet
sheet <- workbook$Worksheets(1)

# Run the macro
excel_app$Run("FillRandomNumbers")

NULL

# Save the workbook (optional)
workbook$SaveAs("C:\\Users\\steve\\Documents\\GitHub\\steveondata\\posts\\2024-06-28\\random_numbers.xlsm")

[1] TRUE

# Close Excel
excel_app$Quit()

NULL

# Release the COM object
rm(excel_app)
rm(sheet)
rm(workbook)
gc()

          used (Mb) gc trigger (Mb) max used (Mb)
Ncells  666819 35.7    1477710   79  1122664 60.0
Vcells 1220653  9.4    8388608   64  1770896 13.6

Explanation

1. Initialize Excel Application: COMCreate("Excel.Application") starts a new instance of Excel.
2. Make Excel Visible: This step is optional but useful for debugging.
3. Add Workbook and Reference Worksheet: We create a new workbook and reference the first sheet.
4. Run the Macro: excel_app$Run("FillRandomNumbers") executes the macro.
5. Save Workbook: Optionally save the workbook with the generated random numbers.
6. Close and Clean Up: Close Excel and clean up the COM object to free up resources.

Did it work?

Let’s make sure this actually worked.

library(readxl)

read_excel(
  path = "C:\\Users\\steve\\Documents\\GitHub\\steveondata\\posts\\2024-06-28\\random_numbers.xlsm",
  col_names = FALSE
)

# A tibble: 10 × 1
     ...1
    <dbl>
 1 0.706 
 2 0.533 
 3 0.580 
 4 0.290 
 5 0.302 
 6 0.775 
 7 0.0140
 8 0.761 
 9 0.814 
10 0.709 

Try It Yourself

Give this a try and see how you can extend it to your own needs. Whether it’s automating report generation, data cleaning, or complex calculations, integrating R with VBA can enhance your productivity significantly. The RDCOMClient package opens up a world of possibilities for combining the strengths of R and Excel.

Feel free to share your experiences or ask questions in the comments.

Happy coding!

Steve