Excel Trusted Publisher Setting and Automation Add-in UDFs

Add-in Express™ Support Service
That's what is more important than anything else

Excel Trusted Publisher Setting and Automation Add-in UDFs
Requiring Trusted Publisher Causes Automation UDF #NAME? error. 
William Hughes


Guest


We apparently have run into a similar issue described in the article.

https://www.add-in-express.com/forum/read.php?FID=5&TID=14428

We have an automation add-in with UDF's. Apparently more companies are using the Trust Center signed add-in requirement leading to the #Name error with the UDFs. Just signing the adxloader files does not correct the problem. We are not employing an XLL add-in because of a requirement for parameter arrays.

Any further information on dealing with this would be appreciated.
Posted 27 Apr, 2017 14:49:48 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Hello William,

I need time to prepare a complete answer. I'll try to provide it next week.

Could you please show an example of a function supporting parameter arrays? What programming language do you use?


Andrei Smolin
Add-in Express Team Leader
Posted 28 Apr, 2017 11:01:52 Top
William Hughes


Guest


Andrei,

Thanks for your reply,

An example of a UDF formula with a parameter array would be a distance formula that supports a variable number of destinations.

=Distance(Destination1,Destination2,Destination3)

where Destination is a parameter array.

We are using VB.NET for our automation UDF module.
Posted 28 Apr, 2017 11:37:13 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Hello William,

Would declaring an XLL UDF with a fixed number of parameters (say, 32 or 255) and defining a required number of parameters at the run time work for you?


Andrei Smolin
Add-in Express Team Leader
Posted 03 May, 2017 08:39:45 Top
William Hughes


Guest


Andrei,

Allowing 255 parameters would probably cover the majority of cases. Could you provide an example of what you are proposing?
Posted 03 May, 2017 13:51:33 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Hello William, 

        Public Shared Function MyFunction(ByVal arg0 As Object,
                ByVal arg1 As Object,
                ByVal arg2 As Object,
                ByVal arg3 As Object,
                ByVal arg4 As Object,
                ByVal arg5 As Object,
                ByVal arg6 As Object,
                ByVal arg7 As Object,
                ByVal arg8 As Object,
                ByVal arg9 As Object,
                ByVal arg10 As Object,
                ByVal arg11 As Object,
                ByVal arg12 As Object,
                ByVal arg13 As Object,
                ByVal arg14 As Object,
                ByVal arg15 As Object,
                ByVal arg16 As Object,
                ByVal arg17 As Object,
                ByVal arg18 As Object,
                ByVal arg19 As Object,
                ByVal arg20 As Object,
                ByVal arg21 As Object,
                ByVal arg22 As Object,
                ByVal arg23 As Object,
                ByVal arg24 As Object,
                ByVal arg25 As Object,
                ByVal arg26 As Object,
                ByVal arg27 As Object,
                ByVal arg28 As Object,
                ByVal arg29 As Object,
                ByVal arg30 As Object) As String
            Dim parameters(30) As Object
            parameters(0) = arg0
            parameters(1) = arg1
            parameters(2) = arg2
            parameters(3) = arg3
            parameters(4) = arg4
            parameters(5) = arg5
            parameters(6) = arg6
            parameters(7) = arg7
            parameters(8) = arg8
            parameters(9) = arg9
            parameters(10) = arg10
            parameters(11) = arg11
            parameters(12) = arg12
            parameters(13) = arg13
            parameters(14) = arg14
            parameters(15) = arg15
            parameters(16) = arg16
            parameters(17) = arg17
            parameters(18) = arg18
            parameters(19) = arg19
            parameters(20) = arg20
            parameters(21) = arg21
            parameters(22) = arg22
            parameters(23) = arg23
            parameters(24) = arg24
            parameters(25) = arg25
            parameters(26) = arg26
            parameters(27) = arg27
            parameters(28) = arg28
            parameters(29) = arg29
            parameters(30) = arg30
            Dim resultString As String = "result: "
            For iParam As Integer = 0 To 30
                Dim arg As Object = parameters(iParam)
                If (TypeOf arg Is System.Reflection.Missing) Then
                    resultString += "arg" + CStr(iParam) + "=" + "Missing;"
                ElseIf (arg Is Nothing) Then
                    resultString += "arg" + CStr(iParam) + "=" + "Empty;"
                Else
                    resultString += "arg" + CStr(iParam) + "=" + arg.ToString() + ";"
                End If
            Next

            Return resultString
        End Function


I've used the Word VBA macro below to create the list of arguments. A created a similar macro for the list of parameters(X) = argX assignments. 

Sub fdgdfgd()
Dim count As Integer
count = 0

Dim sel As Word.Selection
Do While count < 31
    Set sel = Application.Selection
    sel.Text = "ByVal arg" + CStr(count) + " As Object," + vbCrLf
    count = count + 1
    sel.Collapse WdCollapseDirection.wdCollapseEnd
    'Stop
Loop
End Sub



Andrei Smolin
Add-in Express Team Leader
Posted 04 May, 2017 07:18:03 Top
William Hughes


Guest


Andrei,

Thanks. I'll give it a try.
Posted 05 May, 2017 16:52:09 Top