VBA (Visual Basic for Applications)

Visual Basic for Applications (VBA)とは、マイクロソフトのオフィススイートである Microsoft Office に搭載されているプログラミング言語です。基本的な文法はVisual Basicと同じですが、Microsoft Officeを操作するための拡張が施されています。

目次

1. 組込み関数
2. 1. DateAdd
   2. DateSerial
   3. Format
   4. InStr
   5. IsDate
   6. IsEmpty
   7. IsNumeric
   8. LBound
   9. Mid
   10. MsgBox
   11. Randomize
   12. Rnd
   13. Trim
   14. Split
   15. Open
   16. UBound

コメント

シングルクォート(')から行末まではコメントとなる。コメントは、ソースプログラムに注釈を付けるために使用される。コメントはプログラムの実行には影響しない。

アンダースコア(_)で行が続く場合は、その続きもコメントとなる。

Dim id As Integer ' 識別子

' 識別子の初期化
id = 1

グローバル変数

VBAでグローバル変数を定義するには、標準モジュールの[(General)]-[(Declarations)]でPublic宣言する。

Public iSheet As Worksheet

Debug.Print

VBAでは、Debug.Printでデバッグ用の情報をイミディエイトウィンドウに出力することができる。

Debug.Print "message"

If

条件を満たした場合にのみ、ある処理を実行させたいときは、If文を使用します。

Dim i As Integer
i = 1
If i > 0 Then
    Debug.Print "Positive"
End If

条件を満たした場合に実行する処理に加えて、条件を満たさなかったときに実行する処理も指定することができます。

Dim i As Integer
i = 1
If i > 0 Then
    Debug.Print "Positive"
Else
    Debug.Print "Non-positive"
End If

条件を満たさなかった場合にのみ処理を実行させたいときは、条件の前に Not 句を付けます。

Dim i As Integer
i = 1
If Not i > 0 Then
    Debug.Print "Non-positive"
End If

複数の条件をすべて満たした場合にのみ処理を実行させたいときは、複数の条件を And 句で繋げます。

If 条件1 And 条件2 Then
  条件1と条件2の両方を満たしたときに実行する処理
End If

複数の条件のうち、いずれかひとつでも満たした場合に処理を実行させたいときは、複数の条件を Or 句で繋げます。

If 条件1 Or 条件2 Then
  条件1か条件2のいずれかでも満たしたときに実行する処理
End If

Select Case

ある変数の値を調べて、その値によって実行する処理を変えたい場合は、Select Case文を使用します。

Select Case varname

Case value1
    ' varnameの値がvalue1のときに実行する処理

Case value2, value3, value4
    ' varnameの値がvalue2、value3又はvalue4のいずれかのときに実行する処理

Case value5 To value7
    ' varnameの値がvalue5からvalue7の間のときに実行する処理

Case Is > value8
    '  varnameの値がvalue8より大きいときに実行する処理

Case Is < value9
    '  varnameの値がvalue9より小さいときに実行する処理

Case Else
    ' varnameの値が上記のいずれでもないときに実行する処理

End Select

複数の値をカンマで区切って並べることができます。

Case 4, 5, 6, 7

最小値から最大値の間の範囲を表すためにToを使用することができます。

Case 4 To 7

Do Until

条件を満たしている間、ある処理を繰り返し実行させるには、Do文またはDo While文を使用します。

i = 0
Do
  i = i + 1
Loop Until i < 10

Do文とDo While文のどちらも条件を満たしている間は処理を繰り返し実行しますが、Do文とDo While文は条件を判定するタイミングが異なります。

Do文は、まず繰り返し処理を実行してから条件の判定を行います。つまり、最初から条件を満たさなくても、最低1回は繰り返し処理を実行します。

条件を満たさなくなったら繰り返し処理を止めますが、Exit Do文を用いて強制的に繰り返しを止めさせることもできます。

Do While

Do While文は、まず条件の判定をして、条件を満たせば繰り返し処理を実行します。つまり、最初から条件を満たさなければ、繰り返し処理を1度も実行しません。

i = 1
Do While i < 10
  i = i + 1
Loop

For

ある処理を一定の回数繰り返して実行させたい場合、Do文やDo While文を使うことで実現することができます。たとえば、Do While文を使って、処理を10回繰り返す例を次に示します。

i = 1
Do While i <= 10 Then
  sum = sum + i
  i = i + 1
Loop

このように、ある一定の回数繰り返して処理を実行することは多いので、For文という専用の構文が用意されています。

For 変数名 = 初期値 To 最大値
  繰り返し処理
Next 変数名

For文では、繰り返し処理を行う前に、変数に初期値を代入します。処理を繰り返す（Next句に達する）たびに、この変数の値には1が加わります。変数の値が最大値を超えると、処理を繰り返すのを止めます。

For文を使って、処理を10回繰り返す例を次に示します。

For i = 1 To 10
  sum = sum + i
Next i

Exit For

Forループの内部でForから抜け出すには、Exit For文を使用する。

For i = 1 To 10
  sum = sum + i
  If i > 5 Then
    Exit For
  End If
Next i

With...End Withステートメント

With...End Withステートメントを使用すると、指定オブジェクトに対して一連のステートメントを実行できる。

Worksheets("MySheet").Cells(1, 1).Value = "first"
Worksheets("MySheet").Cells(1, 2).Value = "second"
Worksheets("MySheet").Cells(1, 3).Value = "third"

上記のソースコードは、次のように記述することもできる。

With Worksheets("MySheet")
  .Cells(1, 1).Value = "first"
  .Cells(1, 2).Value = "second"
  .Cells(1, 3).Value = "third"
End With

On Error GoTo

COMクライアントをVisual Basicで作成した場合、COMサーバでエラーが発生するとVisual Basicの例外メカニズムがエラーメッセージを表示します。これは実行時エラーとして扱われるため、ダイアログボックスを表示した後、プログラムは終了してしまいます。

これを防ぐには、On Errorステートメントでエラー発生時の例外処理を記述して、自前でエラーハンドリングを行わなければなりません。

Private Sub foo()
  On Error GoTo ErrorHandler
  ・・・
  ' COMサーバの機能を利用する
  obj.Advise(pCallBack)
  ・・・
  Exit Sub
ErrorHandler:
  ' エラー時の処理
  MsgBox Err.Description, 0, "ユーザ固有のエラー処理"
  Exit Sub
End Sub

Err

Errオブジェクトは実行時エラーに関する情報を格納している。ErrオブジェクトのDescriptionプロパティには、エラーに関連する説明の文字列が格納されている。

Date

現在の日付と時刻を取得するには、Date関数を使う。

Sub Example()
  MsgBox Date
End Sub

Format関数で日付と時刻を任意のフォーマットに変換することができる。

Sub Example()
  MsgBox Format(Date, "yyyymmdd")
End Sub

Now

現在の時刻を取得するには、Now関数を使う。

Sub Example()
  MsgBox Now
End Sub

UBound

VBAで配列の長さ（添字の最大値）を取得するには、Ubound関数を使う。

配列の要素が1つの場合、UBound関数は0を返す。要素がない場合、UBound関数は-1を返す。

Public Function UBound(
  ByVal array As System.Array,
  Optional ByVal rank As Integer = 1
) As Integer

array

対象となる配列

rank

添字の最大値を取得する配列の次元

Sub uboundExample()
  Dim array As Variant
  Dim i As Integer
  array = Array("野中ここな", "白鳥沙南", "佐藤愛桜", "野崎結愛")
  For i = 0 To UBound(array)
    Cells(i + 1, 2) = array(i)
  Next
End Sub
  

VBのプログラムからDLLの関数を呼ぶ

VBのプログラムからDLLの関数を呼ぶためには、DLL関数の宣言を行う必要がある。

戻り値がある関数の場合：

戻り値がない関数の場合：

FuncName

DLL関数の名前

DllName

DLLのファイル名

ArgList

引数のリスト

Type

DLL関数の戻り値のデータ型

Visual BasicのプログラムからDLLの関数を呼び出す例を次に示す。

Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)

