24 VBA初心者向けの便利なExcelマクロの例（すぐに使用できます）

Excelマクロを使用すると、作業をスピードアップし、時間を大幅に節約できます。

VBAコードを取得するXNUMXつの方法は、マクロを記録し、マクロが生成するコードを取得することです。ただし、マクロレコーダのコードは、実際には必要のないコードでいっぱいになることがよくあります。マクロレコーダにもいくつかの制限があります。

したがって、バックポケットに入れて必要なときに使用できる便利なVBAマクロのセットを用意する価値があります。

最初にExcelVBAマクロをコーディングするのに時間がかかる場合がありますが、完了したら、それを参照として使用し、次に必要になったときに使用できます。

この大規模な記事では、私が頻繁に必要とし、プライベートボールトに保管する便利なExcelマクロの例をいくつかリストします。

このチュートリアルは、さらにマクロの例で更新していきます。リストに何かあるべきだと思うなら、コメントを残してください。

このページをブックマークして、後で参照することができます。

ここで、マクロの例に入る前に、VBAコードを紹介します。まず、サンプルコードの使用方法を説明します。

Excelマクロの例のコードを使用する

いずれかの例のコードを使用するための手順は次のとおりです。

• マクロを使用するブックを開きます。
• Altキーを押しながら、F11キーを押します。これが開きますVBエディター。
• プロジェクトエクスプローラで任意のオブジェクトを右クリックします。
• [挿入]-> [モジュール]に移動します。
• モジュールコードウィンドウにコードをコピーして貼り付けます。

例で、コードをワークシートコードウィンドウに貼り付ける必要があると示されている場合は、ワークシートオブジェクトをダブルクリックし、コードをコピーしてコードウィンドウに貼り付けます。

ブックにコードを挿入した後、.XLSMまたは.XLS拡張子を付けてコードを保存する必要があります。

マクロを実行する方法

VBエディターでコードをコピーした後、マクロを実行する手順は次のとおりです。

• [開発者]タブに移動します。
• [マクロ]をクリックします。

• [マクロ]ダイアログボックスで、実行するマクロを選択します。
• [実行]ボタンをクリックします。

リボンに開発者タブが見つからない場合は、このチュートリアルを読んで取得方法を確認してください。

コードがワークシートのコードウィンドウに貼り付けられている場合は、コードの実行について心配する必要はありません。指定されたアクションが発生すると自動的に実行されます。

それでは、作業を自動化して時間を節約するのに役立つ便利なマクロの例を見てみましょう。

注：多くのアポストロフィ（ '）の後にXNUMX行かXNUMX行が続きます。これらは、コードの実行時に無視され、自己/リーダーコメントとして配置されるコメントです。

記事やコードに誤りを見つけた場合は、お知らせください。

Excelマクロの例

この記事では、次のマクロの例について説明します。

すべてのワークシートを一度に再表示

複数の非表示のシートを含むブックで作業している場合は、それらをXNUMX枚ずつ再表示する必要があります。隠しシートが多い場合は、時間がかかる場合があります。

ブック内のすべてのシートを再表示するためのコードは次のとおりです。

'このコードは、ワークブック内のすべてのシートを再表示しますSub UnhideAllWoksheets（）Dim ws As Worksheet For Each wsInActiveWorkbook。Worksheetsws。Visible= xlSheetVisible Next ws End Sub

上記のコードは、VBAループ（For Each）を使用して、ブック内の各ワークシートを反復処理します。次に、シートのvisibleプロパティをvisibleに変更します。

これは、さまざまな方法を使用してExcelでワークシートを再表示する方法の詳細なチュートリアルです。

アクティブなワークシートを除くすべてのワークシートを非表示にする

レポートまたはダッシュボードで作業していて、レポート/ダッシュボードがあるシートを除くすべてのシートを非表示にする場合は、このマクロコードを使用できます。

'このマクロは、アクティブなシートを除くすべてのワークシートを非表示にしますSub HideAllExceptActiveSheet（）Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden Next ws End Sub

VBAを使用してワークシートをアルファベット順に並べ替える

このマクロコードは、シートが多いワークブックがあり、それらのシートをアルファベット順に並べ替える場合に便利です。これは、シート名が年、従業員名、または製品名である場合に発生する可能性があります。

'このコードは、ワークシートをアルファベット順に並べ替えますSub SortSheetsTabName（）Application.ScreenUpdating = False Dim ShCount As Integer、i As Integer、j As Integer ShCount = Sheets.Count For i = 1 To ShCount-1 For j = i + 1 To ShCount If Sheets（j）.Name <Sheets（i）.Name Then Sheets（j）.Move before：= Sheets（i）End If Next j Next i Application.ScreenUpdating = True End Sub

すべてのワークシートを一度に保護する

