如何更快地将Excel数据传递至VBA Shell中运行的R脚本

当前通过保存整个Excel工作簿再让R读取的方式效率较低，以下是几种更高效的替代方案：

1. 剪贴板直接传递（无需文件） #

适合中等规模数据，跳过文件IO步骤，直接通过系统剪贴板传输：

VBA修改部分 #

在调用R脚本前，将目标数据复制到剪贴板：

Sub RunRScriptWithClipboard(Rpath As String, scriptPath As String)
    Dim command As String
    Dim shell As Object
    Dim targetRange As Range
    
    ' 指定要传递的数据区域（示例为Sheet1的A1到D100）
    Set targetRange = ThisWorkbook.Sheets("Sheet1").Range("A1:D100")
    targetRange.Copy ' 复制数据到剪贴板
    
    ' 构建R执行命令
    command = Chr(34) & Rpath & Chr(34) & " " & Chr(34) & scriptPath & Chr(34)
    Debug.Print command
    
    Set shell = CreateObject("WScript.Shell")
    shell.Run command, 1, True
End Sub

R脚本部分 #

直接从剪贴板读取数据：

# Windows环境下直接读取剪贴板数据
df <- read.delim("clipboard", header = TRUE, sep = "\t")
# 后续数据处理逻辑...

若需跨平台或更稳定的剪贴板操作，可使用clipr包：

library(clipr)
df <- read_clip_tbl()

2. 命令行参数传递（适合小数据集） #

将数据转成CSV格式字符串作为命令行参数传给R，完全避免文件操作：

VBA修改部分 #

把目标数据转成CSV字符串，作为参数传递给R脚本：

Sub RunRScriptWithArgs(Rpath As String, scriptPath As String)
    Dim command As String
    Dim shell As Object
    Dim targetRange As Range
    Dim csvStr As String
    Dim r As Long, c As Long
    
    Set targetRange = ThisWorkbook.Sheets("Sheet1").Range("A1:D100")
    ' 构建CSV格式字符串
    csvStr = ""
    For r = 1 To targetRange.Rows.Count
        For c = 1 To targetRange.Columns.Count
            csvStr = csvStr & """" & Replace(targetRange.Cells(r, c).Value, """", """""") & """"
            If c < targetRange.Columns.Count Then csvStr = csvStr & ","
        Next c
        If r < targetRange.Rows.Count Then csvStr = csvStr & vbCrLf
    Next r
    
    ' 构建含CSV参数的命令行
    command = Chr(34) & Rpath & Chr(34) & " " & Chr(34) & scriptPath & Chr(34) & " " & Chr(34) & csvStr & Chr(34)
    Debug.Print command
    
    Set shell = CreateObject("WScript.Shell")
    shell.Run command, 1, True
End Sub

R脚本部分 #

解析命令行参数中的CSV字符串：

args <- commandArgs(trailingOnly = TRUE)
csv_str <- args[1]
# 从字符串读取数据为数据框
df <- read.csv(text = csv_str, header = TRUE)
# 后续数据处理逻辑...

注意：命令行参数存在长度限制，仅适合小体量数据。

3. 临时CSV文件（比保存xlsx高效） #

仅导出需要的数据到临时CSV文件，R读取后自动删除临时文件，比保存整个工作簿速度快很多：

VBA修改部分 #

Sub RunRScriptWithTempCSV(Rpath As String, scriptPath As String)
    Dim command As String
    Dim shell As Object
    Dim tempPath As String
    
    ' 生成临时CSV文件路径
    tempPath = Environ("TEMP") & "\temp_data.csv"
    ' 导出指定区域到CSV
    ThisWorkbook.Sheets("Sheet1").Range("A1:D100").ExportAsFixedFormat _
        Type:=xlTypeCSV, Filename:=tempPath, Quality:=xlQualityStandard
    
    ' 传递临时文件路径给R脚本
    command = Chr(34) & Rpath & Chr(34) & " " & Chr(34) & scriptPath & Chr(34) & " " & Chr(34) & tempPath & Chr(34)
    Debug.Print command
    
    Set shell = CreateObject("WScript.Shell")
    shell.Run command, 1, True
    
    ' 执行完成后删除临时文件
    Kill tempPath
End Sub

R脚本部分 #

读取临时CSV文件：

args <- commandArgs(trailingOnly = TRUE)
temp_path <- args[1]
df <- read.csv(temp_path, header = TRUE)
# 后续数据处理逻辑...

4. COM直接调用R（性能最优） #

通过R的COM服务器，VBA直接与R内存交互，数据无需落地文件或通过命令行传递，是效率最高的方案：

第一步：启用R的COM服务器 #

在R中执行以下命令（只需配置一次）：

install.packages("RDCOMClient")
library(RDCOMClient)
comRegisterServer()

VBA代码 #

直接调用R对象传递并处理数据：

Sub CallRDCOM()
    Dim r As Object
    Dim df As Object
    Dim targetRange As Range
    Dim dataArr As Variant
    Dim colData As Variant
    
    Set targetRange = ThisWorkbook.Sheets("Sheet1").Range("A1:D100")
    dataArr = targetRange.Value ' 将数据读入VBA数组
    
    ' 创建R应用对象
    Set r = CreateObject("R.Application")
    r.Visible = True ' 可选，显示R窗口便于调试
    
    ' 将VBA数组转换为R数据框
    Set df = r.Evaluate("data.frame()")
    For c = 1 To UBound(dataArr, 2)
        ' 转换列数据格式适配R
        colData = r.Evaluate("c(" & Join(Application.Transpose(Application.Index(dataArr, , c)), ",") & ")")
        ' 以Excel表头作为R数据框列名
        df.Add colData, Key:=targetRange.Cells(1, c).Value
    Next c
    
    ' 在R中执行数据处理（示例：新增计算列）
    r.Evaluate("df$new_col <- df$col1 + df$col2")
    ' 将处理结果转回VBA数组
    resultArr = r.Evaluate("as.matrix(df)")
    
    ' 将结果写回Excel指定区域
    ThisWorkbook.Sheets("Sheet2").Range("A1").Resize(UBound(resultArr, 1), UBound(resultArr, 2)).Value = resultArr
    
    ' 清理资源
    r.Quit
    Set r = Nothing
End Sub

此方案适合大量数据交互，但需提前配置R的COM环境。

内容的提问来源于stack exchange，提问作者FG120