XlCall(XlCall.xlfReftext,...) throws XlCallException when invoked via VBA

[Thomas Baier]

My AddIn implements a COM object which exposes functions to be used in VBA code. One of the functions uses XlCall(XlCall.xlfRefText...) to convert from an ExcelReference to a Range Object.

    [ComVisible(true)]

    [ClassInterface(ClassInterfaceType.AutoDispatch)]

    [ProgId("TestInterface")]

    public class TestInterface

    {

        public int MyFunc(Excel.Range pRange)

        {

            return internalFunction(RangeToExcelReference(pRange));

        }

        private int internalFunction(ExcelReference pRef)

        {

            Excel.Application lApp = (Excel.Application)ExcelDnaUtil.Application;

            object x = XlCall.Excel(XlCall.xlfReftext, pReference, true); // throws Exception

            ...

        }

When implementing a Sheet-Function in VBA calling TestInterface.MyFunc, an XlCallException is thrown.

Of course the example above could be changed to use Excel.Range objects only, but the reall AddIn is much larger and uses ExcelReference internally for different purposes and this shall not be changed.

Marking TestInterface.MyFunc with

        [ExcelDna.Integration.ExcelFunction(IsMacroType = true)]

does not change anything.

Is there any other (easy) way to compute the Range reference string from an ExcelReference object which can be used in any context (so not using XlCall) or is it possible to synchronously use XlCall in any context?

Best wishes,

Thomas

[Govert van Drimmelen]

Hi Thomas,

You can only call the C API - XlCall.Excel(...) - from a UDF or macro in the add-in (public static void ....).

So it is not surprising that the call from VBA through COM into the add-in does not allow you to call the C API.

You could get into a context where the C API works by calling Application.Run("MyMacro"), either from VBA or from inside your COM-exposed method.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Thomas Baier [bai...@gmail.com]
Sent: 22 July 2017 01:16 PM
To: Excel-DNA
Subject: [ExcelDna] XlCall(XlCall.xlfReftext,...) throws XlCallException when invoked via VBA

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

[Thomas Baier]

Thanks Govert,

sounds reasonable, but I don't think this is a nice solution. Is there any practical way to get a Range object from an ExcelReference without using XlCall.Excel()? If not I will have to refactor my AddIn as I don't think that Application.Run() is more than a workaround.

Thomas

To post to this group, send email to exc...@googlegroups.com.

[Govert van Drimmelen]

The main issue is getting the sheet name information from the ExcelReference.

The ExcelReference contains a SheetId, which is an opaque pointer (or handle) to Excel’s internal sheet data structure.

 

The most direct way to get from a SheetId to the Workbook and Worksheet information is to use the xlSheetNm function (https://msdn.microsoft.com/en-us/library/office/bb687895.aspx):

    XlCall.Excel(xlSheetNm, reference)

 

Perhaps the xlSheetNm call is safe to make from the COM context, and that would solve your problem (you can easily build up the Range address string from there, using the rest of the ExcelReference information). There is some hope, since I’m pretty sure the function is thread-safe (for use in thread-safe UDF calculations). You’ll have to test whether it works better than xlfReftext in your context.

If xlSheetNm doesn’t work in the VBA / COM call context either, then you’ll have to manage it some other way.

 

-Govert

To post to this group, send email to exce...@googlegroups.com.

[Govert van Drimmelen]

In your initial question you ask about converting from an ExcelReference to a COM Range object.

But your code actually goes the other way, you are converting from a COM Range object that is passed from VBA into an ExcelReference structure.

You certainly won't be able to use such an ExcelReference structure in any C API (XlCall.Excel(...)) calls (including ExcelReference.GetValue()) in this VBA / COM context.

Application.Run is the right way to transition from the VBA / COM context into a macro context where you are allowed to use the C API.

(I think Application.ExecuteExcel4Macro is now considered deprecated.)

-Govert

[Thomas Baier]

Yes, I'm converting a Range to an ExcelReference, but then somewhere in the backend code, I'm using a Range object to read and write data using the Value property.

Thomas