『Excel VBAスタンダード VBAエキスパート』

田中亨(著)：『Excel VBAスタンダード　VBAエキスパート』, オデッセイ，2009 スタンダードは、 Excel VBAの基礎からの始まって幅広く終わる。 基本というのは、 変数/配列、プロシージャ、ステートメント等についてです。 イベントとUserFormの使い方が詳しいです。 その他、IEやレジストリの操作など薄く広く、 VBAエキスパートの試験範囲をカバーしていると思われます。 VBAエキスパートのテキストですので、 内容は薄いですが、ゆったりとしていて説明は丁寧です。 試験を受ける人は試験範囲を確認するため ベーシックはなくても スタンダードくらい見ておいた方がいいかと思います。

VBEの使い方が詳しく説明されているので、
コードを入力するだけだった人も
もっとVBEを便利に使えると思います。

VBEを知ると、
きっとExcelって面白いソフトだと思うようになると思います。

いままでイベントを使ったことがない人には、
イベントの使用方法が実例が多くわかりやすいです。

最初の一回でも
イベントを使ったマクロが実行できるまでは、
本当にイベントとは何かということがわからないので、
とりあえず一回、成功できるように
画像付きで手順を教えてくれるのは、
イベントが分からない人にはいいと思います。

エラーについても対処の仕方の考え方が示されています。
こういったところは
あまり他のVBA本では見られないと思います。

UserFormの作り方と
そのコントロールのマクロの書き方など
順次写真付きで視覚的にもわかりやすいです。

メニューの操作
OLEオートメーション/IEの操作
レジストリの操作
ファイルの操作

などなど幅広いが浅い範囲をカバーしています。
これは他のVBAの和本でも同様ですけど…

しかしVBAエキスパートの試験に必要なのでしょう。

ベーシック同様に
インターネット上で模擬問題などが提供されています。
これには本に書いてあるID、パスワードが必要です。

『Excel VBAベーシック VBAエキスパート』

田中亨(著)：『Excel VBAベーシック　VBAエキスパート』, オデッセイ，2009 Excel VBAの基礎中の基礎からの始まって基礎中の基礎で終わる。 ベーシックなだけある。 漠然と使っていた用語が何を意味しているか 丁寧に説明されているで、 「そういうことっだったのか」と思うことも多いです。 そういう理由でVBAが初めての人よりは ある程度つかえている人が読むと有益だと思います。

Excel VBAとは何か？VBEとは…、マクロとは… など初歩から説明されています。

順番に読んで行けば
VBEを開いて簡単なマクロが作れるようになると思います。

VBAエキスパート用のテキストということもあり、
実用的ではないです。
マクロの実例も少なく、ページの余白も多く
いかにもテキストといった作りです。

本のタイトルにベーシックとありますが、
具体例が少ないので、VBAの初心者には向かないと思います。
ある程度VBAを触っている人の方が試験用に
この本を読むことは有益だと思います。

試験対策用とあって、
この本のよいところは用語が何を意味するかが
しっかりわかることです。
初心者に用語の説明をしてもよく理解できませんが、
ある程度経験している人は
実体験と用語を結び付けられますので
理解が深まると思います。

インターネット上でVBAエキスパート模擬問題などが提供されています。
これには本に書いてあるID、パスワードが必要です。

Shapeを指定して削除する

すべてのShapeを選択する方法を
以前の記事『 ActiveSheetのすべてのShapeを選択 』で書きましたが、
この方法を使わなくても
すべてのShapeを選択できることがわかりました。

SelectAllメソッドという
すべてのShapeを選択できるメソッドがありました。

これを使ってすべてのShapeを削除します。
コードはこちら

Sub macro110821a()
'Shapeにアクセスする
'すべてのシェイプを選択して削除
   
    ActiveSheet.Shapes.SelectAll
    Selection.Delete
   
End Sub

次にShapeの種類ごとにアクセスしていく方法です。

ActiveSheet.Shapes.TextBox.SelectAll

などとしてもできません。

これにはTypeプロパティを使います。

流れは、
すべてのシェイプをFor Eachステートメントで回していき、
ひとつひとつ目的のTypeであるかをIf文で判定して
操作したいシェイプにアクセスします。
ここで大きさ、色、位置などのプロパティを変更します。
下のコードではTextBox削除していきます。

Typeの種類についてはこちらのWebページを参照してください。
参: MsoShapeType 列挙型

