VBAのRangeオブジェクトについて

質問

いつもお世話になっております。

VBAのRangeオブジェクトについてご教示下さい。

例えば下記のPGを組みます。

Sub test()
Dim r As Excel.Range

Set r = Me.Range("A1")
Debug.Print r.Cells.Count
End Sub

この時、Debug.Printには"1"と表示されます。

Debug.Printでブレイクを置き、ここでA列を削除するとDebug.printでエラーが出てしまいます。

Rangeオブジェクト生成後、もしも参照先のセルが削除してしまった場合、条件分岐の処理を入れたいのですが、どのように処理をいれればよろしいでしょうか？

セル削除後、Rangeのプロパティを参照した時点でエラーが発生してしまいます。

Typeで確認するとRangeがちゃんと取れるのですが・・・。

"参照先のセルが消されたかどうか"を判別する方法はあるのでしょうか？

回答No.8

cj_mover · ベストアンサー

こんにちは。＃１、２、５、ｃｊです。
＃７さんへ。
不躾なお願いにも関わらず、ご確認いただいたこと感謝いたします。
＃２で私は、Err を問う以外に方法は"無い"という旨書いてましたが、
他にあるのなら知りたいという興味（向学心？）から
異例なことながら質問させていただいた次第です。
さて、＃４、＃７、にてお答えされている趣旨については（＃６も同様）
私も賛同できますし最初に回答した時点で同じようなことを考えていました。
私のは、はっきりわかり易く書かなかったということです。
可能かどうかは別としても、"VBA実行中の手作業によるセル削除"
を前提としたVBAの設計というのは、やはり奇異な印象を免れません。
私としてはVBAでどうこうする以前に、全体の設計や運用について
再検討されるのが望ましいと考えています。

"VBA実行中の手作業によるセル削除"
が可能かどうか、ですが、
このスレッドの進行の為にも私の方で例を挙げてみます。

Private Declare Sub Sleep Lib "KERNEL32.dll" _
　　　　(ByVal dwMilliseconds As Long)
Sub Re7822694yy()
　　Dim dummy
　　Dim i&
　　Dim r As Range

　　Set r = Range("A1")
　　Do Until i = 20
　　　　i = i + 1
　　　　DoEvents
　　　　Sleep 300　　' ここで手作業によるセル削除
　　　　DoEvents
　　Loop

　　On Error Resume Next
　　dummy = r.Count
　　If Err.Number <> 0 Then
　　　　MsgBox "Lost"
　　　　' ' r が不正な場合の処理
　　Else
　　　　MsgBox "Exist"
　　　　' ' r が正常にsetされている場合の処理
　　End If
　　On Error GoTo 0

　　Set r = Nothing
End Sub

（＃以上、一応、なるべく、見かける記述を選んだつもりです）

仮に、設計上、仕方なく（他に方法がなく）
"VBA実行中の手作業によるセル削除"が必要なのだとしても、
"VBA実行中の手作業によるセル削除"を許さないようにして
"VBAのコードからユーザー選択によるセル削除"をすることによって
必要な判別が可能になる、というのが、有力な方法だと思います。
（この点は＃４でも触れられていますね。）

以上のような前提で私の方からは、可能かどうかには触れず、
最初から教唆的な指摘は避けてコードのみで回答したりしていたのですけれど。
といって、質問者さんの意図にそっていたかというと、そうでもないようなので
必要なら、また追加レスするつもりなのです。

あらためて、
ありがとうございました。
　　　　　ｃｊ　拝

お礼

>cj_mover様、ki-aaa様、WindFaller様、x-1919様

皆様、色々な見解及びご指摘、本当にありがとうございました。
お礼が遅くなりました事をまず深くお詫びいたします。

私が知りたい状況は、x-1919様がおっしゃっている状態にあります。

Excel VBAで大昔に作られた既存の古いPGの回収作業で、
「Rangeオブジェクト生成後、長い処理中にユーザーがシートを操作できる状態の時があって、参照先のセルが削除された後に既存ロジック内でそのRangeオブジェクトを参照するとエラーで落ちる」状態にあるので、On error文ではなく、何らかの手法やAPI関数等で回避する方法があるのかなと思った次第です。

>cj_mover様
No.1で答えは出ていたのですね。
意図を深く読み取れずに申し訳ありませんでした。

