パーツに分けて作ることのメリットは、前回、お話ししました。
今回は、もうひとつの方法、「ユーザー定義関数」を使う方法を紹介します。
サブプロシージャと、ユーザー定義関数は、とてもよく似ています。
違いがあるのは、ユーザー定義関数は戻り値があるということです。
つまり、メインプロシージャは、ユーザー定義関数から何らかの値を受け取ることができるのです。
ユーザー定義関数を使うと、様々な計算を外部にまかせるため、とても見栄えがよくなります。
下の例は、データのあるセルの合計を計算するプロシージャです。
データが、とびとびで入力されているため、最初の位置と最後の位置を求めて計算しています。
| Sub test() Dim R As Long Dim 合計 As Integer If 最初 = 0 Then ←③ MsgBox "データがありません。" Exit Sub End If For R = 最初 To 最後 ←① 合計 = 合計 + Cells(R, 1) ←② Next R MsgBox 合計 End Sub |
≪メインプロシージャ≫ ① For ～ Next文で繰り返し処理を行います。 （最初という値から、最後という値まで） ※ともに、ユーザー定義関数から値をもらっています。 ② 合計を計算します。 ③ データが無いときは、メッセージを表示して終了します。 |
| Function 最初() As Long ←④ Dim 数 As Long Dim R As Long 数 = Range("A:A").Count If Cells(1, 1) <> "" Then R = 1 Else R = Cells(1, 1).End(xlDown).Row If R = 数 And Cells(R, 1) = "" Then R = 0 End If 最初 = R End Function |
④ ユーザー定義関数 「最初」 1列目のデータの中で、最初のデータのある行の位置を返します。 （データが無いときは、０を返す） |
| Function 最後() As Long ←⑤ Dim 数 As Long Dim R As Long 数 = Range("A:A").Count If Cells(数, 1) <> "" Then R = 数 Else R = Cells(数, 1).End(xlUp).Row If R = 1 And Cells(R, 1) = "" Then R = 0 End If 最後 = R End Function |
⑤ ユーザー定義関数 「最後」 1列目のデータの中で、最後のデータのある行の位置を返します。 （データが無いときは、０を返す） |
通常、ユーザー定義関数が戻り値として返すのは、１つの値だけです。
しかし時として、２つの値が欲しいときがあります。
例えば、ある数値の位置を、行の位置と列の位置で返して欲しいときです。
こういう場合は、ひと工夫すれば可能です。
それでは、下のプロシージャを見てみましょう。
これは、５×５のマスの中で、ある数値がどこにあるか、その座標を返すものです。
| Sub test() Dim n As Integer n = 位置(12) ←① MsgBox "行位置は、" & n \ 10 & "行目です。" _ & vbCr & "列位置は、" & n Mod 10 & "列目です。" ←② End Sub |
① 上の表から、ある数字がどの場所にあるのか、 行と列の位置を返します。 ( )の中には、探したい数値を入れます。 戻り値は、2桁の整数で、十の位が行位置、 一の位が列位置です。 ② \は、余りがある割り算の商の部分、 MODは、余りがある割り算の余りの部分を それぞれ求めます。 |
| Function 位置(数値 As Integer) As Integer Dim R As Integer Dim C As Integer For R = 1 To 5 For C = 1 To 5 If Cells(R, C) = 数値 Then Exit For ←③A Next C If Cells(R, C) = 数値 Then Exit For ③B Next R 位置 = R * 10 + C ←④ End Function |
③A③B セルの値が、引数の数値と同じだったら、 For～Next文から抜け出ます。 ④ 求めた行位置・列位置を、ひとつの値にまとめます。 行位置は十の位に、列位置は一の位になります。 ※見つからなかった場合は、６６を返します。 （これは、For ～ Next文の性質を利用しています。） |
ユーザー定義関数は、計算結果を返すのが本来の目的ですが、サブプロシージャと同じように処理をすることもできます。
さらに、ユーザー定義関数は戻り値を返せるので、実際に処理を行ったかどうか、マクロ上で確認することができます。
下のプロシージャは、前にも出てきた例ですが、ユーザー定義関数として修正しています。
| Sub test() If 色付け2(4, 2, 3) = True Then ←① MsgBox "処理しました。" Else MsgBox "処理しませんでした。" End If End Sub |
① ユーザー定義関数で、 「4列目で、２未満の値を、赤色（３）にする」処理をさせ、 処理が True（できた）ときは、「処理しました。」の メッセージを表示します。 |
| Function 色付け2(列 As Integer, 基準 As Integer, _ 色番号 As Integer) As Boolean ←② Dim R As Long For R = 1 To 10 If Cells(R, 列) < 基準 Then ←③ Cells(R, 列).Interior.ColorIndex = 色番号 色付け2 = True ←④ End If Next R End Function |
② 戻り値として、ブール型(Boolean)を指定しています。 ブール型は、True か False で戻り値を返します。 ③ 指定した列で、基準値より小さい値のあるセルの色を、 色番号の色に変えます。 ④ 処理をしたとき、True を返します。 |