VBA よく使うメソッドとプロパティ

VBAでよく使うメソッドとプロパティをまとめておきます

エラー解決関係

データ型の確認

TypeName() と VarType() というプロパティを使います

( )内にデータ型を確認したい値を入力すると

下記の値を取得します

データ型	TypeName	VarType
Int型	Integer	2
Double型	Double	5
文字列	String	8
ブール値	Boolean	11
日付値	Date	7
オブジェクト	Object	9
バリアント※1	Empty	0
Str配列	Str()	8200
Int配列	Integer()	8194

バリアント※1: 初期化前の状態です
初期化後は、代入された値に応じて出力が変わります
Empy と 0 は、初期化されていないという意味です

関数や変数の定義を確認

定義を確認したい関数や変数にカーソルがあたっている状態で、Shift + Fn + F2
これで、宣言している箇所へ移動します

また、Ctr + Shift + Fn + F2 で、移動する直前の位置へ戻ります

データ処理関係

リストオブジェクトの作成

追加したい表を選択した状態で下記処理を実行


    
        (シート名).ListObjects.Add XlListObjectHasHeaders:=xlYes

リストオブジェクトの削除

データも削除されるので、コピーをとっておく


    
        (シート名).ListObjects(1).Delete

添字は、1 始まり

リストオブジェクトのメンバ

メンバ	解説
HeaderRowRange	ヘッダー部
DataBodyRange	ボディー部
ListColumns	列のコレクション
ListRows	行のコレクション

特定の列のデータを取得



    Dim MyRange as Range

    Dim MyStr as Variant


    
    Set MyRange =  (シート名).ListObjects(1).ListColumns('列の名前').DataBodyRange


    
    For Each MyStr In MyRange


    
        Debug.Print MyStr


    
    Next

特定の行のデータを取得


    
        Dim MyRange as Range

        Dim MyStr as Variant


        
        Set MyRange = (シート名).ListObjects(1).ListRows(インデックス).Range


        
        For Each MyStr In MyRange


        
            Debug.Print MyStr


        
        Next

インデックスは、1始まり

ユーザーフォーム関係

ユーザーフォームをモードレス表示する

Workbook_Openなどの中に次のコードを記述


    
        (ユーザーフォーム名).Show vbModeless

ユーザーフォームを閉じる(非表示)

CommandButtonのクリック時などに次のコードを記述


    
        Unload Me

コンボボックスのリスト設定

UserForm_Initializeなどに次のコードを記述


    
        With (コンボボックス名)


    
        Dim lists As Variant:   lists = (シート名).ListObjects(1).ListColumns("見出し名").DataBodyRange


        
        .List = lists

        .ColumnCount = 1

        .BoundColumn = 1

        .TextColumn = 1


    
    End With

※注意：引用したいシートには、ListObjects が作成されていることが前提条件となります

コントロールの順序を確認/変更

ユーザーフォームを選択した状態で、表示 → タブオーダーを選択するとコントロールの順序を確認/変更できます

キーボード操作関係

キーバードを操作するためには、 SendKeys というプロパティを使います。

例えば、SendKeys "%" を実行すると、”alt” 押下できます

“%” の箇所を別のコードにすると、他のキーも押下できます

主なキーとそのコード（他にもたくさんあります）

キー	コード
A	“A”
AB	“AB”
alt	“%”
tab	“{TAB}”
shift	“+”
shift + tab	“+({TAB})”
↓	“{DOWN}”

実際の使う場面としては、エクセルの機能を呼び出す時にも活躍します
例えば、下記のマクロを作成して呼び出すと


Public Sub renzokudata()



SendKeys "%"

SendKeys "H"

SendKeys "FI"

SendKeys "S"

SendKeys "+({TAB})"

SendKeys "+({TAB})"

SendKeys "{DOWN}"

SendKeys "O"



End Sub

“連続データ” を入力する機能を一瞬で表示することができます

日頃からよく使うエクセルの機能を呼び出す時に設定しておくととても便利です！

配列関係

文字列を指定文字列で分割

例えば、”C:\Users\nobby\folder1″ という文字列を、”\” で分割する場合、Split というプロパティを使用します


Dim MyPath As String

MyPath = "C:\Users\nobby\folder"



Dim MyList As Variant