コードはこちら

Sub macro110821b()
'Shapeにアクセスする
'Type別
   
    Dim s As Shape
    For Each s In ActiveSheet.Shapes
        If s.Type = msoTextBox Then
            '操作
            s.Delete
        End If
    Next s
   
End Sub

既にシートにあるShapeのTypeが何かわからないときは、
そのShapeを選択して、次のコードを実行します。

Sub kara()
    Stop
End Sub

このコードは何もしません。
中断したところで、
「Selection.ShapeRange.Type」と
ウォッチウィンドウに入力するとTypeがわかります。

イミディエイトに
次のように入力してENTERを押してもわかります。

?Selection.ShapeRange.Type

この場合は数字でしかわかりません。

Shapeについての基本は、
VBAヘルプの「図形で作業する (描画オブジェクト)」に
詳しく書いてあります。

時差の計算

時差の計算なんて言うと
中学の地理の授業を思い出します。

調べてみると日本では、
以前はGMT(グリニッジ標準時刻)が採用されていて、
現在はUTC(協定世界時)が採用されているということです。

GMTではイギリスのグリニッジが標準になっていましたが、
UTCでもイギリスが時差0であるようです。
両者の違いの詳細はわかりませんが、
GMTが100年に何秒かズレる誤差があり
UTCはそれを調整したもの。

そんな認識でいいのでしょうか？

いずれにせよ日常生活では意識もできない誤差だと思います。
Excelでは100分の1秒までしか表示できないので
まあGMTとUTCの違いを意識する必要はなさそうです。
間違っていたらすいません。

かつての日本標準時JSTはGMT+9、
つまりイギリスのグリニッジから9時間足した時間でした。
今はUTC+9、
協定世界時から9時間足した時間です。
時間を足すということは
イギリスよりも9時間進んでいるということです。

地球の自転を思い浮かべると感覚的にわかります。
日本は中国から見て「日のいづる国」ですよ、
別にナショナリズムはありません。
ただそう覚えているだけです。
日本が16時のとき、イギリスは16-9=7時です。

Web上のデータを使うときはその国の標準時間が
UTCからどれだけズレているかわかれば、
日本標準時に直せます。

こちらのWikipediaにUTCについての世界地図があります。
『 UTC+0 』

Web上のデータに「UTC+1」や「GMT+1」などと
明記されていれば日本標準時に直すのは8時間足せばいいだけです。
「UTC+11」であれば3時間引きます。
「GMT-7」であれば(9+7)時間足します。

これについては「世界時計」で検索すれば、
たくさんのサイトがありますので一目瞭然です。

標準時が明記されていない時は調べるしかないです。

世界の時刻を計算するのに
TimeValue関数を使います。

コードはこちら

Sub macro110820a()
'世界の時刻の計算

    Cells(1, 1) = "場所"
    Cells(1, 2) = "時刻"
    Columns(2).NumberFormatLocal = "yyyy/mm/dd hh:mm:ss(aaa)"
   
    'イギリス
        Cells(2, 1) = "ロンドン(イギリス)GMT+1"
        Cells(2, 2) = Now - TimeValue("08:00:00")
    'ニューデリー(インド)
        Cells(3, 1) = "ニューデリー(インド)GMT+5:30"
        Cells(3, 2) = Now - TimeValue("03:30:00")
    '上海(中国)
        Cells(4, 1) = "上海(中国)GMT+8"
        Cells(4, 2) = Now - TimeValue("01:00:00")
    '日本
        Cells(5, 1) = "日本"
        Cells(5, 2) = Now
    'シドニー
        Cells(6, 1) = "シドニー(オーストラリア)"
        Cells(6, 2) = Now + TimeValue("01:00:00")
    'サンフランシスコ
        Cells(7, 1) = "サンフランシスコ(アメリカ)GMT-7"
        Cells(7, 2) = Now - TimeValue("16:00:00")
    'ニューヨーク
        Cells(8, 1) = "ニューヨーク(アメリカ)GMT-4"
        Cells(8, 2) = Now - TimeValue("13:00:00")
    'サンパウロ(ブラジル)
        Cells(9, 1) = "サンパウロ(ブラジル)GMT-3"
        Cells(9, 2) = Now - TimeValue("12:00:00")
       
End Sub

実行前のシート：

足し算と引き算です。

Range オブジェクトのOffset プロパティの使い方

