0

I have a workbook in structure not very unlike this one (but much more advanced):

https://www.youtube.com/watch?v=UeGncSFijUM

That is :

(1)a worksheet where a row makes some calculations involving rand() and where the result value is carried over to the next row where a similar calculation is done and where the final result occur after a maximum of 250 rows.

(2)Excel then repeats this 250 calculation 20000 times

(3)and do some calculations on the 20000 independent results.

Much is done through functions and I use VBA mostly to control the flow of calculations.

All this takes some time and I try to optimize it all step by step.

Now the result of each (1) is likely to be reached much before row 250, on average row 125. I would like to erase the rest of the calculations if that condition is met at, say, row 125. Say call a VBA script through an if statement that erase the content of the rest of the cells.

Is it possible to call a VBA statement from a function?

Anders
  • 147
  • 1
  • 1
  • 12
  • You cannot call a macro from the worksheet but you can call a UDF function. However, a UDF cannot alter any cell but the cell that calls it. I suppose some sort of public boolean variable might halt a looping VBA operation and a UDf *could* change the state of that. –  Nov 01 '14 at 14:03

1 Answers1

0

Here's my best shot at some outside the box thinking.

Could you possibly use Call Methodname or CallByName(wsWorksheet, sMethodname, VbMethod, vOptionalVariantToPass) on a worksheet_change event that checks for specific values resulting from your formulas?

Arwel The Iridescent
  • 1
  • 4