Excel VBAでセルを操作する関数等リファレンス 個人的メモ
今更こんなものまとめるのは大変遺憾なのですが、色々なサイトを巡るのが面倒なため個人的に必要な情報だけまとめます。
必要に応じて拡張していきます。
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 |
