VBA マクロ作成のヒント

VBA macro 作成の簡単なヒントを作成します。例題に示したのは英文として読んでも理解できそうな表現なので取り上げました。a1 〜 e50 のセル範囲で void の語を含む全てのセルを フォントの色(赤）、斜体、中央揃え に設定します。macro を使わない方法を検索セル全部選択とデータ書式の一挙設定で扱いました。

図１

図２

例題

Set myRange = Range("a1:e50"): k = 0 
With myRange
  Set c = .Find("void", LookAt:=xlPart)
   If Not c Is Nothing Then
     first = c.Address
      Do
       k = k + 1
       c.HorizontalAlignment = xlCenter
         With c.Font
          .Color = vbRed
          .Italic = True
         End With
          Set c = .FindNext(c)
      Loop While Not c Is Nothing And c.Address <> first
   End If
End With
MsgBox k & " 個のセルを設定しました。"

変数、定数について

変数

変数には漢字なども使えます。not, is, nothing など vba で予約されている語は変数として使えません。例題では myRange, k, c, first が変数です。k = 0 は Let k = 0 ( k を 0 とせよ ）の Let を省略したものです。k = k + 1 は k に 1 足した数値を k に代入せよということです。myRange は数値や文字列を格納する変数ではないので Set myRange = Range("a1:e50") のように表記します。Let と Set が相対応し Set は当然省略できません。 Evaluate メソッドを使った簡易表示で Set myRange = [a1:e50] とすることも出来ます。

(注) Set を省略して a = [a1:e50] とすれば a は次元を持った変数になってしまい、a1〜e50 のセル内容が a( , ) に代入されます。 a(3, 3) = "void" などと指定し、 [a1:e50] = a としたら c3 セルが "void" に変わります。この場合 変数 a を単独で使うことはできません。

１行目の myRange、k の変数を使わず （ つまり１行目を削除して ）、With [a1:e50] 、あるいは With Range("a1:e50") から始めてもOK です。 k は当初 『 Empty 値 』 として扱われ k = k + 1 などの演算には影響されません。しかし、長いプロシージャを作成していると １行目の記述がバグを防いだり重みをもつことが多いです。

『 検索セル全部選択と … 』 のページで扱った表部分 ( 図1 ) を選択して作業を進めたい場合は、macro 実行前に表中の任意のセルをアクティブにしておいて１行目を Set myRange = ActiveCell.CurrentRegion に変更します。 ActiveCell.CurrentRegion.Select は 通常のワークシートの表中で Ctrl + Shift + * の操作をするのと同等です。ActiveCell.CurrentRegion.Select のような簡単なステートメントの場合イミディエイトウィンドウを使うのが便利です。

プロシージャを編集するときは小文字で入力するのがよいです。自動的に大文字に変わればその変数は不適であることが分かります。反対に myRange 等の変数はわざと大文字を含めます。すると以降スペルミスをしても容易に発見できます。first は変数として使えますが second は予約されているため使えません。

定数

xlPart, xlCenter, vbRed は VBA で予約された定数で値はそれぞれ 2, -4108, 255 です。.Color = vbRed を .Color = 255 としても同じですが判読が困難になります。

入れ子構造について

例題では With … End With, 　If … End If,　 Do … Loop, さらに With … End With が入れ子になっているのが分かります。大きな箱の中に小さい箱があるという感じです。それぞれの箱は End ‥ ( Do では Loop、For では Next ) でふたをします。蓋が開いたままだと Error になります。他の例では Sub … End Sub,　 Select Case … End Select などがあります。

条件を満たせば箱から出たい場合があります。If … Then Exit For ( For … Next の場合 ), Exit Do （ Do … Loop の場合 ）, Exit Sub ( Sub … End Sub のサブルーチン ) 等を使います。

For i = 1 To 10　　　　　　For i = 1 To 10       For i = 1 To 10
  If … Then GoTo Skip       If … Then            If … Then Else _ 
   　… 　　　　　             Else                  … : … : …
　　 …                        …                Next
　   …                        … : …
Skip:　　                    End If
Next　　                   Next

例題について

１行目は前述の通り変数への代入です。

With

With を使わないで３行目を Set c = myRange.Find("void" … とし １３行目を Set c = myRange.FindNext(c) としてもかまいません。With を使うと処理速度というよりも記述の簡素化（ 緑色部分が省略できること ）がメリットです。９行目で With を使わないと c.Font.Color = vbRed, c.Font.Italic = True のように表記します。

Find, FindNext

Set c = .Find("void", LookAt:=xlPart) は Excel シート上で Ctrl + F の操作で 検索を行う場面に相当します。（ 図２参照 ） 検索でヒットすると当該セルが選択されます。『 次を検索（F) 』 を繰り返すと最初に見つかったセルに戻ります。これはいつまでも続きます。 LookAt:=xlPart は 図２の 『 オプション（T) 』 をクリックして 『 セル内容が完全に同一であるものを検索する(O) 』 にチェックが入っていない状態で検索しています。これにチェックが入った状態で検索するには LookAt:=xlWhole に変えます。

最初にセルが見つからなければ 変数 c に Nothing が代入されます。If Not c Is Nothing ( c が Nothing でなければ … 見つかれば ) 最初のセル番地、例えば c4 セルなら first に 『 $C$4 』 が代入されます。

Do, Loop While

Find メソッドでセルが見つかれば Do … Loop 間を実行します。前述した通り検索は最初に見つかったセルに戻り延々と続きます。従って最初のセルアドレスになれば終わるように While Not c Is Nothing And c.Address <> first ( セル c が見つかり、最初のセルアドレスにならない限り ） と条件を付けています。 Do … Loop を使うときは無限ループに入らないように注意する必要があります。無限ループに陥った場合は Ctrl + Break でストップさせます。

Do … Loop には、While 以外に Until を使い、Do Until condition のように先頭に記載する、あるいは condition を使わない ( 余録参照 ) など５様の使い方があります。注意したいのは終わりには終わりが含まれているのか、あるいは重複していないかなどの検討です。例題では変数 k を使い実際の個数を確かめています。

余録

VBA 画面の表示法などはmacro およびイミディエイトに記しました。

For … Next、Do … Loop の使い方の例を示します。

For Each c In myRange
   … 
Next

上の例は myRange に含まれるセル c それぞれに対して実行されます。この場合、変数 c に対して Set で指定する必要がありません。例題でも扱ったように変数 c はセルとしてよく使われますが決まっているわけではありません。MsgBox c とすればセルの値が表示されますが c は数値でも文字列でもありません。値を指定するときは c.Value を使うのが無難です。

For y = 1 To 9: For x = 1 To 9
 ActiveCell.Cells(y, x) = x * y
Next: Next

For Each c In ActiveCell.Range("a1:i9")
 c.Value = (c.Column - ActiveCell.Column + 1) * (c.Row - ActiveCell.Row + 1)
Next

n = 10
For i = 1 to n - 2
 For j = i + 1 to n - 1
  For k = j + 1 to n
   … …
   MsgBox i & "," & j & "," & k 
  Next k
 Next j
Next i

Next k などの k は省略した方がよいです。順を間違えると入れ子が崩れ Error になります。上の例は１０個のデータが配列変数に格納されていて、３個ずつをすべての組み合わせで取り出したい場合の一方法です。プロシージャの冒頭で Dim a(10) と宣言したうえで 上記の … … の部分で a1 = a(i) : a2 = a(j) : a3 = a(k) 等と代入し、以下 a1, a2, a3 を比較検討するステートメントを記述します。上記の場合 MsgBox は何回表示されるでしょうか

次の例は 100 点満点のテストの順位を求める一方法です。ワークシート上ならば rank 関数を使って容易に求めることができます。１行目で配列変数に余裕をもって 10 で宣言しています。p(1)〜p(5) は 1〜5 の人の得点、配列変数は 0 も使えるので p(0) に満点 100 を使い、rnk(0) を順位の変数に当てています。rnk(1)〜rnk(5) にはそれぞれの人の順位が求まります。

n は人数、k は順位の決まった人数を数えます。

Dim p(10), rnk(10)

p(1) =78:p(2) =89:p(3) =76:p(4) = 89:p(5) = 98　 '得点の入力
p(0) =100: n = 5: rnk(0) = 1: k = 0      '初期設定
Do
For i = 1 To n
 If p(i) = p(0) Then rnk(i) = rnk(0): k = k + 1
Next
 If k = n Then Exit Do
  p(0) = p(0) - 1         '最初100点の人を探し、次に99点を探す。
    rnk(0) = k + 1     '同点の人もいるので順位の変数を更新します。
 j = j + 1: If j > 100 Then Exit Do          '無限ループを回避
Loop

得点の入力は面倒なので p = Array(100, 78, 89, 76, 89, 98) とする方がすっきりしますが Word では使えません。終わりから２行目は誤入力による無限ループを回避するためのものです。誤入力で 125点とした人は順位の対象外となりループから出ても順位は付されません。

b = Split(" 高橋 佐藤 羽生 谷川 三浦") などと名前のデータを追加し、 例えば i = 2 と指定した上で 『 MsgBox b(i) & "さんの得点は " & p(i) & " 点で、順位は " & rnk(i) & " 位です。" 』 と記載すれば 佐藤さんの得点と順位が表示されます。コンマ区切りのデータの場合は b = Split(",高橋,佐藤,羽生,谷川,三浦", ",") とします。

この例のように、セル範囲を指定しない記述は Word でも有効となります。順位の結果を Word の表中はもちろんのこと、段落中に反映させることもできます。データを VBA 表記で入力せずに word の表中、段落中からも取り込めます。

Function quad1(a, b, c)
D = b ^ 2 - 4 * a * c
If D >= 0 Then quad1 = (-b + Sqr(D)) / (2 * a) _
 Else x = -b / (2 * a): y = Sqr(-D) / (2 * a): _
 quad1 = x & " + " & y & " i"
End Function

Function quad2(a, b, c)
D = b ^ 2 - 4 * a * c
If D >= 0 Then quad2 = (-b - Sqr(D)) / (2 * a) _
 Else x = -b / (2 * a): y = Sqr(-D) / (2 * a): _
 quad2 = x & " - " & y & " i"
End Function

最近 VBA を使って数独の解き方, スリザーリンク解法, タイ語等タイプ入力の拙作を作りました。

このサイトで扱った項目