Excelファイル加工に関するマクロTips

(随時追記)何回かGoogle先生に聞いた気がする関数やメソッドを追加
(2019.12.02追記)ウチが書いている他のマクロ記事を冒頭に載せました。

---

もっとはじめに

Excelマクロ関連の記事他にもありますので、よかったら併せてみてもらえるとうれしいです。

• 個人用マクロブックのすすめ～入門編～
  • 個人用マクロブックの作成やVBA Editorの使い方の紹介
• 個人用マクロブックのすすめ～実践編～
  • ウチの個人用マクロブックに入れているマクロを紹介してます

はじめに

仕事で利用するけど「あれってコマンドなんだっけ？」てよく検索するものをメモ代わりに
あとはよく使うマクロセット

関数

よく使う関数

項目	関数
開いているブックの数	Workbooks.Count
アクティブブックの名前	ActiveWorkbook.Name
i番目のブックの名前	Workbooks(i).Name
アクティブブックのパス	ActiveWorkbook.Path
アクティブブックのシート数	ActiveWorkbook.Worksheets.Count
アクティブシートの名前	ActiveSheet.Name
アクティブシートの インデックス番号※1	ActiveSheet.Index
i番目シートの表示形式 (True=再表示,False=非表示)※2	Sheet(i).visible
i行目セルの表示形式 (True=再表示,False=非表示)	Rows(i).Hidden
i列目セルの表示形式 (True=再表示,False=非表示)	Columns(i).Hidden

※1:インデックス番号はExcelシートに割り振られた番号です。
シート名が可変でシート順は変更無い場合はこちらで指定した方が良いです。

※2:値参照するより、シートを表示したり非表示にするのに利用します。
シートの非表示は一括でできますが、再表示は1シートずつしかできなく非常にまどろっこしいのでSheets.CountまでFor文でSheets(i).visible = Trueを括ると一括表示楽です。

全シートを表示

'for用一時変数
dim i as integer

For i = 1 To Sheets.Count

    Sheets(i).Visible = True
    'その他各シートに対して実行したいメソッドを追加

Next i

何かと便利な関数

x列の最終行数、列数

アクティブシートまたは対象シートモジュールでのみ有効

dim x as Integer
x = 1

'x列の最終行数
Debug.Print Cells(Rows.Count, x).End(xlUp).Offset(0).Row

'x行の最終列数
Debug.Print Cells(x, Columns.Count).End(xlToLeft).Offset(0).Column

メソッド

よく使うメソッド

ブック関連

