Excel VBA でシートのイベントから呼び出した標準モジュールの Range メソッド実行について

Question

Excel VBA で標準モジュールから Range メソッドの実行

Excel2016 の VBA で、ワークシートのイベントから、標準モジュールの関数を呼び出しています。
ThisWorkBook はキチンと取得できているようですが、ActiveSheet が取得できないようです。
WorkSheet("Sheet1").Select としても、Range メソッドが失敗します。
WorkSheet("Sheet1").Name はキチンと「Sheet1」が取得できています。
どうしてアクティブなシートなどが取得できずに Range メソッドが失敗するのでしょうか？
因みに下記コードの Cells プロパティは正常に取得できています。

発生している問題・エラーメッセージ

実行時エラー 1004
「アプリケーション定義またはオブジェクト定義のエラーです。」

該当のソースコード

（一部抜粋）
Dim wSh As WorkSheet
Dim sTemp As String

sTemp = ThisWorkbook.Sheets("Sheet1").Name '←これは「Sheet1」が取得できる
Set wSh = ThisWorkbook.Sheets("Sheet1") '←正しい？（複数やった中の方法のひとつ）
sTemp = wSh.Range(Cells(2, 3)) '←ここでエラーとなる

試したこと

関数の引数として ActiveSheet を参照渡しもしてみましたが変わりませんでした。
同じように WorkBook を参照渡しすると、そちらは正常に渡っているようです。
色々とシートの指定方法は試してみましたが、状況は変わりませんでした。
ちなみに、ローカルウォッチで確認すると、
一応 WorkSheet オブジェクトとして認識はされていそうです。

補足情報（FW/ツールのバージョンなど）

Windows7 SP1 32bit ＋ Excel2016 VBA
シートの「On_Click」から標準モジュールの Public 関数を呼び出しています。

Answer 1

受け取りたい型は？

sTemp = wSh.Range(Cells(2, 3))
の処理でsTempには何を受け取りたいのでしょうか？

C2セルに入力されている値ですか？
それともC2セルを指すRangeオブジェクトですか？

前者（セルの値の代入）であれば、
sTemp = wSh.Cells(2, 3).Value
とすればよいです。

単一のセル範囲指定になっていればRangeプロパティを使って
sTemp = wSh.Range(wSh.Cells(2, 3), wSh.Cells(2, 3)).Value
としても取得できると思います。
しかし複数セル範囲が指定されたらエラーがおきそうです。

後者（Rangeオブジェクトを代入）の場合、オブジェクトの代入になるのでSet句が必要です。
Set sTemp = wSh.Cells(2, 3)
または
Set sTemp = wSh.Range(wSh.Cells(2, 3), wSh.Cells(2, 3))

Rangeプロパティの指定方法について

ここからは補足情報です。

Rangeプロパティには引数の指定方法が２種類あります。
⇒MSDN

①Range ( arg ) プロパティ
②Range ( cell1, cell2 ) プロパティ

①の指定方法では、引数argにはセルのアドレス文字列("A1"・"B2:D5"など)を指定します。
結果、指定したアドレスの示すセル範囲をRangeオブジェクトとして返します。

②の指定方法では、引数cell1,cell2として２つのRangeオブジェクトを渡します。
結果、cell1を始点、cell2を終点としたセル範囲をRangeオブジェクトとして返します。

---

今回の指定方法は、引数が１つしか指定していませんが、渡しているのはCellsなのでRangeオブジェクトです。
しかしRangeプロパティにはRangeオブジェクトを１つだけ指定するような引数指定は用意されていません。

ここでExcelはこう考えます。
⇒Rangeオブジェクトを渡されたけど、引数は１つしかないからアドレス文字列なのかもしれない。
⇒RangeオブジェクトのValueプロペティの値をアドレス文字列として使ってあげよう。

しかしそのセルにはアドレス文字列として使用できるような文字は入力されていませんでした。
Excelはできる限りがんばったけど解決できずエラーとなります。

型を意識していないと思わぬ結果となりますよ、という話です。
参考になれば幸いです。

Comments

[kays0009]

jawa さん
ありがとうございます。
おっしゃるとおり、複数の要因が重なってエラーとなっていたようです。
Range の引数によって返す型が異なるのが理解できました。
ほかの返答でも記載しましたが、基本的に単一のセルへの操作を想定しておりますので、今回は Range ではなく Cells を利用しようと思います。

[jawa]

＞基本的に単一のセルへの操作を想定しておりますので、今回は Range ではなく Cells を利用しようと思います。
これは良い選択だと思います。
単一セルを扱うのであれば断然Cellsの方が扱いやすいですし、見る方も直感的でわかりやすいと思います。

＞Range の引数によって返す型が異なる
ここはちょっとニュアンスが違うような気がしますが、大丈夫カナ？

Rangeには引数の指定の仕方が２種類ありますが、どちらも返す型はRange型です。
Cellsは単一セル限定版のRangeだと思っていただければいいので、返す型は同じくRange型です。

