Updated Answer Regarding Optional Range Parameters
Ok, after discussing the issue with some Excel MVPs, there's a couple of points I can add to this quirky issue regarding optional Range parameters when used in a user-defined function (UDF) created using VB.NET.
(1) The first is that this appears to be some sort of subtle issue regarding how Excel calls the .NET user-defined function; the issue cannot be replicated using VBA. For example, the following function written in VBA does not exhibit the same problem:
Function OptionalRange4(Optional ByVal optRange1 As Excel.Range = Nothing, _
Optional ByVal optInt As Integer = 0, _
Optional ByVal optRange2 As Excel.Range = Nothing) _
As Variant
Dim arg1 As String
Dim arg2 As String
Dim arg3 As String
If optRange1 Is Nothing Then
arg1 = "<Nothing>"
Else
arg1 = optRange1.Address
End If
arg2 = CStr(optInt)
If optRange2 Is Nothing Then
arg3 = "<Nothing>"
Else
arg3 = optRange2.Address
End If
OptionalRange4 = arg1 + "|" + arg2 + "|" + arg3
End Function
(2) The other information I learned is that you can pass in multi-area ranges into a single parameter of a worksheet function by enclosing the entire range address in brackets. For example, the following formula will pass in two Range arguments to the "MyFunction", the first argument being a multi-area address:
=MyFunction((A1:C3,D4:E5), G11)
So the two solutions available to you would appear to be:
(a) Change your parameter types from Range to Object and then cast the Object to a Range within your code. This would seem to be the cleanest and easiest approach.
(b) Create a VBA wrapper as your front-end, which then calls your Visual Basic.NET code. I'm sure that this is not what you would want, but I thought that I should mention it. For what it's worth, this approach is mandatory if wishing to use UDFs from VSTO, at least as of Visual Studio 2008. The article How to create Excel UDFs in VSTO managed code by Paul Stubbs describes this very approach.
That's all I can add Hugh. It is a subtle bug or flaw in how the Excel call tries to work it's way through the .NET Interop. I don't know why exactly it fails, but it does. Fortunately, the solution to it is not too onerous.
-- Mike
Prior Answer Regarding Optional Range Parameters
I still don't know why I can't get this to work:
Function OptionalRange2(Optional ByVal optRange1 As Excel.Range =Nothing, _ Optional ByVal optRange2 As Excel.Range = Nothing) As Object _ Implements IFunctions.OptionalRange2
Both =OptionalRange2(E2:E7,F2:F7) First =OptionalRange2(E2:E7,)
Second =OptionalRange2(,F2:F7)
Neither =OptionalRange2()
Neither with comma =OptionalRange2(,)[Calls to Both and Neither are successful.] Putting in the comma changes the call. It's as if with no comma, Excel sends two Nothings but with the comma it does something else --
•send System.Type.Missing/System.Reflection.Missing.Value?
•call Range.Value(), the default function on Range, passing a parameter of a different type?
Either way, it's like there is a type mismatch at runtime because it just does not get into the debugger's stack frame.
What's happening here is that Excel can get confused when passing in range arguments. The reason is that a range address can include a comma in it, such as the multi-area address "A1:C3, D4:E5". When you pass in such an address as a range argument, Excel has to make a determination as to whether this is a single multi-area range or consists of two single-area range arguments.
So the question is: which way does Excel interpret it?
The answer is that Excel always interprets the comma as an argument separator, so there is actually no way to pass in a multi-area range into a single parameter in Excel directly. To do that, you'd have to pass in a named range (such as defining a multi-area range named "MyRange") and then passing that into your user-defined worksheet function.
So, in these examples, when you pass in MyFunction(A1:C3, D4:E5), you are using up both parameters, not just one.
Ok, but where's the problem?
The problem is: how does Excel interpret the range address "A1:C3," when passed into a worksheet function? The answer is that Excel attempts to interpret this as a single range address with invalid syntax, since the final part of the address following the comma is missing. In short, it's a syntax error, so your function is never called in the first place, and a #VALUE results.
This is very unfortunate since I already said that Excel is supposed to give precedence to interpreting the comma as an argument separator, not as an area separator within a range address. But, alas, Excel is inconsistent here and it attempts to interpret a leading or trailing comma as part of the range address itself.
You hit on the solution yourself earlier: change the data type for these optional parameters from Range to Object and then cast the Object to Range within your code. If you do this, then Excel will no longer attempt to interpret the arguments as a Range and everything will work fine.
You can test the difference between the two approaches with the following user-defined functions:
Function DualOptionalRanges(Optional ByVal optRange1 As Excel.Range = Nothing, _
Optional ByVal optRange2 As Excel.Range = Nothing) _
As Object _
Implements IFunctions.DualOptionalRanges
Dim arg1 As String = If(optRange1 IsNot Nothing, optRange1.Address, "<Nothing>")
Dim arg2 As String = If(optRange2 IsNot Nothing, optRange2.Address, "<Nothing>")
Return arg1 + "|" + arg2
End Function
Function DualOptionalVariants(Optional ByVal optVariant1 As Object = Nothing, _
Optional ByVal optVariant2 As Object = Nothing) _
As Object _
Implements IFunctions.DualOptionalVariants
Dim range1 As Excel.Range
Try
range1 = CType(optVariant1, Excel.Range)
Catch ex As Exception
range1 = Nothing
End Try
Dim range2 As Excel.Range
Try
range2 = CType(optVariant2, Excel.Range)
Catch ex As Exception
range2 = Nothing
End Try
Dim arg1 As String
If range1 IsNot Nothing Then
arg1 = range1.Address
Else
arg1 = If(optVariant1 IsNot Nothing, optVariant1.ToString(), "<Nothing>")
End If
Dim arg2 As String
If range2 IsNot Nothing Then
arg2 = range2.Address
Else
arg2 = If(optVariant2 IsNot Nothing, optVariant2.ToString(), "<Nothing>")
End If
Return arg1 + "|" + arg2
End Function
I think this should be the last hurdle. (Famous last words, right?)
-- Mike
Answer Regarding IIF
Bummer about the IIF ("immediate if") issue. I believe that other than the CType() and DirectCast() operators, and new functionality given to the IF keyword, everything that utilizes method syntax in VB.NET is, in fact, a method call that is executed at run-time. CType() and DirectCast() are the exceptions; they are casting mechanisms that are evaluated at compile-time, and the IF keyword can now be utilized as an operator using a method-like syntax, but it uses short-circuit evaluation instead of evaluating all parameters before evaluating the IF operator.
The lack of a short-circuit capability for IIF was discussed at length by Paul Vic in 2006 in the article IIF, a True Ternary Operator and Backwards Compatibility. The article shows a strong leaning towards changing IIF to use short-circuit evaluation as of VB.NET 2009. However, due to backwards-compatibility issues, they decided not to change the behavior of IIF and to simply extend how the IF keyword can be used, allowing it to be utilized as an operator. This is described in IIF becomes If, and a true ternary operator, also by Paul Vick.
Sorry you got snagged on this.
-- Mike
Answer Regarding Naming Conflicts
Hi Hugh,
I think you have a naming conflict with Excel's built-in 'DB' worksheet function. If you rename your user-defined function from "DB" to "MyDb" (or almost anything else) I think you'll be fine.
It was tough to know what might be going wrong with your add-in, because your code looks clean. So I created an automation add-in using VB.NET and experimented with your user-defined function (UDF) using the same exact parameter signature you described. I found no problems.
The automation add-in I used was defined as follows:
Imports System
Imports System.Collections.Generic
Imports System.Runtime.InteropServices
Imports Microsoft.Win32
Imports System.Text
Imports Excel = Microsoft.Office.Interop.Excel
<ComVisible(True)> _
<Guid("7F1A3650-BEE4-4751-B790-3A527195C7EF")> _
Public Interface IFunctions
' * User-Defined Worksheet Functions Definitions *
Function MyDb(ByVal A As Integer, _
ByRef B As Excel.Range, _
ByRef C As Excel.Range, _
Optional ByVal D As Integer = 1, _
Optional ByVal E As Double = 0, _
Optional ByVal F As Excel.Range = Nothing, _
Optional ByVal G As Boolean = True, _
Optional ByVal H As Integer = 1) As Object
Function OptionalBoolean(Optional ByVal optBoolean As Boolean = False) As Object
Function OptionalDouble(Optional ByVal optDouble As Double = 0.0) As Object
Function OptionalInteger(Optional ByVal optInteger As Integer = 0) As Object
Function OptionalRange(Optional ByVal optRange As Excel.Range = Nothing) As Object
Function OptionalVariant(Optional ByVal optVariant As Object = Nothing) As Object
End Interface
<ComVisible(True)> _
<Guid("FCC8DC2F-4B44-4fb6-93B5-769E57A908A1")> _
<ProgId("VbOptionalParameters.Functions")> _
<ComDefaultInterface(GetType(IFunctions))> _
<ClassInterface(ClassInterfaceType.None)> _
Public Class Functions
Implements IFunctions
' * User-Defined Worksheet Functions */
Function MyDb(ByVal A As Integer, _
ByRef B As Excel.Range, _
ByRef C As Excel.Range, _
Optional ByVal D As Integer = 1, _
Optional ByVal E As Double = 0, _
Optional ByVal F As Excel.Range = Nothing, _
Optional ByVal G As Boolean = True, _
Optional ByVal H As Integer = 1) As Object _
Implements IFunctions.MyDb
Return "MyDb Successfully called"
End Function
Function OptionalBoolean(Optional ByVal optBoolean As Boolean = False) As Object _
Implements IFunctions.OptionalBoolean
Return optBoolean.ToString()
End Function
Function OptionalDouble(Optional ByVal optDouble As Double = 0.0) As Object _
Implements IFunctions.OptionalDouble
Return optDouble.ToString()
End Function
Function OptionalInteger(Optional ByVal optInteger As Integer = 0) As Object _
Implements IFunctions.OptionalInteger
Return optInteger.ToString()
End Function
Function OptionalRange(Optional ByVal optRange As Excel.Range = Nothing) As Object _
Implements IFunctions.OptionalRange
If optRange Is Nothing Then
Return "<No Range Provided>"
Else
Return optRange.Address
End If
End Function
Function OptionalVariant(Optional ByVal optVariant As Object = Nothing) As Object _
Implements IFunctions.OptionalVariant
If optVariant Is Nothing Then
Return "<No Argument Provided>"
Else
Return optVariant.ToString()
End If
End Function
' * automation add-in Registration *
<ComRegisterFunctionAttribute()> _
Public Shared Sub RegisterFunction(ByVal type As Type)
Registry.ClassesRoot.CreateSubKey( _
GetSubKeyName(type, "Programmable"))
Dim key As RegistryKey = _
Registry.ClassesRoot.OpenSubKey( _
GetSubKeyName(type, "InprocServer32"), _
True)
key.SetValue( _
String.Empty, _
System.Environment.SystemDirectory + "\mscoree.dll", _
RegistryValueKind.String)
End Sub
<ComUnregisterFunctionAttribute()> _
Public Shared Sub UnregisterFunction(ByVal type As Type)
Registry.ClassesRoot.DeleteSubKey( _
GetSubKeyName(type, "Programmable"), _
False)
End Sub
Private Shared Function GetSubKeyName(ByVal type As Type, ByVal subKeyCategory As String) As String
Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder()
sb.Append("CLSID\{")
sb.Append(type.GUID.ToString().ToUpper())
sb.Append("}\")
sb.Append(subKeyCategory)
Return sb.ToString()
End Function
End Class
Using the above, one can test for individual optional parameters such as Booleans, Doubles, Integers, Ranges, or any Variant via the 'OptionalBoolean', 'OptionalDouble', 'OptionalInteger', 'OptionalRange', and 'OptionalVariant' functions. And the complete function signature in question can be tested via the 'MyDb' function. None of the above failed in my testing, unless passing in an obviously incompatible argument type, such as passing in a string into an integer or double parameter, passing a numeric value into a range parameter, or the like.
There are problems, however, when naming the main user-defined function "DB", because this conflicts with Excel's built-in "DB" worksheet function, which stands for "Declining Balance" and has existed since at least Excel '97. Basically, because the user-defined function in your automation add-in has the same name as Excel's built-in version, your UDF is ignored.
In my testing using Excel 2007, I could not get Excel to recognize your 'DB' user-defined function at all. I'm not sure why you seemed to get erratic behavior, where your call sometimes came through and sometimes did not. It is possible that earlier versions of Excel handle this naming conflict differently.
The bottom line is, though, that you should be able to rename your UDF and then have no problems. I don't see anything else that could be causing this.
I hope this does it for you Hugh, fingers crossed....
Mike
Answer Regarding Optional Data Types
With this amount of information, I can only guess, but I do not think that you are not violating any COM Interop rules.
My guess is that the problem has to do with what arguments you are providing to your function. If the arguments passed into the user-defined function (UDF) do not match the expected parameter types, then Excel will throw a #VALUE! error without ever calling your code at all.
So in your third scenario, which is failing, you are passing in one of the parameters incorrectly. For example, the signature for the 'F' parameter is:
Optional ByVal F As Range = Nothing
With this signature, if you pass it a value directly, such as a string "Hello", then a #VALUE! error will result without your code ever getting called. If you passed in a cell reference that held the value "Hello", however, then the 'F' parameter would accept this with no problem.
What you need to do is to try calling your UDF while very carefully considering the parameter types being passed in. Change the parameters one at a time and see which force your UDF to at least be called. You can either set a breakpoint, or have your UDF return "Success" or the like just for debugging purposes. The key for now is just to figure out what the correct parameter types are. I have a feeling that if you do this you will figure out what is wrong very quickly.
Actually, I think I might see the problem:
I'm a bit suspicious of your 'Integer' parameters, which are parameter's 'A', 'D', and 'H'. If you pass an integer value into the function directly, then these parameters should work fine. But if you pass in a cell reference, then Excel will automatically pass in the Range.Value for that cell, automatically -- which is what you want...
... but the problem is that Excel cells can never hold an 'Integer' data type! They can hold "Integer" values, such as 0, 1, 2, -1, etc., of course, but these are actually typed as 'Double' when held by the cell. To prove this, you can assign an Integer to the Cell.Value, but it will be stored as a Double -- if you check the data type held by the Cell.Value via TypeName() or .GetType().ToString(), it will return "Double".
Cells can holds Booleans, Strings, Double, Date, Currency (which translates to Decimal in .NET), and CVErr values, and that's it. Under standard usage they cannot return an 'Integer'. (If directly accessing the XLOPER via a C++ XLL then you technically could access integer values, but this is a very non-standard, and you can't do this from VB.NET or C#.)
Therefore, I would focus on these Integer parameters first, particularly with respect to passing in an Integer value directly versus passing in a cell reference that holds a numeric value. I'm guessing that the non-optional 'A' parameter might be able to accept a cell reference -- but maybe not, I'm not sure -- but I'm even less sure that the optional parameters can accept a cell reference holding a Double data type.
So, overall, I'm guessing here, but I think you should be able to start with a working set of parameters and then change each parameter one by one to see which call works or fails for each parameter.
Good luck, Hugh, let us know how this goes...
Mike