Excel vba: Is it possible to access the value of a cell before the UDF called from that cell executes?
excel, vba
Solution
There are several possible ways, but they all have disadvantages. The simplest way is to use Application.Caller.Text but it returns the formatted value rather than the actual value. See my blog post on the subject for more discussion http://fastexcel.wordpress.com/2012/01/08/writing-efficient-vba-udfs-part-8-getting-the-previously-calculated-value-from-the-calling-cells/
Problem
I have a UDF which reads data from other sheets indirectly, adding the same cell across a number of sheets, ie: ``` Function myFunction( StrArgs As String ) As Long .... End Function ``` I'm calling this function from cell A1, which right now has a value of 100: ``` =myFunction( ... ) ``` Calculation is set to manual, and the sheet is refreshed when needed. Since the arguments StrArgs define the sheets to be queried, I'm including some error-checking inside myFunction in case either the specified sheets don't exist, or there's an error in the specification of StrArgs. This works fine, however what I'm struggling with is the following: when an error is found within myFunction, I want to return (keep) the existing value of the calling cell, instead of a zero or error value. What I want to do at the beginning of myFunction is: ``` existingCellValue = Application.Caller.Text 'or Application.Caller.Value ``` Then perform the calculations, and when an error is encountered: ``` myFunction = existingCellValue ``` However, I find that this returns zero. In the debugger I see that, as soon as myFunction starts to execute, the cell value is already set to zero. My question is - is there a way to access the existing value of the calling cell, before executing the UDF ? -- edit - more complete code, as an example, which seems to work fine: -- ``` Function GETNUMBER(Col As String, Row As Integer) As Double Dim LookStr As String Dim TheAnswer As Double Dim CellVal As Variant On Error GoTo errHandler CellVal = Application.Caller.Text LookStr = "=" & Col & Row TheAnswer = Application.Evaluate(LookStr) GETNUMBER = TheAnswer On Error GoTo 0 Exit Function errHandler: GETNUMBER = CellVal End Function ``` With the above code in a module, I enter the following in my workbook: ``` Row 1: | D | 1 | =GETNUMBER(A1,B1) | 10 2: | D | 2 | =GETNUMBER(A2,B2) | 20 3: | D | 3 | =GETNUMBER(A3,B3) | 30 ``` This returns the values of 10, 20 and 30 from column D. Now I change one of the cells in column B to zero, to invoke the errHandler, and return CellVal, stored at the start. This seems to work, and both Application.Caller.Text and Application.ThisCell.Text give the correct result. Thanks to both Charles Watson and KazJaw, both of whom answered the question.