エクセル　マクロ　ソルバ　中止（Ｔ）を選択したい

Question

エクセル２００７のマクロを修正しています。
ソルバで、オプションの制限時間を設定しており、
制限時間に達した時に、「試行状況の表示」が現れるため、
マクロが途中で停止してしまい、困っております。

「試行状況の表示」が現れる時に、中止（Ｔ）を選択できれば、
目的のマクロになります。いくつかの方法を試したのですが、
うまくマクロが動作しません。

【試した事】
　(1)Dummy Macro()　→　「値の更新」が表示されて止まる。
　参考　http://oshiete.goo.ne.jp/qa/2630207.html

(2)Function　→　「値の更新」が表示されて止まる。
　参考　http://msdn.microsoft.com/ja-jp/library/office/ff197237.aspx

(3)Application Display Alerts　→　「試行状況の表示」が表示されて止まる。
　参考　http://oshiete.goo.ne.jp/qa/2630207.html

コードを記述しないと、非常に回答し辛い事は、承知しております。
ただ、セキュリティの問題で、生コードは持ち出せません。また、
現在、マクロを勉強中のため、簡易的に書く技術が無く申し訳ございません。

やりたい内容や課題になっている所は、gucchi-you殿の質問と同様だと思います。
http://oshiete.goo.ne.jp/qa/2630207.html
この質問は、解決せずに終了しております。

２週間試行錯誤しましたが、うまくいかずに投稿させて頂きました。
もし、ご回答頂ければ、非常にありがたいです。
お知恵をお貸し下さい。

queuerev2 · Accepted Answer

質問者様と似たようなトラブルを解決した事例が見つかり、そこでのトラブルの原因を取り入れてやってみたところ、場合によっては「値の更新」ダイアログが出るようになりました。

原因は、ファイル名にスペースやハイフンなどが入っていることです。
ソルバーにはいまだにExclel4.0マクロが使われている（ことを今回初めて知った）のですが、そこでスペースやハイフンなどが入っているブック名の処理がうまくいかないようです。
（SolverSolveをステップ実行するとある程度はわかります）

よって、ファイル名（ブック名）にスペース、ハイフンその他かっこ等の文字が入らないようにすれば解決する可能性があります。
英字全半、数字全半、アンダースコア全半、ひらがな、カタカナ全半、漢字は大丈夫なようです。
逆にスペース等は全角でもだめです。

情報元は以下のところです。
http://www.mrexcel.com/forum/excel-questions/683906-solver-macro-error-cell.html

ついでに付け加えると、ShowRefで指定する関数名がA1やR2C3などセル範囲と誤解されるようなものだとエラーが発生します。セル範囲の名前があればそれも同様です。
ただしこちらはモジュール名をつければ回避できます。
たとえば関数A1がModule1にあるなら、以下のようにします。
SolverSolve UserFinish:=True, ShowRef:="Module1.A1"
この件は、まだよくわからないうちに回答No.1の最後に書いたものですが、そのときはExcel2010固有の問題で"ShowRef1"という長い名前でも問題が発生する、と書きました。
しかしそれは正しくないようで、セルのアドレスやセル範囲の名前と同じ関数名が問題となるようです。

（なお、もし上記の情報で解決した場合でも、Sendkeysの情報をもご希望であるならば、質問を締め切らずにお待ちください）

参考URL：http://www.mrexcel.com/forum/excel-questions/683906-solver-macro-error-cell.html

queuerev2 · Answer

質問者様はExcel2007をお使いになっているとのことですが、Office2007は32bit版なのでこのままで大丈夫です。
（現状では2010以降でも32bit版を使っているケースが多いと思われます。なお、この話にはWindowsやCPUのbit数は直接は関係ありません。）

とはいえ、今後64bit版のOfficeへの対応が必要になるかもしれませんので方法を述べます。
標準モジュールに書く場合です。

#If VBA7 Then
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal ms As LongPtr)
#Else
Public Declare Sub Sleep Lib "kernel32" (ByVal ms as Long)
#End If

