VBAプログラミング¶

目次
VBAプログラミング

開発環境¶

ExcelでVBAの作成¶

Excelのファイル形式をマクロ有効ブック（拡張子xlsm）にする
開発タブがリボンになければリボンの設定で開発を追加する
[ファイル]タブ > [オプション] > [リボンのユーザー設定] を選択、[開発]にチェックを付ける
[開発]タブ > [コード]リボン > [Visual Basic] で、「Microsoft Visual Basic for Application」画面が開く
プロジェクトペインに、[VBAProject(ファイル名)]があるので、これを選択し右クリックしポップアップメニューから[挿入] > [標準モジュール]を選択する。
[VBAProject(ファイル名)]の下に[標準モジュール]フォルダが現れ、デフォルトで[Module1]が作成される。コードペインに「Module1(コード)」ウィンドウが現れる。
プロジェクトペイン上で[Module1]を選択し、プロパティペインで[オブジェクト名]欄の値を[Module1]から適切なモジュール名に変更する

コーディング¶

小文字で記述するとチェックになる¶

VBAのコードを見ると、キーワード、標準プロシージャ名、プロパティ名などは大文字で始まるキャメルケースです。
しかし、コーディングの際に小文字で入力し、その行でEnterあるいはカーソルキーで上下の行に移動すると、キーワード等の名前が大文字で始まる名前に書き変わります。これをもってスペルミス等がないことの確認ができます。スペルミスであれば該当するキーワード、プロシージャ、プロパティがないので小文字のままです。

デバッグ¶

デバッグ情報の表示¶

コード中にDebug.Print a, b, c... とデバッグ表示したい要素を指定すると、イミディエイトウィンドウにその要素が表示されます。
イミディエイトウィンドウは、VBA画面の[表示]メニュー > [イミディエイトウィンドウ]で表示します（ショートカットキーは Ctrl + G）。

イミディエイトウィンドウでのFunction実行¶

イミディエイトウィンドウは、Debug.Printの表示だけでなく、Functionの実行も可能です。
Funtion add(ByVal a As Long, ByVal b As Long) As Long の関数を実行するには、イミディエイトウィンドウ上でクエスチョンマークに続き関数名と引数を指定します。

?add(12,24)
36

ローカルウィンドウでの変数表示¶

ローカルウィンドウを表示すると、スコープ内のローカル変数とモジュールの変数の値が確認できます。

変数¶

スコープ¶

関数の中でDimキーワード定義した変数は、その関数の中がスコープとなります。関数が呼ばれる度に初期化されます。

関数の中でStaticキーワード定義した変数は、その関数の中がスコープとなります。但し変数は前回関数実行時の値を継続して使用します。

関数の外（モジュール）にDimキーワードまたはPrivate定義した変数は、そのモジュール内がスコープとなります。

関数の外（モジュール）にPublic定義した変数は、モジュールを跨ったスコープとなります。

変数の種類¶

定数¶

Const INPUT_FOLDER As String = "入力"

オブジェクト（Rangeなど）は定数として定義できません
代入できる式は定数式

変数¶

Dim result As Long

宣言と初期化は同一行ではできません

Function プロシージャ¶

引数を取り、戻り値を返す関数です。

スコープ¶

デフォルトはPublicでモジュール外に公開します。Privateを指定するとモジュール内のみ利用可能となります。

Function プロシージャの定義¶

[Public|Private|Friend] [Static] FunctionName[(arglist)] [As type]
    本体
End Function

Staticは、呼び出しの間でローカル変数が保持される

arglistの書式

[Optional] [ByVal|ByRef] [ParamArray] varname[()] [As type] [=デフォルト値]

例

Function GetLength(alfa As String) As Long
    Dim length
    length = Len(alfa)
    GetLength = length  ' 関数名に戻り値を代入
End Function

Function プロシージャの呼び出し¶

戻り値を取る場合¶

引数を括弧で囲み、関数を変数に代入します。

len = GetLength(message)

戻り値を取らない場合¶

引数を囲む括弧は省略します。

MyProc "Hello, world", 1

プロシージャの戻り値設計¶

複数の値を返したい¶

いくつか方法があります。

Typeで複数の値を持つ構造体を定義して使用
クラスを定義
ByRef引数に値を詰める
Variant配列を返す

ステータスコードとメッセージ文字列を返す例では、

Type Result
    status As Long
    message As String
End Type

を定義して

Function ProcSome() As Result
    Dim res
    res.status = 0
    res.message = "Ok, some" 
    ProcSome = res
End Function

サブルーチン（Sub）¶

引数を取り、戻り値は返さない関数です。
スコープについては、Procedureと一緒です。

呼び出し¶

Call文¶

Call MySubroutine(a, b)

