見出し画像

第53回 配列入門！Array&Collection&Dictionary欲張りセット

しゃあ＠やっぱりVBAが好き

今後のスケジュールはコチラ
投げ銭ページはコチラ

登壇者：和風スパ
日時　：2023年3月18日 20時
テーマ：配列入門！Array&Collection&Dictionary欲張りセット

はじめに

自己紹介

英語で書いたら混乱しそうな変数名

本日の内容

今夜も安定のExcelスライドショー

配列とは

VBAを使うに至った過程で配列のとらえ方が違う。
ExcelVBAに限って言えば配列はWorksheetで事足りる。だから配列を使っていないVBAerが多い。

変数とは

リンゴの値段を計算するコード

実際にデータを保管している変数は、リンゴの売上額。
Rとかis100個売り上げた日があった などは、プログラムを動かすための道具に過ぎない。

変数の種類（概念的な種類）

つまり変数にも種類がある。あくまで概念的な種類であってVBAの規約ではない。主に使うものは上の3つ。データを保管する箱はシート上のセルで十分なのだが、主に配列にする変数は一番下のデータを保管しておく変数である。

配列の使いどころ

変数の数が動的とは


商品名が何種類あるか分からない。

んじゃDim 変数宣言をループで回せばいいんじゃない？
※このプログラムは動かないけど、やりたいことのイメージはこんな感じ。

これをExcelシートでイメージするとこんな感じ。
商品コードの数だけ行を確保し、その右列で必要な計算を行う。

2019までは事前に商品コードを記載するか、VBAで実施するしかなかったが2021以降のスピル機能でVBA使うことなく出力できるようになっている。配列数式はもともとあったがそれを出力に使える様にしたのがスピル。
こういうこともあってさらに配列の必要性が低くなってしまった。

ワークシートで配列をイメージ

※FxはWorksheetFunction

下の表から商品コード毎の件数、数量、金額の合計をセルに足し込んでいくプログラム。

結果、これを作る。

そもそもExcelを利用していたユーザはフィルタしたりSUMIFやCOUNTIFSで十分なのです。なので配列必要ない・・・ってなる。
でも今日はそこをあえて配列使って勉強していく。

配列に組み合わせると有用なテクニック

Enum


例えば商品コードはCells(R,7）とかCells(R,"G")と記述されることが多いが、7とかGが何を意味してるか分かりにくい。こんな時使うのがEnum

最初に=2と書くとあとは連番になる

汎用関数化

前前会で説明した内容
これは覚えて！

これでコード中にWorksheetFunctionって長々と書く必要がない。

Worksheet

Array/Collection/Dictionaryと書いたが、実はWorksheetを含めて４大配列。
地道に加算するのではなく、CountIfで計算してしまうコード。

毎ループ加算する人もいれば、CountIfしかしたことない人もいるのがExcelVBAerの面白いところ。
Worksheetを配列として使うことがまず第一。

Formulaを使う


まずは商品個コードだけを並べる

その後 はセルに数式を記入し、それを値化する。

範囲.FormulaプロパティにExcel数式を記入

一番上のセルアドレスを指定した計算式を使うことで指定した範囲にはその行用の計算式に変換されて記入される。

途中経過
最終結果

しかもこれはめちゃめちゃ処理速度が速い！
理由はセルへの書き込む回数が少ないから。（１列毎に２回）
これが使えればArrayはいらない！

Worksheet/Array/Collection/Dictionary

今日はこれらの概念の説明、良いところ悪いところを主に説明。

Worksheetの特徴

「目に見える」というのが非常にいい！
配列がないと記述できない言語と違って、このWorksheetが付属しているというのが最強！

WorksheetをVBAで扱うテクニック４選

イミディエイトで.Select使うと対象セルが視認できる

WorksheetをVBA用の配列として扱う

新規シートを作業用シート（高機能配列）として用意する
最後はシートを削除して終わり

開発中はこのDeleteをコメントアウトしておくことにより、配列に演算結果がのこる。これはデバッグ効率の向上につながる。

Worksheetの弱点（遅い、１次元配列）

Arrayの特徴

長所１つに欠点多数！

Array必修コード３選

必修コード１

最初はVariantで良いですよ

必修コード2

必修コード3


金額を計算する

Arrayは橋渡し役、Transpose関数の利用

サンプル
１次元配列はセルへ直接書き込めない

これはNG
Tranporseを使うと２次元配列になるのでこれはOK

Collectionの特徴

Dictionaryとの違いは？・・・あんま違いない。
DictionaryはCollectionができないことをできる様にしたものなので、Dictionary使えればCollectionは出番がない。

Collection.Addが便利

.Addで要素が追加できるのが素晴らしい。Worksheetだと行番号などの変数用意してコントロールする必要があるが、Collectionはそれ不要。

Keyの指定で結果（登録データ）を抽出

KeyからItemを抽出するのが超速

これらを解決してくれるのがDictionary

Dictionaryの特徴

CollectionにはないExistsプロパティがある

Dictionaryの基本コード


Dictionaryで重複なしリストの作成

Keyだけ欲しいのでItemはNull文字
コードの結果

商品コードと商品名を抽出

短縮記法（.Addを省略）

DictionaryでCountIF

DictionaryでSumif

CountIf版と違うのは、＋１じゃなくて数量足しているだけ

ツール完成版

必要なDicは商品コードを除いた５つ
全部のDicにAdd。Addをするタイミングは全Dicで合わせる必要がある。
CountIf、Sumif、合計金額の計算は超楽
金額/件は全部読み終わってからFor Eachで前Keyに対して実施
最後に汎用関数で出力する
コードをシート関数で生成するテクニック

使ったファイル


今日のちゅんちゅん日誌

こちら

和風スパゲティのレシピ ブログ

リンク


しゃあのココナラリンクはこちら

いいなと思ったら応援しよう！