はじめに

私は元々プログラマーですが現在は単なる事務職です。
事務職ではよくあることですがExcelを使って様々なデータ処理を行っています。
業務としては単純な繰り返し作業が多くつまらないものです。

プログラマーという人種はこういうのが一番嫌いなのです。
プログラマーという人種は例えば手作業でやれば20分で終わるようなことでも、プログラムを作ってやろうとします。例えプログラム作成に一時間かかろうとも。
でも仕事だから仕方ありません。

手作業で10時間かかることも、プログラムでやれば数分で終わるものも珍しくありません。
プログラム作成に1時間かかっても充分元が取れますし、その数分はPCが勝手にやっているのでサボってコーヒーでも飲んでいても終わります(いや、私は他の仕事をやっていますよ)。

なによりものメリットはケアレスミスは発生しない、ということです。
単純作業の繰り返しは人間ならばどうしても起きてしまうものなのです。
プログラムでやれば、プログラム上の処理ミスさえ撲滅しておけば間違いは起こりません。
結果の間違い探しもプログラムで行ったり、人間によるチェックもざっとやっておけば充分です。

しかしこの事務職になるまではExcelはもとかくVisual Basicは見たくもありませんでした。
なぜならVisual Basicというのは他のプログラム言語と比べれば著しく“醜い”のです(英語でいえばugryでしょうか)。

しかしそれでもVisual Basicを使おうと思ったのは定型・単純作業があまりに多くその苦痛感のほうがきつくなったからです。
会社には様々なITシステムがあるのですが、「通り一辺倒のデータ入力をして通り一辺倒のデータ蓄積」をするだけで“新しい課題への対応”ができない、要するに応用力が皆無なのです。
「Excelデータ出力」はかろうじてあるが、せめてもの救いなのですが、そのデータから解析をしなければなりません。(悪く言えばそのあとは関係社員に投げているだけなんですね。)
周囲や前任者を見ているとみんな手作業、良くてセルに関数を組み込んで処理をしています。
これはプログラマーとしては我慢ならなかったというのが実情なのです。

この記事はそんな中でVisualBasic for Applicationを使うにあたっての、自分なりのまとめと考察です。

題目ではExcelと書いていますが、あまりExcelに偏らず、コンピュータ言語と捉えております。

VBAと略されますが、ApplicationのためのVisual BASICという意味です。
ApplicationというのはExcelだったり、WordやAccess等のことを指しています。
VBAというとExcelで語られることが多いのですが、AccessでVBAは重要ですし、Wordでも使うと便利なことが多くあります。
いわゆるOfficeスイートなので、OutlookやPowerPointやPublisherでもVBAは利用できます。

実際、私はOutlookでもVBAを使ってメールの新着お知らせポップアップを作ったり、システムからくる警告メールを解析するためにタイトルやメール本文などからも情報を抜き出して自動処理させたりもしています(メールの振り分け時に連携することもできます)。

VBAを使った相互連携ができるのも便利です。
例えばWordからExcelブックで作った住所録を開いてデータを引き出し、差し込み印刷をする、みたいなこともできます。

Officeアプリの持つ各機能をプログラムで動かせる、機能拡張したように見せる、様々な作業を自動化できる等のメリットは非常に大きいということは確かです。

これがVBAを習得する目的であると考えるのが妥当でしょう。

「最近プログラムが学校教育にも導入されるわけだし、勉強するか」といっていわゆる「プログラミングのお勉強」としてVBAを学ぶというのは全くオススメはしません。

今の世の中、はるかに良い言語はいくらでもあります。

2010年代に入りWeb全盛となった現在では、JavaScript、JAVA、PHP、Perl、Ruby、Pythonあたりがメジャーどころでしょうか。
昔はコンピュータ言語というものはシステム(コンピュータ)に付属するものか、高額で購入するものでした。
ところが今は上記のものがタダで入手できます。
VBEという劣悪なプログラミング環境ではなく、とても良い統合環境が無料でも入手できます。

VBAは前身が古いこともありますがコンピュータ言語として見ると時代遅れな部分が余りに多いのです。
例えばJavaScriptも醜いとは言われますが、醜い部分を取り除いて(無視して)一部の良いところだけを使っても近代言語として十分なものを持っていると思います。
その観点でもVBAはあまりに言語として基本的に貧弱で不足なのです。

“for Application”ではないVBがあり、それは言語仕様として発展しており、まだマシなのですが、VBAはそれよりもバージョンが大分低く言語的に明らかに劣っています。

「美しいプログラム」「保守性の高いプログラム」を作るのが困難なレベルであり習得や活用を妨げるレベルの問題がありますので、その辺は覚悟をする必要があります。

