Go to list of users who liked
Share on X(Twitter)
Share on Facebook
More than 5 years have passed since last update.
昨日までJavaJavaしてた人がいきなりExcelのVBAを実装する羽目になったときのためのメモ
Excel は滅びぬ！ Excel の力こそ日本企業の夢だからだ！
VBA 実装してて学んだこととかのメモ。
JavaJava してたかはあまり関係ないかも。
エディタの使い方
エディタを表示する
Alt + F11 で VB エディタを表示できる。
環境設定
背景色・フォントを調整する
デフォルトの白背景とか気が狂うので、暗い色にする。
「ツール」→「オプション」を選択し、「エディターの設定」タブを開く。
「コードの表示色」を選択して、「背景」の色を選択する。
ついでにフォントも見やすいやつに変更する。
これだけで開発効率が５割増しになる。
イミディエイトウィンドウ
イミディエイトウィンドウを表示する
いわゆるコンソールに当たるのが、__イミディエイトウィンドウ__と呼ばれるウィンドウ。
Ctrl + G で表示される。
イミディエイトウィンドウに出力する
↓イミディエイトウィンドウに実行するプロシージャを入力して、 Enter を入力する。
プロシージャが実行されて、Debug.Print で指定した文字列がイミディエイトウィンドウに出力される。
このように、イミディエイトウィンドウではプログラムの実行とコンソール出力の確認ができる。
デバッグの時は、デバッガで処理を止めながらイミディエイトウィンドウで変数の値やプロシージャの処理結果などをチェックしたりするので重宝する。
コンパイルエラー時にポップアップが出ないようにする
標準だと、ちょっとでもコンパイルエラーがあれば即座にポップアップが表示されて非常に鬱陶しい。
「ツール」→「オプション」を選択し、「編集」タブの「コードの設定」にある「自動構文チェック」のチェックを外す。
これで、ポップアップは表示されなくなる。
変数の宣言を強制する
VBA はデフォルトでは変数を宣言していなくてもいきなり使用することができる。
つまり、そのままだと変数名をタイポしてもコンパイルは通ってしまい、バグの温床になる。
これは非常に危険なので、変数を宣言せずに使用するとコンパイルエラーが発生するように設定する。
「ツール」→「オプション」を選択し、「編集」タブの「コードの設定」にある「変数の宣言を強制する」にチェックを入れる。
これで、新規にモジュールなどを追加すると、先頭に自動的にOption Explicit が挿入されるようになり、変数の宣言を強制させることができる。
デバッガ
ブレークポイントを設定する
エディターの左端をクリックすると、ブレークポイントを設定できる。
デバッグ中の操作
| キー | 操作 |
|---|---|
| F8 | ステップイン |
| Shift + F8 | ステップオーバー |
| F5 | 再開 |
変数に代入されている値を確認する
マウスカーソルをホバーさせる
変数の上にマウスカーソルを乗せると、変数に代入されている値が表示される。
ウォッチウィンドウを使う
「表示」→「ウォッチウィンドウ」でウォッチウィンドウを表示できる。
「ウォッチウィンドウ」上で右クリックして、「ウォッチ式の追加」を選択すると、ダイアログが表示されるのでウォッチしたい変数の名前を入力して「OK」をクリックする。
ウォッチウィンドウで変数の中身を見れるようになる。
イミディエイトウィンドウを使う
イミディエイトウィンドウで変数の値を出力させることもできる。
? で出力する
? の後ろに確認したい変数名を記述してEnter で、内容を確認できる。
コメントで教えていただきました。ありがとうございます。
Ctrl + Z, Ctrl + Y の罠
一般的に、Ctrl + Z は「元に戻す」、Ctrl + Y は「やり直し」のショートカットとして使用されていることが多い。
Excel の VB エディタもCtrl + Z は「元に戻る」のショートカットになっている。
しかし、Ctrl + Y は「やり直し」のショートカットではない。Ctrl + Y は行の削除のショートカットになっているﾌｧｯｸ!!。
Ctrl + Z で編集を戻してて、「あ、戻しすぎた」と思ってあわてていつものノリでCtrl + Y を連打すると、行がどんどん削除されていくという罠。
狂気を感じるショートカット設定です。
じゃあ、「やり直し」はどのショートカットで入力できるのかというと、
ありません。
どうみても悪質な罠です。本当にありがとうございました。
追記
ショートカットキーを設定する方法 を教えていただきました。
ショートカットを設定する
コメントでショートカットを設定する方法を教えていただきました。
- ツールバーを右クリックして、[ユーザー設定]を選択
- 「ユーザー設定」ダイアログが開くので、そのままツールバーのコメントブロックのアイコンをクリックする。
- 「選択したボタンの編集」を選択して、「名前」の末尾に
(&C)と入力する。 - 「イメージとテキストを表示」または「テキストのみ表示」を選択する。
- 「閉じる」ボタンで「ユーザー設定」ダイアログを閉じる。
これで、Alt +C でコメントブロックを実行できるようになる。C 以外のキーに設定したい場合は、(&T) とか(&K) といった感じで設定すればいい。
注意
ツールバーにボタンが表示されていないと、ショートカットが働かない。
もし、設定したのにショートカットがきかない場合は、ツールバーにボタンが表示されているか確認する。
↑の状態だと、コメントブロックは動作するが、非コメントブロックは見えなくなっているのでショートカットが動作しない。
↑のように、ボタンが見えるようにウィンドウサイズやツールバーの位置を調整しないといけない。
参考
文法の基礎
コメント
シングルクォーテーションより後ろはコメント行になる。
' コメント複数行コメント
複数行コメント？ なにそれおいしいの？
代わりに、「表示」→「ツールバー」→「編集」を選択して編集ツールバーを表示させる。
すると、選択している範囲を一気にコメントアウトできる「コメント ブロック」とコメントの解除ができる「非コメント ブロック」が使えるようになる。
ショートカットは割り当てられていないみたいﾌｧｯｸ!!。
追記
ショートカットキーを設定する方法 を教えていただきました。
変数の宣言
Dim a As IntegerDim <変数名> As <型> で変数を宣言できる。
代入
基本
Public Sub procedure() Dim i As Integer i = 10 Debug.Print i Dim s As String s = "string" Debug.Print s Dim b As Boolean b = True Debug.Print b End SubSheet1.procedure 10 stringTrue基本は、<変数名> = <代入する値> で変数に値を代入できる。
オブジェクト型の代入
代入する値が__オブジェクト型__の場合は注意が必要。
Public Sub procedure() Dim c As Collection c = New Collection Debug.Print c.CountEnd Subこれを実行すると、「引数は省略できません」という意味不明なエラーメッセージが表示されるﾌｧｯｸ!!。
正しくは、以下のように変数の前にSet を入れる。
Public Sub procedure() Dim c As Collection Set c = New Collection ' 変数の前に Set を入れる Debug.Print c.CountEnd SubSheet1.procedure 0オブジェクト型というのは、Integer やBoolean、String などの基本的な型以外全てを指す。
これが意外と厄介モノで、つい記述を忘れて実行時にエラーが連発したりする。
Function の戻り値でも必要
Public Sub procedure() Dim c As Collection Set c = GetCollectionEnd SubPublic Function GetCollection() As Collection GetCollection = New CollectionEnd Functionprocedure プロシージャでSet c = GetCollection としているため大丈夫そうに見えるが、これを実行するとエラーが発生する。
正しくは、以下のように実装する。
Public Sub procedure() Dim c As Collection Set c = GetCollectionEnd SubPublic Function GetCollection() As Collection Set GetCollection = New CollectionEnd FunctionFunction の戻り値がオブジェクト型の場合も、Set を付けなければならない。
If 文
If Number = 1 Then ' ... ElseIf Nember = 5 Then ' ... Else ' ... End Ifループ
For 文
Dim i As IntegerFor i = 0 To 5 Debug.Print iNext 0 1 2 3 4 5Do While 文
Dim i As IntegerDo While i < 5 Debug.Print i i = i + 1Loop 0 1 2 3 4プロシージャ
プロシージャの種類
プロシージャには、 Sub と Function の２種類が存在する。
Public Sub SubProcedure() Debug.Print "SubProcedure" Debug.Print FunctionProcedureEnd SubPublic Function FunctionProcedure() As String FunctionProcedure = "FunctionProcedure"End FunctionSheet1.SubProcedureSubProcedureFunctionProcedureSub は戻り値無し、 Function は戻り値有りという違いがある。
Function の戻り値
Function の戻り値は、プロシージャ名と同じ名前の変数？に戻り値を代入することで実現できる。
Public Sub SubProcedure() Debug.Print "SubProcedure" Debug.Print FunctionProcedureEnd SubPublic Function FunctionProcedure() As String FunctionProcedure = "FunctionProcedure" Debug.Print "ここも実行される"End FunctionSheet1.SubProcedureSubProcedureここも実行されるFunctionProcedureJava などのreturn とは異なり、戻り値を設定したあとも処理が続行される。
そのプロシージャの処理を中断させたい場合はExit Function またはExit Sub と記述する。
Public Sub SubProcedure() Debug.Print "SubProcedure" Debug.Print FunctionProcedureEnd SubPublic Function FunctionProcedure() As String FunctionProcedure = "FunctionProcedure" Exit Function Debug.Print "ここは実行されない"End FunctionSheet1.SubProcedureSubProcedureFunctionProcedureプロシージャの呼び出し方
Public Sub OneParameter(a As Integer) Debug.Print aEnd SubPublic Sub TwoParameter(a As Integer, b As Integer) Debug.Print a + bEnd SubSheet1.OneParameter 1 1 Sheet1.TwoParameter 1, 2 3プロシージャを単独で呼び出す場合、丸括弧は不要（付けるとコンパイルエラーになる）。
ただし、下記例のように、あるプロシージャの引数に別のプロシージャの呼び出しを渡す場合などは、丸括弧が必要になる。
Public Sub OneParameter(a As Integer) Debug.Print TwoParameter(a, 10) ' TwoParameter の呼び出しに括弧が必要になるEnd SubPublic Function TwoParameter(a As Integer, b As Integer) As Integer TwoParameter = a + bEnd FunctionSheet1.OneParameter 5 15変数に代入するときに、右辺でプロシージャを呼ぶ場合も必要になる。
Public Sub procedure() Dim s As String s = TypeName("hoge") ' 括弧が必要 Debug.Print sEnd Subプロシージャの処理を途中で中断させる
Public Sub SubProcedure() Debug.Print "[Sub] before" Exit Sub Debug.Print "[Sub] after"End SubPublic Function FunctionProcedure() Debug.Print "[Function] before" Exit Function Debug.Print "[Function] after"End FunctionPublic Property Let Value(Value As Integer) Debug.Print "[Property] before" Exit Property Debug.Print "[Property] after"End PropertyPublic Sub Execute() Dim Instance As New MyClass Instance.FunctionProcedure Instance.SubProcedure Instance.Value = 10 End Sub[Function] before[Sub] before[Property] before- プロシージャの処理を途中で中断させたい場合は、
Exit <プロシージャの種類>を使用する。 - 「プロシージャの種類」には、
Sub,Function,Propertyなどを指定する。
省略可能な引数を定義する
Public Sub Execute() SubProcedure "Explicit Value" SubProcedureEnd SubPublic Sub SubProcedure(Optional Value As String = "Default Value") Debug.Print ValueEnd SubExecute プロシージャを実行する。
Explicit ValueDefault ValueOptional <引数の宣言> = <デフォルト値>とすることで、省略可能な引数を定義できる。
データ型を確認する
Dim a As StringDim b As BooleanDim c As New CollectionDebug.Print "a : " & TypeName(a)Debug.Print "b : " & TypeName(b)Debug.Print "c : " & TypeName(c)a : Stringb : Booleanc : CollectionTypeName()でデータ型を文字列で取得できる。
文字列中に改行コードを入れる
Dim Text As StringText = "aaa" & vbCrLf & "bbb"Debug.Print TextaaabbbvbCrLfという定数が定義されているので、それを利用する。vbCrLfは、キャリッジリターンとラインフィード。
コードの途中で改行を入れる
Dim Text _As StringText = _"Hoge" & _"Fuga"Debug.Print TextHogeFuga_を末尾に記述することで、コードを途中で改行させられる。
コレクション（List）を使用する
VBA には配列もあるが、サイズの調整とかを自分でしないといけないのでなんかアレ。
基本
Dim list As New Collectionlist.Add "hoge"list.Add "fuga"Debug.Print list(1)Debug.Print list.Item(2)hogefugaCollectionというクラスをNewすることで、コレクション（Listみたいなの）を生成できる。Add()メソッドで要素を追加できる。- 丸括弧か
Item()メソッドを使うことで、インデックス指定で要素を取得できる。 - インデックスは、まさかの
1始まり。
ループ
Dim list As New Collectionlist.Add "hoge"list.Add "fuga"list.Add "piyo"Dim i As IntegerFor i = 1 To list.Count Debug.Print (i & " : " & list(i))NextDim e As VariantFor Each e In list Debug.Print eNext1 : hoge2 : fuga3 : piyohogefugapiyoCountで要素数を取得できるので、それでForループを回すことができる。- もしくは、
For Eachでループすることもできる。
要素の削除
Dim list As New Collectionlist.Add "hoge"list.Add "fuga"list.Add "piyo"list.Remove 2Dim i As IntegerFor i = 1 To list.Count Debug.Print (i & " : " & list(i))NextHoge1 : hoge2 : piyoRemove()メソッドを使うと、インデックスを指定して要素を削除できる。
連想配列を使用する
基本
Dim Map As ObjectSet Map = CreateObject("Scripting.Dictionary")Map("hoge") = "Hoge"Debug.Print "hoge: " & Map("hoge")hoge: Hoge- VBA の連想配列は、
Dictionaryを使う。 - 標準だと、この型をそのまま使うことはできず、
CreateObject("Scripting.Dictionary")でインスタンスを生成する。
Dictionary の型を使用できるようにする
- [ツール] → [参照設定] を開く。
Microsoft Scripting Runtimeのチェックを入れる。
こうすると、Dictionary の型が利用できるようになる。
Dim Map As New DictionaryMap("hoge") = "Hoge"Debug.Print "hoge: " & Map("hoge")ループ処理
Dim Map As New DictionaryMap("hoge") = "Hoge"Map.Add "fuga", "Fuga"Dim Key As VariantFor Each Key In Map Debug.Print "Key=" & Key & ", Value=" & Map(Key)NextKey=hoge, Value=HogeKey=fuga, Value=FugaDictionary に定義されているメソッド
| フィールド名 | 引数 | 説明 |
|---|---|---|
Exists | キー | 指定したキーの要素が存在するか確認する。 |
Count | - | 保有している要素の数を取得する。 |
Keys | - | 保有しているキーを配列で取得する。 |
Add | キー, 値 | 要素を追加する。 |
Item | キー | 指定したキーの値を取得する。 |
Items | - | 全ての要素の値を配列で取得する。 |
セルで使用できる関数を VBA から利用する
Dim Summary As IntegerSummary = WorksheetFunction.Sum(Range("A1:C1"))Debug.Print SummaryWorksheetFunctionにメソッドとして関数が定義されている。- 範囲やセルを渡していた部分には、
Rangeを渡すようにする。
フォルダ・ファイル操作
ディレクトリ・ファイルが存在するか確認する
Dim ExistsDirPath As StringDim NotExistsDirPath As StringExistsDirPath = "D:\tmp\excel"NotExistsDirPath = "D:\xxxx"Debug.Print "> " & Dir(ExistsDirPath, vbDirectory)Debug.Print "> " & Dir(NotExistsDirPath, vbDirectory)Debug.Print "> " & (Dir(NotExistsDirPath, vbDirectory) = "")> excel> > TrueDir()関数で存在チェックが行える。- ディレクトリをチェックする場合は、第二引数に
vbDirectoryを渡す。 - 存在する場合は、どのディレクトリの名前が、存在しない場合は空文字が返される。
- ファイルの場合は、
vbDirectory無しでDir()関数を使えばいい。
ディレクトリを作成する
1階層だけ
MkDir "D:\path\to\dir"複数階層をまとめて生成
Private Declare Function SHCreateDirectoryEx Lib "shell32" Alias "SHCreateDirectoryExA" ( _ ByVal hwnd As Long, _ ByVal pszPath As String, _ ByVal psa As Long) As LongPublic Function MkDirs(Path As String) As Long MkDirs = SHCreateDirectoryEx(0&, Path, 0&)End FunctionPublic Sub Test() Debug.Print MkDirs("D:\tmp\hoge\fuga")End Sub- Win API を呼び出しているらしい。
- 作成に成功したら、
0が返される。 - 既にフォルダが存在する場合、自分の環境では
183が返された。
カレントフォルダを取得する
Dim CurrentPath As StringCurrentPath = ActiveWorkbook.PathActiveWorkbook.Pathで現在開いているブックのあるディレクトリのパスを取得できる。
ファイル出力
基本
Open "D:\tmp\test.txt" For Output As #1Print #1, "Hello World!!"Close #1Hello World!!Open <ファイルパス> For <処理> As <ファイルNO>で、ファイルを開く。Print <ファイルNO>, "出力内容"で、ファイルに文字列を書き込む（末尾に改行が自動で付く）。Close <ファイルNO>で、ファイルを閉じる。<処理>には、以下が指定可能。
| 処理種別 | 説明 |
|---|---|
Input | 読み取り |
Output | 書き込み（上書き） |
Append | 書き込み（追記） |
<ファイルNO>は、操作しているファイルを識別するための数値。- 複数のファイルを同時に制御する必要がないなら、
#1とかで OK。 - 複数ファイルを同時に扱う場合は、どの数値を利用しているか管理が大変になるので、その場合は以下のようにする。
Dim FileNo As IntegerFileNo = FreeFile ' ★FreeFile 関数を使うOpen "D:\tmp\test.txt" For Output As FileNoPrint #FileNo, "Hello World!!"Close FileNoFreeFileは、現在利用可能なファイルNO を返す関数。
Print ステートメントで改行コードを付けないようにする
Open "D:\tmp\test.txt" For Output As #1Print #1, "Hello World!!";Close #1Hello World!!Printステートメントの末尾にセミコロン;をつけると、末尾の改行が付かなくなる。
エラー時のクローズは必要か？
Open "D:\tmp\test.txt" For Output As #1Print #1, "Hello World!!"こんな感じで、Close することなくプロシージャが正常すると、ファイルは開きっぱなしになる。
このファイルのロックは、 Excel のブックを閉じるまで解放されない。
Open "D:\tmp\test.txt" For Output As #1Print #1, "Hello World!!"Err.Raise 10000, , "test"一方、上記のようにClose 前にエラーで落ちた場合、エラーダイアログで「終了」を選択すると、ファイルのロックは解放された。
つまり、 Java でいうところの「finally ブロックで必ずリソースが解放されるように実装する」みたいな制御は必要ないのかもしれない。
※あくまで個人的に軽く試した結果なので、落とし穴の可能性もあるため過信しすぎないように。
メッセージボックス
基本
MsgBox "Hello World!!"
MsgBox関数を使用することで、メッセージボックスを表示できる。
表示ボタンの変更
MsgBox "Hello World!!", vbOKCancel| 定数 | ボタン |
|---|---|
vbOkOnly | OK のみ |
vbOKCancel | OK, キャンセル |
vbAbortRetryIgnore | 中止、再試行、無視 |
vbYesNo | はい、いいえ |
vbRetryCancel | 再試行、キャンセル |
vbOKCancel を指定した場合
MsgBoxの第二引数でボタンの表示を切り替えができる。
デフォルトで選択されているボタンを変更する
MsgBox "Hello World!!", vbOKCancel + vbDefaultButton2
- 第二引数に
vbDefaultButtonNを加算することで指定する。 Nのところは 1～4 がある（左から１→２→３→４）。
アイコンを変更する
MsgBox "Hello World!!", vbCritical
| 定数 | アイコン |
|---|---|
vbCritical | 警告アイコンを表示 |
vbQuestion | 問合せアイコンを表示 |
vbExclamation | 注意アイコンを表示 |
vbInformation | 情報アイコンを表示 |
- 第二引数でアイコンを指定できる。
ボタンの変更・アイコンの変更を組み合わせる
MsgBox "Hello World!!", vbOKCancel + vbDefaultButton2 + vbCritical
- 組み合わせたいオプションを
+で加算する。
押下されたボタンの情報を取得する
Dim Clicked As IntegerClicked = MsgBox("Hello World!!", vbOKCancel)Debug.Print Clicked2| 値 | 対応する定数 | 押されたボタン |
|---|---|---|
| 1 | vbOK | OK |
| 2 | vbCancel | キャンセル |
| 3 | vbAbort | 中止 |
| 4 | vbRetry | 再試行 |
| 5 | vbIgnore | 無視 |
| 6 | vbYes | はい |
| 7 | vbNo | いいえ |
クラスを定義する
クラスモジュールを追加する
「プロジェクトウィンドウ」で対象のマクロを右クリックし、「挿入」→「クラスモジュール」を選択する。
クラスはデフォルトではClass1 のような名前になる。
名前を変更する場合は、「プロパティウィンドウ」を表示して、「オブジェクト名」で指定する。
インスタンスを生成する
Public Sub procedure() Dim mine As New MyClass Set mine = New MyClassEnd Subインスタンスを生成する方法は、以下のいずれかがある。
Dimで宣言するときに、As New <クラスモジュール名>とする。- 変数に代入するときに
New <クラスモジュール名>とする。
コンストラクタ
Option ExplicitPrivate Sub Class_Initialize() Debug.Print "initialize"End SubPrivate Sub Class_Terminate() Debug.Print "terminate"End SubPublic Sub procedure() Dim mine As MyClass Set mine = New MyClassEnd SubSheet1.procedureinitializeterminateClass_Initialize、Class_Terminate という名前のプロシージャを宣言することで、インスタンス生成時と削除時に処理を挟むことができる。
引数が宣言できない！
ただし、このコンストラクタには引数を宣言できないﾌｧｯｸ!!。
妥協策としては、
initのようなプロシージャを作り、コーディング規約でinitというプロシージャがあれば必ずインスタンス生成後に実行させること、みたいな縛りを入れる。- 標準モジュールにファクトリ用のプロシージャを定義する。
こんな方法もある。
Dim で宣言するときに New しているだけでは実行されない！
Option ExplicitPrivate Sub Class_Initialize() Debug.Print "initialize"End SubPrivate Sub Class_Terminate() Debug.Print "terminate"End SubPublic Sub Method() Debug.Print "Method"End SubPublic Sub procedure() Dim mine As New MyClass Debug.Print "before call Method" mine.MethodEnd SubSheet1.procedurebefore call MethodinitializeMethodterminateDim の宣言と同時にNew した場合、その時点ではClass_Initialize は呼ばれない。Class_Initialize が実行されるのは、最初にそのインスタンスが使用されるときなので注意。
静的メンバーは定義できない！
VBA のクラスモジュールには、静的な変数やプロシージャを定義できないﾌｧｯｸ!!。
静的なプロシージャなどは、標準モジュールに定義しなければならない。
自分のメンバーにアクセスする
Option ExplicitPublic Name As StringPublic Sub Introduce() Debug.Print "My name is " & Me.Name & "."End SubPublic Sub procedure() Dim mine As New MyClass mine.Name = "Taro" mine.IntroduceEnd SubSheet1.procedureMy name is Taro.Me.<メンバー> で、自分のメンバーにアクセスできる。
ただし、この方法でアクセスできるメンバーは、可視性がPublic で宣言されている必要がある。
Private なメンバーにアクセスする場合は、Me は使えない。
プロパティを定義する
Option ExplicitPrivate My_Age As IntegerPublic Property Let Age(In_Age As Integer) My_Age = In_AgeEnd PropertyPublic Property Get Age() As Integer Age = My_AgeEnd PropertyPublic Sub procedure() Dim mine As New MyClass mine.Age = 17 Debug.Print mine.Age End SubSheet1.procedure 17Public Property Let <プロパティ名>(引数) で書き込み用のプロパティを、Public Property Get <プロパティ名>() As <型> で読み取り用のプロパティを宣言できる。
プロパティは、<変数名>.<プロパティ名> でアクセスできるようになる。
With でメソッドの呼び出しをまとめる
Private MyValue As IntegerPublic Sub SubProcedure(Message As String) Debug.Print "Sub Procedure : " & MessageEnd SubPublic Property Let Value(Value As Integer) MyValue = ValueEnd PropertyPublic Sub Execute() Dim Instance As New MyClass With Instance .SubProcedure "with statement" .Value = 100 End WithEnd SubSub Procedure : with statementWithを使うことで、あるインスタンスのメソッド（プロパティ）呼び出しをまとめることができる。
インターフェースを実装する
Public Sub Method()End SubImplements MyInterfacePublic Sub MyInterface_Method() Debug.Print "Hoge Method"End SubImplements MyInterfacePublic Sub MyInterface_Method() Debug.Print "Fuga Method"End SubPublic Sub Execute() Dim HogeInstance As MyInterface Dim FugaInstance As MyInterface Set HogeInstance = New Hoge Set FugaInstance = New Fuga HogeInstance.Method FugaInstance.MethodEnd SubModule1.Execute プロシージャを実行する。
Hoge MethodFuga Method- VBA では、インターフェースを定義したポリモーフィズムを利用できる。
- インターフェースの定義は、クラスモジュールで行う（
MyInterface）。- インターフェースには、実装が空っぽのプロシージャ定義だけを記載する。
- インターフェースを実装するときは、まずクラスモジュールの先頭で
Implements <実装するインターフェース>と記述する。- 次に、インターフェースで定義されたメソッドの具体的な実装を記述する。
- この時、実装するメソッドの名前にはプレフィックスとして
<インターフェースの名前>_を追加する（MyInterface_Method）。
- 使用できるのはインターフェースなので、 Java でいう抽象クラスみたいなのは作れない
ﾌｧｯｸ!!。
ダウンキャスト
上述のインターフェース実装で作成したHoge とFuga のインスタンスは、MyInterface 型の変数として定義されている。
これをダウンキャストして、Hoge 型の変数に代入するには以下のようにする。
Public Sub Execute() Dim Instance As MyInterface Set Instance = New Hoge Dim HogeInstance As Hoge Set HogeInstance = Instance ' ダウンキャスト HogeInstance.MyInterface_MethodEnd SubHoge Method普通にダウンキャスト後の型の変数に代入すればいい。
例外
例外をスローする
Public Sub procedure() Err.Raise 10000End Sub
Err.Raise で例外をスローできる。
第一引数のエラーコードが必須。第三引数にエラーの説明を載せることができる。
エラーコードは、 0 ～ 512 が予約済みで、ユーザーが定義できるのは 513 ～ 65535。
例外をキャッチする
Public Sub procedure() On Error GoTo EXCEPTION Err.Raise 10000, , "My Error" Exit Sub ' これを忘れないように注意！EXCEPTION: Debug.Print Err.Number & " : " & Err.DescriptionEnd SubSheet1.procedure10000 : My ErrorOn Error GoTo <ラベル名> を先頭に書き、ラベル名で指定したラベルを宣言する。
すると、例外がスローされたときに指定したラベルに処理が移る。
その際、Err オブジェクトにスローされた例外の情報が格納される。
注意しないといけないのは、ラベルの前にプロシージャを終了させるExit Sub またはExit Procedure を入れないといけない、という点。
あくまでラベルなので、これが漏れていると普通に例外処理を書いた領域が実行されてしまうﾌｧｯｸ!!。
例外の情報が・・・消えた・・・？
Public Sub procedure() On Error GoTo EXCEPTION Err.Raise 10000, , "My Error" Exit SubEXCEPTION: hoge Debug.Print Err.Number & " : " & Err.DescriptionEnd SubPublic Sub hoge() On Error Resume Next Debug.Print "hoge"End SubSheet1.procedurehoge0 :例外が発生したあとで別のプロシージャ（hoge）を呼び出し、その後でErr の情報を出力しようとすると、エラーコードと説明が 0 と空になるという現象が発生している。
原因は、hoge プロシージャで使用されているExit Sub ステートメントにある。
Err の仕様として、On Error ステートメントが実行されると、Err に設定されている情報がリセットされるようになっているﾌｧｯｸ!!。
マクロの実行を終了させる
Public Sub procedure() EndEnd SubEnd でマクロの実行を終了させることができる。
他のブックをこっそり開いて操作する
|-main.xlsm`-sub.xlsxsub.xlsx
Public Sub Execute() Dim BookPath As String BookPath = "他のブックへのフルパス" If Dir(BookPath) = "" Then MsgBox "ファイルが見つかりません : " & BookPath Exit Sub End If Dim SubBook As Workbook Application.ScreenUpdating = False Set SubBook = Workbooks.Open(BookPath) ThisWorkbook.Activate Application.ScreenUpdating = True Dim Value As String Value = SubBook.Worksheets("Sheet1").Range("A1").Value Debug.Print ValueEnd SubModule1.Execute プロシージャを実行する。
sub bookmain.xlsmのマクロから、sub.xlsxをこっそり開いてセルの値を参照している。Dir(＜フルパス＞)の戻り値が空文字かどうかでファイルが存在するかどうかをチェックする。Workbooks.Openでブックを開く。Application.ScreenUpdating = FalseとThisWorkbook.Activateでフォーカルが移らないようにしている。
Workbooks.Openの戻り値が、開いたブックを表すWorkbookオブジェクトになっているので、そのメソッドを使ってシートの情報にアクセスできる。
MVC で書く
Excel VBA を書いていると、シートの値の参照やセルの変更などで、シートに関する処理が肥大化しやすい。
その中にドメインロジックが紛れ込むと死にたくなるので、 MVC で書いて View と Model を分離するようにしたい。
以下が処理の流れのイメージと、実装の例。
（個人的に考えた方法なので、一般的な VBA 実装はどうなるのかは不明）
処理の流れのイメージ（シーケンス図）
- ユーザーアクションがあったら、シートや標準モジュールに宣言しているプロシージャを実行するように紐付けておく。
- 標準モジュールで、必要な MVC のインスタンスを生成し、コントローラに処理の開始を指示する。
- コントローラは、ビューから必要な値を取得する。
- コントローラは、ビューから取得した値をモデルに渡す。
- モデルは、受け取った値を元に処理を実行する。
- コントローラは、モデルの処理が完了したら、ビューに結果の表示を指示する。
- ビューは、モデルの値を参照して結果を画面（シート）に反映する。
自分が実装したときは、１つのシートにつき１つのビューを作るようにしていた。
ビューが複雑な場合は、カタマリごとにビュークラスを設けてもいいかもしれない。
とにかく、ビューは他（コントローラやモデル）から隔離し、独立したクラスを定義すべきだと考えている。
ビューを独立したクラスに分ける理由
ビュー操作はノイズになる
ビューの実装は、主に Excel の API を使ったものになる。
Excel の API の使い方などは、モデル（ドメインロジック）にとっては、全く無関係なノイズでしかない。
なので、可読性の面からビューの操作は独立したクラスに隔離すべきだと考える。
ビューの変更に強くする
Excel の見た目（セルの位置等）は頻繁に変更されることが予想される。
ビューの操作をクラスにまとめておけば、ビューの変更に対して変更箇所を簡単に特定できるようになる。
少なくとも１シート１ビューとしておけば、変更されたシートに対応するビュークラスが修正対象であると、容易に判断できる。
また、ビュークラス内の実装では、ある項目へのアクセス（値の設定・取得）を、特定のプロシージャを介してのみ行うようにする。
こうすることで、変更箇所をより局所化できる。
ビュー操作を抽象化でき、可読性・再利用性が向上する
ビューの処理は、「"A1" のセルの値を取得する」のように、非常に抽象度の低い実装になる。
Dim Name As StringName = ActiveSheet.Range("A1").Value実は、シートが何らかの申請書で、 "A1" には申請者の名前が入力されていたとする。
ビュークラスを使って操作をカプセル化すれば、
Dim ApplicationSheet As New ApplicationSheetDim Name As StringName = ApplicationSheet.ApplicantNameというふうに実装の抽象度が上がり、可読性があがる。
また、名前を取得する操作が共通化されるので、他のところでも名前が必要になった場合、簡単に使いまわすことができる。
Excel の API の操作方法を知ることなく欲しい情報にアクセスできるようになるので、実装しやすさも向上する。
MVC の実装例
前置き
あくまで例のための実装なので、「SUM 関数やAVERAGE 関数つかえばマクロ組む必要ねーじゃん」というツッコミはなしで。
画面
A列に点数を列挙して、「計算」ボタンをクリックすると合計と平均が表示される、というマクロを書く。
実装
Option ExplicitPublic Sub CalculateSummary() Dim Model As New Model Dim View As New View Dim Control As New Control With Control .Model = Model .View = View End With Control.CalculateSummary End SubOption ExplicitPrivate My_Model As ModelPrivate My_View As ViewPublic Property Let Model(Model As Model) Set My_Model = ModelEnd PropertyPublic Property Let View(View As View) Set My_View = ViewEnd PropertyPublic Sub CalculateSummary() Dim Scores As Collection Set Scores = My_View.GetScores My_Model.CalculateSummary Scores My_View.ShowResult My_ModelEnd SubOption ExplicitPublic Function GetScores() As Collection Dim Scores As New Collection Dim ScoreCell As Range Set ScoreCell = ActiveSheet.Range("A2") Do While ScoreCell.Value <> "" Scores.Add CInt(ScoreCell.Value) Set ScoreCell = ScoreCell.Offset(1, 0) Loop Set GetScores = ScoresEnd FunctionPublic Sub ShowResult(Model As Model) Summary = Model.Sum Average = Model.AverageEnd SubPrivate Property Let Summary(Sum As Integer) ActiveSheet.Range("C2").Value = SumEnd PropertyPrivate Property Let Average(Ave As Double) ActiveSheet.Range("D2").Value = AveEnd PropertyOption ExplicitPrivate My_Sum As IntegerPrivate My_Average As DoublePrivate Sub Class_Initialize() My_Sum = 0 My_Average = 0End SubPublic Sub CalculateSummary(Scores As Collection) Dim Score As Variant For Each Score In Scores My_Sum = My_Sum + Score Next My_Average = My_Sum / Scores.CountEnd SubPublic Property Get Sum() As Integer Sum = My_SumEnd PropertyPublic Property Get Average() As Double Average = My_AverageEnd PropertyModel 用のシートを設ける
コメントで Model 用のシートを設けて、 View シートはそこを参照するようにしたら良いという情報を頂いたので、試してみました。
あと、はてブのコメントで View クラスはシートごとに用意されるコードに書いてはどうかという意見も頂いてその通りな気がしたので、それも試してみました。
ExcelVBAってモジュールやクラス以外にワークシートやワークブック単位にコードビハインドみたいのが書けるから、ビューはそこに定義したらどうかな。
シート
Sheet
- 計算結果を表示する部分のセルを、
modelシートを参照するように設定する。
model
- 入力は、逆に
modelシートが View のシートを参照している。
実装
Option ExplicitPublic Sub Execute() Dim Model As New Model Dim Control As New Control With Control .Model = Model .ModelSheet = ModelSheet End With Control.CalculateSummaryEnd SubOption ExplicitPrivate My_Model As ModelPrivate My_ModelSheet As ModelSheetPublic Property Let Model(Model As Model) Set My_Model = ModelEnd PropertyPublic Property Let ModelSheet(ModelSheet As ModelSheet) Set My_ModelSheet = ModelSheetEnd PropertyPublic Sub CalculateSummary() Dim Scores As Collection Set Scores = My_ModelSheet.GetScores My_Model.CalculateSummary Scores My_ModelSheet.ShowResult My_ModelEnd SubOption ExplicitPublic Function GetScores() As Collection Dim Scores As New Collection Dim ScoreCell As Range Set ScoreCell = Range("B3") Do While ScoreCell.Value <> "" Scores.Add CInt(ScoreCell.Value) Set ScoreCell = ScoreCell.Offset(1, 0) Loop Set GetScores = ScoresEnd FunctionPublic Sub ShowResult(Model As Model) Summary = Model.Sum Average = Model.AverageEnd SubPrivate Property Let Summary(Sum As Integer) Range("B1").Value = SumEnd PropertyPrivate Property Let Average(Ave As Double) Range("B2").Value = AveEnd PropertyModel は、実装は変更なしだが、Instancing を2 - PublicNotCreatable にする。
これは、モジュールの可視性を表している。
デフォルトの1 - Private だとModel をModelSheet から参照できないので、2 - PublicNotCreatable にしている。
参考
説明
- Model 用のシートを追加して、 View シートはそちらの値を参照するようにセルの値を設定する。
- こうすると、 View のシート上でセルの位置を変更しても、 VBA の実装には手を加えなくて良くなる。
- 例では Model 用シートを表示させているが、実際は非表示にすると思う。
- ただ、この方法は入出力するセルの個数が固定な場合は有効だが、動的に変化したりする場合は難しくなることが予想される。
- その場合は、直接シートを触るモジュールを用意することになると思う。
ある範囲のセルを１つずつ処理していく実装が書きたい
画面イメージ
ボタンをクリックすると、 A 列に入力されている各数値について偶数か奇数かを判定し、結果を B 列に出力する。
A 列を順番に処理していく実装が必要になるが、前述の MVC 構成を維持したまま、これを実装してみる。
実装
Option ExplicitPublic Sub Execute() Dim Model As New Model Dim View As New View Dim Control As New Control With Control .Model = Model .View = View End With Control.Execute End SubOption ExplicitPrivate My_Model As ModelPrivate My_View As ViewPublic Property Let Model(Model As Model) Set My_Model = ModelEnd PropertyPublic Property Let View(View As View) Set My_View = ViewEnd PropertyPublic Sub Execute() My_View.ForEach Me, "ProcessForEach"End SubPublic Sub ProcessForEach(RowIndex As Integer, Number As Integer) My_View.SetResult RowIndex, My_Model.IsEvenNumber(Number)End SubOption ExplicitPublic Sub ForEach(Instance As Control, MethodName As String) Dim Cell As Range Dim RowIndex As Integer Set Cell = ActiveSheet.Range("A1") RowIndex = 1 Do While Cell.Value <> "" CallByName Instance, MethodName, VbMethod, RowIndex, CInt(Cell.Value) Set Cell = Cell.Offset(1, 0) RowIndex = RowIndex + 1 Loop End SubPublic Sub SetResult(RowIndex As Integer, IsEvenNumber As Boolean) Dim Cell As Range Set Cell = ActiveSheet.Range("B" & RowIndex) Cell.Value = IIf(IsEvenNumber, "偶数", "奇数")End SubOption ExplicitPublic Function IsEvenNumber(Number As Integer) As Boolean IsEvenNumber = ((Number Mod 2) = 0)End FunctionCallByName でリフレクションみたいな感じでプロシージャを実行できることを利用している。
これで、 MVC の構成を維持し、ビューの感心事を外に漏らすことなくセルごとの順次処理が実装できる。
まとめて処理する
順次処理していかなくても、まとめて処理すればCallByName とか使わずに済むと、何故か後になって気づいたので実装してみる。
Option ExplicitPublic Sub Execute() Dim Model As New Model Dim View As New View Dim Control As New Control With Control .Model = Model .View = View End With Control.ExecuteEnd SubOption ExplicitPrivate My_Model As ModelPrivate My_View As ViewPublic Property Let Model(Model As Model) Set My_Model = ModelEnd PropertyPublic Property Let View(View As View) Set My_View = ViewEnd PropertyPublic Sub Execute() Dim Numbers As Collection Set Numbers = My_View.GetNumbers Dim Result As Collection Set Result = My_Model.JudgeEvenNumbers(Numbers) My_View.SetResults ResultEnd SubOption ExplicitPublic Function GetNumbers() As Collection Dim Numbers As New Collection Dim Cell As Range Set Cell = ActiveSheet.Range("A1") Do While Cell.Value <> "" Numbers.Add CInt(Cell.Value) Set Cell = Cell.Offset(1, 0) Loop Set GetNumbers = NumbersEnd FunctionPublic Sub SetResults(Result As Collection) Dim Numbers As New Collection Dim Cell As Range Dim i As Integer For i = 1 To Result.Count Set Cell = ActiveSheet.Range("B" & i) Cell.Value = IIf(Result.Item(i), "偶数", "奇数") NextEnd SubOption ExplicitPublic Function JudgeEvenNumbers(Numbers As Collection) As Collection Dim Result As New Collection Dim Number As Variant For Each Number In Numbers Result.Add IIf((Number Mod 2) = 0, True, False) Next Set JudgeEvenNumbers = ResultEnd Function個人的には、CallByName を使った方が好みかなぁ。
理由は、各プロシージャの役割が小さいから。
後者は、それぞれのプロシージャでループを回す必要があり、仕事量が多くノイズも多く感じる。
Excel で RESTful な Web API を叩きたい
Excel-REST を使う。
JSON の変換とかもやってくれるナイスガイ。
MIT ライセンスです。
セルの背景色を設定する
Public Sub Execute() Range("A1").Interior.ColorIndex = 38End Sub
RangeのInterior.ColorIndexに色ごとの整数値を設定すると、背景色を変更できる。- 使用できる色と数値の対応は、こちらに分かりやすくまとめられている。
参考
- Office TANAKA - 今さら聞けないVBA「Option Explicitって何？」
- VB エラー操作編
- プロパティを作成する - EXCEL-LENCE web
- 複数行をまとめてコメントアウト:Office(オフィス)の使い方-VBE(Visual Basic Editor)
- Office TANAKA - Excel VBAファイルの操作[ブックを開く]
- Office TANAKA - Excel VBAファイルの操作[作業用ブックを開く]
- Excel VBA を学ぶなら moug モーグ | 即効テクニック | データ型を確認する
- VBA／マクロ便利Tips：VBAにおけるワークシート関数――INDEX、MATCH、VLOOKUPの使い方 - ＠IT
- Dictionary（ディクショナリー）連想配列の使い方について｜ExcelマクロVBA技術解説
- メッセージを改行する - ダイアログ - Excel VBA入門
- ファイル・ディレクトリが存在するか確認する - アプリケーションとしてのVBA
- Office TANAKA - 番外編[存在しないパスのフォルダを一発で作成する]
- 20007-0 | 「0&」の意味 - VB初心者友の会 - はじめたばかりの人専用Q&A掲示板過去ログ
- SHCreateDirectoryEx function (Windows)
- Office TANAKA - Excel VBA講座：ファイルの操作[テキストファイルを操作する]
- エクセルExcel大事典 VBAマクロ Open Line Input # Write Print Get Put Seek Width UnLock
- Office TANAKA - Excel VBA関数[MsgBox関数]
- 全ては時の中に… : 【Excel VBA】Printステートメントに改行コードを含めない方法
Register as a new user and use Qiita more conveniently
- You get articles that match your needs
- You can efficiently read back useful information
- You can use dark theme