第6回 和風スパゲティ勉強会「VBAの落とし穴大全」の配布資料と配信アーカイブを共有します。全34品のタイトル・解決策・URLも資料内にリスト化しましたので、復習にお役立てください。

Excelがスピル対応のバージョンか調べる方法を解説します。一発で調べる方法はありませんので、RangeオブジェクトにFormula2プロパティが存在するかで判定します。

オブジェクトに特定のプロパティが存在するかを調べる方法を解説します。一発でこれを行うプロパティは存在しませんので、オブジェクト.プロパティがエラーになるかどうかを調べて判定します。

自シートを参照する「自シート名!」が数式内に入ってしまうと、読みにくくなってしまうだけでなく、ソートすると式が破損するという罠を生んでしまいます。今回はこれを自動でしょう供するマクロを紹介します。

特定の条件を満たす列を削除する方法を解説します。For文でStep-1を利用して最終列から1行ずつ削除していく方法と、Unionメソッドで一つのRangeオブジェクトにしてから一括で削除する方法があります。

書式のコピーにはPasteSpecialが思い浮かびますが、行ごとコピーする場合はAutoFillメソッドが使用できます。クリップボードを介さず安全高速ですので、フィルハンドルのようなコピー処理にはこちらを使用しましょう。

セルの結合解除にはショートカットキーが設定されていません。これをショートカットキーに登録するためにセル結合機能をマクロで再現してみました。複数行実行時に自動で横方向結合する機能も付けましたのでご活用ください。

セルの右下にカーソルを合わせたときに出る✚のコピー機能をオートフィル(フィルハンドル)と呼びます。これは便利なのですがショートカットがないため、マクロで再現してショートカットに登録してみましょう。

AutoFillとFillDownの違いを解説します。どちらも先頭のセルをセル範囲へコピーするメソッドですが、AutoFillはフィルハンドル、FillDownはCtrl+Dと対応しているためAutoFillの方がコピーの種類が豊富です。

先頭セルをセル範囲全体へコピーするCtrl+DやCtrl+Rと同じ処理をVBAで実行するには、FillDown/FillUp/FillRight/FillLeftメソッドを使用します。連続データ、値/書式のみコピーをしたい場合はAutoFIllメソッドを使用します。

Collection内に要素が存在するかチェックするには、標準機能がないため愚直にFor Each文で一致する要素があるかを判定します。DictionaryならExistsメソッド一発なので、出来ればそちらで実装しましょう。

DictionaryはKeyとItemをセットで持っておく仕組みですが、Itemを無視してKeyだけを一次元配列として使用する方法があります。このKeyのみDictionaryはCollectionの上位互換なので積極的に活用しましょう。

WorksheetFunction(以下WF)に渡せる一次元配列には上限があり、65,537を超える件数の一次元配列を正しく受け取れません。二次元配列やRangeオブジェクトを渡せば正しく動くので一次元配列のみお気を付けください。

CollectionはIndexによるアクセスが遅いという特徴があり、ForEach文なら一瞬でもFor文だと数秒～数十秒かかってしまうことがあります。これはデータ件数が増えると加速度的に遅くなるので注意しましょう。

処理が超速なことで知られるDictionaryですが、実はデータ件数が増えすぎると急に遅くなるという欠点もあります。件数の二乗に比例して処理が遅くなる傾向があるため、Dictionaryを分割するなどで対応します。

現在作業中のブックを保存する際、上書き保存ではなく別名で保存しておきたい場面があります。ブック名_yyyymmddhhmm_作業メモという名称でファイルのバックアップを取るマクロを紹介します。

デスクトップのフォルダパスを取得する方法を解説します。いくつか方法がありますが、環境変数を取得するEnviron関数を使用するのが簡単です。他にWScript.Shellオブジェクトを使用する方法もあります。

せっかくの同形式グラフなのに、軸上限・軸下限・目盛り幅が違うとグラフ間で比較ができませんので、これを自動設定するマクロを紹介します。120%上限のグラフを100%上限に一括変更するときにも活用できます。

全マクロブックで共有するような自作汎用関数(ライブラリ)を、ワンタッチでアクティブブックにコピーするマクロを紹介します。Scripting Runtimeの参照もついでに実行しているため、FSOやDictionaryも即使用可能です。

DictionaryやFSOを使用する際As FileSystemObject/Dictionaryとデータ型を明示するにはScripting Runtimeの設定が必要です。設定すれば選択肢入力が活用できるため、自分でコードを書く場合は必ず設定を行いましょう。

Scripting Runtimeの設定をVBAから行う場合はVBProject.References.AddFromGuidメソッドを利用します。Scripting Runtime以外のライブラリもIDを調べれば自動化できますので流用してみてください。

ブックに標準モジュールを挿入、ソースコードを書き替えるなど、マクロの作成や更新を自動化する際にに使用する基本コードを汎用関数としてまとめました。ご自由にお持ち帰りください。

CollectionとDictionaryの違いについて解説します。どちらも「複数のデータをまとめて持つ仕組み」で、KeyとItemをペアで格納することができますが、Dictionaryが完全上位互換と言ってよい性能を持っています。

Collectionを配列に変換する方法を解説します。標準機能にはないため、愚直に全要素を配列へ出力して変換します。CollectionはFor文で回すと非常に遅い性質があるため、配列への代入はForEach文で行ってください。

Collectionの中身をセルに出力する方法を解説します。For Each文を使って各要素を出力する方法と、一旦二次元配列に変換してから一括出力する方法があります。後者を汎用関数化しておくと便利です。

セルの行高を設定するにはRangeオブジェクトのRowHeightプロパティ、列幅にはRangeオブジェクトのColumnWidthプロパティを使用します。セルの値に合わせて自動調整を行う場合はAutoFitメソッドを使用します。

和風スパゲティ勉強会 第6回のテーマは「VBAの落とし穴大全」ということで、知らないとハマってしまうVBAの初見殺し30品を一挙に紹介します。マニアックなバグに対策するためにマニアックな仕様を覚えてしまいましょう。

知らずに落ちると抜け出せなくなるVBAの落とし穴です。 Killステートメントを実行したら関係のないファイルも削除された ファイル名を半角に変換した際、Killステートメントが暴発する 環境依存文字を用いたファイルの削除が安定しない あたりにお悩みの方は…

ブックを開いたまま移動したり、名前を変更する方法を解説します。これをストレートに実行する方法はありませんので、対象のブックを名前を付けて保存したあと元のブックを削除という手順で実行します。

ExcelVBAで使用できる変数の型(データ型)の一覧と、各変数を宣言・使用するサンプルコードを解説します。Long(整数)、Double(小数)、String(文字列)、Range(セル)、Worksheet(シート)など主要な型を覚えておきましょう。