office2007などでは２行目がエラーで赤くなりますがそれでＯＫです。
64bit版Officeでは逆に４行目がエラーになるらしいです。
Public はなくても同じですが書いた方がお行儀がいいので今後は書くことにしようと思います。
そのモジュールの中だけでSleepを使えるようにしたい場合やシートモジュールなどに書く場合はPrivateにします。

http://www.happy2-island.com/excelsmile/smile04/capter00306.shtml
http://support.microsoft.com/kb/983043/ja
http://answers.microsoft.com/ja-jp/office/forum/office_2013_release-access/access200732bit%E3%81%A8201364bit/2f6d3b5f-98da-44c1-b11e-08847a42b727?msgId=81b4e138-a25a-46fe-a0f9-b9025091300e

参考URL：http://www.happy2-island.com/excelsmile/smile04/capter00306.shtml

queuerev2 · Answer

前の回答に書ききれなかったダイアログ操作用のコードです。
標準モジュールに貼り付け、ブックを KeySender1.xlsm として保存してください。
あとは１つ前の回答をご覧ください。

Declare Sub Sleep Lib "kernel32" (ByVal ms As Long)
'VBAで低CPU負荷で1ms単位で指定可能なSleepを使うための宣言
'64bit版Officeを使用している場合は要変更

Private TitleA As String, TitleD, Ks, RTime, FFP, FTime
'モジュール変数。　アプリケーションタイトル、ダイアログタイトル、
'送信キー、実行時間、フラグファイルパス、停止時刻

Const CC_FFP = 10 'フラグファイル確認１回あたりのウィンドウ探索回数
'フラグファイル確認はファイルアクセスなので回数を減らしたい

Const contTimeStr = "00:00:10"
'連続してウィンドウアクティブ化・キー送信しっぱなしになった際の停止タイマー
'連続送信で停止したくないなら"00:00:00"に

'本体起動用プロシージャ
'OnTimeによる本体の即時起動を仕掛けて呼び出し元に戻る
'引数は     Title_Application アプリケーションタイトル、
'　　　     Title_Dialog　ダイアログタイトル、Keys　送信キー
'           RunningTime　実行時間　0なら実行時間制限なし
'           FlagFilePath　フラグファイルパス
Sub Start_SKtoD(Title_Application, Title_Dialog, Keys, RunningTime, FlagFilePath)
    TitleA = Title_Application
    TitleD = Title_Dialog
    Ks = Keys
    RTime = RunningTime
    FFP = FlagFilePath
    FTime = RTime + Now()
    Application.OnTime Now(), "Call_Sendkeys_to_Dialog"
End Sub

'OnTimeで呼び出されるプロシージャ
'本体を呼び出し、終了後Excelを終了する。
Sub Call_Sendkeys_to_Dialog()
    Call Sendkeys_to_Dialog
    Application.DisplayAlerts = False
    Application.Quit
End Sub

