ActiveWorkbook vs ThisWorkbook how to get the right one in UDF

Jun 2, 2015 at 4:52 PM
Hi,

My ExcelDna AddIn crashes, if I try to access Application.ThisWorkbook or Application.ThisCell with COM-Error.

How can I access the current workbook, the UserFunction is called for??

Application.ActiveWorkbook is only the solution, if only one workbook is loaded, if I have more than one workbook loaded, than it returns the "active" one, not the This one.
So if the Application recalculates all workbooks,(Shift Ctrl F9) or something is done in the background, this is not the correct one!???

Govert, please help and explain how to get the workbook for the calling userfunction if more than one workbook is loaded into the Excel application.

Application.ThisCell.Parent.Parent should normally return the correct one, but I can not use it, because ThisCell raises the COM Exception.

Tnx

Roman
Coordinator
Jun 2, 2015 at 5:44 PM
Hi Roman,

It's not clear what you are trying to do.
  • Is this in a macro, or in a UDF?
  • Do you want Application.ThisCell, or Application.Caller?
  • What do you want to do with the information you get? Remember you can't do anything with the Range object inside the UDF itself - you can just store it and use it later in a macro or something.
In a UDF you would use the C API to get an ExcelReference with the information of the caller. From there, it depends on what you want to do with the information...
Public Module MyFunctions

    Public Function MyFunction(...) As Object
        Dim callingRef As ExcelReference
        callingRef = XlCall.Excel(XlCall.xlfCaller);

        ' callingRef is a C API type ExcelReference. It can be converted to a COM Range object like this:
        Dim caller As Range = ReferenceToRange(callingRef)

        ' The rest of your function here

    End Function

    Function ReferenceToRange(ByVal xlInput As Object) As Range

        Dim xlRef As ExcelReference = xlInput   ' Will throw some Exception if not valid, which will be returned as #VALUE

        Dim cntRef As Long
        Dim strText As String
        Dim strAddress As String

        strAddress = XlCall.Excel(XlCall.xlfReftext, xlRef.InnerReferences(0), True)
        For cntRef = 1 To xlRef.InnerReferences.Count - 1
            strText = XlCall.Excel(XlCall.xlfReftext, xlRef.InnerReferences(cntRef), True)
            strAddress = strAddress & "," & Mid(strText, strText.LastIndexOf("!") + 2) ' +2 because IndexOf starts at 0
        Next
        ReferenceToRange = CType(ExcelDnaUtil.Application, Application).Range(strAddress)
    End Function

End Module
-Govert
Jun 2, 2015 at 6:35 PM
Hi Govert,

tnx 4 ur quick answer!

I have written an ExcelAddIn with Userdefined functions and packed all together to an XLL.
This works great, as long as I have only one workbook loaded..

In my workbooks are existing two "wellknown" named ranges named "Config1" and "Config2",
each of this ranges contains a lot of config stuff.

Many of my UDF's will use this config stuff, but they do not! reference to this two ranges as parameters.
this is not handy.
Instead the UDF code reads now the ranges with access to Application.Names, but this is not correct, this gives me the
ranges from the ActiveWorkbook.Names.

How to solve this?
(Appplication.ThisWorkbook.Names should return the correct one??)

I will try your solution!

Can you post a c# version?


Roman