個人的に、
以前の記事『 虫食い 』で初めて使ったプロパティです。

存在は知っていたものの使いどころがわからず
使っていませんでした。

使い方は、例えば次のようになります。
左辺がOffsetの使い方、右辺がそれが返す範囲です。

Range("D5").Offset(1, 0) = Range("D6")
Range("D5").Offset(0, 1) = Range("E5")
Range("D5").Offset(1, 1) = Range("E6")
Range("D5").Offset(-1, 0) = Range("D4")
Range("D5").Offset(0, -1) = Range("C5")
Range("D5").Offset(-1, -1) = Range("C4")

相対的すぎて使う気がしないですが、
1行から順番に処理していくようなプロシージャで
For文の代用にできます。

For文だと最終行を取得しないといけませんが、
Offsetプロパティならそれは必要ないです。

流れは、 Range型の変数r を作ります。
rの行を指定する変数i も作成します。

最初のセルをrに入れるために

i = 1
set r = Cells(i, 1)

このようにします。

まず1行目の処理を変数r を使ってします。
同じ行の違う列のセルを指定するのにもOffsetを使います。

r.Offset(0, 1) = rと同じ行のB列
r.Offset(0, 2) = rと同じ行のC列

のように指定します。
1行目の処理が終わったら

i = i + 1

として次の行に進みます。
進んだセルが空白なら（途中に空白がないという前提）
終了します。

そうでないなら、
GoToステートメントで処理をするコードの前に戻ります。
これの繰り返しです。

コードはこちら

Sub macro110814a()
'Offsetを使用する

    Dim i As Integer
    Dim r As Range
    i = 1
    Set r = Cells(i, 1)
   
Step100:
    '処理
    r.Offset(0, 1) = r + 1
    r.Offset(0, 2) = r + 2
   
    '次 の行へ
    Set r = r.Offset(1, 0)
   
    '終了か繰り返しかの判定
    If r <> "" Then
        '繰り返し
        GoTo Step100
    End If
   
End Sub

実行前のシート：

このシートに上のコードを実行すると
次のようになります。

最後のIf文の条件を r < 11 に変更すれば、
処理を10行目までするようになります。
このように繰り返すかの条件を変更することで
応用できます。

値によってセルを色分けする

数字を可視化する方法として
セルに値の大小によって異なった色を付けて
グラフ化したいと思います。

まず、色分けするシートを作成します。
下のコードは以前の記事『 虫食い 』で使ったものを
一部変更したものです。

一回通る度にセルの値に1を足していきます。
たくさん通ればほど値は大きくなります。

コードはこちら

Sub macro110813a()
'虫食い2

    Application.ScreenUpdating = False
    Sheets.Add
   
    Dim i As Long
    Dim MyRange As Range
    Dim r As Range
    Set r = Selection
   
    'セルの大きさ変更
    Cells.RowHeight = 3
    Cells.ColumnWidth = 2 * (6.88 / 45)
   
    For i = 0 To 200000
'        r.Select
        Select Case Int(Rnd * 4)
            Case 0
                If r.Row + 1 < Rows.Count Then
                    r.Offset(1, 0).Interior.ColorIndex = 56
                    r.Offset(1, 0) = r.Offset(1, 0) + 1
                    Set r = r.Offset(1, 0)
                End If
            Case 1
                If r.Column + 1 < Columns.Count Then
                    r.Offset(0, 1).Interior.ColorIndex = 56
                    r.Offset(0, 1) = r.Offset(0, 1) + 1
                    Set r = r.Offset(0, 1)
                End If
            Case 2
                If r.Row - 1 > 0 Then
                    r.Offset(-1, 0).Interior.ColorIndex = 56
                    r.Offset(-1, 0) = r.Offset(-1, 0) + 1
                    Set r = r.Offset(-1, 0)
                End If
            Case 3
                If r.Column - 1 > 0 Then
                    r.Offset(0, - 1).Interior.ColorIndex = 56
                    r.Offset(0, -1) = r.Offset(0, -1) + 1
                    Set r = r.Offset(0, -1)
                End If
        End Select
    Next i
   
    Application.ScreenUpdating = True
   
End Sub

上のプロシージャでできた1色のシートを使用して
セルの色づけをしていきます。

まずはカラーパレットを
赤単色のグラデーションにします。
このカラーパレットを変更するコードは
以前の記事で使ったものを少し変更したものです。