>ki-aaa様
Is Nothingは試してみたのですが、No.5でcj_mover様がおっしゃるとおり、Falseが返ってきました。

不思議だったのが、参照先を手動で削除後、Rangeオブジェクトに対するプロパティ等を参照するとエラーが発生するのは分かるのですが、TypeName関数等で調べるとしっかりRangeと出てくるんですよね。。。

オブジェクトの型自体は生きている(?)のに、プロパティを参照した瞬間に実行時エラーとなってしまうのが不思議でした。

やはりOn Error Resume Nextでエラーを回避しつつやるしか方法はなさそうですね。

皆さん、色々とありがとうございました。
感謝致します。

回答No.7

WindFaller

#4の回答者です。#5さんの返事。

私が、#3さんの回答に対して、スポット的に正解と申した言葉に刺激されたとされれば、申し訳ありません。

確かに、おっしゃるとおり、Excel 2010/2003 でも、"r Is Nothing" では、False になります。昨日、手動でという所で、何か、どこかでミスしたのかもしれません。"r Is Nothing" で、コードが通ったので、Trueになると思って、私は良く調べませんでした。大変失礼しました。

'//
Sub Test_R3()
　　Dim dummy As Variant
　　Dim r As Range
　　Set r = Range("A1")
Stop
　　Columns(1).Delete　 '←ここを手動で行うか、どちらか
　　On Error Resume Next　'r Is Nothing の代わり
　　 dummy = r.Address
　　Debug.Print Err.Number = 0
　　On Error GoTo 0
End Sub
'//

こちらとしては、元の質問としては、ありえない展開の中で話を進めているわけで、今回の質問をどのように解釈するかにもよります。"r Is Noting" の代わりに、上記で通るはずですね。

「ありえない」とは言いながら、絶対にないと言えるかというと、VBAだけですと、私は、たかが10年程度ですが、そういえば、そんな経験もあったかな程度で、もう少し、ご質問者さんに状況を教えていただかないと、明確な回答はできない、というのが実際のところなのです。何か、モヤモヤっとした記憶が残っているだけなのです。ここらは、#6さんのご回答にも含まれていると思います。

それで、最初から話を詳しく教えていただいたほうが良いのかなって思っている次第なのです。

回答No.6

x-1919

#4 の指摘に賛成。

質問者が想定している現実的な可能性がわからない。
セル A1 を参照した次のステップをプログラムが実行するよりも速く、ユーザーがセルを削除することなどあるのだろうか、と。

セル A1 を変数 r が参照したして何らかの処理をしたあと、ユーザーがシートを操作できる状態の時があって、そのあとに r が正常な状態かどうかわからないっていうコードを書いているのであれば、ただちにプログラムの作り方を見直す必要があると思う。

「そんなに長い間、当初の A1 を同じ変数で参照しつづける必要があるのか」 って話。

それともう一つ。
もしそのセルが削除されていたら質問者としてはどうしたいのかって話。
これによっても回答が違ってくるんじゃないのか？
まぁ現実的には On Error GoTo でエラーラベルにジャンプさせてしまい、当初予定していた処理をキャンセルするのが精いっぱいって所だと思う。
だって VBA だもん。

回答No.5

cj_mover

皆さんこんにちは。＃１、２、ｃｊです。
＃３さん、＃４さん、宛てになってしまいますが

Sub Re7822694r()
　　Dim r As Range

　　Set r = Range("A1")
　　Debug.Print r Is Nothing

　　Columns(1).Delete
　　Debug.Print r Is Nothing
Stop
　　Set r = Nothing
End Sub

少なくとも私の環境（XL2010）で、以上のTestを実行すると
　　False
　　False
が返りますし、
ローカルウィンドウで確認しても Nothing にはなっていません。

スポット的に正解、というのは、ひょっとして環境によっては判別可能ということなのでしょうか？
すみませんが、教えていただけませんか？

回答No.4

WindFaller

こんにちは。

最初に、お使いのアプリ/言語はなんでしょうか？
Excelでもあるようで、Excelでもないような、VB.Net からでしょうか？
だとしても、Me キーワードは使いませんね。
あくまでも、Excel VBAオンリーとして解釈します。

