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シート(セル)の中で、他の既にある組み込み関数と同じように使うことが出来ます。(関数の補完候補としても出てきます)
こんな単純なものでもエクセルの関数の組合わせで作ろうとすると判りにくい入れ子構造になって面倒ですが、プログラムで書けばすっきるとします。
なお、引数の数が可変である関数を定義しても構いません。
-

変数宣言

Dim 変数名 As 型名
例えば
Dim a As String
という風に書く。
型名は省略できて
Dim a
と書ける。この場合は型名Variantが省略されたとみなされる。
この型名の省略による“Variantみなしルール”は言語仕様上問題レベルであり、注意が必要です。
例えば
Dim a,b As Integer
と書いてもエラーにはなりません。

一般常識では
Dim a As Integer,b As Integer
と解釈がされるものと勘違いしがちですが、VBAでは
Dim a As Variant,b As Integer
と解釈されてしまいます。両方Integerにしたければきちんと
Dim a As Integer,b As Integer
と書く必要があります。

さて、ここからは蛇足です。
このDimというのは“実行される”ものではなく、“あれば良い”というおかしなものなのです。
言っている意味が分からないと思いますので実例を挙げます。

If 0 Then
Dim a
Endif
a=30
こんな風に書いてもエラーにはなりません。
If 0 Then なので次の行は実行されないのですが、Dim aはキチンと有効になっているのです。
もちろん変数を宣言しないとエラーになるよう設定をしています。
例えばGoto文でまったく通らないところに置いても有効ですし、逆にGoto文で先に実行するように見えるところに置いても、変数を使うところより後ろの行にあるとエラーになってしまいます。
つまり実行される順番などは関係なく、プログラムの行として変数が使われるところより前でDimを書かないといけません。
「Dimはプログラムを実行する前に評価されるから」と言えるでしょう。

一方でDimの親戚に見えるRedimというのもあります。
これは“実行が必要な”文です。
「Redimはプログラムを実行する時に評価する」と言えます。

-

Selection

VBAプログラムというよりマクロ寄りの使い方をしたいとき、またはマクロそのものを作る時には便利な手法。
特定の領域(セル)を選択し、それに対してのみデータを加工したいということもある。
選択したところだけ色を塗るとか、各セル全て同じような操作をするときは「マクロの記録」を使った方が早い。
しかし例えば選択したセルの文字列全ての頭に“○”をつけるとか言うのはマクロではできないし、もっと複雑なことをしたいこともある。

これを実現するにはSelectionを使えば良い。

SelectionはRange型の集合体(コレクション)と考えれば良さそうだ。
まずはためしに選択したセル全ての位置(アドレス)を表示するプログラムを書いてみる。
定石通りFor Eachで取り出してループにする。

Dim r As Range
For Each r In Selection
    Debug.Print r.Address
Next

変数rはセル一つを示すRange型なので代入や値の読み出しやら自由にできる。

先に言った「○をつける」を書くなら

Dim r As Range
For Each r In Selection
    r.Value = “○” & r.Value
Next

で良い。

-

配列連係

連係というのは今ひとつの表現だが、
配列に値を設定する、逆に配列から値を作る、
という時に一行くらいでスマートにできる方法を例示していく。

配列をある初期値で設定することはよくある。
やり方をいくつか考えてみよう。

array Array(vanum,num,...)

まず普通なのはArray関数を使って引数列を配列に変換します。

Dim a
a = Array("First","Second","Third")
MsgBox a(2)

変数名宣言がa()ではなく、aと言う名前のみというのが気持ち悪い(a()とするとエラーになる)。
また、型名はVariant以外を指定してはならない(だからここでは無指定にしている)。
もっと気持ち悪いのが、Arrayの前に例えばa="abc"のように代入してもエラーにはならない。
一体どういうポリシーで動いているのだろうか。

array Split(str1,str2)

PerlのSplitに似ている。
文字列str1を区切り文字str2で区切って配列を作る。

Dim a As Variant
a = Split("Excel,Word", ",")

Arrayと同じく“a”のところは“a()”とするとエラーになる。

文字列を入れる場合はこのArrayを使うよりこのSplitのほうが楽に書ける。
先の例で言えば

Dim a As Variant
a = Split("First,Second,Third",",")
MsgBox a(2)

とすれば結果は同じになる。

よくある、曜日を漢字で配列に入れるときなどは

Dim a As Variant
a = Split("日,月,火,水,木,金,土",",")

とすればArrayで書くよりかなり楽だ。
なお、区切り文字を空白文字にしても良いだろう。

str Join(array[,str])

次に配列から文字列を作る方法。

配列を区切り文字列で結合して文字列にする。
区切り文字を省略時は空白一文字。

Dim a(1)
a(0)="Excel"
a(1)="Word"
Debug.Print Join(a,",")

結果は 

Excel,Word

PerlのJoinに似ているがVBAの配列の使い勝手の悪さ故、使い勝手は良くない。
配列の数が“合っていない”とあまりうまくない。

Dim a(3)
a(0)="Excel"
a(1)="Word"
Debug.Print Join(a,",")

結果は 

Excel,Word,,

という残念な結果になる。
これを回避するにはきちんとDim a(1)とするか、Redimで修正が必要。

-

文字列操作(3)

str Replace(str1,str_find,str_replace[,start[,count[,compare]]])
str1の中の文字列str_findを探してその部分をstr_replaceで置き換える。
startは検索開始位置、countは置き換える回数、compareは文字列を比較するモード。
compare
数値意味
0テキストモード比較。大文字と小文字の区別無し
1バイナリモード比較。

