ParamArray issue with ExcelDNA Add-In DLL for VBA automation

[Alberto Ghiglia]

Hi Govert

I've written a simple VB.NET DLL (tlb file) to automate VBA but I have problems with Paramarray, that seem not to be supported by VBA 

Here you are the example, that gives the following error on the .UnionRange call:

"Compile Error: the function or interface have restrictions or the automation is not supported in visual basic "

Could you pls, shed light on this issue?

Thanks

Best regards

Alberto

VB.NET (Add-in)

Public Function UnionRange(ParamArray CellRanges() As Range) As Range
 

    Dim ExcelApp As Microsoft.Office.Interop.Excel.Application
    ExcelApp = ExcelDnaUtil.Application
 
    Dim FinalRange As Range = Nothing
 
    For Each R As Range In CellRanges
        FinalRange = ExcelApp.Union(FinalRange, R)
    Next
 
    Return FinalRange
 
End Function

VBA Sub that calls ExcelDNA Add-In

Sub UNO()
    Dim A As Range, B As Range, C As Range
  Set A = Range("A1:A5")
    Set B = Range("H18:Z18")
    
    
    Set C = VBA.UnionRange(A, B)
    MsgBox C.Address
    
End Sub

[Govert van Drimmelen]

It could be that ParamArray is not supported by the .NET / COM interop.

Just make the parameter an array, and always pass it that way from VBA.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Alberto Ghiglia]

Hi Govert,

Thanks!

I will work it around

Alberto