例えばこんなブックを処理するとしましょう。
このような種類ごとにシートが分かれたデータを扱う際、
セルの値からシート名を取得したくなることがあります。
- 処理するシートをセルの入力リストから選んでもらう
- 検索などで取得したセルからシートを特定する
などを実装するときですね。
例えばこんな感じでしょうか。
この時、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プロパティでの指定でも同じ現象が起きるようです。
どういう理屈なんでしょうね？教えて偉い人(´∀｀)
この辺のプロパティの省略仕様は別に知らなくても、実務上は問題ないと思います。
心の片隅にでも、置いておいてください。