I have a monte carlo simulation I am running in the cloud based on an Excel spreadsheet. The process works in a loop, and goes through 10000 iterations. The way I have it set up, it goes through the cells that will hold the random variables and assigns them a random number based on a distribution. After it’s done assigning the new random numbers, I have to pull the resultant calculation from a single cell on another sheet.

Hi,

Yes, you can try to set the formula calculation chain to false if you need to call Workbook.CalculateFormula() method only once, see the document for your reference:

Alternatively, you may also try to call a formula directly, see the document for your reference:

Moreover, you may also consider using Worksheet.CalculateFormula() and Cell.Calculate() method to calculate all the formulas in a single sheet and in a single cell accordingly.

Hope, this helps a bit.

Thank you.

Which should I used if I must recalculate my spreadsheet 10,000 times?

Hi,

I believe I know what’s happening. After I perform a FormulaCalculation, I go back and rewrite essentially all the formulas used. So when I call the FormulaCalculation again, it has to recreate the chain once again. That must be why it takes so long.

jpo645:I believe I know what's happening. After I perform a FormulaCalculation, I go back and rewrite essentially all the formulas used. So when I call the FormulaCalculation again, it has to recreate the chain once again. That must be why it takes so long.

jpo645:I saw in another post, someone used something like FormulaCalculation(true, CustomFunction)... what is that second parameter in the FormulaCalculation method? Perhaps I could circumvent having to rewrite every formula by using a custom formula. (In fact, how I am rewriting formulas now is to replace a UDF I had created...)

Thank you! If I replace my custom function with this, and then simply iterate 10000 times (Without having to rewrite the formulas each time), do you believe that should speed things up considerably?

Hi,

I can’t really provide you even a console application as the work is proprietary. What I can do is provide you the code (slightly edited) where the bottleneck takes place:

One other question, if I am using

Hi,