EXCEL VBA UserFormで困っています。

質問

EXCEL VBA UserFormで困っています。
VBAプログラミングで以下のような現象が起こります。

開発環境 　WinXP , EXCEL2003

ブックを起動するとメニューが表示され、そこからボタンを押すとuserform1が表示されるというプログラムなんですが、そのボタンを押してuserform1を起動しようとすると『問題が発生したため、　　Microsoft Excel Windowsを終了します。・・・ エラー報告を送信する　送信しない』のメッセージが出て起動できない。
UserForm1.Showのステップで一旦止めてステップモードで続行すると正常に動作します。

過去に似た質問を見つけ、そのアドバイスを元に以下を試してみました。
http://okwave.jp/qa/q2001805.html

・エラーの発生行の特定
userform1.showの前に、userform1.label.visible=falseのように、表示、非表示を切り替える命令を出しており、どうやらその表示命令の行でつまづいている事がわかりました。
表示命令を全て消去し実行したところ、現在までは問題なく起動できています。

・on timeメソッドで時間差をつける
ラベルやボタンの表示、非表示命令行をまとめて、
Application.OnTime Now + TimeValue("00:00:01"), "ラベルボタン表示設定"
userform1.show
といった形で時間差をつけてみました。
結果として、userform1自体は立ち上がるようになったのですが、時間差で実行される"ラベルボタン表示設定"の実行時に「オートメーションエラーです。起動されたオブジェクトはクライアントから切断されました。」と出てくるようになってしまいました。

ラベルはまだ良いのですが、コマンドボタンの表示、非表示は、誤操作を防ぐ手段として非常に重要です。なんとかエラーを出さずにコード通りに起動する方法はありませんでしょうか。

回答No.5

Wendy02 · ベストアンサー

>最終的な結論としては、マルチページを使用しているために起こるエラーとの事でした。
この話は、Excel2000で、Excel2003も同じ構造的なものを持っていますから、共通していると思います。

私自身としては、今回のエラーの理由は、UserForm にインスタンスのオブジェクトが乗る時に、オーバーヘッドが掛かり過ぎるからと考えています。途中で割り込みを入れるから、エラーが発生してしまいます。だから、立ち上がりの時は、必要以上に、メモリに割り込みさせないようにするぐらいしか手がないのだと考えています。

他の言語のように、Control 自身を、フォーム上に生成するという方法もありますが、Excelでは、それはとても遅いのではないかと思います。

>userform1.show
>userform1.textbox300.value=○○
>にしたいのですが、現状、userform1.showすると、そこでコードがとまってしまい、

それは無理ですね。Show の後は、UserForm 側に渡されるので、UserForm のプロパティはUserForm モジュール側からでないと変更できません。

>フォームを先にshowし、その後に操作するにはどうすればよいでしょうか？
それは、その後のコードは、「Private Sub UserForm_Initialize()」 側に渡します。

ただ、もうひとつの提案の、予めプロパティを設定しておくというのはダメですか？

面倒なことは言うまでもないですが、20個程度ならなんとかなると思いますが。
そうしたら、起動時の処理は必要ありません。他にも、方法がないわけではありませんが、今の見通しでは、あまり変わらないように思っています。

それから、

>独自のバックアップシステムというものに替えて検証してみたいです。どういったコードなのか、教えて頂けませんか？

その骨子の部分だけ書くと、こういうことです。

Backup というフォルダを予め作っておきます。以下のようにします。何だとお思いになるかと思いますが、最悪の状態は、これだけで免れるのです。それに、日にちを置いたものをバックアップを取れば、なお良いわけです。

それを、Workbook_Open ()イベントに入れておきます。Copy では、出来ません。
Excelは、キャッシュの中に入って、物理的ファイルは別にありますから、外部オートメーションでないと出来ません。5～6年、ほとんど毎日のように使っていますが、ファイルを失ったことは一度もありません。ファイルサイズは、最大のひとつは、12.2MB　です。生半可に小さいものではありません。

Set objFso = CreateObject("Scripting.FilesystemObject")
　objFso.Copyfile ThisWorkbook.Path & "\" & ThisWorkbook.Name, ThisWorkbook.Path & "\Backup\" & ThisWorkbook.Name
Set objFso = Nothing

回答No.4

Wendy02

#2の回答者てす。

お礼の中の
>Private Sub CommandButton15_Click()
　Label220 ということは、コントロールか、Labelコントロールが220個あるということですね。
ちょっとやってみないとはっきり言えないけれども、UserForm は一応、セキュリティはExcel でフリーになっているとは言うものの、外部オブジェクトですから、立ち上げる前には、それだけやると、うまくいかない部分が出てしまうように思います。

