Thread-safe XLL. How to get the caller address

Posted on Friday, October 7th, 2011 at 5:28 am by Andrei Smolin.

The implementation of ADXExcelRef.ConvertToA1Style (ConvertToR1C1Style) uses xlfRefText which is NOT thread-safe as per Financial Applications Using Excel Add-in Development in C/C++ (2nd edition). On the other hand, xlSheetNm returning the sheet name is thread-safe. It means that the thread-safe way to get the caller address is to write some code. Here is an attempt to write such code.

C#:

public static string GetCallerAddress(bool A1Style)
{
    ADXExcelRef caller =
        Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Caller) as ADXExcelRef;
    ADXExcelWorksheetFunction xlSheetNm = (ADXExcelWorksheetFunction)16389;
    string bookAndSheet = Module.CallWorksheetFunction(xlSheetNm, caller).ToString();
    int rowFirst = caller.RowFirst + 1;
    int rowLast = caller.RowLast + 1;
    int columnFirst = caller.ColumnFirst + 1;
    int columnLast = caller.ColumnLast + 1;
    bool isOneCell = (rowFirst == rowLast && columnFirst == columnLast);
    string cellAddress = "";
    if (A1Style)
    {
        cellAddress = "!" + GetExcelColumnName(columnFirst) + rowFirst.ToString();
        if (!isOneCell)
            cellAddress += ":" + GetExcelColumnName(columnLast) + rowLast.ToString();
    }
    else
    {
        cellAddress = "!R" + rowFirst.ToString() + "C" + columnFirst.ToString();
        if (!isOneCell)
            cellAddress += ":R" + rowLast.ToString() + "C" + columnLast.ToString();
    }
    return bookAndSheet + cellAddress;
}

VB.NET:

Public Shared Function GetCallerAddress(A1Style As Boolean) As String
    Dim caller As ADXExcelRef = _
        TryCast( _
            _Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Caller), _
            ADXExcelRef)
    Dim xlSheetNm As ADXExcelWorksheetFunction = _
        DirectCast(16389, ADXExcelWorksheetFunction)
    Dim bookAndSheet As String = _
        _Module.CallWorksheetFunction(xlSheetNm, caller).ToString()
    Dim rowFirst As Integer = caller.RowFirst + 1
    Dim rowLast As Integer = caller.RowLast + 1
    Dim columnFirst As Integer = caller.ColumnFirst + 1
    Dim columnLast As Integer = caller.ColumnLast + 1
    Dim isOneCell As Boolean = (rowFirst = rowLast AndAlso columnFirst = columnLast)
    Dim cellAddress As String = ""
    If A1Style Then
        cellAddress = "!" & GetExcelColumnName(columnFirst) & rowFirst.ToString()
        If Not isOneCell Then
            cellAddress += ":" & GetExcelColumnName(columnLast) & rowLast.ToString()
        End If
    Else
        cellAddress = "!R" & rowFirst.ToString() & "C" & columnFirst.ToString()
        If Not isOneCell Then
            cellAddress += ":R" & rowLast.ToString() & "C" & columnLast.ToString()
        End If
    End If
    Return bookAndSheet & cellAddress
End Function

This method returns a string of this type: “[Book1]Sheet1!A1”.

Now, let’s walk through the code.

As you see, 16389 is the identifier of the xlSheetNm function. Calling it creates no problems.

As to getting the cell part of the caller address, please keep in mind that in the Excel UI, rows & columns are counted starting from one but internally, they are zero-based.

The only remaining problem is to convert the column number to an Excel column name, e.g. “AD” or “AAD”, so that to comply with limits of various Excel versions: Excel 2000-2003 provides 256 columns (“A” through “IV”), while in Excel 2007-2010, there are 16384 columns (from “A” to “XFD”). This is what the GetExcelColumnName method is purposed for. You can find its code in the sample projects available for download at the end of this post.

Good luck!

Available downloads:

This sample XLL UDF was developed using Add-in Express for Office and .net:

C# sample XLL add-in
VB.NET sample XLL add-in

You may also be interested in:

Creating Excel XLL add-ins
Excel UDF tips and tricks

.NET, C#, Excel, Office, VB.NET, XLL
Filed under Add-in Express for Office and .net

8 Comments

Henri Pellemans says:

October 8, 2011 at 2:14 am

Hi Andrei, the sample UDFs both contain the C# version. So the VB.NET version of MyXLLAddin14.zip is missing.

However, thank you for showing the blog in VB.NET too.

Best regards,

Henri Pellemans
Andrei Smolin (Add-in Express Team) says:

October 10, 2011 at 3:56 am

Hi Henri,

It was a wrong link in case of the C# version. Sorry for this. It is corrected now.
XL-Dennis says:

October 27, 2011 at 4:53 pm

Andrei,

This is an interesting post and thanks for sharing it. Has it been tested extensive?

All the best,
Dennis
Andrei Smolin (Add-in Express Team) says:

October 28, 2011 at 5:00 am

Hello Dennis,

No, it wasn’t.

Glad to see you here again :)
Pierre Alain Carrier says:

January 8, 2013 at 4:07 pm

This was working fine until lately. Now, it always returns the active sheet.

Could it be an office update? Can someone confirm this? I’m using Office 2010.
Andrei Smolin (Add-in Express Team) says:

January 9, 2013 at 5:01 am

Hello Pierre Alain,

This problem was introduced recently. It is now fixed. Install the latest build (which is 7.1.4050).
Adam says:

July 31, 2015 at 9:04 am

Hi,

This may be slightly off topic but it is related to CallWorksheetFunction.

I currently have this code in a UDF implementation as a first step to determine the calling cell’s address:

ADXExcelRef caller = Module.CallWorksheetFunction(ADXExcelWorkseetFunction.Caller) as ADXExcelRef;

Everything works great but in VS IDE I see that this member is obsolete and to use another variant of this member. What the current equivalent to this member? It is not clear from the message.

Thanks.

Adam
Andrei Smolin (Add-in Express Team) says:

July 31, 2015 at 9:57 am

Hello Adam,

It was a silly mistype: ADXExcelWorkseetFunction vs. ADXExcelWorksheetFunction. The first one is obsolete.

Thread-safe XLL. How to get the caller address

Available downloads:

You may also be interested in:

8 Comments

Post a comment

Have any questions? Ask us right now!