エクセルマクロ中の再計算について

質問

A1 -> 11(初期値)
A2 -> if(A1>10,1,2)

上記の様に表にデータが入っている場合に以下のマクロを実行した時に再計算されずにマクロの処理が進み遅くなって再計算が実行されるため正確なマクロが実行されないのですが、どうしたら確実に再計算を実行したあとにマクロが処理を継続するようにさせる事が出来るのでしょうか、よろしくお願いします。なお、実際のマクロ及び関数は違います。

Range("A1").value = 1
Calculate
if Range("A2").value = 1 then
　msgbox "OK"
endif
end

以上の場合に『OK』メッセージが表示されるものと理解していますが、表示されずに終了してしまいます。

回答No.19

papayuka · ベストアンサー

「Yahoo! eグループ」に参加しましたが、
「オーナー/管理者のみが投稿できるグループ」のようで、投稿出来ませんでした。

「Yahoo! eグループ」を紹介しましたが、私もはじめてです。管理者は参加者のアドレスが判るのかな？
ならばそちらに送ってみてください。

また、共有フォルダと言うのがあるようなので、該当のファイルをそこに置いても良いです。

ちなみにこちらはExcel97なので、Excel97が読める形式で保存してないと私の方では開けないかもしれません。

補足

「Yahoo! eグループ」にて送信しました。

よろしくお願いします。

回答No.9

papayuka

再び、#6です。

「※３の頃になって、シート名『合計』が再計算される」とありますが、
（※４の時点で選択されているシートはコピーしたシート『合計 (2)』になってる）

質問１）何をもって※３で再計算されていると判断されているのでしょう？

「再計算のマクロがあるにもかかわらず完全に再計算されない状態でマクロが次ぎの処理を実行する」「msgboxが表示される時間はマクロが中断されているが再計算は処理されているために問題が解決され If Range("AD46").Value <> "" の処理が正常に動作した」

この解釈はどうも納得出来ません。
VBAとシート計算が完全に別スレッドで動いているなら有り得るかも知れませんが、もしそうなら再計算の終了を返す手続きや関数がないと危なくて使えないと思います。

質問２）そのままでは上手く行かない処理が、 Timer と DoEvents を入れたら上手く行きますか？

　End If
　Calculate
　　　PauseTime = 10　　　'10秒
　　　Start = Timer
　　　Do While Timer < Start + PauseTime
　　　　 DoEvents
　　　Loop
　Sheets("合計").Select
　If Range("AD46").Value <> "" Then ' ※２

質問３）そもそもどう上手く行かないでしょうか？
　合計のA1にループで入る、請求先のB5～の値はどのように変化するのか？
　それによって合計AD46の値はどのように変化するのか？
　それによって合計C26の値はどのように変化するのか？
　それによって合計又は合計(2)のA4の値はどのように変化するのか？

------------------------------------------------------------------------------
以下は、「こんな感じ」と判断しTEST
Sheets("請求先")のB5～下には請求先名が入っている
Sheets("請求先")のB列最終セルは『総合計』と入っている
Sheets("請求先")のB列の請求先毎に計算式を消したシートを作る。
Sheets("合計")のAD46にはA1の値変化に関連したIF関数が入っている
Sheets("合計")のAD46が空白かどうかでシートコピーするかどうかを決めている
Sheets("合計")のC26にはA1の値変化に関連したIF関数が入っている
Sheets("合計")のC26が空白かどうかで印刷範囲を設定している
Sheets("合計")のA4にはA1の値変化に関連したIF関数が入っている（=A1）？
Sheets("合計")をコピーしたシートのシート名に年間計+請求先名を付ける？

Sub TEST()
Dim cs As Worksheet, ws As Worksheet
i2 = 4　　　　　　　　　　　　　　　　　　　　　 '取あえず B5～B8 まで
Application.Calculation = xlAutomatic　　　　　　'自動計算
Set ws = Sheets("合計")　　　　　　　　　　　　　'変数に代入
ws.Range("A1").ClearContents　　　　　　　　　　 '合計のA1クリア
For i = 1 To i2
　ws.Range("A1").Value = _
　　 Sheets("請求先").Range("B" & i + 4).Value　 '請求先名を合計のA1に※１
　If ws.Range("AD46").Value <> "" Then　　　　　 '※２はここから
　　If ws.Range("C26").Value = "" Then　　　　　 '印刷範囲を設定
　　　 ws.PageSetup.PrintArea = "$B$1:$AD$25"
　　Else
　　　 ws.PageSetup.PrintArea = "$B$1:$AD$46"
　　End If
　　ws.Copy After:=ws　　　　　　　　　　　　　　 '合計シートをコピー※４
　　Set cs = ActiveSheet　　　　　　　　　　　　　'コピーシートを変数に代入
　　cs.Name = "年間計" & cs.Range("A4").Value　　 'シート名変更
　　If cs.Name = "年間計『総合計』" Then　　　　　'シート名により移動させる
　　　 cs.Move Before:=Sheets("実績")
　　Else
　　　 cs.Move Before:=Sheets("品名等")
　　End If
　　cs.Cells.Copy　　　　　　　　　　　　　　　　 '※３
　　cs.Cells.PasteSpecial xlValues　　　　　　　　'関数を全部消すため
　　cs.Columns("A:A").Delete　　　　　　　　　　　'一時処理用の列削除
　　cs.Range("A1").Select
　End If　　　　　　　　　　　　　　　　　　　　　'※２はここまで
Next　　　　　　　　　　　　　　　　　　　　　　　'次の請求先
　'Application.Calculation = xlManual
End Sub

