HOWTO: Getting the row/column/workbook/worksheet from an XLL call

How can I get the row/column/workbook/worksheet that a function was called from?

Issue

I am looking for a way to retrieve the row/column and ideally also sheet name and workbook name from which an XLL call was made. I need to do this without VBA and without using the ROW/COLUMN functions in the excel cell itself (i.e. purely inside the XLL C++ function, so the C++ can call those functions if possible).

Summary

From within an XLL add-in function, you can use the function CXlOper::GetCaller(). If the function has been called from a cell, this returns 0 and fills the CXlOper with a reference to the calling cell(s).

You should also use CXlOper::IsRef() or CXlOper::IsMRef() to check that the call qwas made from a cell - add-in functions can also be called from code trfiggered by an event, a menu or a button etc.

You can then use CXlOper::GetSheetName() and CXllApp::SplitSheetName() to get and inspect the names of the book and sheet and CXlOper::GetRef() to get the cell reference within the sheet. You can also use CXlRef::Top() and CXlRef::Left() to get the column and row indices of the cell reference returned by GetRef().

Example

The function below extracts the various parts of the reference, and then puts them back together as a full cell address and returns the address as the result of the function.

// Function:    TestFunction
// Purpose:     Demonstrates the use of GetCaller
//{{XLP_SRC(TestFunction)
       // NOTE - the FunctionWizard will add and remove mapping code here.
       //    DO NOT EDIT what you see in these blocks of generated code!
#pragma region TestFunction support code
IMPLEMENT_XLLFN4(TestFunction, TestFunction_4, TestFunction_12, "RB", "UB$", 
       L"TestFunction", 0, L"x", 0, L"14", 0, L"Demonstrates the use of GetCaller",
       0, L"is a number\0", 0, 0, L"{TestFunction,,,Demonstrates the use of GetCa"
       L"ller,14,1,1,U,{{0,{x,Double,0,,is a number,,,,}}},{},3,,0,0,,,,0,0}", 1, 
       0, 0)
CXlOper* TestFunction_Impl(CXlOper&, double);
extern "C" __declspec(dllexport)
LPXLOPER12 TestFunction_12(double x)
{
       XLL_FIX_STATE;
       CXlOper xloResult;
       try {
              CXlStructuredExceptionHandler _seh_;
              xloResult.HandleResult(TestFunction_Impl(xloResult, x));
       }
       catch(const CXlRuntimeException& ex) {
              CXllApp::Instance()->DisplayException(xloResult, ex);
       }
       XLP_CATCH_CLR_EXCEPTIONS_TO(xloResult)
       return xloResult.Ret12();
}
extern "C" __declspec(dllexport)
LPXLOPER4 TestFunction_4(double x)
{ 
       XLL_FIX_STATE;
       CXlOper xloResult;
       try {
              CXlStructuredExceptionHandler _seh_;
              xloResult.HandleResult(TestFunction_Impl(xloResult, x));
       }
       catch(const CXlRuntimeException& ex) {
              CXllApp::Instance()->DisplayException(xloResult, ex);
       }
       XLP_CATCH_CLR_EXCEPTIONS_TO(xloResult)
       return xloResult.Ret4();
}
#pragma endregion
CXlOper* TestFunction_Impl(CXlOper& xloResult, double x)
{
       // End of generated code
//}}XLP_SRC
       CXlOper xloCaller;
       // Get a reference to the source of the call, and check that it is a cell reference
       if ((xloCaller.GetCaller(TRUE) == 0) && xloCaller.IsMRef())
       {
              CString strAddress;

              // Get the workbook and sheet names
              CString strBook, strSheet;
              CString strFullName = xloCaller.GetSheetName();
              CXllApp::SplitSheetName(strFullName, strBook, strSheet);
              strAddress.Format("[%s]%s!", (LPCTSTR)strBook, (LPCTSTR)strSheet);

              // Get the cell address as a string
              strAddress += xloCaller.GetRef().ToString(TRUE);

              // Return the complete address
              xloResult = strAddress;
       }
       
       return xloResult.Ret();
}