Excelマクロを書くためのVBA(Visual Basic for Application)

Excelマクロ書くことになったので勉強した内容のメモ。

動作をマクロに記録

ツール -> マクロ -> 新しいマクロの記録

で手作業の動作をマクロに記録できる。マクロの内容はVBAとして書き出されるので何かやりたいことがあったら手動記録して出来上がったVBAソースコードを見ると参考になる。

マクロの実行

  • Alt + F8 でマクロウィンドウを開いて、マクロ名を選択して実行する。
  • Alt + F8 でマクロウィンドウを開いて、マクロ名を選択 -> オプション -> ショートカットキーの設定
  • 表示 -> ツールバー -> フォーム でフォーム部品のウィンドウが開くのでボタンを設置 -> 押下
  • 表示 -> ツールバー -> Visual Basic -> コントロールツールボックス -> コマンドボタンを設定 -> 押下

VBA の文法

コメントとステートメント

' コメントはシングルクオート
Sub ClearSelectedContents
  ' 行末はセミコロンではなく改行
  Selection. _ ' 文を2行以上にしたい場合は行末にアンスコ
    ClearContents
End Sub

制御構文

' if
If 条件式 Then
  
ElseIf 条件式 Then
  
Else
  
End If

' switch
Select Case 
  Cass 0
    
  Case 1, 2, 3
    
  Case Is < 10
    
  Case Is >= 100
    
  Case Else
    
End Select

' for
For ループ制御変数 = 123 To 456
  
Next

' while
While 条件式
  
Wend

' do while
Do
  
Loop While 条件式

' goto
Goto LabelName
LabelName:

' 例外処理
Sub SubName
  On Error GoTo ErrorLabelName
  エラーが発生しうる文
  Exit Sub

ErrorLabelName:
  エラーハンドリング
  Errオブジェクトからエラー内容を取得可能
End Sub

ループを抜ける break に当たるのは ExitExit Do, Exit For, Exit Function, Exit Sub... のように書く。

演算子

気になった演算子だけ羅列。

  • & : 文字列の結合。+ でも結合可能(&は文字列型に変換して結合)
  • ^ : 指数演算
  • Like : 文字列簡素なパターンマッチ(Booleanに評価される)
  • != が使えないので <> を使う
  • &&, || は使えないので And, Or を使う
  • Mod : %
  • and, or, not, xor : ビット演算子

プロシージャ(関数)定義

  • 戻り値のない関数は Sub, 戻り値のある関数は Function で定義する。
  • デフォルト引数は Optional : Optional parameter as long = 123
  • VBの引数はデフォルトで参照渡し(Javaなどはプリミティブ型は値渡しだけどVBではそういうのも全部参照渡し)だが、ByVal キーワードで値渡しも可能。ByRef キーワードもあるけどデフォルトが ByRef なので使わなくていい。(VB.NETByVal がデフォルトで ByVal の省略も可能)

    ' Sub 関数名 - End Sub で戻り値の無い関数定義
    Sub ClearSelectedContents
    ' 行末はセミコロンではなく改行
    ' 関数の呼び出しの後ろに () は不要(付けると構文エラー)
    ' つまり以下の文は Selection の ClearContens 関数呼び出し
    Selection.ClearContents
    ' AlertHogeHoge を実行すると "hogehoge" と表示される
    ' Public or Private で可視性の指定が可能
    Public Sub AlertHogeHoge()
    ' これは変数定義
    Dim result As String
    
    ' Functionプロシージャの呼び出し
    result = FuncName("hoge")
    
    ' Subプロシージャの呼び出し
    ' Call Alert(result) でも同じ
    Alert result
    End Sub
    
    ' Function は戻り値ありのプロシージャ
    ' 戻り値は末尾の " As 型" の部分
    Private Function FuncName(arg As String) As String
    ' プロシージャ名に値を渡すとこれが戻り値となる
    FuncName = arg & arg
    End Function
    
    ' Sub は戻り値なしのプロシージャ
    Private Sub Alert(message As String)
    MsgBox message
    End Sub

SubFunction で引数指定の方法が異なる。

変数, 定数, 型など

  • 基本的な型 : Byte, Boolean, Integer, Long, Single, Double, Currency, Date, Object, String, Variant
  • 型は as で指定 : parameter as Type
  • Variant 型 : プリミティブ系の型 あるいは その配列を保持できる不思議な型。めんどくさそうなので使わないほうが良さげ。型を省略した変数や引数は全て Variant 型として扱われてしまうので省略しないように。
  • String 型 : 文字列は不変型ではなく可変長型らしい…文字列毎にインスタンスが生成される。
  • Option Explicit : これを書いておかないと変数宣言なしで変数が使える。つまり書け。
  • Dim で変数を、Const で定数を、 Static でローカル静的変数を定義できる。
  • 変数時にオブジェクト型のものを代入する場合は Set キーワードが必要

    Option Explicit
    
    ' 大域変数
    Public publicVar As String
    Private privateVar As String
    
    ' 大域定数
    Public Const const1 As String = "const1"
    Private Const const2 As String = "const2"
    
    Public Sub TestSub()
    Const localConst As String = "ローカル定数"
    
    ' ローカル変数
    Dim localVar As String
    localVar = localConst
    
    ' ローカル静的変数
    Static staticVar As String
    staticVar = staticVar + localVar
    
    MsgBox staticVar
    End Sub
    Sub Macro1()
    ' 文字サイズを指定可能
    Dim str As String * 3
    str = "abcde"
    MsgBox str ' "abc"
    
    Dim test As String
    test = "abc"
    Repeat2 test
    MsgBox test ' "abcabc"
    End Sub
    
    Sub Repeat2(target As String)
    target = target & target
    End Sub

