開かれているブックから指定したシートを取得する

コードコピー

Function zzzワークシート取得(ByVal hzz検索文字列, Optional ByVal hzzCN検索 As Boolean = False) As Worksheet
Dim zz対象Bk As Workbook, zz対象Sh As Worksheet, zz取得Sh As Worksheet


For Each zz対象Bk In Application.Workbooks
    For Each zz対象Sh In zz対象Bk.Worksheets
        If hzzCN検索 = True Then
            If zz対象Sh.CodeName Like hzz検索文字列 Then
                Set zz取得Sh = zz対象Sh: Exit For
            Else: End If
        Else
            If zz対象Sh.Name Like hzz検索文字列 Then
                Set zz取得Sh = zz対象Sh: Exit For
            Else: End If
        End If
        If Not zz取得Sh Is Nothing Then
            Exit For
        Else: End If
    Next zz対象Sh
Next zz対象Bk
Set zz対象Sh = Nothing
Set zz対象Bk = Nothing

If Not zz取得Sh Is Nothing Then
    Set zzzワークシート取得 = zz取得Sh
    Set zz取得Sh = Nothing
Else: End If


End Function

開かれているブックの中から指定の名称のシートを取得する関数です
この関数は引数に指定した文字列に完全に一致するシートをオブジェクトで返します

シートオブジェクトにはVBAでのみ扱うオブジェクト名というものが存在します
CodeNameプロパティを使用することで取得できます
この名称でも検索を行えるようにしています、その場合は2つ目の引数にTrueを指定してください

Dim zz対象Sh As Worksheet
Set zz対象Sh = zzzワークシート取得("Sheet1")
If zz対象Sh Is Nothing Then
Exit Sub
Else: End If

zz対象Sh.Range("A1") = 1000
zz対象Sh.Parent.Save

実際に使用する場合は、返し値のある関数なので代入先を作成してから使用します
今回で言えば、「zz対象Sh」というWorksheet型の変数を作成してそこに代入させています

さらに、この関数は検索が見つからなかった場合はNothingが返されるので関数の後に判定を行う必要があります
もし取得できていなかった場合は実行時エラーが発生するからです

その際にはオブジェクトの比較をIs演算子で行います

オブジェクトの取得が完了していれば、処理を実行します
今回の処理では、A1セルに「1000」を入力してから、ブックの上書き保存を実行しています

このシートからの指定の最大の利点がアクティブを遷移させる必要が無いため、処理の高速化が図れる点です
シートの切り替えを行わずにバックグランドで処理を実行すれば画面更新の抑止も全く必要なくなります
セル選択を減らすことが処理の高速化につながりますが、さらにシート・ブックまでも選択を減らすことでさらに改善できます

また、Parentプロパティを使用することでブックの指定を行うことも可能なのでブックに対する処理も取得したオブジェクトで実行することが出来ます
あまり分かりやすいとは言えないので、せいぜいこの程度の処理で行うと良いです
Parentプロパティを経由すれば別のシートの操作も可能ですが、あまりしない方が良いですね

コード解説

Function zzzワークシート取得(ByVal hzz検索文字列, Optional ByVal hzzCN検索 As Boolean = False) As Worksheet

～～中略～～

End Function

引数を2つ使用する関数の作成です
取得したワークシートを返すので、関数の返し値の型はWorksheet型に指定しています
最後のEnd Functionまでが処理の中身になります

Dim zz対象Bk As Workbook, zz対象Sh As Worksheet, zz取得Sh As Worksheet

変数の宣言です
「zz対象Bk」はWorkbook型で、この関数の処理では開かれているすべてのブックを参照するので、そのループ処理に使用します

「zz対象Sh」と「zz取得Sh」はWorksheet型です
対象Shはループ用の変数で、取得Shは検索文字列と一致するものを見つけた時の代入先です

For Each zz対象Bk In Application.Workbooks
    For Each zz対象Sh In zz対象Bk.Worksheets

～～中略～～

    Next zz対象Sh
Next zz対象Bk
Set zz対象Sh = Nothing
Set zz対象Bk = Nothing

