Excel 私のマクロ作成・黄金パターン(2)マクロの記述

Excel 私のマクロ作成・黄金パターン(1)下準備

2020年3月22日

前回の記事では、Excelブックにマクロを組み込むに当たって、下準備といえる段階のことを書いていった。

ここからは、実際にExcelにマクロを書いていく手順を示す。

前回作成した、VBEの「モジュール」というやつには、「変数の宣言を強制する」という設定がされていれば「Option Explicit」という文言が自動で入る。

この文字は消さず、これの後に続けてソースコードを書いていく。

ソースコードのひな形的なもの

では、私が書いていくマクロのソースコードのパターンは、だいたい次のようなものだ。
後で解説も書くし、長ったらしいからサラッと見てもらえれば良いが。

Sub macro() '「macro」には実際のマクロ名を入れる
    Dim arr         As Variant      '配列名は「arr」とか「arr○○」といったパターン
    Dim wb          As Workbook     'マクロを入れている自ブックをwbとする
    Dim newWb       As Workbook     'wsを新規ブックにコピーした際の新規ブックをnewWbとする
    Dim ws          As Worksheet    'マクロの起動ボタンを配置しているメインシートをwsとする
    Dim newWs       As Worksheet    'wsを新規ブックにコピーした際のシートをnewWsとする
    Dim rSize       As Long     '縦のデータ行数
    Dim cSize       As Long             '横のデータ列数
    
    Set wb = ActiveWorkbook 'マクロを入れている自ブック
    Set ws = ActiveSheet    'マクロの起動ボタンを配置しているメインシート
 
    'メッセージボックスで処理実行の確認
    If MsgBox("処理実行しますか？", vbOKCancel + vbQuestion, "確認") = vbCancel Then Exit Sub
    
    Call appSet '描画など省略して高速化
    
    '********************************************************
    (メイン部分。配列などの様々な処理を書いていく)
    '********************************************************
    
    ws.Copy'新規ブックにシートをコピー
    Set newWb = ActiveWorkbook'シートをコピーして作られた新規ブック
    Set newWs = ActiveSheet'コピーされたシート
    
    '********************************************************
    (newWsに値をセットするなどの処理)
    '2次元配列arrの値をnewWsにセットする処理もよくやる
    newWs.Cells(3, 1).Resize(rSize,cSize).Value = arr
    '********************************************************
 
    'wsには処理実行ボタンを付けているが、それがwsをコピーしたnewWsに残っている。
    'それをButtons.Deleteで全て削除する。
    newWs.Buttons.Delete
    
    Call appReset'描画など再開
    
End Sub
 
Sub appSet()
    With Application
        .ScreenUpdating = False '描画を省略
        .DisplayAlerts = False '警告を省略
        .Calculation = xlCalculationManual '手動計算
    End With
End Sub
 
Sub appReset()
    With Application
        .ScreenUpdating = True '描画を再開
        .DisplayAlerts = True '警告を再開
        .Calculation = xlCalculationAutomatic '自動計算
    End With
End Sub

Sub macro() '「macro」には実際のマクロ名を入れる

Dim arr As Variant '配列名は「arr」とか「arr○○」といったパターン

Dim wb As Workbook 'マクロを入れている自ブックをwbとする

Dim newWb As Workbook 'wsを新規ブックにコピーした際の新規ブックをnewWbとする

Dim ws As Worksheet 'マクロの起動ボタンを配置しているメインシートをwsとする

Dim newWs As Worksheet 'wsを新規ブックにコピーした際のシートをnewWsとする

Dim rSize As Long '縦のデータ行数

Dim cSize As Long '横のデータ列数

Set wb = ActiveWorkbook 'マクロを入れている自ブック

Set ws = ActiveSheet 'マクロの起動ボタンを配置しているメインシート

'メッセージボックスで処理実行の確認

If MsgBox("処理実行しますか？", vbOKCancel + vbQuestion, "確認") = vbCancel Then Exit Sub

Call appSet '描画など省略して高速化

'********************************************************

(メイン部分。配列などの様々な処理を書いていく)

'********************************************************

ws.Copy'新規ブックにシートをコピー

Set newWb = ActiveWorkbook'シートをコピーして作られた新規ブック

Set newWs = ActiveSheet'コピーされたシート

'********************************************************

(newWsに値をセットするなどの処理)

'2次元配列arrの値をnewWsにセットする処理もよくやる

newWs.Cells(3, 1).Resize(rSize,cSize).Value = arr

'********************************************************

'wsには処理実行ボタンを付けているが、それがwsをコピーしたnewWsに残っている。

'それをButtons.Deleteで全て削除する。

newWs.Buttons.Delete

Call appReset'描画など再開

End Sub

Sub appSet()

With Application

.ScreenUpdating = False '描画を省略

.DisplayAlerts = False '警告を省略

.Calculation = xlCalculationManual '手動計算

End With

End Sub

Sub appReset()

With Application

.ScreenUpdating = True '描画を再開

.DisplayAlerts = True '警告を再開

.Calculation = xlCalculationAutomatic '自動計算

End With

End Sub

処理の流れ概説

ソースコードで実施されている処理の流れを、改めて文章で書いてみる。

処理の流れ

最初にメッセージボックスMsgBoxで、実行確認をする。OKなら、以降に進んでいく。
まずはExcelシートのセルなどは極力いじらず、配列とか変数とかを内部的に計算していくだけの処理をしていく。
この内部処理に留まっているうちは、もし失敗してもExcelシートに被害は無いからね。
内部処理が終わったら、メインシートwsを別の新規ブックにコピー。
ws.Copy‘新規ブックにシートをコピー
の部分ね。
メインシートwsを新規ブックにコピーしたコピーシートnewWsは、もはやどこにも保存されていないので、単なる使い捨てシートとして扱うことができる。
メインシートws自体の方は決して変更せず、コピーシートnewWsの方に、内部処理で求めた値などをセットしていく。