Excel VBA 基本操作
日本語変数と、Excelの計算式を記述
VBA の変数名には、日本語が使えます。変数を日本語にしておくと、関数やコマンドとの区分が容易で、マクロ記述が格段に判読しやすくなります。コメントをそれほど挿入しなくても、プログラムがどんな処理をしているか、理解できるでしょう。
キー入力時に、半角英数と、日本語を頻繁に切替えるときは、ホーム・ポジションから手を動かさなくてもよいように、[無変換]キーを左親指で押す方法を使うと、スムーズに切替えできます。
下のマクロは、計算を、VBA で処理する方法と、Excel の計算式を VBA で記述する方法の、2つを対比した例です。
一般に、VBA で計算してしまう方が、マクロの記述は簡単で、Excel も重たくなりません。このため、Excel の計算式を VBA で記述するのは、Excelシートで、部分修正して再計算したいなど、明確な目的がある場合に限られるでしょう。
このマクロをダウンロードできます。→money.xls
解説:
変数名には、文字 (英数字、漢字、ひらがな、カタカナ) とアンダスコア (_) を使うことができます。スペースや記号は使えません。変数名の先頭の文字には、数字とアンダスコア (_) は使えません。変数名の長さは、半角で 255 文字以内でなければなりません。
Formula プロパティを Range オブジェクトに指定した場合は、オブジェクトの数式を、A1 形式の表記形式で、コード記述時の言語で設定します。値の取得および設定が可能です。バリアント型 (Variant) の値を使用します。
FormulaLocal プロパティは、コード実行時の言語で設定します。
EntireColumn プロパティは、オブジェクトを返すプロパティです。指定されたセル範囲を含む 1 列または複数の列全体 (Range オブジェクト) を返します。値の取得のみ可能です。
使用例
次の使用例は、アクティブ セルを含む列の先頭セルに値を設定します。この使用例は、ワーク シートから実行してください。
AutoFit メソッドは、対象セル範囲の列の幅や行の高さを内容に合わせて調節します。
expression.AutoFit
expression 必ず指定します。対象となる Range オブジェクトを返すオブジェクト式を指定します。サイズを調節するセル範囲を指定します。1 行または行の範囲、あるいは 1 列または列の範囲を指定する必要があります。それ以外の選択範囲に対してこのメソッドを使うと、エラーが発生します。
Resize プロパティは、直前の Range で指定したセルに対して、サイズを変更したセル範囲 (Range オブジェクト) を返します。
expression.Resize(RowSize, ColumnSize)
expression 必ず指定します。サイズを変更する Range オブジェクトを返すオブジェクト式を指定します。
RowSize と ColumnSize 新しい範囲の行数と列数をそれぞれ指定します。この引数は、正数でないとエラーになります。
上の例では、Resize プロパティを使って、数値で動的に、Range("E3")を左上端とする矩形のセル範囲を指定しています。
OSのバージョンとExcelのバージョンを取得
下記を表示させます。
2000 :5.00
XP :5.01
Vista:6.00
7 :6.01
8 :6.02
Excel 2003 :11.0
Excel 2007 :12.0
Excel 2010 :14.0
Excel 2013 :15.0
Word 2002→10.0
Word 2003→11.0
Word 2007→12.0
Word 2010→14.0 ←13は忌み嫌われた数で欠番?
IE のバージョンを取得も、参照して下さい。
解説:
OperatingSystem プロパティは、現在のオペレーティング システムの名前とバージョンを返します。たとえば、Windows (32 ビット) 4.00 や Macintosh 7.00 を返します。値の取得のみ可能です。文字列型 (String) の値を使用します。
OS が 32ビット版か、64ビット版かの判断を、下記のコードでできるかと思ったのですが、ダメでした。
ググって見たら、WMI Fun !! のサイトで、下記のコードを公開していただいていました。
http://www.wmifun.net/sample/win32_operatingsystem_f.html
http://www.wmifun.net/library/win32_operatingsystem.html
上のコードは、WMI Fun !!管理人 Toh さんのご了解をいただいて転載しています。
WMI Fun !! は、WMI の紹介サイトです。
WMI (Windows Management Instrumentation) は、システムに関する情報を簡単に取り出せるツールで、WMI を使うと、このように、端末情報を取得できるのですね。
Version プロパティを Application オブジェクトに指定した場合、Excel のバージョン番号を返します。値の取得のみ可能です。文字列型 (String) の値を使用します。
データを並び替え
データが、Excelの行の数以内なら、Excelの Sort メソッド が使えます。
しかし、これ以上のデータ件数をソートしたい場合は、一般的なソーソ・ルーチンを使います。下は、「クイックソート」と呼ばれるものです。
下のコードは、よねさんのWordとExcelの小部屋 の
Excel(エクセル) VBA入門:配列の並べ替え
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_array2.html
を、変数名を漢字に変更して、転載させていただきました。
いろいろなソートアルゴリズム←ソートの過程を動画で理解できます!
http://www.ics.kagoshima-u.ac.jp/~fuchida/edu/algorithm/sort-algorithm/top.html
大量データを照合
複数のファイルを照合するとき、数が少ないときは、繰返し処理で照合します。
(参考→Excel関数の VLOOKUP をマクロで)
しかし、件数が多くなると、その処理回数は、掛け算になるので、当然時間がかかります。
例えば、100万件のデータと、20万件のデータを、「繰返し処理」で照合すると、その回数は、
100万件×20万件/2=1,000億回
にもなります。
このようなときの、照合作業の速度を上げる方法を、二つ紹介します。
1.それぞれのデータを、前もって、照合キーで並べておいて、二つの歯車を噛み合わせるように、順次処理で照合する。
2.「連想配列」を使って照合のキーの索引を作成し、キー参照して直接照合する。←私の推奨
ふたつとも、その速度が、飛躍的に改善されて、感激しました。
下のマクロは、マスタとワーク(トランザクション)を、「郵便番号」をキーとして照合して、ワークに存在したデータを、マスタの属性情報として追記しています。
(このサンプルには、業務的な意味は有りません。)
単純照合の処理時間 :01分31秒
ソートして照合の処理時間:00分05秒
連想配列で照合の処理時間:00分05秒
進捗状況が、Excelの左下のステータスバーに表示されます。
この例のマクロとサンプル・データを、ダウンロードできます。
マクロ:matchingVBA03.xls
マスタファイル(2009年の郵便番号データ)ZipCode2009.zip(122,874件)
ワークファイル(2007年と2009年の追加差分データ)ADD0702_0902.zip(2,237件)
データの例として、郵便番号ダウンロードを使いました。
注:キーが数値の場合は、マスタとトランザクション(ワーク)の形式を合わせること!
キーが数値の場合、固定長でソートしたときと、可変長でソートしたときでは、その並び順が変わります。
例:
数値を固定長(頭に空白追加) = 35,77,134,407,515,554,614,735,809
数値を可変長(前詰めのまま) = 134,35,407,515,554,614,735,77,809
このため、照合キーが数値の場合は、照合するそれぞれのファイルのキーの登録方法が違っていると、前後関係が変わってしまい、順次処理で突き合わせ、できません。
配列に登録する時点で、キー部分の桁数の処理方法を、固定長か可変長かのどちらか一方に、統一します。