Excel VBAでよく使用する関数

Excel VBAで私がよく使用する関数の一覧です。

変数の宣言

変数とデータ型を宣言し、変数に値を代入します。

Dim a As Integer
a = 1

下のようにデータ型の宣言は省略できます。省略された場合、変数はVariant型（全てのデータ型）として扱われます。

a = 1
str1 = "文字列"
str2 = "答えは" & a "です"

配列の宣言

配列は「Dim 配列名(要素数) As データ型」で宣言します。Dim str(3) as Stringと宣言した場合はa(0), a(1), a(2)の3つの要素の配列が作成される。（インデックス番号は0から始まることに注意）

Dim str(3) As String
str(0) = "文字列Ａ"
str(1) = "文字列Ｂ"
str(3) = "文字列Ｃ"

セルの指定

セルの指定方法にはCellsとRangeがあります。Cells(行, 列)は単一セルの指定に使用します。行番号と列番号には変数を使用することができます。Range(“セル範囲”)は単一セルまたはセル範囲の指定に使用します。

セル範囲はA1:B3などと記述するため基本的に変数を使用できません。変数を使って範囲指定したい場合には、RangeとCellsを組み合わせて使用します。

Cellsで単一セルを指定

cells(行, 列)で指定します。下記はA2セルを指定する場合。

cells(1, 2)

Rangeで単一セルを指定

1セルのみ選択する場合は、Range(“セル番号”)で指定します。

Range("A1")

Rangeで範囲を指定

範囲で指定する場合はRange(“開始セル番号:終了セル番号”)で指定します。

Range("A1:C3")

Rangeで複数の範囲を指定

複数の範囲を指定する場合は、Range(“開始セル:終了セル, 開始セル:終了セル”)で指定します。下記はA1:C3とF5:G10の２つの範囲が指定されます。

Range("A1:C3, F5:G10")

RangeとCellsを組み合わせて範囲を指定

変数で範囲指定したい場合には、RangeとCellsを組み合わせて使用します。下記のように記述すると変数a, b, c, dで範囲指定できます。

Range(Cells(a, b), Cells(c, d))

行・列の指定

行全体または列全体を指定するには、CellsやRangeの後に.EntireRow（行全体）または.EntireColumn（列全体）を付け加えます。

行の指定

'これは1行目全体を指定します。
Cells(1,2).EntireRow

列の指定

'これはB列全体が指定されます。
Cells(1,2).EntireColumn

シート全体の指定

シート全体を指定する場合はCellsと記述するだけです。

Cells

ブックとシートの指定

Excelのブックとシートを指定する方法です。下記で希望のブックとシートのA1セルが指定されます。

Wordbooks("ブック名").Worksheets("シート名").cells(1, 1)

最終行・最終列の指定

最終行や最終列をしているする場合には以下のように記述します。

最終行のセルを指定

データが入っている最終行を指定するには、エクセルシートの一番下の行からCtrl+↑でデータの最終行にジャンプする操作を記述します。下記がそのコードです。Rows.Countはエクセルシートの一番下の行を意味します。End(xlUp)はCtrl+↑と同じ動作をします。

Cells(Rows.Count, 列番号).End(xlUp)

最終列のセル指定

データが入っている最終列を指定するには、エクセルシートの一番右の列からCtrl+←でデータの最終列にジャンプする操作を記述します。下記がそのコードです。Columns.Countはエクセルシートの一番右の列を意味します。End(xlToLeft)はCtrl+←と同じ動作をします。

Cells(行番号, Columns.Count).End(xlToLeft)

最終行番号・最終列番号の取得

データが入っている最終行番号や最終列番号を整数で取得するには、上述の最終行・最終列の指定に最後に.Rowまたは.Columnと付け加えます。

最終行番号の取得

最後に.Rowを付け加えて、行番号を整数で取得します。

max_row = Cells(Rows.Count, 列番号).End(xlUp).Row

最終列番号の取得

最後に.Columnと付け加えて、列番号を整数で取得します。

max_column = Cells(行番号, Columns.Count).End(xlToLeft).Column

セルの削除

セルを指定し、.Deleteで選択セルを削除します。引数で削除後のシフト方向を上（xlShiftUp）または左（xlShiftToLeft）に指定できます。

単一セルの削除

'A1セルを削除
Cells(1,1).Delete

'A1セルを削除し左方向にシフト
Cells(1, 1).Delete(xlShiftToLeft)

行・列を削除

行・列を削除するにはCellsまたはRangeに.EntireRowまたは.EntireColumnで行全体または列全体を指定して.Deleteで削除できる。

