エクセル2000ではOKなのに2003でエラーになるマクロについて

質問

下記のVBAコードにつきご教示ください。
終了時にワークシート上に配置したCloseButtonを押すと、ダイアログシート上に配置したフォームのDropDownsやEditBoxesを初期化して保存する単純なマクロです。
エクセル2000では問題なく、2003以降で使用すると実行時エラー424「オブジェクトが必要です」となります。

ただし、いろいろ試したところ以下のことがわかりました。

1.エラーが出るのはCloseButtonを押した場合のみで、エクセル画面右上の×で終了する場合はエラーになりません。
2.終了時以外にSub 初期化()を単独で実行してもエラーになりません。
3.このBOOKの他にもBOOKを開いている場合、(Workbooks.Count= 1でない場合)エラーになりません。

なぜこうなるのか不思議です。
ご教示いただければ幸いです。

Sub CloseButton()
ans = MsgBox("終了しますか？", vbYesNo + vbQuestion, "　おしまい？")
Select Case ans
Case vbYes
Call Auto_Close
Case vbNo
Exit Sub
End Select
End Sub

Sub Auto_Close()
Call 初期化
ThisWorkbook.Save
If Workbooks.Count = 1 Then
Application.DisplayAlerts = False
Application.Quit
Else
ActiveWorkbook.Close (False)
End If
End Sub

Sub 初期化()
With DialogSheets("Dialog1")
.DropDowns.ListIndex = 1 'ここでエラー(「オブジェクトが必要です」)
.EditBoxes.Text = ""
End With
End Sub

回答No.2

onlyrom · ベストアンサー

>エクセル2000では問題なく
>2003以降で使用すると実行時エラー424「オブジェクトが必要です」となります。

これ勘違いではありませんか？
どのバージョンでもエラーになると思いますが。。。

それは置いといて。

＞1.エラーが出るのはCloseButtonを押した場合のみで、エクセル画面右上の×で終了する場合はエラーになりません。
＞2.終了時以外にSub 初期化()を単独で実行してもエラーになりません。
＞3.このBOOKの他にもBOOKを開いている場合、(Workbooks.Count= 1でない場合)エラーになりません。

質問の件、上記の件も全て、Auto_Close　が原因です。
Auto_Closeがどういうときに実行されるか理解していると問題は回避できます。

要するに、CloseButtonをクリックして終了すると、
Auto_Closeプロシージャが２回実行されるということです。

Auto_Closeの●の部分にMsgBoxを入れてみてください。確認できます。

Sub Auto_Close()
　　●MsgBos "AutoCLose"
　Call 初期化

お礼

ありがとうございます。

>> エクセル2000では問題なく
>> 2003以降で使用すると実行時エラー424「オブジェクトが必要です」となります。

> これ勘違いではありませんか？
> どのバージョンでもエラーになると思いますが。。。

いえ、勘違いではありません。
会社の2000と知人の2007および自宅の2003でそれぞれ試した結果です。

> 要するに、CloseButtonをクリックして終了すると、
> Auto_Closeプロシージャが２回実行されるということです。

ありがとうございます、その通りですね。

Sub Auto_Close()
Static st As Integer
st = st + 1
If st > 1 Then Exit Sub
Call 初期化
ThisWorkbook.Save
If Workbooks.Count = 1 Then
Application.DisplayAlerts = False
Application.Quit
Else
ActiveWorkbook.Close (False)
End If
End Sub

で、あっけなく解決しました。
しかし、２度目だとなぜエラーになるのでしょうか？
掲示したコードは関係ない部分を省略していますが、Dialogsheet以外に行う操作（セルのクリア等）はエラーになりません。

回答No.6

onlyrom

onlyromです。

