Excel VBA

ホームページのトップに戻る目次

0.初めの一歩  1.デバッグと動作確認  2.基本操作  3.基本文法  4.文字列操作  5.セル操作  6.ワークシート操作  7.ファイル操作  8.日付・時刻・時間  9.グラフ操作  10.図形を扱う  11.色操作  12.確率  13.インターネットを操る  14.ユーザフォーム  15.文章を読み上げ  16.他のプログラムとの連係  17.連想配列★を使って検索を高速化する  18.部品表展開  19.株取引に VBA を使う  20.html から ePub を作成  索引

ブロック解除  ここでは、Excel VBA の、よく使えそうな事例、お決まりのパターンを、気が付いた都度、掲載して行きます。いくつかの記述方法が有る場合は、対比して紹介します。
 なお、Excel VBA の お役立ちサイト や テキスト は、Excelマクロ(VBA) のところで紹介しています。

注1:このサイトでダウンロードできる VBA サンプル・ファイルを実行するときには、右図のように、ファイルのプロパティの「全般」タブで、ブロックを解除して下さい。

注2:マクロ起動のボタンを押したとき「このブックでマクロが使用できないか、またはすべてのマクロが無効になっている可能性があります。」マクロの登録が間違っていないか確認しましょう。


VBA はプログラム言語
├→単語を覚える  △
├→フレーズを覚える○←自動生成機能を活用する
└→場面を覚える  ◎→本やネットで調べる
              ↑
            場面を切り分ける
              ↑
やりたいこと(場面)は限られる┘

 英語でも、オリジナルで「英作文」することは難しくても、「英文解釈」なら、辞書を使えば、比較的簡単ですよね。
 そして、既存の英文例を組み合わせてアレンジして「英作文」した経験があるでしょう。
 プログラム言語でも、同じです。
 英語の場合、必要な単語数は、数千語ですが、プログラム言語は、数百語なので、単語習得という点でも、はるかに容易?です。

 Excel VBAプログラムの作成支援 の項も参照下さい。
 VBA_VariableProcedureXref(変数のクロスリファレンス)は、私の作品ですが、役立つと思いますよ。


索引

詳細目次

信頼できる場所
初めの一歩
Excel2013でマクロを使う

Excel2010でマクロを使う
 ・Excel2010の設定
 ・Excel2010で、アドインを使えるようにする
 ・Excel2010で、マクロを書く
 ・Excel2010で、マクロを開く
 ・Excel2010の、マクロのセキュリティ設定

Excel2007でマクロを使う
 ・Excel2007の、マクロの設定
 ・Excel2007で、マクロを開く
 ・Excel2007で、マクロを書く

VBE の設定と使い方
 ・ツール・バーの設定
 ・VBE のオプション
 ・VBE の使い方
 ・モジュール名を変更

 Excel の上部のメニューには、標準で「開発」が有りません。このため
[ ファイル ] タブで、[ オプション] → [ リボンのカスタマイズ] に移動します。
[リボンのユーザー設定] で [メイン タブ] をクリックして、下の [開発] チェック ボックスをオンにします。

 「信頼できる場所」に追加
自分のマクロで扱うファイルを保存するフォルダを、「信頼できる場所」に追加しておくと、ファイルを開いた時に、いちいちセキュリティ警告がでたり、ファイルを開けなかったりすることがなくなります。

 ファイルのプロパティ「全般」で「許可する」にチェックを入れる
2022年からインターネットで入手した VBAマクロは、Office アプリ上の手続きでは実行できなくなりました。
 VBAマクロを実行する場合は、保存したファイルにつけられている「Mark of the Web」(MOTW)を解除する必要があります。
 「MOTW」とは、インターネットで入手したファイルに Windows が、NTFS システムの機能「代替データストリーム」(ADS:Alternate Data Stream)で追加する属性です。
 FAT32 でフォーマットされたディスクでは「MOTW」が無いため、VBAマクロをそのまま実行できます。
セキュリティ Excel 2010での設定
Excel 2007での設定


デバッグと動作確認

分かりやすいコードを書く
 ・変数は、宣言して使う
 ・変数は、全角文字を使う
 ・プロシジャーを細分する
 ・分かりやすいコメントを入れる