MyList = Split(MyPath,"\")



Debug.Print MyList(UBound(MyList))

フルパスから末端のフォルダ名を抽出するときに使えます
上記のコードでは、”folder1″ が取得され表示されます

UBound(配列)で、この配列の最後の要素を取得しています

二次元配列

二次元配列を使用する主な目的として、予め代入した値を呼び出す（読み取り用）
と
データのサイズに合わせて配列のサイズを決定し、各要素に値を代入する（書き込み用）があります

読み取り用と書き込み用の二次元配列は、宣言、代入、要素の数の求め方などが異なります

読み取り用

配列として宣言するときは、バリアント型で宣言します
Arrayを使って、値を代入します

Dim MyList As Variant



MyList = Array(Array("月", "火", "水", "木", "金", "土", "日"), _

                        Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"))



Dim i, j As Long



For i = 0 To UBound(MyList)



For j = 0 To UBound(MyList(0))



Debug.Print MyList(i)(j)



Next



Next

書き込み用

配列を宣言するとき、変数名の後ろに ( ) を付けます
ReDimで、配列の大きさを再宣言します

Dim MyList() As String



ReDim MyList(1, 6) As String



MyList(0, 0) = "月"

MyList(0, 1) = "火"

MyList(0, 2) = "水"

MyList(0, 3) = "木"

MyList(0, 4) = "金"

MyList(0, 5) = "土"

MyList(0, 6) = "日"



MyList(1, 0) = "Mon"

MyList(1, 1) = "Tue"

MyList(1, 2) = "Wed"

MyList(1, 3) = "Thu"

MyList(1, 4) = "Fri"

MyList(1, 5) = "Sat"

MyList(1, 6) = "Sun"



Dim i, j As Long



For i = 0 To UBound(MyList)



For j = 0 To UBound(MyList, 2)



Debug.Print MyList(i, j)



Next



Next

再宣言時の数値（上記の 1 や 6）は、添え字の最大値となります
要素数ではないので注意！

ファイル操作関係

ファイルを開く

Openメソッドを使います

Dim MyBook As Workbook

Set MyBook = Workbooks.Open([開きたいブックのパス], ReadOnly:=True, UpdateLinks:=False)

ReaOnly:=True
ブックを読み取り専用で開きます
他の人が開いていると、処理の途中でメッセージが表示され処理が中断されます
それを回避します

UpdateLinks:=False
外部参照(リンク)の更新をオフにします
対象のブックが外部参照していると更新するかどうか、処理の途中でメッセージが表示され処理が中断します
それを回避します

ファイルを閉じる

Closeメソッドを使います

MyBook.Close SaveChanges:=False

Set MyBook = Nothing

SaveChanges:=False
ブックを閉じる際に上書き保存しないようにしています

Set MyBook = Nothing
ブックを開くときに、Set MyBook = … としています
必要な処理が終わったら、Set MyBook = Nothing として、オブジェクトの参照を解除します

Application関係の設定

処理の直前で下記を追加しておくとその処理が速くなります

Application.Cursor = xlWait
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Application.Cursor = xlWait
マウスポインタ―の形状を（砂時計型ポインターに）設定します
マクロVBA実行中にカーソルが頻繁にちらつくような場合は、この設定により処理速度が向上します

Application.EnableEvents = False
新たなイベント発生を停止します

イベント：Worksheet_Changeなどシートやマウス操作などをトリガーに発火するもの

Application.DisplayAlerts = False
メッセージを非表示にします

Application.Calculation = xlCalculationManual
計算モードを手動に設定ます

Application.ScreenUpdating = False
画面表示の更新を止めます
画面更新にかかる時間が不要になりその分だけ処理が速くなります
ブックを開く場合は表示されなくなりチラツキがなくなります

処理の最後は、下記を記述して設定を元に戻しておきます

Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.Cursor = xlDefault
Application.ScreenUpdating = True

処理時間の測定

処理の前後に下記のコードを記述することで、処理時間を測定することができます

処理の直前

Dim startTime, endTime, processTime As Double


startTime = Timer

処理の直後

endTime = Timer

processTime = endTime - startTime



Dim ln, lm As Long

ln = processTime

lm = Int(ln / 60)



MsgBox (lm & "分" & Format(ln - lm * 60, "00") & "秒")