同じバージョンの2000でエラーが出たりでなかったり、おかしいですね。
が、その理由を調べる術がありませんので、すっきりしませんがこの問題はこれで終了ということで。。。(^^;;;

＞わたしの不勉強のせいでユーザーフォームやコントロールツールボックスが使いこなせないからです

質問者はいろいろ回答もされてますよね。
VBAの知識は回答できるほどにあるということですから、
そのダイアログシートのコードをユーザーフォームに書き直していく過程で覚えるようにしたらどうでしょうか。

因みに当方はダイアログシートは使ったことありせん。
今回の質問に回答するために初めて使ってみました。
とはいってもコントロールをひとつふたつ配置しただけですが。。

何れにしろ、質問者は仕事でVBAを使われているみたいですから
ユーザーフォームは是非覚えるべきだと考えます。

お礼

> ユーザーフォームは是非覚えるべきだと考えます。

おっしゃる通りですね。
近々ユーザーフォームやコントロールツールボックスの使用法でまたいろいろ質問することになると思います。

ただ、今回のはDialogSheetの問題ではないことがわかりました。
ためしにワークシート上にフォームのDropDownと、Editboxはワークシートに配置できないのでTextBoxをおいてみたところ２回目に通ったところで同じようにエラーが出ました。

回答No.5

onlyrom

再度の登場です。

>エクセル2000では問題なく
>2003以降で使用すると実行時エラー424「オブジェクトが必要です」となります。

これが勘違いでないかと言いましたのは、
auto_Closeが２度実行されることと、
手持ちのxl2000（xl2003が手元にない）でも同じエラーが出たからです。
同じxl2000でありながらエラーが出たり出なかったりも不思議といえば不思議ですが、office updateの違いかも知れません。
因みに、バジョン情報は、xl2000 (9.0.6926 SP-3)　です。

＞しかし、２度目だとなぜエラーになるのでしょうか？
＞掲示したコードは関係ない部分を省略していますが、Dialogsheet以外に行う操作（セルのクリア等）はエラーになりません。

いまいくつかコードを試してみたところ、どうもApplication.Quitに原因があるようです。
Application.Quitでブックがクローズされる場合、dialogsheetに対しては今回のような動作をする、そのような仕様になっているのかもしれません。

何れにしろ、今回のように一度クローズされた（されてる最中）のブックに対して、再度クローズ処理をするようなことは避けるべきでしょうね。

これでは参考意見にもなってないですね。(^^;;;

（疑問）
なぜ、いまダイアログシートなのでしょう。

お礼

何度もありがとうございます。2000のバジョン情報は会社に行かないとわからないので明日、補足欄に記入しますね。

> どうもApplication.Quitに原因があるようです。

そうですね。ActiveWorkbook.Close (False)のほうでは起きませんから。

> dialogsheetに対しては今回のような動作をする、そのような仕様になっているのかもしれません。

DialogSheet自体がエクセル95時代の遺物なのでしょうね。

> なぜ、いまダイアログシートなのでしょう

きっと誰かに言われるとは思っていました。原因は簡単、わたしの不勉強のせいでユーザーフォームやコントロールツールボックスが使いこなせないからです。最初にわたしにVBAを手ほどきしてくれた人が規定のMsgbox以外で同様の表示をする方法としてこれを教えてくれたのです。すでにエクセル97の時代でしたが。ですからついついなじみのあるダイアログシートやフォームに頼ってしまいます。2007になってダイアログシートやフォームが使えなくなるのではとひやひやしていましたが2007形式で保存しても大丈夫なので今のところは安心しています。

補足

会社のエクセル2000のバージョンを調べました。 2000(9.0.6926 SP-3)　です。同じですね。
でも、今もやってみましたがエラーが出ません。2台の端末（両方とも2000の同じバージョン）で試しましたが同じです。
Auto_Closeを2回通っていることも
Static st As Integer
st = st + 1
MsgBox st
をAuto_Closeに入れて確認済みです。

不思議ですね。

回答No.4

Wendy02

こんばんは。

>しかし、Auto_Close() 側から 'Call 初期化' を抜いてしまうと、

どうして、Auto_Close()で、しなくてはならないのでしょうか？
必要があるなら、ブックの起動時に行ってもよいと思います。それでも可能なはずですね。

それに、
>「×」を使用不能にすればいい

方法は、いろいろあると思いますが、もし、どうしても、というなら、以下のように、私ならフラグを立てますね。Auto_Close()側でも可能ですが、あえて、ステップ・イン・モードで試すとエラーが発生するので、初期化()側にフラグを立てました。

>２度目だとなぜエラーになるのでしょうか？
それは、ステップ・インでやると、また、Auto_Close に戻るというわけですよね。ただ、通しで行ってみると、#3の私のコードは、仮に二回通っているのかもしれませんが、それではエラーが出ません。しかし、私のコードの'Call 初期化'を、Auto_Close()側に入れたら、エラーが返るはずです。

ただ、子のオブジェクトから、子プロセスで、確実に親オブジェクトに返ればよいのですが、再び、子のオブジェクトに戻ろうとしても、子のオブジェクトが終了しているのか、クローズしているのかで取れないのではないでしょうか？これは私は単なる想像で、屁理屈と聞こえるかもしれませんし、結果に対して、推論はどのようにも立てられますね。

いずれにしても、子のオブジェクトの中で、子のプロセスから、親オブジェクトを終了させるときに、子オブジェクトのプロパティを変えようとすると、それを失っているというメッセージが返るという問題は、マイグレーションをざっと検索してみましたが、出てきませんでした。ちゃんと読めば出てくるかもしれません。

何かのセキュリティのために、そうできないようにしたとしか思えません。

---------------------------------------

Private flg As Boolean
Sub CloseButton()
　　Dim ans As Integer
　　ans = MsgBox("終了しますか？", vbYesNo + vbQuestion, "　おしまい？")
　　Select Case ans
　　　　Case vbYes
　　　　　Call 初期化
　　　　　flg = True　'←ここ
　　　　　Call Auto_Close
　　　　Case vbNo
　　　　　　Exit Sub
　　End Select
End Sub

Sub 初期化()
　　If flg = True Then Exit Sub　'←ここ
　　With DialogSheets("Dialog1")
　　　　.DropDowns.ListIndex = 1
　　　　.EditBoxes.Text = ""
　　End With
End Sub

お礼

何度もありがとうございます。

> 必要があるなら、ブックの起動時に行ってもよいと思います。それでも可能なはずですね。

はいAuto_Openに入れればもちろん可能です。しかし、今回のはブックの起動時に自動的に初期化を行いたくない事情がありました。提示のコードでは終了時に全て初期化し保存していますが、実際のコードはある特定の条件でのみ初期化を行うのです。

> 私ならフラグを立てますね。

なるほど、こういう方法があるんですね。とても勉強になります。
いつもありがとうございます。

回答No.3

Wendy02

こんにちは。

前の話の続きのようですが、終了時に、プロパティ値を変化させることが可能か、可能でないか、という問題で、Excel 2000 と Excel 2003 の違いがあるのだと思います。

しかし、この命令を受け取ったときに、
　Application.Quit
Excel.Application としての対応の仕方だと考えると、時系列で動いているものなら、この話のつじつまが合いません。

私の単なる想像ですが、ここら辺も、やっぱり、VBA(少なくとも、Excel2003）は、スクリプト言語とは違うものなんだろうなって思います。もし、完全にスクリプト言語なら、ありえませんね。

それに関連してなのですが、直接的には、

Sub CloseButton()　= 命令の範囲がどこまでか?

ということですね。

私は、ボタンのオブジェクトの中で、DialogSheet の属性のオブジェクトが生きている間に、命令を出せば、問題は出ないはずだと考えました。

ためしに、

Sub Auto_Close() 側から 'Call 初期化' を抜いて、Sub CloseButton() に以下のように入れてみたらどうでしょうか？

Sub CloseButton()
　　Dim ans As Integer
　　ans = MsgBox("終了しますか？", vbYesNo + vbQuestion, "　おしまい？")
　　Select Case ans
　　　　Case vbYes
　　　　　 Call 初期化
　　　　　 Call Auto_Close
　　　　Case vbNo
　　　　　　Exit Sub
　　End Select
End Sub

'なお、私は、PERSONAL.XLS を入れていますので、このようなコードにしました。

Sub Auto_Close()
　　Dim i As Integer
　　Dim j As Integer
　　ThisWorkbook.Save
　　For i = 1 To Windows.Count
　　　　If Windows(i).Visible Then
　　　　　　j = j + 1
　　　　End If
　　Next i
　　If j = 1 Then
　　　　Application.DisplayAlerts = False
　　　　Application.Quit
　　Else
　　　　ActiveWorkbook.Close (False)
　　End If
End Sub

それと、ヘルプを読んでみましたが、.DropDowns　や .EditBoxes というのは、コレクションではなくて、コントロール配列のようですね。ヘルプでも、DropDows ListIndex プロパティが存在しているので、かなり、コントロールツール(Active X)とは、考え方が違うようです。

たかが、VBAですが、やっぱり、知らないことが一杯あります。

お礼

> Sub Auto_Close() 側から 'Call 初期化' を抜いて、Sub CloseButton() に以下のように入れてみたらどうでしょうか？

ありがとうございます。
このやり方でもうまくいきました。しかし、Auto_Close() 側から 'Call 初期化' を抜いてしまうと、×を押して終了された場合、初期化されなくなります。×を使用不能にすればいいのはわかりますがそこまではやりたくなかったものですから。

No2さんへのお礼欄にも書きましたが、Dialogsheetに対する操作だけがエラーになるのが不思議です。

回答No.1

bin-chan

Sub 初期化()

　'ダイアログシートのありかを明示してみる
　With Thisworkbook.DialogSheets("Dialog1")
　　.DropDowns.ListIndex = 1
　　.EditBoxes.Text = ""
　End With
End Sub

だとどうなりますか？

お礼

さっそくありがとうございます。
やってみましたが同じでした。