Call文で呼び出すときは、引数を囲む括弧を付けます。

Call文なし¶

MySubroutine a, b

引数のないSubを呼び出すときは、関数名のみになってしまいます。

命名標準¶

自分用整理¶

ハンガリアン記法は使わない

モジュール名　PascalCase
クラス名　PascalCase
関数名　PascalCase
定数名　すべて大文字のSnakeCase
モジュール内変数名　camelCase
ローカル変数名　camelCase
引数名　camelCase
公開変数名　camelCase

逆引きメモ¶

ファイル操作¶

パス文字列からファイル名取得（Dir関数）¶

filename = Dir("C:¥Users¥foo¥bar.txt")

bar.txtを取得します。

ワイルドカードでファイル名を取得（Dir関数）¶

filename = Dir(ThisWorkbook.path & "\処理対象\*.txt")

実行しているExcelブックのあるフォルダの下の"処理対象"フォルダ内にある拡張子.txtに合致するファイルが存在すればそのファイル名を（フォルダ名は付かず）取得します。ファイルが存在しなければ空文字列を返します。合致するファイルが複数あるときは最初の1つ目（拡張子名でソートし、次にファイルベース名でソートし最初の1つ目）のファイル名を取得します。

2つ目以降の合致ファイル名を取得したいときは、上述のDir関数呼び出し後に、繰り返しDir()と引数なしの呼び出しを、結果が空文字列になるまで続けます。

Excelブックを開いたときに自動で処理を実行¶

標準モジュールに`Auto_open()`¶

標準モジュールに、Auto_Open()のサブルーチンを定義します。Excelブックを開いたときに自動で実行します。
複数の標準モジュールがあり、2つ以上のモジュールにAuto_Open()を定義するとエラーになりました。

ThisWorkbokに`Workbook_Open()`¶

VBA画面の左側、[Microsoft Excel Objects] > [ThisWorkbook] をダブルクリックし、タイトルバーのすぐ下の左側ドロップダウンリストを[(General)]から[Workbook]に変更します。すると、その右側のドロップダウンリストが[Open]に自動で変更され、エディタ部分にPrivate Sub Workbook_openN()が生成されます。その中に必要なコードを書きます。

文字列操作¶

文字列中で連続する空白を1つの空白にする¶

ワークシート関数のTrimを使うと、文字列の前後の空白の削除に加えて、文字列の途中にある連続する空白を1つの空白に置き換えます。
WorksheetFunction.Trim(line)

正規表現を扱う¶

    Dim regexp As Object
    Set regexp = CreateObject("VBScript.RegExp")
    With regexp
        .Pattern = "^\d{4}/\d{2}/\d{2}.*$" 
    End With
    Dim isYmd As Boolean
    isYmd = regexp.test(text)

日時・時刻¶

現在日時・時刻の取得¶

いずれも、ローカル時刻

現在の日時を得る関数 Now
現在の日付を得る関数 Date
現在の時刻を得る関数 Time

日付・時刻の計算¶

今の日時（Now関数）から8時間後の日時
AddDate("h", 8, Now)

データの格納¶

データの型¶

整数¶

Byteは8bit符号無し整数
Integerは16bit符号付き整数（-32,768～32,767）
Longは32bit符号付き整数（約-20億～+20億）

浮動小数点数¶

Singleは単精度浮動小数点数
Doubleは倍精度浮動小数点数

日付・時刻¶

Date型を使います。日付、時刻、または日付と時刻を入れることができます。
文字列表記をDate型に代入（暗黙の変換）することができます。

Dim theDay As Date
theDay = "2018/1/11"

配列¶

サイズ固定の配列、サイズが変更できる配列があります。後者は動的配列と言われます。

配列の次元数を取得する¶

素直に取得する方法がないので、次元を指定してその次元の要素上限数を取得するUBound関数を、エラーがでるまで次元値を増やして呼び続けます。

Public Function dimensionsOf(ByVal data As Variant) As Integer
    Dim i As Integer
    Dim temp As Variant
    On Error Resume Next
    Do while Err.Number = 0
        i = i + 1
        temp = UBound(data, i)
    Loop
    dimensionsOf = i - 1
End Function

Err.Numberは、エラーが発生したときのエラー番号が格納されます。0はエラーが発生していないことを示します。
UBoundの第1引数は配列、第2引数は配列の次元を指定します。

Collection¶

Excel標準のコレクションで、キーも割当可能ですが、後述のDictionaryに比べて機能（メソッド）が少なく貧弱です。可変長配列として使う用途では便利ですが、キーと値の組を扱うなら後述のDictionaryを使うのがよいです。

キーのリスト、値のリストが取得できない等

Dictionary（連想配列、マップ）¶