'本体
Sub Sendkeys_to_Dialog()
    Dim i, contTimer, contTime
    Dim oWshShell
    Dim oShell
    Set oWshShell = CreateObject("WScript.Shell")
    Set oShell = CreateObject("Shell.Application")
    
    oWshShell.AppActivate TitleA 'ダイアログを出すアプリケーションをアクティブ化
    DoEvents
    Sleep 300
    contTime = TimeValue(contTimeStr)
    contTimer = Now()
    Do '以下ループ内ではDoEventsやSleepを適当に入れてうまく動くように調整する
        If RTime > 0 Then 'RTime <= 0なら終了時刻確認なし
            If Now > FTime Then '終了時刻を過ぎたか？
                Debug.Print "time over"
                Exit Do '過ぎたら終了
            End If
        End If
        i = i + 1 'フラグファイル確認用カウンタ
        If i >= CC_FFP Then 'カウンタがCC\FFP以上？
            If Len(Dir(FFP)) > 0 Then 'フラグファイルはあるか？
                DoEvents
                Kill FFP 'あるならファイルを削除して
                Debug.Print "flagfile exists"
                Exit Do '終了
            End If
            i = 0 'フラグファイルがないならカウンタリセット
        End If
        DoEvents
        Sleep 200
        If oWshShell.AppActivate(TitleD) Then 'ダイアログはあるか？
            Debug.Print "dialog found.. ";
            If contTimer > 0 And Now() - contTimer > contTime Then
                Debug.Print "continuous act&key"
                Exit Do 'キー送信連続時間超過なら終了。（contTimer=0で無制限）
            End If
            DoEvents
            oWshShell.AppActivate TitleA 'ダイアログを出したアプリケーションをアクティブ化
            'AppActivate TitleA 'ダイアログを出したアプリケーションをアクティブ化
            DoEvents '十分にアクティブ化するまでDoeventsとSleepで待つ
            Sleep 200
            DoEvents
            SendKeys Ks 'キー送信
            DoEvents '送信したキーによる処理を進めるためにDoEvents
        Else
            contTimer = Now() 'キー送信連続開始時間更新
        End If
    Loop
End Sub

Sendkeysによるダイアログの操作は以上です。

最後に、解答No.1のコードの不具合にて少し述べさせていただきます。
解答No.1のコードは、ソルバーを適用するための数式をSheet1に書き込み、ソルバーはアクティブなシートで実行しています。そのため、Sheet1以外をアクティブにして動作させるとうまくいきません。
つまり、わざわざSheet1を指定したことが余計だったというわけです。
解決策としては、 S. をすべて削除するか、もっと簡単にはSet S = Sheets("Sheet1")をSet S = ActiveSheetにします。
当方の未熟さが出てしまい申し訳ありません。

queuerev2 · Answer

ファイル名を制限することで解決したのですね。
いろいろ調べたりした甲斐がありました。
しかし「、」もだめとはちょっとびっくりです。
Excel4.0マクロだけのせいかどうかはわかりませんが、やっかいな問題ですね。

それでは、ダイアログをSendkeysで操作する方法を解説いたします。

Sendkeysはキーボードのキーを押す代わりにプログラムからキーを送信する方法です。
人間がキーを押す場合は画面を見て確認しながら操作することができます。しかしVBAは画面の状態をほとんど確認することができませんし、ウィンドウの検出には通常はウィンドウタイトルを手掛かりにするので、タイトルの似ているウィンドウがあると誤検出することがあります。そのため、どうしても動作が不安定になる可能性を排除できません。後述のとおりサンプルを作ったもののそれほどよい出来ではないことも考え合わせ、やはり非常手段だという印象です。
それでも、Sendkeysなどユーザーインターフェースを操作する以外よい方法がないことも多く、いろいろなところで使われているようです。
なお、マウス操作も行いたい場合などは、UWSCというソフト（シェアウェア・フリーウェア）がよく使われます。
http://www.uwsc.info/

Sendkeysおよびウィンドウアクティブ化に使用するAppActivateの機能や使用法についてはWeb検索で多くの情報が得られると思いますが、とりあえずMSDNのリンクを載せておきます。
http://msdn.microsoft.com/ja-jp/library/office/gg278655.aspx
http://msdn.microsoft.com/ja-jp/library/office/gg278643.aspx
http://msdn.microsoft.com/ja-jp/library/cc364423.aspx
http://msdn.microsoft.com/ja-jp/library/cc364396.aspx
上記を見てお気づきかもしれませんが、VBAで使用可能なSendkeysとAppActivateとしては、VBAのステートメントとWshShellオブジェクトのメソッドの２種類があり、機能が微妙に異なります。（Sendkeysはもう１つApplicationのものあります）
今回はAppActivateでウィンドウの検出を行うことにしますので、その部分にはWshShellのメソッドを使うことになります。
もう１つ今回参考にした例を挙げます。（VBScriptですが）
http://www.atmarkit.co.jp/fwin2k/tutor/cformwsh12/cformwsh12_02.html

