How do I figure out why an UDF does not register properly in Excel ?

[Clifton Liu]

Hi,

I created an Excel-DNA xll and it seems to be loaded just fine by Excel. But the UDF was not registered since I can't see it in the formula wizard. How do I trouble shoot stuff like this ?

Below is the DNA file.

<DnaLibrary RuntimeVersion="v4.0" Name="Application Events Test" Language="VB" >

<Reference Name="Microsoft.Office.Interop.Excel" />

<Reference Name="Office" />

<![CDATA[

Imports Microsoft.Office.Interop.Excel

Imports Microsoft.Office.Core

Imports ExcelDna.Integration.XlCall

Public Module MyFunctions

    Public Sub getParameters(inputMtx As Object(,), _

                            ByRef MaxLTVSharePrice As Double, _

                            ByRef MaxLoanAmt As Double, _

                            ByRef ADTV As Double, _

                            ByRef BlockSaleDiscount As Double, _

                            ByRef BlockSaleDiscountFlag As Boolean, _

                            ByRef BlockSaleMultiplier As Double, _

                            ByRef numCollateralShares As Double)

        MaxLTVSharePrice = inputMtx(0, 0)

        MaxLoanAmt = inputMtx(1, 0)

        ADTV = inputMtx(2, 0)

        BlockSaleDiscount = inputMtx(3, 0)

        BlockSaleDiscountFlag = inputMtx(4, 0)

        BlockSaleMultiplier = inputMtx(5, 0)

        numCollateralShares = inputMtx(6, 0)

    End Sub

    <ExcelFunction(Description:="liquidationPnL", IsThreadSafe:=True, Category:="Useful Excel-DNA Functions")>

    Public Function liquidationPnL(inputMtx As Object(,), path As Double(,)) As Object

        Dim MaxLTVSharePrice As Double

        Dim MaxLoanAmt As Double

        Dim ADTV As Double

        Dim BlockSaleDiscount As Double

        Dim BlockSaleDiscountFlag As Boolean

        Dim BlockSaleMultiplier As Double

        Dim numCollateralShares As Double

        Dim nSharesLeft As Long

        Dim totalDollarSaleValue As Double

        Dim S(,) As Double

        Dim nDays As Integer

        Dim t As Integer

        Call getParameters(inputMtx, _

                            MaxLTVSharePrice, _

                            MaxLoanAmt, _

                            ADTV, _

                            BlockSaleDiscount, _

                            BlockSaleDiscountFlag, _

                            BlockSaleMultiplier, _

                            numCollateralShares)

        S = path

        nDays = path.Length

        nSharesLeft = numCollateralShares

        totalDollarSaleValue = 0

        For t = 1 To nDays

            Dim nSharesThisDay As Double

            Dim saleDollarValueThisDay As Double

            nSharesThisDay = XlCall.Excel(XlCall.xlfMin, BlockSaleMultiplier * ADTV, nSharesLeft)

            If BlockSaleDiscountFlag = False And t <> 1 Then

                saleDollarValueThisDay = S(0, t - 1) * nSharesThisDay

            Else

                saleDollarValueThisDay = S(0, t - 1) * (1 - BlockSaleDiscount) * nSharesThisDay

            End If

            totalDollarSaleValue = totalDollarSaleValue + saleDollarValueThisDay

            nSharesLeft = nSharesLeft - nSharesThisDay

            If nSharesLeft = 0 Then

                Exit For

            End If

        Next t

        If nSharesLeft > 0 Then

            liquidationPnL = "Error: more paths!"

        Else

            liquidationPnL = XlCall.Excel(XlCall.xlfMin, totalDollarSaleValue - MaxLoanAmt, 0)

        End If

    End Function

End Module

]]>

</DnaLibrary>

Thanks,

Cliff

[Clifton Liu]

I think I know what's wrong, but I don't know what is the right solution.

  <ExcelFunction(Description:="liquidationPnL", IsThreadSafe:=True, Category:="Useful Excel-DNA Functions")>

    Public Function liquidationPnL(inputMtx As Object(,), path As Double(,)) As Object

....

The argument types don't seem to be supported ( I was using Range before and it did not work either)

Public Function liquidationPnL(inputMtx As Range, path As Range) As Object

[Govert van Drimmelen]

Hi Cliff,

What problem do you have with the originla .dna file you posted?

When I try it, the liquidationPnL function is available in Excel (though I get #VALUE - you might need to post some sensible input values too, for me to try).

The signature and types are fine.

Certainly Range parameters won't work without some extra help - they are supported with the ExcelDna.Registration.VisualBasic helper library though.

But if you just need the values, using Object(,) and Double(,) is much better.

I'd also suggest you replace the xlfMin calls to the C API with your own helper - it should be faster.

If you are not seeing the function, and also no errors when loading the .xll, you might have security settings that block add-ins. Look out for the "Registering ...." message in the Excel status bar when you File->Open the .xll file. That indicates the add-in is indeed being loaded.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Clifton Liu [clift...@gmail.com]
Sent: 24 July 2015 10:40 PM
To: Excel-DNA
Cc: clift...@gmail.com
Subject: [ExcelDna] Re: How do I figure out why an UDF does not register properly in Excel ?

--
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 http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Clifton Liu]

Hi Govert,

Thanks for replying so quickly. I was simply trying to turn this particular VBA function into a thread safe function since this is being run by Excel 20,000 times in a Monte Carlo simulation. I also tried to not to alter this function too much in its original form since this code is being maintained by another person and it will likely change in VBA form.

Unfortunately I don't think it's the security setting because as soon as I took out the arguments and had just

 Public Function liquidationPnL() as Double, the it was registered fine (ie I can see it in the function list)

Note that I could see the status bar "Registering ...." also using the original function signature, but I could not see it in the function list.

On Friday, July 24, 2015 at 4:01:31 PM UTC-4, Clifton Liu wrote:

[Clifton Liu]

Hi,

I spoke too soon. I must have packed the wrong version. I can see the function now.

Thanks,

Cliff

[Govert van Drimmelen]

Cool - I'm glad it's working now.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Clifton Liu [clift...@gmail.com]

Sent: 25 July 2015 09:07 AM

To: Excel-DNA
Cc: clift...@gmail.com
Subject: [ExcelDna] Re: How do I figure out why an UDF does not register properly in Excel ?

--