Officeアプリケーションに同梱される Microsoft Scripting Runtime ライブラリに含まれます。
Dictionaryは、キーと値のペアを集合として保持するデータ構造です。他のプログラミング言語では、連想配列やマップと呼ばれることもあります。

Dictionaryオブジェクトの生成¶

次の2つの方法があります。

CreateObject("Scripting.Dictionary")
「Microsoft Visual Basic for Application」画面を開き、[ツール]メニュー > [参照設定]から、[Microsoft Scripting Runtime]にチェックを付けた上で
New Dictionary

セルの指定¶

RangeとCells¶

C5セルを指定する方法は2つあります。

Range("C5")
Cells(5, 3)
なお、Cells(5, "C")も可

Rangeは、セルの名前でセルを指定します。Cellsは、行番号と列番号でセルを指定します。VBAの変数でセルを指定したい場合は、Cellsを使うことになります。

Rangeでセルの範囲指定¶

Range("B2:C5")
Range("B2", "C5")
Range(Cells(2, 2), Cells(5, 3))

CurrentRegionでセルの範囲指定¶

指定したセル（1つ）が含まれる一塊のセル範囲（空白行、空白列で区切られた範囲）を返します。
Cells(1,1).CurrentRegion

Excelのシート上であるセルを選択し、Ctrl+Shift+* で選択される範囲と同じ。

CurrentRegionの範囲から見出しを除くには¶

	A	B	C
1	見出し	見出し	見出し
2	データ	データ	データ
3	データ	データ	データ
4	データ	データ	データ

となっているセルから、見出しを除いてデータだけを範囲とするには

Dim data As Range
Set data = Cells(1, 1).CurrentRegion
Set data = data.Offset(1, 0).Resize(data.Rows.Count - 1, data.Columns.Count)

と1行オフセットしてリサイズします。行方向に1つオフセットするので、リサイズ後の行サイズを1つ少なくします。

エラー処理¶

エラーメッセージを表示してマクロを終了¶

    Err.Description = "集計中に回復不能なエラーが発生しました。" 
    Err.Raise (3201)

Functionの戻り値にエラーを返す¶

Functionの戻り値型をVariant型とし、正常なときはその値を、エラーのときはエラー値を返します。

Function searchSomething(ByVal name as String) As Variant
    Dim ret As Variant
    ret = CVErr(xlErrNA)  ' #N/Aエラー（数値）からCVErr関数でエラー値作成（Variant型にのみ代入可）
    On Error Resume Next  ' 次のMatchは該当が0件のとき実行時エラーとなるためエラー時は次に進む指定
    ret = WorksheetFunction.Match(name, Range("A1:A100"))
    If IsError(ret) Then  ' エラー値かどうか判定
        searchSomething = ret
    Else
        searchSomething = Range(Cells(ret, 1), Cells(ret, 5))
    End If
End Function

外部プログラムの実行¶

Excel VBAから、外部プログラム（コマンド）を実行するときに、WScript.Shellオブジェクトを使用します。

Run¶

WScript.Shellオブジェクトを作成し、Runメソッドで指定したコマンドを実行します。コマンドを実行するターミナル（コマンドプロンプト）画面を表示するか否かを指定できます。コマンドラインのコマンドを実行させるとき、Execではターミナルが表示されてしまいますが、Runではターミナルを表示するかどうか指定できます。
Runは呼び出したコマンドが終了するのを待機することも、コマンドを呼び出した後終了を待たずに次の処理に進むかを指定できます。
Execでは、コマンドの標準出力・標準エラー出力を読み取ることができますが、Runではサポートされていません。
Runでの代替手段は、コマンド実行時にリダイレクトを使って　標準出力や標準エラー出力をファイルに落としてそれを読み出します。

Dim wsh As Object
Dim secretKey As String
Dim cmd As String
Dim ret As Long

Set wsh = CreateObject("WScript.Shell")
secretKey = Environ("USERPROFILE") & "\.ssh\id_ed25519" 
cmd = "scp -i " & secretKey & " C:\data\data1.bin mine@example.com:/data/" 

' 第1引数に実行するコマンドライン
' 第2引数にウィンドウ表示状態（0:非表示）
' 第3引数にコマンド完了待ち（True:完了待ち、False:非同期）
ret = wsh.Run(cmd, 0, True)

If ret = 0 Then
    MsgBox "転送成功しました", vbInformation
Else
    MsgBox "転送エラー発生：" & ret, vbCritical
Endif
Set wsh = Nothing

標準出力をリダイレクトしコマンドの出力を取得¶

Dim tempFile As String
Dim fso As New FileSystemObject
Dim content As String

tempFile = Environ("TEMP") & "\scptemp.txt" 
    :
