title: VB.NET COM Server implementing Excel UDF not callable with optional Excel.Range
body: <h2>Versions</h2>
  
  <p>Excel 2007 (12.0.6425.1000) SP2, Visual Studio 2008 with VB.NET, Windows Vista </p>
  
  <h2>The windup</h2>
  
  <p>I have an Excel UDF written in VB.NET that has this signature:</p>
  
  <pre><code>Public Function NonExcelName(ByVal A As Integer, _
                          ByRef B As Range, ByRef C As Range, _
                          Optional ByVal D As Integer = 1, _
                          Optional ByVal E As Double = 0, _
                          Optional ByVal F As Range = Nothing, _
                          Optional ByVal G As Boolean = True, _
                          Optional ByVal H As Integer = 1) As Object
  </code></pre>
  
  <p>I had no trouble calling this in Excel with optional arguments like this:</p>
  
  <pre><code>=NonExcelName(99,$D$2:$D$65,$B$2:$B$65,12,,,,0)
  </code></pre>
  
  <p>for several months through last night. Now, it's a problem. Not sure why. The symptoms are:</p>
  
  <blockquote><ol>
  <li>required arguments only<br />
  If I provide all the required arguments and omit the optional ones, the function is callable: I will hit a breakpoint in the VB.NET debugger.</li>
  
  <li>required arguments plus some optional<br />
  If I provide all the required arguments plus the optional ones up to optional Range G, the function is callable: I will hit a breakpoint in the VB.NET debugger.</li>
  
  <li>required arguments plus optional through Range G<br />
  If I call the function with all the required arguments plus optional arguments including Range G, then I get <i>#VALUE!</i>. In this case, a break point at the first line of code in the debugger is not hit: the function simply is not called.</li>
  
  <li>all parameters<br />
  Works.</li>
  
  <li>all parameters except optional parameter G<br />
  Does not work.</li>
  </ol></blockquote>
  
  <p>I can't identify any Windows updates that might have caused this. I have other COM Servers implemented in the same way that do not have optional Ranges and are not exhibiting this problem.</p>
  
  <p>I have experimented with <code>Optional ByVal F As Range = Nothing</code> to no effect.</p>
  
  <p>I have not rolled my source code back to last night yet. I'm trying that next.</p>
  
  <hr>
  
  <h2>The pitch</h2>
  
  <p>Is there some rule of COM/.NET interaction that I am violating? Is there something in the Windows universe that has changed? What would explain why Excel/COM refuses to call this function with a comma-separated optional parameter?</p>
  
  <hr>
  
  <p>Edit 2009-10-07: Changing Integer arguments to Double does not help. Changing <code>Optional ByVal F As Range = Nothing</code> to <code>Optional ByRef F As Range = Nothing</code> does not help.</p>
  
  <hr>
  
  <p>Edit 2009-10-10: I changed the UDF from <em>DB</em> to <em>NonExcelName</em> to underline the fact that it is not clashing with an Excel function name. The real name is for custom software to be released on teh web, so I did not want to give the name away.</p>
  
  <hr>
  
  <p>Edit 2009-10-11: I've run Mike's code and I get much the same results as with my own code: it fails when I omit the optional Range argument with a comma.</p>
  
  <p><a href="http://www.iwebthereforeiam.com/files/MyDb%20fail.gif" rel="nofollow noreferrer">alt text http://www.iwebthereforeiam.com/files/MyDb%20fail.gif</a></p>
  
  <p>I can put in the mandatory arguments and I am fine, but once I omit the optional argument with a comma (either putting in the rest of the arguments or not), the function call generates a <em>#VALUE!</em>.</p>
  
  <p>Edit 2009-10-11: Maybe it's like <a href="https://stackoverflow.com/questions/1168290/how-to-pass-a-late-bound-parameter">this</a>: maybe my optional Range is being passed as a String instead of as Nothing. How would I go about testing/confirming this? I don't seem to hit a breakpoint inside my code.</p>
  
  <hr>
  
  <p>Edit 2009-10-12: Wow. The fix was in two parts. </p>
  
  <p>(1) Use of VB.NET IIf() statement</p>
  
  <p>This does not short-circuit like the ternary operator in C/C++/C#. You cannot write code like this and expect it to work in VB/VBA/VB.NET:</p>
  
  <pre><code>Return IIf(optRange Is Nothing, "&lt;No Range Provided&gt;", optRange.Address)
  </code></pre>
  
  <p>VB/VBA/VB.NET seems to evaluate both expressions, causing an exception when the argument <em>optRange</em> is Nothing. You have to write it like this:</p>
  
  <pre><code>If optRange Is Nothing Then
      Return "&lt;No Range Provided&gt;"
  Else
      Return optRange.Address
  End If
  </code></pre>
  
  <p>(2) Use of optional Object instead of optional Range</p>
  
  <p>I don't quite know what the problem is, but I can't get any function to take optional Ranges anymore. I am passsing optional Objects and it works. This does not work:</p>
  
  <pre><code>Function OptionalRange2(Optional ByVal optRange1 As Excel.Range = Nothing, Optional ByVal optRange2 As Excel.Range = Nothing) As Object
  </code></pre>
  
  <p>and this does:</p>
  
  <pre><code>Function OptionalRange3(Optional ByVal optRange1 As Object = Nothing, Optional ByVal optRange2 As Object = Nothing) As Object
  </code></pre>
  
  <hr>
  
  <p>Edit 2009-10-12: I've explored the multiple Range parsing hypothesis. Interesting, but not the cause, as far as I can tell. This function signature should not have that problem because it has an integer in the middle:</p>
  
  <pre><code>Function OptionalRange4(Optional ByVal optRange1 As Excel.Range = Nothing, _
                          Optional ByVal optInt As Integer = 0, _
                          Optional ByVal optRange2 As Excel.Range = Nothing) As Object _
      Implements IFunctions.OptionalRange4
  </code></pre>
  
  <p>And yet it produces <em>#VALUE!</em> for <code>=OptionalRange4(,12,)</code>.</p>
  
  <p>I have a fix (declare optional Object, not Range, and cast the argument) but I don't know why I have the problem.</p>