VBA Excelシートの関数処理が完了してから変数に代入するには

Question

VBAでデータを処理しようとしているのですが、Excelシートの関数がVBAの処理に追いついていません。

やりたいことは下記になります。
①関数が記入されているExcelシートにダウンロードしたデータを貼り付ける
②貼り付けたデータの内容で関数の結果が変更される
③変更された関数の結果をVBAの変数に代入
④代入された変数を用いて次の処理を行う```VBA
コード

' 準備シートの関数をコピペ
range1 = Workbooks("a.xlsm").Sheets("準備").Range("D1").Value
Workbooks("a.xlsm").Sheets("準備").Range("E1:R1").Copy
Workbooks("a.xlsm").Sheets("準備").Range("E3:E" & range1 + 2).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False

' 関数計算
Workbooks("a.xlsm").Sheets("準備").Calculate
Workbooks("a.xlsm").Sheets("次").Calculate

' 次シート更新
range1 = Workbooks("a.xlsm").Sheets("次").Range("B1").Value
Workbooks("a.xlsm").Sheets("次").Range("D20").Copy
Workbooks("a.xlsm").Sheets("次").Range("D20:D" & (19 + range1 * 9)).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False

しかし、③で代入される変数が正しい結果で代入されず、計算途中もしくは計算前のデータで代入されてしまいます。
(コードで言う「次シート更新」部分のrange1の値が正しく反映されません。)

VBAを途中で停止し、続きから再度同じコードを実行すると求めている変数が代入されるので、関数処理が追いついていないのかなと思うのですが、何か解決策はございますでしょうか。

よろしくお願い致します。

Comments

[AMK]

対象のVBAのコードを載せてください

[yureighost]

コードが書いてないので何とも言えませんが、
VBAにはシートを指定して再計算させる
Worksheet.Calculate
プロシージャがありますがそちらは使っていますでしょうか。

[meg_]

全てVBAの中で計算させれば良いのではないでしょうか？

[emiri_py]

コード載せました。
Calculate追加しても反映されませんでした。
すべてVBAの中でやれば良いのでしょうが、関数使ったほうが簡単だったのでもしそれでできるならと思いまして…。

[AMK]

ワークシート関数でVBA内で計算させましょう
https://www.moug.net/tech/exvba/0100035.html

[yureighost]

ちょっと再現が難しいですね。
貼られたソースと要望から見ると、
恐らく次シートのB1が、準備シートのE3～Eの計算後の値を参照してるのだと思いますが。
単純な四則演算程度の数式で試す分には計算が間に合っているので。

[coco_bauer]

’次シート更新の行の前に、"Application.CalculateFull"という行（Excel全体を再計算させる命令)を追加してみてください。

[emiri_py]

みなさんコメントありがとうございます。
ワークシート内でVBA計算はまだ試せていないですが、Application.CalculateFullでもだめでした…

Answer 1

難しいですね。それを直接的に解決する手段は恐らくMSでは用意していないでしょう。
もしかしたらできるかも？という泥臭い方法なら、後で提案してみます。

計算はVBAの実行とは関係なく裏でセコセコと計算しています。参照セルに変化があれば常に再計算するだけです。
いつどのVBAの依頼で計算を開始したかなど意識していません。
なので、計算中かを問い合わせても、そのタイミングでは計算をしていないというだけで、次のタイミングではどうなるはわかりません。また、計算(宿題)は残っているけど、一時的に計算を止めているかもしれません。
従って、「裏で計算する」という時点で、「シーケンシャルな仕組みのVBAでは解決手段はほぼ無し」と考えていいと思います。

なので、VBAでセルの計算を待つようなやり方はMSは想定していないでしょう。根本的な対応は「VBA内で閉じて計算しなさい、エクセル関数を使いたいならEVALを使いなさい」となります。

さて、泥臭い対応ですが、「最低限ここの計算は終わったか？」ならもしかしたら判断できるかもしれません。
適当なユーザー関数を用意して該当セルの計算式として組込むことです。コールされたかどうかをグローバル変数に結果として書き込んでタイミングを調整してやればいけるかもです。
作るユーザー関数は、1または""を返すようにして掛けるか結合すれば計算結果には影響を与えません。

この提案は本当に可能かどうかは確認していませんし、やりたいことに合わせての対応が必要です。
なので提案に留めさせていただきます。

Answer 2

再計算が完了するまで待ってみてはいかがでしょうか。
ただ速度はVBA内で計算した方が早いと思います。

Do While Application.CalculationState <> xlDone
Sleep 100
Loop

Comments

[emiri_py]

回答ありがとうございます。
試してみたのですが、やはり計算途中の結果が表示されてしまいます。