#ExcelVBAマクロを使いこなす 57 ' ￥r￥n の改行コードを ' 文字列内で全置換し ￥n に統一 s = Replace(s, vbCrLf, vbLf) ' ￥n で分割して配列にする arr_lines = Split( s, vbLf ) Replace(s, "￥r￥n", "￥n") だと 改行コードではなく そのように書かれた文字列を置換処理してしまう

#ExcelVBAマクロを使いこなす 55 セル更新イベントの無限ループを防ぐ hensa40.cutegirl.jp/archives/6182 Private Sub Worksheet_Change(～) ' イベント検知を無効化 Application.EnableEvents = False ' 安全にセル値を書き換え ' イベント有効化 Application.EnableEvents = True

#ExcelVBAマクロを使いこなす 53 Private Sub Worksheet_Change(ByVal Target As Range) ' Targetが存在するシート sheet_name = Target.Parent.Name Set target_sheet = Sheets(sheet_name) Intersect( Target, target_sheet.Range(～) ) ↑ これで2つの引数のシートが同一になる

#ExcelVBAマクロを使いこなす 52 イベントが発生したシートの名前 relief.jp/docs/018364.ht… ' シート内容変更時 Private Sub Worksheet_Change(ByVal Target As Range) ' Targetが存在するシートを取得 sheet_name = Target.Parent.Name Set target_sheet = Sheets(sheet_name)

#このアカウントのハッシュタグ一覧 2 表計算ソフト: #Excel関数を使いこなす #ExcelVBAマクロを使いこなす #GAS・GoogleAppsScriptの勉強 他のオフィスソフト: #Wordの使い方Tips #メールとメーラーの知識 マルチメディア系: #AviUtlで動画編集する方法

#ExcelVBAマクロを使いこなす 51 「実行時エラー'1004' Intersectメソッドは失敗しました。 '_Global'オブジェクトのエラー」 okwave.jp/qa/q2989438.ht… Intersect( Target, Range(～) )で 異なるシートのセルを比べるとエラーになる。 Targetはイベント発生シート RangeはActiveSheet

#ExcelVBAマクロを使いこなす 47 Private Sub Worksheet_Change(ByVal Target As Range) For Each r In Target x = r.Column y = r.Row MsgBox "変更セルは" & y & "行" & x & "列目" Next 複数セル範囲が選択・更新された場合， For Eachで1セルずつ処理できる。

#ExcelVBAマクロを使いこなす 46 Private Sub Worksheet_Change(ByVal Target As Range) x = Target.Column y = Target.Row … ・Ctrlキーを押しながら 複数セルを選択し一括更新した場合 最初に選択したセルの番地が返る。 ・矩形セル領域を選択した場合 左上のセル番地が返る。

#ExcelVBAマクロを使いこなす 44 自動処理: ThisWorkbookモジュール内に 下記の関数を書く。 ' ブックを開いた時に自動的に実行 Private Sub Workbook_Open() ' 現在日時を書き込み Sheets("Sheet1").Cells(1, 1).Value = Format(Now, "yyyy/mm/dd(aaa)hh:mm:ss") End Sub

#ExcelVBAマクロを使いこなす 43 モジュールの先頭・宣言セクションに モジュール変数を定義し 宣言と同時に値を初期化するには… ・そのモジュール内だけで使う場合はPrivate ・他のモジュールでも使うならPublic ・値が不変の定数ならConst Private Const a = 1 Public Const b = "hoge"

#ExcelVBAマクロを使いこなす 42 ・SubやFunctionの 内部で宣言された変数を 「プロシージャ変数」(ローカル変数)という. ・複数のプロシージャ間で 共通の変数を使うには， モジュールの先頭 (全ルーチンの前。宣言セクション)に 「モジュール変数」を宣言する. moug.net/tech/exvba/015…

#ExcelVBAマクロを使いこなす 41 VBAコードの置き場は3種類 excel-ubara.com/excelvba4/EXCE… (1)標準モジュール: ここに書いたプロシージャは (Private指定しなければ) 全シートから呼び出し可能 (2)ブックモジュール: ブックイベントなど書く (3)シートモジュール: シートイベントなど書く

#ExcelVBAマクロを使いこなす 39 頻出パターン8: DB接続 Set conn = CreateObject("ADODB. Connection") conn.Open 接続文字列 ' SQL実行 Set rs = CreateObject("ADODB.RecordSet") rs.Open strSQL, conn ' SELECT結果がセットされる language-and-engineering.hatenablog.jp/entry/20090127…

#ExcelVBAマクロを使いこなす 38 頻出パターン8: DB接続 language-and-engineering.hatenablog.jp/entry/20090401… Excelのシート上に 一覧表形式で記載されているデータを， 外部データベースと連携して処理できる。 ・RDBMSからExcelへデータ読み込み ・ExcelからRDBMSへデータ書き込み

