RのコードをExcel VBA で実行する #R

この記事では、Rのプログラムを、Excel VBA から実行する方法を説明します。

Excel VBAでRファイルを実行するメリット

Excelってすごく便利で、わかりやすい表やグラフも表示できて、VBAを使うといろいろな業務を効率化することもできますよね。でも、残念なことに、複雑な計算をVBAで実行するのはかなり大変なので、Rの手を借りたくなることも多くあると思います。

そんな時に、VBAの途中でRを一時的に呼び出して計算をすることができると大変助かります。いちいちRStudioを起動するのも面倒ですし。

私も、Rに渡すデータをVBAでcsvに出力して、Rに計算させて、計算結果のcsvをVBAで取り込んで、Excelで表を見るという使い方をよくしています。Excelの高い表作成能力と、Rの計算力のいいとこどりをしている使い方です。

とても便利なので、以下にVBAからRを実行する方法をまとめておきます。

設定の流れ

事前準備として、
1. 環境変数の設定
2. 実行したいコードが書かれたRファイルの準備
3. Rを実行するためのバッチファイルの準備
を行っておく必要があります。

これらについては、Rのコードを決まった時間に自動的に実行すると同じですので、こちらの記事も参考にしてください。

なお、3.でバッチファイルを作成しておくと、一度にたくさんのコマンドを実行する時に便利ですが、一つのRファイルを実行するだけなら、直接実行することもできるので3.は省略しても大丈夫です。

Step 1 - 環境変数の設定

環境変数を設定しておくと、Rのバージョンを変えた時や、Rを呼び出すVBAを他の人と共有するときに便利です。一度設定すると、Rのバージョンを変えるまで再設定の必要はありません。

設定の仕方がわからない人は、Rのコードを決まった時間に自動的に実行するという記事のステップ１を参考にしてください。

Step 2 実行したいコードが書かれたRファイルの準備

（【Rのコードを決まった時間に自動的に実行する】のStep2 と同じです。）

Rのコードを実行したいのだから、Rのコードを準備するのは当然なのですが、一つ注意点があります。コードの中でsetwd()関数を使って作業ディレクトリを指定しておきましょう。しておかないと、どこかデフォルトのフォルダで実行されちゃいます。

なお、フォルダの区切りは \ ではなく/ なので注意しましょう。

今回実行するコードは次のようなコードです。

setwd("D:/Dropbox/データ分析/test")
library(tidyverse)
print("testです。")
A <- matrix(rnorm(100),ncol = 2)
write.csv(A,"rand_matrix.csv")

サンプルなのでなんでもいいんですが、今回は適当に何かライブラリを読み込んでみて、さらにコードが実行されたことがわかるようにcsvの出力もするコードにしてみましょう。

Step 3 Rを実行するためのバッチファイルの準備

（ここも【Rのコードを決まった時間に自動的に実行する】のStep3 と同じです。）

次に、バッチファイルにRファイルを実行するコマンドを書きましょう。
"D:\Dropbox\データ分析\test\test.r" にあるRファイルを実行したいとすると、下のようになります。これだけ知っておけばいいと思います。私もこれしか知りません。

rem これはコメントです。以下のRファイルを実行します。
Rscript D:\Dropbox\データ分析\test\test.r

rem 10秒待機するコマンド。実行の確認などに。
timeout /t 10

rem 実行が終わった後コマンドプロンプトを閉じないコマンドは　cmd /k です。
rem cmd /k

文字コードを指定できるので実行時に文字化けしないものを選んでください。

VBA からRを実行する

ここからが本題です。VBAからcmdファイルを実行することで、Rファイルを実行しましょう。

まずは、VBEを開いて、"Windows Script Host Objedct Model" への参照設定をしておきましょう。

次にVBAのプロシージャを作成します。先ほど作成した"test実行バッチ.cmd"を実行するには次のように書きます。


Sub test_run()
    'Windows Script Host Objectmodel を参照設定
    Dim Wsh As WshShell
    Set Wsh = New WshShell

    'WaitOnReturn は実行完了まで待つかどうか。
    Call Wsh.Run("D:\Dropbox\データ分析\バッチ\test実行バッチ.cmd", WaitOnReturn:=True)

    MsgBox ("Rのコードを実行しました。")
End Sub

WaitOnReturn 引数をFalse にするとVBAはcmdファイルが完了するまで待たずに、次のプロセスに進みます。

cmdファイルを作らずにRファイルを直接実行する

先ほどのVBAコードはcmdファイルを実行しましたが、直接 Rscript D:\Dropbox\データ分析\test\test.rを実行しに行くのが下のコードです。

Sub test_run2()
    'Windows Script Host Objectmodel を参照設定
    Dim Wsh As WshShell
    Set Wsh = New WshShell

    'WaitOnReturn は実行完了まで待つかどうか。
    Call Wsh.Run("Rscript D:\Dropbox\データ分析\test\test.r", WaitOnReturn:=True)


    MsgBox ("Rのコードを実行しました。")
End Sub

実行確認のために10秒待つ、timeout /t 10など不要な場合はこれでもいいですね。

実行してみる

ボタンにマクロを登録して押してみるとこんな感じで、コマンドプロンプトが立ち上がり、Rの実行が始まります。WaitOnReturn:=Trueなので、cmdのコマンドが全部実行されるまでVBAは待ってくれています。

実行が終わるとコマンドプロンプトが消えて、msgbox が実行完了を報告してくれました。

どうでしょうか、簡単にRを呼び出すことができましたね。この方法を使ってExcelとRのいいとこどりをして、分析や業務を効率的に進めていきましょう。