実用(?)ヒント集

このページに含まれるプログラム、ファイル等については、

1. 一応、著作権は放棄してません。転載等される方は私にご連絡ください。
2. 内容については万全を期していますが、利用については自己責任でお願いします。いかなる損害にも責任を負いません。
3. 動作確認は、(1)Windows 98+Excel 2000; (2)Windows XP+Excel 2002で行っています。

1. Withの罠
2. 変数は使う前に定義しよう
3. 文字列結合は"&"で。
4. 変数の帰り値
5. For〜NextとDo〜Loop
6. v(1)(1)って分かります?
7. 関数の自動実行
8. フォームもオブジェクト
9. ByRef,ByVal
10. 再帰呼び出し

Withを使うと書く手間が省け、よりすっきりしたプログラムが書けます。しかし、うっかりするととんでもない落とし穴があります。次のプログラムを見てください。A1，B1，C1にそれぞれ1,2,3と記入します。

Sub test()
    ActiveSheet.Range("A1").Select
    Selection.Value = 1
    ActiveSheet.Range("B1").Select
    Selection.Value = 2
    ActiveSheet.Range("C1").Select
    Selection.Value = 3
End Sub

Sub test2()
    ActiveSheet.Range("A1").Select
    With Selection
        .Value = 1
        ActiveSheet.Range("B1").Select
        .Value = 2
        ActiveSheet.Range("C1").Select
    End With
    Selection.Value = 3
End Sub

よく言われることです。短いプログラムなら、めんどくさいでしょうが。Office VBAではOption Explicitとプログラムの頭に記述することにより、変数の宣言(定義)が義務化されます。
変数の名前の付け方にもいろいろ流儀があると思いますが、私はOffice VBAでは次のようにしてプログラミングしています。

• 変数(関数)名に大文字を含めて宣言する。
• 変数(関数)を使用するところではわざと全て小文字で入力する。

• 使用するメモリが少なくなる。(その場限りで消滅するようにFunction/Subプロシージャ内で宣言するのもさらに効果的だと思います。)
• 処理速度が速くなる(たぶん)。
• 必要な変数が決まっているような関数に入力するような場合は構文エラーが検出できる。
• 入力が簡単になる。

Dim ls As XlLineStyle

文字列(String)はC言語なんかをちょっとかじっていれば分かると思いますが、他の数字とは扱いが異なります。にもかかわらず、VBAでは「+」でつなげることが出来たりします。ここで、VBAお得意の「賢い自動変換」がかかわってくるので話がややこしくなります。次の例を見てください。

Sub aaa()
    MsgBox "1" + "2"    '12
    MsgBox 1 + "2"      '3
    MsgBox "1" + 2      '3
    MsgBox 1 + 2        '3
    MsgBox "a" + 1      'error!
End Sub

    MsgBox "a" + Cstr(1)

    MsgBox "a" & 1

下のプログラムではキャンセルとして、Nullを返しています。この方法はUserFormのトグルボタン(三状態可能にするとTrue,False,Nullを返す)からヒントを得たものです。ところで、この方法はVariantでないと使えません。かといって他の変数型で0や負の値を返したりするとまずい場合も良くあります。特にTrueかFalseかで返したいときにキャンセルが発生したりしたらどうしようか?とよく頭を悩まします(私だけ?)。
解決方法は色々あると思います。下にいくつか考えたものを書いてみます。

'1. True, False, Null
Function foo1(arg As Variant) As Variant
	foo1 = True	'True
	foo1 = False	'False
	foo1 = Null	'キャンセル
End Function

'2. 自分で設定
Const ReturnTrue As Integer = 1
Const ReturnFalse As Integer = 2
Const ReturnCancel As Integer = 3
Function foo2(arg As Variant) As Integer
	foo2 = ReturnTrue	'True
	foo2 = ReturnFalse	'False
	foo2 = ReturnCancel	'キャンセル
End Function

'3. 値は参照渡し、キャンセルかどうかは関数の帰り値
Function foo3(arg As Variant,Check As Boolean) As Boolean
	Check = True: foo3 = True	'True
	Check = False: foo3 = True	'False
	Check = False: foo3 = False	'キャンセル
End Function

'4. MsgBoxの帰り値を流用する
Function foo4(arg As Variant) As vbMsgBoxResult
	foo4 = vbYes	'True
	foo4 = vbNo	'False
	foo4 = vbCancel	'キャンセル
