はじめに
ExcelVBAでは、シートを非表示にしてもセルの値にアクセスする事はできます。
ただ、非表示シートをSelectするとエラーとなります。また、非表示とは関係なしに選択されていないシート内のセルをSelectしようとするとエラーとなります。
実例を挙げて解説します。
非表示シートはループのメンバーになる
ブック内の全てのシートをループする際、非表示になっているシートも参照されます。
以下のプログラムでは、ブックに以下のシートがありますが、表示・非表示に関わらず選択されていることがわかります。
・Sheet1 (表示)
・Sheet2 (表示)
・hidden (非表示)
Sub test()
Dim i As Long
For i = 1 To Worksheets.Count
Debug.Print Worksheets(i).Name
Next i
End Sub
【結果】
Sheet1
Sheet2
hidden
hidden シートは非表示になっていますが、参照されています。
非表示のシートを選択するとエラーとなる
非表示になっているシートを選択(Select)するとエラーとなります。
Sub test1()
Dim i As Long
For i = 1 To Worksheets.Count
Worksheets(i).Select
Next i
End Sub
3番目の「hidden」シートを選択(Select)しようとした時、
「Worksheet クラスの Select メソッドが失敗しました。」
というエラーが発生します。
非表示シートは選択できません。
非表示シートのセルを選択するのでなければ、値を取得したり、セットする事は可能です。
以下のプログラムは、選択(Select)していないのでエラーとなりません。
Sub test2()
With Worksheets("hidden")
Dim rng
Dim total As Long
For Each rng In .Range("A1:A5")
total = total + rng
Next rng
Debug.Print total
End With
End Sub
非表示シートをSelectしたい場合は表示設定にする
シートの表示・非表示設定は、Visibleプロパティで設定できます。Trueにすれば表示、Falseにすれば非表示となります。
尚、アクティブになっていないシートのセルを選択するとエラーとなります。以下のプログラムでは、一旦 Select して hedden シートをアクティブにしています。
Sub test3()
With Worksheets("hidden")
.Visible = True 'シートを表示設定に変更
.Select 'シートを選択
.Range("A1:A5").Select 'セルを選択
Dim rng
Dim total As Long
For Each rng In Selection
total = total + rng
Next rng
Debug.Print total
.Visible = False 'シートを非表示に変更
End With
End Sub
CurrentRegion や UsedRange はエラーにならない
Rangeをセットする、CurrentRegion や UsedRange はセルを選択するわけでは無いのでエラーにはなりません。セルの範囲を取得するだけで選択(Select)してはいないからです。
Sub test4()
With Worksheets("hidden")
Dim cr
Set cr = .Range("A1").CurrentRegion
Dim rng
Dim total As Long
For Each rng In cr
total = total + rng
Next rng
Debug.Print total
End With
End Sub
Sub test5()
With Worksheets("hidden")
Dim cr
Set cr = Worksheets("hidden").UsedRange
Dim rng
Dim total As Long
For Each rng In cr
total = total + rng
Next rng
Debug.Print total
End With
End Sub
まとめ
非表示シートは、値を参照・設定する事はできますが、選択(Select)するとエラーとなります。選択したい場合は、Visible = True として表示状態にしておく必要があります。
変更されたくない設定値などを非表示シートに設定しておくという手法を取る場合、非表示から表示に切り替えなくても値の取得やセットを行う事ができます。
シートの Select は、マクロの記録などでは頻繁に出てきますが、VBAプログラムでは明示的にシートを表示させたい時以外は使いません。
尚、アクティブでないシートのセルを選択(Select)しようとすると、
「Range クラスの Select メソッドが失敗しました。」
というエラーが発生しますので注意してください。もちろん選択(Select)でなく参照であればエラーにはなりません。