和風スパゲティのレシピ

日本語でコーディングするExcelVBA

VBAの落とし穴

"1"+"1"="11"

「+」を文字列に対して使うと連結演算子(&と同じ)になります。足し算の結果がかなり大きな誤った値になったり、セルの表示書式を「文字列」にすると正しく計算されないような不具合が発生した場合は、この仕様を疑ってください。

DisplayAlertsを常時Falseにしてはいけない

Application.DisplayAlertsプロパティはExcelからの確認メッセージを非表示にする機能で、削除や閉じる処理に使います。これを常時OFFにしていると、予期せぬ確認メッセージに自動返答されてしまい、マクロのバグが迷宮入りしてしまいます。

Dictionary.Keys(i) .Items(i)と書いてはいけない

DictionaryのKeys/Itemsメソッドは全key/itemを1次元配列にして返すメソッドです。つまりKeys(i)はKeysの「引数i」ではなく、Keysが返す配列の「添字i」ということです。Keys(i)を使用したコードは都度配列を生成するため非常に遅くなります。

読取専用ブック.Saveは警告なく別ブックに保存される

読取専用ブックにSaveメソッドを実行した場合、無警告でカレントディレクトリに保存されてしまいます。上書き保存したのにブックが更新されていないかったり、ファイル置き換えダイアログがでた場合は、この仕様を疑ってください。

Protectメソッドを連続実行すると保護が解除される

保護されたシートに再度Protectメソッドを実行した際、引数DrawingObjectsまたはScenariosを指定したときだけ保護が外れる不具合が存在します。Protectメソッドの引数「Contents」にTrueを渡すか、必ずUnprotectを挟むことで解消できます。

Findは遅くて非表示に弱くてユーザー操作の影響を受ける

検索によく用いられるFindメソッドさんですが、実は危なくて遅い地雷メソッドです。非表示セルを見つけられない、ユーザーと設定を共有する、MATCH関数より十数倍レベルで遅い、の三重苦なので、なるべく使わないようにしましょう。

Function名に句読点を使うとシート上で呼べなくなる

Functionプロシージャの名前に句読点を使用すると、ワークシート上でユーザー定義関数として呼び出せなくなります。読点「、」とカンマ「,」、句点「。」とピリオド「.」が自動変換されることが原因のように思えますが定かではありません。

Shapesにはドロップダウンリストの▼が含まれる

For Eachで全図形を処理したとき変な動きをする、Shapes.Countを数えるとなぜか1個多い、入力規則の▼が消えてリストから選べない、あたりにお困りの方は、Shapesがドロップダウンリストの▼を含む罠に落ちていないかご確認ください。

通常の実行ではエラーが出るのに1行ずつ実行すると上手くいく原因

マクロがエラーで止まったのに「デバッグ」から1行ずつ実行したら上手くいってしまう。マクロの結果がおかしいので、ステップイン(F8)で1行ずつ実行してみたら正しい結果が出てしまうなどにお悩みの方はこの記事に目を通してみて下さい。

Endによる最終行取得は非表示の行を検知できない

知らずに落ちると抜け出せなくなるVBAの落とし穴です。最終行がなぜか小さい値になるときは、この罠を疑ってください。Endプロパティは非表示セルを検知しない仕様がありますので、表示されている最後のセルしか取得できません。

日本語関数はクイックヒントの太字強調がズレる

関数を入力する際、仮引数のリストを表示する機能があり、この機能をクイックヒントと呼びます。このクイックヒントには「入力中の引数を太字で強調する」機能があるのですが、関数・引数名が日本語だとこの協調がズレるので注意しましょう。

結合セル.Offset(1)は結合エリアの1つ下を指す

A1:C3の3×3正方形が結合されているとき、Range("A1").Offset(1)は、結合下端の1つ下であるA4セルを取得します。結合エリア全体Range("A1:C3")からのOffset(1)も、同じくA4になり、Offsetの特長である「元Rangeの大きさを維持」もなくなります。

掛算を置換するとワイルドカード*と認識される

あいまい検索に使用するワイルドカード「*」と、掛け算に使用する「*」は同じ文字です。よって、掛け算を置換する際に、「*」も含めた置換をすると、*の部分は任意の文字列のため、置換が暴走します。「~*」とすることで、記号をターゲットに置換できます。

Len関数は文字列長でなく変数の消費メモリを返すことがある

Len関数には、「String以外の変数を渡すと、変数のメモリ上の大きさを返す」といういらぬ仕様があります。Len関数がなぜか4を返す、文字列の切り抜きがうまくいかない、あたりにお悩みの方は、この落とし穴に落ちていないかご確認ください。

セル範囲のコピー&ペーストに非表示のセルは含まれる?

セル範囲をコピーした場合、オートフィルターで抽出していないレコードはペーストされません。しかし、非表示にしている列はペーストされます。この仕様の違いは、知っている方も多いのではないでしょうか。では両方がかかっているデータをコピーすると…?

For Each ○ In Range はセルごとのループとは限らない

落ちると抜け出せなくなるVBAの落とし穴です。For EachでRangeオブジェクトをループしたとき、全セルが処理されない。Rangeオブジェクト.Countがセルの数を返さない。あたりにお悩みの方は、この落とし穴に落ちていないかご確認ください。

Workbooks.Open中にShiftを押すとマクロが停止する

知らずに落ちると抜け出せなくなるVBAの落とし穴です。Wrokbooks.Open 以降が実行されない。マクロが何のエラーメッセージも出さずに静かに止まる。Exitもなく絶対実行されるはずの行が実行されない。あたりにお悩みの方は、この落とし穴を疑いましょう。

Val関数は全角(日本語入力)の「1」を0に変換する

知らずに落ちると抜け出せなくなるVBAの落とし穴です。今回の罠は超単純です。Val("全角の1") ' ← 0が返ります。という罠です。皆さん気をつけましょう。他にも罠がたくさんあるので、Val関数は正直言うと使用禁止関数です。

OnErrorResumeNextはIfの分岐をTrueとみなす

Ifの判定部分がOn Error Resume Nextでスキップされた場合、IfステートメントはTRUE側に分岐します。実はIfステートメントはTrueなら通ってよしという処理ではなく、FalseならEndIfまでジャンプという処理なので、それがスキップされるという理屈です。

Dirループ中は別関数であっても他のDirは使えない

知らずに落ちると抜け出せなくなるVBAの落とし穴です。Dir関数が途中で止まる、逆に無限ループする、「プロシージャの呼び出し、または引数が不正です。」エラーを出す、あたりにお悩みの方は、この落とし穴に落ちていないかご確認ください。

単セル.SpecialCellsはシート全体から実行とみなされる

知らずに落ちると抜け出せなくなるVBAの落とし穴です。SpecialCellsを使ったらバグった。急にシート全体の値が書き換わった。SpecialCellsでセルを絞ったつもりが、元のRangeよりなぜか広くなった。あたりにお悩みの方は、この落とし穴を疑ってください。