Vba excel custom function not updating

That is not arbitrary, but makes some sense, as normally format changes do not influence anything else, so a recalculation - which is expensive - would be wasted.Since Excel now allows VB to use format information and react to it, this can become an issue, as it is for you.

vba excel custom function not updating-47

Excel cannot examine the VBA code within the UDF and identify cells that may influence the function's output.

So while the function's author may construct the function to return different results based on any number of changes made in the workbook, the only cells Excel knows will change the function's result are the declared inputs.

They cannot be used to try and change any part of another cell in any way at all.

This means a UDF cannot place a value into any other cell except the cell housing the UDF.

Extra info: I'm using Excel on the Mac and I've checked in settings and automatically calculate is turned on.

During normal use of my workbook, the code of my UDF will only be called if there's a change to the contents of cell A2 or another cell referenced by a formula in A2.

By accident I discovered that including indirect cell reference in the function call appears to make it run whenever (seemingly) any other cell is edited.

Presumably this is because excel has fewer guarantees about what it can trust to be unmodified when an indirect modifier is used.

A UDF cannot alter the structure of a Worksheet, such as changing the Worksheet name, turning off gridlines, protecting the Worksheet etc.

They cannot change a physical characteristic of a cell, including the one that houses the UDF, so we cannot use a UDF to change the font colour, background colour etc of any cell.

There are however a few drawbacks when writing a Function in the VBE as opposed to a Macro or Procedure.

121 Comments

  1. The competitors list can be found next tothe search input field above.

  2. we started talking nonstop and have now been together since December 2016. My best love story ever My sophomore year in hs we ended up having both chemistry and history together. Sometimes I scare myself wondering what if we’d never had that drunk night...

Comments are closed.