'（中略）

Sleep(500)

Class_Initialize

クラス・モジュールのコードを表示するウィンドウのドロップダウン・リストボックスから [Class] を選択すると、[Initialize] および [Terminate] が選択できるようになる。

Initializeを選択すると、PrivateなSubプロシージャClass_Initializeが追加される。これは、このクラスがNewされたときに実行されるプロシージャである。

たとえば、クラス MyClass にClass_Initializeプロシージャを作成しておくと、MyClassをNewしたときにClass_Initializeプロシージャが自動的に呼び出される。クラスの初期化処理などは、Class_Initializeプロシージャ内で行うとよい。大抵はメンバー変数の初期化などを行う。

Private mIsConnected As Boolean

Private Sub Class_Initialize()
    mIsConnected = False
End Sub

Dim mc As MyClass

' Class_Initializeが呼び出される
Set mc = New MyClass

Class_Terminate

クラス・モジュールのコードを表示するウィンドウのドロップダウン・リストボックスから [Class] を選択すると、[Initialize] および [Terminate] が選択できるようになる。

Terminateを選択すると、PrivateなSubプロシージャClass_Terminateが追加される。これは、オブジェクトが破棄されるときに実行されるプロシージャである。

Property

クラス・モジュールにプロパティを含めることができる。

プロパティを参照できるようにするために、クラス・モジュールにProperty Getプロシージャを作成する必要がある。

