パーツに分けて作ろう。(2)

パーツに分けて作ることのメリットは、前回、お話ししました。
今回は、もうひとつの方法、「ユーザー定義関数」を使う方法を紹介します。
サブプロシージャと、ユーザー定義関数は、とてもよく似ています。
違いがあるのは、ユーザー定義関数は戻り値があるということです。
つまり、メインプロシージャは、ユーザー定義関数から何らかの値を受け取ることができるのです。

1.ユーザー定義関数を使う。

 ユーザー定義関数を使うと、様々な計算を外部にまかせるため、とても見栄えがよくなります。
 下の例は、データのあるセルの合計を計算するプロシージャです。
 データが、とびとびで入力されているため、最初の位置と最後の位置を求めて計算しています。

Sub test()

  Dim R As Long
  Dim 合計 As Integer

  If 最初 = 0 Then ←B
    MsgBox "データがありません。"
    Exit Sub
  End If

  For R = 最初 To 最後 ←@
    合計 = 合計 + Cells(R, 1) ←A
  Next R

  MsgBox 合計

End Sub
≪メインプロシージャ≫

@ For 〜 Next文で繰り返し処理を行います。
  (最初という値から、最後という値まで)
   ※ともに、ユーザー定義関数から値をもらっています。

A 合計を計算します。

B データが無いときは、メッセージを表示して終了します。
Function 最初() As Long ←C

  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
C ユーザー定義関数 「最初」

1列目のデータの中で、最初のデータのある行の位置を返します。
(データが無いときは、0を返す)
Function 最後() As Long ←D

  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
D ユーザー定義関数 「最後」

1列目のデータの中で、最後のデータのある行の位置を返します。
(データが無いときは、0を返す)

2.ユーザー定義関数で、2つの値を戻り値として返す。

 通常、ユーザー定義関数が戻り値として返すのは、1つの値だけです。
 しかし時として、2つの値が欲しいときがあります。
 例えば、ある数値の位置を、行の位置と列の位置で返して欲しいときです。
 こういう場合は、ひと工夫すれば可能です。
 それでは、下のプロシージャを見てみましょう。
 これは、5×5のマスの中で、ある数値がどこにあるか、その座標を返すものです。

Sub test()

  Dim n As Integer

  n = 位置(12) ←@

  MsgBox "行位置は、" & n \ 10 & "行目です。" _
& vbCr & "列位置は、" & n Mod 10 & "列目です。" ←A

End Sub
 
@ 上の表から、ある数字がどの場所にあるのか、
   行と列の位置を返します。
   ( )の中には、探したい数値を入れます。
  戻り値は、2桁の整数で、十の位が行位置、
  一の位が列位置です。

A \は、余りがある割り算の商の部分、
  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 ←BA
    Next C

    If Cells(R, C) = 数値 Then Exit For BB
  Next R

  位置 = R * 10 + C ←C

End Function
 
BABB セルの値が、引数の数値と同じだったら、
   For〜Next文から抜け出ます。

C 求めた行位置・列位置を、ひとつの値にまとめます。
   行位置は十の位に、列位置は一の位になります。


   ※見つからなかった場合は、66を返します。
    (これは、For 〜 Next文の性質を利用しています。)

3.処理が実行されたかどうか、戻り値として返す。

 ユーザー定義関数は、計算結果を返すのが本来の目的ですが、サブプロシージャと同じように処理をすることもできます。
 さらに、ユーザー定義関数は戻り値を返せるので、実際に処理を行ったかどうか、マクロ上で確認することができます。
 下のプロシージャは、前にも出てきた例ですが、ユーザー定義関数として修正しています。

Sub test()

  If 色付け2(4, 2, 3) = True Then ←@
    MsgBox "処理しました。"
  Else
    MsgBox "処理しませんでした。"
  End If

End Sub
 
@ ユーザー定義関数で、
  「4列目で、2未満の値を、赤色(3)にする」処理をさせ、
  処理が True(できた)ときは、「処理しました。」の
  メッセージを表示します。
Function 色付け2(列 As Integer, 基準 As Integer, _
      色番号 As Integer) As Boolean ←A

  Dim R As Long

  For R = 1 To 10
    If Cells(R, 列) < 基準 Then ←B
      Cells(R, 列).Interior.ColorIndex = 色番号
      色付け2 = True ←C
    End If
  Next R

End Function
 
A 戻り値として、ブール型(Boolean)を指定しています。
   ブール型は、True か False で戻り値を返します。

B 指定した列で、基準値より小さい値のあるセルの色を、
  色番号の色に変えます。

C 処理をしたとき、True を返します。