以前の記事では
手動で色を付けることを前提にしていたので、
見た目のカラーパレットの順序でグラデーションにしていましたが
今回はColorIndexの順番でグラデーションにしています。
そのために変数MyIndexを変更しています。

その他にも今回はカラーパレット56個すべて使うので
For文のところ最大値が55にそれに伴って
RGBを設定するところも変更してあります。

カラーパレットを確認するプロシージャも
以前の記事にありますので、
これらは上のGoogleで検索してみてください。

色を付ける段階についてですが、
Selectステートメントを使って
値によって分岐してセルに塗りつぶしの設定をしていきます。

コードはこちら

Sub macro110813b()
'値によって色分け
   
    'カラーパレットの変更
    '任意 の色0から任意の色1のグラデーション
   
        '任意の色の設定
        Dim r0, g0, b0, r1, g1, b1 As Integer
        r0 = 255: g0 = 255: b0 = 255
        r1 = 255: g1 = 0: b1 = 0
       
        Dim i, r, g, b As Integer
        Dim MyIndex As Variant
        MyIndex = Array(1, 2, 3, 4, 5, 6, 7, 8, _
            9, 10, 11, 12, 13, 14, 15, 16, _
            17, 18, 19, 20, 21, 22, 23, 24, _
            25, 26, 27, 28, 29, 30, 31, 32, _
            33, 34, 35, 36, 37, 38, 39, 40, _
            41, 42, 43, 44, 45, 46, 47, 48, _
            49, 50, 51, 52, 53, 54, 55, 56)
   
        For i = 0 To 55
            r = r0 + Int((r1 - r0) * i / 55)
            g = g0 + Int((g1 - g0) * i / 55)
            b = b0 + Int((b1 - b0) * i / 55)
            ActiveWorkbook.Colors(MyIndex(i)) = RGB(r, g, b)
'            Debug.Print r & "," & g & "," & b
        Next i

    '色 分け
    Dim rng As Range
    Dim MyRange As Range
    Set MyRange = ActiveSheet.UsedRange '全セル
   
    For Each rng In MyRange
        rng.Select
        Select Case rng.Value
            Case Is > 30
                rng.Interior.ColorIndex = 55
            Case 20 To 29
                rng.Interior.ColorIndex = 45
            Case 10 To 19
                rng.Interior.ColorIndex = 35
            Case 7 To 9
                rng.Interior.ColorIndex = 25
            Case 4 To 6
                rng.Interior.ColorIndex = 15
            Case 1 To 3
                rng.Interior.ColorIndex = 5
            Case Else
                rng.Interior.ColorIndex = 1
        End Select
    Next rng
   
End Sub

実行後のシートはこのようになります。

これで虫さんがどこをたくさん通ったか
バッチリわかります。

エラーを無視してエラーを使う

エラーに対処する方法として
エラートラップを作るという方法があります。
これは、

On Error GoTo ErrHandle

などとしてエラーが起きたときに、
「ErrHandle」行ラベルに行き
エラーに対処するという方法です。

今回は
これを使わないでエラーに対処する方法です。

まず、エラーが起きてもそのまま次を実行する状態に
次のコードでします。

On Error Resume Next

このコードでエラーが起きても無視して次へ進みます。
「無視」するとデバッグモードになりません。

エラーが発生していない時はErr = 0 です。
逆にエラーが発生しているときはErr <> 0 になります。
これを利用してエラーが発生しているかいないかを判定します。

コードはこちら

Sub macro110807a()
'エラーを無視してエラーを使う
   
    On Error Resume Next
        'エラーを起こす
        Err.Raise 1004
       
        'エラーが起きているかいないかを判定
        If Err <> 0 Then
            Debug.Print "エラー発生"
        End If
    On Error GoTo 0
   
End Sub

この構造を使って、
以前の記事『 2度目のエラーは捕えない 』 の中の
macro110806bと同じことをしてみます。

次のプロシージャは、
エラーを故意に引き起こしも問題なく
1から100までの数字を順番にA列に入れます。

コードはこちら

Sub macro110807b()
'エラーを無視してエラーを使う
   
    Dim i As Integer
   
