エクセルVBAのボタン設置で引数を渡してマクロを呼び出す方法

★気に入ったらシェアをお願いします!

buttons

photo credit: door bells via photopin (license)

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

エクセルVBAでプログラムを動かす際にボタンを設置しますよね。

ボタンを設置しておけば、わざわざVBEを呼び出す必要もなくなりますし、開発タブを追加していない方でもプログラムを動作させることができるようになります。

しかし、そんなボタンですが、こうは思ったことはないですか?

ボタンに引数を渡せたらなぁ…と。

ほとんど同じような処理なのに、わざわざボタンごとにプロシージャをしたためるのもバカバカしいというとき、ありますよね。

今回はその悩みにお応えして、ボタンに登録したVBAマクロを引数を渡しながら実行する方法についてお伝えします。

では、よろしくお願いします。

一般的なボタン設置とマクロの登録方法

まずは引数を渡さない場合のボタン設置とマクロの登録方法をおさらいしておきます。

例えばこのような簡単なSubプロシージャを作ります。

これを新規で作成したボタンにマクロ登録していきます。

設置したいシートを開いてリボンから「開発」→「挿入」→「ボタン(フォームコントロール)」です。

エクセルVBAでフォームコントロールボタンを設置

トラップ的な感じですぐ下に「ActiveXコントロール」のボタンもあるのですが、VBAの神様いわく

一生触らなくて良い

とのことです。フォームコントロールだけ使いましょう。

設置するボタンの大きさをドラッグで決めると、マクロの登録ウィンドウが開きます。ここで登録したいマクロを選択してOKです。

エクセルVBAでボタンにマクロを登録

これでボタンへのマクロの登録は完了です。

ボタンを押せば

エクセルVBAでボタンを押してマクロを実行

ちゃんと実行がされます。

ボタンに応じた引数を渡してマクロを呼び出す

では、こちらの図のようにボタンを二つ設置して

エクセルVBAでボタンを2つ設置

それぞれ

「ボタンAが押されました」
「ボタンBが押されました」

とするにはどうすれば良いでしょうか?

わざわざそれぞれのボタン用のSubプロシージャを作ると処理が重複してしまいますので望ましくありませんね。

この場合は、それぞれのボタンに応じた引数を渡した上でマクロを呼び出せばOKですね。

引数を渡してマクロを登録する方法ですが、マクロの登録ウィンドウで

‘マクロ名 引数’

とします。記述のルールとしては

  • マクロ名の続きで半角スペースを空けて引数
  • 引数が文字列型、日付型の場合は引数をダブルクォーテーションで囲む
  • 引数を複数指定したいときはカンマで区切る
  • マクロ名と引数全体をシングルクォーテーションで囲む

となります。

今回のボタンAについては

エクセルVBAのボタンに引数を渡しつつマクロを登録

と設定すればOKですね。

ボタンA、ボタンBのマクロの登録は

button.xlsm!’showMessage “A”‘
button.xlsm!’showMessage “B”‘

となります。

一方でSubプロシージャのほうも引数の受け皿を用意する必要があります。

ボタンに設定した引数はstrという文字列型の変数に格納されます。

そしてその変数を使ってMsgBoxの文字列を生成しています。

こちらを実行してみますと

エクセルVBAのボタンで引数を渡してマクロを実行

ボタンに応じたメッセージが表示されます。

引数があったりなかったりする場合

別の例を考えてみましょう。

例えばこちらの図のように二つのボタンを配置します。

エクセルVBAでボタンを2つ配置

ボタンには引数を渡さず、ボタン2にだけ整数2を引数を渡したいというときがあるとします。

その場合、それぞれのボタンに登録するマクロは

button.xlsm!’showMessage’
button.xlsm!’showMessage 2′

となります。

呼び出すSubプロシージャはこんなふうに用意してみました。

しかし、ボタン2は無事に実行できるのですが、ボタンのほうを押すと

エクセルVBAの引数は省略できませんのエラー表示

「引数は省略できません」とエラーになってしまいます。

Optionalキーワードで引数を省略可能とする

このように引数があったりなかったりする場合は、受け取る側でOptionalキーワードをつけて省略可能な引数であることを明示します。

Subプロシージャをこのように変更します。

これによりnumという変数は省略可能で、省略した場合は1が代入されるという形になります。

こちらでボタンを押してみますと

エクセルVBAで省略可能な引数を渡してボタンによるマクロ実行

省略した場合の数値1が適用されて無事に実行されました。

まとめ

ボタンに登録したVBAマクロを引数を渡しながら実行する方法についてお伝えしました。

Optionalキーワードで引数を省略することもできます。

その他、引数の渡し方は色々とできますので、作成するプログラムに合わせて工夫をしてみてくださいね。