ブックに多数のシートがあり、すべてのシートを保護する場合は、このマクロコードを使用できます。

コードでパスワードを指定できます。シートの保護を解除するには、このパスワードが必要になります。

'このコードは、一度にすべてのシートを保護しますSub ProtectAllSheets（）Dim ws As Worksheet Dim password As String password = "Test123"' Test123を必要なパスワードに置き換えますForEach ws In Worksheets ws.Protect password：= password Next wsサブ終了

すべてのワークシートの保護を一度に解除します

ワークシートの一部またはすべてを保護した場合は、ワークシートの保護に使用するコードをわずかに変更するだけで、保護を解除できます。

'このコードは一度にすべてのシートを保護しますSubProtectAllSheets（）Dim ws As Worksheet Dim password As String password = "Test123"' Test123を必要なパスワードに置き換えますForEach ws In Worksheets ws.Unprotect password：= password Next wsサブ終了

パスワードは、シートのロックに使用したものと同じパスワードである必要があることに注意してください。そうでない場合は、エラーが表示されます。

すべての行と列を再表示します

このマクロコードは、すべての非表示の行と列を再表示します。

これは、他の人からファイルを取得していて、非表示の行/列がないことを確認したい場合に便利です。

'このコードは、ワークシートサブのすべての行と列を再表示しますUnhideRowsColumns（）Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub

マージされたすべてのセルのマージを解除します

セルをXNUMXつにマージするのが一般的な方法です。動作している間は、セルが結合されたときにデータを並べ替えることはできません。

セルが結合されたワークシートを使用している場合は、以下のコードを使用して、結合されたすべてのセルを一度に結合解除します。

'このコードは、マージされたすべてのセルをマージ解除しますSub UnmergeAllCells（）ActiveSheet.Cells.UnMerge End Sub

[マージして中央揃え]オプションではなく、[選択範囲全体を中央揃え]オプションを使用することをお勧めします。

名前にタイムスタンプを付けてブックを保存する

動作するバージョンを作成する必要がある場合がよくあります。これらは、時間の経過とともにファイルを処理する長期プロジェクトに最適です。

ファイルをタイムスタンプ付きで保存することをお勧めします。

タイムスタンプを使用すると、ファイルに戻って、行われた変更や使用されたデータを確認できます。

指定したフォルダにブックを自動的に保存し、保存時にタイムスタンプを追加するコードは次のとおりです。

'このコードは、名前にタイムスタンプが付いたファイルを保存しますSub SaveWorkbookWithTimeStamp（）Dim timestamp As String timestamp = Format（Date、 "dd-mm-yyyy"）＆ "_"＆Format（Time、 "hh-ss"） ThisWorkbook.SaveAs "C：UsersUsernameDesktopWorkbookName"＆timestamp End Sub

フォルダの場所とファイル名を指定する必要があります。

上記のコードでは、「C：UsersUsernameDesktopは私が使用するフォルダーの場所です。ファイルを保存するフォルダの場所を指定する必要があります。また、ファイル名のプレフィックスとして一般名「WorkbookName」を使用しました。プロジェクトや会社に関連するコンテンツを指定できます。

各ワークシートを個別のPDFとして保存します

異なる年、部門、または製品のデータを使用する場合は、異なるワークシートをPDFファイルとして保存する必要がある場合があります。

これを手動で行うと時間のかかるプロセスになる可能性がありますが、VBAは処理を高速化します。

各ワークシートを個別のPDFとして保存するVBAコードを次に示します。

'このコードは、各ワークシートを個別のPDFサブとして保存します。SaveWorkshetAsPDF（）Dim ws As Worksheet For Each ws In Worksheets ws.ExportAsFixedFormat xlTypePDF、 "C：UsersSumitDesktopTest"＆ws.Name＆ "。pdf" Next ws End Sub

上記のコードでは、PDFを保存するフォルダーの場所のアドレスを指定しました。また、各PDFにはシートと同じ名前が付けられます。このフォルダーの場所を変更する必要があります（名前がSumitであり、デスクトップのテストフォルダーに保存した場合を除く）。

このコードはワークシートに対してのみ機能することに注意してください（チャートシートに対しては機能しません）。

各ワークシートを個別のPDFとして保存します

これは、ブック全体をPDFとして指定されたフォルダーに保存するためのコードです。

'このコードは、ブック全体をPDF Sub SaveWorkshetAsPDF（）ThisWorkbook.ExportAsFixedFormat xlTypePDF、 "C：UsersSumitDesktopTest"＆ThisWorkbook.Name＆ "。pdf" EndSubとして保存します。

このコードを使用するには、フォルダの場所を変更する必要があります。

すべての数式を値に変換する

多くの数式を含むワークシートがあり、それらの数式を値に変換する場合は、このコードを使用します。

