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 Integer

Dim <変数名> 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 Sub

イミディエイトウィンドウ

Sheet1.procedure
 10 
string
True

基本は、 <変数名> = <代入する値> で変数に値を代入できる。

###オブジェクト型の代入
代入する値が__オブジェクト型__の場合は注意が必要。

これはエラーになる

Public Sub procedure()
    Dim c As Collection
    
    c = New Collection
    
    Debug.Print c.Count
End Sub

これを実行すると、「引数は省略できません」という意味不明なエラーメッセージが表示される ~~ﾌｧｯｸ!!~~。

正しくは、以下のように変数の前に Set を入れる。

Public Sub procedure()
    Dim c As Collection
    
    Set c = New Collection ' 変数の前に Set を入れる
    
    Debug.Print c.Count
End Sub

イミディエイトウィンドウ

Sheet1.procedure
 0

オブジェクト型というのは、 Integer や Boolean、 String などの基本的な型以外全てを指す。

これが意外と厄介モノで、つい記述を忘れて実行時にエラーが連発したりする。

####Function の戻り値でも必要

Public Sub procedure()
    Dim c As Collection
    
    Set c = GetCollection
End Sub

Public Function GetCollection() As Collection
    GetCollection = New Collection
End Function

procedure プロシージャで Set c = GetCollection としているため大丈夫そうに見えるが、これを実行するとエラーが発生する。

正しくは、以下のように実装する。

Public Sub procedure()
    Dim c As Collection
    
    Set c = GetCollection
End Sub

Public Function GetCollection() As Collection
    Set GetCollection = New Collection
End Function

Function の戻り値がオブジェクト型の場合も、 Set を付けなければならない。

##If 文

    If Number = 1 Then
        ' ...
    ElseIf Nember = 5 Then
        ' ...
    Else
        ' ...
    End If

ループ

For 文

Dim i As Integer

For i = 0 To 5
    Debug.Print i
Next

実行結果

Do While 文

Dim i As Integer

Do While i < 5
    Debug.Print i
    i = i + 1
Loop

実行結果

##プロシージャ
###プロシージャの種類

プロシージャには、 Sub と Function の２種類が存在する。

Public Sub SubProcedure()
    Debug.Print "SubProcedure"
    Debug.Print FunctionProcedure
End Sub

Public Function FunctionProcedure() As String
    FunctionProcedure = "FunctionProcedure"
End Function

イミディエイトウィンドウ

Sheet1.SubProcedure
SubProcedure
FunctionProcedure

Sub は戻り値無し、 Function は戻り値有りという違いがある。

###Function の戻り値
Function の戻り値は、プロシージャ名と同じ名前の変数？に戻り値を代入することで実現できる。

Public Sub SubProcedure()
    Debug.Print "SubProcedure"
    Debug.Print FunctionProcedure
End Sub

Public Function FunctionProcedure() As String
    FunctionProcedure = "FunctionProcedure"
    Debug.Print "ここも実行される"
End Function

イミディエイトウィンドウ

Sheet1.SubProcedure
SubProcedure
ここも実行される
FunctionProcedure

Java などの return とは異なり、戻り値を設定したあとも処理が続行される。

そのプロシージャの処理を中断させたい場合は Exit Function または Exit Sub と記述する。

Public Sub SubProcedure()
    Debug.Print "SubProcedure"
    Debug.Print FunctionProcedure
End Sub

Public Function FunctionProcedure() As String
    FunctionProcedure = "FunctionProcedure"
    Exit Function
    Debug.Print "ここは実行されない"
End Function

イミディエイトウィンドウ

Sheet1.SubProcedure
SubProcedure
FunctionProcedure

###プロシージャの呼び出し方

Public Sub OneParameter(a As Integer)
    Debug.Print a
End Sub

Public Sub TwoParameter(a As Integer, b As Integer)
    Debug.Print a + b
End Sub

イミディエイトウィンドウ