補足

前回のmsgboxの実施時は勘違いがあったのでしょうか？今日、実施したら上手く出来ませんでした。

　Calculate
　　　PauseTime = 10　　　'10秒
　　　Start = Timer
　　　Do While Timer < Start + PauseTime
　　　　 DoEvents
　　　Loop
　Sheets("合計").Select
　If Range("AD46").Value <> "" Then ' ※２

また、上記を挿入して、実行し静止中に合計の表を確認したと再計算がされず、別のシートが作成された時点にて再計算されていました。
なお、マクロが終了した後に同ファイルを確認しても問題なく関数は処理されていす。

以上、よろしくお願いします。

回答No.8

papayuka

#6です。
変数　i2　と　i3　と　Range("AD46").Value　の式、Range("C26").Value　のセルに入っている関数式が不明で良く解かりませんのでアドバイスを。

if 条件　then
　真の時
else
　偽の時
end if

のように else を使った方がコードが見渡しやすくなります。

i が 0 のループはループ前に Sheets("合計").Range("A1").ClearContents を入れれば不要に思います。

※２の条件は
Sheets("合計").Range("AD46").Value 　が「空白かどうかだけの判断」
で、空白以外なら条件内に入り、※３も実行される仕組みになっていますよね？
ループ中に、Sheets("合計").Range("A1").Value = b　が「再計算される」と
Sheets("合計").Range("AD46").Value　が「空白になり」
※２が「実行されない」事を望んでいるのですか？？

お礼

補足漏れをさせて下さい。

AD46が職場別の合計金額であり、私の実行したい処理は、例を言うと
　A.○部署の実績なし(AD46=0)　－＞　IF文内を実行しない(別シートを作成しない)
　B.△部署の実績あり(AD46>0)　－＞　IF文内を実行する(シート名「合計」を別シートにコピーして当該部署の実績表を作成する)
ところが、実績の無い部署を一度処理すると、その後、実績が有ってもIF文内が処理されず、実績なし(AD46=0)と処理されて、職場ごとの実績シートが作成されない。

また、AD46は別に関数を記載しましたがそれ以外が記載漏れであったので、下記に記載します。なお、#6さんの言われた通りに単純にAD46=sum(・・・)に変更しました。

※i2 -> シート名「請求先」に職場名を入力してあるのですが当該請求先の数以上ループさせる必要が無いために、部署数をカウントする関数をSheets("請求先").Range("A5")に入力して、その値を当該変数に代入しています。
※i3 -> 部署番号が連番（同一の番号は付与していません）でないために、シート名「請求先」に入力している部署名の表の上から順番に部署番号を表から抽出する時の当該部署のセルの行数です。
※Sheets("合計").Range("C26") -> =IF($A26="","",VLOOKUP($A26,品名等!$C$6:$F$45,2,FALSE))
※Sheets("合計").Range("A26") -> =IF(ISERROR(SMALL(実績!J$5:J$596,B26))=TRUE,"",SMALL(実績!J$5:J$596,B26))
※Sheets("合計").Range("A26") -> 21(上下に２つの表がありA26は２枚目の１行目のデータセル)
※実績!J$5:J$596 -> 全体で実績のある品目を品目番号を昇順にJ5から列べた

最後に、「if then else end if」等は、この後、実施します。

補足

何回も有り難う御座います。

職場毎のデータがあり、各職場毎に集計表を作成させるマクロなんですが、シート名『合計』は職場別の合計を作成するシートで、
Sheets("合計").Range("AD46").Valueは合計金額欄であり、=IF(SUM(F46,H46,J46,L46,N46,P46,R46,T46,V46,X46,Z46,AB46)=0,"",SUM(F46,H46,J46,L46,N46,P46,R46,T46,V46,X46,Z46,AB46))となっています。
ですから、その職場に実績がある場合には、If内のマクロにて、別シートを作成し、実績が無い場合には、次の職場へ変更して処理を継続させる。
以上です。よろしくお願いします。