特に問題なのは変数のスコープであり、制御構造の稚拙さです。
正規表現を言語として対応していなかったり、ハッシュ変数がないのも厳しい場面が多々あります。
WindowsやOfficeがオブジェクトの塊である一方で、それを扱うVBAではオブジェクト指向面で言語として不十分なのはあまりに問題がありますが、現実として諦めるしかないのです。

-

はじめに(大文字と小文字)

いわゆる予約語について、説明の中では先頭文字が大文字となっている。
例えば「for」ではなくて「For」である。

これはVisualBasicというよりは専用エディタの特性なのだが、小文字で入力しても、内部で予約語かどうかを確認をして予約語だと頭文字を大文字に自動変換をする。

他の普通の言語では太文字にしたり色を変えたりするのだが、VBではこういう作法である。

プログラムを作る側の作法としては、小文字で入力して頭文字が大文字に変換されれば正しい予約語として理解してくれたんだな、と思えば良い。
もし変換されなかったらミスタイプを疑うべきであり、スペルチェックをしてくれていると好意的に受け取ろう。

予約語というのはこういう制御文とオブジェクトそのものやそのプロパティも含まれる。

もっといえば、これはプログラムで定義した変数やプロシージャ名、関数名等も同様の扱いになる。

-

型名 (2)

厳密に言うと型できちんと区別すべきという論もあります。
しかしVBAは型に対して実にいい加減です。
それでも数値、文字列、いくつかの特殊な型、オブジェクト系の型という分類では区別する必要があります。
多くの関数は数値および文字列を扱うものなので以下のように表記することにします。

num : 数値
数式なども含む数値です。
複数の引数を取る場合は後に連番をつけていきます。
num func_a(num1,num2)
関数func_a は引数num1とnum2を引数とし、数値を返します。

str : 文字列
文字列を示します。
str func_b(str1,str2,num)
関数func_bは引数str1,str2および数値numを引数とし、文字列を返します。

Date : 日付
Excelの特徴として日付型という概念があり、時刻を含んだ日付を1つのデータとして扱います。

他にも特殊な型がいくつかあるのですがその都度説明をします。

省略形について
一部の引数を省略できるものもあります。
num func_a(num1[,num2])
これは
a = func_a(num1,num2)
a = func_a(num1)
の両方をともOKということです。
後者ではnum2に相当する値はその関数で設定される規定値になります。
-

その他

num LBound(array[,dimention])
num UBound(array[,dimention])
配列の最小引数/最大引数を返す
動的変数などで配列の上限が不明な場合
Sub/Function等で不定の場合
などに使えるでしょう。
2番目の引数(dimention)は多次元配列の時に使う。
1次元目なら1,2次元目なら2を指定。

Long RGB(red,green,blue)
RGB各256階調で指定したRGBコードを返す
セルなどの色つけの時に利用すると良い。

Long QBColor(num)
指定した色番号(Excelの色番号)のRGBコードを返す
ExcelVBAで定義されている色名(色番号)をRGBコードにして得る。
色の管理をRGBコードで統一して管理する時に使えるだろう。

MsgBox
メッセージボックスを表示する。複数のボタンを用意し選択を得ることもできる。
関数なのか?という気もするが、関数リファレンスに載っているので仕方ない。
パラメータなどが複雑なので別記する。

InputBox(Prompt,[Title],[Default],[XPos],[YPos],[HelpFile],[Context])
ダイアログボックスを表示してユーザーに値を入力させ、値を返す。
Prompt:表示するメッセージ文字列(数字を入力してください、等)
Title:ダイアログボックスのタイトル部分の文字列。
Default:予め入力してあるように入力する場所に置いておく値。使用時にはただEnterを押せばその値が入力されることになる。
XPos,YPos:ダイアログボックスの表示位置
HelpFile:ヘルプファイルの指定
Context:ヘルプファイル内の場所を示すコンテキストID
引数では,で単に区切って空白にしてパラメータ指定もできるし、Default:=200等で名前付引数を使うこともできる。

Shell(str[,num])
外部プログラムを実行する。
非同期実行なのでプログラム起動後はVBAプログラムとしては外部プログラムとは無関係に先に進みます。
strには実行するプログラムのファイル名を指定します。
numには実行時のウィンドウ形式を指定します。省略時には2。
定数内容
vbHide0非表示。フォーカス移動あり
vbNormalFocus1元のサイズと位置に復元。フォーカス移動あり
vbMinimizedFocus2最小化表示。フォーカス移動あり
vbMaximizedFocus3最大化表示。フォーカス移動あり
vbNormalFocus4元のサイズと位置に復元。フォーカス移動なし
vbMinimizedNoFocus6最小化表示。フォーカス移動なし