Sheet1.OneParameter 1
 1 
Sheet1.TwoParameter 1, 2
 3

プロシージャを単独で呼び出す場合、丸括弧は不要（付けるとコンパイルエラーになる）。

ただし、下記例のように、あるプロシージャの引数に別のプロシージャの呼び出しを渡す場合などは、丸括弧が必要になる。

Public Sub OneParameter(a As Integer)
    Debug.Print TwoParameter(a, 10) ' TwoParameter の呼び出しに括弧が必要になる
End Sub

Public Function TwoParameter(a As Integer, b As Integer) As Integer
    TwoParameter = a + b
End Function

イミディエイトウィンドウ

Sheet1.OneParameter 5
 15

変数に代入するときに、右辺でプロシージャを呼ぶ場合も必要になる。

Public Sub procedure()
    Dim s As String
    s = TypeName("hoge") ' 括弧が必要
    Debug.Print s
End Sub

###プロシージャの処理を途中で中断させる

MyClass

Public Sub SubProcedure()
    Debug.Print "[Sub] before"
    Exit Sub
    Debug.Print "[Sub] after"
End Sub

Public Function FunctionProcedure()
    Debug.Print "[Function] before"
    Exit Function
    Debug.Print "[Function] after"
End Function

Public Property Let Value(Value As Integer)
    Debug.Print "[Property] before"
    Exit Property
    Debug.Print "[Property] after"
End Property

Module1

Public 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"
    SubProcedure
End Sub

Public Sub SubProcedure(Optional Value As String = "Default Value")
    Debug.Print Value
End Sub

Execute プロシージャを実行する。

実行結果

Explicit Value
Default Value

Optional <引数の宣言> = <デフォルト値> とすることで、省略可能な引数を定義できる。

##データ型を確認する

Dim a As String
Dim b As Boolean
Dim c As New Collection

Debug.Print "a : " & TypeName(a)
Debug.Print "b : " & TypeName(b)
Debug.Print "c : " & TypeName(c)

実行結果

a : String
b : Boolean
c : Collection

TypeName() でデータ型を文字列で取得できる。

##文字列中に改行コードを入れる

Dim Text As String
Text = "aaa" & vbCrLf & "bbb"

Debug.Print Text

実行結果

aaa
bbb

vbCrLf という定数が定義されているので、それを利用する。
vbCrLf は、キャリッジリターンとラインフィード。

##コードの途中で改行を入れる

Dim Text _
As String

Text = _
"Hoge" & _
"Fuga"

Debug.Print Text

実行結果

HogeFuga

_ を末尾に記述することで、コードを途中で改行させられる。

コレクション（List）を使用する

VBA には配列もあるが、サイズの調整とかを自分でしないといけないのでなんかアレ。

基本

Dim list As New Collection

list.Add "hoge"
list.Add "fuga"

Debug.Print list(1)
Debug.Print list.Item(2)

実行結果

hoge
fuga

Collection というクラスを New することで、コレクション（List みたいなの）を生成できる。
Add() メソッドで要素を追加できる。
丸括弧か Item() メソッドを使うことで、インデックス指定で要素を取得できる。
インデックスは、まさかの 1 始まり。

ループ

Dim list As New Collection

list.Add "hoge"
list.Add "fuga"
list.Add "piyo"

Dim i As Integer

For i = 1 To list.Count
    Debug.Print (i & " : " & list(i))
Next

Dim e As Variant

For Each e In list
    Debug.Print e
Next

実行結果

1 : hoge
2 : fuga
3 : piyo
hoge
fuga
piyo

Count で要素数を取得できるので、それで For ループを回すことができる。
もしくは、 For Each でループすることもできる。

要素の削除

Dim list As New Collection

list.Add "hoge"
list.Add "fuga"
list.Add "piyo"

list.Remove 2

Dim i As Integer

For i = 1 To list.Count
    Debug.Print (i & " : " & list(i))
Next

実行結果