End Function

基本的なところは別のところでごらんいただくとして、今回は使い分けで注意しなければいけないところを 書いてみようと思います。

Sub test()
    Dim i As Integer, max As Integer
    max = 5
    For i = 1 To max
        MsgBox i
        max = max - 1   '☆
    Next i
    i = 1: max = 5
    Do While i <= max
        MsgBox i
        i = i + 1
        max = max - 1   '☆
    Loop
    i = 1: max = 5
    Do
        MsgBox i
        i = i + 1
        max = max - 1   '☆
    Loop While i <= max
End Sub

Sub test2()
    Dim i As Integer, s As String
    s = "teddddst"
    For i = 1 To Len(s)
        MsgBox Mid(s, i, 1)
        If Mid(s, i, 1) = "d" Then
            s = Left(s, i - 1) & Right(s, Len(s) - i)
            i = i - 1
        End If
    Next i
    MsgBox s
End Sub

Microsoftの公式ページを見てると勉強になることがあります。ちょっと見てみると配列を返す関数を作成する方法というのが載っています。C言語では配列や構造体(VBAで言うTypeみたいなものです)で返す方法を用いていましたので、配列についてはすぐに納得しました。
ところがArray()を用いて返す方法というのが載っていますが、何のことかわかりませんでした。Helpを見るとVariantに配列を代入するための関数のようです。JavaScriptのようですね。確認のために以下の関数を作ってみました。

Option Base 1

Function func()
    Dim v
    v = Array(Array(1, 2), Array(3, 4))
    func = v
End Function

Function func2()
    Dim a(2, 2)
    a(1, 1) = 1: a(1, 2) = 2: a(2, 1) = 3: a(2, 2) = 4
    func2 = a
End Function

1   2
3   4

Sub test()
    Dim v, w, x
    v = Array(Array(1, 2), Array(3, 4))
    For Each w In v         '一重ではエラーになります。
        For Each x In w     '1,2,3,4の順に回ります。
            MsgBox x
        Next x
    Next w
End Sub

Sub test2()
    Dim a(2, 2), b
    a(1, 1) = 1: a(1, 2) = 2: a(2, 1) = 3: a(2, 2) = 4
    For Each b In a         'なぜか、1,3,2,4の順に回ります。
        MsgBox b
    Next b
End Sub

Sub test3()
    Dim v, v2
    v = Array(Array(1, 2), Array(3, 4))
    v2 = v(1)       'Array(1, 2)の部分が代入されます。
    MsgBox v2(1)    '1を返します。
    MsgBox v(1)(1)  '同じく1を返します。
End Sub

Sub test4()
    Dim v
    v = Array(Array(1, 2, 3), Array(4, 5), Array(6, 7, 8))
    MsgBox v(1)(3)  '3
    MsgBox v(3)(3)  '8
    MsgBox v(2)(3)  'Error!
End Sub

ある時間が来れば、自動的にマクロを実行するという便利な命令があります。OnTimeメソッドです。

expression.OnTime(EarliestTime, Procedure, LatestTime, Schedule)

Sub my_Procedure()
    処理
    Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"
End Sub

以前の話でダイアログで進行度を現すプログラムを掲載しました。例えば、2つのタイマーを同時に表示する場合はどうすれば良いでしょう?別に同じフォームをもう一つ作る?いえいえ、そうではないのです。
題にあるようにフォームもユーザー定義のオブジェクトです。ということは、次の命令で実体を複数個作ることが可能です。

    Dim t As New TimerForm, t2 As New TimerForm
    t.Show: t2.Show

    Dim t As New TimerForm, t2 As TimerForm
    Set t2 = t