Error(num)
エラー番号に対応するエラーメッセージを表示する

CreateObject
ActiveXオブジェクトへの参照を作成する

GetObject
ActiveXオブジェクトへの参照を作成する

CallByName
オブジェクトに関する操作

以下、関数としての存在意義が分からないので説明省略。
Partition
Tab
-

セルへのコメント

コメントをVBAでつけてみます。

Dim rng As Range
Set rng = ActiveSheet.Range("A1")
rng.AddComment "Comment2"

これだけでコメントがつきます。
ところがここには罠があります。
一度実行した後、もう一度実行すると「アプリケーション定義またはオブジェクト定義のエラーです」という日本語としてもおかしいエラーメッセージがでます。

エラーメッセージが使い物にならないのはいつものことなので放っておいて、なぜエラーになるかと考えると、既にコメントがあるのに、更にAddCommentしたからエラーになったのだと推察されます。

既にコメントがあり、それを変更するには

rng.Comment.Text "Comment2"

と書きます。Textだからといってプロパティと思い込んではダメで、これはメソッドです。
プロパティだと思って代入しようとして

rng.Comment.Text = "Comment2"

と書くと「定数には値を代入できません」というまたもおかしなメッセージが出ます。(定数ではないだろう)
一方でメソッドのはずなのですが、これはなぜか値を持っています。

s = rng.Comment.Text

と言う風になぜか文字列変数としての値をもっているのです。
本当に意味不明です。こうするならばプロパティにすれば良いのにとしか思えません。(メソッドとしても引数はひとつしかと取らないのですから)

ここで.Commentは既にコメントがついていないとエラーになります。
これらのことから、コメントがあるかどうかを気遣って(判定して)処理しないとダメだということです(判定方法は後述します)。

ついでに関連の記述をいくつか示しておきます。

rng.Comment.Visible = True
rng.Comment.Shape.Width = 100
rng.Comment.Shape.Height = 20

一つ目は「コメントの表示/非表示」に相当します。Trueなら表示、Falseなら非表示。
二つ目でコメント表示枠の幅を設定できます。
三つ目で同じく高さを設定できます。

さて、コメントがあるかないかで処理を分ける必要があります。
コメントありなしをTrue/Falseを返すプロパティがありそうなものですが違います。

TypeName(rng.Comment)

という風にして.Commentのタイプを文字列判定しなければならないのです。
コメントがあると”Comment”、ないと”Nothing”という文字列を返すからです。

最後にコメントの削除はというと

rng.Comment.Delete

です。これもコメントがないとエラーになります。

常に1からコメントを入れるのでならば、一旦コメントを削除してから(もちろんコメントがないとエラーになるのでコメントの有無を確認してから削除)、AddCommentをした方が処理は楽かもしれません。

Dim rng As Range
Set rng = ActiveSheet.Range("A1")
If TypeName(rng.Comment) = "Comment" Then rng.Comment.Delete
rng.AddComment "Comment2"
rng.Comment.Shape.Width = 100
rng.Comment.Shape.Height = 20
rng.Comment.Visible = True

このように、まずは既にあるコメントを削除しても良いのであればClearCommentsを使って

Dim rng As Range
Set rng = ActiveSheet.Range("A1")
rng.ClearComments
rng.AddComment "Comment2"
rng.Comment.Shape.Width = 100
rng.Comment.Shape.Height = 20
rng.Comment.Visible = True

としても同じ結果になります(判定文が不要になります)。
ClearCommentsはコメントがなくてもエラーにはならず、コメントがあればコメントを削除してくれます(Comment.Deleteはコメントがないとエラーになる)。

-

結合されたセルの値

結合セルというのはなかなか厄介な仕様となっている。

例えば、A1とA2が結合されており、なんらかの値が入っているとした場合
Range(“A1”).Valueでその値を得られるが、Range(“A2”).Valueは空っぽ。
これでは全てのセルをサーチして処理するような場合、面倒なことになる。

上記の場合、まずA2が結合されたセルかを判定する必要がある。
それがMergeCellsプロパティで、結合されたセルの一部ならTrue、違えばFalseとなる。

一部とわかれば欲しいのは結合セルの値(Value)。
一行で書くなら

a = Range(“A2”).MergeArea.Cells(1).Value

となるが、順に説明をしていく。

まずMergeAreaプロパティで、指定したセルを含む結合セルの範囲を示すRange型の値を得る。

Dim r As Range
Set r = Range(“A2”).MergeArea

