For...Nextでの変数の挙動を追いたい

質問

生物の研究の為のプログラムをVBAで自動化しようとしたのですが、
うまくプログラムできない為、質問させて頂きます。

「変数計算」というシートに変数(50%から200%)を入力し、計算する。
「Sheet1」から「Sheet11」は各年のデータで、「変数計算」のデータを
元に体温変化等を計算します。
「日々の増減」で各年のデータをグラフ化、「集計」シートで
標準偏差等を計算し、「総合評価」のシートに「集計」シートで
計算した３つの数値(C8,C10,C31)の値のみを
「総合評価」のシートのC4,D4,E4から縦方向にそれぞれ
順番に貼り付ける。

という作業をコード化しました。
Sub Macro1()

'画面更新停止
Application.ScreenUpdating = False
'自動計算停止
ActiveSheet.EnableCalculation = False

cntRec = cntRec + 1
Application.StatusBar = "処理実行中．．．．(現在 " & cntRec & "件)"

For i = 0 To 30

Sheets("変数計算").Select
Cells(41, 12).Value = 50 + (i * 5)
ActiveSheet.EnableCalculation = True
ActiveSheet.EnableCalculation = False

Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", _
"Sheet8", "Sheet9", "Sheet10", "Sheet11")).Select
ActiveSheet.EnableCalculation = True
ActiveSheet.EnableCalculation = False

Sheets("日々の増減").Select
ActiveSheet.EnableCalculation = True
ActiveSheet.EnableCalculation = False

Sheets("集計").Select
ActiveSheet.EnableCalculation = True
ActiveSheet.EnableCalculation = False
Cells(8, 3).Copy Destination:=Worksheets("総合評価").Cells(4 + i, 3)

Sheets("集計").Select
Cells(10, 3).Copy Destination:=Worksheets("総合評価").Cells(4 + i, 4)

Sheets("集計").Select
Cells(31, 3).Copy Destination:=Worksheets("総合評価").Cells(4 + i, 5)

Next i

Application.StatusBar = False

MsgBox "バックテストが完了しました"

End Sub

このコードですと、同じ値が「総合評価」にコピペされてしまいました。
変数iに応じたそれぞれの結果をコピペするにはどのように
したら良いのでしょうか？

VBAにお詳しい方のアドバイスを宜しくお願い致します。

回答No.3

Wendy02 · ベストアンサー

こんばんは。

何か、オプションをいじりすぎてはいませんか・
何のためにしているのか、わかってしているのか意味があればよいのですが、分からないままに、オプションをいじっているような気がします。

数式がどのような内容なのか見えませんが、

ActiveSheet.EnableCalculation = False
たぶん、ワークシートを全部、計算不能にしてしまったのでは、上手くないのでは？

.Cells(8, 3).Value等に　特殊な数式が入っているのだろうと思います。だから、値が変わるというのは、分かりますが、問題は、Calculate の問題では？

ただし、Excelでは、再計算の計算後の値を取得する方法はなかったと思います。マクロが素通りしてしまうかもしれません。どのような数式を置いているかにもよります。よほど重いシートでなければ、再計算を命令する必要はないと思います。一応、特殊な数式の場合、以下のような書き方がありますが、それでも値が取れないなら、ワークシートで計算させるのはやめて、VBAの中で計算させたほうがよいです。

なお、

>cntRec = cntRec + 1
>Application.StatusBar = "処理実行中．．．．(現在 " & cntRec & "件)"
それと、こういうオプションは、何万件というデータで、一向に終わらない場合以外は、格好だけになってしまいます。少なくとも、コードが完成していない前につけるのはやめたほうがよいです。

>For...Nextでの変数の挙動を追いたい
途中で取るなら、Debug.Print か変数でも置いてあげればよいのですが、単に、数値を出力するだけなら、そのままでシートに出せばよいと思います。

例：
'-------------------------------------------
Sub MacroTest1()
Dim i As Long

Application.ScreenUpdating = False

For i = 0 To 30
　Worksheets("変数計算").Cells(41, 12).Value = 50 + (i * 5)
　With Sheets("集計")
　　.Cells(8, 3).Calculate
　 Worksheets("総合評価").Cells(4 + i, 3).Value = .Cells(8, 3).Value
　　.Cells(10, 3).Calculate
　 Worksheets("総合評価").Cells(4 + i, 4).Value = .Cells(10, 3).Value
　　.Cells(31, 3).Calculate
　 Worksheets("総合評価").Cells(4 + i, 5).Value = .Cells(31, 3).Value
　End With
Next i
Application.ScreenUpdating = True