Private mStartTime As Date

Property Get StartTime As Date
  StartTime = mStartTime
End Property

Dim mc As MyClass
Set mc = New MyClass
Form1.txtStartTime.Text = mc.StartTime

プロパティプロシージャとは、プロパティを参照または設定するためのプロシージャである。

プロパティを設定できるようにするには、Property Letプロシージャを作成する。

Private mName As String

  Public Property Let Name(ByVal Name As String)
      mName = Name
  End Property

Dim mc As MyClass

  Set mc = New MyClass

  mc.Name = "Terminal1"

Cells.Clear

Microsoft Excel のセルを行番号と列番号で指定するには、Cells オブジェクトを使用する。セル内容を削除するには、Clear を呼び出す。

Cells.Clearメソッドを呼び出す構文を次に示します。

Cells(行番号, 列番号).Clear

セルのデータを削除するVBAソースコードの例を次に示します。

Cells(1,2).Clear

Cells.Value

Cells.Valueプロパティに値を代入する構文を次に示します。

Cells(行番号, 列番号).Value = 値

対象となるワークシートを明示的に指定する場合の構文を次に示します。

Worksheets("シート名").Cells(行番号, 列番号).Value = 値

行番号と列番号は共に1から始まります。

セルにデータを入力するVBAソースコードの例を次に示します。

Cells(1,2).Value = "VBA"
Cells(2,2).Value = 12.3

他のワークシートのセルのデータを参照することもできます。

Cells(1,2).Value = Worksheets("Sheet2").Range("A1").Value

Collection.Add

キーと項目のペアをコレクションに追加する。VBAのDictionaryオブジェクトとは異なり、Collectionのキーは大文字と小文字を区別しない。

Dim collect As Collection
Set collect = New Collection
collect.Add "value1", "key1"
collect.Add Item:="value2" key:="key2"

Collection.Remove

Dim collect As Collection
Set collect = New Collection
collect.Add "value1", "key1"
collect.Remove "value1"

Collection.Clear

Dim collect As Collection
Set collect = New Collection
collect.Add "value1", "key1"
collect.Clear

Collection.Count

Dim collect As Collection
Dim count As Integer
Set collect = New Collection
collect.Add "value1", "key1"
count = collect.Count

Collection.Contains

Dim collect As Collection
Set collect = New Collection
collect.Add "value1", "key1"
If collect.Contains("key1") Then
  ' 処理
End If

Collection.Item

指定したキーに対応する項目を返す。VBAのDictionaryオブジェクトとは異なり、Collectionのキーは大文字と小文字を区別しない。

Dim collect As Collection
Set collect = New Collection
collect.Add "value1", "key1"
MsgBox collect.Item("key1")

正規表現

VBAで正規表現を使うにはRegExpオブジェクトを利用する。RegExpを使用するためには、事前に次の参照設定を行う必要がある。

1. Visual Basic for Applicationsの「ツール」メニューから「参照設定」をクリックする。
2. 「Microsoft VBScript Regular Expressions 5.5」にチェックを入れる。
3. 「OK」ボタンをクリックする。

RegExpオブジェクトとは、正規表現を表すオブジェクトである。

Dim re As RegExp

Set re = New RegExp
re.Pattern = "[0-9]"
MsgBox re.Test("0123")

ADO (ActiveX Data Objects)

