シート名をセル値から取得してWorksheetを指定する方法

例えばこんなブックを処理するとしましょう。

シート名のリスト

このような種類ごとにシートが分かれたデータを扱う際、
セルの値からシート名を取得したくなることがあります。

などを実装するときですね。

例えばA1セルからシート名を取得する際、

Set ws処理シート = Worksheets(Range("A1"))

こんなコードを書きたくなるのですが、

型が一致しません

というエラーになってしまします。

このページにたどり着いた方は、このエラーの解決策を探されていたかもしれません。

実はこの解決策、非常に簡単で、

Set ws処理シート = Worksheets(Range("A1").Value)

と、Valueプロパティを省略せずに明示するだけでOKです。

この解決策を探すのに時間を食っていたり、
わざわざ、

Dim シート名 As String: シート名 = Range("A1")
Set ws処理シート = Worksheets(シート名)

と変数を用意された方は、肩透かしを食いそうな簡単さですね。

ちなみにシート名が「1」「2」「0401」のように数値の場合は、
単にWorksheets(Range("A1").Value)とすると、
シート番号の指定と認識されてしまいます。

その場合は、　

Set ws処理シート = Worksheets(Range("A1").Text)

と、Textプロパティを使ってセル値を読み込むか、

Set ws処理シート = Worksheets(Range("A1") & "")

と、セル値に""を結合するなどして、VBAさんに「文字列だよ」と教えてあげれば、シート名として処理させることができます。

Set ws処理シート = Worksheets(CStr(Range("A1")))

と、CStr関数でStringにしてから渡してもOKですね。

このエラーがなぜ起きるかを解説したいのですが、
ぶっちゃけよくわかりません(笑)

ちょっと難しい話かもしれませんので、分からなかったら無視してOKです。

なので、

「Variant型の引数にRangeオブジェクトを渡した
　⇒ LongでもStringでもないから型が一致しません」

なのかな？と最初は思ったのですが、違うんですよね。

A1セルに「1」と数値を入れた場合は、

Worksheets(Range("A1"))

これが普通に動くんですよ。
インデックス番号として動き、第1シートを取得できます。

つまり、セルに数値が入っているときは、WorksheetsにRangeオブジェクトを渡しても動かせるわけです。

セルの文字列でシート名を指定しようとしたときだけ、エラーが起きるということです。

現象だけなんとか説明すると、

Worksheetsは、Stringを渡せばオブジェクト名として動き、Longを渡せばインデックス番号として動く。
WorksheetsにRangeオブジェクトを渡した場合は、値の種類に関わらず、インデックス番号バージョンのWorksheetsが動く？
このインデックスバージョンのWorksheetsに実際にValueプロパティを渡すことになり、インデックス番号LongにStringを渡せないため、型が一致しないエラーがでる。

みたいな感じなんでしょうかね？

関数内部の動きまではまったく説明できないので、これで勘弁してください(笑)

なお、Workbooks(Range("A1"))という記述でも、まったく同じ症状が起きます。

セル値を使ってシートやブックを指定する際は、
この仕様に気を付けていきましょう。

ちなみに、混乱を避けるために「Worksheets」と濁して書きましたが、
Worksheets(x)という記述は、Worksheetsプロパティの引数にxを渡しているわけではありません。

「Worksheetsプロパティで取得したSheetsオブジェクト」の、
「_Defaultプロパティにxを渡した」という扱いになります。

その上で、

Worksheets.[_Default](Range("A1"))
Worksheets.Item(Range("A1"))

これはどちらも、「セル値が数値ならOK/文字列ならエラー」という、
Worksheets(Range("A1"))の時と同じ症状になります。

_Defaultプロパティが何か裏でやってるのかな？とも思いましたが、

Worksheets.Item(Range("A1")) ' セル値が数値ならOK/文字列ならエラー
Worksheets.Item(Range("A1").Value) ' どちらもうまくいくようになる

このようにストレートなItemプロパティでの指定でも同じ現象が起きるようです。
どういう理屈なんでしょうね？教えて偉い人(´∀｀)

この辺のプロパティの省略仕様は別に知らなくても、実務上は問題ないと思います。

心の片隅にでも、置いておいてください。

和風スパゲティのレシピ