#ExcelVBAマクロを使いこなす 37 ファイル名の拡張子に 関連付けられたプログラムで ファイルを開くには… moug.net/tech/exvba/006… cmd_str = """" & txt_file_path & """" Set wsh = CreateObject("Wscript.Shell") wsh.Run cmd_str, 3 ※.Shell関数ではダメ

#ExcelVBAマクロを使いこなす 36 頻出パターン7: 外部コマンド呼び出し ・Shell 関数で， Windowsコマンドプロンプトと同じように コマンドを実行できる。 ・デフォルトのカレントディレクトリは マイドキュメント。 ・ChDir / ChDriveは，UNCパスの設定は不可。

#ExcelVBAマクロを使いこなす 35 頻出パターン7: 外部コマンド呼び出し Excelのシート上から 外部コマンドを呼び出すことができれば 【実行可能ドキュメント】になる。 「仕様書を書けば，その仕様書自体が動く」。 例 テスト仕様書を書いた →その書面上から，書いてあるテストを直接実行可

#ExcelVBAマクロを使いこなす 34 頻出パターン7: 外部コマンド呼び出し language-and-engineering.hatenablog.jp/entry/20090401… ' カレントディレクトリを設定 ChDrive ThisWorkbook.Path ChDir ThisWorkbook.Path ' コマンド実行 output_path = ThisWorkbook.Path & "\a.txt" Shell "cmd.exe /c dir > " & output_path

#ExcelVBAマクロを使いこなす 33 文字コード判定: 専用の関数を公開しているお方がいる。 (1) popozure.info/20190515/14201 ADODBのバイナリモードで テキストファイルを開き， ビット列を調べ判定 (2) excel-ubara.com/excelvba4/EXCE… Excel関数でも頑張れば SJISか判定できる

#ExcelVBAマクロを使いこなす 32 UTF-8でファイル入力 テキストファイルの内容を 全行一括読み取り。 Set ado = CreateObject("ADODB. Stream") ado.Charset = "UTF-8" ado.Open ado.LoadFromFile txt_file_path buf = ado.ReadText ado.Close MsgBox buf

#ExcelVBAマクロを使いこなす 31 頻出パターン6: ファイル入力(読み込み) ' SJISで開く fp = FreeFile Open input_path For Input As #fp ' 末尾まで1行ずつ読み込み Do Until EOF( fp ) ' 読み取り(行末の改行はトリムされる) Line Input #fp, s MsgBox s Loop Close #fp

#ExcelVBAマクロを使いこなす 30 頻出パターン6: ファイル入力(読み込み) language-and-engineering.hatenablog.jp/entry/20090401… ' ファイルは存在するか？ input_path = ThisWorkbook.Path & "\" & "temp.txt" If Dir( input_path ) = "" Then MsgBox input_path & "は存在しません。" Exit Sub ' 終了 End If

#ExcelVBAマクロを使いこなす 29 UTF8Nで出力 ado.Charset="UTF-8" ado.Open ado.WriteText s ado.Position=0 ado.Type=1 ado.Position=3 b=ado.Read ado.Close ado.Open ado.Type=1 ado.Write b ado.SaveToFile file_path, 2 ado.Close

#ExcelVBAマクロを使いこなす 28 VBAでファイル出力をする場合， 通常は FreeFile を使って SJIS で書き出す。 ADODB. Stream で UTF-8 で出力すると， デフォルトで先頭に BOM が付く。 開発ツールによっては， BOM無し (UTF-8N) でないと Syntax Error になったりするので注意。

#ExcelVBAマクロを使いこなす 27 ' UTF8で出力 output_path = ThisWorkbook.Path & "\a.txt" Dim ado As Object Set ado=CreateObject("ADODB. Stream") ado.Open ado.Type = 2 ado.Charset = "UTF-8" ado.WriteText "ほげ" ado.SaveToFile output_path, 2 ado.Close

#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マクロを使いこなす 23 頻出パターン4: 「大量データの一括処理」 シート内でkey文字列を含むセルを検索 language-and-engineering.hatenablog.jp/entry/20090304… Worksheets("sheet1").Cells.Find(key) セル範囲をフィル language-and-engineering.hatenablog.jp/entry/20080929… Range(～).AutoFill ' 連続データ作成 Range(～).FillDown ' 複写

#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マクロを使いこなす 18 頻出パターン1： ブック内の全シートをスキャン language-and-engineering.hatenablog.jp/entry/20090401… ' 全シートに対し For Each ws In Worksheets ' このシートを表示 ws.Activate ' このシートに対する処理 MsgBox ws.Name ' シート名を表示 Next ws

#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