上記のようにすればrはRange(“$A$1:$A$2”)と同等になる。
(r.Addressを表示させれば確認ができる。)

セルの値はRangeの範囲の最初に入っているので、Cellsプロパティを使って一番最初を指定してやれば良い。.Cells(1)で指定できるので、上の続きで以下のように書けば値を得られる。
(縦横に結合していてもCells(1,1)と書く必要はない。Cells(1)で一番最初となる)

a = r.Cells(1).Value

ちなみにこのようにMergeAreaプロパティで得たRange型の範囲から、結合した部分の列数(.Columns.Count)や行数(.Rows.Count)などを得ることもできる。

-

ファイル

CurDir
指定したドライブの現在のパスを返す。
そもそもExcel(Office)においてはパスの扱いが雑なんですが、ファイルを読み書きする場合にはしっかりと指定が求められるので、VBAではプログラム側で完全に把握して操作する必要があります。

Dir(Path as String)
ファイル/フォルダ一覧を得る。
一度Pathを指定したら次からDir()と呼び出す毎に次のファイル名が入っている。

EOF(ファイル番号)
ファイル読み込みが末尾に達しているか。

Long Loc
現在の読み込みもしくは書き込み位置を得る
Long Seekとの違いは?

LOF
ファイルの長さを返す

FileAttr
ファイルモードを返す

GetAttr
ファイルまたはフォルダの属性を整数型で返す

FileDateTime(pathname as String)
作成日時または最終修正日時を返す

FileLen(pathname as String)

FreeFile
使用可能なファイル番号を返す。
(ユーザーにファイル番号なんか管理させるからこんなものが必要になるんだ・・)

Input
openしたファイルからに文字数で文字列を読み込む

InputB
openしたファイルからバイト数でバイトデータを読み込む(文字列で返す)
これでは一行ずつ読み込んで表示するプログラムすら導き出すのは不可能であるため、サンプルを書いておきます。
ファイルとしてはsample.txtがブックのフォルダにあるとします。
ファイルはShiftJISしか扱えません。
Open ThisWorkbook.Path & "\" & "sample.txt" For Input As #9
Do Until (EOF(9))
  Input #9, s
  Debug.Print s
Loop
Close #9
VBAは非常に醜いプログラム記述になる言語です。
普通はファイルハンドラというものをopenで得るものですが、VBAは通し番号を人間側が管理する必要があります。
ここでは9という数字にしています。
open [ファイル名] For Input As #[数字]
なお、#は省略できます。
引数の順番だけで認識すれば良いのに、For/Asなんて語が必要なのが理解不能です。
ちなみに接続詞で書き順の融通を利かせているのかと思いきや
open [ファイル名] As #[数字] For Input
と書くとエラーになります。
次にEOFでファイルの終わりかを判断しています。
この数字に#をつけてはダメで数字のみを書きます。
文字列をファイルから一行読み込むのは
Input #[数字],[読んだ文字列を入れる変数]
となります。ここは#を省略できません。
サンプルでは読んだ一行は文字列変数sに入るのでそれをPrintしています。
最後にファイルを閉じるために
Close #[数字]
ここの#は省略できます。
-

IE操作

ツール-参照設定でMicrosoft Internet Controls とMicrosoft HTML Object Libraryを参照にすること

Dim ie As InternetExplorer
Sub ie_test()
  Dim doc As HTMLDocument
  Dim coll As IHTMLElementCollection
  Debug.Print "----"
  
  IE_Open ("http://xxxxx")
  IE_Wait
  Set doc = ie.Document
  'タイトルを表示してみる
  Debug.Print doc.Title
  'aタグの一覧を表示してみる
  Set coll = doc.getElementsByTagName("a")
  Dim el As HTMLInputElement 'セレクタなら HTMLSelectElement
  For Each el In coll
    Debug.Print el.innerText
  Next
  '特定入力ボックスに入力してみる
  doc.getElementsByName("chat")(0).Value = "Input Text"
  'ボタンをクリックする
  doc.getElementsByTagName("input")(0).form. submit
  MsgBox "OK"
  IE_Close
End Sub
Sub IE_Open(url)
  Set ie = New InternetExplorer
  ie.Navigate url
  ie.Visible = True
End Sub
Sub IE_Close()
 If Not ie Is Nothing Then ie.Quit
 Set ie = Nothing
End Sub
Sub IE_Wait()
  Do While ie.Busy = True Or ie.ReadyState < READYSTATE_COMPLETE
    DoEvents
  Loop
End Sub
-

RangeとCells