Hoge
1 : hoge
2 : piyo

Remove() メソッドを使うと、インデックスを指定して要素を削除できる。

#連想配列を使用する
##基本

Dim Map As Object
Set 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 Dictionary

Map("hoge") = "Hoge"

Debug.Print "hoge: " & Map("hoge")

##ループ処理

Dim Map As New Dictionary

Map("hoge") = "Hoge"
Map.Add "fuga", "Fuga"

Dim Key As Variant

For Each Key In Map
    Debug.Print "Key=" & Key & ", Value=" & Map(Key)
Next

実行結果

Key=hoge, Value=Hoge
Key=fuga, Value=Fuga

##Dictionary に定義されているメソッド

フィールド名	引数	説明
`Exists`	キー	指定したキーの要素が存在するか確認する。
`Count`	-	保有している要素の数を取得する。
`Keys`	-	保有しているキーを配列で取得する。
`Add`	キー, 値	要素を追加する。
`Item`	キー	指定したキーの値を取得する。
`Items`	-	全ての要素の値を配列で取得する。

##セルで使用できる関数を VBA から利用する

Dim Summary As Integer
Summary = WorksheetFunction.Sum(Range("A1:C1"))
Debug.Print Summary

WorksheetFunction にメソッドとして関数が定義されている。
範囲やセルを渡していた部分には、 Range を渡すようにする。

#フォルダ・ファイル操作
##ディレクトリ・ファイルが存在するか確認する

Dim ExistsDirPath As String
Dim NotExistsDirPath As String

ExistsDirPath = "D:\tmp\excel"
NotExistsDirPath = "D:\xxxx"

Debug.Print "> " & Dir(ExistsDirPath, vbDirectory)
Debug.Print "> " & Dir(NotExistsDirPath, vbDirectory)
Debug.Print "> " & (Dir(NotExistsDirPath, vbDirectory) = "")

> excel
> 
> True

Dir() 関数で存在チェックが行える。
ディレクトリをチェックする場合は、第二引数に 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 Long

Public Function MkDirs(Path As String) As Long
    MkDirs = SHCreateDirectoryEx(0&, Path, 0&)
End Function

Public Sub Test()
    Debug.Print MkDirs("D:\tmp\hoge\fuga")
End Sub

Win API を呼び出しているらしい。
作成に成功したら、0が返される。
既にフォルダが存在する場合、自分の環境では 183 が返された。

##カレントフォルダを取得する

Dim CurrentPath As String
CurrentPath = ActiveWorkbook.Path

ActiveWorkbook.Path で現在開いているブックのあるディレクトリのパスを取得できる。

##ファイル出力
###基本

Open "D:\tmp\test.txt" For Output As #1
Print #1, "Hello World!!"
Close #1

test.txt

Hello World!!

Open <ファイルパス> For <処理> As <ファイルNO> で、ファイルを開く。
Print <ファイルNO>, "出力内容" で、ファイルに文字列を書き込む（末尾に改行が自動で付く）。
Close <ファイルNO> で、ファイルを閉じる。
<処理> には、以下が指定可能。

処理種別	説明
`Input`	読み取り
`Output`	書き込み（上書き）
`Append`	書き込み（追記）

<ファイルNO> は、操作しているファイルを識別するための数値。
複数のファイルを同時に制御する必要がないなら、 #1 とかで OK。
複数ファイルを同時に扱う場合は、どの数値を利用しているか管理が大変になるので、その場合は以下のようにする。

Dim FileNo As Integer
FileNo = FreeFile ' ★FreeFile 関数を使う

Open "D:\tmp\test.txt" For Output As FileNo
Print #FileNo, "Hello World!!"
Close FileNo

FreeFile は、現在利用可能なファイルNO を返す関数。

###Print ステートメントで改行コードを付けないようにする

Open "D:\tmp\test.txt" For Output As #1
Print #1, "Hello World!!";
Close #1

test.txt

