エクセルの神髄
ExcelマクロVBA技術解説

ExcelマクロVBAの問題点と解決策等の技術的解説です
最終更新日:2019-08-20

ExcelマクロVBA技術解説


ExcelマクロVBAの、良くある問題・難度の高い問題に対する技術的解説です。


掲載しているVBAコードは解説用のコードとなっていますので、適宜変更してお使いください。

そのまま使えるサンプル集は、マクロVBAサンプル集をお探しください。
EXCELマクロVBAの実用サンプル集、なるべくそのまま使えるようにVBAコードを書いています。技術的な解説は少なめになっています。技術的な解説は、マクロVBA入門 マクロVBA技術解説ExcelマクロVBAの、良くある問題・難度の高い問題に対する技術的解説です。


プログラミング全般

プログラミングの基本~ロジックの組み立て
プログラミングの基本というとプログラミング言語の基本文法についての解説と思われるかもしれませんがここではプログラミングする上で最も大切な考え方ロジックの組み立て方について解説します本来は言語は問わないのですがVBAのサイトですのでVBAを例に解説します。何かをしようとしたときどのような手順になるか… これが重要です。
実は奥が深いIfステートメント
プログラムと言うものは条件により処理する これにつきます。つまりこの条件が一番問題となります。そしてその記述をするものがIfステートメントになります。If条件式Then 条件式が真(True)の場合の処理 Else 条件式が偽(False)の場合の処理 EndIf 条件式は真(True)か偽(False)を評価する数式または文字列式を指定します。
論理積(And)論理和(Or)と真(True)偽(False)の判定
And演算子Or演算子は2つの数式内のビット単位の比較を行いますそしてIfステートメントのThenはTrueの時と言うよりFalse以外の時に実行されます。何を言っているのか分かりずらいと思いますので順に解説します。Trueとは何かFalseとは何か TrueFalseを数値で表すと
条件式のいろいろな書き方:TrueとFalseの判定とは
If条件式の書き方でVBAプログラムは大きく様相が変わってきますVBAを習い始めは比較演算子で比較した結果が 正しければTrue間違っていればFalse という事で理解するはずです。しかしいろいろなサンプルコードを見ていると「あれっ」比較演算子が無い… というようなIfステートメントやSelectCaseに出くわします。
VBAにおける括弧()の使い方
メソッドやSubやFunctionを呼ぶときに引数を括弧()で囲うのか囲わないのか… 初心者が赤い文法エラーがでて悩むことの一つです。まず基本系から オブジェクト.メソッド引数1,引数2,引数3 Callオブジェクト.メソッド(引数1,引数2,引数3) Set変数=オブジェクト.メソッド(引数1,
VBAにおけるピリオドとカンマとスペースの使い方
VBAの文法の中で、初心者が戸惑うことが多いのが、ピリオド、カンマ、スペース これらの使い方のようです。使い方を間違って、ピリオドを打つべきところにカンマを打ってしまうと、VBAコードが真っ赤になってしまいます。こうなるとビックリしてしまい、冷静に見直すことが出来なくなってしまったりします。
変数とプロシージャーの命名について
VBAを習い始めると、「変数」について学びます、変数に付ける名前を変数名と呼び、若干の規則はあるが、好きな名前を付けて良いと教わります。好きな名前って…ケイコ、ハルカ、アツコ… まあ、それでも良いけど、入れるデータが分かるような名前がよいですね、わかり易い名前を付けましょう。
文字列置換の基本と応用(Replace)
マクロVBAの処理において文字列置換は頻出の処理ですが、これに苦労している初心者の方が多いようです、そこで文字列置換の基本と応用について解説します。・文字列内の空白(半角・全角)を取り除く VBA関数のTrimは前後の空白が削除されるだけです、そこで、Replace関数を使います。
データクレンジングと名寄せ
複数の名簿を突き合わせて一つにする、いわゆる「名寄せ」名寄せを行うためには、その前にデータクレンジングを行う必要があります、データクレンジングとは、データの中から、重複の排除、誤記、表記の揺れの修正などを行い、データの品質を高めることです。データを整形して、扱いやすいデータに変換します。
ForとIfのネストこそがVBAの要点
VBA習得で最も肝心なものは、For文とIf文をしっかりと覚えることです、そして、For文とIf文をネストさせるプログラミング技術の習得です。For~Nextステートメントは、繰り返し処理 If~EndIfステートメントは、条件分岐 つまり、条件により分岐しつつ繰り返し処理を行う。
For Next の使い方いろいろ
VBAの繰り返し処理としては、ForNextまたはDoLoop この二つを最初に学んだのではないでしょうか。プログラムにおける繰り返し処理は、基本中の基本です。特に使用頻度の高いForNextを重点的に学んでいることと思いますが、書き方、使い方は、いくつかのパターンがあります。
複数条件判定を行う時のコツ
多くの条件を判定して処理を決めなければならない場合は多くありますが複数条件判定を行う時のIfステートメントの使い方はいろいろあります。覚えておきたい基本的なIfステートメントの組み立て方法を紹介します。以下では条件1から条件5まであり・全ての条件を満たしている時 ・どれか一つでも条件を満たしている時 ・全ての条件を満たしていない時 これらを行う時のVBA…
VBAの省略可能な記述について
VBAには、省略可能な記述が数多くあります、省略可能な記述とは、書いても書かなくても動作に何の違いもないものになります。VBAのこの記述の自由度は、慣れてしまえば楽なものですが、初心者の方が覚え始める時には、多少混乱することもあると思います。

VBA入門編

マクロとは、VBAとは
エクセル作業に忙殺されて、「どうにかならないものか」そう思って調べるてみると、「VBA」「マクロ」何やら操作を自動化できるようだが、、、VBAとは何?マクロとは何? 以下では、順にVBAとマクロがどのようなものかを簡単に説明していきます。マクロとは エクセルの操作を自動化するものです。
コーディングとデバッグ
効率的なコーディングと、プログラム作成では避けて通れないデバッグについて説明します。また、陥りやすい間違い、よくやってしまう間違い等も説明しておきます。効率的なコーディング モジュールの移動 コール先のSubプロシージャーやFunctionプロシージャーに移りたい場合があります。
ローカルウィンドウの使い方
VBAのエディター、VBEにはいくつかのウィンドウがあります、その中で、ローカルウィンドウの使い方の説明です、これが使えないと、配列やオブジェクトを扱ったVBAのデバッグに困ることになります。サンプルコードは、以下の表を使っています。まずは、配列でのローカルウィンドウの使い方について。
WorksheetFunctionについて
WorksheetFunctionについてです。VBAをやれば、必ず使用することになるでしょう。と言いますか、これを使わないと、エクセルの意味がなくなっちゃいますので。まあ、関数あってのエクセルですし、関数無けりゃ、ただの作文用紙です。
RangeとCellsの深遠
RangeとCells特集にします。今さら…と、あなどるなかれ、結構奥が深いのです。すでに説明した内容もありますが、知っておいた方が良い事、知らなくても困らない事(笑) これらを、まとめてみました。まずは基本 A1セルに"エクセル"と入れる場合。
Offset、Resizeを使いこなそう
OffsetとResizeはエクセルVBAで非常に便利でぜひ使いこなしてもらいたい機能です。OffsetもResizeもRangeオブジェクトのプロパティでRangeオブジェクトを返します。以下ヘルプより Offsetプロパティ 指定された範囲からのオフセットの範囲を表すRangeオブジェクトを返します。
値渡し、参照渡しについて(ByVal,ByRef)
ByValが値渡し、ByRefが参照渡しです。ここまでは、どこにでも書いてありますし、なんとなく理解していても、実際の活用がなかなか出来ない事が多いようです。使用例を通じて、理解して下さい。上記のsample1を実行すると、2,3の順にメッセージ表示されます。
最終行・最終列の取得方法(End,CurrentRegion,SpecialCells,UsedRange)
エクセルの表をVBAで扱う時はデータ部分の先頭から最終行までの開始列から最終列まで処理する事が多いでしょう。開始行や開始列はほとんどの場合見出し行や見出し列の次からになります。単純な話として1行目に見出しがあれば2行目から 1列目に見出しがあれば2列目から ではここで最終行や最終列は
ユーザー定義関数の作り方
マクロを作成して、ボタンで一括処理…それほどではないが、関数だけではちょっと大変、そんな時は、ユーザー定義関数を使ってみましょう。作り方は簡単です。上記は、引数を足し算して返すユーザー定義関数です。FunctionUserFunc Function これは決まり文句です。
セルの値について(Value,Value2,Text)
マクロVBAにおいて、セルの値を操作する事は基本の基本ですが、意外に理解できていない場合が多いようです。Value 指定されたセル範囲の値を表すバリアント型(Variant)の値を設定します。値の取得および設定が可能です。
Excelのバージョンを判断して「名前を付けて保存」
Excel2007以降とExcel2003以前ではExcelのファイルフォーマットが違いますExcel2007以降で2003以前の形式で保存したい場合に問題が発生します。Excel2007以降で普通に97-2003形式で保存するVBAコードを書くとExcel2003以前ではエラーとなってしまいます。
空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)
空白セルの判定は、VBAにおいては頻繁に発生しますが、正しく空白セルを判定する事は以外と難しいものです。そもそも「空白」とはどのような状態なのか… これが、はっきりしない為に何が正しいのかが判然としないことが問題を複雑にしています。以下、A1セルが空白かどうか判定する方法をいくつか紹介します。
空白セルを正しく判定する方法2
空白セルの判定についていろいろな方から意見を頂きましたやはり空白判定は奥が深く結構難しいものとなっています。ここでは各プロパティや関数がセルの状態によって返す値を再確認してみます。元記事は空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula) 上記の記事では
Rangeオブジェクト.Valueの省略について
エクセルVBAを教えていてこれほど多く聞かれる質問はないでしょうRangeやCellsの.Valueは省略したほうが良いか書いた方が良いか当然省略出来ない場合もあればオブジェクトとして扱うために.Valueは書けない場合もあります。ですので結論から言えば書きたければ書けば良いし書きたくなければ書かなくて良い。
ユーザー操作を制限する(Locked,Protect,ScrollArea)
ユーザーにあちこち触られたくない時や、操作していて迷子にならないように、操作できるセルを徹底的に限定してしまおうというものです、無用なセルはクリックもできない、スクロールすることもできなくします。以下の手順になります。セルのロック→シートの保護→ScrollAreaの設定 順に説明します。
シートに数式を設定する時のセル参照の指定方法
シートに計算結果ではなく、計算式を設定する場合の、セル参照の記述方法について解説します、マクロVBAでは多くの場合、計算結果をセルに入れる事が多いのですが、時に計算式を設定する必要があります、その時の、セル参照の記述が以外に面倒なものです。下の表で説明していきます。
標準モジュールとシートモジュールの違い
エクセルVBAを初めての人に教えるとき、まずは標準モジュールを挿入して、そこに書きましょう、と教えます、しかし、時に間違ってシートモジュールに書いてしまう事があります。そういう時に、必ず聞かれるのが、「何が違うんですか?」「どこが違うんですか?」これらになります。
オートフィルタ(AutoFilter)の使い方まとめ
オートフィルタはエクセルの中でもデータ処理において非常に強力なものです、特に大量データの処理には書くことのできない機能となっています。しかし、使い方が難しく、またバージョン違いの影響が大きく、使いずらい物となっていて、問い合わせを受ける事も多いです。
複雑な条件(複数除外等)のオートフィルター(AutoFilter)
オートフィルターはExcelにおいて重要かつ便利な機能ですが、その使い方の理解が不十分な事が多いようです、単純な条件の場合は問題ないのですが、条件が複雑になった途端に書き方で相談を受ける事が多々あります。まず、オートフィルターの一般的な解説としては以下を参考にしたください。
クリップボードを使わないセルのCopy
セルをコピーすることはマクロVBAにおいてもっとも基本的なことですが、セルをコピーすると、クリップボードが使われてしまうので、他の作業との併用時に困ることが多々あります。そこで、クリップボードを使わずに、セルをコピーする方法を考えてみましょう。
Rangeの使い方:最終行まで選択を例に
Rangeの使い方・書き方についてデータ最終行まで選択する場合を例に説明しますRangeの書き方なのでRangeオブジェクトではなくRangeプロパティの解説という事になります。最近続けざまに以下のようなコードを見かけました。Range(_A2_,Range(_A2_).End(xlDown)).Select Range(_A2_,
フルパスをディレクトリ、ファイル名、拡張子に分ける
ファイルのフルパスを、ディレクトリ、ファイル名、拡張子に分けます。FileSystemObjectを使う方法と、VBA関数で分けるVBAのサンプルコードになります。順に、C:\Users\hogehoge\Desktop サンプル.txt サンプル txt と表示されます。
Colorプロパティの設定値一覧
塗りつぶし文字色等々の色指定は結構悩ましいものがありますExcel2003までならColoIndexで56色だけだったので簡単でしたがExcel2007以降はフルカラーがつかえるようになった為色指定が悩ましくなりました。Excelのフルカラーは24ビットカラー(16,777,216色)
VBAを定型文で覚えよう
VBAの書籍を読んでも、ネットの解説記事を読んでも、なかなかVBAをマスター出来ないという場合は、学習方法が間違っているかもしれません。その人に合った学習方法、人それぞれ違うのは当然です。本サイトでも、入門者用として多くのページが存在します。
VBAこれだけは覚えておきたい必須基本例文10
VBAを覚える順序としては・文法から始める方法 ・実践例文から始める方法 どちらが良いとは言い切れませんがもし文法習得に限界を感じているなら実践的な基本例文を暗記することから始めてみると良いでしょう。またVBA習得において・何を目標にやっていけばよいのか ・どの程度出来たらVBAができると言えるのか こんな疑問もでてくるでしょう。
エクセルVBAでのシート指定方法
ExcelマクロのVBAにおいてシート指定は必須になりますがシートの指定方法は何種類かありそれぞれの特徴があります。シート指定方法ごとに使い方を簡単に説明しながらそれぞれの主な使用場面やメリットデメリット等を解説します。インデックス指定 シートのインデックス(順番の数値)で指定する方法になります。

VBA応用編

マクロ作成後に、表位置がずれた場合の対処
マクロの最も不便なところは、セル参照が自動で変更されない事ですね。どういう事かと言うと、ワークシート関数なら、セル位置をずらしても、計算式が自動で変更されます。しかし、マクロの記述は、自動で変更されません。
ExecuteExcel4Macroについて(Excel4.0マクロ)
ExecuteExcel4Macroは、Excel4.0のマクロを実行します。つまり、昔のマクロを使うということです。VBAが使える前にあったものですが、最新バージョンのエクセルでも使用できます。とはいえ、積極的に使うようなものでもないですし、MSもVBAへの移行を勧めています。
再帰呼出しについて(再帰プロシージャー)
再帰プロシージャとは、自分自身を呼び出すプロシージャです。プロシージャが、そのプロシージャ内で自分自身を呼び出すような処理を再帰呼び出しと呼びます。自分自身を呼び出し、終了条件を満たすまで、意図的に無限ループさせます。
文字列でのセル参照と文字列の計算式について(Evaluate,INDIRECT)
セルまたはセル範囲を表す文字列からセル参照する場合と文字列としての計算式から計算する場合です。_Sheet1_の A1に_Sheet2_ B1に_A1_ _Sheet2_の A1に_(2+3)*2_ このように入っている場合に
リボンを非表示、2003以前ならメニューを非表示
Excel2007以降のリボンは幅を取られて邪魔な場合があります特に業務アプリの場合はリボンを消したい事も多いと思います。リボンを消すだけなら消えているリボンを表示する場合はApplication.ExecuteExcel4Macro_SHOW.TOOLBAR(__Ribbon__,
印刷ページ設定の余白をセンチで指定する(CentimetersToPoints)
印刷のページ設定の余白サイズは、自動記録ではApplication.InchesToPointsで記録されます、しかし、ページ設定のダイアログ画面ではセンチで指定しているので、どうもしっくりしません。できれば、余白サイズはセンチで指定したいものです。
文字列としてのプロシージャー名を起動する方法(Run,OnTime)
文字列変数の中にプロシージャー名が入っていて、そのプロシージャーを起動したい場合になります、実際には、そのような構造が良いとは思えませんが、知っていればプログラミングの幅が広がります。使うのは、OnTimeメソッドorRunメソッドになります。
ドキュメントの作成者を取得(GetObject,BuiltinDocumentProperties)
エクスプローラーで表示される作成者を取得したいとの問い合わせを受けたので改めて確認をしました所有者と混同されがちですが全く別のものになりますDOSのDIRで表示できるものは所有者になります。従ってVBAでShell.Applicationで作成者を取得すると書かれているものは全て勘違いをしています。
画像サイズ(横x縦)の取得について
マクロVBAで、画像サイズ(横x縦)ピクセル数を取得する方法についての解説です、画像は種類が多いので、全ての画像に対応しようとすると、かなり面倒になります。このような処理は、私もたびたび使いますので、自身の覚書としての意味もあり掲載します。
文字種(ひらがな、全半角カタカナ、半角英大文字等々)の判定
マクロVBAで、文字の種類を判定する具体的なコードになります、書き方はいろいろありますし、なにより、文字を何の種類に入れるかは決まりがありません。カタカナって、どこからどこまで、カナ記号は含むの このような細部については、使用する時々に合わせて微調整が必要になります。
オブジェクトとプロパティの真実
オブジェクトとプロパティについて、解説をします。対象は、VBA中級以上になると思いますが、初級の方でも、VBAって奥が深いんだなーと感動位は出来ると思います。(笑) ただし、あまり役には立たないかもしれませんので、あしからず。
オブジェクト式について
オブジェクトは、objectであり、物・物体のことです、VBAで何か操作をしようとする対象となるものがオブシェクトです、オブジェクト式とは、オブジェクトを操作しようとしたときに、オブジェクトを特定するための書き方です。ただし「オブジェクト式」という言い方自体は使う事が少ないです。
オブジェクトの探索方法
VBAを書き進めて行くと、どうしてもオブジェクトの扱い時に分からないことがでてきます、何が分からないかというと、オブジェクトの中の目的の要素をどのように指定したら良いのかということです、オブジェクトの中を探索して、目的の要素にたどり着く方法を説明します。ローカルウィンドウを主体に説明します。
条件付きコンパイル(32ビット64ビットの互換性)
条件付きコンパイルはVBAの特定のコードブロックを選択してコンパイルしますVBAの他の部分は無視されます条件付きコンパイルステートメントは実行時ではなくコンパイル時に実行されます。条件に基づいてコンパイルするコードのブロックを指定するには #If...Then...#Elseディレクティブを使用します。
ドキュメントプロパティ(BuiltinDocumentProperties,CustomDocumentProperties)
ブックのドキュメントプロパティには組み込みドキュメントプロパティとユーザー設定のドキュメントプロパティがあります。VBAで扱うときはそれぞれ以下のコレクション・オブジェクトを使いますBuiltinDocumentProperties, CustomDocumentProperties これらは
VBAでファイルを規定のアプリで開く方法
Excelマクロでエクセル以外のファイルを規定のアプリケーションで開く場合の方法について何通りか解説しますVBAでファイルを単純に開くだけの場合についてになります。ファイルを開いた後にそのファイルに対して何らかの操作をしたい場合は対応するアプリケーション毎に個別の対応が必要になりますがWindowsで開けるファイルであり
Excelアドインの作成と登録について
Excelアドインの作成方法と登録方法についての手順と注意点、そして対応するマクロVBAコードについて紹介します。具体的にどんなアドインを作成するかではなく、一般的なアドインの作成と登録についての概要の解説とVBAコードのサンプルになります。
VBAでのタイマー処理(SetTimer,OnTime)
VBAでタイマー処理(一定時間間隔で処理)を行う方法についての解説です。最も一般的な方法はApplication.OnTimeを使う方法になりますが今回の主題としてはWindowsAPIのSetTimerを紹介します。まずはApplication.OnTimeの確認してから次にWindowsAPIのSetTimerを紹介します。
マクロでShift_JIS文字コードか判定する
環境依存文字・機種依存文字をチェックしたいという話は時々聞くことなのですが何をもってして判別するかという事が実はとても難しい問題になります①②もIMEでは[環境依存]と表示されますが通常これが問題になる事はないでしょう。そもそもコンピューターの中は2進数だけで表現されているものでコンピューターの文字とは

Byte配列と文字コード関数について

Applicationを省略できるApplicationのメソッド・プロパティ一覧
Applicationには多数のメソッド・プロパティがありますがこの中にはApplicationを記述しなければならないものとApplicationを省略できるものとが存在します。これらの違いはどこから来るのでしょうか… Applicationの全てのプロパティ・メソッドについてはApplicationのプロパティ
PowerQueryの強力な機能をVBAから利用する方法
Excel2016より標準搭載されたPowerQueryはとても強力な機能ですが使える環境が限られるので頻繁に使われだすのはこれからになると思います。PowerQueryが広く使われだすと色々な使い方が出てくると思います。ここではPowerQueryの優れた機能をVBAから利用する場合の手順を説明したものです。

VBAの制限とエラー対応

入力規則のドロップダウンが消えてしまうマクロ(Shapes内のDrop Down)
シートのShapeを全削除すると、入力規則のリストのドロップダウンが消えてしまいます。入力規則のリストのドロップダウンの設定については、エクセル入門.入力規則.リスト こちらを参照して下さい。入力規則のリストのドロップダウンが消えてしまう具体的なマクロは、以下のようなVBAコードになります。
実行時にトラップ可能なエラー番号一覧と対処
VBA実行時に発生するエラーのうち以下の表にあるエラーはトラップ(捕捉)できるエラーになります。OnErrorステートメントおよびErrオブジェクトのNumberロパティを使用してエラーへの対処を行うことができます。VBAでのエラーとは 上記の結果はイミディエイトウインドウに 13 型が一致しません。
フォルダー・ファイル(ブック)・シートの文字制限
フォルダーの作成、ファイルの作成、ブックの作成、シートの作成、この時に指定できない文字があります、使用可能な文字に制限があります、シートの文字制限について間違った解説が結構見受けられます。フォルダ・ファイル・ブックの文字制限 使用不可文字は以下になります。
Excel2013におけるScreenUpdatingの問題点
今現在Excelの最新バージョンであるExcel2013ですがApplication.ScreenUpdating の動作がそれまでとは違ったものになっています。Microsoftサポートにも掲載されています。Excel2013で複数のブックを開きAppliction.ScreenUpdatingプロパティ使用中にアクティブブックが切り替わるとウィンドウ…
Dir関数の制限について
VBAでフォルダ・ファイルの存在確認や、一覧取得において使われる関数ですが、いくつかの使用上の注意点、制限事項があります。・3桁拡張子の指定時の問題 このように指定した場合、xlsxやxlsmも対象となります。3桁の拡張子を指定した場合は、4桁の拡張子も対象となります。
よくあるVBA実行時エラーの解説と対応
VBAを書き終えて、いざ実行したら意味不明なメッセージが… ここでは初心者向けに、代表的な実行時エラーの解説とその対応について説明します。中級者以上の方は、実行時にトラップ可能なエラー番号一覧と対処 こちらを参考に、エラー対応(OnError等)を実装してください。
Application.Goto使用時の注意
Applicationのメソッドに、Gotoメソッドがあります、Application.Gotoメソッドは、指定ブックの、指定シートの、指定セル範囲を選択します、そのブック・シートがアクティブでなければアクティブにします。最初に、Application.Gotoの一般的な文法説明をします。
ScreenUpdating=False時にエラー停止後にシートが固まったら
VBAでは処理の高速化や画面チラつきを防ぐためにApplication.ScreenUpdating=False これを入れていることが多いと思います。これが入っているとVBAがエラーで停止してそこで「リセット」した場合にExcelシートをクリック出来なくなったりシートタブの切り替えができなくなってしまう場合があります。
標準スタイル違いの問題点:標準フォント複写、列幅をピクセルで合わせる
VBAでスタイルの標準フォントが違うブック間のコピー等の処理では、列幅や行高の違いが問題となる場合があります。列幅や行高は、標準スタイルの1文字分を基本としているためです。上の画像は、標準フォントが違うブックにおいて、同じ列幅20の時の、列幅の表示です。

処理速度対策と配列

配列の使い方について
今回は、配列についての基礎知識をまとめました。配列とは シートのセルを考えて下さい。縦1列だけを取り出した場合は、1次元の配列です。縦横の複数行列を取り出した場合は、2次元の配列です。このようなデータを変数で扱うのが、配列になります。
VBAの配列まとめ(静的配列、動的配列)
VBAで配列を必要とするのは処理速度を上げる為だと言えますそもそもエクセルにはセルの2次元配列であるシートがあります。にもかかわらずVBAの学習を進めると必ず配列が出てきますではなぜVBAで配列必須になるかと言うとセルを使うと処理速度が非常に遅くこれを高速に処理するために配列が必要となるからです。
最終行の判定、Rangeオブジェクトと配列、高速化の為に
最終行の判定 エクセル顧客管理の記事からのスピンオフ記事になります。以前にモジュール「顧客一覧へ登録」において.Cells.SpecialCells(xlLastCell).End(xlUp).Row と Cells(Rows.Count,1).End(xlUp).).Row を紹介しましたが
記述による処理速度の違い
記述の違いで、どの程度処理速度に変化があるかを検証します。どのような記述が処理速度に影響するかという点を分かり易くするために、あえて極端なマクロVBAで検証をしています。※本記事は2013年に書いたものを2019/2に再計測しつつ一部書き直したものです。
速度比較決定版【Range,Cells,Do,For,For Each】
何度も言っているのですが、RangeとCellsでどっちが早いか、とか、DoとForとFor Eachでどれが早いか とか、そもそも、その議論がナンセンスなんです。以下のコードと結果を見て、各自で判断して下さい。巷の議論が、いかに無意味で、実は良く解っていないのだと言う事を、理解してもらいたい。
エクセルVBAのパフォーマンス・処理速度に関するレポート
ExcelのVBAは遅い・重いと良く言われることが多いようですが、VBAが遅い・重いのではなく、その書かれたVBAコードが遅いのです。正しい高速化・速度対策をしたコードなら、それほど遅くはありません。むしろ、巨大なスプレッドシートを扱っている事を考えれば、驚異的なパフォーマンスとも言えるのです。
VBAのFindメソッドの使い方には注意が必要です
vbafindでの検索が極めて多く、Findメソッドは検索からの流入ではトップクラスです、アクセス解析で分かった事ですが正直少し戸惑っています。なぜなら私はFindメソッドをほとんど使いません、Match関数や配列を使って処理したほうが高速かつ確実に動作するからです。
WorksheetFunction.Matchで配列を指定した場合の制限について
WorksheetFunctionでMatchを使いデータ検索する事は良くあります。この時、他の部分の記述との関係で、配列を指定してMatchを行う事があります。以下のようなVBAコードになります。これは正しく動作します。
マクロVBAの高速化・速度対策の具体的手順と検証
マクロVBAが遅い・重いという相談が非常に多いので、遅い・重いマクロVBAを高速化・速度対策する場合の具体的な手順をここに解説・検証します。マクロVBAの速度に関する記事は既にいくつか書いています。特に、以下はぜひお読みください。
動的2次元配列の次元を入れ替えてシートへ出力(Transpose)
動的配列を使い様々な処理をした後にシートへ出力しようとしたとき縦横が違っている為そのまま出力できませんそもそも動的配列の要素数をRedimで変更できるのは最下位の次元のみになります。2次元配列の場合ReDimmyArray(2,10) ReDimmyArray(2,11) これはOKですが
大量データで処理時間がかかる関数の対処方法(WorksheetFunction)
大量データ処理において、一般的な速度対策をやってさえ、時に何時間もかかってしまう事があります、そういう場合でも、多くの場合は何らかの対策があるものです、個別のロジックの記述でこれに対応する一つの有効なマクロVBAコ-ドについて解説します。以下の例で解説します。
大量データにおける処理方法の速度王決定戦
VBAで自動化したが、大量データ処理に時間がかかってしまう… そんな悩みが非常に多いようです、そこで、各種処理方法の速度比較を行い、どの処理方法が最も速いかを検証します。つまり、処理方法の速度王決定戦です。検証する題材としては、最も一般的な集計で行います。
遅い文字列結合を最速処理する方法について
VBAは遅い… よく聞くことですが、確かに普通にコード記述しているととても遅いことがあります、その代表の一つに、文字列結合があります、文字列結合を最速処理する方法について解説します。そもそも文字列結合は、なぜ遅いのか、String型(可変長文字列)についての基礎知識が必要です。
大量VlookupをVBAで高速に処理する方法について
大量データ同士のVlookup処理は、非常に時間のかかる処理となります、マクロVBAで、これを高速に処理する方法について、VBAコードを示し解説します。ワークシート上の関数の場合 シートに関数を入れる場合は、以下を参照してください。【奥義】大量データでの高速VLOOKUP 以下の表で検証します。
Withステートメントのマクロ実行速度と注意点
マクロVBAにおいてWithステートメントはとても重要です、可読性(読みやすさ、理解しやすさ)、実行速度においては、その役割はとても大きいものになります。本記事では、Withステートメントを使うか使わないかでのマクロ実行速度の差に焦点を絞って検証します。
IfステートメントとIIF関数とMax関数の速度比較
VBAの実行速度比較はとても良く読まれている人気記事となっていますがそのほとんどは配列やDictionaryを使った少し高度なVBAでの比較が多くなっています。今回は極めて基本的な大小比較して大きい方を返すという処理においてIfステートメントVBA関数のIIF関数シート関数のMax関数 これらの処理速度を比較検証してみます。

イベント

イベント処理について
ワークブックのイベント VBEの画面です。上図の「プロジェクト」で「ThisWorkbook」を選択し「表示」→「コード」またはF7 ワークブックのイベントはここに記述します。「(General)」と表示されているコンボボックスで「Workbook」を選択すると自動でPrivateSubWorkbook_Open() EndSub が作成されます。
ブックが開いた時に自動実行(Workbook_OpenとAuto_Open)
Excelブックを開いた時にVBAを自動実行させる方法としてWorkbook_OpenとAuto_Open この2通りの方法があります。それぞれの簡単な説明と動作の違いを解説します。Workbook_Open Workbook_Openはブックが開かれたときに起動されるブックのイベントプロシージャーになります。
ブックが閉じる時に自動実行(Workbook_BeforeCloseとAuo_Close)
Excelブックを閉じる時にVBAを自動実行させる方法としてWorkbook_BeforeCloseとAuo_Close この2通りの方法があります。それぞれの簡単な説明と動作の違いを解説します。Workbook_BeforeClose Workbook_Closeはブックが閉じられたときに起動されるブックのイベントプロシージャーになります。

クラス入門

VBAのクラスとは(Class,Property)
VBAを覚えていろいろ作りながらネットで調べたりしているとクラスやらオブジェクト指向やらという言葉に出くわします。いくら言葉を尽くしてもこれらクラスやオブジェクト指向を完全に説明しつくすことは難しいと思われます。オブジェクトとは 操作対象の事ですと説明されたりしますがまずは何かの物体つまりは対象物と理解すれば良いでしょう。
VBAクラスの作り方:列名の入力支援と列移動対応
クラスを使う良さとして、入力支援が使えてコーディングが楽になるという利点があります、列番号をクラスに持てば、列名が候補表示されて非常に便利です。しかし、これを実装するには、かなりの手間がかかります。つまり、クラス作成に手間をかけて、その後を楽にするということになります。
VBAクラスの作り方:列名のプロパティを自動作成する
クラスに列名のプロパティを作成することで、入力支援が使えてコーディングが楽になりますが、列数が多くなればVBAの記述量が増え、コーディングが大変になります。入力支援が使えるのは良いが、その事前準備があまりに大変ではやる気が失せてクラス作るのが面倒になってしまいます。
VBAクラスの作り方:独自Rangeっぽいものを作ってみた
クラスの作成は、標準モジュールで作成していた時とは様相が違い戸惑う部分も多いと思います、それは、初めてVBAに取り組んだ時の戸惑いと同じかもしれません。最初はとにかく慣れることが一番です、細かい文法や機能は、少し慣れてから改めて学んでも遅くはありません。
クラスを使って他ブックのイベントを補足する
VBAでイベントを使う場合は通常はイベントが発生するオブジェクト(ブックやシート等)のモジュールに記載しますつまり各ブックの中にマクロを入れなければなりません。他ブックのイベントを処理するにはWithEventsキーワードを付けた変数宣言を使いますクラスを使って他ブックのイベントを補足する方法について具体的なVBAコードで紹介します。
クラスとイベントとマルチプロセス並列処理
エクセルVBAではマルチスレッドによる並列処理はサポートされていません、つまり通常は順序良く直列に処理していくしかありません。しかし処理時間が多大にかかるような処理も現実には存在しているため、エクセルVBAで並列処理したいという要望も出てきます。
クラスとCallByNameとポリモーフィズム(多態性)
VBAの使い方が進んでいくとクラスを使うようになっていきます。クラスを使うようになるとオブジェクト指向という言葉に出会いオブジェクト指向を学んでいくとポリモーフィズム(多態性)という言葉に出会います。オブジェクト指向における多態性の説明としてはメソッドの呼び出し時にメソッドが属するオブジェクト(クラス)の種類によって呼び出し先の実装コードが選択され
オートフィルタを退避回復するVBAクラス
シートにオートフィルタが適用されていてかつ絞り込みされている場合はVBAでは何かと注意が必要になります。このような場合オートフィルタを解除するかフィルタ絞り込みをクリアして対応している事が多くなります。しかしオートフィルタを解除したりフィルタをクリアしてしまうとそれまでの絞り込み条件が消えてしまい
オートフィルタ退避回復クラスを複数シート対応させるVBAクラス
シートにオートフィルタが適用されていてかつ絞り込みされている場合はVBAは何かと面倒になります。そこでこれに対応するために作成したものがオートフィルタを退避回復するVBAクラス ただしこれはシートが一つしか扱えません。複数シートで使う場合はシートごとにクラスのインスタンスを作成する必要があります。
コレクション(Collection)の並べ替え(Sort)に対応するクラス
オブジェクトを扱う事が多くなってくるとコレクション(Collectionオブジェクト)を使う機会も増えてくると思います。配列やディクショナリー(Dictionary)を使ったほうが良い場合も多くはありますが単純にオブジェクトを保管し順序通り(FIFO)に処理するだけならとても扱いやすい場合もあります。

Excel Application外のオブジェクト

事前バインディングと遅延バインディング(実行時バインディング)
オブジェクトがオブジェクト変数に代入されるとき事前バインディングと遅延バインディング(実行時バインディング)の2通りがあります。バインディングとは バインディングはbindingで縛るとか束ねると言う意味の英単語です。オブジェクトがオブジェクト変数に代入されるときにバインディングと呼ばれる処理を実行します。

Dictionary(ディクショナリー)連想配列の使い方について
エクセル掲示板で、Dictionaryオブジェクトについて簡単な使用例を上げて解説して欲しいです。検討お願いしますm(_ _)m と頂いたので、分かる範囲内で解説します。実際は、私はあまり使う事はありません。

Dictionary(ディクショナリー)のパフォーマンスについて
Dictionary(ディクショナリー)は辞書機能ですこの辞書は重複は許されずキーとデータの2つが存在します今回はこのDictionaryのパフォーマンス(処理速度を)を検証します。Dictionaryの基本的な使い方についてはDictionary(ディクショナリー)連想配列の使い方について こちらを参照してください。


VBAでのInternetExplorer自動操作
VBAでInternetExplorerを操作したくなることがあります。Webのデータを取得したり、リンクをクリックしたりと、これらを自動で行う事が出来ます。使うのは、InternetExplorer というオブジェクトです。

VBAでのSQLの基礎(SQL:Structured Query Language)
SQL(StructuredQueryLanguage:構造化問い合わせ言語)はデータベースの定義や表の操作を行う言語です。データ定義言語であるDDL(datadescriptionlanguage)と データ操作言語であるDML(datamanipulationlanguage)に分けられます。

VBAで正規表現を利用する(RegExp)
正規表現は複雑なパターンマッチングとテキストの検索置換するためのツールです、VBAで正規表現を使う場合はRegExpオブジェクトを使用します、RegExpは、VBScriptに正規表現が用意されているオブジェクトです。メタ文字 メタ文字の一覧です。

VBAでメール送信する(CDO:Microsoft Collaboration Data Objects)
VBAでメール送信する方法はいろいろありますがここではCDO(MicrosoftCollaborationDataObjects)を使っての送信方法を解説します。メール送信はメーラーを使う方法やフリーのDLLを使う方法等もありますがまずはWindowsに標準であるCDOを使ってみましょう。

VBAでのOutlook自動操作
エクセルでメールの送受信する方法はいくつかありますがなかなか面倒ですしサポートされない機能(CDOは送信のみ)があったりと不便な事が多いです。そこでExcelを使っていると言う事は大抵はOutlookもインストールされているはずですのでこれを使ってメールの送受信を行うという選択をすることは自然な流れです。

ADO(ActiveX Data Objects)の使い方の要点
ADOはMicrosoftが提供するデータベースアクセスのためのソフトウェア部品です。OLEDBをActiveXコントロールの形で使えるようにしたプログラミングインターフェースになります。ここでは、ADOを使用したデータベースへの接続方法を解説します。

特殊フォルダの取得(WScript.Shell,SpecialFolders)
デスクトップのフォルダスタートメニューのフォルダ個人用ドキュメントのフォルダなどWindowsの特殊フォルダを取得するにはネイティブのWindowsシェルへのアクセスを提供するWScript.ShellのSpecialFoldersプロパティを使用します。CreateObject関数で作成したWscript.ShellのSpecialFoldersプロパ…

参照設定、CreateObject、オブジェクト式の一覧
VBAでエクセル外のオブジェクトを使うときには事前バインディングと遅延バインディング(実行時バインディング)の2通りがありますこの時それぞれ何を指定したらよいのか指定する文字列が長いので結構探してしまうことが度々あります。そこで自身の覚え書きとしての意味も含めて参照設定CreateObjectのclass

VBAのスクレイピングを簡単楽にしてくれるSelenium
VBAでWebスクレイピングする方法としてIE自動操作がありますがVBA記述が結構面倒になりますもっと簡単にスマートにVBAを書きたいと思ったら…SeleniumBasicを使ってみましょう。SeleniumBasicはエクセルVBAでのWeb閲覧を自動化することを強力かつ簡単に実現してくれます。

VBA+SeleniumBasicで検索順位チェッカー作成
VBAでSeleniumBasicを使って検索順位チェッカーを作ってみます。SEO対策として各キーワードでの検索順位チェックは欠かせませんが簡単に使えてキーワードを大量に指定できる良いツールがなかなかありません。Google検索をスクレイピングすることはGoogle利用規約に反する可能性があります。

VBA+SeleniumBasicで検索順位チェッカー(改)
VBAでSeleniumBasicを使って検索順位チェッカーを作りGoogle検索順位の履歴を管理します既に作成解説したVBA+SeleniumBasicで検索順位チェッカー作成こちらの改訂版になります。Google検索をスクレイピングすることはGoogle利用規約に反する可能性があります。




新着記事NEW ・・・新着記事一覧を見る

Byte配列と文字コード関数について|VBA技術解説(8月20日)
PowerQueryの強力な機能をVBAから利用する方法|VBA技術解説(8月4日)
練習問題31(セル結合を解除して値を埋める)|VBA練習問題(7月30日)
練習問題30(マトリックス→リスト形式)|VBA練習問題(7月25日)
Applicationを省略できるApplicationのメソッド・プロパティ一覧|VBA技術解説(7月22日)
コレクション(Collection)の並べ替え(Sort)に対応するクラス|VBA技術解説(7月20日)
CSVの読み込み方法(ジャグ配列)|VBAサンプル集(7月15日)
その他のExcel機能(グループ化、重複の削除、オートフィル等)|VBA入門(7月14日)
オートフィルタ退避回復クラスを複数シート対応させるVBAクラス|VBA技術解説(7月6日)
オートフィルタを退避回復するVBAクラス|VBA技術解説(7月6日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数とデータ型(Dim)|ExcelマクロVBA入門
6.繰り返し処理(For Next)|ExcelマクロVBA入門
7.マクロって何?VBAって何?|ExcelマクロVBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.ひらがな⇔カタカナの変換|エクセル基本操作
10.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説



  • >
  • >
  • ExcelマクロVBA技術解説

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    なお、掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。




    このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
    本文下部へ