MsgBox "バックテストが完了しました"
End Sub
'-------------------------------------------

お礼

ご回答有り難う御座います。

With...End Withステートメントを使って試したところ
希望通りの結果が出そうです。
大変参考になりました。

ありがとうございました。

回答No.6

cistronezk

またお答えがいただけませんでしたが、Cells(8, 3)には、値があるのですか？それとも数式があるのですか？
回答４にも書きましたが、それによって
＞Cells(8, 3).Copy Destination:=Worksheets("総合評価").Cells(4 + i, 3)
の結果が全く違ってきます。一体どちらなのでしょう？

＞求めた各パーセントに対するそれぞれ３つの値の結果を
＞Worksheets("総合評価")のC4,D4,E4にコピペしたいのです。
コピペの目的は何ですか？セルCells(8, 3)の値が欲しいのでしたら
Worksheets("総合評価").Cells(4 + i, 3).Value=Cells(8, 3).Value
とした方がいいでしょう。

いずれにしても、示していただいたマクロの説明では、集計シートのCells(8, 3)などにある値がどう変化するのかが全然見えてきません。
＞Cells(41, 12).Value = 50 + (i * 5)
とCells(8, 3)などとの関係が依然として不明です。変数計算シートとSheet1～Sheet11、集計シートの関係もわかりません（といって、全シート・全セルの複雑な関係を長文で書かれたとしても困るのですが）。なので、回答も憶測の域を出ません。
＞Cells(8, 3).Copy Destination:=Worksheets("総合評価").Cells(4 + i, 3)
にブレークポイントを設定して、そこからF8でステップ実行してセルの値を確認してみてください。

お礼

＞またお答えがいただけませんでしたが、Cells(8, 3)には、値があるのですか？それとも数式があるのですか？
大変失礼致しました。Cells(8, 3)、Cells(10, 3)、Cells(31, 3)には
数式が入っております。

シートの関係ですが、変数計算シート→Sheet1～Sheet11→集計シートという
流れで数式を組んでおり、集計シートの結果の値を総合評価のシートに順に
コピペするというコードです。

ご指摘頂いた
「Worksheets("総合評価").Cells(4 + i, 3).Value=Cells(8, 3).Value」
のコードとWith...End Withを使用し、Next iの前に

With Worksheets("総合評価").Cells(4 + i, 3).Value=Sheets("集計").Cells(8, 3).Value
Worksheets("総合評価").Cells(4 + i, 3).Value=Sheets("集計").Cells(10, 3).Value
Worksheets("総合評価").Cells(4 + i, 3).Value=Sheets("集計").Cells(31, 3).Value
End With

を入れる事でおそらく希望通りの結果になりそうです。

うまく説明することが出来ず、申し訳ありませんでした。
ありがとうございました。

回答No.5

rakko1

私はVBAの専門じゃないのでこのコードをパッとデバッグして差し上げれませんが、変数の挙動がわからないということであれば、ほかのループ、while などを使うといいのだと思うしだいです。
for句と違い、ループ中で、パラメーターの増減を自分で責任をもって操作できますから。

あと、アルゴリズムの表示にはソースよりも、チャートを使うのがわれわれの世界では一般的です。PDFなどにすればダウンロードもできますしね。
最低でもフローチャート、親切なのは、PADチャートであればどのような言語にでも出来ます。

回答No.4

cistronezk

マクロの説明がないので、憶測ばかり先行してしまいます。

＞このコードですと、同じ値が「総合評価」にコピペされてしまいました。
ということは、Cells(8, 3)などには「式」ではなく「値」があると推測できます。
加えて
＞Cells(8, 3).Copy Destination:=Worksheets("総合評価").Cells(4 + i, 3)
という式は、値貼り付けはしません。
つまり、Cells(8, 3)に数式があれば、Worksheets("総合評価").Cells(4 + i, 3)にもその数式がコピーされます。でも、同じ式を何度もコピペする処理はナンセンスですから、Cells(8, 3)に数式があるとは思えません。
それとも、
＞Cells(8, 3).Copy Destination:=Worksheets("総合評価").Cells(4 + i, 3)
と
Worksheets("総合評価").Cells(4 + i, 3).Value=Cells(8, 3).Value
とが同じ処理だと誤解されているのでしょうか？

