Skip to main content

Accepting Range Parameters in UDFs

Parameters with the type of Excel's Range COM object are not directly supported by Excel-DNA. There is a list of allowed parameter types here: Reference for data types in UDFs

If you want the function to also accept a sheet reference, your parameter should be of type 'object' and marked with an <ExcelArgument(AllowReference:=true)> attribute. In that case you'll get an object of type ExcelDna.Integration.ExcelReference if the function is called with a sheet reference.

ExcelReference is not the same as the COM Range type, it is a small wrapper type for the Excel C API reference structure. From the ExcelReference it is possible to get a COM Range -

Imports ExcelDna.Integration.XlCall
Private Function ReferenceToRange(ByVal xlRef As ExcelReference) As Object
Dim cntRef As Long, strText As String, strAddress As String
strAddress = Excel(xlfReftext, xlRef.InnerReferences(0), True)
For cntRef = 1 To xlRef.InnerReferences.Count - 1
strText = Excel(xlfReftext, xlRef.InnerReferences(cntRef), True)
strAddress = strAddress & "," & Mid(strText, strText.LastIndexOf("!") + 2) ' +2 because IndexOf starts at 0
ReferenceToRange = ExcelDnaUtil.Application.Range(strAddress)
End Function

The internal xlfReftext call in ReferenceToRange can only be made from functions that are registered as a macro-sheet functions. For this the exported function will need to be marked as IsMacroType:=True.

So a function that can accept a sheet reference, and process these as a COM Range object, might look like this:

<ExcelFunction(IsMacroType:=True)> _
Public Shared Function GetAddress(<ExcelArgument(AllowReference:=true)> ByVal arg As Object) As String
Dim range As Object
If TypeOf arg Is ExcelReference Then
range = ReferenceToRange(arg)
Return range.Address(False, False)
Return "!!! Not a sheet reference"
End If
End Function