【VBScript】VBScriptからExcelのマクロのサブルーチンに引数を渡して呼び出すには

この記事では、VBScriptからExcelのマクロのサブルーチンに引数を渡して呼び出す方法についてご説明します。

ちなみに、Excelのマクロのサブルーチンを呼び出すのに、引数なしの場合についても記事にしてありますので、そちらも参照して頂けたら幸いです。

【VBScript】VBScriptからExcelのマクロのサブルーチンを呼び出すには

【動画】VBScriptからExcelのマクロのサブルーチンを呼び出す実際の動き

本題に入る前に、まずは次の動画をご覧ください。


Excelのマクロのサブルーチンを呼び出すVBScriptを実行することで、Excelのマクロのサブルーチンが実行されます。

今回用意したVBScriptが呼び出すExcelのマクロのサブルーチンは、「test」という文字列をテキストファイルに書き出して保存する処理です。

「test」という文字列はVBScript側で用意して、Excelのマクロのサブルーチンを呼び出す際に渡しています。

サブルーチンがVBScript側から受け取った「test」という文字列をテキストファイルに書き出して保存しています。

コードの流れ

VBScriptのコード

STEP.1
Excelアプリケーションのオブジェクトを作成する
Excelアプリケーションのオブジェクトを作成します。
STEP.2
Excelファイルを開く
Excelファイルを開きます。
STEP.3
Excelファイルのモジュールにあるプロシージャを実行する
Excelファイルのモジュールにあるプロシージャを実行します。
プロシージャを実行するRunメソッドの引数には、プロシージャ名とプロシージャに渡したい値を指定します。

VBScriptが呼び出すExcelのマクロのサブルーチンの例

今回は次のExcelファイルを用意しました。

このExcelファイルは、VBScriptに呼び出されるマクロのサブルーチンを用意してあります。

Option Explicit

Sub WriteTextFile(str As String)

    Dim filePath    As String      '出力するテキストファイルの場所
    
    '出力するテキストファイルの場所を取得する
    filePath = ThisWorkbook.Path & "\test.txt"
    
    'テキストファイルを開く
    Open filePath For Output As #1
    
    'テキストファイルに「test」の文字列を書き込む
    Print #1, str
    
    'テキストファイルを閉じる
    Close #1
    
End Sub

以上のサブルーチンは「WriteTextFile」という名前で、標準モジュール「mdl_test」内に存在しています。Excelファイルの名前は「0002.xlsm」です。

ちなみに「WriteTextFile」では何をやっているのかというと、テキストファイルを生成し、そのテキストファイルに変数「str」の文字列を書き込んで保存している処理を行っています。

サブルーチン「WriteTextFile」の引数には変数strを指定しており、このstrにはVBScriptから渡された値が格納されます。

Sub WriteTextFile(str As String)

変数strをPrint # ステートメントの引数に指定し実行することで、変数strの値がテキストファイルに書き込まれて保存されます。

例えばVBScriptから「test」という文字列が渡された場合は、「test」という文字列をマクロのプロシージャが受け取って変数「str」に格納され、変数「str」に格納された「test」の文字列がテキストファイルに書き込まれて保存されます。

また、VBScriptから「WriteTextFile」が呼び出されて実行されると、Excelファイルと同じ場所に「test.txt」という名前のテキストファイルが生成されます。

テキストファイルの中身を見てみると、「test」という文字列が書き込まれています。

コードの例

Option Explicit

Dim objExcel		'Excelアプリケーションオブジェクト用変数

'Excelアプリケーションのオブジェクトを作成する
Set objExcel = CreateObject("Excel.Application")

'Excelファイルを開く
objExcel.Workbooks.Open("C:\work\10_勉強\16_VBScript\0002\0002.xlsm")

'開いたExcelファイルを非表示にする
objExcel.Visible = False