例えば、ループで、DoEvents でも付けてみたらいかがでしょうか。
その場合は、以下のように出来るように思います。

　With UserForm1
　 For Each n In Array("Label1", "Label3", "Label4", "Label6", "Label8")
　　 .Controls(n).Visible = False
　　 DoEvents
　 Next
　 .Show 0
　End With

でも、普通は、起動時の状態というのは、プロパティで、Vislble = False にして、起動時は、規定の状態で立ち上がるようにしますね。

また、起動を遅らせる方法としては、APIを使って、
Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)

で、起動を遅らせる方法もあるかと思います。ただ、これでは、ユーザーを待たせることがあるので、状況にもよります。

今は、ざっと見ただけですから、どれが良いのかはこちらでは試しておりません。

お礼

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

ご教授頂いただいた、APIでのCall Sleep(1000&)を要所要所に配置し、時間差を設けましたが、現象は変わりません。

終了時には、ユーザーフォームを全てunloadし、フォームを起動したままブックを閉じる事はしていません。

考えたのですが、ユーザーフォームをshowする前に、labelやtextboxに値を代入するのではなく、ユーザーフォームをshowしてから代入したほうが良いのでしょうか？(ユーザーフォームが出る前に値を代入すると迷子になる？)
とすると、
userform1.textbox300.value=○○
userform1.show
だったのを
userform1.show
userform1.textbox300.value=○○
にしたいのですが、現状、userform1.showすると、そこでコードがとまってしまい、それ以下のコードが実行されないようです。フォームを先にshowし、その後に操作するにはどうすればよいでしょうか？

また、視点を戻し、上書き保存が原因だと考えるならば、現状の保存＋終了プログラムを、先の回答にあった、独自のバックアップシステムというものに替えて検証してみたいです。どういったコードなのか、教えて頂けませんか？
>自動保存機能
>私は、一応、これは外しています。自動保存と、マクロがぶつかる時があり、マクロに支障が出ます。こちらでは、独自のバックアップシステムをマクロで作っています。

補足

何度もすみません。
webにて調査していましたら、以下のような記述を発見しました。
http://www.keep-on.com/excelyou/2001lng4/200112/01120371.txt

見る限り、この質問者と私は状況が非常に似通っています。

1.ステップモードは問題なしなのに、上書き保存後再度実行するとオートメーションエラー
2.ユーザーフォームにマルチページを使用している。

最終的な結論としては、マルチページを使用しているために起こるエラーとの事でした。

回答No.3

myRange

回答１です。

