【Excel VBA】Excelでツールファイルを作る

目次

このページで書くこと

どのExcelファイルでも使用できる「ツールファイル」を作成します。
このファイルの「設定」シートに対象のExcelファイルの名前やシート名を入力して実行する、という使い方を想定しています。

経緯

会社で仕事をしていると、共有のフォルダに保存されたExcelファイルというものが必ずあると思います。
そういうファイルって、部署の決まりで「xlsm」形式の保存がNGの場合もあるので少し厄介です。
編集するときはファイルをコピーして、古いファイルはバックアップとして「old」フォルダに移動させる、という方法も多いですよね。
もちろん都度VBAを消したり修正したりすればいいだけの話ですが、何回もやるのは面倒……。
なので、必要な処理を外に出してみました。

使用するデータ

以下のようなExcelのサンプルデータを使用します。
「生産量」ファイルはくだものの数が少ないので問題なさそうですが、行数が増えると面倒そうです。
「県別生産数」ファイルはデータが多いうえにバラバラでストレスフルな仕上がりです。

「生産量」ファイル

「県別生産数」ファイル

用意

①「xlsm」ファイルを作ります。
②シート名は「設定」とでもしておきます（なんでもいいです）。
③ファイル名、シート名など、必要な情報を入力するセルを決めておきます。
④必要に応じてボタンなどをつけておきます（あとでマクロを設定します）。

「設定」シート

使用するコード

セルに入力されたファイル情報を取得し、Workbook型やWorksheet型の変数に代入します。

ファイル名取得
→取得したファイル名をString型の変数に代入
→変数を使ってWorkbookやWorksheetを定義

の流れです。
あとはWorkbook型やWorksheet型の変数を使ってコードを書くだけです。

Dim SetSheet As Worksheet 'ツールファイルの「設定」シート
Dim dataA As Worksheet '対象ファイルA
Dim aBook As String '「設定」シートのB5セル（対象ファイルAの名前）
Dim aSheet As String '「設定」シートのB6セル（対象ファイルAのシート名）
'
Set SetSheet = ThisWorkbook.Worksheets("設定")
aBook = SetSheet.Range("B5").Value
aSheet = SetSheet.Range("B6").Value
Set dataA = Workbooks(aBook).Worksheets(aSheet)

コードの例

普段使っている雰囲気のコードです。

例①

以前書いたExcelブック内の全シートをA1セル選択にするコードの汎用版です。
「設定」シートのB2セルに対象のファイル名を入力すればワンクリックで動作します。
arekoretips.hatenablog.jp

Sub UPPER_LEFT()
'
    Dim SetSheet As Worksheet 'ツールファイルの「設定」シート
    Dim dataB As String '「設定」シートのB2セル（対象ファイル名）
    Dim DataBook As Workbook '対象ファイル
    '
    Set SetSheet = ThisWorkbook.Worksheets("設定")
    dataB = SetSheet.Range("B2").Value
    Set DataBook = Workbooks(dataB)
    '
    DataBook.Activate
    '
    Application.ScreenUpdating = False '画面の更新を停止させる
    '
    Dim i As Long
    For i = 1 To Worksheets.Count
        Sheets(i).Activate
        ActiveWindow.ScrollColumn = 1 'A列が左側に来るように調整
        ActiveWindow.ScrollRow = 1 '1行目が一番上に来るように調整
        Range("A1").Select 'A1セルを選択
    Next i
    '
    Sheets(1).Activate '先頭のシートを表示
'
Application.ScreenUpdating = True '画面の更新を戻す
'
End Sub

バラバラのシート

画面が整う

例②

Aのファイルで選択した文字列で、AとBのファイルをフィルタリングします。
なにに使用するのか不明かもしれませんが、たまにこの処理が必要になるときがあります。
arekoretips.hatenablog.jp
arekoretips.hatenablog.jp

Sub AtoB()
'
    Dim SetSheet As Worksheet 'ツールファイルの「設定」シート
    Dim dataA As Worksheet '対象ファイルA
    Dim aBook As String '「設定」シートのB5セル（対象ファイルAの名前）
    Dim aSheet As String '「設定」シートのB6セル（対象ファイルAのシート名）
    Dim dataB As Worksheet '対象ファイルB
    Dim bBook As String '「設定」シートのB7セル（対象ファイルBの名前）
    Dim bSheet As String '「設定」シートのB8セル（対象ファイルBのシート名）
    Dim fruitsName  As String 'フィルタリングする文字列
    '
    Set SetSheet = ThisWorkbook.Worksheets("設定")
    aBook = SetSheet.Range("B5").Value
    aSheet = SetSheet.Range("B6").Value
    bBook = SetSheet.Range("B7").Value
    bSheet = SetSheet.Range("B8").Value
    Set dataA = Workbooks(aBook).Worksheets(aSheet)
    Set dataB = Workbooks(bBook).Worksheets(bSheet)
    '
    dataA.Activate  'ファイルAをアクティブにする
    fruitsName = ActiveCell.Value 'ファイルAのアクティブセルの値を変数に代入
    dataA.AutoFilterMode = False 'オートフィルタ が有効なら無効にする（エラー防止）
    dataA.Rows(1).AutoFilter Field:=2, Criteria1:=fruitsName 'ファイルAの1行目B列を「fruitsName」でフィルタリング
    '
    dataB.Activate 'ファイルBをアクティブにする
    dataB.AutoFilterMode = False 'オートフィルタ が有効なら無効にする（エラー防止）
    dataB.Rows(1).AutoFilter Field:=2, Criteria1:=fruitsName 'ファイルBの1行目B列を「fruitsName」でフィルタリング
'
End Sub

ファイルAのセルを選択した状態で実行する。

ファイルAとファイルBが選択した文字列でフィルタリングされる。

以上です。