回答No.7

sakenomo

If Range("AD46").Value <> "" Then ～　End If 間が動かないということでしょうか。

なんだか怒られそうなアドバイスですが、Range("AD46")が＜＞””になるのかは、確認されたのでしょうか。まだでしたら、MsgBox関数で確認されてみては。確認済みでしたらすみません

>　Calculate
>　Sheets("合計").Select
MsgBox i & "の時　" & Range("AD46").Value
>※２　If Range("AD46").Value <> "" Then
>　　If Range("C26").Value = "" Then

補足

おはよう御座います。

>怒られそうな
折角、回答していただいているのに怒る事なんて・・

>If Range("AD46").Value <> "" Then ～　End If 間が動かないということでしょうか。
テスト動作を何回もしていますが動作はしていると思います。
実際、「msgbox Range("AD46").Value」をテストしていませんでしたが、先ほど実施したところ、msgboxを入力したところ、上手く出来ましたので、私の見解としては、当初の予想通り、msgboxが表示される時間はマクロが中断されているが再計算は処理されているために問題が解決され「If Range("AD46").Value <> ""」の処理が正常に動作したと思っています。
でも、何回も中断させるのは、マクロを使用する意味が無いので別の方法で解決策（私の私見が違えば別ですが）をご存じでしたら、よろしくお願いします。

回答No.6

papayuka

A1に 11
A2に =IF(A1<10,1,2)

Sub aaa()
　Range("A1").Value = 1
　Calculate
　If Range("A2").Value = 1 Then
　　 MsgBox "OK"
　End If
End Sub

上記をExcel97で実行しましたが、メッセージ出ますよ。
マクロ実行中に シートが変わっていて、別のシートの Range("A1") や Range("A2") を対象にしているとか無いですか？

Sub bbb()
　Worksheets(1).Range("A1").Value = 1
　Worksheets(1).Calculate
　If Worksheets(1).Range("A2").Value = 1 Then
　　 MsgBox "OK"
　End If
End Sub

のように、シートも指定してみては？

補足

すみません。

実際に問題が発生しているマクロを#5の補足に掲載しましたので、そちらを見て頂けませんか。
今回の問題に対するものは、私が思うところ、マクロの記述が問題ではなく、再計算のマクロがあるにもかかわらず完全に再計算されない状態でマクロが次ぎの処理を実行するものなのかなって勝手に思っています。（思っている事が違うのであれば別ですが・・・）
#5へ記載した内容では次を実行してしまうのであるなら、再計算が完了するまでマクロの実行を中止して、再計算が完了したのちにマクロが実行される方法はないものでしょうか？

以上、よろしくお願いします。

回答No.5

imogasi

