0

Assume that a have some values in cells A1 and A2, respectively. In A3, there is a formula, =A1+A2. In a macro, in a loop I am changing values of A1 and A2, and then my code reads the value in A3, which is a function of A1 and A2. It is basically as follows;

for i = 1 to 10
    Range("A1").Value = Range("A1").Value + i
    Range("A2").Value = Range("A2").Value + i*2
    somearray(i) = Range("A3").Value
Next i

So during an iteration, how can I be sure that A3 is already updated according to new values of A1 and A2, before I get the value in A3 to assign in an array? I just used very simple formula to explain it clearly, but in my real code, there will be very complex formulations, so I need to be sure that I am recording correct (updated) values in A3. Could anybody help me to clear my mind? Can we say that vba automatically updates value in A3 and does not goes to next line of my code without finishing the formula update?

Thanks in advance, Abdullah

braX
  • 11,506
  • 5
  • 20
  • 33
abilgin
  • 1
  • 1
    Add `Range("A3").Calculate` before reading its value – chris neilsen Jul 19 '20 at 20:52
  • Thank you Chris, I think it will handle my problem. – abilgin Jul 19 '20 at 21:40
  • 1
    Does this answer your question? [Wait until Application.Calculate has finished](https://stackoverflow.com/questions/11277034/wait-until-application-calculate-has-finished) – DeanOC Jul 19 '20 at 23:53
  • Calculating the entire workbook because one value was changed appears like taking out a baseball bat to swat a mosquito. If your workbook is large and the delay causes friction consider transferring the calculation form the cell to VBA. This solution especially recommends itself if the calculation is complicated because the VBA version is much easier to maintain - and much less likely to be messed up by an unwitting user. – Variatus Jul 20 '20 at 01:17

0 Answers0