今更こんなものまとめるのは大変遺憾なのですが、色々なサイトを巡るのが面倒なため個人的に必要な情報だけまとめます。
必要に応じて拡張していきます。
Excel VBAには初めて触れるため、間違いがあればご指摘ください。
前提知識いろいろ
文法やら細かいところ
ループ文
Dim i As Long For i = 0 To 3 処理 Exit For ←break相当 Next i Do 処理 Exit Do ←break相当 Loop Until 式
条件式
If a = b Then ElseIf c <> d Then Else End If
式を評価してtrueの場合に返る値と、falseの時に返る値を指定したいときはIIfを使う。
以下はa=bのとき10が返り、そうでないときは5が返る例。
IIf(a=b,10,5)
関数
Subは戻り値を扱えない。Functionは戻り値を扱うことができる。
Sub func() ExitSub ←return相当 End Sub
Function func() func = 10 ←プロシージャ―名に値を入れることが戻り値を設定することに相当する。 Exit Function ←return相当 End Function
関数の呼び出し方。
Callステートメントで関数をコールする場合と、Callを省略する場合で引数の渡し方が異なるので注意!
参考:
Office TANAKA - VBAのステートメント[Call]
Callステートメントをつける場合は引数を()で囲って呼び出す。()をつけないとエラーになる。
Call func(a, b)
Callを省略する場合は引数を()で囲わずに呼び出す。()をつけるとエラーになることがある。
func a,b
またいくつか注意点が。
- 配列を引数として渡すときはCallをつけて呼び出す必要がある(これ本当?)
- 戻り値を受け取る場合はCallを省略し、()をつけて引数を渡さなければならない
疑問点。
↑が本当なら引数で配列を受け取り、戻り値を返す関数が作れないということになるのですが…。
動作させてみると上記のようになっているのですが、どなたか解決法知りませんか?
配列周り
配列の定義は以下。
他言語と違い、配列サイズを渡すのではなく配列に格納できる最大インデックスを渡す点に注意すること。
つまり以下の例ではvalueArray(5)としているが、これはインデックス0, 1, 2, 3, 4, 5が格納可能なサイズ6の配列を定義していることになる。
また、変数定義時に配列のサイズを指定しない場合は、後にReDimで配列サイズを拡張する必要がある。
Dim valueArray(5) As Long Dim emptyArray() As Long
配列に値が格納されている最も小さいインデックスおよび、値を入れることができる最大インデックスはそれぞれLBound,UBound関数で求められる。
これは配列のサイズや要素数ではなく、インデックスが返ってきている点に注意。
なお変数定義時にサイズを指定していないものをLBoundおよびUBoundに渡すと「インデックスが有効範囲にありません。」エラーになる。
Dim valueArray(5) As Long valueArray(0) = 10 LBound(valueArray) → 0が返る UBound(valueArray) → 5が返る
Dim emptyArray() As Long LBound(valueArray) → 「インデックスが有効範囲にありません。」エラー UBound(valueArray) → 「インデックスが有効範囲にありません。」エラー
Dim emptyArray() As Long ReDim emptyArray(2) emptyArray(0) = 10 emptyArray(1) = 11 LBound(valueArray) → 0が返る UBound(valueArray) → 2が返る
配列がサイズが0(初期化されていない状態)かどうかを判定する方法。
ネットで調べるとSgn関数で判定可能とありますが、この関数は使うべきではないと思います。理由は以下。
<理由>
本来Sgn関数は渡された数値の符号を判定して正なら1、0なら0、負なら-1を返す関数であり、配列は渡せないはずです。
しかしSgn関数に配列を渡すと、初期化されていないと0、初期化されていると-16064を返す動作をします。
しかし環境によっては「エラーコード51の内部エラー」になることから、おそらくバグでたまたまこのような動作をしているものと考えられます。
というわけで、配列サイズが0かどうか判定するときは、以下のような関数を作って判定しましょう。
'配列arrが初期化されていない(配列サイズが0)場合:0を返す '配列arrが初期化されている(配列サイズが1以上)の場合:1を返す 'arrが配列でない等予期せぬエラーの場合:-1を返す Public Function IsArrayEmpty(arr As Variant) As Integer On Error GoTo ERR_CATCH IsArrayEmpty = -1 Dim tmp As Integer tmp = UBound(arr) If tmp < 0 Then ' ユーザー定義型の場合は例外は発生せず-1が返ってくるため、ここで空として判断する IsArrayEmpty = 0 Exit Function End If IsArrayEmpty = 1 Exit Function ERR_CATCH: If Err.Number = 9 Then IsArrayEmpty = 0 End If End Function
呼び出し方は以下。
aは初期化済(サイズが0でない)配列、bが初期化されてない(サイズが0の)配列、cは配列でない変数です。
Dim a(5) As Long Dim b() As Long Dim c As Long If IsArrayEmpty(a) > 0 Then Debug.Print ("UBound(a)=" & UBound(a)) End If If IsArrayEmpty(b) = 0 Then Debug.Print ("bは初期化されていない(サイズが0の)配列です") End If If IsArrayEmpty(c) = -1 Then Debug.Print ("予期せぬエラー(cが配列かどうか確認してください)") End If
エラー
事象 | 対処 |
メッセージボックス「400」とだけ表示される | 400は「アプリケーション定義またはオブジェクト定義のエラーです。」の意味。識別子のタイプミス等。xlToRightをxlRightと書いてしまう等。 |
Setで変数を更新するとコンパイルエラーと言われる | Setで更新するのはSheetなどのオブジェクトだけ。Long型やString型などプリミティブな?型はSetを使わずに代入しなければならない |
x列目y行目のセルの値を取得・値を設定
取得
Dim cell As Range cell = Cells(1,1) Debug.Print(cell.Value)
なお「A1」のような名前でセルを指定することも可能。
Dim cell As Range cell = Range("A1") Debug.Print(cell.Value)
設定
セル操作をするには、まず書き込むシートをアクティブ化する必要がある。
以下は書き込み対象とは別のシート(例:def)から書き込みをする例。
シートdefの関数から別シート(xyz)を用意する場合は、別シート(xyz)をアクティブ化した後、アクティブ化したシートを指定してセルに値を入れる必要がある。
ここをActiveSheet.をつけずに単にCells(1,1)としてしまうと、元のシート(def)に書き込まれてしまうので注意。
(Cells単体の場合Me.(C++でいうthisポインタのようなもの)が省略されているためと思われる)
以下は"xyz"というシートの左上に値10を設定する例。
Worksheets("xyz").Activate ActiveSheet.Cells(1, 1) = 10
何度もActiveSheet.とつけるのが面倒な場合はWithステートメントを使って省略することができる。
ただし省略する場合は以下例のように頭に「.」をつけること。
Worksheets("xyz").Activate With ActiveSheet .Cells(1, 1) = 10 .Cells(1, 2) = 20 : End With
アクティブなセルを取得
Dim cell As Range cell = ActiveCell Debug.Print(cell.Value)
選択しているセル(複数選択可)を取得
Dim cells As Range cells = Selection
以下のようなセルを選択していた場合。
1 | 2 | 3 |
4 | 5 | 6 |
7 | 8 | 9 |
Selection(x)のxに渡す数値と、取得できる値は以下の通り。
Selection(1) | 1 |
Selection(2) | 2 |
Selection(3) | 3 |
Selection(4) | 4 |
Selection(5) | 5 |
Selection(6) | 6 |
Selection(7) | 7 |
Selection(8) | 8 |
Selection(9) | 9 |
選択範囲の行と列の数の取得方法は以下。
行数を取得 | Selection.Rows.Count |
列数を取得 | Selection.Columns.Count |
ここで例。二次元配列っぽく全データを巡回したいときはこう。
Dim x,y As Long Dim data As Range Set data = Selection For y = 1 To data.Rows.Count For x = 1 To data.Columns.Count Debug.Print( data(x + (y-1)*data.Columns.Count).Value ) Next x Next y
1つ隣のセルを取得する
これはセル上でTabやShift+Tabを押したときに選択されるセルを取得する
1つ前のセル(Shift+Tab相当) | ActiveCell.Previous |
1つ次のセル(Tab相当) | ActiveCell.Next |
特定のセルから辿った終端セルを取得する
これはCtrl+矢印で取得できるセルに相当する。
以下は左上(1,1)のセルから辿った終端セルを取得するコード。
Dim cell As Range cell = Cells(1,1).End(方向)
方向については以下の通り。
上(Ctrl+↑相当) | xlUp |
下(Ctrl+↓相当) | xlDown |
左(Ctrl+←相当) | xlToLeft |
右(Ctrl+→相当) | xlToRight |
終端セルが何番目か、数値で取得するには以下の通り。
Cells(1,1).End(xlToRight).Column | 列が4つなら「4」が返ってくる |
Cells(1,1).End(xlDown).Row | 行が4つなら「4」が返ってくる |
変数の型を判定する
式を評価し、その型を数値で返す。
数値はこのあたりを参照のこと。
Office TANAKA - Excel VBA関数[VarType]
VarType 関数 (Visual Basic for Applications) | Microsoft Docs
VarType(式)
セルが空かどうかを返す
IsEmpty(Cells(1,1))
可変配列(もどき)
以下は配列arrayを定義し、ReDimを使って配列サイズを1つ拡張している。
Preserveをつけると配列のデータを保持したままサイズを拡張する。
Dim array() As String 処理 ReDim Preserve array(UBound(array) + 1)
セルをクリア(空にする)・削除
左上のセルをクリアする
Cells(1,1).Clear
範囲指定してクリアする
以下どちらでもよい。
Range("A1:B2").Clear Range(Cells(1,1), Cells(2,2)).Clear
列全体をクリアする
以下どちらでもよい。
Range("A1").EntireColumn.Clear Cells(1,1).EntireColumn.Clear
行全体をクリアする
以下どちらでもよい。
Range("A1").EntireRow.Clear Cells(1,1).EntireRow.Clear
列・行を削除する
見ればわかると思うので説明は省略。
Range("A1").EntireColumn.Delete or Cells(1,1).EntireColumn.Delete Range("A1").EntireRow.Delete or Cells(1,1).EntireRow.Delete
構造体
Excel VBAにもC言語の構造体(struct)相当の仕組みが存在します。
以下は名前、年齢、コメントを持つStudent構造体の宣言方法です。
Private Type TStudent name As String age As Long comment As String End Type
使用するときは、通常の型と同様以下のように変数定義します。
Dim student1 As TStudent student1.name = "FUFUFU.T" student1.age = 18 student1.comment = "good"
なお、構造体のスコープにはPublicとPrivateがありますが、標準モジュールなどに定義するときにはPublicが使えますが、Sheet1などのシートモジュールに定義する際はPrivateにする必要があります。
※ちなみに元々1つあるThisWorkbookはブックモジュールである。
サンプルプログラム 1行1レコードから日付ごとのレコード数を数える
サンプルデータ
日付 | 名前 | 点数 | コメント |
2020/6/1 | TANAKA | 80 | aaaaa |
2020/6/2 | MAMADA | 100 | bbbbb |
2020/6/3 | HAKAMA | 50 | ccccc |
2020/6/3 | AYAYA | 77 | ddddd |
2020/6/5 | MOMOMO | 99 | eeeee |
プログラム
以下は"def"というシートに定義するものとします。
"def"シートにボタン等を配置し、ボタンが押されたらこの関数がコールされるようにしてください。
Option Explicit Private Type TRecord date As Date count As Long End Type Sub defFunc() Dim records() As TRecord ReDim records(0) ' データが1つもなければ何もせず帰る If IsEmpty(Cells(2, 1)) Then Exit Sub End If Dim LineNum As Long LineNum = Cells(1, 1).End(xlDown).Row ' めんどくせーけどy=2(最初のデータ)だけ特別扱いする。可変配列はReDim xxx(0)で ' 最低1つ分のデータを持たせておかないとUBound関数でエラーになるため。 records(0).date = Cells(2, 1) records(0).count = 1 ' 3行目(2つ目のデータ)からはループで処理する Dim y As Long For y = 3 To LineNum Dim bFoundSameDate As Boolean bFoundSameDate = False ' すでに同じ日付がないか、日付配列から検索する Dim indexForFind As Long For indexForFind = 0 To UBound(records) If records(indexForFind).date = Cells(y, 1) Then bFoundSameDate = True Exit For End If Next indexForFind If bFoundSameDate = True Then 'すでに一回以上出てきている日付。そしてそのインデックスはindexForFind。 records(indexForFind).count = records(indexForFind).count + 1 Else '初めて出てきた日付なので、配列を+1してカウントは1としておく。 ReDim Preserve records(UBound(records) + 1) records(UBound(records)).date = Cells(y, 1) records(UBound(records)).count = 1 End If Next y ' コンソールに結果表示 Dim c As Long For c = 0 To UBound(records) Debug.Print ("date=" & records(c).date & " count=" & records(c).count) Next c ' "xyz"というシート(事前に作成しておく)に結果を書き込む Worksheets("xyz").Activate ActiveSheet.Cells(1, 1).EntireColumn.Clear ActiveSheet.Cells(1, 2).EntireColumn.Clear Dim i As Long For i = 0 To UBound(records) ActiveSheet.Cells(i + 1, 1) = records(i).date ActiveSheet.Cells(i + 1, 2) = records(i).count Next i ' 累計も出してみる ActiveSheet.Cells(1, 3).Value = "=B1" ActiveSheet.Cells(2, 3).Value = "=B2+C1" ' ここからオートフィルで埋める ActiveSheet.Range("C2").AutoFill Destination:=ActiveSheet.Range("C2:C" & ActiveSheet.Cells(1, 2).End(xlDown).Row) End Sub
結果はこのように出力される。
date=2020/06/01 count=1 date=2020/06/02 count=1 date=2020/06/03 count=2 date=2020/06/05 count=1
さらに別シートxyzに以下のデータが書き込まれる。
2020/6/1 | 1 | 1 |
2020/6/2 | 1 | 2 |
2020/6/3 | 2 | 4 |
2020/6/5 | 1 | 5 |
個人的なメモ
abcというシートに目標件数を入れる場合の例。
abcシートの内容は以下の通り。カラムは日付、目標件数である。
2020/6/1 | 1 |
2020/6/2 | 1 |
2020/6/3 | 2 |
2020/6/4 | 3 |
2020/6/5 | 1 |
Option Explicit Private Type TRecord date As Date bugCount As Long '実績件数 expectCount As Long '目標件数(こっちは累計) End Type Sub defFunc() Dim records() As TRecord ReDim records(0) ' データが1つもなければ何もせず帰る If IsEmpty(Cells(2, 1)) Then Exit Sub End If Dim LineNum As Long LineNum = Cells(1, 1).End(xlDown).Row ' まず実績を読む ' めんどくせーけどy=2(最初のデータ)だけ特別扱いする。可変配列はReDim xxx(0)で ' 最低1つ分のデータを持たせておかないとUBound関数でエラーになるため。 records(0).date = Cells(2, 1) records(0).bugCount = 1 records(0).expectCount = 0 ' 3行目(2つ目のデータ)からはループで処理する Dim y As Long For y = 3 To LineNum Dim bFoundSameDate As Boolean bFoundSameDate = False ' すでに同じ日付がないか、日付配列から検索する Dim indexForFind As Long For indexForFind = 0 To UBound(records) If records(indexForFind).date = Cells(y, 1) Then bFoundSameDate = True Exit For End If Next indexForFind If bFoundSameDate = True Then 'すでに一回以上出てきている日付。そしてそのインデックスはindexForFind。 records(indexForFind).bugCount = records(indexForFind).bugCount + 1 Else '初めて出てきた日付なので、配列を+1してカウントは1としておく。 ReDim Preserve records(UBound(records) + 1) records(UBound(records)).date = Cells(y, 1) records(UBound(records)).bugCount = 1 records(UBound(records)).expectCount = -1 End If Next y ' 続いて、目標件数を読む Worksheets("abc").Activate ' 目標件数の累計を出す。目標件数は面倒なんで累計で読んじゃう。 ActiveSheet.Cells(1, 3).Value = "=B1" ActiveSheet.Cells(2, 3).Value = "=B2+C1" ' ここからオートフィルで埋める ActiveSheet.Range("C2").AutoFill Destination:=ActiveSheet.Range("C2:C" & ActiveSheet.Cells(1, 1).End(xlDown).Row) ' ヘッダがないので最初の行から読む LineNum = ActiveSheet.Cells(1, 1).End(xlDown).Row ' Dim y As Long For y = 1 To LineNum 'Dim bFoundSameDate As Boolean bFoundSameDate = False ' すでに同じ日付がないか、日付配列から検索する 'Dim indexForFind As Long For indexForFind = 0 To UBound(records) If records(indexForFind).date = ActiveSheet.Cells(y, 1) Then bFoundSameDate = True Exit For End If Next indexForFind If bFoundSameDate = True Then 'すでに一回以上出てきている日付。そしてそのインデックスはindexForFind。 records(indexForFind).expectCount = ActiveSheet.Cells(y, 3) Else '初めて出てきた日付なので、配列サイズを+1して値を入れる ReDim Preserve records(UBound(records) + 1) records(UBound(records)).date = ActiveSheet.Cells(y, 1) records(UBound(records)).bugCount = 0 records(UBound(records)).expectCount = ActiveSheet.Cells(y, 3) End If Next y ' コンソールに結果表示 'Dim c As Long 'For c = 0 To UBound(records) ' Debug.Print ("date=" & records(c).date & " bugCount=" & records(c).bugCount) 'Next c ' "xyz"というシート(事前に作成しておく)に結果を書き込む Worksheets("xyz").Activate ActiveSheet.Cells(1, 1).EntireColumn.Clear ActiveSheet.Cells(1, 2).EntireColumn.Clear ActiveSheet.Cells(1, 3).EntireColumn.Clear ActiveSheet.Cells(1, 4).EntireColumn.Clear ActiveSheet.Cells(1, 1) = "日付" ActiveSheet.Cells(1, 2) = "目標件数(累計)" ActiveSheet.Cells(1, 3) = "実績件数(累計)" ActiveSheet.Cells(1, 4) = "実績件数(日毎)" Dim i As Long For i = 0 To UBound(records) ActiveSheet.Cells(i + 2, 1) = records(i).date ActiveSheet.Cells(i + 2, 2) = records(i).expectCount '目標件数(累計) ActiveSheet.Cells(i + 2, 4) = records(i).bugCount '実績件数 Next i ' 日付順にソート Call ActiveSheet.Range("A2:D" & ActiveSheet.Cells(2, 1).End(xlDown).Row).Sort(Key1:=ActiveSheet.Range("A2")) ' 目標件数の累計は、目標の入力データがない場合-1になっているので、その日付の時点で最も大きい件数を入れて補完する Dim expectMaxCount As Long expectMaxCount = 0 For y = 2 To ActiveSheet.Cells(2, 1).End(xlDown).Row If ActiveSheet.Cells(y, 2) = -1 Then ActiveSheet.Cells(y, 2) = expectMaxCount Else expectMaxCount = ActiveSheet.Cells(y, 2) End If Next y ' 実績の累計も出してみる ActiveSheet.Cells(2, 3).Value = "=D2" ActiveSheet.Cells(3, 3).Value = "=D3+C2" ' ここからオートフィルで埋める ActiveSheet.Range("C3").AutoFill Destination:=ActiveSheet.Range("C3:C" & ActiveSheet.Cells(2, 1).End(xlDown).Row) End Sub
結果は以下のようになる。
日付 | 目標件数(累計) | 実績件数(累計) | 実績件数(日毎) |
2020/6/1 | 1 | 1 | 1 |
2020/6/2 | 2 | 2 | 1 |
2020/6/3 | 4 | 4 | 2 |
2020/6/4 | 7 | 4 | 0 |
2020/6/5 | 8 | 5 | 1 |