ワクワクV054回目配列完全解説１回目(2021-08-29)

ほえほえ＠スプシマン @hoehoe1234

ワクワクV054回目 2021-08-29 配列完全解説１回目 配列完全解説講義シリーズについて一連の投稿をします。特に推敲などしていませんので、時系列順にトピックの羅列となります。また、動画も公開していますのでご興味のある方はご覧ください。

ほえほえ＠スプシマン @hoehoe1234

物事を理解するには可能であればまず全体像を把握するのが良いです。プログラミングにおけるトップダウン設計と同じですね。この手法が取れない場合もありますが。まずはエクセルVBAにおける配列の全体像です。精査はしていませんがだいたいこのようになるでしょう。 pic.twitter.com/R0A2VOSUUt

拡大

ほえほえ＠スプシマン @hoehoe1234

エクセルVBAで配列を使う目的とはどういうことでしょう？私は図にあるように配列の全体像を理解して①～⑥のように適切な場所で配列を使用し、使わない場合に比べてアプリを簡単に作り、高性能、保守性を確保することだと考えています。良い道具を適切に使うということですね。

ほえほえ＠スプシマン @hoehoe1234

これには道具の理解が欠かせません。まずは使う場所の列挙です。①は範囲とスピルですね。スピルが導入されてからは配列と範囲は似たような扱いになります。②はセル上で使用する関数です。これらの関数の多くは配列を受け入れます。配列数式ですね。③はユーザ定義関数（UDF)です。

ほえほえ＠スプシマン @hoehoe1234

これは標準関数ですが、配列を返す関数をシート上から呼び出すことによりスピルします。セルに値を設定する場合はレンジの拡張が必要ですがＵＤＦの場合はそのままスピルします。 pic.twitter.com/houX2Cu5Yy

拡大

拡大

ほえほえ＠スプシマン @hoehoe1234

④と⑤は同じにしても良かったかもしれません。④のVAB関数の中では②のワークシート関数と⑤のVBA組み込みの配列関数が使えます。これが狭義での配列ということですね。最後に⑥になります。これが配列のもっとも有効な使い方の一つ、レンジの値を配列で取得する、設定するとなります。

ほえほえ＠スプシマン @hoehoe1234

シートとセルは２次元配列とみなすことができるますのでcells(i,j).valueを使うことにより値の取得と設定ができます。しかし、この方式は場合により致命的に遅くなります。確たる理由はわかりませんがエクセルのスレッドとVBAのスレッドが都度切り替わるオーバヘッドが大きいのではないかと思います。

ほえほえ＠スプシマン @hoehoe1234

レンジから配列として一気に取得する、設定するのは早いのでやはりセルの値を都度取得／設定するのは相当のオーバヘッドがかかるのでしょう。計測値は以前計測しましたが忘れました。２桁以上の速度差があったように思います。これは件数が多い場合は致命的ですね。配列を使えば１分で終わる処理に

ほえほえ＠スプシマン @hoehoe1234

１００分かかるということですから。逆に件数が多くなければセルを使うと１秒が配列をつかって0.01秒になってもさして問題はないでしょう。この当たりは性能とコードの書きやすさ見合いということになるかと思います。cells(i,j)の呪縛から卒業するために配列にチャレンジしてみてはどうでしょうか。

ほえほえ＠スプシマン @hoehoe1234

二次元配列のイメージです。なんということはないですね。シートと同じです。ループも直感のとおりです。arr(i,j)とした場合にはiが行、jが列イメージになります。なにごとも基礎の確認からですね。 pic.twitter.com/d9Ck1ZbHNu

拡大

ほえほえ＠スプシマン @hoehoe1234