Set r = Me.Range("A1")　
↑
ここで、オブジェクトそのものが生きているのですから、それを削除すればエラーが出るというのは当然だと思います。ブレイクポイントを置いて、手動で削除するなどは実験的に行うならともかく、現実の実務では成り立つものではありません。

>対象のセルが手動で削除された時に、途中でRangeオブジェクトを参照(プロパティ等使用)した際に、エラーで落ちてしまうのを回避したいと思っております。

対象セルを手動というのが、対話型VBAで、セルを削除するということはないとは言えませんが、それなら、そもそも、Set r = Range("A1")　などということはありえません。また、変数に確保していたら、改めて、再確保することになります。

確かに、#3さんのご指摘は、スポット的には正解だと思いますが、私は、もっと前の段階から考えてしまいます。実験でもない限り、一体、どんなにとをしているのか全体的なことを知りたいものですね。

'//
Sub Test_R()
　　Dim r As Range
　　Set r = Range("A1")
　　Stop　'手動で削除されたとする
　　Set r = Range("A1")
　　Debug.Print r.Cells.Count
End Sub

'//
Sub Test_Dialog()
　Dim r As Variant
　On Error Resume Next
　Set r = Application.InputBox("セルを選択してください", "セルの削除", Type:=8)
　If Err.Number > 0 Then Exit Sub
　On Error GoTo 0
　r.Delete
End Sub

'//

回答No.3

ki-aaa

こんにちわ

Findなどでよく使われる、is nothing then を使われたらどうでしょう。

if r Is Nothing Then　　'rが無かったら

回答No.2

cj_mover

こんにちは。＃１、ｃｊです。
> プログラム上でセルを削除するのではなく、Rangeオブジェクトを生成し、解放するまでに(何らかの重たい処理を行っているをご想像下さい)対象のセルが手動で削除された時に、途中でRangeオブジェクトを参照(プロパティ等使用)した際に、エラーで落ちてしまうのを回避したいと思っております。
>
> 削除はセルかもしれませんし、行毎かもしれませんし、列毎かもしれないため、Rangeオブジェクトが常に生きているか(?)のチェックが行えれば、条件分岐で回避できるかなと思った次第です。

はい、そういう理解でお応えしたのですけれど。
＃１のテストコードにある
If Rnd() > 0.5 Then Columns(1).Delete
は、コード側で判別できない"対象のセルが手動で削除された時"
を再現する意味でRnd()関数を使ったダミーです。
"Rangeオブジェクトが常に生きているか(?)のチェックが行えれば、条件分岐で回避できる"
私の提示した方法では"できない"と仰りたいのでしょうか？
だとすると、ご質問の意図が量り兼ねます。
例えば、その判定を目的としたプロパティ、メソッド、関数などがあるか、ということでしたら
無いです。
純粋にＶＢＡだけで判別する方法としては＃１の方法以外にないのではないでしょうか。

ＶＢＡ実行中にセルの削除を手動でする、という想定が少し不思議ではありますが、
プログラムという意味では、エラートラップで対処するべきものかと存じます。
それがお気に召さないということでしたらば、全体の設計や運用などを見直す
ということなのではないでしょうか。

回答No.1

cj_mover

Sub Re7822694y()
　　Dim dummy
　　Dim r As Range

　　Set r = Range("A1")
If Rnd() > 0.5 Then Columns(1).Delete

　　On Error Resume Next
　　dummy = r.Count
　　If Err.Number = 0 Then　　'　　If Err.Number <> 424 Then
　　　　MsgBox "Exist"
　　Else
　　　　MsgBox "Lost"
　　End If
　　On Error GoTo 0

　　Set r = Nothing
End Sub

というようなことでしょうか。

補足

cj_mover様

早速のご回答ありがとうございました。

プログラム上でセルを削除するのではなく、Rangeオブジェクトを生成し、解放するまでに(何らかの重たい処理を行っているをご想像下さい)対象のセルが手動で削除された時に、途中でRangeオブジェクトを参照(プロパティ等使用)した際に、エラーで落ちてしまうのを回避したいと思っております。

削除はセルかもしれませんし、行毎かもしれませんし、列毎かもしれないため、Rangeオブジェクトが常に生きているか(?)のチェックが行えれば、条件分岐で回避できるかなと思った次第です。