いずれにしても情報不足です。詳しい説明をお願いします。

ちなみにデバッグの方法としては、怪しい場所はＶＢＥでＦ８キーを叩いてステップ実行して、変数や各セルの値を地道に確認された方が確実でしょう。

補足

ご回答有り難う御座います。
うまく説明できず、申し訳ありませんでした。

詳しく説明させて頂きますと

For i = 0 To 30
Sheets("変数計算").Select
Cells(41, 12).Value = 50 + (i * 5)
でパーセントを50%から5%ずつ、200％まで変化させて、

Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", _
"Sheet8", "Sheet9", "Sheet10", "Sheet11")).Select
で各年のパーセンテージに対する反応を計算、

Sheets("日々の増減").Selectでグラフを更新
（この作業は特別必要ではありません）、

Sheets("集計").Select
ActiveSheet.EnableCalculation = True
ActiveSheet.EnableCalculation = False
Cells(8, 3).Copy Destination:=Worksheets("総合評価").Cells(4 + i, 3)
Sheets("集計").Select
Cells(10, 3).Copy Destination:=Worksheets("総合評価").Cells(4 + i, 4)
Sheets("集計").Select
Cells(31, 3).Copy Destination:=Worksheets("総合評価").Cells(4 + i, 5)
ここでSheets("集計")のC8,C10,C31にて求めたい３つの値を求めます。
求めた各パーセントに対するそれぞれ３つの値の結果を
Worksheets("総合評価")のC4,D4,E4にコピペしたいのです。
（50％の結果をC4,D4,E4にコピペ、
55％の結果をC5,D5,E5にコピペ、60％の結果をC6,D6,E6にコピペ、、、）

大変わかりにくいかとは思いますが、アドバイスを頂けたら
嬉しいです。

宜しくお願い致します。

回答No.2

suzusan7

こんばんわぁ

変数の挙動を追いたいとのことですので、
Cells(8, 3).Copy Destination:=Worksheets("総合評価").Cells(4 + i, 3)
の下に次の４行のコードを挿入してみてはいかがでしょうか？

With Worksheets("集計")
Debug.Print "i = " & i & "," & .Cells(8, 3).Value & "," & _
.Cells(10, 3).Value & "," & .Cells(31, 3).Value
End With

これは、イミディエイトウィンドウにiの値、計算した３つの数値を
出力するコードです。この出力で、計算した数値が希望通りの数値となっているかを
まず確認してみてはどうでしょうか？

ちなみに、イミディエイトウィンドウはVBAのコードを書いている画面に表示されます。
もし、表示されていない場合は、VBAコードを書いている画面のツールバーの「表示」の中に
イミディエイトウィンドウという項目がありますので、これを選択すると表示されます。
（当方エクセル２００２です）

お礼

参考になるアドバイスを有り難う御座います。

VBAはまだ始めたばかりでイミディエイトウィンドウというものを
知りませんでした。
イミディエイトウィンドウで調べた結果、希望の値になっていますが
コードがうまく書けていないせいで、希望通りの結果には
なっていない事が分かりました。

もう少し調べてみようと思います。

ありがとうございました。

回答No.1

cistronezk

＞Cells(8, 3).Copy Destination:=Worksheets("総合評価").Cells(4 + i, 3)
という式なら、常に「Cells(8, 3)」の値が「Worksheets("総合評価").Cells(4 + i, 3)」にコピーされるのですから
＞このコードですと、同じ値が「総合評価」にコピペされてしまいました。
となるの当然だと思いますが。
どういう結果を期待されているのでしょう？

補足

ご回答有り難う御座います。
私がしたいと思っている事は、

For i = 0 To 30

Sheets("変数計算").Select
Cells(41, 12).Value = 50 + (i * 5)
でパーセンテージを50%から5%ずつ、200％まで変化させ、
Sheets("Sheet1")からSheets("Sheet11")とSheets("日々の増減")
でパーセンテージに対する反応を計算、Sheets("集計")のC8,C10,C31にて３つの値を求めます。
Sheets("集計")で求めたそれぞれの値の結果をWorksheets("総合評価")のC4,D4,E4にコピペしたいのです（50％の結果をC4,D4,E4にコピペ、
55％の結果をC5,D5,E5にコピペ、60％の結果をC6,D6,E6にコピペ、、、
という具合です）。
こういったプログラムは可能なのでしょうか。