array Filter(ary,str[,bool[,compare]])
boolを指定しないか、Trueの時:配列aryの中から、strを含む要素を抜き出した配列を作って返す。
boolがFalseの時:配列aryの中から、strを含まない要素を抜き出した配列を作って返す。
compareはReplaceと同じ定義
Perlでいうgrepに相当するが、正規表現は使えないのでかなり使い勝手は悪い。

比較的複雑な機能の文字列操作関数です。
Perl等に刺激を受けて作ったとみられる関数ですが、VBAの言語仕様の悪さからあまり使い勝手の良くない(使用用途が限定される)残念なものになっています。
-

ExcelのVBAでの扱い方

エクセルというのは大まかに言って3つの階層があるという捉え方があると考える。
3つの階層のオブジェクトがある、といっても良いかもしれない。
まずはこれを改めて押さえておこう。

Workbookという概念。日本語ではブックという表現が多いと思う。
エクセルファイルと考えても良いと思うが、それを開いた時に「ブック」と称する。
ブックには名前があって、それは拡張子を除いたファイル名と同じになる。

次にSheetという概念。日本語ではシート。
ブックはひとつ以上のシートから構成される。

次がCell または Rangeという概念。日本語ではセル。
シートにはたくさんのセルから構成されている。
単にマス目がたくさんあるようにも見えるが、マス目の一つ一つがオブジェクトなのだ。
セルというのは英単語では細胞という意味もある。

Rangeは領域と訳すこともできるが、セルの一定の範囲を指す。

Cellは単一のセル、Rangeは単一または複数のセルを指す。

ExcelVBAのプログラミングというのは一般的にはこれらのモノをどう操作していくのか、ということになろう。

-

ワークブックのあるフォルダのファイルを操作したい

「このエクセルファイル(ブック)のあるパスはわからないものか」ということは多い。
これはThisWorkbook.Pathで知ることができる。
ファイルオープンの例で言えば

Open ThisWorkbook.Path & "\foo.txt" For Output As #1

みたいに書けば良い。

また、デスクトップやマイドキュメント等のフォルダへ操作したいことも多い。
この場合はEnviron関数によって環境変数を取得すると良い。

Open Environ(“HOMEDRIVE”) & Environ(“HOMEPATH”) _
 & “\Desktop” & "\foo.txt" For Output As #1

みたいに書ける。HOMEPATHというのはデスクトップやマイドキュメントのあるフォルダを指す。
これはドライブ名はついていないのでHOMEDRIVEでドライブ名(コロン付き)を取得する。
HOMEPATHに続くのは、デスクトップはDesktop、マイドキュメントはDocuments、ピクチャはPicturesとなる。

また、一時ファイルを置くフォルダを探しているのならTEMPを使うと良いだろう(これはドライブ名付き)。

このようにパスを取得して指定すればそのExcelファイルがあるフォルダに書き出しができる。

問題のある事例としてVBAを使ってファイルに書き出す、ようなときに、ついやりがちなのが

Open "foo.txt" For Output As #1

というような記述だ。

こうすると一言で言えばどこのフォルダに書かれるか分からない。不定状態ということになる。

ソフト的に「不定」というのはあまり適切な表現ではない。
正確に言えばユーザーのファイル操作などによって最後に操作したフォルダである。

一度も操作していなければシステムかExcelのバージョンに依存して決まる。
ともあれ、これはプログラムからすれば不定であるとも言える。

一般常識ではVBAを記述したエクセルファイルのあるフォルダ(開いたフォルダ)に書き出されるように思えるが、エクセルには基本的に「フォルダ」という概念はないようだ。

このことの事例として、例えばフォルダAのtest.xlsを開いた後にフォルダBのtest.xlsを開くと「既に開かれています」というメッセージが出る。
フォルダが違うのに、同じファイルであるという認識をするのだ。
これはフォルダ(パス)を意識してファイルの区別をしていないということである。

蛇足だが、Excelではシート名やファイル名等に角括弧([])を使うとファイル操作の時におかしな動作(場合によっては見慣れないエラー)を起こす。

-

セルの名前で参照

VBA以前の話でExcelではセルに“名前”をつけることができる。
例えば「ファイル名」という名前をつければ、そのセルを参照する場合、

a = Range(“ファイル名”)

と言うように書くことができる。

名前の定義が面倒臭いが、これによってプログラムの可読性やメンテナンス性が高くなる。
セルを移動させた婆、シート上の参照はそれに追従して書き換えてくれるのであまりメリットを感じないが、VBAプログラムで参照した場合は追従してくれるわけはない。
しかし名前で参照しておけば、セルを移動されても名前はくっついていくので問題ない。

-

VBEの問題(全角ダブルクオーテーション)

VBEのエディタにはたくさんのお節介な機能があるが、どうしようもない問題がある。

ひとつが「全角のダブルクオーテーション」を入力できないというものがある。
全角で入力してもエディタが勝手に半角に変えてしまう。
全く理解に苦しむ仕様で本当に腹が立つ。

対処方法としては文字コードで指定するという原始的な方法を使うしかないようだ。

左ダブルクオーテーション:Chr(&H8167)
右ダブルクオーテーション:Chr(&H8168)

ちなみに半角ダブルクオーテーションは、半角のを4つ並べれば良い。
3つ並べて入力すると、勝手にエディタが半角4つに修正する。

Excelではないが、VBAにはおかしな仕様がある。
それはMS-WordでParagraphで書かれた文書を処理しようとした場合、半角でダブルクオーテーションを書いても勝手に全角に変換するのだ。
これでは困るので半角に変えようとして上記のことに気がついた。

-