Excel VBA に対する防衛術 #Excel

「闇の魔術に対する防衛術 Advent Calendar 2020」の枠が空きまくっているので急遽書きました。

個人的には Excel VBA はその存在そのものが闇の魔術だと思っているので, 落とし穴にはまらないよう備忘録として書いておきます。

すでにいろいろなところで口を酸っぱくして言われていることも多いですが, まあ。

(註) この記事ではつねに Excel VBA と表現しますが, おおむね VBA 全体に当てはまることです。単純に VBA がもっとも活用される部分が Excel なのと,素の Excel 計算との対比になる項目があるためこのようにしています。

論理演算子は短絡しない

Excel VBA の論理演算子は, ビット演算子と共用している関係か短絡しません。

たとえば, Java では以下のような Null チェックは頻出ですよね。

if hoge != null && hoge.fuga() == 0 {
    /* ... */
}

Java の場合hogeが Null であれば後段はスキップされるため Null に対するメソッドコールをしようとして失敗することを避けられます。

しかし, 同様のコードを Excel VBA で記述すると見事に落ちます。

If Not hoge Is Nothing And hoge.Fuga() = 0 Then
    ' ...
End If

VB.NET には短絡評価するAndAlso演算子およびOrElse演算子が存在していますが, Excel VBA にはくれませんでした。なんで?(殺意)

ちょくちょく「三項演算子の代用として紹介されるIIf関数は引数が全部評価されてしまうので使うな!」という主張がありますがそもそも論理演算子が短絡しない Excel VBA では今更なことです。ペッ!

防衛術

そういうわけなので, 前段の結果如何で後段の評価を行うかコントロールしたい, 具体的には今回のパターンや後段の評価に時間がかかるパターン, また後段が副作用をもつ場合などは以下のように入れ子にするほかないと思われます。

If Not hoge Is Nothing Then
    If hoge.Fuga() = 0 Then
        ' ...
    End If
End If

ところで, 関数の引数に関数を渡すことができれば評価を遅延できます。どうやら Excel にLAMBDA関数が乗るということでデータ型に「関数」が追加されるようですので, それに合わせて Excel VBA にもラムダ式が来れば, 以下のようなAndAlso関数を作ることができそうなので, 期待せずに待っておきます。 ~~いいからAndAlso演算子よこせよ~~

Public Function AndAlso(Lhs, Rhs)
    If Lhs Then
        AndAlso = Rhs()
    Else
        AndAlso = Lhs
    End If
End Function

' ...

If AndAlso(Not hoge Is Nothing, Function() hoge.Fuga() == 0) Then ' VB風なラムダ式予想図
    ' ...
End If

なんでもかんでも`Variant`なせいでインテリセンスがニート

Excel VBA にはVariant型が存在しているため, 戻り値の型が一つに絞れない場合などにも便利です。いや本音を言えばもっと行儀よく Union Types とか使ってほしいのだがまあ Excel VBA にそんな期待はしてない。そもそも型引数の仕組みすらねえし。

しかし, そもそも公式の関数がVariantでばっか返すせいで, せっかく搭載されているインテリセンス(オブジェクトが持っているメソッドやプロパティを挙げてくれる機能)が完全にニートと化しています。実際に複数の型がありえる場合はともかくRange#Item(RowIndex, [ColumnIndex])がRangeじゃなくVariantなのはおかしいだろオラオラ。

防衛術

一度想定している型の変数に突っ込んでしまうのも手ですが, そのためだけに変数を宣言するのも手間ですね。

プリミティブ型(っていう用語あるんですかね, Excel VBA)にはキャスト関数がありますが, オブジェクト型にはないです。そもそも, キャストは型が違った場合にも可能な限り変換してしまうせいでバグの発覚が遅れる危険性もありますし, 単純に強制的にその型として扱い, 型が違ったら落ちるような「型強制」のシステム(Swift でいうas!構文)のほうが適切でしょう。

ということで, 以下のようなグローバル関数を用意しておくと捗ります。

Public Function AsRange(Expression As Range) As Range
    Set AsRange = Expression
End Function

Public Function AsStr(Expression As String) As String
    AsStr = Expression
End Function

これらの関数はある種のアサーションとしても働きます。たとえば,

AsRange(Selection)

などと書かれていれば, それはユーザがセルを選択してからマクロを起動する, あるいはそれまでの処理でセルが選択されているということを前提にしている, と読み取れます。そうでなければ型エラーを起こします。

型引数システムがないせいでCollectionがVariantしか返せない問題に関しても, こういった関数を噛ませることで多少はマシになるかと思います。

文字列中の改行が面倒

エスケープ文字? そんなものうちにはないよ。

Dim s As String
s = "1行目\n2行目" ' 改行されない

ま, 正規表現を扱う場合なんかは逆に便利です~~が, その正規表現も参照設定な Excel VBA じゃねぇ…~~。

防衛術

素直に定数を使いますか。

Dim s As String
s = "1行目" & vbCrLf & "2行目" ' 要求通り改行される

あるいはあらかじめこういう関数を作っておく方法もあります。