Range型とはセルやセル範囲を表す型で、基本的にはそのままRange型として使用するのですが、Range型の以外の引数や変数に代入する際には、都合よく.Valueプロパティに置き換えて動作してくれるのです。

例えば今回sTempはString型で宣言していますが、ここにCells(2,3)を代入する際は冗長的に解釈されてCells(2,3).Valueが代入されるという訳です。
Rangeプロパティの引数にCellsを使う場合も同様で、引数が１つしかない場合は冗長的にCells.Valueに置き換えられているというわけです。

ご理解されているようでしたら蛇足ですみません。

Answer 2

sTemp = wSh.Range(Cells(2, 3))

RangeはwShから参照してますが、CellsはwShを使用していないのが原因と思われます。
以下のように修正してみてください。

sTemp = wSh.Range(wSh.Cells(2, 3))

または

With wSh
  sTemp = .Range(.Cells(2, 3))
End With

Comments

[kays0009]

ttyp03 さん
回答ありがとうございます。

早速上の方のコードに修正してみましたが、やはりエラーとなりました。
ちなみにエラーはトラップしていたので、今回は除外し詳細なメッセージが確認できました。
「’Range’メソッドは失敗しました’_Worksheet’オブジェクト」
やはり Range メソッドだけが失敗します…。

[ttyp03]

Rangeの場合、"A1"のような形式にするか、Cellsを使う場合は2点の範囲を指定しないといけませんでした。すみません。
以下で試してみてください。
sTemp = wSh.Range("C2")
または
sTemp = wSh.Range(wSh.Cells(2, 3), wSh.Cells(2, 3))

[ttyp03]

あ、あくまでもRangeオブジェクトが欲しいということでいいんですよね？
C2セルの値ではなく。

[kays0009]

ttyp03 さん
ありがとうございます。
確かに呼び出し元でも sTemp = Range(Cells(1,1),Cells(1,1)) とすることで取得できました。
ただし、呼び出された関数では同様のコードではやはり同じエラーになります…。
今時点では C2 セルの値が取得できればいいのですが、将来的に Range で取得する可能性もあると思い、Range での取得を考えております。

[ttyp03]

いや、ですから、Cellsに対してwShを付加してください。

[kays0009]

ttyp03 さん
はい、関数側では Cells に対しても wSh を付与していますが、やはり Range メソッドにてエラーになります。
正確に言うと With ステートメントを利用して、wSh に対して実施しております。

[ttyp03]

ん？なんかおかしいですね。
一応動作確認して書いているので大丈夫だと思うのですが。
以下、全部ダメですか？
➀sTemp = wSh.Range("C2")
②sTemp = wSh.Range(wSh.Cells(2, 3), wSh.Cells(2, 3))
③
With wSh
sTemp = .Range(.Cells(2, 3), .Cells(2, 3))
End With

[kays0009]

ttyp03 さん
①以外は試しましたが、やはりダメでした…。
②③は同じ Range メソッドでエラーが出ました。
また休み明けに試してみたいと思います。

[ttyp03]

今気づきましたが、
Dim sTemp As String
これじゃないですか？
と思ったんですが、対象が１セルであるなら問題ありませんでした。
将来的に複数範囲を対象とするのであれば、
Dim sTemp As Variant
とすれば配列で受け取ることが可能です。

そもそもエラーが「アプリケーション定義またはオブジェクト定義のエラーです。」だとすると、wShが正しく設定できていない、RangeまたはCellsがwShを使用していない、としか思えません。
休み明けに状況が変わったらお知らせください。

[kays0009]

ttyp03 さん
sTemp を Variant 型にしても結果は同じでした。
今回の目的は各セルの情報を確認・加工・配列への格納ですので、Range は使用せずに Cells でいこうと思います。

因みに、
--------------------------------------------------
Dim sTemp As String
Dim rngTemp As Range
Dim wSh As Worksheet

With Worksheets("Sheet1")
sTemp = wkbActBook.Name
sTemp = .Name
Set wSh = ThisWorkbook.Sheets("Sheet1")
Set rngTemp = .Range(.Cells(2, 3).Cells(2, 3))
End With
--------------------------------------------------
としても、やはり Range の格納で同じエラーが発生しました。
ありがとうございました。

[ttyp03]

> .Cells(2, 3).Cells(2, 3)
間にカンマがないですよ。
.Cells(2, 3), .Cells(2, 3)

[kays0009]

ttyp03 さん
すみません、単純にコードの TYPO でした。
Set rngTemp = .Range(.Cells(2, 3), .Cells(2, 3))
とすることで、Range 型で取得することができました。
同じく Variant 型での受け取りも成功しました。
ありがとうございました。

Answer 3

Cells(2, 3)の文字列が欲しいならこうかな？

    Dim wSh As Worksheet
    Set wSh = ThisWorkbook.Worksheets("Sheet1")
    
    Dim sTemp As String
    sTemp = wSh.Cells(2, 3).Text

Comments

