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

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

すぐにソースコードを見たい場合はこちらをクリック

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

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

Table of Contents

【動画】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に格納されます。