デバッグ
 ・実行前にデバッグを
 ・ステップ インを使う
 ・デバッグ用のコードを埋め込む
 ・ウオッチ式を使う
動作確認
 ・マクロ的な観点でチェック
 ・ミクロ的な観点でチェック

基本操作
日本語変数と、Excelの計算式を記述
OSのバージョンとExcelのバージョンを取得
IE のバージョンを取得
データを並び替え
大量データを照合
ソートして順位(ランク)を設定
二次元配列ソート(Access データベースをシーケンシャルに更新する)
連想配列を使って検索を高速化する★
 ・ホームページから写真リンクを取得
 ・英文テキストから英単語帳を作成
 ・シーザー暗号(換字暗号)
 ・文字の出現頻度を数える
 ・品目マスタと照合
 ・Dictionary オブジェクトと Collection オブジェクト

基本文法
Select Case ステートメントを使った分岐

ブックを開いた時に実行(イベント)
ブックを閉じる時に実行(イベント)
MouseMove イベント
2種類のボタン
「フォーム」の「オプション・ボタン」を使う
連続数を配列に割り当てるための算術★
戻り値が複数の関数を作る(ユーザ定義型)
 ・「ファイルを開くダイアログ」で指定
 ・Excelのセルをポイントして指定

クリップボードを扱う

文字列操作
Excel & VBAの文字列処理
 ・文字列操作関数
 ・検索した文字列の出現回数
 ・活用事例
 ・検索した文字列の出現回数と出現位置
 ・1バイト2バイト混在文字列から固定文字数を抽出
 ・全角文字数をカウント
 ・LanguageSettings オブジェクト
 ・.Value と .Text の違い
 ・VBAのStrとCStrの違い
 ・英数字を漢数字に変換する
 ・年月の連続文字データを作る
 ・DateValue 関数
 ・改行などの特殊文字(ASCII文字コード)
 ・文字列の中の引用符
 ・数字と文字(数字以外)の判定
 ・数字に見られるセルを、文字列にする
 ・全角文字の文字コード
 ・全角英数を半角に変換
 ・全角英数を半角に変換
 ・全角を半角に大文字を小文字に空白を無しに
 ・計算式のエラーを文字列にする関数
 ・UNICODE の文字コードに変換

ワイルドカードを使った検索
 ・Access データベースをシーケンシャルに更新する
 ・Access データベースをシーケンシャルに更新する(正規表現カウント)

指定列のセルの値を、指定した方法で型変換して、指定したシートの指定した列に貼付ける
 

セル操作
セルの指定方法いろいろ
Excelの列名⇔列番号
Excelの最終行を取得★
複数行をまとめて削除
行列処理
 ・同上の補完
 ・変数を使って、セルを指定する方法★
行列入れ替えコピー
 ・連立1次方程式の解
 ・子供の血液型の出現率
配列に、セル範囲を一括登録、およびその逆
Excel関数の VLOOKUP をマクロで★
Excel関数の VLOOKUP をマクロで(セル置換)

選択範囲を取得し、A1形式で表示
セルのデータチェック
セルに背景色を付ける
セルの文字検索と置換
セル内改行以降の文字列を除外する
指定フォルダの全てのExcelブックを対象に、検索して置換する
セルデータの集計と文字検索
並び替え+重複行削除
使っている全てのセルを指定する

複数シートを串刺し集計(3D集計)
セルの範囲を連続コピー
キーのトップと属性の重複を摘出
指定ブック中の全てのシートで、指定列の「セルデータの重複」をチェック
数字に見られるセルを、文字列にする
列名に変数を使ってセルを指定する
シートの内容を削除

指定列のセルの値を、指定した方法で型変換して、指定したシートの指定した列に貼付ける
ファイル名として使えない文字を削除

数独
階段昇降

ワークシート操作
Book名とSheet名・シート数を取得
指定ブックの全シートの「拡大縮小印刷」の設定を、ページ数に合せて印刷、に変更
指定ブック中の全てのシートで、指定列の「セルデータの重複」をチェック
追い番を付けてシートを追加(シートをコピー)
 ・セルに付けた名前を削除する
シートの内容を削除
オートフィルタを設定
オートフィルタした行を削除
指定フォルダの全てのExcelブックを対象に、検索して置換する
ワークシートをオブジェクトにSet

