はじめに
- マクロとは、複雑なコンピュータの操作を自動化するための技術の総称。
- VBAとは、Visual Basic for Applicationsの略で、Microsoft Officeシリーズ専用のプログラミング言語。
- (Microsoft Officeシリーズの世界では)マクロで記録した操作はVBAで記述される。
以下の手順でやると軽いコストで自動化を実現できるのでオススメ!
- 「ひとまずやりたいことをGUI操作で実行し、マクロ記録する」
- 「マクロで記録したVBAをいじる」
VBAによる自動化とは、つきつめれば条件分岐と繰り返し処理
基礎、前提知識
マクロVBAのエディタ→VBE
VBEに標準モジュールを挿入し、標準モジュール内にマクロVBAを記述していく
Excel×VBA
ソートや検索などはわざわざ配列を用意して複雑なループ処理を実装するのは、処理速度的にも実装工数的にもナンセンス。
VBAの変数はループを回す際のカウンタやデータ交換時の仮置き場くらいの使い方にとどめておいたほうがいろんな意味で合理的♪
開発前準備
シンタックスハイライトは設定しておいたほうがいい。
基本
i行j列のセルを指定する |
Cells(i, j) |
複数のセルを指定する | Range(Cells($i_1$, $j_1$), Cells($i_2$, $j_2$)) |
行全体を指定する(i行目) | Rows(i) |
列全体を指定する(j列目) | Columns(j) |
セルで計算式や関数を使う① |
Cells(i, j).Value <brCells(i, j).Formula
|
変数の宣言 | Dim |
定数の宣言 | Const |
関数の種類
関数タイプ | 内容 | 使用例 |
---|---|---|
ワークシート関数 | 通常のExcelの関数(セルに関数として入力して使うヤツらのこと) (例) SUM 、MIN 、MAX
|
Appplication.Min(Range("A1:B7")) |
標準関数 | 組み込み関数(ワークシート関数との違いがよくわからん、、) (例) ABS 、ROUND
|
|
ユーザー定義関数 |
Sub 、Function などを使って自分で作成した関数 |
よく使う標準関数
文字列関係
Val | 文字列の先頭にある数字を数値に変換する |
Cstr | 指定した値を文字列型(String)に変換する |
Replace | 文字列の指定文字を置換する |
Trim | 文字列の前後の空白ごみ(全半角とも)を取り除く |
StrConv | 文字列を大文字/小文字/全角/半角に統一する |
Mid | 文字列の指定位置から指定文字数を取り出す |
Left | 文字列の左(先頭)から指定文字数を取り出す |
Right | 文字列の右(末尾)から指定文字数を取り出す |
Format | 指定した書式に変換する |
Len | 文字列の長さ(文字数)を調べる(Byte数を調べたいときはLenB ) |
日付関係
Weekday | 指定した日付の曜日を数値で取得する |
Year | 指定した日付の年を取得する |
Month | 指定した日付の月を取得する |
Day | 指定した日付の日を取得する |
Hour | 指定した時刻の時を取得する |
Minute | 指定した時刻の分を取得する |
Second | 指定した時刻の秒を取得する |
IsDate | 指定した値を日付型(Date )に変換できるかを判定する |
Now | 現在の日時を取得する |
DateDiff | 2つの日付の期間(差分)を取得する |
数値計算関係
Int | 実数の小数点以下を切り捨てる 四捨五入したいとき→ Int(k + 0.5)
|
Fix | 単純切り捨て |
Sqr | 平方根 |
Sin | 正弦。サイン |
Cos | 余弦。コサイン |
Tan | 正接。タンジェント。 |
Atn | 逆三角関数。アークタンジェント? |
Abs | 絶対値 |
^ | べき乗 |
Mod | 剰余 |
セルの指定
Range
とCells
があるが、変数を使ってセルを指定する場合はよっぼどの事がない限りCells
を使う。
行全体の指定
列全体の指定
計算式
Values、Formula、FormulaR1C1などのプロパティを使用する。
Valuesプロパティ
setアクセサ | で関数を入れることができる |
getアクセサ | 計算結果が取得できる |
Formulaプロパティ
setアクセサ | 関数を入れることができる |
getアクセサ | 計算式が取得できる |
FormulaR1Caプロパティ
setアクセサ | |
getアクセサ |
アクティブとセレクト
アクティブ○○:操作対象の○○。常に1つ。
セレクト○○:XXXXX。複数同時に存在し得る。
逆順Forループ
Forループを逆順で回したいときは以下のようにする。
For i = 10 To 1 Step -1
~何かしらの処理~
Next i
「行/列/グラフetcを削除するとき」などはインデックスのずれを防ぐために逆順で処理するほうが賢いポイント高いよ♪
値渡しと参照渡し
値渡しと参照渡し。突き詰めると奥が深いが、ひとまず簡単に以下の理解でOK
値渡し | 値変更を呼び出し元に反映しない | ByVal |
参照渡し | 関数内での値変更を呼び出し元に反映する | ByRef |
高速化手法
- 余計な処理を止めておく
- ソートはExcel上で行う
- RangeではなくCellsを使う
- オートフィルタを使う(不要行を高速に削除する)
- 1行ずつVBAで処理するより、セル範囲に一括で計算式を入れ、その後に値貼り付けする
- ワークシート関数を使う
- 配列を利用する
オートフィルタとは
ベンチマークテストのやり方(処理時間の図り方)
' ↓ Windows起動からの経過時間をミリ秒単位で取得する関数を宣言
Declare Function GetTickCount Lib "kernel32.dll" () As Long
Sub Macro1()
stTimer = GetTickCount
~測定したい処理~
endTimer = GetTickCount
Debug.Print "経過時間 = " & (endTimer - stTimer) & "msec"
End Sub
余計な処理を止めておく
画面更新、自動再計算、シートイベントなどを停止した状態で処理を実行する。
(シートイベントについては諸説あるっぽい?
もっと深く理解してから使用したほうがいいかも。。)
Application.ScreenUpdating = False ' 画面更新停止
Application.Calculation = xlCalculationManual ' 手動計算にする
Application.EnableEvents = False 'シートイベントを無効
~処理~
Application.EnableEvents = True 'シートイベントを有効
Application.Calculation = xlCalculationAutomatic ' 自動計算にする
Application.ScreenUpdating = True ' 画面更新再開
ワークシート関数を使う
VLOOKUP
Function FindByVLOOKUP(ByVal target, _
ByVal stCol As String, _
ByVal edCol As String, _
ByVal targetCol As Long, _
Optional ByVal isPerfectMatch As Boolean = True)
On Error Resume Next
FindByVLOOKUP = WorksheetFunction.VLookup(target, _
Columns(stCol & ":" & edCol), _
targetCol, _
isPerfectMatch)
If Err.Number <> 0 Then MsgBox "見つかりませんでした。"
On Error GoTo 0 'エラー処理ルーチンを無効化する(以降の処理でエラーが無視されなくする)
End Function
COUNTIF
結果 = WorksheetFunction.CountIf(検索範囲, 検索条件)
配列を利用する
どうしても処理に時間がかかってしまう場合は
プログレスバーを表示する
デバッグ方法
マクロ実行してエラーが出た画面で「デバッグ」ボタンをクリックすると、エラーとなった行が黄色表示され、その時の変数状態も確認できる。
エラートラップ
エラーを検知するコードを記述するための仕組み。(VBAにはTry~Catchのような例外処理を実装する仕組みがないので自分で検知するしかない)
On Error Resume Next 'エラートラップの構文(何らかの実行エラーが発生しても処理を止めないでエラーが起きた行を無視する)
Err.Clear '以前のエラーはあらかじめクリアしておく
~エラーが出そうな処理~
If Err.Number <> 0 Then 'Err.Numberが0以外の時は前述の処理内でエラーが発生している
~エラーが出た時の処理~
End If
On Error GoTo 0 'エラートラップの解除
使い方含めあまりピンときていないので、勉強進めて分かったら追記します。
イミディエイトウィンドウ
コンソールウィンドウ的な感じで出力できる
○○したいとき集
グラフ作成
積み上げ縦棒グラフ:xlColumnStacked
100%積み上げ縦棒グラフ:xlColumnStacked100
特定のフォルダ内のファイルをループする
DirPath = "フォルダパス"
FileName = Dir(DirPath + "¥") '最初のファイル名を取得
Do While FileName <> ""
~ファイル名を使った何かしらの処理~
FileName = Dir '次のファイル名を取得
Loop
CSVファイル入出力
CSVファイル入力(読み込み)
Sub macro1()
Dim a(256) As String
s = InputBox("キーワードを入力してください。")
If Trim(s) = "" Then Exit Sub 'キーワードが空なら終了
fPath = InputBox("ファイルパスをフルパスで入力してください。")
If Trim(fPath) = "" Then Exit Sub 'ファイルパスが空なら終了(存在チェックもしたいところ)
Open fPath For Input As #5
j = 1
Do While Not EOF(5)
Line Input #5, buf
If buf Like "*" & s & "*" Then
Call wReadCSV(buf, a, n)
For i = 1 To n
Cells(j, i).Select
ActiveCell.FormulaR1C1 = a(i)
Next i
j = j + 1
End If
Loop
Close #5
MsgBox j - 1 & "件のデータを取り込みました。"
Range("A1").Select
End Sub
'csv読み込み本処理
'bufbuf:1行分の文字列データ、aa():分割した文字列データを入れた配列、num:分割数
Sub wReadCSV(ByVal bufbuf As String, aa() As String, num, Optional ByVal delimiter As String = ",")
Dim char1 As String
num = 1
aa(num) = ""
For n = 1 To Len(bufbuf)
char1 = Mid(bufbuf, n, 1)
Select Case char1
Case delimiter 'デリミタのとき
num = num + 1
aa(num) = ""
Case Chr(34) '○○のとき
Case Else
aa(num) = aa(num) & chart1
End Select
Next n
End Sub
CSV出力(書き込み)
大容量のデータファイルを扱う場合、Excelにそのすべてを取り込んでから選別/加工/削除などをしようとすると、処理時間の増大や動作停止のリスクなどのデメリットがある。
→マクロで必要なデータを選んでからExcelに取り込むようにすると高パフォーマンスを発揮してくれる。
テキストファイル入出力
テキストファイル入力(読み込み)
テキストファイル出力(書き込み)
下記コードはcsv形式の書き込みとなっています。
Sub Macro2()
sDirPath = InputBox("出力先フォルダをフルパスで入力してください。")
If Trim(sDirPath) = "" Then sDirPath = ThisWorkbook.Path
sFileName = InputBox("出力ファイル名を入力してください。")
If Trim(sFileName) = "" Then Exit Sub
If sDirPath.EndsWith("\") = False Then sDirPath = sDirPath & "\"
sFilePath = sDirPath & sFileName
Open sFileName For Append As #6 'Append(追加書き出し)モードでオープン
Call wWriteTxtFile
Close #6
End Sub
Sub wWriteTxtFile()
n = Cells(Rows.Count, "A").End(xlUp).Row
m = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To n
tmp = ""
For j = 1 To m
tmp = tmp & Cells(i, j) & ","
Next j
Print #6, tmp
Next i
End Sub
書き出す文字列形式の指定にはFormat
を使いましょう。(というか使い勝手が良くて便利です。)
Word×VBA
T.B.D.
PowerPointl×VBA
T.B.D.
Outlook×VBA
メール一斉送信
単語集
BASIC言語 | 基本的にインタープリタ型の言語で、行単位に記述された命令文が逐次実行されるようになっている。 このため、プログラムの書き換えも容易である。 ただし、速度向上のためにコンパイラ型言語として開発されたBASICの処理系もある。 |
アプロケーション | 誤植? |
VBA索引
<> |
不等号 |
:= |
引数に値を設定する http://officetanaka.net/excel/vba/beginner/02.htm |
#(番号) |
ファイル番号 |