Newbie question

Sep 1, 2013 at 11:31 PM
I've followed the Getting Started examples and all worked okay. So I've tried to get the following VBA code working
Public Function LastRow(ByVal rng As Range) As Long

    LastRow = rng.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

End Function
using
<DnaLibrary RuntimeVersion="v4.0">
<![CDATA[

    Public Module Module1
            Public Function LastRow(ByVal rng As Range) As Long

                LastRow = rng.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

        End Function    
    End Module

]]>
</DnaLibrary>
However, this produces errors. So I've attempted to port to VB.NET using
<DnaLibrary RuntimeVersion="v4.0">
<![CDATA[

    Public Module Module1
            Public Function LastRow(ByVal rng As Microsoft.Office.Interop.Excel.Range) As Long

                LastRow = rng.Find(What:="*",                                       SearchDirection:=Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious,                       SearchOrder:=Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows).Row

            End Function    
    End Module

]]>
</DnaLibrary>
again, this is not working. Please can I have a few hints on what I'm doing wrong :)

Thanks
Q
Coordinator
Sep 2, 2013 at 11:33 AM
Edited Sep 2, 2013 at 11:33 AM
Hi Q,

Parameters of the COM 'Range' type are not supported by the Excel C API which Excel-DNA uses. So your function is not being registered at the moment.

For most cases you probably just want the array of values that the range contains, so you'd just set the type to Object(,).
Otherwise you can get an 'ExcelReference' which is a thin wrapper aroung the C API sheet reference, and which can then be converted to a Range COM object. See https://exceldna.codeplex.com/wikipage?title=Range%20Parameters&referringTitle=Documentation.

For converting from VBA to Excel-DNA, you might find this guide helpful: http://sysmod.wordpress.com/2012/11/06/migrating-an-excel-vba-add-in-to-a-vb-net-xll-with-excel-dna-update/

Regards,
Govert