こんばんは、ファルコンM です。

フリーランスになって、初めての仕事はVBA 開発。

勤務表の自動生成ツールを作成した。

今まで、C言語、Java言語 などでソフトウェア開発を行っていた。VBA もプログラミング言語に変わりないので、とっかかりは順調だった。

だが、やはりソフトウェア開発。同じようにはまった。初めてVBA で開発を行ったときのはまったポイントを3つ紹介する。

1. 列のアルファベットはデフォルトでは取得できない

上記の場合。

range( “A1” ).Offset( 1, 2 ) は、C2 セルを表すが、そのC という文字を返すAPI がないのだ。

具体的に言うと、セル内の関数で 「XXXシート!C2 」って書きたいとき。range( “A1″ ).Offset( 1, 2 ) から、C2 という文字列を取得するAPI がない。

VBA だったらそういうAPI があってもいいんじゃね？って思って、必死で探した。だけど、なかった。ググってみたんだけど、本当にないみたいだ。。

なので、対策として、新しくAPI を作った。以下の記事を参照。

上記記事に書かれている関数をVBA コードにコピーすれば使える。range を引数にして、”C2” のような文字列を取得できる。

2. 文字列の連結は処理時間を考慮しなければならない

文字列の連結時、 「 str = “A” & “B” 」を使う場合が多い。これは、”A” と”B” を連結して “AB” という文字列をstr に代入する式。

だが、この 「&」 という演算子を使うと処理が遅くなる。詳しい理由はググってもらうのが早い。「&」 演算をするたびに毎回文字列のメモリ領域を確保しているのが原因。

上記のように1回だけの「&」だと、そんなに処理時間に影響はない。

私の場合、1つの式に 「& 」を15回くらい使っていて、それを90×300 回処理して、同じ処理を30 シート行っていた。つまり 「& 」の処理を

15×90×300×30 = 12,150,000 回

行っていたのだ。

むちゃくちゃ時間がかかった。というか、あまりに時間がかかりすぎたので、強制終了した。今までのソフトウェア開発の経験から、なんとなく文字列があやしいなあと想像することができた。だが、その想像ができない場合は、原因を探るのに時間がかかる。

解決策として、私は「&」 演算子を使わずに「Join()」関数を使った。配列の文字列の領域をあらかじめ確保して置き、「Join()」で結合する。

strs(0) = “A”
strs(1) = “B”
str = Join(strs, “”)
[/C]

上のように書けば、strs の分メモリ領域を使用するが、速度は改善される。「&」演算の 処理が多くなる場合は、Join を検討することをお勧めする。

3. セルの時刻、日付の直接入力時、自動変換に気をつける

セルに数値を入力した時、エクセルによって自動的に時刻や日付形式に変換されてしまったことはないだろうか？

私は何回もある。エクセルを使い始めて間もないころは、これが原因で1時間くらい作業が遅れたことがある。

VBA の場合もこの自動変換に気をつけたほうがいい。

手動で入力したセルの値をVBA で取得するとき、意図しない値となっている可能性がある。

この値で比較演算を行うとき判定結果が意図しない結果になることがある。

私は、これではまってしまった。

VBA で、比較演算処理をエクセル関数として1つのセルに出力する処理を作った。

その後、動作確認のテストを行った。

だが、エクセル関数結果が意図しない結果となっている。

この時、エクセル関数の処理ばかりを疑っていた。

が、まったく原因が分からない。

1時間くらい調査した。

まさかの、手動入力値がエクセルによって自動変換された箇所に原因があった。

VBA でなくてもエクセル入力時に、この自動変換に悩まされることは多い。

こういうことがあるということを知っておいて、損はない。