(静的)配列とコレクション

' 一次元配列
Sub Macro1()
  Dim result(3) As Integer
  result(0) = 3
  result(1) = 6
  result(2) = 9
  MsgBox result(0) & result(1) & result(2) ' "369"
End Sub

※配列の要素数は定数でなければ指定できないが、Redim ステートメントで要素数を変更可能

' 二次元配列
Sub Macro1()
  Dim result(2, 2) As Integer
  result(0, 1) = 1
  result(1, 0) = 2
  MsgBox result(0, 0) & result(0, 1) & result(1, 0) ' "012"
End Sub
' コレクション
  Dim map As Collection
  Set map = New Collection
  ' Dim map As New Collection の1行でも同じ…
  ' かと思ったけど違うらしい。
  ' 参考: https://msdn.microsoft.com/ja-jp/library/3fxtxxwa(v=vs.80).aspx

  map.Add "Item1", "Key1"
  map.Add "Item2", "Key2"
  map.Add "Item3", "Key3"

  ' インデックスが 1 から始まるとかやめて下さい…
  MsgBox map(0 + 1)
  MsgBox map.item(1 + 1)
  MsgBox map("Key3")
  MsgBox map.Count

  map.Remove (1 + 1)

  Dim item
  For Each item In map
    MsgBox item
  Next item

Enum

Private Enum MyEnum
  MyEnum1
  MyEnum2 = 25
  MyEnum3
End Enum

Public Sub TestSub()
  MsgBox MyEnum1 & MyEnum2 & MyEnum3 ' "02526"
End Sub

Type : ユーザー定義型(構造体)

Private Type MyType
  id As String
End Type

Sub Macro1()
  Dim hoge As MyType
  hoge.id = "hoge"
  MsgBox hoge.id
End Sub

クラス

Visual Basic Editor メニューの 挿入 -> クラスモジュール でクラスを追加。 クラス宣言みたいなものはなくてプロパティでクラス名を指定。 覚えるべき文法はプロパティプロシージャくらいしかない。あと Friend (C#internalにあたるもの。C++friendとは違う)

Option Explicit

Private mMember As String

' ゲッター
Public Property Get Member() As String
  member = mMember
End Property

' セッター
Public Property Let Member(member As String)
  mMember = member
End Property

' 初期化イベントプロシージャ
Private Sub Class_Initialize()
  mMenber = "none"
End Sub

' 破棄イベントプロシージャ
Private Sub Class_Terminate
End Sub

※基本的には localVariable = Nothing などとしなくても循環参照がなければ問題なく解放される。

その他

メモ

  • MsgBox : アラートウィンドウやプロンプトウィンドウを表示。
  • InputBox : 文字入力可能なプロンプトウィンドウを表示。 MsgBoxInputBox ではできないようなものはユーザーフォームを利用する。VBA記述5-4
  • WorksheetFunction : エクセルの関数が使える
  • イベントハンドラ : auto_open, auto_close
  • Range("selected range")
    範囲を取得。 Range(***).Select で範囲を選択。
    選択範囲は "A1"(1セル) や "B5:F10"(複数セル) のようなフォーマットとなる。
  • Range("A1").Value
    A1に入力されている値を取得
  • ActiveCell.FormulaR1C1 = "HOGE"
    現在アクティブなセルに HOGE という文字列を入れる
  • ActiveCell.Offset(y, x)
    現在のセルの位置から x(→), y(↓) 座標ずれた位置を取得
  • Selection
    選択範囲のオブジェクト
  • Selection.AutoFill
    選択範囲のオートフィルを実行
  • Selection.FormulaR1C1 = "HUGA"
    選択範囲内のセル全てに HUGA という文字列を入れる
  • Selection.ClearContents
    選択範囲の数式と値を消去
  • Selection.MergeCells = false
    選択範囲のセルの結合を解除
  • Sheet
    シートの操作を行うオブジェクト
  • Sheet.Add
    新しいシートを追加してActiveにする。
  • Workbooks
    ワークブックの操作を行うオブジェクト
  • Workbooks.Add
    新しいワークブック(エクセルのファイル)を作成して開く
  • ActiveSheet
    現在選択中のシートの操作を行うオブジェクト
  • ActiveWorkbook
    現在選択中のワークブックの操作を行うオブジェクト

参照設定 と CreateObject

参照設定したくない場合は CreateObject を使えばインスタンス生成できるけど Object 型としてしか扱えず、補完が効かないのでかなりメンドイ

MSXML

  • 生成 : Set xml = New MSXML2.DOMDocument / CreateObject("MSXML2.DOMDocument") でもOK
  • ファイルパスからXML読み込み Call xml.Load(filePath)
  • ファイルパスからXML書き出し Call xml.Save(filePath)
  • 要素名でXML要素を取得 xml.getElementsByTagName(elementName) 戻り値の型は IXMLDomElement
  • XPathで要素を取得 xml.selectSingleNode(XPath)
  • ノードのディープコピー xml.clonseNode(true, IXMLDOMNode **cloneRoot) 第1引数が true だとディープコピーとなる。
  • XML要素の値のセット・ゲットは Text プロパティ
  • まぁあとは appendChild だとか createElement だとか

三項演算子?

存在しないけど IIf 関数で代用すれば OK (ただし IIf は三項演算子と違い短絡評価ではない)

VB .Net には三項演算子もあるのだけれど…

Share