シートの存在を確認する2種類のコードと実務での例題

sheetexistanceeyecatch

エクセルVBA最速理解。シートの存在確認に使えるコードは2種類あります。

シート追加削除以外にも存在確認の後につづく、実務で使えるのサンプルコードも紹介します。

こんにちは　じゅんぱ店長　(＠junpa33)　です。

エクセルBOOKの中に「目的のワークシート」があるかどうかを調べる作業

エクセルVBAでプログラムを作成する中では、かなり頻繁に登場してくる作業です。

この作業を行わないとエラーで処理ストップ頻発なんてことも多くあります。

今回はこの「目的のシートをチェックする」コードとその後の繋がりについて紹介します。

シートの存在をチェックする2種類のVBAコード

エクセルBOOK中の目的のシートの存在を確認する方法としていわゆる「ループを使ったコード型」と「エラーを発生させるオブジェクト変数型」があります。

ループコード型　ループしながら1つづつシート名を確認

シートの存在をチェックするコードの基本形です。

For Each～Next　で対象のワークブック内のシートを調べます。存在していればループを外れます。

調べるシート名は「✖〇✖〇」です。

チェックVBA
    Dim Flag As Boolean
    Dim ws As Worksheet
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name = "✖〇✖〇" Then
                Flag = True
                Exit For
            Else
                Flag = False
            End If
        Next

上のコードは本体や何かを実行するプロシージャーの中での一つのコード塊として配置します。

Flagが「True」と「False」の条件分岐で次の動作を切り分けていきます。

vbadoloopeyecatch VBA 回数不定のループ処理はDo LoopとFor Each

オブジェクト変数型　シートが変数にセットできるかどうか

Setステートメントを利用して正常にオブジェクト変数に代入できれば、シートが存在している。

代入できなければ、シートは存在せずエラーが発生することを値として入手します。

HTML
    Dim ws As Worksheet
    Dim Exis As Boolean
        On Error Resume Next
        Set ws = Worksheets("✖〇✖〇")
        If ws Is Nothing Then
            Exis = False
        Else
            Exis = True
        End If
        On Error GoTo 0

上のコードは本体や何かを実行するプロシージャーの中での一つのコード塊として配置します。

Exisが「True」と「False」の条件分岐で次の動作を切り分けていきます。

で、どちらを使えばいいのですか？

ループコード型とオブジェクト変数型どちらを使った方がより良いのでしょうか。

結論としては、「どちらでもそんなに大差はない！」ということです。

オブジェクト変数型は高速処理が出来る！、コード行数が少なくなる！、という指摘も巷では散見されます。

スピードがミリ秒でどうかという話ですし、コード行数も5行増えてもそれほど負荷のかかる所ではありません。実際、自分的には差を感じることは出来ないですし、特に気にする部分でもありません。

むしろ、今ではなく充分後になって、

このコードを見て「何をしているコードなのか」が自分や他の人がパッと理解できるか（より視認性の高い）方を選ぶ方が吉になるでしょう。またほかにも自分以外の人の視点も考えておく必要があるかもです。

自分的には「ループコード型」をほぼ使っています。

目次へ戻る

「シートの存在確認をするコード」の実務での活かし方

2つのVBAコード「ループコード型」と「オブジェクト変数型」を紹介しました。

では「実務で使えるか？」という質問に対しては「このままでは実用的ではない！」とお答えするしかありません。

実際の業務では、エクセルVBAでの一つの業務自動化をはかるプログラムコードの中で、

「シートの存在確認をするコード」を一度利用するだけでは収まらないプログラムコードはたくさんあります。

またそれは、エラーSTOP防止のためにもコード内に配置しておきたいということもあります。

ですので勢い、同じコードを何度も記述することになります。これでは何か実用的ではありません。

つまり、プログラムコード内に「同じコードをたくさんたくさん」記述する非効率性を解消することを考える必要があるということです。

Functionプロシージャーでコードを部品化する

「シートの存在確認をするコード」を部品プロシージャー化して独立させます。そのためには「Functionプロシージャー」を利用します。