'Rangeで指定した範囲の行全体をを削除します。
Range("A1:C3").EntireRow.Delete

'Rangeで指定した範囲の列全体を削除します。
Range("A1:C3")Entirecolumn.Delete

範囲を削除

'Rangeで指定した範囲を削除します。
Range("A1:C3").Delete

'Rangeで指定した範囲を削除ｓひ、上方向にシフトします。
Range("A1:C3").Delete Shift:=xlShiftUp

シート操作

シートの操作に関する関数です。

シートを削除

シートのインデックス番号またはシート名を指定して.Deleteでシートを削除できます。

'インデックス番号1のシートを削除
Worksheets(1).Delete

'シート名「sheetA」を削除
Worksheets("sheetA").Delete

シートを作成

Worksheets.Addでシートが追加されます。追加直後は追加されたシートがアクティブになっているのでアクティブシートに名前を付けます。

Worksheets.Add
Activesheet.Name = "シート名"

Worksheets.Add After:=Worksheets(Worksheets.Count)

If文

条件で処理を分ける場合には、If文を使います。

真の場合のみ処理を実行する場合

if a <= 10 Then
  処理Ａ
End If

真と偽で処理を分ける場合

If a <= 10 Then
  処理A
Else
  処理B
End If

複数の分岐がある場合

If a<= 10 Then
  処理A
Else If a <=20 Then
  処理B
Else
  処理C
End If

論理演算子（And・Or・Not）

論理演算子（And・Or・Not）を使って条件を記述する場合は以下のように記述します。

And演算子

If a <= 10 And a >= 5
  処理A
End If

Or演算子

If a <= 10 Or a >= 20
  処理A
End If

Not演算子

If Not a <= 10
  処理A
End If

繰り返し処理

処理したい回数があらかじめわかっている場合はFor Nextを使います。回数が不明で条件のみ決まっている場合は、Do While ～ LoopまたはDo ～ Loop Whileを使います。

For ～ Nextで繰り返し処理

下記は0～100まで合計101回の処理Ａが実行されます。

For i = 0 To 100
  処理Ａ
Next

Do While ～ Loopで繰り返し処理

下記はaが10以下の間、繰り返し処理が行われます。aの初期値が10超だと1回も処理されません。

Do While a <= 10
  処理Ａ
Loop

Do ～ Loop Whileで繰り返し処理

下記は処理Ａを最初に1回実行した後、aが10以下の間、繰り返し処理Aが実行されます。

Do
  処理Ａ
Loop While a <= 10

繰り返し処理を途中で抜けるには？

繰り返し処理を抜けるにはExitを使う

Exit For    'For文を抜ける
Exit Do     'Do While Loop文を抜ける

独自関数

独自関数はSub ～ End Subの外で定義し、Sub ～ End Subの中で「Call 関数名」によって呼び出します。

基本

Sub Test
  Call MyFunction
End Sub

Function MyFunction()
  関数の処理内容を記述
End Function

戻り値がある場合

Sub Test
  Dim a As Integer
  a = MyFunction()
End Sub

Function MyFunction() As Integer
  MyFunction = 1
End Function

引数がある場合（値渡しByVal）

関数の定義時に引数の宣言をすることで、関数呼び出し時に引数を渡すことができます。引数をByValで宣言すると関数内で引数の値を変更してももともとの引数の値は変更されません。

Sub Test
  Dim a As Integer
  a = 1
  b = MyFunction(a)
End Sub

Function MyFunction(ByVal arg As Integer) As Integer
  arg = arg + 1  '←ByValの場合、ここではSubプロシージャ内のaは書き換えられません。
  MyFunction = a
End Function

引数がある場合（参照渡しByRef）

参照渡し（ByRef）を指定すると関数内で引数を変更すると、関数内で引数の値を変更するともともとの引数の値が変更されます。

Sub Test
  Dim a As Integer
  a = 1
  b = MyFunction(a)
End Sub

Function MyFunction(ByRef arg As Integer) As Integer
  arg = arg + 1  '←ByRefの場合ここでSubプロシージャ内のaも書き換えらるので注意。
  MyFunction = a
End Function

数値の四捨五入・切り上げ・切り捨て

VBAには四捨五入・切り上げの関数がないためワークシート関数を使用します。

四捨五入

WorksheetFunction.Round(数値, 小数点以下桁数)

切り上げ

WorksheetFunction.RoundUp(数値, 小数点以下桁数)

切り捨て

WorksheetFunction.RoundDown(数値, 少数点以下桁数)

Int(数値)
Fix(数値)

Int(-5.5)    'この結果は-6
Fix(-5.5)    'この結果は-5