例えばこんなブックを処理するとしましょう。
このような種類ごとにシートが分かれたデータを扱う際、
セルの値からシート名を取得したくなることがあります。
- 処理するシートをセルの入力リストから選んでもらう
- 検索などで取得したセルからシートを特定する
などを実装するときですね。
例えばこんな感じでしょうか。
この時、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ですね。
おまけ：Valueを省略すると何故エラーになるのか
このエラーがなぜ起きるかを解説したいのですが、
ぶっちゃけよくわかりません(笑)
ちょっと難しい話かもしれませんので、分からなかったら無視してOKです。
Worksheets(index)という記述の、この引数indexはVariant型です。
index：オブジェクトの名前またはインデックス番号を指定します。
なので、
「Variant型の引数にRangeオブジェクトを渡した
⇒ LongでもStringでもないから型が一致しません」
なのかな？と最初は思ったのですが、違うんですよね。
A1セルに「1」と数値を入れた場合は、
Worksheets(Range("A1"))
これが普通に動くんですよ。
インデックス番号として動き、第1シートを取得できます。
つまり、セルに数値が入っているときは、WorksheetsにRangeオブジェクトを渡しても動かせるわけです。
セルの文字列でシート名を指定しようとしたときだけ、エラーが起きるということです。
現象だけなんとか説明すると、
- Worksheetsは、Stringを渡せばシート名として動き、Longを渡せばインデックス番号として動く仕様。
- WorksheetsにRangeオブジェクトを渡した場合はセル値に関わらずインデックス番号バージョンのWorksheetsが動く？
- 実際にRangeオブジェクトのValueプロパティが評価されるのはインデックス番号バージョンのWorksheetsが動いた後なので、型が一致しないエラーがでる。
みたいな感じなんでしょうかね？
関数内部の動きまではまったく説明できないので、これで勘弁してください(笑)
なお、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プロパティでの指定でも同じ現象が起きるようです。
どういう理屈なんでしょうね？教えて偉い人(´∀｀)
この辺のプロパティの省略仕様は別に知らなくても、実務上は問題ないと思います。
心の片隅にでも、置いておいてください。