ここでは2つのオブジェクトループを行っています
1つ目が開かれているブック全てのループです
Application.WorkbooksはExcelが開いているブック全てを返すプロパティです

ここで1つ注意点として、このApplicationというのはVBAが実行されているブックを開いているExcelという意味になります
実際、Excelは複数起動することが可能です
単純にExcelブックを開いただけならExcelが複数起動することは無く、Excelの中でブックが複数開かれる状態になります

もし、あえて複数起動を行っている状況であれば少し複雑な処理になってきますので、ここでは割愛しますが、複数起動されていても同様の目的の処理を作成することは可能です

次に、その1つ1つのブックのシートのループです
zz対象Bk.Worksheetsというのは、1つ目のブックループで取得された1つのブックの中のすべてのシートを返すプロパティです
これですべてのブックのすべてのシートをループさせることが出来ます

オブジェクトループはループが終了すると代入変数は解放されます
しかし、今回の処理では目的のものが見つかった時点でループを最後まで実行せず途中で強制終了します
その場合は、変数が解放されません

そのため、ループを抜けた後に変数をそれぞれ解放しています

        If hzzCN検索 = True Then
            If zz対象Sh.CodeName Like hzz検索文字列 Then
                Set zz取得Sh = zz対象Sh: Exit For
            Else: End If
        Else
            If zz対象Sh.Name Like hzz検索文字列 Then
                Set zz取得Sh = zz対象Sh: Exit For
            Else: End If
        End If

シートのループまで実行して、1枚のシートが取得されたら
そのシートの名前もしくはオブジェクト名が指定のものかどうかを判定します

その判定基準が引数で指定されたものになるので、まずはhzzCN検索がTrueなのかどうかで処理を分岐します

もしTrueであればオブジェクト名で判定を行うことになります
オブジェクト名はCodeNameプロパティを使用することで取得できます
このプロパティは取得専用です

またFalseであった場合はNameプロパティを使用して判定します
Nameプロパティはシートの見出しに表示されている文字列なのでユーザーが認識しているシートの名前です

この2つのプロパティはどちらが良いというものでは無いので、関数としてどちらも検索を行えるようにしました
あえてこの2つを並列に検索するようなことはしていませんが、もしいずれかにでも指定文字列が含まれるかを調べる関数にしたい場合はここの処理分岐を無くせば可能です

                Set zz取得Sh = zz対象Sh: Exit For

そしてどちらにしても、検索文字列と一致するものであった場合は取得変数に代入してループを抜けます

特に意味は無いのですが、ここでは「:」を使用して処理を1行に収めています

        If Not zz取得Sh Is Nothing Then
            Exit For
        Else: End If

シートのループが終了もしくは抜けてきたら、zz取得Shが代入済みかを判定します
この時点で代入済みであればブックのループももう必要ないのでループを抜けます

シートが見つかった時点でGotoを使用すれば一気にブックループも抜けられますが、基本的にそういった処理の組み方はお勧めできません
変数の型によってはロックしたりしてしまいます

ループの個数分、しっかり終了させるようにしてください

If Not zz取得Sh Is Nothing Then
    Set zzzワークシート取得 = zz取得Sh
    Set zz取得Sh = Nothing
Else: End If

最後に、ブックのループを終了もしくは抜けてきた場合にzz取得Shが代入済みかを判定します
ここで代入済みであれば、その代入されたオブジェクトを返し値に設定します
それが済めばzz取得Shは解放します

この1つ前の処理で、全く同じ条件式でIf分岐が存在していますが、そちらはあくまでもブックのループを終了させるかどうかの判定用です
こちらとは目的が違っているので、同じ条件式ならまとめてしまっていいじゃないの～と感じるかもしれませんが、そこは丁寧にコーディングしてあげてください

この関数を使用すれば、別ブックのシートなどをいちいちブック指定から行う必要がなくなるので非常にコードがスッキリします
最近は処理の最前にこの関数があるのが当たり前になってきましたので、実際に使い慣れてくるとめちゃめちゃ便利です