Hello World!!

Print ステートメントの末尾にセミコロン ; をつけると、末尾の改行が付かなくなる。

###エラー時のクローズは必要か？

Open "D:\tmp\test.txt" For Output As #1
Print #1, "Hello World!!"

こんな感じで、 Close することなくプロシージャが正常すると、ファイルは開きっぱなしになる。
このファイルのロックは、 Excel のブックを閉じるまで解放されない。

Open "D:\tmp\test.txt" For Output As #1
Print #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 Integer
Clicked = MsgBox("Hello World!!", vbOKCancel)

Debug.Print Clicked

キャンセルボタンを押した場合

値	対応する定数	押されたボタン
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 MyClass
End Sub

インスタンスを生成する方法は、以下のいずれかがある。

Dim で宣言するときに、 As New <クラスモジュール名> とする。
変数に代入するときに New <クラスモジュール名> とする。

##コンストラクタ

MyClass

Option Explicit

Private Sub Class_Initialize()
    Debug.Print "initialize"
End Sub

Private Sub Class_Terminate()
    Debug.Print "terminate"
End Sub

Public Sub procedure()
    Dim mine As MyClass
    Set mine = New MyClass
End Sub

イミディエイトウィンドウ

Sheet1.procedure
initialize
terminate

Class_Initialize、Class_Terminate という名前のプロシージャを宣言することで、インスタンス生成時と削除時に処理を挟むことができる。

###引数が宣言できない！
ただし、このコンストラクタには引数を宣言できない ~~ﾌｧｯｸ!!~~。

妥協策としては、

init のようなプロシージャを作り、コーディング規約で init というプロシージャがあれば必ずインスタンス生成後に実行させること、みたいな縛りを入れる。
標準モジュールにファクトリ用のプロシージャを定義する。

こんな方法もある。

###Dim で宣言するときに New しているだけでは実行されない！

MyClass

Option Explicit

Private Sub Class_Initialize()
    Debug.Print "initialize"
End Sub

Private Sub Class_Terminate()
    Debug.Print "terminate"
End Sub

Public Sub Method()
    Debug.Print "Method"
End Sub

Public Sub procedure()
    Dim mine As New MyClass
    Debug.Print "before call Method"
    mine.Method
End Sub

イミディエイトウィンドウ

Sheet1.procedure
before call Method
initialize
Method
terminate

Dim の宣言と同時に New した場合、その時点では Class_Initialize は呼ばれない。
Class_Initialize が実行されるのは、最初にそのインスタンスが使用されるときなので注意。

##静的メンバーは定義できない！
VBA のクラスモジュールには、静的な変数やプロシージャを定義できない ~~ﾌｧｯｸ!!~~。

静的なプロシージャなどは、標準モジュールに定義しなければならない。

##自分のメンバーにアクセスする

MyClass

Option Explicit

Public Name As String

Public Sub Introduce()
    Debug.Print "My name is " & Me.Name & "."
End Sub

Public Sub procedure()
    Dim mine As New MyClass
    mine.Name = "Taro"
    mine.Introduce
End Sub

イミディエイトウィンドウ

Sheet1.procedure
My name is Taro.

Me.<メンバー> で、自分のメンバーにアクセスできる。

ただし、この方法でアクセスできるメンバーは、可視性が Public で宣言されている必要がある。

Private なメンバーにアクセスする場合は、 Me は使えない。

##プロパティを定義する

Option Explicit

Private My_Age As Integer

Public Property Let Age(In_Age As Integer)
    My_Age = In_Age
End Property

Public Property Get Age() As Integer
    Age = My_Age
End Property

Public Sub procedure()
    Dim mine As New MyClass
    mine.Age = 17
    
    Debug.Print mine.Age
    
End Sub

イミディエイトウィンドウ

Sheet1.procedure
 17

Public Property Let <プロパティ名>(引数) で書き込み用のプロパティを、
Public Property Get <プロパティ名>() As <型> で読み取り用のプロパティを宣言できる。

