和風スパゲティのレシピ

日本語でコーディングするExcelVBA

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

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

シート名のリスト

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

  • 処理するシートをセルの入力リストから選んでもらう
  • 検索などで取得したセルからシートを特定する

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

例えばこんな感じでしょうか。
セル値で指定したシートに処理するマクロ


この時、C2セルの値からシート名を取得するために、

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

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

型が一致しません

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


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


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

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

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


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

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

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


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

1,2番目のシートを処理しちゃったり、
401番目のシートがなくて「インデックスが~」が出ちゃったり。


その場合は、 

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

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

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

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

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

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

おまけ:何故エラーになるのか

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

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


Worksheets(index)という記述の、この引数indexはVariant型です。
index:オブジェクトの名前またはインデックス番号を指定します。


なので、

「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プロパティでの指定でも同じ現象が起きるようです。
どういう理屈なんでしょうね?教えて偉い人(´∀`)


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

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