2022-02-03 エクセルVBA 名前は間接参照

4
ほえほえ@DX塾 @hoehoe1234

名前もenumもテーブルもすべて「間接参照」の仕組みなので適した所につかえばいいね。テーブルが最強なのは名前空間が自動で生成されるから。名前定義は便利だけど「手順」なので見えにくいし大量定義はしんどいという弱点があるね。しかしテーブルでカバーできていな単票形式は相性がいい。

2021-03-26 14:57:03
ほえほえ@DX塾 @hoehoe1234

となると次は名前定義の可視化になるんだけどこれは簡単な関数で実現できる。以前読んだ書籍にも「名前定義は最初のシートにまとめてかいておけ」というのがあったけどそれの自動化だね。これは割と簡単。

2021-03-26 14:58:25
ほえほえ@DX塾 @hoehoe1234

2つの方法がある。ユーザ定義関数として実装するか、VBA的は実装をするか。前者は関数を=で入れたところに名前定義が転換されて後者は、たとえばselectされたところからVBAで名前を書き込むイメージになる。一般的には前者が便利そうだね。

2021-03-26 14:59:45
ほえほえ@DX塾 @hoehoe1234

このように範囲に名前定義をした場合に操作性が問題になる場合があるように思う。データ全体を取るにはよいが個々の列指定の論理指定ができない。 pic.twitter.com/FAmGF6MOhv

2021-03-26 15:16:40
拡大
ほえほえ@DX塾 @hoehoe1234

一括して取得するのは問題がない。うまく取れている。しかし、特定の列の特定の値を書き換えるためには左上を基点として番号始点か、match関数で列番号を取得せねばならない。この使用方法では名前定義の良さが失われてしまっている。 pic.twitter.com/tpPUjjAfxm

2021-03-26 15:19:03
拡大
ほえほえ@DX塾 @hoehoe1234

列を論理指定するために結局、matchかEnumを使うことになる。この場合は動的に位置を取得するmatchよりも静的なEnumのほうがわかりやすいだろう(動的なほうが自由度は高いのでこれも使いどころだろう)。 pic.twitter.com/GQRVkiI6dx

2021-03-26 15:21:25
拡大
ほえほえ@DX塾 @hoehoe1234

名前定義をしてEnumを生成するのであればそもそも名前定義は必要だったのか?という疑問が生じる。なぜならば基点とEnumだけで名前定義と同じ範囲が取得できるからだ。ただし、このためには1つ情報が足りない。それは「何行あるか?」だ。

2021-03-26 15:23:06
ほえほえ@DX塾 @hoehoe1234

これを解決するために作成したのがこの2つの関数となる。これで最終行レンジと行範囲(行数)がわかる。最終行方式なので行の増減にも対応できている。 pic.twitter.com/jMnSooAsXj

2021-03-26 15:26:05
拡大
ほえほえ@DX塾 @hoehoe1234

ただし、行の範囲が変わらない読み取り表であれば、シンプルに範囲をしているする名前定義は「手順である」部分を差し引いても相当にわかりやすいのでそちらを使うケースのほうが多いと思う。列位置も固定であれば1スタートの番号で十分だろうし。

2021-03-26 15:28:48
ほえほえ@DX塾 @hoehoe1234

使用する要素技術としては次のようなものであろうか? ①テーブル ②基点+Enum+レンジオーバラン ③名前定義(範囲指定) ④Matchによる列番号検索 ⑤最終行レンジ等のヘルパー関数群

2021-03-26 15:32:10
ほえほえ@DX塾 @hoehoe1234

実は④のMatchによる動的番号検出は相当によいと思う。Enumの場合は論理定義位置なのでどうしても始点が必要になる。Matchの場合はいきなり物理位置番号が取得できる。余計なことを考えなくて済むのだ。列と項目名を引き数に物理番号(もしくはレンジ)を取得できる。

2021-03-26 15:34:35
ほえほえ@DX塾 @hoehoe1234

さて、この場合の最終物理行番号は取得できるのだが開始番号はどうだろうか?開始物理番号も同じように縦のmatchで取得できる。これで行列の物理番号が取得できることがわかったのでmatch関数ベースの手法も相当に強力だと言える。

2021-03-26 15:36:21
ほえほえ@DX塾 @hoehoe1234

欠点が何点かある(それは他手法も同じだけど)。まずmatchで指定する列と行はどうしても物理位置を書かざるを得ない。しかしこれは「列指定」なので比較的変更に強いだろう。もう一つは列指定が文字列なので実行時まで間違っていることがわからないこと。項目名の変更に弱いと言える。

2021-03-26 15:38:27
ほえほえ@DX塾 @hoehoe1234

これもさしてデメリットとは思えないが項目数が多くなるとやはりEnumの簡潔性とエラーの事前検出に対しては劣ることはいなめない。また、基点のみmatch+enum方式はmatchの良さが失われるような気もする。

2021-03-26 15:41:02
ほえほえ@DX塾 @hoehoe1234

どちらにしろ、「課題と解決方法候補」はある程度明確になったので今週末のほえDX塾で整理しなおしてみたい。問題の定義と解決方法をさぐる手段もホワイトボードに手順を追って解説するのたのしみにしていてください。

2021-03-26 15:42:21