プロパティは、 <変数名>.<プロパティ名> でアクセスできるようになる。

##With でメソッドの呼び出しをまとめる

MyClass

Private MyValue As Integer

Public Sub SubProcedure(Message As String)
    Debug.Print "Sub Procedure : " & Message
End Sub

Public Property Let Value(Value As Integer)
    MyValue = Value
End Property

Module1

Public Sub Execute()
    Dim Instance As New MyClass
    
    With Instance
        .SubProcedure "with statement"
        .Value = 100
    End With
End Sub

実行結果

Sub Procedure : with statement

With を使うことで、あるインスタンスのメソッド（プロパティ）呼び出しをまとめることができる。

##インターフェースを実装する

MyInterface

Public Sub Method()
End Sub

Hoge

Implements MyInterface

Public Sub MyInterface_Method()
    Debug.Print "Hoge Method"
End Sub

Fuga

Implements MyInterface

Public Sub MyInterface_Method()
    Debug.Print "Fuga Method"
End Sub

Module1

Public Sub Execute()
    Dim HogeInstance As MyInterface
    Dim FugaInstance As MyInterface
    
    Set HogeInstance = New Hoge
    Set FugaInstance = New Fuga
    
    HogeInstance.Method
    FugaInstance.Method

End Sub

Module1.Execute プロシージャを実行する。

イミディエイトウィンドウ出力

Hoge Method
Fuga 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_Method
End Sub

実行結果

Hoge Method

普通にダウンキャスト後の型の変数に代入すればいい。

#例外
##例外をスローする

Public Sub procedure()
    Err.Raise 10000
End 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.Description
End Sub

イミディエイトウィンドウ

Sheet1.procedure
10000 : My Error

On Error GoTo <ラベル名> を先頭に書き、ラベル名で指定したラベルを宣言する。

すると、例外がスローされたときに指定したラベルに処理が移る。

その際、 Err オブジェクトにスローされた例外の情報が格納される。

注意しないといけないのは、ラベルの前にプロシージャを終了させる Exit Sub または Exit Procedure を入れないといけない、という点。

あくまでラベルなので、これが漏れていると普通に例外処理を書いた領域が実行されてしまう ~~ﾌｧｯｸ!!~~。

##例外の情報が・・・消えた・・・？

Public Sub procedure()
    On Error GoTo EXCEPTION

    Err.Raise 10000, , "My Error"

    Exit Sub
EXCEPTION:
    hoge
    Debug.Print Err.Number & " : " & Err.Description
End Sub

Public Sub hoge()
    On Error Resume Next
    Debug.Print "hoge"
End Sub

イミディエイトウィンドウ

Sheet1.procedure
hoge
0 :

例外が発生したあとで別のプロシージャ（hoge）を呼び出し、その後で Err の情報を出力しようとすると、エラーコードと説明が 0 と空になるという現象が発生している。

原因は、 hoge プロシージャで使用されている Exit Sub ステートメントにある。

Err の仕様として、 On Error ステートメントが実行されると、 Err に設定されている情報がリセットされるようになっている ~~ﾌｧｯｸ!!~~。

#マクロの実行を終了させる

Public Sub procedure()
    End
End Sub

End でマクロの実行を終了させることができる。

#他のブックをこっそり開いて操作する

ファイル構成

