Excel/VBAでフォームからプロシージャにデータを送る最適解（異論求む）

2022/04/08

例えば、以下のようにシート上のボタンを押したらUserForm1が現れ、UserForm1のボタンを押すと、TextBox1に入力した数値の10倍をA1セルに入力されるプログラムを組んだとします。この時、計算はプロシージャ側で行うものとします。

よくあるパターンとして、以下のようなプログラムを組むんじゃないかと思います。ボタン押下でtestプロシージャを呼び出してUserForm1を表示させるってやつです。calプロシージャはUserForm1からのコールバック用です。

Sub test()
    UserForm1.Show
End Sub

Sub cal(a)
    Sheets("Sheet1").Range("A1").Value = a * 10
End Sub

UserForm1側のコードはこちら。ボタン押下後の処理です。

Private Sub CommandButton1_Click()
    a = CInt(TextBox1.Text)
    Call cal(a)
    Me.Hide
End Sub

ちょっとしたマクロだったらこんな感じで作るんじゃないでしょうか。でも、UserFormの再利用という観点からすると、欠点の多いことがわかります。例えば別シートの異なるセルに入力するとなった場合です。上のプログラムはシート名もセル位置も固定なので再利用が困難になります。結果、ほぼ同じUserFormを作ってしまうというケースが散見されます。

これに対する僕の今のところの最適解は以下です。UserFormを再利用するしないに限らずこのような方法を取っています。

「Common」という標準モジュールを用意しておく。※モジュール名は任意
そこにPublic変数を用意しておく。
FormからのデータはそのPublic変数に投げて、標準プロシージャはそこからデータを受け取る。
Form呼び出しとその後の処理は１つのプロシージャ内で賄う。

以下のようにPublic変数用のCommonモジュールを用意しています。ここには共通で使う関数も書いたりします。この中にBoolean変数を用意することが肝です。

以下がUserFormを呼び出してその後処理するまでのコードです。１つのプロシージャにまとめています。validというBoolean変数を使うことでUserFormの状態を検知するようにしています。一旦Falseにしておいて、UserFormでTrueにした場合のみ処理実行するという流れです。

Sub test()
    Common.valid = False
    UserForm1.Show
    
    If Common.valid = True Then
        Sheets("Sheet1").Range("A1").Value = Common.d * 10
    End If
End Sub

以下がUserFormでボタンを押した時の処理です。Public変数に値を投げてUserFormを閉じるだけとなります。

Private Sub CommandButton1_Click()
    Common.valid = True
    Common.d = CInt(TextBox1.Text)
    Me.Hide
End Sub

基本的に後で読み返しやすいプログラムを目指しているのですが、どうしてもUserFormが絡むとプロシージャが分離したりして複雑化しやくなります。いろいろ試してみて今こんな感じに落ち着いています。現時点で最適解と思っていますが、他に良い方法があったらいつでもアップデートするので連絡ください。

- Excel, VBA