Go to Qiita Advent Calendar 2024 Top
LoginSignup
12
9

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@kato__tatsu

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

Last updated at Posted at 2019-04-04

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

もっとはじめに

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

はじめに

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

関数

よく使う関数

項目 関数
開いているブックの数 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 オブジェクトの配列を枠線に合わせる
12
9
4

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
12
9

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?