|-main.xlsm
`-sub.xlsx

sub.xlsx

main.xlsmのModule1

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 Value
End Sub

Module1.Execute プロシージャを実行する。

実行結果

sub book

main.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 String
Name = ActiveSheet.Range("A1").Value

実は、シートが何らかの申請書で、 "A1" には申請者の名前が入力されていたとする。

ビュークラスを使って操作をカプセル化すれば、

抽象度が高い実装

Dim ApplicationSheet As New ApplicationSheet
Dim Name As String

Name = ApplicationSheet.ApplicantName

というふうに実装の抽象度が上がり、可読性があがる。

また、名前を取得する操作が共通化されるので、他のところでも名前が必要になった場合、簡単に使いまわすことができる。

Excel の API の操作方法を知ることなく欲しい情報にアクセスできるようになるので、実装しやすさも向上する。

##MVC の実装例
###前置き
あくまで例のための実装なので、「SUM 関数や AVERAGE 関数つかえばマクロ組む必要ねーじゃん」というツッコミはなしで。

###画面

A列に点数を列挙して、「計算」ボタンをクリックすると合計と平均が表示される、というマクロを書く。

###実装

標準モジュール

Option Explicit

Public 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 Sub

Control

Option Explicit

Private My_Model As Model
Private My_View As View

Public Property Let Model(Model As Model)
    Set My_Model = Model
End Property

Public Property Let View(View As View)
    Set My_View = View
End Property

Public Sub CalculateSummary()
    Dim Scores As Collection
    
    Set Scores = My_View.GetScores
    
    My_Model.CalculateSummary Scores
    
    My_View.ShowResult My_Model
End Sub

View

Option Explicit

Public 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 = Scores
End Function

Public Sub ShowResult(Model As Model)
    Summary = Model.Sum
    Average = Model.Average
End Sub

Private Property Let Summary(Sum As Integer)
    ActiveSheet.Range("C2").Value = Sum
End Property

Private Property Let Average(Ave As Double)
    ActiveSheet.Range("D2").Value = Ave
End Property

Model

Option Explicit

Private My_Sum As Integer
Private My_Average As Double

Private Sub Class_Initialize()
    My_Sum = 0
    My_Average = 0
End Sub

Public 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.Count
End Sub

Public Property Get Sum() As Integer
    Sum = My_Sum
End Property

Public Property Get Average() As Double
    Average = My_Average
End Property

Model 用のシートを設ける

コメントで Model 用のシートを設けて、 View シートはそこを参照するようにしたら良いという情報を頂いたので、試してみました。

あと、はてブのコメントで View クラスはシートごとに用意されるコードに書いてはどうかという意見も頂いてその通りな気がしたので、それも試してみました。

ExcelVBAってモジュールやクラス以外にワークシートやワークブック単位にコードビハインドみたいのが書けるから、ビューはそこに定義したらどうかな。

シート

Sheet

計算結果を表示する部分のセルを、 model シートを参照するように設定する。

model

入力は、逆に model シートが View のシートを参照している。

実装

Main

Option Explicit

Public Sub Execute()
    Dim Model As New Model
    Dim Control As New Control

    With Control
        .Model = Model
        .ModelSheet = ModelSheet
    End With

    Control.CalculateSummary
End Sub

Control

Option Explicit

Private My_Model As Model
Private My_ModelSheet As ModelSheet

Public Property Let Model(Model As Model)
    Set My_Model = Model
End Property

Public Property Let ModelSheet(ModelSheet As ModelSheet)
    Set My_ModelSheet = ModelSheet
End Property

Public Sub CalculateSummary()
    Dim Scores As Collection
    
    Set Scores = My_ModelSheet.GetScores
    
    My_Model.CalculateSummary Scores
    
    My_ModelSheet.ShowResult My_Model
End Sub

ModelSheet

Option Explicit

Public 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 = Scores
End Function

Public Sub ShowResult(Model As Model)
    Summary = Model.Sum
    Average = Model.Average
End Sub

Private Property Let Summary(Sum As Integer)
    Range("B1").Value = Sum
End Property

Private Property Let Average(Ave As Double)
    Range("B2").Value = Ave
End Property

Model は、実装は変更なしだが、 Instancing を 2 - PublicNotCreatable にする。

これは、モジュールの可視性を表している。
デフォルトの 1 - Private だと Model を ModelSheet から参照できないので、 2 - PublicNotCreatable にしている。

参考

Excel VBAでのクラスモジュール、標準モジュールのスコープまとめ(Friend, PublicNotCreatableなど) - Qiita

説明

Model 用のシートを追加して、 View シートはそちらの値を参照するようにセルの値を設定する。
こうすると、 View のシート上でセルの位置を変更しても、 VBA の実装には手を加えなくて良くなる。
例では Model 用シートを表示させているが、実際は非表示にすると思う。
ただ、この方法は入出力するセルの個数が固定な場合は有効だが、動的に変化したりする場合は難しくなることが予想される。
- その場合は、直接シートを触るモジュールを用意することになると思う。

#ある範囲のセルを１つずつ処理していく実装が書きたい
##画面イメージ

ボタンをクリックすると、 A 列に入力されている各数値について偶数か奇数かを判定し、結果を B 列に出力する。

A 列を順番に処理していく実装が必要になるが、前述の MVC 構成を維持したまま、これを実装してみる。

##実装

標準モジュール

Option Explicit

Public 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 Sub

Control

Option Explicit

Private My_Model As Model
Private My_View As View

Public Property Let Model(Model As Model)
    Set My_Model = Model
End Property

Public Property Let View(View As View)
    Set My_View = View
End Property

Public Sub Execute()
    My_View.ForEach Me, "ProcessForEach"
End Sub

Public Sub ProcessForEach(RowIndex As Integer, Number As Integer)
    My_View.SetResult RowIndex, My_Model.IsEvenNumber(Number)
End Sub

View

Option Explicit

Public 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 Sub

Public Sub SetResult(RowIndex As Integer, IsEvenNumber As Boolean)
    Dim Cell As Range
    
    Set Cell = ActiveSheet.Range("B" & RowIndex)
    
    Cell.Value = IIf(IsEvenNumber, "偶数", "奇数")
End Sub

Model

Option Explicit

Public Function IsEvenNumber(Number As Integer) As Boolean
    IsEvenNumber = ((Number Mod 2) = 0)
End Function

CallByName でリフレクションみたいな感じでプロシージャを実行できることを利用している。

これで、 MVC の構成を維持し、ビューの感心事を外に漏らすことなくセルごとの順次処理が実装できる。

###まとめて処理する
順次処理していかなくても、まとめて処理すれば CallByName とか使わずに済むと、何故か後になって気づいたので実装してみる。

標準モジュール

Option Explicit

Public 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 Sub

Control

Option Explicit

Private My_Model As Model
Private My_View As View

Public Property Let Model(Model As Model)
    Set My_Model = Model
End Property

Public Property Let View(View As View)
    Set My_View = View
End Property

Public Sub Execute()
    Dim Numbers As Collection
    
    Set Numbers = My_View.GetNumbers
    
    Dim Result As Collection
    
    Set Result = My_Model.JudgeEvenNumbers(Numbers)
    
    My_View.SetResults Result
End Sub

View

Option Explicit

Public 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 = Numbers
End Function

Public 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), "偶数", "奇数")
    Next
End Sub

Model

Option Explicit

Public 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 = Result
End Function

個人的には、 CallByName を使った方が好みかなぁ。
理由は、各プロシージャの役割が小さいから。

後者は、それぞれのプロシージャでループを回す必要があり、仕事量が多くノイズも多く感じる。

#Excel で RESTful な Web API を叩きたい

Excel-REST を使う。

JSON の変換とかもやってくれるナイスガイ。
MIT ライセンスです。

#セルの背景色を設定する

Public Sub Execute()
    Range("A1").Interior.ColorIndex = 38
End Sub

Range の Interior.ColorIndex に色ごとの整数値を設定すると、背景色を変更できる。
使用できる色と数値の対応は、こちらに分かりやすくまとめられている。

#参考

? で出力する

ショートカットを設定する

ループ

For 文

Do While 文

コレクション（List）を使用する

基本

ループ

要素の削除

Model 用のシートを設ける

シート

実装

説明

`?` で出力する