cmd = cmd & ">" & tempFile & "2>&1" ' 標準エラー出力を標準出力へ送り、標準出力をファイルへリダイレクト
    :
If fso.GetFile(tempFile).Size > 0 Then
    With fso.GetFile(tempFile).OpenAsTextStream
       content = .ReadAll
       .Close
    End With
End If

注意点¶

数値計算の注意点¶

整数の計算でオーバーフロー¶

次の計算はオーバーフローとなってしまいます。

Dim result As Long
result = 24 * 3600

24と3600は整数（Integer型）の即値、Integer型は16bit符号付きなので、-32,768～+32,767が有効範囲です。24 * 3600 は有効範囲を超えるのでオーバーフローとなります。左辺値型がLongでも、まず右辺値がIntegerで計算しようとするのでオーバーフローです。
回避策：24 * 3600# と#を付けると3600がLong型として扱われます。また、3600!と、!を付けると単精度浮動小数点（Single型）として扱われます。

変数代入の注意点¶

オブジェクト型の代入はSetがないとエラー¶

次のマクロがエラーとなってしまいました。

Dim alfaBook As Workbook
alfaBook = Workbook.Add

実行すると、「実行時エラー'91': オブジェクト変数またはWithブロック変数が設定されていません。」

オブジェクト型（Workbook型）の代入には、set命令が必要です。

- alfaBook = Workbook.Add
+ Set alfaBook = Workbook.Add

Functionプロシージャの戻り値がオブジェクト型の場合もSetが必要

プロシージャの注意点¶

プロシージャ呼び出しで引数を括弧で囲むとエラー¶

戻り値を使用しないプロシージャ呼び出しでオブジェクト型の引数を括弧で囲むとエラーが発生します。
次のマクロがエラー（実行時エラー438 オブジェクトは、このプロパティまたはメソッドをサポートしていません）となりました。

Dim arg As Workbook
  :
workbookをほげる (arg)

引数がStringなどの非オブジェクト型のときは括弧を付けていてもエラーにはなりません。
エラーを回避する方法は、括弧を外すか、Call を付けるかでした。

workbookをほげる arg
Call workbookをほげる (arg)

Callを付けたときは引数の括弧は省略できません。

24日前に更新

ファイル (1)

プロジェクト

全般

プロフィール

ソフトウェアエンジニアリング

最近更新されたページ¶

タグクラウド¶

タグ¶

Wiki

VBAプログラミング¶

開発環境¶

ExcelでVBAの作成¶

おすすめ設定¶

自動構文チェックを外し、変数の宣言を強制する¶

エディタの色付け¶

コーディング¶

小文字で記述するとチェックになる¶

デバッグ¶

デバッグ情報の表示¶

イミディエイトウィンドウでのFunction実行¶

ローカルウィンドウでの変数表示¶

変数¶

スコープ¶

変数の種類¶

定数¶

変数¶

Function プロシージャ¶

スコープ¶

Function プロシージャの定義¶

Function プロシージャの呼び出し¶

戻り値を取る場合¶

戻り値を取らない場合¶

プロシージャの戻り値設計¶

複数の値を返したい¶

サブルーチン（Sub）¶

呼び出し¶

Call文¶

Call文なし¶

命名標準¶

自分用整理¶

逆引きメモ¶

ファイル操作¶

パス文字列からファイル名取得（Dir関数）¶

ワイルドカードでファイル名を取得（Dir関数）¶

Excelブックを開いたときに自動で処理を実行¶

標準モジュールにAuto_open()¶

ThisWorkbokにWorkbook_Open()¶

文字列操作¶

文字列中で連続する空白を1つの空白にする¶

正規表現を扱う¶

日時・時刻¶

現在日時・時刻の取得¶

日付・時刻の計算¶

データの格納¶

データの型¶

整数¶

浮動小数点数¶

日付・時刻¶

配列¶

配列の次元数を取得する¶

Collection¶

Dictionary（連想配列、マップ）¶

Dictionaryオブジェクトの生成¶

セルの指定¶

RangeとCells¶

Rangeでセルの範囲指定¶

CurrentRegionでセルの範囲指定¶

CurrentRegionの範囲から見出しを除くには¶

エラー処理¶

エラーメッセージを表示してマクロを終了¶

Functionの戻り値にエラーを返す¶

外部プログラムの実行¶

Run¶

標準出力をリダイレクトしコマンドの出力を取得¶

注意点¶

数値計算の注意点¶

整数の計算でオーバーフロー¶

変数代入の注意点¶

オブジェクト型の代入はSetがないとエラー¶

プロシージャの注意点¶

プロシージャ呼び出しで引数を括弧で囲むとエラー¶

標準モジュールに`Auto_open()`¶

ThisWorkbokに`Workbook_Open()`¶