Visual BasicでADOアプリケーションを作成するには、参照設定を行う必要がある。

[プロジェクト] メニューから [参照設定] を選択すると、「参照設定 - プロジェクト名」ダイアログが表示される。リストの中から「Microsoft ActiveX Data Objects x.x Library」を選択して、[OK] ボタンをクリックする。

データベースへの接続は ADODB.Connection.Open メソッドで行う。

接続に必要なパラメーターは、ADODB.Connection.ConnectionString プロパティに接続文字列を指定する。

Dim cn As ADODB.Connection

Set cn = ADODB.Connection
cn.ConnectionString = "DSN=testds;UID=okai;PWD=chisato"
cn.Open

接続時に指定するパラメーターがデータソース名およびユーザID、パスワードのみのときは、ADODB.Connection.Open メソッドの引数で指定することもできる。

Dim cn As ADODB.Connection

Set cn = ADODB.Connection
cn.Open "testds", "okai", "chisato"

ADOプログラムの例を次に示す。

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = ADODB.Connection
Set rs = ADODB.Recordset

cn.ConnectionString = "DSN=testds;UID=okai;PWD=chisato"

cn.Open
rs.Open "select * from emptable", cn

Do Until rs.EOF
  Debug.Print rs!empid rs!empname
  rs.MoveNext
Loop

rs.Close
cn.Close

Dir()

フォルダ内にあるすべてのファイル名を取得する例を示す。

Dim buf As String
Dim cnt As Long

buf = Dir("C:\foo\*.xlsx")
Do While buf <> ""
  cnt = cnt + 1
  buf = Dir()
Loop

Range.Value

Range.Valueプロパティに値を代入する構文を次に示します。

Range("A1セル名").Value = 値

対象となるワークシートを明示的に指定しなければ、現在アクティブなワークシートが対象となります。対象となるワークシートを明示的に指定する場合の構文を次に示します。

Worksheets("シート名").Range("セル名").Value = 値

セルにデータを入力するVBAソースコードの例を次に示します。

Range("A1").Value = "Excel"
Range("A2").Value = 2000

Range.Valueプロパティの場合、ひとつのセルだけでなく、セル範囲を指定してデータを入力することもできます。

セルA1からA9までの範囲にデータを入力するVBAソースコードの例を次に示します。

Range("A1:A9").Value = "VBA"

セルに入力するデータとして、他のセルのデータを参照することもできます。

Range("A1").Value = Cells(1,2).Value

他のワークシートのセルのデータを参照することもできます。

Range("A1").Value = Worksheets("Sheet2").Range("A1").Value

複数のセルをまとめてコピーする

セル範囲を指定して複数のセルをまとめてコピーすることができる。

Range("A1:C1").Value = Range("A2:C2").Value

Range.AutoFilter()

Range.AutoFilter() はオートフィルタを使ってリストにフィルタをかけるメソッドである。

Function AutoFilter( _
  Optional Field As Variant, _
  Optional Criteria1 As Variant, _
  Optional Operator As XlAutoFilterOperator, _
  Optional Criteria2 As Variant, _
  Optional VisibleDropDown As Variant = True, _
) As Variant

Field

フィルタの対象となるフィールド番号を整数で指定する。

Criteria1

抽出条件となる文字列

Operation

フィルタの種類をXlAutoFilterOperatorクラスの定数のいずれかで指定する。

Criteria2

2番目の抽出条件となる文字列を指定する。

VisibleDropDown

Trueを指定すると、フィルタ処理されるフィールドのオートフィルタのドロップダウン矢印を表示する。

Range.Clear()

Range.Clearメソッドの場合、ひとつのセルだけでなく、セル範囲を指定してデータを削除することもできます。

Range.Clearメソッドを呼び出す構文を次に示します。

Range("セル名").Clear

セルA1からA9までの範囲のデータを削除するVBAソースコードの例を次に示します。

Range("A1:A9").Clear

特殊フォルダ

デスクトップやマイドキュメント等の特殊フォルダのパスをVBAから取得するには、WScript.Shell オブジェクトの SpecialFolders メソッドを使う。

Dim wsh as object
Dim path as String

Set wsh = CreateObject("WScript.Shell")
path = wsh.SpecialFolders("Desktop")

次に示す特殊フォルダのパスを取得できる。