ではダイアログ操作を解説します。どうやって解説しようか考えているうちに結局は一応動くものを作ってしまったのですが、自己流の部分もあり不適切なところもあるかもしれませんが、とりあえず動いた例として書かせて頂きます。

ExcelにおいてSendkeysでダイアログを操作するという話題で、印刷設定を行うというものがあります。そういうコードを見ると、With AcitveDialogの中でダイアログを表示させてそのオブジェクトを得て、Application.Sendkeysを使って操作しています。
しかし、質問者様の最初の質問にあるように、VBA実行中に意図しないダイアログボックスが出て実行が止まるのでそれを操作したいという場合、おそらくこの方法にあるようなダイアログボックスのオブジェクトの取得はできないと思います。
（もしできるのなら知識不足で申し訳ありません）
仕方がないので、他のプロセスからダイアログの出現を検出し、キーを送信することにします。
他のプロセスで動くソフトとしては、VBScript、Word、UWSCなどExcel以外でSendkeysが可能なものがいくつか考えられますが、今回はExcelの複数起動でやってみたいと思います。

考慮した点は以下のとおりです。
１．CreageObjectかNewでExcelのオブジェクトを作って別プロセスでExcelを起動し、ダイアログ操作用のブックを開く。
２．別プロセスのExcelでVBAを起動する。（マルチプロセスで動作させる方法としては、自動起動、イベント利用、Application.OnTimeの使用が考えられるが、今回はApplication.OnTimeを使用した。）
３．ダイアログ操作の方針は、ダイアログ検出と親ウィンドウのアクティブ化の両方を行った後にキー送信。ダイアログのウィンドウだけをアクティブ化してもキーを受け付けてくれないことが多い。
なお、途中にはDoEventsやSleepを適宜入れてうまく動くように調整する。
４．メインルーチンは、ダイアログのウィンドウを検出し、検出したらキーを送信する動作を繰り返す。（今回の場合ダイアログが何回出るかわからないので、検出と送信を繰り返す必要がある）
５．ダイアログ操作が不要になった場合に外部から終了できるようにしておく。（終了フラグとしてファイルのパスを指定しておいて、そのファイルが存在すると停止するようにした）
６．誤動作してアクティブ化とキー送信が連続して行われる状態になるとWindowsを手動で操作することができなくなるので、このような状態を検出して停止するようにしておく。（一定時間連続してウィンドウアクティブ化とキー送信が発生すると停止するようにした。）
７．実行時間設定機能を付加。

以上の点を考慮してコードを作成したのですが、結局解決しなかった問題があります。
ダイアログを出すExcelをアクティブ化していない場合、タスクバーが点滅するだけでダイアログが出ずに止まってしまうのです。しかしダイアログ検出のたびに親ウィンドウをアクティブ化するとVBA動作中はExcelが操作不能になってしまったので、妥協策として最初にダイアログを出すExcelをアクティブ化し、それ以降はダイアログが検出された場合のみアクティブ化することにしました。（終了まで原則人手で操作しないなら、ダイアログ検出のたびに親ウィンドウをアクティブ化する方がいいかもしれません）

キー送信用VBAのコードは字数が多くてこの回答に入りませんので、次の回答への掲載とさせていただき、先に使い方やサンプルについて述べます。
まず、次の回答に載せるコードを標準モジュールに書き込んだブックを、ファイル名 KeySender1.xlsm として保存しておきます。
次に、以下のサンプルコードを標準モジュールに書き込んだブックを、ファイル名 KSSample1.xlsm とし、KeySender1.xlsmと同じフォルダに保存します。
そして、KSSample1.xlsmのtest1を起動させれば動作するはずです。
なお、停止用のフラグファイルを同じフォルダに書き込むのでご注意ください。

ではサンプルコードを載せます。
使用法等はこのサンプルコードをご覧ください。