ファイル操作
フルパス→ファイル名、拡張子
ファイルを検索する
ブックを開いて、オブジェクト型変数に格納して活用
テキストファイルの読み書き
 ・1.Line Input # / Print # を使う
 ・ ・ファイルを開くダイアログを使って、ファイルのフルパスを取得
 ・ ・比較:ファイルを開くダイアログ
 ・ ・別解:Microsoft Word から [ファイルを開く] ダイアログ ボックスを呼び出す
 ・2.FileSystemObjectを使う
 ・ ・英文単語間の空白を抜いて単語頭文字を大文字に
 ・ ・階層付きテキストからタイトルのみ抽出
 ・ ・「青空文庫」の「字下げ」を空白字下げに変換
 ・ ・「青空文庫」の入力者注「傍点」をルビに変換
 ・ ・「青空文庫」にルビ設定
 ・ ・「青空文庫」のルビを、HTMLのルビ・タグに変更する
 ・ ・(UTF8版)「青空文庫」のルビを、HTMLのルビ・タグに変更する
 ・ ・文中の()を、HTMLのルビ・タグに変更する(Shift JIS)
 ・ ・文中の()を、HTMLのルビ・タグに変更する(UTF8)
 ・ ・([ファイルを開く]ダイアログで指定したフォルダの全てのファイルを対象)テキスト・ファイルの特定項目を取得
 ・ ・(Excel と同じフォルダの全てのファイルを対象) html のタグ項目を読み込む
 ・UNICODE(UTF-8)ファイルを読む
 ・ファイル名を付けて保存
 ・UNICODE でテキストを出力する
 ・階層付きテキスト(WzMemo)を、OPML に変換する
 ・HTML で出力
 ・ExcelシートをUNICODE でテキスト保存
 ・CSVファイルに書き出し
 ・固定長orTab区切テキストから、指定項目部分を切り出す

 ・英辞郎のテキスト・ファイルを、StarDict用 1行形式に変換する
 ・PDICテキスト形式の EJDIC テキスト・ファイルを、StarDict用 1行形式に変換する
 ・(UTF-8 ファイルの読み書き) EPWING 辞書複数行テキストを StarDict 1行テキストに変換
 ・(UTF-8 ファイルの読み書き) 電子ブック辞書テキストから漢字キーを生成する
 ・(UTF-8 ファイルに追記) UTF-8ファイルを読んで、指定文字列を含む行を抽出して出力
 ・(UTF-8 ファイルに追記) 英文 HTML に、Google で作成した英和対訳ファイルの日本語部分を挿入して出力
 ・UTF8 html から、本文以外(ヘッダーとフッターなど)を削除
 ・Excel の指定した列データを、CSV または 固定長でテキスト出力(汎用ツール)

指定フォルダの直下のサブフォルダの全てのファイルを検索
(VBA側でフォルダを指定)

フォルダ内の指定拡張子のファイルを名前のプレフィックスでカウント([ファイルを開く]ダイアログボックスで、ファイルを指定)
フォルダ内のファイルを検索 ([ファイルを開く]ダイアログボックスで、ファイルを指定)
 ・別解:Dir 関数を使う
指定フォルダの全てのExcelブックを対象に、検索して置換する

サブ・フォルダを含めて、フォルダ毎のファイル数をカウント
ファイル名の英数を半角に変更
(Excelと同じフォルダおよびサブフォルダの全てのファイル)

ファイル名一覧(マクロと同じフォルダの全てのファイル)
ブック名シート名一覧(マクロと同じフォルダの全て)
ファイルリスト一覧(サブフォルダも)★

指定フォルダの全てのExcelファイルのプロパティを出力する
指定フォルダの全ての Office ファイルのプロパティを出力して、変更する
指定フォルダの全てのファイルのプロパティを出力する
指定フォルダの全てのファイルのプロパティを出力
(サブ・フォルダ以下も含む)

指定フォルダの全てのPDFファイルのプロパティを出力して、変更する

名前を変えてファイルをコピー
指定フォルダの指定ファイルを、別のフォルダにそのままコピー
指定フォルダのサブ・フォルダの構成ファイルも含めて、
指定拡張子のファイルを拡張子を変更してコピー
(ファイル名変更コピー)

