#ExcelVBAマクロを使いこなす 26 頻出パターン5: ファイル出力(書き込み) Excelは，外部ファイルの出力前段階として データ整理・加工など「中間的な役割」を パッパと果たすのに最適。 ・Excelのテーブル定義書から大量DDLを一括生成 ・Excelの設定一覧シートから大量XML設定ファイルを生成

#ExcelVBAマクロを使いこなす 25 頻出パターン5: ファイル出力(書き込み) language-and-engineering.hatenablog.jp/entry/20090401… ' ブックと同じフォルダ上で output_path = ThisWorkbook.Path & "\" & "temp.txt" ' 開く fp = FreeFile Open output_path For Output As #fp ' 出力 Print #fp, "hoge" ' 終了 Close #fp

#ExcelVBAマクロを使いこなす 24 あるセルより下を シート内で全削除: Set sheet1 = Sheets("sheet1") ' C4から下の全セルをRangeとして操作 sheet1.Range( _ sheet1.Range("C4"), _ sheet1.Range("C" & Cells.Rows.Count) _ ).EntireRow.Delete

#ExcelVBAマクロを使いこなす 22 頻出パターン4: 「大量データの一括処理」 language-and-engineering.hatenablog.jp/entry/20090401… ・大量Read＝シート内検索 ・大量Write＝シート内フィル こういった一括処理系のロジックは ExcelやVBAに組み込みの関数を使うこと。 For文等で独自実装すると，実行時間が数千倍かかる。

#ExcelVBAマクロを使いこなす 21 頻出パターン3: シート内の更新イベントを拾う language-and-engineering.hatenablog.jp/entry/20090401… ' ThisWorkBook のモジュールに追加 Private Sub Workbook_SheetChange( ByVal sheet As Object, ByVal rng As Range ) MsgBox rng.Row & "行" & rng.Column & "列を編集。" End Sub

#ExcelVBAマクロを使いこなす 20 頻出パターン2 シート内の全行をスキャン　続き 「空欄になるまで次の行へスキャンし続ける」 ' この行の1列目のセルの値 emptytest_str = Cells( row_num, 1 ).Value ' 空か？ If Len( emptytest_str ) = 0 Then ' 終了 continue_flag = False End If

#ExcelVBAマクロを使いこなす 19 頻出パターン2 シート内の全行をスキャン language-and-engineering.hatenablog.jp/entry/20090401… row_num = 1 ' 処理開始行 Do While continue_flag = True ' この行の10列目にhogeと入力 sheet.Cells( row_num, 10 ).Value = "hoge" ' 次の行へ row_num = row_num + 1 Loop

#ExcelVBAマクロを使いこなす 17 yt4u.hatenablog.com/entry/2016/06/… ▶配列の場合: Redim Preserve で 「全体の大きさ」を先に決めてから 値を入れていく必要がある ▶Collectionの場合: 全体の大きさを先に考える必要が無く， どんどんAddしてゆける。 全体の大きさがよく変わる場合に便利

#ExcelVBAマクロを使いこなす 16 VBA　配列とコレクションの違い thom.hateblo.jp/entry/2016/08/… 配列 ・宣言時に型と要素数を決定してメモリ領域確保 ・ArrayやSplitで生成，Joinで結合できる コレクション ・データの追加・削除・挿入が容易 ・インデックスの代わりにキー文字列で参照可

#ExcelVBAマクロを使いこなす 15 配列の末尾に要素追加してゆく例 ' 要素数が不定の動的配列 Dim fuga() For i = 0 To 3 ' 上限添え字を再定義 ' 既存要素を保持するためPreserve付き ReDim Preserve fuga(i) ' 要素を追加 fuga(i) = i MsgBox Join(fuga, ",") Next i

#ExcelVBAマクロを使いこなす 14 配列の初期化 sejuku.net/blog/29274 ・「可変長配列」の宣言と定義は， 配列名のあとに ( ) を記述し ( ) 内に何も記述しない。 Dim 配列名() ・データの値を保持したまま 配列の要素数を変更したい場合， ReDim Preserveステートメントを使用。

#ExcelVBAマクロを使いこなす 13 配列とFor文: ' 配列を宣言&初期化 hoge = Array( "a", "b", "c" ) ' 文字列の配列なら，これでもよい ' hoge = Split("a,b,c", ",") ' 全要素を1つずつ表示 For i = 0 To UBound(hoge) ' i 番目の要素 s = hoge(i) ' 表示 MsgBox s Next i

#ExcelVBAマクロを使いこなす 12 例外をキャッチする: On Error GoTo HOGE ' ファイルをリネーム Name a.txt As b.txt Exit Sub ' 正常終了 ' エラー発生時はこのラベルに飛ぶ HOGE: MsgBox "リネーム失敗。ファイルが開かれている可能性があります。" ' マクロ全体の実行を停止 End