'このコードは、すべての数式を値に変換しますSub ConvertToValues（）With ActiveSheet.UsedRange .Value = .Value End With End Sub

このコードは、使用されているセルを自動的に認識し、それを値に変換します。

数式でセルを保護/ロック

計算が多く、誤って削除または変更したくない場合は、数式でセルをロックすることをお勧めします。

これは、数式ですべてのセルをロックし、他のすべてのセルのロックを解除したままにするコードです。

'このマクロコードは、数式を使用してすべてのセルをロックしますSub LockCellsWithFormulas（）With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells（xlCellTypeFormulas）.Locked = True .Protect AllowDeletingRows：= True End With End Sub

関連チュートリアル：Excelでセルをロックする方法。

ブック内のすべてのシートを保護する

次のコードを使用して、ブック内のすべてのシートを一度に保護します。

'このコードは、ワークブック内のすべてのシートを保護しますSub ProtectAllSheets（）Dim ws As Worksheet For Each ws InWorksheetsws。ProtectNextws End Sub

このコードは、すべてのシートをXNUMXつずつ調べて、それらを保護します。

すべてのシートの保護を解除する場合は、コードでws.Protectの代わりにws.Unprotectを使用してください。

選択範囲の各行の後に行を挿入します

選択した範囲のすべての行の後に空白行を挿入する場合は、このコードを使用します。

'このコードは、選択範囲のすべての行の後に行を挿入しますSub InsertAlternateRows（）Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRowActiveCell.EntireRow。 ActiveCell.Offset（2、0）を挿入します。[次へ] i EndSubを選択します。

この場合も、このコードを変更して、選択した範囲の各列の後に空白の列を挿入できます。

隣接するセルに日付とタイムスタンプを自動的に挿入します

タイムスタンプは、アクティビティを追跡するときに使用するものです。

たとえば、特定の経費が発生したとき、売上請求書が作成されたとき、セルにデータが入力されたとき、レポートが最後に更新されたときなどのアクティビティを追跡したい場合があります。

このコードを使用して、既存のコンテンツを入力または編集するときに、隣接するセルに日付とタイムスタンプを挿入します。

'このコードは、隣接するセルにタイムスタンプを挿入します。PrivateSub Worksheet_Change（ByVal Target As Range）On Error GoTo Handler If Target.Column = 1 And Target.Value <> "" Then Application.EnableEvents = False Target.Offset（0、 1）= Format（Now（）、 "dd-mm-yyyy hh：mm：ss"）Application.EnableEvents = True End If Handler：End Sub

このコードをワークシートコードウィンドウに挿入する必要があることに注意してください（これまでの他のExcelマクロの例で行ったようにモジュールコードウィンドウには挿入しません）。これを行うには、VBエディターで、この機能を使用するシート名をダブルクリックします。次に、このコードをコピーして、そのシートのコードウィンドウに貼り付けます。

また、このコードは、列Aでデータ入力が行われる場合に機能します（コードにはTarget.Column = 1行があることに注意してください）。それに応じて変更できます。

選択範囲内の代替行を強調表示

交互の線を強調表示すると、データの読みやすさが大幅に向上します。これは、印刷してデータを参照する必要がある場合に役立ちます。

これは、選択範囲内の代替行をすぐに強調表示するコードです。

'このコードは、選択範囲内の代替行を強調表示しますSub HighlightAlternateRows（）Dim Myrange As Range Dim Myrow As Range Set Myrange = Selection For Each Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End Next Myrow EndSubの場合

コードで色をvbCyanとして指定したことに注意してください。他の色（vbRed、vbGreen、vbBlueなど）を指定することもできます。

スペルミスのあるセルを強調表示する

Excelには、WordまたはPowerPointのスペルチェックがありません。F7キーを押すとスペルチェックを実行できますが、スペルミスの視覚的な手がかりはありません。

このコードを使用して、スペルミスのあるすべてのセルを即座に強調表示します。

'このコードは、単語のスペルが間違っているセルを強調表示しますSub HighlightMisspelledCells（）Dim cl As Range For Each cl In ActiveSheet.UsedRange If Not Application.CheckSpelling（word：= cl.Text）Then cl.Interior.Color = vbRed End If Next cl EndSub

強調表示されたセルは、Excelがスペルミスと見なすテキストを含むセルであることに注意してください。多くの場合、理解できない名前やブランド用語も強調表示されます。

ブック内のすべてのピボットテーブルを更新します

ブックに複数のピボットテーブルがある場合は、このコードを使用して、それらすべてを一度に更新できます。

'このコードは、ブック内のすべてのピボットテーブルを更新しますSub RefreshAllPivotTables（）Dim PT As PivotTable For Each PT In ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub

ピボットテーブルの更新について詳しくは、こちらをご覧ください。

選択したセルの大文字と小文字を大文字に変更します