指定フォルダの内容を丸ごとコピー
指定フォルダを削除
フォルダを作成
ファイル名を変更 (or ファイルを移動)★
ファイルを別のフォルダに移動する★
指定高さ以下の JPG ファイルを指定フォルダに移動する★
HTMLの Titleタグの内容をファイル名に付加する(UTF8)
ブックを開く。保存しないで閉じる
戻り値が複数の関数を作る(ユーザ定義型)
 ・「ファイルを開くダイアログ」で指定
 ・Excelのセルをポイントして指定

ファイルを開くダイアログで複数ファイルを選択して ZIP圧縮・解凍

指定したフォルダのサブフォルダを含めて、条件付きファイルを検索して別フォルダにコピー
(フォルダ選択ダイアログ)★


ADO接続して Excel ブックを更新
ファイルを排他制御する
CSV項目のデータ形式を指定して読み込んでExcelブックとして保存する
複数ブックのシートを1つのブックにコピーする

日付・時刻・時間
日付・時刻の関数
待ちの設定
処理時間計測
日数計算
稼動日数だけさかのぼった日付を求める
前後の月を求める
文字列から日付けを求める
年月の文字列から前月を求める


グラフ操作

シートの位置を指定して、複数の領域を含む、複数のグラフを表示
2軸グラフを作成する
散布図(多重)を作成する
ヒストグラム(度数分布縦棒グラフ)を作成する
二重のドーナツグラフを作成する

色操作
色を並べる
色コード(ColorIndex)

確率
乱数を使う(円周率 Monte Carlo simulation)
指定範囲の整数乱数を発生する関数
正規乱数・二項乱数・指数乱数・ポアソン乱数を発生
数の暗黙知能力向上プリント


インターネットを操る

Internet Explorerオブジェクト
インターネット・エクスプローラ IE のバージョンを調べる
MSXML2.XMLHTTP を使って、HTMLソースを取得
 ・ホームページ (DHolic) から写真リンクを取得
 ・「青空文庫」から一括ダウンロード
ファイルをダウンロード
ファイルをアップロード
テキスト・データを取得
VBA で FTP アップロード・ファイル一覧取得・ダウンロード
Google と DeepL で翻訳比較

株取引に VBA を使う

IE 画面を、スクリプトで操作

TinySeleniumVBA


フォーム

三年日記表示


文章を読み上げ

SAPIを使う
TextToSpeech と MSAgentSpeak
クリップボードからテキストデータを取得
英語字幕テキストを整形

Windows 標準で読み上げ
Microsoft Agentで読み上げ
エージェント停止
エージェント設定

音声応答ツール

他のプログラムとの連係
VBAで他のプログラムを動かす(起動・終了)
ExcelのVBAで、Accessのデータを参照・更新する
 ・Access のデータを検索して、Excelに取り込む(Accessオブジェクト利用)
 ・Access のデータを使って、Excelのセルに貼り込む(連想配列を使う)
 ・Access Jet Engine のバージョンを調べる
 ・Access データベースをシーケンシャルに更新する(順位を算出する)(ADO接続)
 ・Access データベースをシーケンシャルに更新する(正規表現置換)(ADO接続)
 ・Access データベースをシーケンシャルに更新する(正規表現カウント)(ADO接続)
 ・Excel VBA で Access のデータベースを最適化/修復
 ・dBASE データベースをシーケンシャルに参照する(ADO接続)
VBA で DOSコマンドを実行する
ExcelVBA で Word オブジェクトを操作


部品表展開

部品表とは
部品表のローレベル・コード設定
 ・ローレベル・コード設定(その1)
 ・ローレベル・コード設定(その2)
部品表集約展開
部品表マルチ・レベル展開・マルチ・レベル逆展開
部品表マルチ・レベル→シングル・レベル抽出


索引


 このサイトの VBA コードは、クリエイティブ・コモンズライセンス・パターンの内、
表示-非営利-継承 表示-非営利-継承 Attribution-NonCommercial-ShareAlike」に準拠して、公開します。
「表示-非営利-継承」の条件では、無断で複製、改変、転載できます。連絡や承諾の必要はありません。

 
この種類の目次に戻る↑  索引へ↓ トップページに戻る


ホームページのトップに戻る