Call/Run VBA sub from ExcelFunction written in C#

Mar 10, 2014 at 4:00 PM
Edited Mar 10, 2014 at 4:43 PM
Hi there,

I have a rather simple issue that I cannot figure out:
I want to write a UDF using Excel-DNA that is called from Excel and which is written in C#. Inside this UDF (which is declared as IsMacroType = true) I have to get a value from a VBA sub procedure (a boolean) that is in a module in the same Excel workbook.

I know I can run my UDF written in C# from an Excel workbook by typing in a cell: =UDFname()
and to call the same UDF from VBA (possibly inside a sub procedure) I use Application.Run("UDFname",...)

However, I want to run/call a sub that is in a module in the same Excel Workbook. I tried using: XlCall.Excel(XlCall.xlUDF, "SubIWantToCall") but this doesn't seem to work.

Any help is apprediated.
Thanks
NTK

EDIT1:
I tried: bool test = (bool)XlCall.Excel(XlCall.xlcRun, "VBAFunctionName"); but keep on getting this error:
"A first chance exception of type 'ExcelDna.Integration.XlCallException' occurred in ExcelDna.Integration.dll"

EDIT2:
Calling a sub (like a macro) without a return type would be strange to be called within a UDF I guess. So I should clarify that my VBA function I want to call from C# is:
Function IsDebug() As Boolean
IsDebug = True
End Function
Mar 10, 2014 at 4:57 PM
Now I understand the hint from: https://groups.google.com/forum/#!msg/exceldna/HVhMVmVik-s/V7OwXRV6boAJ

I can access my VBA function within C# like that: bool)XlCall.Excel(XlCall.xlUDF, "'WorkbookName'!IsDebug");
" ' Workbookname ' ! VBAFunction
And to get the Workbook name I have to call
string name = (string)XlCall.Excel(XlCall.xlfGetDocument, 88);
Coordinator
Mar 10, 2014 at 8:24 PM
Hi,

It sounds like you're making things very difficult (or you've been forced into a complicated position).
Can't you move the VBA stuff to VB.NET?
Or use some other way, like a COM server, to couple the Excel-DNA add-in and worksheet code?

Otherwise, you might give xlfEvaluate (http://msdn.microsoft.com/en-us/library/office/bb687913(v=office.15).aspx) a try, instead of trying to use Application.Run from within the UDF. That would seem to execute in a context similar to a worksheet formula, so might work from inside the add-in UDF.

-Govert
Mar 10, 2014 at 8:59 PM
Hi govert,

thanks for your post. There's a huge collection of functions in VBA that I need to transfer to C# step by step. So, these functions sometimes get information from another VBA function and I don't want to transfer everything at once.

What do you mean by COM server? Is this the same as using COM Interop, like xlApp = Excel.Application, xlSheet = xlApp.Worksheet("Name")? The way I would use it in VBA from another Office application? I tried to avoid using anything from Microsoft.Office.Excel.Interop in C# and just use ExcelDna, because it's realy neat and I just want to call a VBA function from my UDF.

Thanks for the tip using xlfEvaluate. I'll give this a try.
Mar 11, 2014 at 8:35 AM
I don't want to change the subject but it's not necessary to do this in an ExcelFunction, I could also do it using an ExcelCommand. Does this simplify anything?
Mar 11, 2014 at 9:13 AM
Thanks to govert, I tried xlfEvaluate, but I'm not sure which version is better, rsp. faster:
string name = (string)XlCall.Excel(XlCall.xlfGetDocument, 88);
bool test4 = (bool)XlCall.Excel(XlCall.xlfEvaluate, "!IsDebug()");
bool test5 = (bool)XlCall.Excel(XlCall.xlfEvaluate, "'" + name +"'!IsDebug()");
Using the ! is also required the get values of named ranges on the whole workbook and Sheet!Name if it's a named range on a specific sheet.
Coordinator
Mar 11, 2014 at 10:28 AM
You might consider moving those functions to VB.NET. You'd need to make very few changes to the code to recompile to a .NET library, and the functions might be a lot faster. That's much easier that re-writing in C#. Check Patrick O'Beirne's guide for some idea of the few problems to look out for.

I mean if you're in a macro (an ExcelCommand) you should have access to the COM Automation object model with no hassles. So Application.Run and everything should work as-if you're in a VBA add-in. Being in a function called from a worksheet formula restricts what you can do quite a bit (which is why you get the XlCallException when calling some C API functions.

-Govert
Mar 11, 2014 at 10:45 AM
Ok, thanks again. I read Patrick O'Beirne's guide and it helped me a lot to sort my thoughts. I'll try ExcelCommand and using PIA.