Declare Sub Sleep Lib "kernel32" (ByVal ms As Long)
'Sleepを使用するための宣言

'フラグファイル作成
Sub MakeFile(FFPath)
    Dim fno
    fno = FreeFile
    Open FFPath For Output As #fno
    Close #fno
End Sub

'テスト本体
Sub test1()
    Dim FFP
    Dim XL2 As Excel.Application
    FFP = ThisWorkbook.path & "\FlagFile1" 'フラグファイルのパス付ファイル名
    If Len(Dir(FFP)) > 0 Then
        Kill FFP 'フラグファイルが残っていたら削除
    End If
    Set XL2 = New Excel.Application 'Excelを別プロセスで起動
    XL2.Visible = True '起動したExcelを可視に。（可視にしなくてもよい）
    XL2.Workbooks.Open ThisWorkbook.path & "\KeySender1.xlsm" 'キー送信用ブックを開く
    XL2.Run "Start_SKtoD", Application.Caption, "テストメッセ", "{esc}", TimeValue("00:01:00"), FFP
    'キー送信VBA"Start_SKtoD"を起動
    '引数：このブックのウィンドウタイトル、ダイアログタイトル、送信キー、実行時間、フラグファイル名パス付
    'タイトルは最初または最後が欠けていてもよい
    DoEvents
    Sleep 500
    DoEvents
    MsgBox "メッセージ１です", vbOKCancel, "テストメッセージ1"
    'このMsgBoxはすぐに消されるはず。
    MakeFile (FFP) 'フラグファイルでキー送信VBAを停止
End Sub

queuerev2 · Answer

いただいた補足を見て考えているうちに、いくつか思い違いをしていたことに気付きました。

そもそも、質問者様はShowRefで指定した関数を実行すること自体は成功していたのではないでしょうか。
それができていないと思ってサンプルコード付きの長い回答を書いてしまいました・・・

次に、小さい訂正が１つ。
StepThruですが、No.1ではTrueにすると書きましたが、Falseのままでもおそらく大丈夫です。さらに言うなら、Falseで済むならその方が速度面などで有利ですね。
Trueにすると毎回関数が呼ばれ、Falseにすると中断あるいは終了の条件になったときのみ関数が呼ばれるようです。

それから最大の勘違いですが、「値の更新」がソルバーに特有のダイアログボックスだと思ってしまい、Web検索でほとんど出ないのでどうも変だと思っていました。
しかし調べなおすとソルバーには直接の関係は何もなく、異なるブック（ファイル）の間のリンクがうまくいかない場合に出ることがあるファイル選択のダイアログボックスではありませんか。

ということで、質問者様が使用中のブックには他のブックへのリンクがあり、それが自動更新できない状態になっていると思われます。
それを解消すれば、おそらくはここに質問を出す前の状態でうまくいくのではないでしょうか。
リボン→データタブ→接続グループ→リンクの編集
などからご確認ください。

あとは、このようなダイアログのボタンを自動的に押す方法として、やや不安定ではありますが、他プロセスからSendkeysを使うといった方法があります。
これについてもご希望であれば検討いたします。

queuerev2 · Answer

ソルバーのVBAですね。
私も使ったことがなかったので調べたり試したりいろいろやってみました。
ソルバーを実行するSolverSolve関数にはフックの仕組みがありShowRefというパラメータで指定することになっているということはすぐにわかったのですが、「ShowRefパラメータによる関数指定はうまくいかない」という趣旨の記事が多くだめなのかと思いました。
しかしやってみたら予想に反して少なくともExcel2007ではうまくいきました。

Web検索で調べていてややわかりにくかった要点としては、
1. SolverOptions関数でStepThruパラメータにTrueを指定する
2. SolverSolve関数でUserFinishパラメータにTrueを指定する
3. ShowRefのフック関数では、計算継続ならFalseを、計算終了ならTrueを返すようにする
というあたりでしょうか。