項目	メソッド
新規ブックを開く	Workbooks.Add
ブックを開く	Workbooks.Open (#file_path\file_name#)
アクティブブックを 名前つけて保存※1	ActiveWorkbook.SaveAs (#file_path\file_name#)
アクティブブックを 閉じる※2	ActiveWorkbook.Close
アクティブブックを 保存せずに閉じる	ActiveWorkbook.Close SaveChanges:=False
アクティブブックを 保存して閉じる	ActiveWorkbook.Close SaveChanges:=True
アクティブブックの 全シートを新しいブックに コピー※3	ActiveWorkbook.Sheets.Copy

※1 既存ファイルがある場合ダイアログが出てきてマクロが止まるので注意
※2 アクティブブックに変更が入っているとダイアログが出てきてマクロが止まるので注意
　　基本、下の2つどちらかを使うのが無難
※3 パラメータシート作成など、テンプレ用ブックからファイル作成するときに便利

シート関連

項目	メソッド
新規シートを作成 (アクティブシート前に作成)	worksheets.Add
新規シートを作成 (アクティブシート後に作成)	worksheets.Add After:=Sheets(ActiveSheet.Index)
xシート目(orシート名)を アクティブ化	Sheets(x).Activate
アクティブシートを 新しいブックにコピー	ActiveSheet.Copy
アクティブシートを xシート目(orシート名)の 前にコピー	ActiveSheet.Copy Before:=Sheets(x)
アクティブシートを xシート目(orシート名)の 後にコピー	ActiveSheet.Copy After:=Sheets(x)
アクティブシートを ブック末尾にコピー	ActiveSheet.Copy After:=Sheets(Worksheets.Count)
アクティブシートを ブック先頭に移動	ActiveSheet.Move Before:=Sheets(1)
アクティブシートを ブック末尾に移動	ActiveSheet.Move After:=Sheets(Worksheets.Count)
アクティブシートを削除※1	ActiveSheet.Delete
アクティブシート内の 全リンクを削除	ActiveSheet.Hyperlinks.Delete
アクティブシートをPDF化※2	ActiveSheet.ExportAsFixedFormat

####※1:シート削除の確認メッセージ
※1のDeleteメソッド実行するとシート消していいか確認メッセージが出てきます。
強制的に消したい場合、確認メッセージが出てしまうメソッドに以下の処理を挟むとメッセージが出ずに処理できます**(Trueで元に戻すの忘れずに！)**

'確認メッセージ非表示
Application.DisplayAlerts = False

'～確認メッセージが出ちゃうまどろっこしい処理～

'確認メッセージ表示
Application.DisplayAlerts = True

####※2:PDF化メソッドをもう少し詳しく

※2について、ActiveSheet.ExportAsFixedFormatだけ実行すると、ブック名.pdfがカレントディレクトリにエクスポートされます。
資料のPDF化はお客様への納品時は、文書プロパティを含めないIncludeDocProperties=Falseを指定すると良いと思います。

書くのが面倒ならExportAsFixedFormat部分だけマクロ記録使うのもアリです。

以下のマクロはアクティブブックを文書プロパティ残さずPDF化し、終わったらブックを閉じるものです。
フォルダ内のExcelファイル全て行いたい時はDir()を使えばいい感じに一括処理が出来ます。

アクティブブック全シートを文書プロパティ残さずPDF化、終わったら閉じる

Dim book_path, book_name, book_name_trim_ext As String

'アクティブブックのフォルダパス
book_path = ActiveWorkbook.Path

'アクティブブック名
book_name = ActiveWorkbook.name

'アクティブブック名から、拡張子を削除
book_name_trim_ext = Left(book_name, InStrRev(book_name, ".") - 1)

'アクティブブックのシート全て選択してPDFエクスポート
Workbooks(book_name).Worksheets.Select
ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF,                                           'エクスポートタイプをPDF
    Filename:=book_path & "\" & book_name_trim_ext & ".pdf", _ 'フォルダ、ファイル名指定
    Quality:=xlQualityStandard, _                              '品質設定、最低限ならxlQualityMinimumを指定
    IncludeDocProperties:=False, _                             '文書プロパティ残さない
    IgnorePrintAreas:=False, _                                 '印刷領域は無視しない(印刷プレビュー通り)
    OpenAfterPublish:=False                                    'エクスポート後にPDFファイルを表示しない

'ブックを保存せずに閉じる
Workbooks(book_name).Close SaveChanges:=False

セル関連

項目	メソッド
A1セルの選択※1	Range("A1").Select
x行目y列目セルの選択※1	Range(Cells(x, y),Cells(x, y)).Select
x行目y列目セルの選択※1	Cells(x, y).Select
A1セルの値削除	Range("A1").ClearContents
A1セルの書式削除	Range("A1").ClearFormats
A1セルの値と書式削除	Range("A1").Clear
A1セルをB1セルへコピー※2	Range("A1").Copy Range("B1")

※1 A1形式でセル指定するならRange、R1C1形式で指定するならCellsがベター
　R1C1形式だけどRangeのメソッド使いたい・・・ってときは※1上の力技もできる
※2 手動でコピーするときと同じ挙動

ファイル操作

テキストファイルのあれこれ
基本は絶対パスを使う。

項目	メソッド
ファイルAをファイルBへコピー	FileCopy ファイルA, ファイルB
ファイルAを削除	kill ファイルA
ファイルAをファイルA'にリネーム(移動)	Name ファイルA As ファイルA'

処理効率化

マクロ動かしているとき「なんかもっさりするなー」と思ったとき、
以下を試してみるといいかもしれません。

画面描写無効

最後有効に戻すの忘れずに
マクロ途中で終了させてから画面が固まりっぱなしになるのあるあるですね

'画面描写無効
Application.ScreenUpdating = False

'画面描写有効
Application.ScreenUpdating = True

自動計算無効(※Excel関数)

vlookup関数の参照先をゴリゴリマクロで書き込むときに
これも自動計算有効を最後にするの忘れずに

'自動計算無効
Application.Calculation = xlCalculationManual

'自動計算有効
Application.Calculation = xlCalculationAutomatic

(番外)タイマー

少し観点が違いますが、マクロ処理時間取得用に
そのままだとシリアル値で出力するので、MinuteやSecond関数を利用すると良い感じになります

'時間取得
Dim stop_watch As Variant
stop_watch = Time

'～良い感じの処理～

Debug.Print "所要時間：" & VBA.Format$(Time - stop_watch, "n分s秒")

小技マクロ

ブックの開き直し

上に書いてあるメソッドの組み合わせ
Excel資料作ってて保存せずに閉じてもう一度開くって操作が多かったので

Sub ブックを開きなおす()

dim thisbook_path as String

'アクティブになってるブックのファイルパスを取得
thisbook_path = ActiveWorkbook.FullName

'アクティブになっているブックを閉じる
ActiveWorkbook.Close SaveChanges:=False

'閉じたブックを開きなおす
Workbooks.Open thisbook_path

End Sub

イミディエイトウィンドウ花形出力

イミディエイトウィンドウで出力をわかりやすくするための
ウチの前職場(IBMメインフレーム)ではこういうの花形って言ってましたが一般的にはなんていうんでしょう？

Function hanagata_debug_print(debug_print_string As String)
'こんな感じで表示される
'**********************
'* debug_print_string *
'**********************

Dim printTxt As String
printTxt = "* " & debug_print_string & " *"
Dim headerFooterTxt As String
headerFooterTxt = VBA.String(VBA.LenB(VBA.StrConv(printTxt, vbFromUnicode)), "*")

Debug.Print headerFooterTxt
Debug.Print printTxt
Debug.Print headerFooterTxt
    
End Function

選択しているセルのエラーを全て無視する

表示形式を文字列にしているセルに数字を入れるとよく出てくる緑色のアレです。
いちいち右クリックするのは面倒なのでマクロの出番です。

Option Explicit

Sub ignore_NumberAsText()

'一時変数
Dim i As Integer

'選択しているセル全てに対して
For i = 1 To Selection.Count

    'i番目の選択セルの文字列に数字を入れてるエラーを無視する
    Selection(i).Errors.Item(xlNumberAsText).Ignore = True

Next i

End Sub

UTF-8をShift-JISへ変換する

LinuxやMacからWindowsへテキストファイル移動するとよく文字化けしちゃうので・・・。
実行する場合はMicrosoft ActiveX Data Objects x.x Libraryが必要なので注意。

※Chat-GPTでいい感じのコードにしているけどまだ検証できてないので動かなかったらスマセン。

Sub ConvertUtf8ToShiftJis(ByVal sourceFilePath As String, ByVal targetFilePath As String)
    ' UTF-8からShift_JISへのテキストファイルの変換を行う

    ' テキストファイルを読み込むためのストリームオブジェクトを作成
    Dim inputStream As New ADODB.Stream
    inputStream.Type = adTypeText ' テキストモードで読み込み
    inputStream.Charset = "UTF-8" ' UTF-8エンコーディングを指定
    inputStream.Open

    ' 入力ファイルをストリームに読み込む
    Call inputStream.LoadFromFile(sourceFilePath)

    ' テキストデータを文字列として取得
    Dim textData As String
    textData = inputStream.ReadText

    ' 改行コードの正規化（vbLfをvbcrlfに変換し、重複するvbcrを一つに変換）
    textData = Replace(textData, vbLf, vbCrLf)
    textData = Replace(textData, vbCr & vbCr, vbCr)

    ' Shift_JISに変換するためのストリームオブジェクトを作成
    Dim outputStream As New ADODB.Stream
    outputStream.Type = adTypeText ' テキストモードで書き込み
    outputStream.Charset = "Shift_JIS" ' Shift_JISエンコーディングを指定
    outputStream.Open

    ' Shift_JISエンコーディングでテキストデータを書き込む
    Call outputStream.WriteText(textData)

    ' Shift_JISファイルを指定されたパスに保存する（既存ファイルは上書き）
    Call outputStream.SaveToFile(targetFilePath, adSaveCreateOverWrite)

    ' ストリームを閉じる
    inputStream.Close
    outputStream.Close
End Sub

ActiveXReferenceが参照設定されているかフラグを出力

↑のスクリプトはMicrosoft ActiveX Data Objects 6.1 Libraryが参照設定に含まれていることが前提です。
このように特定のライブラリが参照設定に入っているかBooleanで返すスクリプトが以下になります。

Function CheckActiveXReference() As Boolean
    ' Microsoft ActiveX Data Objects 6.1 Libraryの参照が設定されているかを確認する関数

    Dim ref As Object
    Dim isActiveXReference As Boolean

    ' ExcelファイルのVBAプロジェクト内の参照を順番にチェックする
    For Each ref In ThisWorkbook.VBProject.References
        ' Microsoft ActiveX Data Objects 6.1 Libraryの参照があるかを判定するフラグ
        isActiveXReference = False

        ' 参照オブジェクトの情報を取得し、目的のライブラリの参照があるか確認する
        With ref
            If .Description = "Microsoft ActiveX Data Objects 6.1 Library" Then
                isActiveXReference = True
            End If
        End With

        ' ループを抜ける（参照が見つかれば確認は不要）
        If isActiveXReference Then Exit For
    Next ref

    ' 結果を関数の戻り値として返す
    CheckActiveXReference = isActiveXReference
End Function

おまけ

マクロとは直接関係のないこととか。

地味によく使うショートカット

ショートカット	内容
Ctrl + 1	選択セルの書式設定
Shift + F10	選択セルを右クリックした状態
Shift + Space	行選択 ※半角モードで実行すること
Ctrl + Space	列選択 ※半角モードで実行すること
Alt → h → f → d → o	オブジェクトの選択 ※セルが選択不可
Alt → p → a → a → p	オブジェクトの配列を枠線に合わせる