（元）A1 -> 11(初期値)　(正）A1 -> 11(初期値)
(元）A2 -> if(A1>10,1,2) 　（正）A2 -> =if(A1<10,1,2)
ですよね。
これだと困っている事象の再現になりません（上手くいっちゃう）。
結論は結局どうなりました？この例では、起こっている事象の置換えになっていないのではないでしょうか？
したがって、例え識者でも答えられない。
したがって、そのまま実際例を出してはどうですか。
職務上困りますか。

お礼

補足の追加です。

※１と※２の間に、シート名「合計」のセル「AD46」にマクロで計算式を入れ直し、再計算しても駄目でした。

すみません、よろしくお願いします。

補足

マクロの一部を掲載します。
※１でデータを入れて、再計算をさせて、※２で計算した結果により条件選択させるのですが※３の頃になって、シート名『合計』が再計算されるため、※２での条件選択が機能しない。
なお、関数は数段階あるため、関数も全て記載するとなると同ファイルを添付しないと分からないと思います。（公然と公開するとなると問題となりますのでチョット無理ですが・・・）
以上です。

Application.Calculation = xlAutomatic
For i = 0 To i2
　Sheets("合計").Select
　Range("A1").Select
　If i = 0 Then
　　Range("A1").ClearContents
　End If
　If i <> 0 Then
　　Sheets("請求先").Select
　　i3 = i + 4
　　a = "B" & i3
　　Calculate
　　b = Range(a).Value
　　Sheets("合計").Select
※１　　Range("A1").Value = b
　End If
　Calculate
　Sheets("合計").Select
※２　If Range("AD46").Value <> "" Then
　　If Range("C26").Value = "" Then
　　　ActiveSheet.PageSetup.PrintArea = "$B$1:$AD$25"
　　End If
　　If Range("C26").Value <> "" Then
　　　ActiveSheet.PageSetup.PrintArea = "$B$1:$AD$46"
　　End If
　　Sheets("合計").Select
　　Sheets("合計").Copy After:=Sheets("合計")
　　x = "年間計" & Range("A4").Value
　　Sheets("合計 (2)").Name = x
　　If x = "年間計『総合計』" Then
　　　Sheets("年間計『総合計』").Move Before:=ActiveWorkbook.Sheets("実績")
　　End If
　　If x <> "年間計『総合計』" Then
　　　Sheets(x).Move Before:=ActiveWorkbook.Sheets("品名等")
　　End If
※３　Cells.Select
　　Selection.Copy
　　Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
　　Columns("A:A").Delete
　　Range("A1").Select
　End If
Next
Application.Calculation = xlManual

回答No.4

ki-aaa

次のように、デバッグ用のコードを入れて実行とたらどうなります。

Range("X1") = Range("A2") '******
Range("A1").value = 1
Range("X2") = Range("A2") '******
Calculate
Range("X3") = Range("A2") '******
if Range("A2").value = 1 then
　msgbox "OK"
endif
Range("X4") = Range("A2") '******
end

Xはあいてる列に書き換えてください。

補足

何度もありがとう御座います。

実際のマクロには、A1に値を入れて、再計算後にA2に同じ計算式を入れ直して、また、再計算させて実行しましたが、同様に無理でした。

なお、Calculateだけでなく、先頭にApplication.Calculation = xlAutomaticもいれました。

結果として、条件選択の関数も数個あるうち最後の１個のみ計算式を再入力しただけなので、もっと、計算式を再入力させないと無理なのでしょうか？

回答No.3

diashun

＃１の答えどおりlistさんのVBAでは「ok」メッセージは表示されずに終了します。
セルA1に初期値「11」が入っていても
「Range("A1").value = 1」でセルA1は「1」になりますよね？そうするとif文では　Range("A2").value = 2 になるためif文の「false」となり、終了します。
なぜ「Range("A1").value = 1」のコーディングが必要なのか趣旨が不明です。

お礼

すみません、良く確認すると補足の内容が不十分であったために、こちらより補足させて下さい。

>なぜ「Range("A1").value = 1」のコーディングが必要なのか趣旨が不明です。

A1に入力されている初期値に対する、A2の結果を変更させるために「Range("A1").value = 1」を記載しました。
よって、初期値のA1の値に対するA2が変更しても「if ・・・」が初期値に対するA2の結果により条件処理を実行してしまうと言う事が言いただけです。

どの様に表現したら良いか悩んだのですが、結果的に誤解を招きまねいてしまい申し訳ありませんでした。

補足

すみません記載ミスです。
実際に、これだけの関数及びマクロであれば、今までの経験上から問題なく動作すると思っています。
ただ、実際のマクロ及び関数を記載するのが分かってもらううえで必要であることは承知していますが、記載しきれないため、表現したい内容を記載しました。
なお、実際にマクロが実行されているのを画面にて確認するとマクロが再計算の次の行を実行したあと、再計算されている状況であったため、同表現を記載するためのマクロでしたが記載内容に間違いがあったために誤解を招いた事、申し訳ありませんでした。

誤：A2 -> if(A1>10,1,2)
正：A2 -> if(A1<10,1,2)

回答No.2

bear-3

A2　のセルには見かけ上　１　又は　２　と表示されますが、
あくまでもA2セルに格納されている”値”は、"if(A1>10,1,2)" です。

だから　if Range("A2").value = 1 then とやっても”真”になることはありません。

補足

> A2セルに格納されている”値”は、"if(A1>10,1,2)"

「=if(A1>10,1,2)」と記載しなかったために、入力されているのが計算式でなく文字列だから、結果は必ず”偽”であると言われているのでしょうか？
もし、その通りであるなら#1でも記載したのに加えて記載ミスがあった事をお詫びします。

以上、#1の補足も併せて、よろしくお願いします。

回答No.1

ki-aaa

『OK』メッセージが表示されないのが、プログラムどおりだと思います。

Range("A1").value = 1
Calculate
'A2 := if(A1>10,1,2)
if Range("A2").value = 1 then
　msgbox "OK"
endif
end

補足

すみません記載ミスです。
実際に、これだけの関数及びマクロであれば、今までの経験上から問題なく動作すると思っています。
ただ、実際のマクロ及び関数を記載するのが分かってもらううえで必要であることは承知していますが、記載しきれないため、表現したい内容を記載しました。
なお、実際にマクロが実行されているのを画面にて確認するとマクロが再計算の次の行を実行したあと、再計算されている状況であったため、同表現を記載するためのマクロでしたが記載内容に間違いがあったために誤解を招いた事、申し訳ありませんでした。

誤：A2 -> if(A1>10,1,2)
正：A2 -> if(A1<10,1,2)