注意点

Functionプロシージャーの中で採用する変数名は親プロシージャーで使用する変数名と区別しないといけません。

コード記述の癖で、特に「True」「False」を利用するときは特に「flag,flgなど」変数名を設定しがちです。

Functionプロシージャーで「flg」としていれば、それを親プロシージャーと被らない名称設定が必要です。

VBAFunctioneyecatch Functionプロシージャーとユーザー定義関数

　

今回のエクセルVBA簡単なプログラム

例えば、「ループコード型」であれば

Functionループコード型1
Function Flaga() As Boolean
    Dim ws As Worksheet
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name = ActiveCell.Value Then
                Flaga= True
                Exit For
            Else
                Flaga = False
            End If
        Next
End Function

となります。

このVBAコードを記述しているエクセルBOOKの中で、選択したセルに書かれたシート名で、そのシート名の存在を確認する。

親プロシージャに「Flaga」という変数として、このFunctionプロシージャーで得た値を戻します。

例えば「オブジェクト変数型」であれば

Functionオブジェクト変数型
Function Exisa() As Boolean
    Dim ws As Worksheet
    Dim ACV As Variant
        ACV = ActiveCell.Value
        On Error Resume Next
        Set ws = Worksheets(ACV)
        Exisa = Not ws Is Nothing
        On Error GoTo 0
End Function

となります。

このVBAコードを記述しているエクセルBOOKの中で、選択したセルに書かれたシート名で、そのシート名の存在を確認する。If文を使わない場合はこのようにも記述することが出来ます。

親プロシージャに「Exisa」という変数として、このFunctionプロシージャーで得た値を戻します。

部品化したFunctionプロシージャーをエラーSTOPの防止に使う

折角、「シートの存在確認」コードを部品化しても、最低必要な部分だけの利用で済ませてしまうケースも多いです。

エクセルVBAマクロの組み立てスタイルで、

コマンドボタン1クリックすれば、最初から最後まで完全自動で処理されるコード組み立てのスタイルが結構多いです。に対して、各コード処理プロセスで区切りを付けて処理を進行させるという組み立てスタイルもあります。

この「部分部分のSTEPで処理作業を分けているタイプ」の場合は、途中まで処理したものを、作業中断を入れて、次のステップから再度処理を開始できるという業務のやり方も想定しています。

STEP式のコード進行では、コードエラーの少なくない発生原因に、処理指示された「シートが見つからない」というのがあります。再開するステップ順が違っていたわけですが、このようなことは「ほんのちょっとした休憩後の作業再開」にも普通に起こりえます。

こういったシート存在不明が原因のエラーを防止する上でも、VBAコード作成上で、ステップの開始時点での「シートの存在確認」をし、ことによっては利用者の注意メッセージを表示できるようにすることが、エラー事故の防止にも繋がっていきます。

目次へ戻る

実務で使えるサンプルコードを紹介

サンプル設定条件

エクセルBOOK単位・・例えば拠点単位とか・・で扱い商品毎にシート分けで売上や入庫数、出庫数、扱い期間などのデータを集計されている場合があるでしょう。

拠点ごとに扱い品目に差があるため、商品シートも全拠点100%同じではありません。

商品ヨコ串に拠点別に比較する場合はデータを見やすくする工夫が必要になります。

＜設定＞

インバウンド系の高級果物を扱っている。

「ITIGO」「MOMO」「JAPANBANANA」「BUDOW」「NASHI」「SUIKA」「RINGO」「KURI」「KAKI」のエクセルシートを作成している。

サンプルVBAコードを順番にコピペすれば、実用することが出来ます。（エクセルBOOKのファイル名の指定はありません。）

vbacopipeeyecatch Webで見つけたマクロをコピペで使う

STEP1

部品化したFunctionプロシージャーです。

親プロシージャーから「シート名 （引数）ACV」を取得してこのシートに存在するかどうかを調べます。

あれば「True」なければ「False」「変数Exis」に代入して親プロシージャーに戻します。

