久しぶりにVBAを使ってみたけど、思い出すのに手間がかかりました。
ブログにまとめて、今後はこのブログを見ればちょちょいのちょい！、とコーディングできるように
まとめてみました。
尚、
VBAにイロハを学びたい時は、次のURLを参考にするといいと思います。
URL：
http://www.tipsfound.com/VBA/Default.vbhtml
http://excel-ubara.com/EXCEL.html
----------------------------------------------------------------------------------
まずコーディング規則についてまとめてみました。
●VBAコーディング規約
参考URL：
http://home.att.ne.jp/zeta/gen/excel/c04p15.htm
(1)目的
開発プロジェクトにおける作業、成果物の標準化によって、修正作業及び、保守の容易化
・ コードの可読性と保守性の向上
・ クラスやメソッド、属性等に対して一貫性を持った識別子を持たせ、クラスライブラリの構造やコードを理解し易くし、開発効率の向上
・ 適切なコメントにより、リファレンス等の作成簡易化、クラスライブラリの再利用率向上
(2)変数やオブジェクトの命名法
変数、関数、クラスや構造体などの名前を識別子と呼び、最低限のルールと開発効率を向上させる適切なコーディング規則を適用することで、理解しやすく、かつ他言語の規則に矛盾しない、正確で読みやすいソースコードを作成します。
識別子の命名こそが、自分にも他人にもわかりやすく、正しくて保守性の良い、美しいプログラムを作成するカギを握っています。
Excel VBAでは、識別子は255バイトまで指定でき、英数字および日本語と一部の記号が使用できます。ただし、先頭には記号と数字は使えません。
・先頭は文字（英字または日本語）でなければなりません
・スペースや"."
"!"などの記号、型宣言文字は使用できません
・変数や定数、プロシージャの名前は255バイト以内である必要があります
・コントロール、モジュールの名前は 40バイト以内である必要があります
・予約語（ステートメント、関数、プロパティ、メソッド）と同じ名前は使用できません
VBAでは、同じスコープ内で大文字小文字による識別子の区別を行いません。例えばmystrという文字列型変数とMYSTRという文字列型変数を、同一のスコープ内で異なる変数として定義することはできません。
【識別子の大文字・小文字のスタイル】
・Pascal 形式
識別子の最初の文字と、後に続いて連結されている各単語の最初の文字を大文字にします。
・Camel 形式
識別子の最初の文字は小文字にし、後に続いて連結されている各単語の最初の文字を大文字にします。
・ハンガリアン記法（ハンガリー記法）
変数名にプリフィックスを組み入れた命名法で最近はあまり推奨されなくなりました。ハンガリー記法はMicrosoftのハンガリー出身のプログラマCharles
Simonyiにちなんだものです。
【
一般的な名前付け規則】
VBA、VB6.0にはネームスペースという概念がありません。
クラスに関してはクラス名が、ある意味ネームスペースの代わりを果たしますが、標準モジュールではPrivate指定をしない限りグローバルスコープになります。
ただし、別々の標準モジュールに記述された同一名称のPublicプロシージャの場合、モジュール名で修飾することによって、名前の衝突を回避できます。
例：
Public Sub main()
Call
Module1.test
Call
Module2.test
End Sub
' Module1に記述されたtestプロシージャ
Public Sub test()
Debug.Print
"Module1.test"
End Sub
' Module2に記述されたtestプロシージャ
Public Sub test()
Debug.Print
"Module2.test"
End Sub
testプロシージャが重複しない場合、下記はどちらも同一プロジェクト内のModule1から、testプロシージャをCallしています。（重複している場合、上のステートメントは実行されますが、下のステートメントはコンパイルエラーになります）
例：
Call VBAProject.Module1.test
Call test
同様にグローバル変数が重複している場合も、変数をモジュール名で修飾することで名前の衝突を回避できます。
例：
Module1.MyStr = Module2.MyStr & MyStr
上記のコードをModule3から実行した場合、Module2に定義されたパブリック変数MyStrに、Module3で定義されたパブリック変数MyStrの値を連結し、Module1に定義されたMyStrに代入します。
（注）Module3内にパブリックまたはモジュールレベル、またはプロシージャレベルでMyStrが定義されていない場合、コンパイルエラーになります。（名前の衝突が発生します）
特殊なケースを除いて重複する、。識別子は"できる限り"重複しないことが基本です。
【プリフィックス（接頭語）とサフィックス（接尾語）について】
ブール型(bln)、整数型(int)、長整数型(lng)、単精度浮動小数点数型(sng)、倍精度浮動小数点数型(dbl)、文字列型 (str)、エラー型(err)、オブジェクト型 (obj)、テキストボックス(txt)、ラベル (lbl)、リストボックス(lst)、コンボボックス (cbo)、チェックボックス(chk)、コマンドボタン (cmd)、コモンダイアログ(dlg)、フレーム(fra)、グローバルスコープ (g)、モジュールスコープ(m)、イメージ(img)
(3) レイアウト規則
・インデント(タブ文字を使い、文字数は統一)を挿入
・長いステートメントは行継続文字" _"（スペースとアンダースコア）を使用して、複数行に分割
（継続行には、可読性を上げるためインデントを挿入してください）
(4)Option Explicit
変数を宣言しないと使えませんという命令を定義する
※VBEには「自動的にOption Explicitが記述される」オプションがあります。VBEの[ツール]-[オプション]で開く[オプション]ダイアログボックスの[編集]タブに[変数の宣言を強制する]というチェックボックスがあります。これをオンにしておくと、それ以降に挿入されたモジュールでは、先頭にOption Explicitが自動入力されます。残念なことに、このオプションは、標準ではオフになっています。
----------------------------------------------------------------------------------
● エラー処理
On Error Resume Next
エラーが発生した行を無視（スキップ）して、次行からコードの実行を再開します。
このエラー処理を使用するときは通常、実行してもしなくてもプログラムのメイン機能に影響しない処理（ウィンドウサイズの変更や効果音の再生など）に使用します。
On Error Goto 行ラベル
エラーが発生した場合、行ラベルで指定した行にジャンプします。
Excel VBAではTry～Catch構文が使用できないため、エラー時の処理はプロシージャ内にエラー処理用のサブルーチンを記述しなければなりません。
サブルーチンからメインルーチンに戻るには、Resume 行ラベル、Resume Nextステートメントを使用します。
Resume 行ラベルの場合、指定した行ラベルから処理を再開します。
Resume Nextの場合、エラーが発生した行の次の行から処理を再開します。
ただのResumeの場合、エラーが発生した行から処理を再開します。
例：
Sub test()
Dim MyLng As Long
On Error GoTo ErrHandler
MyLng = 2 / 0
MyLng = 2 / 1
Retry:
MyLng = 2 / 2
Exit Sub
ErrHandler:
Resume Retry
End Sub
この例では、MyLng = 2 / 0 で0除算エラーが発生した後、ErrHandler: 行にジャンプします。
Resume Retry でRetry: 行から処理を再開するよう指定されているので、MyLng = 2 / 2 より処理を再開します。
Resume Retry がResume Next の場合、MyLng = 2 / 1 より処理を再開します。
Resume Retry がResume だけの場合、MyLng = 2 / 0 より処理を再開するので無限ループになります。
Errオブジェクト
エラーが発生したときに、自動的にエラー情報を取得してくれるオブジェクトです。
・Err.Number
エラー番号
・Err.Source エラー発生元のオブジェクト名
・Err.Description エラーに関する説明文
・Err.HelpFile ヘルプファイルへの絶対パス
・Err.HelpContext ヘルプファイルのトピックに対応するコンテキスト番号
・Err.LastDLLError 最後にDLLを呼び出したときのエラーコード
Err.Raise
Errオブジェクトのメソッドで、エラーを意図的に発生させることができます。
エラー処理のテストなどに利用できます。
例：
Sub test()
On Error GoTo ErrHandler
Err.Raise 11
Exit Sub
ErrHandler:
Debug.Print Err.Number & vbCrLf & _
Err.Description & vbCrLf & _
Err.Source
Resume Next
End Sub
Error関数
エラー番号を引数にして、エラーメッセージを返します。エラー処理ルーチン内だけでなく、プロシージャ内のどこにでも記述が可能です。
例：
MsgBox Error(11)
「0で除算しました」のメッセージを表示します。
● 高速化
Application.ScreenUpdating で処理を高速化できます。
VBA の処理を高速化する一番の方法は、描画を停止することです。
Range("A1").Value = "あいう" のようにセルに対して操作すると、そのたびに Excel の画面が更新されます。これに時間がかかるため描画を停止させます。
描画を停止するには Application.ScreenUpdating = False のように指定します。
Public Sub Tips
Application.ScreenUpdating = False
Application.ScreenUpdating = True
End Sub
処理の最後に Application.ScreenUpdating = True にして描画をするのを忘れないようにします。これだけで、十分に高速化を期待できます。
その他に、Variant や Object などあいまいな型で処理をするより、Integer など型を指定した方が高速化します。
●ThisWorkbook関数
VBEの「Microsoft Excel Objects」内の「ThisWorkbook」に記述する関数
(1) Workbook_Open
ワークブックを開く時に自動実行させます
(2) Workbook_BeforeClose
ブックを閉じる時に自動実行させます。Cancel = Trueとすると、Closeイベントはキャンセルされます(ブックは閉じられません)
※標準モジュールでは、Auo_Closeを使用します(Workbook_BeforeCloseの後に実行されます）
(3) Workbook_BeforeSave
保存する時に自動実行させます
(4) Workbook_WindowActivate
ウィンドウを切り替える時に自動実行させます
(5)Worksheet_Activate
シートを切り替える時に自動実行させます
(6)Workbook_BeforePrint
印刷する(印刷する前)時に自動実行させます
(7)Worksheet_SelectionChange
シート上の選択セルを移動する時に自動実行させます
(8)Worksheet_Change
セルの内容を変更する時に自動実行させます
参考URL：http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub04_130_04.html
●文法
VBAで、よく使う文法や、ほかの言語と違っている事などをまとめてみました
(1) For文
・For をループ中に抜けたくなったら Exit For を使用
・ループを一回飛ばして次のループへ移る Continue は 存在しないので、GoTo 文を使用
Dim i As Integer
For i = 0 To 5
' i が 3 のときは Continue: 行へ処理を飛ばします。
If i = 3 Then
GoTo Continue
End If
' i が 3 のときはここは通りません。
Debug.Print (i)
Continue:
Next
(2) Cellの値を取得
・ Range の引数にセル名を指定 Range("A1").Value
・Cells の引数にセルの行と列の番号を指定 Cells(1, 1).Value
・セルの範囲「A1」～「B2」の値を取得 Range("A1:B2").Value/Range("A1", "B2").Value
・アクティブなシートのセルの値を取得 ActiveSheet.Range("A1").Value
・Sheet1 のシートを指定 Sheets("Sheet1").Range("A1").Value
Cellsの行番号には、対象となるセルの行番号を、列番号には列番号を指定します。列番号には「"A"」のように、ダブルクォーテーションで囲んで、列名をアルファベットで指定することもできます。
例：Cells(5, 1).Select
Cells(5, "A").Select
・[]を使ってセル範囲を参照する。
例：
[A1].Value = "Sample"
[A1:C5].Value = "Sample"
・Offsetプロパティ ： オブジェクト.Offset(行方向, 列方向)
あるセルから相対的に指定した位置にある、セルの参照を取得できます。
例：
Range("A1").Offset(1, 1).Select
Range.End の引数に方向を指定すると、指定した行の終端のセルを取得できます。
Dim r As Range
Set r = Range("B2").End(xlDown).EntireRow
Set r = ActiveCell.End(xlUp).EntireRow
r.Select
(4) 使用している範囲の最終行を取得
Sheets.UsedRange を指定すると、指定したシートで使用されているセルの最大範囲を取得できます。最大範囲とはシートで使用されている一番上、下、左、右のセルの範囲です。
UsedRange で取得した範囲を「範囲の最終行を取得」の方法を使用して最終行を取得します。
Dim r As Range
Dim last As Range
Set r = ActiveSheet.UsedRange
Set last = Rows(r.Row + r.Rows.Count - 1)
last.Select
●VBEの使い方(デバッグ)
(1) ブレイクポイント
コード ウィンドウでブレイクポイントを設定する
継続するには[F5]や[F8]で実行を継続することができます。
（２）ブレークポイントのところまでワープして、実行
[F5]は中断地点からブレイクポイントまでコードを実行します。
（３）関数の中にステップインしながら実行
[F8]はステップインで1行ごとにコードが実行されます。
(4) 変数の値を確認
・マウスポインタで変数をポイントすると表示されます
・Debug.Printを記述して、イミディエイト ウィンドウに表示して確認できます
・ローカルウィンドウを表示させておくと変数の値と型をまとめて確認できます
----------------------------------------------------------------------------------
【課題】
以下の条件で、EXCELのセルに色付けする処理をマクロで組み込む
【K列に下記条件による警告表示を行いたい】
①当日が開始（予定）日 ※I列表示 以降にも関わらず状態 ※K列表示
が「-（ハイフン）」又は「未着手」となっている場合、該当行のK列を指定色
（赤色等）で表示
②当日が終了（予定）日 ※J列表示 以降にも関わらず状態 ※K列表示
が「-（ハイフン）」、「未着手」又は「作業中」となっている場合、該当行のK列を
指定色で（黄色等）で表示
----------------------------------------------------------------------------------
●EXCEL起動時に処理を実行する
ThisWorkBookにWorkbook_Openを記述して、実行関数を記述する
Private Sub Workbook_Open()
' セルの色付け処理
Call Initialize_Color_Cell
' 1番目のシートを選択する
ThisWorkbook.Sheets(1).Select
End Sub
●標準モジュールとフォームモジュールとにコード記述区分
他の標準モジュールやシートから呼び出したいものは SubやFunctionをPublic で宣言します。
Module1に セルの色付け処理(Initialize_Color_Cell)を記述する
●条件によって、セルに色付けする処理
istatus列セルが"-"か"未着手"の時j列セルを赤色にする
If Cells(i, istatus) = "-" Or Cells(i, istatus) = "未着手" Then
Cells(i, j).Interior.ColorIndex = 255 '赤
End If
●日付の比較
Date関数を使用する
・今日の日付の取得
Dim DateToday As String
DateToday = Date
・セル記載の日時をオブジェクトDateに変更する
Dim s_start As String
s_start = Cells(i, istart_span).Value
Dim DateStart As Date
DateStart = CDate(s_start)
・文字が日付になっているかチェックする
If IsDate(s_start) = False Then
MsgBox "有効な日付ではありません"
End If
・日付の比較
If DateToday < DateStart Then '当日が開始日以降
MsgBox("今日が開始日以前となっています")
End If
【作成したVBAコード】
Public Sub Initialize_Color()
Color_Cell (ActiveSheet.Name)
End Sub
Function Color_Cell(ByVal shName As String)
Dim i As Integer, j As Integer
Dim irow As Integer
Dim s_start As String, s_end As String
Dim sheet As Excel.Worksheet
Dim DateToday As String
Dim DateStart As Date
Dim DateEnd As Date
Dim strKoutei As String
Dim strStart As String
Dim strEnd As String
Dim strStatus As String
irow = 312
Set sheet = Worksheets(shName)
sheet.Activate ' アクティブにする
irow = sheet.UsedRange.Rows.Count 'Excelが認知している最終行を取得する
strKoutei = "E"
strStart = "I"
strEnd = "J"
strStatus = "K"
DateToday = Date
On Error GoTo ErrHandler ' エラー時にErrHandlerに飛ぶようにします
For i = 8 To irow ' 列
' 工程になにもない場合は終了とする
If sheet.Cells(i, strKoutei).Value = "" Then
GoTo Continue
End If
s_start = sheet.Cells(i, strStart).Value
s_end = sheet.Cells(i, strEnd).Value
If IsDate(s_start) = False Then
GoTo Continue
'MsgBox "有効な日付ではありません"
End If
If IsDate(s_end) = False Then
GoTo Continue
'MsgBox "有効な日付ではありません"
End If
DateStart = CDate(s_start)
DateEnd = CDate(s_end)
' 開始日をチェック
'①当日が開始（予定）日 ※I列表示 以降にも関わらず状態 ※K列表示
' が「-（ハイフン）」又は「未着手」となっている場合、該当行のK列を指定色
' （赤色等）で表示
If DateToday < DateStart Then '当日が開始日以降
If Cells(i, strStatus).Value = "-" Or Cells(i, strStatus).Value = "未着手" Then
Cells(i, strStatus).Interior.ColorIndex = 3 '赤
End If
End If
' 終了日をチェック
'②当日が終了（予定）日 ※J列表示 以降にも関わらず状態 ※K列表示
' が「-（ハイフン）」、「未着手」又は「作業中」となっている場合、該当行のK列を
' 指定色で（黄色等）で表示
If DateToday > DateEnd Then '当日が終了日以降
If Cells(i, strStatus).Value = "-" Or Cells(i, strStatus).Value = "未着手" Then
Cells(i, strStatus).Interior.ColorIndex = 6 '黄色
End If
End If
Continue:
Next i
ErrHandler:
Debug.Print Err.Number & vbCrLf & _
Err.Description & vbCrLf & _
Err.Source
Resume Next
End Function
※ボタンクリックでマクロを実行するようにしたところ、
マクロ 'マクロ名'
を実行できません。このブックでマクロが使用できないか、またはすべてのマクロが無効になっている可能性があります。
といったエラーがでいました。
この場合は、ボタンClick時のマクロ登録をしなおすとうまくいきました。