[kays0009]

hihijiji さん
ありがとうございます。
おっしゃるとおり、単純にひとつのセルの値を取得するだけなら特に問題ないと思うのですが、将来的に Range を利用することを想定して原因と対策を探しているところです。
何故か Range が落ちてしまう…。

Answer 4

Rangeへの引数が1つだけの場合、Rangeのアドレス(文字列)指定とみなされているのではないでしょうか
つまり、Cell(2,3)に入っている文字列をRangeのアドレスとして参照しようとしていると。

wSH.Range(wSH.Cells(2,3),wSH.cells(2,3)) ならエラーになりません。

Comments

[kays0009]

h.horikoshi さん
ありがとうございます。
ttyp03 さんも同様のことをおっしゃったのですが、呼び出し元だとうまくいくのに、関数上でやると Range でエラーとなってしまいます…。
どうも Sheet の中身が悪いんではないかと思っているのですが、ローカルウォッチでみると、普通のシートと同じようなプロパティを保持しているように見受けられるのですが…。

Answer 5

他の方の回答まで見れてないでの既出かもですが、
原因は、Range や Cells の[.]無しエラー系です。

wSh.Range(Cells(2, 3)) の「Cells()」は何を参照しているのでしょうか？

Range、Cellsは[.]無しだと、ActiveSheet が参照されるので、
wSh.Range(ActiveSheet.Cells(2, 3)) と解釈されます。
そうなると、どうやってRangeオブジェクトが取得されるのでしょうか？

wSh.Range(Cells(2, 3).Address) なら取得できるはずです。

余談ながら、自分はシートのRange、Cellsを参照する際は、
基本的には With で囲むようにしています。
Withの範囲で他のシートのセルを参照しなければならない場合は、
局所的にWithを使用して、Range型変数にいれて処理させています。
少々面倒ですが、意味不明なエラーに悩まされなくて済むので。。。

Comments

[kays0009]

ExcelVBAer さん
ありがとうございます。
厳密に言うと、Cells が参照しているのは ActiveSheet であることは認識しております。
今回は該当セルの値が取得できればいいので、Cells でもよかったのですが、今後のことも考慮して Range を使用してみました。
wSh.Range(Cells(2, 3).Address)
であっても、Cells は ActiveSheet を参照するのではないでしょうか？
先にも書いたとおり、基本的にはセル単位での処理を想定しておりますので、今回は Cells を使用したいと思います。

[ExcelVBAer]

言葉足らずだったようですね。
Range([Cell],[Cell]) の [Cell] は
同じ親（シート）でなければ処理できずにデバッグが起きる、
という事です。

wSh.Range(Cells(2, 3).Address) では、
確かにCells自体はActiveSheetを参照しますが、
Rangeに渡されるのはあくまでアドレス（A1等）なので
Rangeメソッドは処理できます。
※但し、アドレスにシート名等が付いてると(Sheet1!A1)、
　もちろん、例のエラーとなります。

参考になりましたら幸いです。

Answer 6

色々な問題が複合的に重なっているようですね。皆様の回答をまとめると

まずrangeで取得したいのであれば、宣言部は

Dim sTemp As Range

格納する際は

set　sTemp = wSh.Range(Cells(2, 3),Cells(2, 3))

別シートから参照される場合はcellsが意図しない動作(プログラマにとって、実際は仕様通り)をするのを防ぐために

With wSh
Set sTemp = .Range(.Cells(2, 3), .Cells(2, 3))
end with

としましょう。そしてなんか引っかかったので、原点に返ってみると

引用テキスト標準モジュールの関数を呼び出しています。

ということは、ユーザ定義関数としてセルで=関数名()とかで呼び足しているということでしょう。
それと・・

rangeが落ちる

つまりセルに期待した結果が返ってこない。コードを実行すると動くのに、セルの再計算とかでは反映されない。といったことなのではないでしょうか？

functionをユーザー定義の関数として使う場合

セルの挿入/削除
他セルの値の変更
アクティブセルや選択範囲の変更
シートの挿入/削除や名前の変更
Excelの設定の変更

などはできないです。(そーゆう仕様です)
つまり

sTemp.Select

これ出来ないです。

なぜかというと、セル一つ一つに関数が仕掛けられた場合、各セルが計算されだけでシート名が変わったり画面が違うシートに移動したりしたら、そのシート使い物にならないからです。
セルから呼び出される関数なのかsubから使われるfunctionなのかで実装の仕方が違うということをお伝えします。

Comments

[kays0009]

rinren さん
ありがとうございます。
ttyp03 さんにも返信しましたとおり、Range 型にしてもエラーになりました。
ので、Set することができませんでした。
そもそもセルから関数を直接 Call しておりません。
シートに作成したボタンのクリック時のイベントから関数を呼び出しています。

[kays0009]

rinren さん
すみません、ttyp03 さんにも返信したとおり、コードの TYPO でした。
Range 型、Variant 型双方での取得が成功しました。
ありがとうございました。