August 03, 2009
Excel VBA スキルアップコレクション
キーワード：
坪崎誠司、Excel、VBA、Tips、技
Excel VBAの高度で実践的な技が示されている本。項目が多いが、技術書は目次が超重要箇所なので、以下にすべて列挙。まぁ、この目次を見れば、Excel VBAを使う人には何が示されていて、どういう傾向の本であるかがわかるだろう。逆に、VBAはおろかExcel自体普段使用しない人にとっては、何のことかわからないと思われる。
- 共通セル範囲を表すRange オブジェクトの取得
- セル範囲の集合を表すRange オブジェクトの取得
- 連想配列を作成
- 動的配列を利用
- シート上へ高速にデータを書出す
- シート上のデータを高速に配列に取込む
- VBA でExcel 関数を使用した方が高速処理できる場合がある
- 時間の差分を計算
- 時間の差分をミリ秒単位で計算
- クリップボードの文字列を操作
- ユーザフォームでカレンダーを使う
- 右クリックメニューに自作マクロを追加
- メニューバーに自作マクロを追加
- ツールバーに自作マクロを登録
- ツールバーを利用してFaceId の一覧を作成
- 右クリックメニューにサブメニューを作成
- メニューバーにサブメニューを作成
- ファイル選択ダイアログを利用
- フォルダ参照ダイアログを利用
- CSV 形式の文字列を分解して配列に格納
- 文字列配列のデータをCSV 形式の文字列にする
- フォルダ内に存在するファイルの一覧を作成
- フォルダ内に存在するサブフォルダの一覧を作成
- テキストファイルを作成
- テキストファイルの内容を読み込む
- フォルダを作成
- ファイル、フォルダを削除
- ファイル、フォルダをコピー
- ファイル、フォルダの存在を調べる
- ファイル操作をWin32 API を利用して高度に実現
- ファイルパスを分解
- ファイルの行数を簡単に調べる
- ファイルを読み取り専用で開き保存せずに閉じる
- ファイルを開くと同時に起動するイベントプロシジャを機能させない
- ファイルを開く際に自動リンク更新を無視
- XLSTART フォルダを利用
- 他のExcel ブックのマクロを実行
- 構造体を定義して利用
- 画面のスクロールをVBA から行う
- VBA 実行中に数秒間処理を止める
- VBA から非同期処理を実行
- VBA から実行した非同期処理の終了を監視
- エクスプローラでフォルダを開く
- エラーを無視
- エラー発生時に処理を分岐
- メッセージボックスをとことん活用
- インプットボックスをとことん活用
- 円周率を取得
- 乱数を使う
- 条件分岐を一行で実現
- 配列を作成せずにリストを使った処理を実現
- 2つの文字列を比較
- グループを利用した折りたたみ機能をVBA から制御
- Excel ファイルに存在するリンクを調べる
- キーボード操作をVBA で実現
- 複数のファイルに存在する個人情報を一度に削除
- VBE をショートカットキーで起動
- 定義されている場所へジャンプ
- 変数や関数の型を調べる
- プログラムの行をショートカットキーで削除/ 追加
- ブック内の全モジュールを対象にした文字列検索
- コードウィンドウをショートカットキーで切り替え
- モジュール内のプロシジャを前後へ行ったり来たり
(目次から抜粋)
この本は、セルの数式を操作する程度のExcel VBAの入門書的なものではなく、VBAの基本はある程度わかる人向けの実践的な内容となっている。これは結構知らないことが多く載っていて、本当に秘伝書みたいな内容だった。
まず、Excel VBAで連想配列が使えるとは思わなかった。これは知らなかったので、なるほどと思った。以下のように定義するようだ。
'連想配列を作成する。連想配列は、Scripting.Dictionaryというオブジェクトを作成するのがポイントらしい。さらに、連想配列の場合は、配列インデックスが任意の型でよいらしい。これも便利だ。
Dim HS As Object
Set HS As CreateObject("Scripting.Dictionary")
'連想配列にデータを格納する。
HS.Add "犬", "Dog"
HS.Add "猫", "Cat"
HS.Add "熊", "Bear"
(中略)
Debug.Print HS.Item("犬")
(pp.017)
あと、結構衝撃的だったが、『 Tips05 シート上へ高速にデータを書き出す』というもの。VBA処理では、数千件というデータをシートの各セルに書き出す、というものをよく実装すると思われるが、その実装方法しだいでパフォーマンスがだいぶ改善するというもの。たいていの人は、画面の自動再描画をオフにする、Application.ScreenUpdatingをFalseにしてパフォーマンス改善まですると思われるが、さらにその一歩先があることはあまり知られていないと思う。自分も知らなかった。それが以下の方法。
Public Sub test3()11行目のコメントに『二次元配列』とあるのに、16行目のコメントには『2次元配列』と示されていたので、そのまま抜粋。
Application.ScreenUpdating = False '画面更新の抑止
Dim lpl As Long, lp2 As Long
Dim dataArr(1000 - 1, 100 - 1) As Variant
' 行数分ループする
For lpl = 1 To 1000
' 行数分ループする
For lp2 = 1 To 100
'"行数+行数"の値を二次元配列に格納する。
dataArr(lpl - 1, lp2 - 1) = lp1 + lp2
Next
Next
'作成した2次元配列をシートに一括書出しする。
Range("A1:CV1000") = dataArr
Application.ScreenUpdating = True '画面更新の再開
End Sub(pp.028-029)
上記の関数は、A1からCV1000の範囲のセルに"行数+列数"の値を書き出す単純な処理である。実際に上記を実行してみたら、驚くほど速かった。本書によれば、普通に入れ子ループの中でセルの値を設定するものよりも、50倍高速であると示されている。2次元配列の値をRangeオブジェクトにセットするのがポイントとなるようだ。
これは単純にすごいなぁと思った。データ件数が万単位のものを入れ子ループ処理をすると、メモリ、CPUが貧弱なPCだと画面がフリーズしそうになる。この方法は知らなかったので、ものすごくへー、そんな方法があるのか!!と感嘆した。また、こういう書き方はネット上にはあまり載っていないと思う。
他にもいろいろと示したいが、それはさすがにネタばれしすぎなので、自重。
Tipsの合間に著者の意見が示されているコラムが載っている。そこもとても勉強になる。以下、『コードの良し悪し』というものから一部抜粋。
”良いプログラム”とは、非常に定義しづらいものです。私もこれまでいくつものプログラムを見る・書くしてきましたが、「可読性」を意識し過ぎて単純なプログラム構文ばかり利用したら「高速性」を失ってしまったとか、プログラムの「高速性」を追及したら「拡張性」がないものになってしまったなど、いくつもの事例を目の当たりにしてきました。”良いプログラム”は必ずしも評価基準をオールAでクリアするものではなく、ケースに応じた”評価バランス”がそこに存在していると思います。D判定がどの程度のものか？にもよるのかなと思った。まぁ、やはり一番の課題は生産性だよね。納期がきついときに、すぐに思いついたコードは、最低限の仕様を満たせるが、可読性や拡張性、保守性が低くなりやすいものであったりする。かといって、可読性、拡張性、保守性までを考慮して自分なりに納得しつつイケているコードを書く時間がないと、どうしようかとジレンマに陥って、結局自分では納得いかない微妙なコードを書くことになる。そして、後でリファクタしようと思っていたら、結局やらなくてその部分でバグったりする（笑）また、生産性を語るときに、見積もりとプログラマーの能力値の関係は切り離せない。
（中略）
現実的には「生産性」という最大の課題もあり、オールA判定されるプログラムの作成は難しいでしょう。せめてD判定のものが一つもない状態のプログラムを目指しましょう。実際の開発現場においては、D判定のものが一つもない状態のプログラムを”良いプログラム”と呼んで良いのだと思います。(pp.052-053)
この本は、Excel VBA本を探していたときに書店で発見した。妙に表紙がかっこいいと思い、中を見てみると、モノクロで玄人っぽいページ構成で、自分の知らない技が多く載っていたので速攻で買った。正直これをものにすれば、2,200円は安いと思う。
自分自身は、Excel VBAを本格的に使うようになったのは、ちょうど1年ほど前だったので、もう1年のキャリアになる。VBAの基本構文はネットでも十分習得できるが、ファイルの入出力などのな定石的な技、アルゴリズムは、あまりまとまって載っているページが少ない。そのため、この本はかなり重宝すると思う。
昔はExcelはただの表計算ソフトだと思っていたけど、関数を多く覚え、そしてマクロを覚え、さらにはVBAまで習得すると、Excel VBAで何でもできそうな感じがしてくる（笑）少なくとも、Excel上で手動でできることはほぼ全部自動化できると思う。そう思うと、Excelはすごく奥が深く、神がかったソフトなんだよなぁと思う。ちなみにExcelを作った神は以下の人。自分もこんなの作れたらなぁ・・・と妄想してもしょうがない。
Excel VBAはプログラミングそのものを考慮すると、玄人プログラマーからしてみたらVBA（笑）って感じかもしれないけど、プログラミング初心者がアルゴリズムの考えを習得するにはよいのではないかなと思う。特に行列位置を指定してセルを操作するというのが結構わかりやすいのではないかと思う。
実践的な技が示されてるスキルアップコレクションシリーズは、他にもいろいろと出版されているようなので、チェックしておきたい。
Excel VBA スキルアップコレクション
読むべき人：
- Excel VBAツールのパフォーマンスを改善したい人
- 体系的に実践的な技を習得したい人
- Excel VBAで何でも仕様を実現できると思っている人
VBAエキスパートクリック☆ 
type5w4_lion at 22:42│コメント(2)│トラックバック(0)
技術本（コンピュータ関連） | 書評総数（日付順）
1. Posted by shocky August 04, 2009 01:11