今回最も参考になったのはMSDNのSolver関連のVBA関数のところなので、以下にリンクを載せておきます。
（SolverSolve関数が重要なのは当然ですが、SolverOptions関数の説明は必見だと思います。）
http://msdn.microsoft.com/ja-jp/library/office/ff195446.aspx
http://msdn.microsoft.com/ja-jp/library/office/ff197237.aspx
http://msdn.microsoft.com/ja-jp/library/office/ff835238.aspx

それでもまだ安心して使えるほどには理解していないのですが、とりあえず動くサンプルを作りましたのでお試し頂ければ、と思います。

Excel2007にて新規ブックを用意し、VBEを開き、SOLVERを参照設定し、標準モジュールに以下のコードを貼り付け、Subプロシージャを実行してみてください。
うまくいけば、反復回数に大きい数字（私の環境では5以上）を入れると計算が完了して結果が保存され、小さい数字（私の環境では1～4）を入れると計算未完了で中断され結果は保存されないはずです。

Option Explicit

Private SolverCount
Private KF

Function SRF_2(Sts)
    SolverCount = SolverCount + 1
    Debug.Print SolverCount;
    If Sts = 3 Then
        Debug.Print " Stop, Sts ="; Sts;
        SRF_2 = True
        KF = 2
    Else
        SRF_2 = False
    End If
End Function

Sub test_ShowRef2007_2()
    Dim S
    Dim MaxIteration
    Set S = Sheets("Sheet1")
    S.Range("B2:B3").Value = 0
    S.Range("A2:A3").Value = "変化"
    S.Range("B4:B6").Value = 0.1
    S.Range("B7").Formula = "=(COS(B3-B5)+SIN(B2-B4-B5))/((B2-B4)^2+B6)"
    S.Range("A7") = "目的"
    S.Range("B8:B9") = ""
    S.Range("A8") = "反復回数"
    S.Range("A9") = "KeepFinal"
    SolverCount = 0
    KF = 1
    MaxIteration = 0 + InputBox("反復回数入力", "Solver ShowRef サンプル")
    SolverOk SetCell:="$B$7", MaxMinVal:=1, ValueOf:=0, ByChange:="$B$2:$B$3"
    SolverOptions Iterations:=MaxIteration, StepThru:=True
    SolverSolve UserFinish:=True, ShowRef:="SRF_2"
    SolverFinish KeepFinal:=KF
    S.Range("B8") = SolverCount
    S.Range("B9") = KF
    Debug.Print " Iterations: " & SolverCount & ", KeepFinal: " & KF
    SolverOptions Iterations:=100, StepThru:=False
End Sub

やや余談になりますが、Excel2010でも少し調べてみました。その結果、Excel2010ではソルバーが強化されており新しいパラメータが追加されたりしているのですが、Excel2007のマクロはおおむねそのまま動くようです。
ただ、ShowRefパラメータの関数名の処理にバグがあるようで、"SR1"、"ShowRef1"などの英文字連続のあとに数字が続いてそれで終わる関数名ではうまくいきませんでした。
なので、Excel2010で同じことをやるときは関数名に配慮が必要と思われます。

何かありましたら補足ください。

参考URL：http://msdn.microsoft.com/ja-jp/library/office/ff195446.aspx

エクセル マクロ ソルバ 中止（Ｔ）を選択したい

質問者様と似たようなトラブルを解決した事例が見つかり、そこでのトラブルの原因を取り入れてやってみたところ、場合によっては「値の更新」ダイアログが出るようになりました。

この回答への補足

質問者様はExcel2007をお使いになっているとのことですが、Office2007は32bit版なのでこのままで大丈夫です。

前の回答に書ききれなかったダイアログ操作用のコードです。

ファイル名を制限することで解決したのですね。

この回答への補足

いただいた補足を見て考えているうちに、いくつか思い違いをしていたことに気付きました。

この回答への補足

ソルバーのVBAですね。

この回答への補足

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

エクセル　マクロ　ソルバ　中止（Ｔ）を選択したい