XLL Only Works on Specific Excel Worksheet Despite Fully Qualified Range Parameters

Question

I have an XLL file (using the xll.codeplex.com library) with the following code:

xll_rootfinder(TCHAR* x, TCHAR* y, const double min, const double max)
{
#pragma XLLEXPORT
    try {
            ExcelX(xlcCalculateDocument);
            be = ExcelX(xlfTextref, OPERX(x), OPERX(true));
            nav = ExcelX(xlfTextref, OPERX(y), OPERX(true));
            typedef std::pair<double, double> Result;
            boost::uintmax_t max_iter=50;
            boost::math::tools::eps_tolerance<double> tol(5);
            Result r1 = boost::math::tools::toms748_solve(recalc, min, max, tol, max_iter);
    }

This function is then called repeatedly in a VBA sub. The parameters x and y are range addresses such as 'XX'!B42. The worksheet name is dynamic. The VBA routine activates a specific worksheet and then does calculations using the XLL function on that worksheet. If I run the VBA sub manually, while starting on the correct worksheet, everything will work (until it needs to switch worksheets). So I don't believe this is a C++ problem. If I start the sub from any other worksheet, Excel will crash as soon as it hits the first XLL call:

 Call Application.Run("ROOTFINDER", PriceAddress, NAVAddress, 45, 500)

There is no reference to ThisWorksheet or ActiveWorksheet in my code. I have set breakpoints and made sure the worksheet variable is set properly.

Why is it that the XLL seems to have a problem accessing the newly activated sheet despite given the proper range addresses including the sheet name?

Much appreciated.

Keith A. Lewis Keith A. Lewis · Accepted Answer · 2013-07-12T04:01:47

Instead of TCHAR* use XLOPER. Excel will do the work for you. All you have to do is declare the argument as XLL_XLOPER instead of XLL_OPER to get your hands on the range instead of the values. Or maybe I'm not understanding your real problem. TOMS 748. Why drag boost into this? Just call Fortran directly. Intel has a highly optimized library for that. Have a look at the xllblas or xlllapack projects on codeplex and let me know what you think.

XLL Only Works on Specific Excel Worksheet Despite Fully Qualified Range Parameters

1 Answers