VBA 手習い |
作成日： 2010-06-19 最終更新日： |
VBA とは、Visual Basic for Applications の略である。 この意味は、Visual Basic という言語を Microsoft 社のアプリケーション、 たとえば Excel や Word に適用できるようにした言語である。
それでどこが謎かというと、Excel VBA に関して言えば、 2次元のセルが入力でもあり、出力でもあり、また記憶領域でもあるということだ。 また、単純な文字列操作からVBAまで、できることが複雑かつ膨大であり、 どこまでできるのかがわからない。これも私にとっての謎である。
Excel で仕事を効率化したいというときに登場するのが、 関数であったり、マクロであったり、VBA であったりする。 概念を整理しよう。 まず、関数というのは、 1つのセルに埋め込まれた式であり、 計算の順序は問わないもの、言い換えれば即時に計算がすみ、終了するものだ。 正確にはワークシート関数と呼ばれる。 一方マクロは、1つのセルだけなく、複数のセルやシート全体、ブック全体、 はては複数のブックにまでまたがって計算を順序立てて行うものである。 そして VBA は、マクロを記述するための文法である。 VBA で使われる関数は VBA関数と呼ばれ、ワークシート関数とは区別される。 VBA は、アプリケーションの自動化や効率化を進める上で有用な道具である。
余談だが、 VBA で思い出すのは Tcl や JavaScript だ。 もともと、アプリケーションに埋め込んで自動化に使うために設計されたのが、 Tcl 言語である。そのアプリケーションとして広まったのが Tk であるが、 私は昔、あるアプリケーションを利用していたとき、そのアプリの操作言語が Tcl だった。 また、JavaScript は Web ブラウザというアプリケーションの操作言語として名を高めた。
さて余談はともかく、ワークシート関数編とマクロ編、 それからどちらにも入らない、あるいはどちらにも入る Excel 編を少しずつメモする。
文字列の切り取りはいつも悩む。 UNIX のコマンドと混同して、最初からは head , 最後からは tail かな、とか begin と end だったかな、とかなかなか出てこない。 mid は覚えているが(それでも middle と混同しそうだ)、 left と right という感覚が思い出せない。 横書き文化にまだ慣れていないということか。 おまけに、アラビア文字のように右から左に文字を続ける場合はどうなるのか。
文字列の最初から文字数だけ切り取る
文字列の指定位置 から文字数だけ切り取る
文字列の終わりから文字数だけ切り取る
例
A1 セルに次の文字列が入っているとする。
"ちょいと一杯のつもりで呑んで"
left(A1, 4) ' ちょいと
mid(A1, 5, 7) ' 一杯のつもりで
right(A1, 3) ' 呑んで
これから書く。
今まで書いていたピボットテーブルについては、 ピボットテーブル手習いにまとめました。
@jitte の日記 (http://d.hatena.ne.jp/jitte/20081111/p1) で、@jitte さんが、 「Excelで既に表になってるデータをピボットテーブル用に並べ替える」と題して VBA のプログラムを掲げている。
簡素で読みやすいが、私の趣味で少し書き換えた。これは、
下記の正規化されていない表を
| title | X | Y | Z | W |
| A | 1 | 2 | 3 | 4 |
| B | 5 | 6 | 7 | 8 |
| C | 9 | 10 | 11 | 12 |
下記のように正規化する
| A | X | 1 |
| A | Y | 2 |
| A | Z | 3 |
| A | W | 4 |
| B | X | 5 |
| B | Y | 6 |
| B | Z | 7 |
| B | W | 8 |
| C | X | 9 |
| C | Y | 10 |
| C | Z | 11 |
| C | W | 12 |
VBA のプログラムである。使用時には、正規化したいところをあらかじめ選択しておく。
Sub PivotData()
Dim shtsrc As Worksheet ' コピー元のシート
Dim sht2 As Worksheet ' コピー先のシート
Dim rngsrc As Range ' コピー元の範囲
Dim rng2 As Range ' コピー先の範囲
Dim colsrc As Integer, rowsrc As Integer ' コピー元のセル位置
Dim col2 As Integer, row2 As Integer ' コピー先のセル位置
Set rngsrc = Selection 'Range
Set shtsrc = rngsrc.Worksheet
Set sht2 = ActiveWorkbook.Worksheets.Add
' コピー先をアクティブにしておく
sht2.Activate
' コピー先の行位置を現在使用中の範囲の次にする
row2 = sht2.UsedRange.row + sht2.UsedRange.Rows.Count
For rowsrc = rngsrc.row + 1 To rngsrc.row + rngsrc.Rows.Count - 1 ' 行ごとのコピー
For colsrc = rngsrc.column + 1 To rngsrc.column + rngsrc.Columns.Count - 1 '列ごとのコピー
CopyAsLink shtsrc.Cells(rowsrc, rngsrc.column), sht2.Cells(row2, 1) '行タイトル
CopyAsLink shtsrc.Cells(rngsrc.row, colsrc), sht2.Cells(row2, 2) '列タイトル
CopyAsLink shtsrc.Cells(rowsrc, colsrc), sht2.Cells(row2, 3) 'データ
row2 = row2 + 1
Next colsrc
Next rowsrc
' １秒後にコピー元をアクティブに戻す
Application.Wait Now + TimeValue("0:00:01")
shtsrc.Activate
' コピーモードを解除
Application.CutCopyMode = False
End Sub
Sub CopyAsLink(rngsrc As Range, rngdst As Range)
rngsrc.Copy
rngdst.Select
rngdst.Worksheet.Paste Link:=True
End Sub
A1 から E1 までのセルの内容を結合して F1 に表示したい、というときの VBA プログラムである。 結合したい範囲を選択すると、その範囲のすぐ右のセルに結合した結果を上書きする。 結合時の区切り文字 delimiter は バックスラッシュ ` で決め打ちした。
なお、この項と次の項は下記を参考にした。
Sub ConcatinateData()
Dim buf() As String
Dim rng As Range
Dim row As Long
Dim column As Long
Const delimiter = "`"
Set rng = Selection
ReDim buf(rng.Columns.Count)
For row = 1 To rng.Rows.Count
For column = 1 To rng.Columns.Count
buf(column) = rng(row, column)
Next column
rng(row, column) = Join(buf, delimiter)
Next row
End Sub
もとの表
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | お勧めの本 | まりんきょ | 2013/03/13 13:49:04 | こんにちは。 | どんな本がお勧めですか。 | |
| 2 | 私の本 | プラトン | 2013/03/14 01:23:00 | こんばんは。 | 私が書いた「ソクラテスの弁明」をお勧めします。 | |
| 3 | 日本では | 松下竜一 | 2013/03/15 05:24:17 | おはようございます。 | 私の「豆腐屋の四季」や「砦に拠る」もいいですよ。 |
結果
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | お勧めの本 | まりんきょ | 2013/03/13 13:49:04 | こんにちは。 | どんな本がお勧めですか。 | ` お勧めの本` まりんきょ` 2013/03/13 13:49:04` こんにちは。` どんな本がお勧めですか。 |
| 2 | 私の本 | プラトン | 2013/03/14 01:23:00 | こんばんは。 | 私が書いた「ソクラテスの弁明」をお勧めします。 | ` 私の本` プラトン` 2013/03/14 01:23:00` こんばんは。` 私が書いた「ソクラテスの弁明」をお勧めします。 |
| 3 | 日本では | 松下竜一 | 2013/03/15 05:24:17 | おはようございます。 | 私の「豆腐屋の四季」や「砦に拠る」もいいですよ。 | ` 日本では` 松下竜一` 2013/03/15 05:24:17` おはようございます。` 私の「豆腐屋の四季」や「砦に拠る」もいいですよ。 |
上とは逆に、ある文字で区切られた１つのセル内容を、 区切り記号を指定して B1 から F1 までにばらして表示したい。 これを実現するための VBA スクリプトを用意した。 範囲を選択すると、その範囲のすぐ右のセルに結合した結果を上書きする。 区切り文字 delimiter は前項と同様、 ` として決め打ちしている。
Sub SeparateData()
Dim buf As Variant
Dim rng As Range
Dim row As Long
Dim column As Long
Const delimiter = "`"
Set rng = Selection
For row = 1 To rng.Rows.Count
buf = Split(rng(row, 1), delimiter)
For column = LBound(buf) + 1 To UBound(buf)
rng(row, column + 1) = buf(column)
Next column
Next row
End Sub
ファイルの読み込みには古典的な方法と FSO(File System Object) による方法がある。 後者は準備が必要だ。 以下は、Microsoft Office XP での対応である。 Offiece 2003 でも同様なことがあるらしい。
Excel で VBE (Visual Basic Environment, Alt + F11 で開く) のプルダウンメニューに、 [ツール] がある。 上から順に、参照、その他のコントロール、マクロ、オプション、VBA プロジェクトのプロパティ、デジタル署名、 とある。そのうち[参照]がグレーアウトされている（ディム化されている）で選択できないことがある。 この場合には Excel 本体の プルダウンメニューの[ツール]→マクロ→セキュリティを選ぶと、 [セキュリティ]というダイアログウィンドウが出る。その中の[信頼のおける発行元]タブを選ぶと、 二つの選択肢のうちの下に[Visual Basic プロジェクトへのアクセスを信頼する]がある。 このチェックがオフになっているはずなので、これをオンにする（マウスで□をクリックするとレ印がはいる）。 この状態で保管し、再度 VBE の ツールを見てみると、[参照]が選択できるようになっている。 早速選択するとあまりにも多い。その中で、Microsoft Scripting Runtime にチェックをすればよい。
具体的な読み込み方法は、次と合わせて説明する。
正規表現を使う場合もやはり、準備が必要だ。参照の選択までは上記と同様である。 ただし選ぶのは「Microsoft VBScript Regular Expressions 5.5」だ。
Lotus Notes （以下、ノーツ）は、情報共有化のツールとして一世を風靡した。 今でも使われているところがあるが、新しいシステムとのつながりが悪く、 データの変換に頭を悩ませているところが多い。 ここでは、ノーツの書き出しのフォーマットとして知られている Structured Text 形式のテキストデータを、 Excel で取り込める形式にする VBA を紹介する。
ノーツの Structued Text 形式を示したいが、公式文書が見当たらない。引用できないことをお詫びする。 私の実例でいえば、たとえばこのようなものだ。
title: お勧めの本 sender: まりんきょ date: 2013/03/13 13:49:04 greeting: こんにちは。 content: どんな本がお勧めですか。 ^L title: 私の本 sender: プラトン date: 2013/03/14 01:23:00 greeting: こんばんは。 content: 私が書いた「ソクラテスの弁明」をお勧めします。 ^L title: 日本では sender: 松下竜一 date: 2013/03/15 05:24:17 greeting: おはようございます。 content: 私の「豆腐屋の四季」や「砦に拠る」もいいですよ。
ここで^L は改ページ（ラインフィード）の制御文字、すなわち 12 を表す。
これでわかるように、１フィールドは改行文字で、１レコードは改ページで区切られている。 AWK、Perl、Pyoton、Ruby などのスクリプト系の言語を使えばすぐ CSV 形式には変換できそうだが、 VBA でやることを考えると次のように面倒になる。
下記のサンプル VBA は、上記の FSO と正規表現モジュールを使っている。
Option Explicit
' テキストファイル読み込みサンプル(FSO)
' 参照設定：Microsoft Scripting Runtime
Sub Read_TextFile()
Const title = "テキストファイル読み込み処理"
Const filter = "全てのファイル (*.*),*.*"
Dim FSO As New FileSystemObject ' FileSystemObject
Dim TS As TextStream ' TextStream
Dim rec As String ' 読み込んだレコード内容
Dim row As Long ' 格納セルの行
Dim column As Long ' 格納セルの列
Dim xlAPP As Application ' Applicationオブジェクト
Dim vnt As Variant ' ファイル名受取用
Dim filename As String ' 変換後のファイル名
Dim RS As String ' レコードセパレーター
Dim re_title As RegExp ' コロンより前は標題
Dim re_content As RegExp ' コロンより後は内容
' Applicationオブジェクト取得
Set xlAPP = Application
' ｢ファイルを開く｣のダイアログでファイル名の指定を受ける
xlAPP.StatusBar = "読み込むファイル名を指定して下さい。"
vnt = xlAPP.GetOpenFilename(FileFilter:=filter, title:=title)
' キャンセルされた場合はFalseが返るので以降の処理は行なわない
If VarType(vnt) = vbBoolean Then Exit Sub
filename = vnt ' ヴァリアント型から文字列型への変換
RS = Chr(12) ' ^L 、すなわちラインフィード (LF) のコントロール文字
Set re_title = New RegExp
Set re_content = New RegExp
' 最初のコロンより前をタイトル、後を内容として正規表現する
re_title.Pattern = ":.*$"
re_content.Pattern = "^[^:]*:" ' : のあとの空白も除く "^[^:]*:\w* がよいかも
' 指定ファイルをOPEN(入力モード)
Set TS = FSO.OpenTextFile(filename, ForReading)
' １行目だけは標題と内容の両方が必要
column = 1
rec = TS.ReadLine
Do While rec <> RS
' MsgBox re_title.Replace(rec, "") '
' MsgBox re_content.Replace(rec, "") '
Cells(1, column).Value = re_title.Replace(rec, "")
Cells(2, column).Value = re_content.Replace(rec, "")
rec = TS.ReadLine
column = column + 1
Loop
row = 3
column = 1
' ２行目からはファイルのEOF(End of File)まで繰り返す
Do Until TS.AtEndOfStream
' 改行までをレコードとして読み込む
rec = TS.ReadLine
If (rec = RS) Then ' ページフィード文字であれば次のレコードとみなす
column = 1
row = row + 1
Else
' ２行目以降は内容のみでＯＫ
' MsgBox re_content.Replace(rec, "") '
Cells(row, column).Value = re_content.Replace(rec, "")
column = column + 1
End If
Loop
' 指定ファイルをCLOSE
TS.Close
Set TS = Nothing
Set FSO = Nothing
End Sub
結果の Excel 表は次の通り。
| title | from | date | greeting | content |
| お勧めの本 | まりんきょ | 2013/03/13 13:49:04 | こんにちは。 | どんな本がお勧めですか。 |
| 私の本 | プラトン | 2013/03/14 01:23:00 | こんばんは。 | 私が書いた「ソクラテスの弁明」をお勧めします。 |
| 日本では | 松下竜一 | 2013/03/15 05:24:17 | おはようございます。 | 私の「豆腐屋の四季」や「砦に拠る」もいいですよ。 |
Yahoo!知恵袋で、ユーザーフォームに関する質問があった（https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1126566081 ) 。
私が補足した形で以下引用する。
Excel のユーザーフォームにはチェックボックスがある。このボックスは、オフの状態では□のような白い四角形となっていて、
この四角形をクリックするとオンとオフが切り替えられることがわかっている。オンのときの表示は☑となっているが、
レ点だけでは見づらく、両者の区別がつきづらい。
オフからオンにマウスクリックで切り替えるとき、チェックボックスを黒い四角形■のように塗りつぶす方法があるか。
もちろんオンからオフにするときには白い四角形に戻し、マウスを四角形の上でクリックするたび反転させたい。
この回答も私が補足して以下引用する。
チェックボックスの標準仕様では実現できないので、チェックボックスの代わりにラベルとマクロを組み合わせで実現する方法を述べる。
以下は Excel 2010 以降の方法である。
開発→挿入→Active X コントロールのラベルをクリックし、作りたい位置に好きな大きさでラベルを作成する。
クリック時にカーソルが＋になるので左上から右下へドラッグアンドドロップすればラベルが作成できる。
このとき、開発のリボンを見るとデザインモードアイコンの背景が変わっているのがわかるはずだ。これはデザインモードが自動的にオンの状態になっていることを表す。
さて、作成したラベルを右クリックし、プロパティ(I)→左欄のBorderStyleの右欄クリック→右端の▼をクリックして[1-fmBorderStyleSingle]を選択する。
さらにその下のCaptionの右欄の文字[Label1]をドラッグで選択してキーボードの[Delete]で削除する。
この状態で、プロパティのウィンドウは右上の☒で閉じる。
次に今しがた作ったらラベルを右クリックすると、下のようなコードの表示が出る（注：ラベルの作成順でLabelの後の数字は変わる）。
Private Sub Label1_Click() End Sub
その行間に下記コードをコピー＆ペ―ストする。なお、下記コードのLabelの後の数字は最初の行に合わせ修正のこと、
Select Case Label1.BackColor Case vbWhite Label1.BackColor = vbBlack Case Else Label1.BackColor = vbWhite End Select
終わったらVBEditorウィンドウを右上の☒で閉じる。そして、デザインモードアイコンをクリックし、デザインモードをオフにする。 あとは作成したラベルの上でマウスのクリックを繰り返し、オンの■とオフの□が交互に変わることを確かめる。
この方法は、ラベルが少ないうちはいいが、ラベルが増えてくるとそのたびに同じようなコードを書く必要があり煩わしい。 そこで、共通操作は一つのサブルーチンにまとめ、ラベルごとの関数は１行で済むように変更する。
Private Sub Label1_Click()
Call toggle(Me.Label1)
End Sub
Private Sub Label2_Click()
Call toggle(Me.Label2)
End Sub
Private Sub toggle(lbl As Object)
Select Case lbl.BackColor
Case vbWhite
lbl.BackColor = vbBlack
Case Else
lbl.BackColor = vbWhite
End Select
End Sub
Me を使うのがミソである（以上 2018/04/06）。
Excel VBA は、Excel 以外のマイクロソフト社製アプリケーションも制御できる。 IE の制御については「操作の自動化」を参照のこと。