Functionシートの存在確認（引数）
Function Exis(ACV As Variant) As Boolean
    Dim ws As Worksheet
    Dim Flag As Boolean
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name = ACV Then
                Flag = True
                Exit For
            Else
                Flag = False
            End If
        Next
        Exis = Flag
End Function
VBAFunctioneyecatch Functionプロシージャーとユーザー定義関数

STEP2

親プロシージャー１では、まず「シートチェック」シートが存在するかどうかを調べます。

無ければ「シートチェック」シートを作成します。

そして、その「シートチェック」シートに、何という名のシートの存在を調べるかを所定セルに入力します。

全体のプログラムの進行（流れ）を、一旦止めて、必要事項をセル入力後、再開することになります。

親プロシージャー１
Sub チェックシート追加()
Dim ACV As Variant
    ACV = "シートチェック"
    If Exis(ACV) = False Then
        ThisWorkbook.Worksheets.Add(before:=Worksheets(1)).Name = ACV
    End If
    With Worksheets("シートチェック")
        .Select
        With .Range("B2")
            .Value = "シート名"
            .Font.Bold = True
            .Interior.ColorIndex = 24
        End With
        With .Range("C2")
            .Value = "あるorない"
            .Font.Bold = True
            .Interior.ColorIndex = 24
        End With
        .Columns.AutoFit
    End With
End Sub
vbawithstateeyecatch With～End Withの使い方。VBAコードを簡潔に記述する

STEP3

調べたいシート名を「シートチェック」シートに記入したら以下のVBAコードを実行します。

目的のシートがあるかどうか調べた結果は、「シートチェック」シートの項目に出力されていきます。

間違って、「シートチェック」シートを作成していないのに、このVBAコードを実行してしまった場合は、メッセージボックスでシートが無いと知らせてVBAコードの実行を終了させます。（エラー防止コードの設置）

目的のシートのあるなしをセルに表示し罫線を引いて終了します。

親プロシージャー２
Sub シート名存在チェック()
    Dim i As Long
    Dim ACV As Variant
        ACV = "シートチェック"
        If Exis(ACV) = False Then
            MsgBox "チェックシートがありません。" & vbCrLf & _
            "まずチェックシートを作成してください。"
            Exit Sub
        End If
        Worksheets("シートチェック").Select
        For i = 1 To Range("B2").CurrentRegion.Rows.Count - 1
                ACV = Worksheets("シートチェック").Cells(2, 2).Offset(i).Value
            If Exis(ACV) = True Then
                Cells(i + 2, 3) = "ありました!!"
            Else
                With Cells(i + 2, 3)
                    .Value = "ないです"
                    .Font.ColorIndex = 3
                End With
            End If
        Next i
        Range("B2").CurrentRegion.Borders.LineStyle = xlContinuous
        Columns.AutoFit
End Sub
sheetsonzai001

番外STEP

この後さらに次の処理へと進むことも可能です。いくらでも発展させることが可能です。

「【シートの存在確認】はVBAプログラムの入り口のコード」だということです。

ある意味、このコードを理解していないと次に進めないと言えるかもしれません。

目次へ戻る

まとめ

「シートの存在確認」のコードはエクセルVBAプログラムでは頻繁に使われる重要なコードです。

シートそのもののへの編集だけでなく、有るはずのシートがちゃんとと有るかの確認、そのことでのエラー発生の防止効果を見込むことも出来ます。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

vbastudyeyecatch2 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

今回の記事はここまでです。   最後までご覧いただき有難うございました。

エクセルVBA最速理解で必要な知識を集めよう！

エクセルVBA業務ツールで日常の業務改善を行いましょう。

＜記事内容についての告知＞

VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。

目次へ戻る

アンケートでポイ活しよう!!

アンケートに答えれば答えるほど ”使える” ポイントがたまります。

NTTコム サーチ

af_banner01

Dstyle web

dstyleweb_logo
dstyle_320x50-min

前の記事

VBACheckBoxeyecatch
シートへの【2種類のチェックボックス】の設置と使い方の違い

次の記事

Excelシートを追加・挿入するAddメソッドの使い方
vbaaddeyecatch