Calling VBA function with XlCall.xlUDF

[Christian Conkle]

In my application, I am trying to get some intermediate data from a
VBA function stored in the worksheet. I currently do so using
Application.Run("FunctionName","parameter"). I'm trying to strip out
NetOffice and replace it with C API calls through
ExcelDna.Integration.

From prior messages in the group, it looks like I should be able to
use XlCall.TryExcel(XlCall.xlUDF, output,
"FunctionName","parameter"). This hasn't been working for me. I just
get a popup, "Cannot run the macro 'FunctionName'. The macro may not
be available in this workbook or all macros may be disabled."
TryExcel returns an XlReturnInvXloper. I tried changing the Excel
macro security to "allow all."

I include a test case below. As you can see, I also tried casting the
arguments to an obj[] explicitly; this didn't change anything. I also
tried calling XlCall.Excel instead, wondering if there was some
problem with the F# obj ref cell; the same popup appears, and of
course the diagnostic message box doesn't appear.

What am I doing wrong? How should one call a VBA function like this
using XlCall.TryExcel?

Thanks,

Christian Conkle


-- VBA module --

Function TestFunction(arg As String) As String

TestFunction = arg

End Function

-- End VBA module --


<DnaLibrary Name="FSharp Sample" Language="F#">
<Reference Path="System.Windows.Forms.dll" />
<![CDATA[
module fsExcelSample

open System.Windows.Forms
open ExcelDna.Integration

[<ExcelCommand(MenuName="Test &TryExcel", MenuText="Call UDF
TestFunction with argument SomeString")>]
let testTryExcel () =
let functionName = "TestFunction"
let functionArg = "SomeString"
let oref = ref(new obj())
let result = XlCall.TryExcel(XlCall.xlUDF, oref, functionName,
functionArg)
MessageBox.Show(
"Called UDF " + functionName +
" with argument " + functionArg +
".\nReturn code: " + (string result) +
"\nGot output: " + (string !oref) )
|> ignore

[<ExcelCommand(MenuName="Test &TryExcel", MenuText="Call UDF
TestFunction with argument SomeString using object[]")>]
let testTryExcelObjArray () =
let functionName = "TestFunction"
let functionArg = "SomeString"
let args =
[functionName :> obj; functionArg :> obj]
|> Array.ofList
let oref = ref(new obj())
let result = XlCall.TryExcel(XlCall.xlUDF, oref, args)
MessageBox.Show(
"Called UDF " + functionName +
" with argument " + functionArg +
"using a single object array argument.\nReturn code: " +
(string result) +
"\nGot output: " + (string !oref) )
|> ignore

[<ExcelCommand(MenuName="Test &Excel", MenuText="Call UDF TestFunction
with argument SomeString")>]
let testExcel () =
let functionName = "TestFunction"
let functionArg = "SomeString"
let result = XlCall.Excel(XlCall.xlUDF, functionName, functionArg)
MessageBox.Show(
"Called UDF " + functionName +
" with argument " + functionArg +
"\nGot output: " + (string result) )
|> ignore
]]>
</DnaLibrary>

[Govert van Drimmelen]

Hi Christian,

It's nice to get an F# question again!

It looks like calling VBA code from an .xll is tricky. I think this
also changed between Excel 2003 and Excel 2007, so if your add-in will
run under both version, I suggest you test both.

* If the UDF is defined in an .xll, all is fine and your call with
xlUDF always works.
* If the UDF is defined in VBA, and you are calling from a worksheet
function, it works with the plain function name.
* If the UDF is defined in VBA, and you are calling from a macro (like
in your example) you need to qualify the function name with the
workbook name. So you have something like:
let functionName = "testudf.xlsm!TestFunction"
where testudf.xlsm is the workboko where the TestFunction is defined.

With this change, all your macros work fine.

Regards,
Govert

[Christian Conkle]

Excellent.  I knew I could count on you; that appears to be the trick.
 It makes sense; the macro isn't associated with any particular
workbook, and needs to distinguish between possibly-conflicting macros
in multiple open workbooks.

I came up with this little helper to allow idiomatic F# curried
arguments:

open ExcelDna.Integration
open System

type FsXlCall = // Need to be class members to use ParamArray
    static member TryExcel (xlFunction, [<ParamArray>] args:
Object[] )=

        let oref = ref(new obj())

        let result = XlCall.TryExcel(xlFunction,oref, args)
        (result, !oref)
    static member CallUDF ([<ParamArray>] args: Object[]) =
        FsXlCall.TryExcel (XlCall.xlUDF, args)

let callUdfFromWorksheetFunction name args =
    FsXlCall.CallUDF (name, args)

let callUdfFromMacro (refInWorkbook : ExcelReference) functionName arg
=
    let sheetName = XlCall.Excel (XlCall.xlSheetNm, refInWorkbook) :?>
string
    let workbookName = XlCall.Excel(XlCall.xlfGetDocument, 88,
sheetName) :?> string
    let udfName = workbookName + "!" + functionName
    FsXlCall.CallUDF (udfName, arg)

Now I can write

["Alice"; "Bob"; "Charlie"]
|> List.map (callUdfFromMacro worksheetName "FindLastName")

to get ["Cooper"; "Marley"; "Chaplain"].  And that makes me happy.

Of course, this only handles "uncurrying" one parameter.  I think
you'd need individual functions (callUdf1, callUdf2, callUdf3...) for
each number of curried arguments.  Otherwise you can go back to using
the (Fs)XlCall functions directly.

Thanks again, Govert!

[Forna]

Hi,

I have the same problem but the solution with the workbook name does
not seem to make a difference in my case.
I created a C# method "DNAgetCurrentEarnings" that gets called from
Excel VBA. This works fine :)
Inside this method I would like to call an Excel VBA function
"testExcelFunction" (that is inside an Excel module).
This will be the Excel function responsible to write the results in
the cells.
FYI: I use this method because I would like to maintain a kind of MVC
logic where the .Net part acts as Controller and Excel as the View
(there is also an SQLite db acting as Model).

--- C# METHOD ---

public class CurrentEarnings
{
[ExcelFunction(Name = "DNAgetCurrentEarnings")]
public static string DNAgetCurrentEarnings(string sym)
{
XlCall.Excel(XlCall.xlUDF, "Earnings-ServerXMLHTTP.xls!
testExcelFunction", "Hi, I am C#!");
return sym;
}
}


--- VBA FUNCTION ---

Public Function testExcelFunction(msg As String) As Boolean
MsgBox (msg)
testExcelFunction = True
End Function


Earnings-ServerXMLHTTP.xls is the Workbook name and all macro security
is enabled.
When I call the "DNAgetCurrentEarnings" C# method from Excel I get the
following error:
"Cannot run the macro 'Earnings-ServerXMLHTTP.xls!testExcelFunction'.

The macro may not be available in this workbook or all macros may be
disabled."

I have also tried to make the call without the Workbook name, but I
get the same error "Cannot run the macro 'testExcelFunction'..."

Thank you

[Forna]

Ok I have found the problem.
Adding single quotes ' ' around the Workbook name solved the issue:

XlCall.Excel(XlCall.xlUDF, "'Earnings-ServerXMLHTTP.xls'!

testExcelFunction", "Hi, I am C#!");

I have found a more elegant solution using ThisWorkbook.Name in VBA.
In this way the Workbook can be renamed and the code still works:

1. When calling the C# method the VBA Application.Run passes the
Workbook name:
Application.Run("DNAgetCurrentEarnings", ThisWorkbook.Name, "test
string!")

2. The C# Method is executed. It takes the Workbook name as argument
to call the VBA function:

public class CurrentEarnings
{
[ExcelFunction(Name = "DNAgetCurrentEarnings")]

public static string DNAgetCurrentEarnings(string workbook,
string sym)
{
XlCall.Excel(XlCall.xlUDF, "'" + workbook + "'!

testExcelFunction", "Hi, I am C#!");
return sym;
}
}

3. The VBA function is called by the C# Method:

Public Function testExcelFunction(msg As String) As Boolean
MsgBox (msg)
testExcelFunction = True
End Function

Cheers