title: VBA in Excel 2010: Scripting.Dictionary return value of a function won't pass as parameter to a wrapping function call
body: <p>Have been banging my head off the table for a couple of hours over this and already searched the chuff our of the interweb, so apologies if I've missed an obvious solution...</p>
  
  <p>I have 2 functions:</p>
  
  <pre><code>Public Function DictFromRanges(rKeys As Range, rValues As Range) As Scripting.Dictionary
  
      Debug.Assert (rKeys.Columns.Count = rValues.Columns.Count)
  
      Dim rRetDict As Scripting.Dictionary
      Set rRetDict = New Scripting.Dictionary
  
      ' loop through ranges adding values from rValues with keys from rKeys
      Dim iKey As Integer
      For iKey = 1 To rKeys.Columns.Count
          rRetDict(rKeys.Columns(iKey).Value) = rValues.Columns(iKey).Value
      Next iKey
  
      ' set return value
      Set DictFromRanges = rRetDict
  
  End Function
  
  Public Function MinTurnsToKill(dictAttack As Scripting.Dictionary, iHealth As Integer) As Integer
  
     MinTurnsToKill = iHealth
  
  End Function
  </code></pre>
  
  <p>If I do the following in a cell of the spreadsheet:</p>
  
  <pre><code>=MinTurnsToKill(DictFromRanges(F2:L2,F3:L3),100)
  </code></pre>
  
  <p>DictFromRanges() seems to work fine when I single step it in the debugger.</p>
  
  <p>MinTurnsToKill() is the one that is causing my brainwrong - clearly it's empty, but it's not even getting called (I had code in it originally, but I took it all out just to confirm whether it was getting called).</p>
  
  <p>To further test what was going on I wrote 2 proxy functions:</p>
  
  <pre><code>Public Function Proxy(rKeys As Range, rValues As Range) As Variant
  
      Dim dict As Scripting.Dictionary
      Set dict = DictFromRanges(rKeys, rValues)
  
      Proxy = MinTurnsToKill(dict, 42)
  
  End Function
  
  Public Function ProxyToo(rKeys As Range, rValues As Range) As Variant
  
      ProxyToo = MinTurnsToKill(DictFromRanges(rKeys, rValues), 42)
  
  End Function
  </code></pre>
  
  <p>When called as follows from cells in the excel spreadsheet, these both work fine (i.e. both print 42):</p>
  
  <pre><code>=Proxy(F2:L2,F4:L4)
  =ProxyToo(F2:L2,F4:L4)
  </code></pre>
  
  <p>So, it looks like excel is just refusing to pass the returned dictionary from DictFromRanges() into MinTurnsToKill() if they're used in the evaluation engine that sits inside the actual cells of the spreadsheet. </p>
  
  <p>In fact it's almost as if it is refusing to acknowledge that MinTurnsToKill() even exists when I do the following in a spreadsheet cell:</p>
  
  <pre><code>=MinTurnsToKill(DictFromRanges(F2:L2,F3:L3),100)
  </code></pre>
  
  <p>I assume there must be a way to make excel play nicely and I would very much appreciate it if anyone could tell me how :)</p>
  
  <p>Cheers,</p>
  
  <p>Alex</p>