Rangeというのは日本語では“範囲”のことを言う。
シート内のどこからどこまで、という表記になる。
もちろん1つのセルでも構わない。「A1〜A1という範囲」も、論理的には間違いではない。
一方Cellsが示すものはあくまでひとつのセルである。

1つ重要な事は、Rangeはオブジェクトであり、Cellsはプロパティである、ということ。
そもそも同列に考えること自体が間違っている。
CellsというのはRange(というオブジェクト)の中のひとつのセルを表わすもの(プロパティ)と考えると理解が深まるのではないだろうか。
Rangeがなくて単にCells(1,1)とも書けるじゃないか、という方もおられると思うが、その場合は暗黙的にシート全体がRangeとなると考えれば良いだろう。
言うまでもなく、シート全体で見ればCells(1,1)はA1と同じになる。

「Rangeはオブジェクト、Cellsはプロパティ」の違いがわかりやすい例としては
Dim r as Range
Set r = Range("C5:D20")
Debug.Print r.Cells(1,1)
のような記述ができる。
RangeとCellsをごっちゃに考えていると違和感があると思う。
これでprintされるのは"A1"の値ではなくて"C5"の値である。
C5〜D20の範囲(世界)で考えてセル座標(1,1)はセルC5であり、その値を表示する、と解釈される。

Excelのシート上にある表は必ずしも左上隅から始まるとは限らない。
ズレていると処理の開始行は6だから変数の開始を6にして、とかになる。
デバッグの時も判りづらいし、その表を移動したりすればまた面倒だ。
こういうとき、その表をRangeで設定しておき、処理を.Cells(x,x)で行ういう方針で行えば、どこに表が置かれていても、プログラムとしては表は左上隅(1,1)から始まるとして書くことができる。
この方針で作れば、表を移動させても最初のRange設定をいじるだけで良く、処理部分に手をつける必要性が低くなる。
(最も推奨されるのは“テーブル”を使うことだろうが、大袈裟に感じる事も多いし、テーブル機能自体が“見出し行”(VBAではHeader)が一行しか許されないので現実として使い勝手も悪い)

また、この考え方で便利なのは“セル結合”されたシートを扱うときだろう。
例えばA1からA5が結合されており、“北海道”という値が入っているとする。
この時、その値が入っているのはA1のみであり、A2〜A5は空である。
このような“仕様”がVBAの処理で困ったことになることは多い。
表作成者としては1〜5行目までは“北海道”のナニカという意図なのだろうが、VBAで単純にRangeやCellでA2〜A5の値を引用しようとしても空の値しか返ってこないからだ。

このような時にはMergeAreaというRangeオブジェクトの一種を利用すると良い。
例えばRange("A1").MergeAreaはRange("A1:A5")と同じ意味を持つ。
Range("A2").MergeAreaもRange("A3").MergeAreaも、はたまたA4やA5の場合でも同じになる。
つまり結合されたどのセルを指しても、結合された全体のRangeを得ることができて、VBAのプログラムとしては都合が良い。

そしてこの範囲のいずれかを指定して期待される値は、一番左上隅つまりCells(1,1)が持っている。
よってsにはA1,A2,A3,A4,A5のいずれかが入っているとして
Range(s).MergeArea.Cells(1,1)
とすればよい。
つまりこれで結合されたどのセルを指しても“北海道”という期待通りの値を得ることできる。
-

VBAで作った関数をExcelの関数として使う

Excelのシート・セル中で使える関数として、VBAで作った関数が使えると便利です。
関数の組み合わせて複雑になってしまうものも、VBAで書けばあっさりできるという事もあるでしょう。
気がついていない人も多いですが、VBAで定義した関数はExcelからも使えます。 要件は以下の三つです。
  • Functionを使った関数定義をする。
  • 標準モジュールシートに書く。
  • FunctionはPublicにする。
要するに普通にやればいいだけです。
標準モジュールではないというとは、ワークブックのところではダメということですが、ここへ定義したものは標準モジュールからも呼べません。
また、Publicも、特にPrivateと書かなければPublicになりますから、実は書く必要がありません。(下記の例では明示します)

ここでは正負判定の文字列を返す関数で例示してみます。
Public Function judge(a As Integer) As Integer
If a < 0 Then
judge="負の数"
Elseif a = 0 Then
judge="ゼロ"
Else
judge="正の数"
Endif
End Function
こんな感じで、Excelシート(セル)の中で、他の既にある組み込み関数と同じように使うことが出来ます。(関数の補完候補としても出てきます)
こんな単純なものでもエクセルの関数の組合わせで作ろうとすると判りにくい入れ子構造になって面倒ですが、プログラムで書けばすっきるとします。
なお、引数の数が可変である関数を定義しても構いません。
-