'プロシージャを呼び出すのに、Runメソッドを実行する
'第1引数には標準モジュール・プロシージャ名を、第2引数にはプロシージャに渡したい値を措定する
'⇒第1引数:標準モジュール「mdl_test」のプロシージャ「sample」を指定
' 第2引数:プロシージャ側に渡したい値「test」を指定

objExcel.Run "mdl_test.WriteTextFile", "test"

'Excelを終了する
objExcel.Quit

'後処理
Set objExcel = Nothing

注目すべきコード①

最初に見て頂きたいのは6行目です。

'Excelアプリケーションのオブジェクトを作成する
Set objExcel = CreateObject("Excel.Application")

コードの説明

以上のコードは、Excelアプリケーションのオブジェクトを作成する処理のコードです。

Excelアプリケーションのオブジェクトを作成することで、VBScriptからExcelファイルを開いたり(Openメソッド実行)、サブルーチンを呼び出す(Runメソッド実行)ことができるようになります。

注目すべきコード②

次に見て頂きたいのは9行目です。

'Excelファイルを開く
objExcel.Workbooks.Open("C:\work\10_勉強\16_VBScript\0002\0002.xlsm")

コードの説明

以上のコードは、ExcelアプリケーションのオブジェクトがExcelファイルを開く処理のコードです。

Excelファイルを開くことで、VBScriptからExcelファイルのマクロのサブルーチンを呼び出すことができるようになります。

注目すべきコード③

次に見て頂きたいのは19行目です。

'プロシージャを呼び出すのに、Runメソッドを実行する
'第1引数には標準モジュール・プロシージャ名を、第2引数にはプロシージャに渡したい値を措定する
'⇒第1引数:標準モジュール「mdl_test」のプロシージャ「sample」を指定
' 第2引数:プロシージャ側に渡したい値「test」を指定

objExcel.Run "mdl_test.WriteTextFile", "test"

コードの説明

以上のコードは、開いたExcelファイルのマクロのサブルーチンを呼び出す処理のコードです。

サブルーチンを呼び出すにはExcelアプリケーションのオブジェクトのRunメソッドを実行します。

Runメソッドには2つの引数を指定します。

Runメソッドの第1引数にはサブルーチンがあるモジュール名と、そのサブルーチン名を指定します。

モジュール名とサブルーチン名は以下のように記述します。

モジュール名とサブルーチン名の記述

「モジュール名」+「.」+「サブルーチン名」
→モジュール名とサブルーチン名の間に「.」を挟んで記述します。

Runメソッドの第2引数にはサブルーチンに渡したい値を指定します。

今回のサンプルコードでは「test」の文字列を指定していますが、このテストの値はサブルーチンの引数に渡されます。

サブルーチンの引数のコード(Excelのマクロ側のコード)

VBScript側から渡される値は、以下のExcelのマクロ側のコードにあるサブルーチンWriteTextFileの引数に指定されている変数strに格納されます。
 

Sub WriteTextFile(str As String)

注目すべきコード④

次に見て頂きたいのは22行目です。

'Excelを終了する
objExcel.Quit

コードの説明

以上のコードは、開いていたExcelファイルを閉じる処理です。

サブルーチンが呼び出されたのでQuitメソッドを実行してExcelファイルを閉じます。

動作確認

VBScript実行前

VBScript実行前のフォルダの状態です。

VBScript実行後

VBScript実行すると、「test.txt」が生成されました。

生成された「test.txt」を開くと、「test」の文字列が書き込まれていることが確認できました。

最後に

この記事では、VBScriptからExcelのマクロのサブルーチンに引数を渡して呼び出す方法についてご説明しました。

Excelファイルを開かずにサブルーチンを呼び出す方法の一つに「VBScriptから呼び出す」方法があります。

「VBScriptから呼び出す」のに、何か値をExcelのマクロのサブルーチンに渡したいことはあるかと思います。

なので、VBScriptからExcelのマクロのサブルーチンに引数を渡して呼び出したいときは本記事を参考にしてみてくださいね。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→ TechAcademyの「1週間 無料体験」はこちら