Public Function Escaped(Str As String) As String
    Dim s As String
    s = Str
    s = Replace(s, "\n", vbCrLf)
    ' お望みなら他のエスケープシーケンスも
    s = Replace(s, "\\", "\")
    Escaped = s
End Function

' ...

Dim s As String
s = Escaped("1行目\n2行目") ' 要求通り改行される

`Date`へのキャストが環境依存

CDate関数や暗黙のキャストの挙動はロケーションに依存します。

Dim d As Date
d = CDate("01/02/03") ' d に代入される日付は地域によって違う

防衛術

横着して文字列からなんとかしようとせずDate型を直接取り回しましょう。 Date型にはリテラルもあります。

Dim d As Date
d = #01/02/03# ' このリテラルは常に「2003年1月2日」を意味します

0 オリジンと 1 オリジンの混在

Excel VBA ではセル操作や文字列操作は 1 オリジンとなっています。

たとえば, Mid関数で一文字ずつ取得する場合, 以下のように1から始めます。

Dim s As String
s = "test"
Dim i As Integer
For i = 1 To Len(s)
    With Selection
        .Value = Mid(s, i, 1)
        .Offset(1, 0).Select
    End With
Next i

にもかかわらず, 配列やCollectionは 0 オリジンです。しかも宣言時の値は配列の要素数ではなく添字の最大数なので,

Dim arr(3) As Integer

などと宣言すると, arrの受け入れ可能な添字は0, 1, 2, 3となり, 4 個の要素が確保されます。は??????

防衛術

これに関してはもう「気をつける」以外の対処がないように思います。

Option Base 1というオプションもありますが, 配列を返す関数にまで影響が及ぶわけではありませんし, Collectionには無力です。

むしろ無用な混乱を招きかねないので個人的にはおすすめません。

Excel との仕様の差

Excel で使える機能なんだから Excel と同じように動作すると思ったら大間違いです。

わかりやすい例として, 負の値の冪乗が挙げられます。

= -1 ^ 2

と Excel のセルに入力した場合, この式は1を返します。つまり, 反数演算子は冪乗演算子よりも高い優先度をもちます。これは単項演算子は二項演算子よりも優先度が高いという原則に従った仕様でしょう。

一方で

Dim x As Integer
x = -1 ^ 2

と Excel VBA で記述した場合, xには-1が代入されます。つまり, 反数演算子は冪乗演算子よりも低い優先度をもちます。これは数学上の慣習にしたがった仕様でしょう。

どちらも理屈は通っていますが, 統一されていないことにはかわりありません。また,

= "123" + "45"

という Excel 数式は168を返します。加算演算子はあくまでも数値に対する演算であり, 文字列はキャストされるわけです(数値として解釈不可能な場合#VALUE!エラーを返します)。文字列連結には&演算子が用意されていて, これは当然 Excel VBA でも使用可能です。

しかし,

Dim s As String
s = "123" + "45"

という Excel VBA マクロはsに"12345"を代入します。 Excel VBA では加算演算子は文字列の連結演算子としても使えます。なんで??????

防衛術

これもまあ基本的には「気をつける」以上の対処はないと思います。 Excel VBA を書いているときは Excel のことはすっぱり忘れましょう!! なんか禅問答みてえだな……。

逆に, Excel の仕様が恋しいときはEvaluate関数およびその略記法であるブラケットを利用する手があります。

Dim x As Integer
x = [-1 ^ 2] ' x = 1

Dim s As String
s = ["123" + "45"] ' s = "168" (文字列となるのは代入時の暗黙のキャストによる)

ちなみにブラケットは単にその中身がすべてEvaluate(" ... ")で囲まれているように動作するだけなので Excel VBA の変数は突っ込めません。無能かよ～～～。

制御フローが変数スコープを切らない

以下の Java コードはコンパイルエラーを引き起こします。変数宣言が if 文の中にあるからです。

int a = 0;
if a == 0 {
    int b = 2;
}
System.out.println(b);

以下の Excel VBA コードは通ります。は?

Dim a As Integer
If a = 0 Then
    Dim b As Integer
    b = 2
End If
Selection.Value = b

ちなみにaが0でない場合は暗黙初期化値の0が表示されます。

で, 以下のコードは変数名重複エラーを引き起こします。 switch 文(Select)にも容赦しません。毎回違う変数名を考えようね!!

Dim a As Integer
If a = 0 Then
    Dim b As Integer
    b = 2
Else
    Dim b As Integer ' 多重宣言
    b = 1
End If

防衛術

これももうしょうがないので慣れてください。なんどめだナウシカ。

`Integer`が16bits

上限 32767 とか短小すぎwww

防衛術

32bits のLongあるいは 32 / 64bits のLongPtrを使いましょう。ここまでの記事すべては悪い例です。

すべてが参照渡し

信じて送り出した引数が参照渡しの関数の副作用にドハマリして"アヘ顔ピースビデオレター"を代入されるなんて……。

防衛術

値渡し可能な型なら基本的に変数名にByValキーワードを付けて関数を定義しましょう。ここまでの記事すべては悪い例です。

Excel VBA に対する防衛術