質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

6回答

1638閲覧

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

kays0009

総合スコア8

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

0クリップ

投稿2018/02/09 07:38

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

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

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

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

該当のソースコード

Excel2016

1(一部抜粋) 2Dim wSh As WorkSheet 3Dim sTemp As String 4 5sTemp = ThisWorkbook.Sheets("Sheet1").Name '←これは「Sheet1」が取得できる 6Set wSh = ThisWorkbook.Sheets("Sheet1") '←正しい?(複数やった中の方法のひとつ) 7sTemp = wSh.Range(Cells(2, 3)) '←ここでエラーとなる

試したこと

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

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

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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答6

0

受け取りたい型は?

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プロパティには引数の指定方法が2種類あります。
⇒MSDN

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

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

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


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

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

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

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

投稿2018/02/09 09:32

編集2018/02/09 09:47
jawa

総合スコア3013

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

kays0009

2018/02/13 00:45

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

2018/02/13 01:33 編集

>基本的に単一のセルへの操作を想定しておりますので、今回は Range ではなく Cells を利用しようと思います。 これは良い選択だと思います。 単一セルを扱うのであれば断然Cellsの方が扱いやすいですし、見る方も直感的でわかりやすいと思います。 >Range の引数によって返す型が異なる ここはちょっとニュアンスが違うような気がしますが、大丈夫カナ? Rangeには引数の指定の仕方が2種類ありますが、どちらも返す型はRange型です。 Cellsは単一セル限定版のRangeだと思っていただければいいので、返す型は同じくRange型です。 Range型とはセルやセル範囲を表す型で、基本的にはそのままRange型として使用するのですが、Range型の以外の引数や変数に代入する際には、都合よく.Valueプロパティに置き換えて動作してくれるのです。 例えば今回sTempはString型で宣言していますが、ここにCells(2,3)を代入する際は冗長的に解釈されてCells(2,3).Valueが代入されるという訳です。 Rangeプロパティの引数にCellsを使う場合も同様で、引数が1つしかない場合は冗長的にCells.Valueに置き換えられているというわけです。 ご理解されているようでしたら蛇足ですみません。
guest

0

ベストアンサー

VBA

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

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

VBA

1sTemp = wSh.Range(wSh.Cells(2, 3)) 2 3または 4 5With wSh 6 sTemp = .Range(.Cells(2, 3)) 7End With

投稿2018/02/09 07:50

ttyp03

総合スコア16996

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

kays0009

2018/02/09 07:56

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

2018/02/09 08:31

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

2018/02/09 08:32

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

2018/02/09 08:48

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

2018/02/09 08:50

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

2018/02/09 09:01

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

2018/02/09 09:05

ん?なんかおかしいですね。 一応動作確認して書いているので大丈夫だと思うのですが。 以下、全部ダメですか? ➀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

2018/02/09 09:51

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

2018/02/09 10:46

今気づきましたが、 Dim sTemp As String これじゃないですか? と思ったんですが、対象が1セルであるなら問題ありませんでした。 将来的に複数範囲を対象とするのであれば、 Dim sTemp As Variant とすれば配列で受け取ることが可能です。 そもそもエラーが「アプリケーション定義またはオブジェクト定義のエラーです。」だとすると、wShが正しく設定できていない、RangeまたはCellsがwShを使用していない、としか思えません。 休み明けに状況が変わったらお知らせください。
kays0009

2018/02/13 00:17

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

2018/02/13 00:21

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

2018/02/13 00:27

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

0

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

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

投稿2018/02/09 08:19

hihijiji

総合スコア4150

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

kays0009

2018/02/09 08:49

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

0

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

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

投稿2018/02/09 08:10

h.horikoshi

総合スコア505

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

kays0009

2018/02/09 08:51

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

0

他の方の回答まで見れてないでの既出かもですが、
原因は、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型変数にいれて処理させています。
少々面倒ですが、意味不明なエラーに悩まされなくて済むので。。。

投稿2018/02/10 05:26

ExcelVBAer

総合スコア1175

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

kays0009

2018/02/13 00:37

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

2018/02/13 00:59

言葉足らずだったようですね。 Range([Cell],[Cell]) の [Cell] は 同じ親(シート)でなければ処理できずにデバッグが起きる、 という事です。 wSh.Range(Cells(2, 3).Address) では、 確かにCells自体はActiveSheetを参照しますが、 Rangeに渡されるのはあくまでアドレス(A1等)なので Rangeメソッドは処理できます。 ※但し、アドレスにシート名等が付いてると(Sheet1!A1)、  もちろん、例のエラーとなります。 参考になりましたら幸いです。
guest

0

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

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

vba

1Dim sTemp As Range

格納する際は

vba

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

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

vba

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

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

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

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

rangeが落ちる

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

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

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

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

vba

1sTemp.Select

これ出来ないです。

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

投稿2018/02/10 02:42

編集2018/02/10 02:45
rinren

総合スコア107

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

kays0009

2018/02/13 00:22

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

2018/02/13 00:28

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問