#ExcelVBAマクロを使いこなす 11 エラー処理 sejuku.net/blog/35484 ・On Errorステートメントを記述すると エラー処理が有効になる ・GoToステートメントで ラベル先に処理をスキップ。 スキップ先で Resume Next と書くと エラー発生箇所の次の行から処理再開できる。

#ExcelVBAマクロを使いこなす 10 VBAマクロのコードを 複数行いっぺんにコメントアウトする方法… asahi-net.or.jp/~ef2o-inue/vba… VBEのメニューから 表示→ツールバー→編集 編集ツールバー上で 「コメントブロック」ボタンを押すと 選択中の全行の行頭が ' でコメントアウトされる。

#ExcelVBAマクロを使いこなす 9 無限ループになってしまったマクロを 実行停止させるには: atmarkit.co.jp/ait/articles/1… ・ESCキーを押せば，実行中のマクロは停まる。 ・MsgBoxが出ている場合などは，ESCキーを長押しすればメッセージボックスが消え，かつマクロの実行が停まる。

#ExcelVBAマクロを使いこなす 8 「End」だけ書くと， マクロ全体の実行を中断することができる。 maku77.github.io/vba/other/end.… If文で条件を調べて 満たすべき条件がクリアされていなかったら MsgBox "エラーのため終了します。" End End If のようにできる。

#ExcelVBAマクロを使いこなす 6 VBA 最初の一歩(3) ▶シート内のセルの読み書き ' 1行2列目のセルの値を取得 a = Sheets("シート名").Cells( 1, 2 ).Value ' 1行2列目のセルに値を書き込み Sheets("シート名").Cells( 1, 2 ).Value = "fuga" なお英語では 行は row 列は column

#ExcelVBAマクロを使いこなす 5 VBA 最初の一歩(2) ▶VBAコードの改行 MsgBox( _ "Hello, World!" ) ・行末に半角スペースと _ を書くと 次の行にコードを続けられる。 ▶文字列の結合，改行 MsgBox( "Hello," & vbCrLf & "World!" ) ・文字列は & で結合し vbCrLf で改行できる。

#ExcelVBAマクロを使いこなす 4 VBA 最初の一歩(1) ▶ルーチン，コメント，メッセージボックス ' コメント Sub hoge() MsgBox "fuga" End Sub ・ボタン押下時などに呼び出される ルーチンは， Sub ～ End Sub で囲む. ・コメントは ' の後ろに書く. ・MsgBoxで文字列を画面表示できる.

#ExcelVBAマクロを使いこなす 3 「セキュリティの警告： マクロが無効にされました。」 VBAマクロを含むブックを開く時 初回はこの警告が出る。 「コンテンツの有効化」ボタンを押すと その時点で， ブックOpenイベントのVBAコードが実行される。 pic.twitter.com/93iwi7hsjw

#ExcelVBAマクロを使いこなす 2 VBA のエディタを VBE (Visual Basic Editor)と呼ぶ。 マクロのコードを編集できる。 Excel上では， Alt＋F11 のショートカットキーから VBEを起動できる。 手が反射的に動くように 記憶しておこう。 tonari-it.com/excel-vba-vbe-…

#このアカウントのハッシュタグ一覧 1 Windows: #コマンドプロンプト・BATを使いこなす #WSH・JScriptでバッチ作成 #PowerShell入門 #Windowsパソコンのメンテナンス 表計算ツール: #Excel関数を使いこなす #ExcelVBAマクロを使いこなす #GAS・GoogleAppsScriptの勉強

#ExcelVBAマクロを使いこなす 1 ボタン押下時に マクロを実行するには… ・ファイル→オプション→ 「リボンのユーザー設定」で 開発タブを表示 ・開発タブを開き 挿入→フォームコントロール→ボタンで シート上にボタンを設置。 押下時に実行される マクロ関数名を登録できる。

#ExcelVBAマクロを使いこなす 108 コード内には， 「End If」とか 「End Sub」など いろんな箇所で「End」が出てくる。 そのせいで， マクロ全体を終了させるための「End」が コード内のどこに書かれているのか 検索して探すことができない・・・。 #ここがダメだよExcelVBA

#ExcelVBAマクロを使いこなす 107 If 変数名＝値 と書こうとして 『変数名』の部分は，他の行からコピペしよう とすると…， If文が未完成のまま行を離れたので 【コンパイルエラー：修正候補　式】 のエラーが表示され， 思考の流れがそこで遮断される！怒 #ここがダメだよExcelVBA

#ExcelVBAマクロを使いこなす 106 VBEの非常に悪い点: タイピング中にカーソル位置をずらすと 「コンパイルエラー」の邪魔なダイアログが出て 思考がさえぎられ 試行錯誤ができない。 nobitan.cocolog-nifty.com/blog/2010/11/p… これを抑止するには VBE上で ツール→オプション→自動構文チェック をOFFにする。