Wendy02さんが登場されましたので後はお任せするとして、(^^;;;
以下のような点が気になりましたので一言。

提示のコードにはおかしいところはないようですが、
Excel終了の　Application.Quit　はどこに書いてありまか？

また、下記のプロシージャにはコードはありませんか？
あるとしたらどんなコードでしょう。

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Private Sub Auto_Close()

提示のコードは自動保存とはいいませんよね？？？
以上です。

お礼

回答ありがとうございます。
＞提示のコードにはおかしいところはないようですが、
Excel終了の　Application.Quit　はどこに書いてありますか？

Application.quitはどこにも書いておりません。使用者が他のエクセルファイルを同時に起動している際、quitしてしまうとそのファイルも閉じてしまうので、workbook.closeだけにしています。

＞また、下記のプロシージャにはコードはありませんか？
あるとしたらどんなコードでしょう。　
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Private Sub Auto_Close()

全て使っていません。下記のコードでテキストボックスの日付チェックをしているくらいです。
Private Sub TextBox300_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

回答No.2

Wendy02

リンク先のアドバイスを書いた本人です。

確か、その時のアドバイスは、開いて直(ダイレクト）に、UserForm を操作したり、ダイレクトで、UserForm でクローズと共にBook をクローズすると、ハングしてしまうということで、時間差を付けるというのを考えました。Excel本体のメモリの割り付けとUserForm のメモリの割り付けとインスタンスの設定を同時にすることが問題になっているようです。

もともと、アプリケーションの本体を表示せずに、UserForm だけを独立させて表示させるのは、無理があるのではないかと思います。富士通のFOMの教本のサンプルには出ていましたが、私はかねがね疑問に感じていました。

以下のコードも、当然は、危ないコードです。
UserForm のほうは、元に戻していないのですか？「EXCEL VBA UserFormで困っています。」のご質問者さんも、ダイレクトで行っていたようです。

>Private Sub CommandButton14_Click()
>y = MsgBox("終了します。よろしいですか？", vbYesNo)
>If y = vbYes Then
>　　Application.DisplayAlerts = False
>　　ThisWorkbook.save
>　　ThisWorkbook.Close
>End If
>End Sub

Private Sub CommandButton14_Click()
If MsgBox("終了します。よろしいですか？", vbYesNo) = vbYes Then
　　Unload Me
　　Application.DisplayAlerts = False
　　If ThisWorkbook.Saved = False Then
　　　ThisWorkbook.Close True
　　End If
End If
End Sub

無理なコードは書かないことに尽きます。そういう私自身、最近PCのせいなのか、UserForm をAPIで最小化させて、ずっと出しっぱなしにしていることがありますが、どうやら、メモリ・リークがあるようです。PCが極端に動かなくなります。ExcelをQuit すれば、元に戻ります。

>自動保存機能
私は、一応、これは外しています。自動保存と、マクロがぶつかる時があり、マクロに支障が出ます。こちらでは、独自のバックアップシステムをマクロで作っています。

お礼

回答ありがとうございます。
保存＋終了の処理を、ご教授頂いた上記のコードに変更してみましたが、現象は改善されません。

menuというフォームから、コマンドボタンを押すとUserform1が立ち上がる、というマクロなんですが、コマンドボタンを押すとuserform1が立ち上がる前に「問題が発生したため...」というエラーが出てしまいます。
ステップモードで実行すると問題なく動作し、その状態で一度動作させると、次に保存＋終了処理をしてブックを閉じるまでは正常動作しています。
しかし、menuフォームから保存＋終了処理をしてブックを閉じると、次に開いたときにまた最初に逆戻りしてしまいます。

一応、一連の動作中に実行されるコードを以下に記載しておきます。
以下のコード内において、ご指摘の「ダイレクトなuserformの操作」箇所はありますでしょうか。

宜しくお願い致します。

'menuフォーム上のコマンドボタン(押すとuserform1が立ち上がる)
Private Sub CommandButton15_Click()

UserForm1.CommandButton6.Visible = False
UserForm1.CommandButton7.Visible = False
UserForm1.CommandButton8.Visible = False
UserForm1.CommandButton9.Visible = False
UserForm1.CommandButton10.Visible = False
UserForm1.Label10.Visible = True
UserForm1.Label18.Visible = True
UserForm1.Label199.Visible = False
UserForm1.Label197.Visible = False
UserForm1.Label198.Visible = False
UserForm1.Label200.Visible = False
UserForm1.Label201.Visible = False
UserForm1.Label217.Visible = False
UserForm1.Label195.Visible = True
UserForm1.Label218.Visible = False
UserForm1.Label219.Visible = False
UserForm1.Label112.Visible = True
UserForm1.Label214.Visible = True
UserForm1.Label220.Visible = False
UserForm1.MultiPage1.Value = 0
UserForm1.Show
End Sub

'Userform1のコンボボックスリストを作成
Private Sub UserForm_Initialize()
'基本情報タブ
Dim MaxRow As Long
MaxRow = Sheets("取引先DB").Cells(Rows.Count, 1).End(xlUp).Row
combocnt = 2
Sheets("取引先DB").Select
Do Until combocnt = MaxRow + 1
combolist = Sheets("取引先DB").Range("B" & combocnt).Value
UserForm1.ComboBox70.AddItem combolist
combocnt = combocnt + 1
Loop
sheets("sheet1").select
end sub

回答No.1

myRange

＞userform1.showの前に、userform1.label.visible=falseのように
＞表示、非表示を切り替える命令を出しており

UserForm1.Showの前がどの時点かが不明なのでなんですが、、、
UserForm1のLoadとかInitializeイベントに置いたらどうなりますか？

----------------------------------------
Private Sub UserForm_Initialize()
　　UserForm1.Label1.Visible = False
End Sub
----------------------------------------

以上です。

補足

回答、ありがとうございます。

あれからこちらで調べた結果、原因はラベルの表示、非表示命令ではなく、終了時に自動で上書き保存させているのが原因のようです。

調べた結果は以下の通りです。：
・ラベル等の表示、非表示命令を全て消去しても改善しなかった。
・エラーが出てから、ステップモードで実行すると正常起動し、その状態で上書き保存すると、次以降にブックを開いたときはエラーが出ない。
しかし、処理終了時に自動で上書き保存すると、次に開いた際、同様のエラーが出る。

自動保存のコードは以下の通りです。
Private Sub CommandButton14_Click()
y = MsgBox("終了します。よろしいですか？", vbYesNo)
If y = vbYes Then
Application.DisplayAlerts = False
ThisWorkbook.save
ThisWorkbook.Close
End If
End Sub

自動保存機能を活かしながら、エラーが出ないようにするにはどうしたら良いでしょうか？