Vba excel custom function not updating


14-Sep-2020 14:38

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.Although Excel has over 300 built in functions, there are those times when you cannot achieve the result you want with a standard Function or you must get your result by creating a deeply nested formula using a combination of standard functions.It is at these times that a Custom Function or User Defined Function (UDF) can be very handy.Therefore, changes to these cells are the only ones Excel pays attention to when deciding whether the UDF needs to be recalculated.I hit this while hoping to update all user defined functions whenever an Active X textbox was changed even if they didn't affect linked cells which were used as inputs to the UDF.To make matters even worse, when you use a line of code in a UDF that cannot be executed you may not receive a Run-time error.You just end up with one of the error values (eg; #VALUE! This can make de-bugging UDF's very difficult and leave one scratching their head!

UDF's do not have the same flexibility as a standard Procedure.You can work around this behavior in several ways: When Excel performs an automatic recalculation, it does not recalculate every single formula in the workbook.Instead it only updates those cells which contain formulas that refer to the most recently modified cell.SUM(r Cell) v Result End If Next r Cell Else For Each r Cell In r Range If r Cell. Color Index = l Col Then v Result = 1 v Result End If Next r Cell End If Color Function = v Result End Function It works fine and gives me the expected result if I click the green tick in the formula bar but won't update automatically after I colour another cell.

How do I set it to automatically update the count every time I manually colour a cell elsewhere on the spreadsheet?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.