title: VBA function forcing recalculation
body: <p>I have a DLL that I'm calling through an Excel VBA function using a subroutine containing <code>Declare Function &lt;func&gt; Lib</code>. In order to control the location of the dll, I'm using <code>Application.VBE</code> to create a module at run time that contains the function reference (a bit messy, but the only solution that seemed to work). </p>
  
  <p>Everything works fine, but modifying modules has the unwelcome consequence that the cell is marked as dirty immediately after evaluation causing i) no result to be written to the cell and ii) immediate revaluation of the function. </p>
  
  <p>Is there a way to prevent a function from dirtying the cell? </p>
  
  <p>pseudo-code (actual code is pretty horrible)</p>
  
  <p>Module1:</p>
  
  <pre><code>Function main()
    newModule = Proj.vbcomponents.Add(1).CodeModule.AddFromString(stng)
    main = module2.callDll()
  End Function
  </code></pre>
  
  <p>Module2 (contained within stng):</p>
  
  <pre><code>Declare Function &lt;func&gt; Lib
  Function callDll()
    callDll = func
  End Function
  </code></pre>
  
  <p>I am calling the function in a worksheet cell using <code>=main()</code></p>
  
  <p>Thanks!</p>
  
  <p>Mat</p>
  
  <p>UPDATE</p>
  
  <p>I have tried converting to manual calculation, but this doesn't seem to solve either problem described above. The following code demonstrates the problem </p>
  
  <pre><code>Function main()
    Application.Calculation = xlCalculationManual
    Dim stng As String
    stng = _
    "Function callDll()" &amp; Chr(13) &amp; _
    "callDll = 1" &amp; Chr(13) &amp; _
    "End Function"
    On Error Resume Next
    ThisWorkbook.VBProject.VBComponents.Remove ThisWorkbook.VBProject.VBComponents(6)
    On Error GoTo 0
    ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule).CodeModule.AddFromString (stng)
    main = Module2.callDll()
  End Function
  
  Function mainAlt()
    mainAlt = Module2.callDll()
  End Function
  </code></pre>
  
  <p>A worksheet call to <code>=main()</code> returns zero but a call to <code>=mainAlt()</code> returns the correct result (one) </p>
  
  <p>Note that 'visual basic for applications extensibility...' needs to be enabled as a reference for this to work, and i've lazily assumed that the created module will have index 6. </p>
  
  <p>Cheers </p>