#ExcelVBAマクロを使いこなす 104 IEを自動操作する際 「COMアプリケーションイベント」を使う： Set objIE = CreateObject("InternetExplorer.Application") CreateObjectの第二引数は… ・WSH (JScript/VBScript) だと COMイベントの接頭辞。 ・VBやVBAの場合は， リモートサーバー名。

#ExcelVBAマクロを使いこなす 103 IEを自動操作する際， 「COMアプリケーションイベント」を使う ツール→参照設定 から， 「Microsoft Browser Helpers」に チェックを入れて参照設定しておくこと。 ' COMイベントと関連付けて IE を定義 Public WithEvents objIE As InternetExplorer

#ExcelVBAマクロを使いこなす 102 頻出パターン IEを自動操作する際， 「COMイベント」を使いたい。 language-and-engineering.hatenablog.jp/entry/20131202… ' イベント検知機能付き Set objIE = CreateObject("InternetExplorer.Application") ' イベントフック Private Sub objIE_OnQuit() MsgBox "IEを閉じた" End Sub

#ExcelVBAマクロを使いこなす 101 ブラウザを自動操作 Set ie = CreateObject("InternetExplorer.Application") ie.Navigate "http:// www. google.co.jp" ie.Visible = True ' ビジー状態の間待つ Do While ie.Busy = True Or ie.readystate <> 4 DoEvents Loop Sleep 100

#ExcelVBAマクロを使いこなす 99 入力候補のサジェスト機能を作る: ・アクティブなセルの 「入力規則」(Validation)を， リアルタイムで変化させてゆく。 ・セルの「入力規則」を，マウスを使わずに キー入力だけで表示させるには Alt＋下キーを押せばよい。 ・キャンセルは Esc キー

#ExcelVBAマクロを使いこなす 98 入力候補のサジェスト機能を作る: セルの「入力規則」を VBAで動的に書き換える。 ActiveCell.Validation.Delete ActiveCell.Validation.Add Type:=xlValidateList, Formula1:="山田,山川" ActiveCell.Validation.ShowError = False language-and-engineering.hatenablog.jp/entry/20081125…

#ExcelVBAマクロを使いこなす 97 入力候補のサジェスト機能を作りたい： セルに文字列を入力したら その入力文字列を確定するために Enter キーではなく，Ctrl + Enter を押す。 ・そうすれば，文字列を確定した時に カーソルが移動しない。 ・押下直後にWorkbook_SheetChangeイベントが走る

#ExcelVBAマクロを使いこなす 96 入力候補のサジェスト機能を作りたい： Excelの「入力規則」は，実用上は ごく少数の入力候補しか使えない。 入力候補の数が増えてくると ドロップダウンが巨大化して使いにくい。 検索のヒントを与えて自動補完する 「サジェスチョン機能」ではないのだ。

#ExcelVBAマクロを使いこなす 95 入力候補のサジェスト機能： Excelの「入力規則」機能で セルに特定のデータしか入力できないよう制限できる。 [データ]タブ →データの入力規則 →入力値の種類 →リスト 「石川,山田」と設定しておけば 「石川」と「山田」がドロップダウンに現れる。

#ExcelVBAマクロを使いこなす 94 頻出パターン: 入力ボックスやセルに， 「入力サジェスト機能」(検索候補の自動補完) を付けたい。 language-and-engineering.hatenablog.jp/entry/20081125… 「山」と入力したら 山田，山川などの候補をドロップダウンで表示。 最初の1文字だけ入力すればよく 楽だし，ミスタイプもない。 .

#ExcelVBAマクロを使いこなす 93 マクロのコードを 外部テキストファイル内に保管することのメリット： language-and-engineering.hatenablog.jp/entry/20090731… ・複数のブックで，常に共通のマクロを利用できる。 ・マクロに変更があっても，たった１箇所の修正ですむ。 ・Git等で差分管理しやすい。

#ExcelVBAマクロを使いこなす 92 頻出パターン: 複数ブック間でVBAコードを共有したい →マクロのコードを 外部テキストファイル内に保管すればよい。 language-and-engineering.hatenablog.jp/entry/20090731… ' テキストファイル内のVBAコードを標準モジュールに登録 ThisWorkbook.VBProject.VBComponents.Import file_path

#ExcelVBAマクロを使いこなす 90 頻出パターン: 配布したExcelブックで，中身のVBAコードを 見れないようにしたい。 language-and-engineering.hatenablog.jp/entry/20100526… VBE上で ツール＞VBAプロジェクトのプロパティ＞保護 「プロジェクトを表示用にロックする」 「プロジェクトのプロパティ表示のためのパスワード」