話がずれますがエラートラップのオプションです。 １つめを選ぶとエラートラップ(on error resume next等）が無効化されます。通常はこれは使いませんが、エラーを握りつぶしている関数などがある場合には発生箇所を知るために便利な選択となります。 pic.twitter.com/dts6dBLwB4

拡大

ほえほえ＠スプシマン @hoehoe1234

２つ目はクラスモジュール内でも止まります。コードがクラスモジュール内にある場合にはこの２つ目にしておかないと、実行時例外発生時にクラス内の「発生した箇所」では止まらずに上位に例外が伝えられます。３つ目はクラスを使わない場合の通常の選択です。エラートラップ以外の例外で止まります。

ほえほえ＠スプシマン @hoehoe1234

演習１です。VBAの配列は下限が指定できる、option baseが指定できる、組み込み関数の下限の指定は変更できない場合があるなど不必要に複雑です。多彩な指定が便利さではなく混乱をもたらしていますね。残念ですが仕方ありません。なのでできるだけ標準で使い、紛らわしさをなくする方向が良いかと。 pic.twitter.com/xMoKMcHdVC

拡大

ほえほえ＠スプシマン @hoehoe1234

そのため、以下のような方針をおすすめしています。 ①option base {0|1}は使わない ②配列の宣言はdim a(1 to 5)のように下限、上限を指定する ③配列のループはLbond(a) to Ubound(a)とする ④Array関数、VBA.Array関数はどちらかに統一する

ほえほえ＠スプシマン @hoehoe1234

⑤多くの場合VBA組み込み関数は下限が０、ワークシート関数は下限が１となりますので、関数設計時には、配列の下限を無理に揃えることはせず、配列を受け入れる関数が引数で渡される配列の下限を固定で想定しない。 などとなります。この他にも手法に応じていろいろな方針があるかと思います。

ほえほえ＠スプシマン @hoehoe1234

演習２です。演習内容というよりもスタイルの話ですが、 ①宣言時に「As Variant」をつけるかどうか ②ループ変数はバリアントかLongか などについてはそれぞれにスタイルがあると思いますのでそれで良いかと思います。また、職場では職場のスタイルに合わせることが最優先されます。 pic.twitter.com/7GGIUVEJAK

拡大

ほえほえ＠スプシマン @hoehoe1234

演習３です。ユーザ定義構造体(Type、以下構造体)の配列も作成できます。とても便利そうですが、構造体にはバリアントに代入できない、コレクション、辞書の要素にできないという致命的な欠陥があります。ですからバリアントの配列の要素としても使えません。常に具体的な配列の型となります。 pic.twitter.com/0SEmh6pSNc

拡大

ほえほえ＠スプシマン @hoehoe1234

すこし話がずれますが、構造体は他言語では「レコード型」と呼ばれることもあり、このように複数値を返すのに特に便利です。しかし、常に具体的な型を要求されるため、スタイルにもよりますがVBAでは使いにくいものになっているのではないでしょうか。なので、以後この解説では構造体は使いません。 pic.twitter.com/2srrc3nc91

拡大

ほえほえ＠スプシマン @hoehoe1234

この代替案としては、①動的配列＋Enum、②クラスモジュールの使用などが考えられます。自分のスタイルに合わせて構造体を含めて適切な物を選ぶ必要があります。どの案にも一長一短があり、決定打がないのが辛いところです。

ほえほえ＠スプシマン @hoehoe1234

バリアントに構造体を代入しようとするとこのようなエラーがでます。この他にも関数から複数の値を返すには戻り値を利用するのではなく、参照型仮引数を使う、モジュール変数を使う、シートを使うなどいろいろな方法があります。 pic.twitter.com/k9n5zYe2Dy

拡大

ほえほえ＠スプシマン @hoehoe1234

配列の次元数を取得する関数です。Lboundで発生する例外を利用しています。APIを使う、SAFEARRAY構造体（配列の実態）を直接読むなどの方法もありますがVBAオンリーのこの手法が良いように思います。 pic.twitter.com/KxqPsiLWTq

拡大

ほえほえ＠スプシマン @hoehoe1234

画像間違えましたね。正しくはこちらです。例外は「その関数にローカル」なのでexit functionしてしまえばなくなります。1 to 61（VBAの最大配列次元数＋１）にしておけば必ず例外が発生しますので、エラートラップ処理でその時点のi - 1を戻り値としています。 pic.twitter.com/D4YLOILsMb

拡大

ほえほえ＠スプシマン @hoehoe1234

この例外トラップではそのまま呼び出し元に戻りますのでon error goto 0で例外トラップの無効化はしていませんがこれで十分です。例外の特性をうまくいかせていますね。安心のため、forループを抜けた場合は戻り値を-1としていますがここは例外をレイズしてもよいでしょう。

ほえほえ＠スプシマン @hoehoe1234

例外を使う場合はこのようになります。例外は戻り値にたよらずに異常な状態を呼び出し関数に伝えることができますのでとても便利です。例外の挙動を理解して正しく使えるようになりましょう。 pic.twitter.com/nWcoUC4VRg

拡大

ほえほえ＠スプシマン @hoehoe1234

演習６です。ユーザ入力を配列に格納する場合などは、要素数が確定しないので動的配列を使用します。redim preserveを使用することにより配列を拡大／縮小させることができます。この演習では拡大した配列の最後に要素を追加しています。redim preserveは早いので１万件程度であれば問題なく使えます。 pic.twitter.com/SOIQBfz4wR

拡大