私のPersonal.xlsへようこそ！
このページでは、ちょっと変わったマクロを取り上げています。中には実用的なものもあるかな？（笑）
以下のマクロはHTML文書ではなくDOSのテキストファイルにあります。環境によっては正しく表示できないかもしれません。ごめんなさい。また、基本的にExcel95以外の環境では動作しません。間違いもあるかもしれません。これらのマクロは自由に使用、改変することができます。ただし、原文のままでの複製物の配布、転載は禁止します。原文のままでのアプリケーションへの組み込みは、以下の条件を充たせば、許可いたします。
1. マクロコードの機能の直接的な利用を目的としていること。
2. アプリケーションの配布先が特定できること。
セルに m^^3 と入力してマクロを実行すると、一括して3を上付き文字にできるというマクロです。比較的容易に独自のコマンドと処理を定義できるように作ってあるので、マクロがわかる方には楽しめるかも。実用性は別としても。
=A1 というセル参照を、=$A$1 =$A1 =A$1 に一括変換するマクロです。たいていの場合、入力し直した方が早いかも。
特に役に立つマクロではありません。Excelの座標の基準値の情報は私が知る限り非公開です。なんとなく解けそうなんですけどね・・・
1.23 という数値を文字列化して、23 の部分を上付き文字にするマクロです。小数点の位置もそろえます。
上から、A, A, B, C, C ... という値があったら、Aの行は白、Bの行は青、Cの行は白・・・と交互に色を付けます。データの切れ目がわかりにくいときに使うと便利かも。
11月 9日, 11月10日 ... というように、日付数字がそろうように表示形式を設定します。おなじみの処理ですが、多くの表示形式に対応している点に特徴があります。書式が決まっているのであれば、専用のマクロを作成する方が簡単で良いでしょう。
123.456 という数値に対して、有効桁数が4桁であれば、123.5 となるように表示形式を設定します。小数点以下の桁数を指定することで、小数点位置をそろえることができます。
1.23, 1.234, 2 ... といった数値に対して小数点位置がそろうように表示形式を設定します。整数の場合、小数点は付けません。0.??? などのユーザ定義の表示形式が増えるので適当に削除してください。
一度引いた罫線の色を一括して変更します。あまり使い道はないかも。
登録したセル書式をショートカットメニューを使ってコピー貼り付けするツールです。スタイル機能と同じようなものですが、枠罫線だけを有効にします。スケジュール表の色分けに使っていました。
Excel 質問箱のログを NIFTY ログ形式に変換するときに使った関数です。投稿者名から ID を生成しました。
CDate 関数 は文字列を可能な限り日付値に変換しますが、それでは都合が悪いという場合もあります。この関数では yyyy/m/d の形式だけを有効にしてみました。年の 1 桁および 2 桁入力時には独自の変換ルールを適用します。
四捨五入、五捨六入、JIS 丸め などができます。Access や VB でも使えると思います。文字列化してから計算しているため、速度は遅いです。
Log 関数を使って桁位置を取得、整数化して丸めてみました。Access や VB でも使えると思います。試してみたら Excel のRound 関数を呼び出すより 20 倍高速でした。
丸める桁の後ろがちょうど5のときに、より近い偶数に丸めるという方法です。文字列化して処理しています。
123.456 という数値に対して、有効桁数が4桁であれば、123.5 という文字列に変換する関数です。
1-1-1 という文字列の数字部分を2桁にして 01-01-01 と変換します。段落番号を並び替えるときなどに使います。
LARGE関数は上位n番目の値を返しますが、この関数は、上位n個のインデックス番号の配列を返します。順位の基準になる列とは異なる列の値を取得したい場合に使います。
セル書式で均等割付すると (株) などの部分も ( 株 ) と文字間が広くなります。この関数は( )で囲んだ部分には空白を挿入しません。
ワークシート関数でも計算できますが。
ある期間の日曜日の数などを計算できます。
例えば、ある期間に4月1日が何回あるか計算する関数です。
月数の足し算引き算をする関数です。5月31日の1月後は6月30日に補正します。
DATEDIF関数には受け入れがたい仕様があります。詳しくは「こまったExcel95」をご覧ください。この関数では期待される数を返すようにしました。また、1900年以前の日付にも対応しているので、長寿の方の年齢計算に利用できます。
フィルタ範囲に対応したSUMIF関数です。手に取るようにわかる・・・遅さです。（苦笑）
VBAには符号なし整数型がありません。この関数を使うとLong型を符号なし整数型と見なし、Double型の数値に変換できます。逆の変換関数もあります。
特に役立つものではありませんが、この関数を使うと、例えば、0.1という数値が内部でどのように表現されているか知ることができます。計算誤差の理由を感得できるかも。
セル範囲の文字列をすべて結合する関数です。
文字列を指定された区切り文字で分割し、配列を作成する関数です。カンマで区切ったり、ラインフィードで区切ったり、結構、応用範囲が広いです。
例えば「Sheet1からSheet5までのシート」という指定方法ができます。あまり必要ないかも。
再計算されない状況でも、強制的に再計算を行うマクロです。ExcelVBAって、こういうのが意外と用意されていないんですよね。
レコード・データ処理
データの各行に見出し行を挿入するマクロです。例えば、印刷して1件毎に切り離したいときに使います。
クロス集計表を一覧形式に変換します。必要になったときにその場で書いても、大した手間ではありませんが。
1つの表を複数コピーし、別々に追加、変更したものを、また1つにまとめるためのマクロです。変更の履歴も作成します。
グループ集計のためのマクロです。汎用性があるので、これを利用して短時間で集計処理を作成できます。
グループ集計のためのマクロです。汎用性があるので、これを利用して短時間で集計処理を作成できます。
1行複数明細の表と1行1明細の表を変換するマクロです。
例えば、複数の数値の中で合計が100になる組み合わせを検索するマクロです。基本的には1つ1つ調べて行く方法なのですが、できるだけ無駄な検索はしないように工夫しています。
入力規制のリスト入力を利用するサンプルです。複数のリストを連動させて、絞り込みを行います。
数式を使って月間カレンダーを作成します。作成後に年と月を変更することができます。
1列1日のカレンダーのサンプルです。作業グループのスケジュール表に使っていました。
CommandBarのComboBoxControlを使ったサンプルです。ツールバーに文字列を入力してA列を検索します。
UsedRangeは書式の設定されたセルも認識しますが、このマクロは書式とは関係なく、実際に値が入っている最後のセルを選択します。ただし、空文字列(')のセルは検索できません。
例えば、選択範囲の2行目から3行ずつ1行間隔で選択する、といったことができます。複数セル範囲の選択には意外に時間がかかるので、ステータスバーに進捗を表示するようにしました。
データ入力時だけでなく、クリアやコピー貼り付けのときにもデータチェックを行うサンプルです。でも、ちょっと無理筋ですので、おすすめはしません。
[編集]-[検索]を行い、見つかったセルをすべて選択します。処理の中断にも対応しています。
保護シートでTabキーでセル移動するサンプルです。マクロは使いません。
保護シートでEnterキーでセル移動するサンプルです。Enterを押したらTabをSendKeysします。
Enterキーでセル移動するサンプルです。移動セルを自由に設定できます。IMEの自動設定も行いますが、NEC機では動かないそうです。
Enterキーでセル移動するサンプルです。列に対して移動するかしないかを設定します。IMEの自動設定も行いますが、NEC機では動かないそうです。
参照元トレースは、なぜか1つのセルに対してしか実行できません。このマクロはそれを繰り返しているだけです。
シートの一覧を表示し、指定されたシートをアクティブにします。シート選択時の自動実行マクロには対応していません。
アクティブセルを数式で参照しているセルのオブジェクト配列を作成するマクロです。用途は・・・忘れました。（笑）
ロックが設定されていないセルを選択するマクロです。ロックの設定状況を確認するのに便利かも。非保護セルのコピーやクリアをしたいときは、範囲に名前を付けておくと、それ以降は簡単に選択できます。
選択解除するマクロです。なぜかExcelには伝統的にこの機能がないようです。(選択範囲-指定範囲)が選択解除で、(指定範囲-選択範囲)が選択範囲以外の選択になるので、2つのマクロはほとんど同じコードになりました。
=""という数式をコピーして値貼り付けすると、空白セルではなく、空文字列（長さ0の文字列）が入っているセルになります。空文字列のセルを空白セルに即、変換する方法はないようです。
このマクロでは、セル単位にクリアする方法、区切り文字を使う方法、置換を使う方法を試してみました。他にはオートフィルタで空白セルをフィルタしてクリアする方法などがあります。
通常、1回の操作でコピーできるのは1つのセル範囲か、一定の規則性を持った複数セル範囲です。このマクロでは任意の複数範囲をそのままの位置関係でコピー貼り付けします。同形式の表の間で特定のセルだけをコピーしたい場合に、1回の操作で済むので便利です。
上の複数セル範囲コピーの応用です。セル選択の機能を使い定数セルの範囲だけを取得し、コピーします。
表示形式の一覧の取得が、なぜかマクロではできません。多すぎる表示形式が原因であるトラブルを結構聞きますので、非常に不便です。このマクロではダイアログボックスから1つ1つ表示形式を取得しています。大量にユーザ定義してあると時間がかかります。
シンプルな操作が特徴です。Ctrl+矢印キーまたはAlt+矢印キーで線を引き、もう一度上書きすると線が消えるというものです。通常罫線と外枠線の2種類だけがあり、それぞれに線種を設定できます。
Excel97がセットアップしてある環境では線種の設定が動作しません。CreateObject("Excel.Sheet")の動作がExcel97では変更されているためです。
スタイルの一覧を表示し、一括削除することができます。
これは単純にどちらかに変換するだけのものです。英数字と一部の記号は半角、それ以外はすべて全角にするという処理もありますね。
行の間を空けるマクロです。セルの挿入や削除は、しばしばバグに関係するので、できれば避けたい処理ですが・・・
上のマクロはExcel97では無限ループになるというご指摘をいただきました。ありがとうございます！ 一部変更してみました。サポート情報にもこの仕様変更についての情報がありました。私のせいではなかったわけです。（笑） まあ、変更されそうな部分ではありましたが。
重複データがあれば、その行番号を書き出します。
フィルタオプションの設定を使った、重複データの除去です。
並べ替えて二分探索するという方法です。コードはやや煩雑ですが、性能は良好です。
ひらがなのデータから、対応する、あ、か、さ、た・・・の文字を取得します。そしてオートフィルタであ行のデータだけを抽出しようというねらいです。マクロではサンプルシートを作成しているだけで、実際にはワークシート関数と名前定義を使います。
オートフィルタを利用するサンプルです。
オートフィルタを利用するサンプルです。
フィルタオプションの設定を利用するサンプルです。ShowAllDataが動作しないバグを回避するために、変なコードを書いています。（苦笑） その場にフィルタ表示させるのは、できるだけ避けた方がよいでしょう。
アクティブセルの値でオートフィルタする機能は、組み込みのツールバーボタンにもあるので（分類の「データ」の中）、たいていはそれで十分でしょう。
円グラフのパターンはグラフ毎に自動的に設定されてしまいます。このマクロではデータ毎にあらかじめ決めておいたパターンを設定します。
グラフ内部の位置やサイズは自動的に調整されるため、複数のグラフのレイアウトを厳密にそろえるのは困難です。このマクロでは同じ設定を数回繰り返していますが、これでも完全ではないでしょう。
少し変更しました。Lotus1-2-3にあってExcelにはない機能です。そのせいか「どうやるのか？」という質問がときどきあります。Excel97では付属のSamples.xlsというファイルに同様のマクロがあります。
知らなかったのですが、Excel 97 のフリーフォームでは Alt キーでセルの枠線に合わせることができないのですね。以前のバージョンではできたのに……
ダイアログシートの作成時に使う補助ツールです。ListFillRangeと同じような機能ですが、選択項目が固定で、作業用のワークシートを使うまでもない、という場合に使います。
オブジェクトの下のセルに合わせるだけなら単純ですが、このマクロでは一番近いセルに合わせるようにしています。アバウトに作成しておいて一括して整列させる、という使い方ができます。
1つの図形オブジェクトを同じ位置にコピーしたいときは、オブジェクトの左上位置のセルを選択して貼り付ければ良いのですが、オブジェクトが複数の場合は、どれが左上のセルかわかりにくく、面倒です。このマクロはその辺をやってくれます。
フィルタ方法にもいろいろありますが、これはMatch関数で最初と最後のデータを見つけています。
リストボックスの表示状態は操作できないので、SendKeysで一度最終項目を選択してからListIndexを設定するようにしています。あまり使いたくない「技」です。（苦笑）
日付を入力するためのダイアログシートです。年のフィールドにスピンボタンを使っています。表示は遅いです。
日付を入力するためのダイアログシートです。表示は遅いです。
これはFAQでもありますね。ダイアログボックスを次々に表示するサンプルです。
ダイアログシートではEnterキーでのフォーカス移動はできません。これは無理矢理な方法で、標準ボタンを利用しています。それでも、データ入力の手間が少しでも減らせるのでしたら検討してみても良いかも。
InputBoxだけでレコード入力するという、ちょっと変わったものです。
なぜかマクロを使わないとできない、という処理です。
指定された 1 ページの行数と段組み数を使って、データ移動と改ページ設定を行います。細かいレイアウトやページ設定は後から行ってください。
奇数ページと偶数ページに分けて印刷する方法の応用です。綴じ代のための余白を自動的に設定します。余白を変更することで自動改ページ位置の変更が発生しないかチェックしていますが、このチェックに結構時間がかかってしまいます。
Excelだけでラベル印刷を行うサンプルです。あらかじめ作成した1ページ分のワークシートを繰り返しコピーして印刷用のブックを作成し、印刷するという方法です。
ワークシートの一覧をリストボックスに表示し、複数選択されたワークシートを実際に選択してプレビューします。複数選択リストボックスのサンプルです。