title: Excel vba: Is it possible to access the value of a cell before the UDF called from that cell executes?
body: <p>I have a UDF which reads data from other sheets indirectly, adding the same cell across a number of sheets, ie:</p>
  
  <pre><code>Function myFunction( StrArgs As String ) As Long
  
    ....
  
  End Function
  </code></pre>
  
  <p>I'm calling this function from cell A1, which right now has a value of 100:</p>
  
  <pre><code>=myFunction( ... )
  </code></pre>
  
  <p>Calculation is set to manual, and the sheet is refreshed when needed.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>What I want to do at the beginning of myFunction is:</p>
  
  <pre><code>existingCellValue = Application.Caller.Text   'or Application.Caller.Value
  </code></pre>
  
  <p>Then perform the calculations, and when an error is encountered:</p>
  
  <pre><code>myFunction = existingCellValue
  </code></pre>
  
  <p>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.</p>
  
  <p>My question is - is there a way to access the existing value of the calling cell, before executing the UDF ?</p>
  
  <p><b>-- edit - more complete code, as an example, which seems to work fine: --</b></p>
  
  <pre><code>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 = "=" &amp; Col &amp; Row
      TheAnswer = Application.Evaluate(LookStr)
  
      GETNUMBER = TheAnswer
  
      On Error GoTo 0
  
  Exit Function
  
  errHandler:
      GETNUMBER = CellVal
  
  End Function
  </code></pre>
  
  <p>With the above code in a module, I enter the following in my workbook:</p>
  
  <pre><code>Row
  1:   | D    | 1    | =GETNUMBER(A1,B1)    | 10
  2:   | D    | 2    | =GETNUMBER(A2,B2)    | 20
  3:   | D    | 3    | =GETNUMBER(A3,B3)    | 30
  </code></pre>
  
  <p>This returns the values of 10, 20 and 30 from column D.</p>
  
  <p>Now I change one of the cells in column B to zero, to invoke the errHandler, and return CellVal, stored at the start.</p>
  
  <p>This seems to work, and both Application.Caller.Text and Application.ThisCell.Text give the correct result.</p>
  
  <p>Thanks to both Charles Watson and KazJaw, both of whom answered the question.</p>