SubプロシージャやFunctionプロシージャ等で受け取る引数にByRef、ByValのキーワードを付けることができます。これは変数の引き渡し方を参照渡しにするか値渡しにするかを決めるキーワードです。(省略したときはByRefになります。)
参照渡し(ByRef)とは変数をそのまま渡します。一方、値渡し(ByVal)は変数のコピーを作って渡します。図書を借り出すかコピーを取って持ち帰るかの違いのようなものです。持ち帰った図書を破ってしまうと今後、読むときに困りますが、コピーをいくら破っても図書自体には何の影響もありません。同様に、ByRefで渡した変数を書き換えると呼び出した元の関数の変数も変わってしまいますが、ByValで渡した場合は元の関数の変数に変化は及びません。
ByValの方が関数の独立性が保つためにはよりよい方法ですが、元の関数に変化した変数を返したいときには不便ですし、コピーのときに若干時間がかかります。また、BASICという言語はもともと参照渡しが基本でしたので、VBAにおいても、省略したときにはByRefが基本となっています。
一方、ByValは引数の型チェックが甘く、コンパイル時にはチェックされません。Variantの変数をIntegerの引数に渡すことも可能です。変換できない場合は実行時エラーとなります。(ByRefはコンパイルエラーとなります。)

ところで、Range等のオブジェクト変数はどうでしょう?ByValで渡すとどこかにセルを作って渡してくれるのでしょうか?
実はセル(オブジェクト)のコピーは作成されません。ByRefで渡してもByValで渡してもr.Value=2とすれば元の関数の変数で指し示していたセルの内容が変わってしまいます。それではオブジェクト変数ではByRef、ByValのキーワードが無効なのでしょうか?
オブジェクト変数とは以前書きましたが、それ自身は実体を持ちません。在る場所を指し示しているだけです。つまり、名刺のようなものです。そして引数のByRefとByValの違いは名刺自体を渡すかコピーを取って渡すかの違いといえます。
名刺を参考に会いに行けば、名刺自体だろうがコピーだろうが本人に会いに行くことができます。ところが、その名刺の例えば部署名を書き換えてしまったら、本人に会いに行くことができなくなります。元の関数の変数にまでその変化が及ぶのがByRefでそうではないのがByValなのです。次のプログラムを参考にしてみてください。

プログラム

Sub test()
    Dim r As Range
    Set r = ActiveSheet.Range("A1")
    subtest01 r
    MsgBox r.Address & ": " & r.Value, , "ByRef" '$A$2: 2
    Set r = ActiveSheet.Range("A1")
    subtest02 r
    MsgBox r.Address & ": " & r.Value, , "ByVal" '$A$1: 3
End Sub

Sub subtest01(ByRef r As Range)
    r.Value = 1
    Set r = r.Offset(1)
    r.Value = 2
End Sub

Sub subtest02(ByVal r As Range)
    r.Value = 3
    Set r = r.Offset(1)
    r.Value = 4
End Sub

ある人物の年齢を調べる。さらに、子供たち一人一人の年齢を調べる。さらにその子供の子供の年齢を…
こういう場合、再帰呼び出しという手法を使います。詳しく、このルーチンを考えて見ると、

1. 初期化して、人物に関してプロシージャを呼び出す。
2. 年齢を調べる。
3. 子供がいるかどうかを確認する。
4. 子供がいたら、子供全員に関してプロシージャを呼び出す。(2に戻る)
5. 子供がいなかったら、プロシージャの処理を終わる。(親の4に戻り、次の子の処理に移る)
6. 全部の人物のチェックが終了したら、終了の処理をする。

プログラム

Sub NewItems()
    Dim App As Outlook.Application
    Dim NowFolder As Outlook.MAPIFolder
    Dim Result As String
    Set App = CreateObject("Outlook.Application")
    Set NowFolder = App.ActiveExplorer.CurrentFolder    '現在のフォルダの取得
    Result = ""
    NewItemFinder NowFolder, Result                     'サブプロシージャの呼び出し
    If Result <> "" Then                                '結果の表示
        MsgBox "件数" & vbTab & "フォルダ" & vbCr & Result
    Else
        MsgBox "新着アイテムはありません。"
    End If
End Sub

Private Sub NewItemFinder(ParentFolder As Outlook.MAPIFolder, Result As String)
    Dim ChildFolder As Outlook.MAPIFolder
    If ParentFolder.UnReadItemCount > 0 Then            '未読アイテム数の確認
        Result = Result & vbCr & ParentFolder.UnReadItemCount _
                & vbTab & ParentFolder.Name
    End If
    If ParentFolder.Folders.Count > 0 Then              '子フォルダの確認
        For Each ChildFolder In ParentFolder.Folders
            NewItemFinder ChildFolder, Result           '子フォルダに対してサブプロシージャ
        Next ChildFolder                                '(このプロシージャ自身)の呼び出し
    End If
End Sub