Excelにはテキスト文字の大文字と小文字を変更する数式がありますが、別のセルセットでこれを行うことができます。

このコードを使用して、選択したテキストのテキストの大文字と小文字を即座に変更します。

'このコードは、選択を大文字に変更しますSub ChangeCase（）Dim Rng As Range For Each Rng In Selection.Cells If Rng.HasFormula = False Then Rng.Value = UCase（Rng.Value）End If Next Rng End Sub

この場合、UCaseを使用してテキストの大文字と小文字を大文字に設定していることに注意してください。小文字にはLCaseを使用できます。

コメントですべてのセルを強調表示する

次のコードを使用して、コメント付きのすべてのセルを強調表示します。

'このコードは、コメントのあるセルを強調表示します `Sub HighlightCellsWithComments（）ActiveSheet.Cells.SpecialCells（xlCellTypeComments）.Interior.Color = vbBlue End Sub

この場合、vbBlueを使用してセルに青色を付けます。必要に応じて別の色に変更できます。

VBAで空白のセルを強調表示する

条件付き書式を使用するか、[特別に移動]ダイアログを使用して空白のセルを強調表示できますが、これを頻繁に行う必要がある場合は、マクロを使用することをお勧めします。

作成したら、このマクロをクイックアクセスツールバーに含めるか、個人用マクロブックに保存できます。

VBAマクロコードは次のとおりです。

'このコードは、データセット内のすべての空白セルを強調表示しますSub HighlightBlankCells（）Dim Dataset as Range Set Dataset = Selection Dataset.SpecialCells（xlCellTypeBlanks）.Interior.Color = vbRed End Sub

このコードでは、赤で強調表示された空白のセルを指定しました。青、黄、シアンなどの他の色を選択できます。

単一の列でデータを並べ替える方法

次のコードを使用して、指定した列でデータを並べ替えることができます。

サブSortDataHeader（） 
Range（ "DataRange"）。Sort Key1：= Range（ "A1"）、Order1：= xlAscending、Header：= xlYes 
End Subの

「DataRange」という名前の範囲を作成し、セル参照の代わりにそれを使用したことに注意してください。

ここで使用されるXNUMXつの主要なパラメータもあります。

• Key1 –これはデータセットを並べ替えるキーです。上記のサンプルコードでは、データは列Aの値に基づいて並べ替えられます。
• Order1 –ここでは、データを昇順または降順のどちらで並べ替えるかを指定する必要があります。
• ヘッダー–ここでは、データにヘッダーがあるかどうかを指定する必要があります。

VBAを使用してExcelでデータを並べ替える方法の詳細をご覧ください。

複数の列でデータを並べ替える方法

次のようなデータセットがあるとします。

複数の列に基づいてデータを並べ替えるコードは次のとおりです。

Sub SortMultipleColumns（）With ActiveSheet.Sort .SortFields.Add Key：= Range（ "A1"）、Order：= xlAscending .SortFields.Add Key：= Range（ "B1"）、Order：= xlAscending .SetRange Range（ "A1 ：C13 "）.Header = xlYes .Apply End With End Sub

ここでは、最初に列Aでソートし、次に列Bでソートするように指定していることに注意してください。

出力は次のようになります。

Excelで文字列から数値部分のみを取得する方法

文字列から数値部分またはテキスト部分を抽出するだけの場合は、VBAでカスタム関数を作成できます。

次に、このVBA関数をワークシートで使用でき（通常のExcel関数と同じように）、文字列から数値またはテキスト部分のみが抽出されます。

以下に示すように：

文字列から数値部分を抽出する関数を作成するVBAコードは次のとおりです。

'このVBAコードは、文字列から数値部分を取得する関数を作成します。FunctionGetNumeric（CellRef As String）Dim StringLength As Integer StringLength = Len（CellRef）For i = 1 To StringLength If IsNumeric（Mid（CellRef、i、1） ）Then Result = Result＆Mid（CellRef、i、1）Next i GetNumeric = Result End Function

コードをモジュールに入れる必要があります。そうすれば、ワークシートで関数= GetNumericを使用できます。

この関数は、数値部分を取得するセルのセル参照というXNUMXつのパラメーターのみを取ります。

同様に、以下の関数は、Excelの文字列からのみテキスト部分を取得します。

'このVBAコードは、文字列からテキスト部分を取得する関数を作成します。FunctionGetText（CellRef As String）Dim StringLength As Integer StringLength = Len（CellRef）For i = 1 To StringLength If Not（IsNumeric（Mid（CellRef、i、 1）））Then Result = Result＆Mid（CellRef、i、1）Next i GetText = Result End Function

したがって、これらは、タスクを自動化し、生産性を向上させるために日常業務で使用できるいくつかの便利なExcelマクロコードです。