Search code examples
excelworksheet-functionvba

How intelligent is excel when is "parses" a function in a cell that includes identical calls to the same function more than once

Consider this: Cell C1 has a formula that calls another function many times:

eg =ExcelFuntionA(ExcelFunctionCallB(1), ExcelFunctionCallB(1)-1, ExcelFunctionCallB(1)-2, ExcelFunctionCallB(1)*3)

Note that the parameter(s) passed to ExcelFunctionCallB have the same value(s) each time.

If ExcelFunctionCallB(..) is a function that involves significant processing, would excel process the formula quicker if the result of ExcelFunctionCallB(..) was found in anther cell and the above formula was changed to reference this cell 4 times.

eg

Say A2 has the formula ExcelFunctionCallB(..) C1 would then read =ExcelFuntionA(A2, A2-1, A2-2, A2*3)

I'm wondering how intelligent excel is!

  1. Can is see that calls are identical and only process a call to the function once?

  2. Is it intelligent enough to do this for array formulas as well as normal function formulas?

  3. If you call a function that has been written in VBA how does it work?
    Bear in might that a VBA function could reference cells directly that are not passed as parameters ( I know this is naughty, but it happens, and there is only small chance that it might return a different value even if the parameters that are used with it are the same.)

Following on from the above, is there a way of telling the code that a VBA function does not access any excel cells directly? (A bit like application.volatile I would imagine)

A more realistic example: I've actually been experimenting with formulas like this:

=MID($A2, FIND("@",SUBSTITUTE("#" & $A2 & "#","#","@",B$1)), FIND("@",SUBSTITUTE("#" & $A2 & "#","#","@",B$1+1)) - FIND("@",SUBSTITUTE("#" & $A2 & "#","#","@",B$1)) - 1 )

which is well documented in one of the proposed by not approved answers to SO question 24182334/splitting-text-columns

Note that this formula uses the text FIND("@",SUBSTITUTE("#" & $A2 & "#","#","@",B$1)) three times.

[PS. I know about split in VBA and the text to columns menu option.]

Solution

  • If ExcelFunctionCallB(..) is a function that involves significant processing, would excel process the formula quicker if the result of ExcelFunctionCallB(..) was found in anther cell and the above formula was changed to reference this cell 4 times.

    This is correct, referencing an already calculated cell is faster than recalculating the same thing multiple times in an individual cell. Excel will calculate each one separately

    Also, as pointed out by pnuts, this link may be of interest: http://msdn.microsoft.com/en-us/library/office/ff700515%28v=office.14%29.aspx

    (posted as answer at chris neilsen's suggestion)