Step100:
    If Cells(1, 1) = 100 Then
        Exit Sub
    Else
        i = Cells(1, 1) + 1
    End If
   
    On Error Resume Next
        For i = i To 100
            Rows(101).Delete
            Rows(1).Insert
            Cells(1, 1) = i
            If Rnd < 0.2 Then
                'エラーをある率で起こす
                Err.Raise 1004
            End If
            
            'エラーが起きているかいないかを判定
            If Err <> 0 Then
                Cells(1, 2) = "エラー発生"
                GoTo Step100
            End If
        Next i
    On Error GoTo 0
   
End Sub

「On Error GoTo ○○」を使って1回エラーになり
○○に飛んでから
GoToステートメントで戻ると、
2回目のエラーではデバッグモードになってしまいます。

一方、上のコードの中のIf文、

If Err <> 0 Then
    …
End If

この中でGoToステートメントを使って前に戻っても
デバッグモードにはなりません。
その代わりに永遠に続くことになりますので、
ある程度でストップする別の対処が必要です。

2度目のエラーは捕えない

エラートラップを作っても
エラーを捕えられない。
このようなことを経験したことはありませんか？

もっと詳細に言うと、
1回目のエラーは捕えることができて
再度、実行しているプロシージャに戻ってから起こる
2回目のエラーはエラートラップに行かずに
デバッグモードになってしまう。

下のコードの次の箇所

Err.Raise 1004

で疑似エラーを起こしています。
1回目の疑似エラーで「ErrHandle」行ラベルのところへ飛びます。
そこでエラーをクリアして
エラーのカウントを1増やしてから

GoTo Retry

で「Retry」行ラベルのところへ飛び、
最初から実行します。

それで2回目の疑似エラーで
「ErrHandle」行ラベルのところへ飛ばず、
次のようなダイアログが表示されます。

コードはこちら

Sub macro110806a()
'2度目のエラーは捕えない

    Dim ErrCount As Integer
    ErrCount = 0 'エラーのカウント
   
Retry:
    On Error GoTo ErrHandle
        Err.Raise 1004
    On Error GoTo 0
   
Exit Sub
ErrHandle:
    Err = 0 'エラーをクリア
    ErrCount = ErrCount + 1
    On Error GoTo 0
    GoTo Retry
   
End Sub

エラーは故意に引き起こしていますから
エラーになるのは当然ですが
なぜエラートラップが反応しないのでしょうか？

おそらく「そういうもの」なのだと
捉えています。

ではどうすればそのプロシージャを実行し続けられるか？
繰り返し持続して実行したいプロシージャがあるとして、
１つの例を示します。

まずは、エラーを捕まえたら
そこでそのプロシージャを一旦終了することです。

上のプロシージャのように
GoToステートメントで戻ると2回目のエラーが起きたときに
デバッグモードになってしまい中断されてしまうからです。

しかし、このままではプロシージャが終了してしまいます。
そこでOnTimeメソッドで再度同じプロシージャを実行します。
そうすればエラーが起きても、
それは1回目のエラーですから
エラーは捕えられます。

このような仕組みにしておくと
エラーが起きた時点で処理が途中になってしまいます。

そこでエラーを捕えらときに、
途中の処理をすべて消すなどして
最初からプロシージャを実行しても問題がないような状態にします。

簡単な例を示します。
次のコードは、
1から100までの数字を
A列に入れていくことを目的としています。
ただし途中でエラーがある確率で起こるようにしてあります。

エラーが起きた時は
「ErrHandle」行ラベルに行き終了しますが、
OnTimeメソッドで3秒後に再び実行されます。

途中で終了してから
再度最初から実行しても
冒頭のIf文でiの値を調整しますので
iは順番通りの数になります。

コードはこちら

Sub macro110806b()
'2度目のエラーに中断されない

    Dim i As Integer
   
    If Cells(1, 1) = 100 Then
        Exit Sub
    Else
        i = Cells(1, 1) + 1
    End If
   
    On Error GoTo ErrHandle
        For i = i To 100
            Rows(101).Delete
            Rows(1).Insert
            Cells(1, 1) = i
            If Rnd < 0.2 Then
                'エラーをある率で起こす
                Err.Raise 1004
            End If
        Next i
    On Error GoTo 0
   
Exit Sub
ErrHandle:
    Cells(1, 2) = "エラー発生"
    Application.OnTime Now() + TimeValue("00:00:03"), _
        "macro110806b"
       
End Sub

実行後のシートの一部：

基本はこのような仕組みにすれば
持続的に実行し続けられると思います。