Return a range of data

Dec 20, 2011 at 4:36 PM

Hi
I am writing a function to take an excel range and return a 2-D array of data (I suppose I need to use OPER). Could you please point me to an example?
Regards,Gary

Coordinator
Dec 20, 2011 at 4:41 PM

To return a range declare a static OPERX o; o.resize(r,c); …fill in o…; return &o;

 

Declare the return type as LPOPERX WINAPI and the C signature as XLL_LPOPERX.

Dec 21, 2011 at 11:17 AM

Thank you, Keith

My test code goes like below:

static AddIn xai_regmatch( "?xll_regmatch", XLL_LPOPERX XLL_LPOPERX XLL_CSTRING_X, "XLL.REGMATCH", "Input Range, String");
LPOPERX WINAPI xll_regmatch(OPER rng, std::string exp) {
#pragma XLLEXPORT
static OPERX o;
o.Resize(1,2);
o(0,0) = 134;
o(0,1) = 1548;
return &o;
}

But I am getting an error:

Debug Assertion Failed!

Program: C:\Program Files\Microsoft Office\Office12\EXCEL.EXE
File: f:\dd\vctools\crt_bld\self_x86\crt\src\dbgdel.cpp
Line: 52

Expression: _BLOCK_TYPE_IS_VALID(pHead->nBlockUse)

For information on how your program can cause an ... 

What's wrong with my code, please?

Coordinator
Dec 21, 2011 at 11:30 AM
Edited Dec 21, 2011 at 11:30 AM

Hi Gary,

There is a mismatch between what you are telling Excel the arguments are and the actual signature of your C++ function. The return is fine, but the first arg should be an LPOPERX and the second should be TCHAR*. (Or xll::traits<XLOPERX>::xcstr) Excel does not know about std::string.

See  xll_regex_search in http://xllregex.codeplex.com/SourceControl/changeset/view/9104#135891 for something similar.

Best,
Keith

Apr 13, 2012 at 6:59 PM

Hi guys,

sorry for reopening this thread, but I have a question closely related to this problem.

I am trying to only return an array to Excel, here is a simple example:

 

LPOPERX WINAPI
MonteCarlo(double spot)
{
#pragma XLLEXPORT
    static OPERX oResult;
    oResult.resize(1,2);

    oResult(0,0)= spot;
    oResult(0,1)= spot * 2;

    return &oResult;
}

 

The code compiles but when I call the function MonteCarlo from the worksheet, I always only get the first value in the array (i.e. the value of spot). Even when I enter the formula as an array over a range of cells, they will all populate with this value.

Can you tell me how I can display the whole array in Excel please?

 

Thanks very much in advance,

TexasIntermediate

Coordinator
Apr 13, 2012 at 10:22 PM

Select one row and two columns then Ctrl-Shift-Enter.

Apr 14, 2012 at 9:52 AM

That is what I tried, but as I said in this case Excel will populate all cells with the first value (i.e. spot) ?

Coordinator
Apr 14, 2012 at 2:57 PM

Can't reproduce what you are seeing. What AddIn constructor are you using?

Apr 15, 2012 at 5:54 PM

sorry, here is the full code from the file "function.cpp". I have changed nothing in any other file after installing the your packages. I am using Visual C++ 2010 Express and Excel 2003.

#include "header.h"

using namespace xll;

static AddInX xai_function(
    FunctionX(XLL_LPOPERX, _T("?MonteCarlo"), _T("MonteCarlo"))
);

LPOPERX WINAPI
MonteCarlo(void)
{
#pragma XLLEXPORT
    static OPERX oResult;
    oResult.resize(1,2);

    oResult(0,0)= 1.00;
    oResult(0,1)= 2.00;

    return &oResult;
}

P.S.: I always get an unexpected error (error code is 2343) when trying to install the project.msi file from the website. So i used the project.msi file from an older version, "xll20110808.zip".

Coordinator
Apr 15, 2012 at 6:35 PM

Works fine for me. If you sweep out, e.g., a 3x4 range you should see something like this:

1 2 #N/A #N/A
1 2 #N/A #N/A
1 2 #N/A #N/A

Reinstall project.msi from http://kalx.net/dnload/project.zip. That problem has been fixed. It will make it a lot easier to create new add-in projects. 

Apr 15, 2012 at 8:01 PM

Hi Keith,

thanks for the fast reply. However, I still get an unexpected error (error code 2343) when trying to install project.msi from the link you mentioned above?

Thx in advance

Coordinator
Apr 15, 2012 at 8:32 PM

What OS and where is Visual Studio installed?

Apr 15, 2012 at 9:42 PM

my OS is Windows 7 Professional. Visual Studio 10 Express is installed on my main drive - however I have also Visual Studio 8 Professional on the same drive installed...

Coordinator
Apr 15, 2012 at 11:03 PM

64 or 32 bit? What directory is VS10 installed in? Also, what is the output of 'set programfiles' in a command prompt? Thanks for helping me get to the bottom of thiis. I'll get it working.

Apr 16, 2012 at 5:28 PM

Hi again, thanks for your help.

I am running a 32-bit operating system. VS10 is installed in: "C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\VCExpress.exe"

The output for 'set programfiles' in cmd is 'ProgramFiles=C:\Program Files'.

As i said, with the project.msi of the older version "xll20110808.zip" I get no errors..

Apr 24, 2012 at 5:43 PM

Hi again,

any news on this issue? I tried to install the project.msi on a different computer with similar setup - this also gives me the code 2343 error..

Many thanks in advance!

Coordinator
Apr 24, 2012 at 8:29 PM

I have updated both http://kalx.net/dnload/setup.zip and http://kalx.net/dnload/project.zip and tested them on your setup. Let me know if you still have problems.

I recommend uninstalling xll and xllproject first using Control Panel.

Apr 26, 2012 at 12:00 PM

Hi Keith,

sorry but I still get the same error for the project.msi. I uninstalled all version of VC, installed Visual C++ 2010 Express again, and it still does not work.

Do you have any idea what actions are left for me to take? If you need more info, please let me know!

Thanks!

Coordinator
Apr 26, 2012 at 6:03 PM

Try this: http://kalx.net/dnload/project2.zip Thanks for your patience on this problem.

Apr 28, 2012 at 7:32 PM

thanks for the file, it's working fine now :)

Sep 1, 2012 at 12:18 AM

Hello Keith,

Thanks for the wonderful XLL add-in.

Yes, project2.zip only worked with my computers one in vista 32, and another window 7 64 bit. 

From the above discussion, I learnt about sending a matrix from C++ to Excel. Now, I wish to know receive a free range matrix from Excel to C++.

Thanks!

 

Coordinator
Sep 1, 2012 at 4:22 AM

That is a bit of a trick. Excel does not have facilities for that. If you use adjust.xll you can right click on the output of an array formula and select Adjust to have your matrix show up. You can find the add-in at http://sdrv.ms/JtaMIV

Sep 1, 2012 at 12:32 PM

Thanks, the "adjust.xll" is a wonderful tool, it puts matrix in the Excel worksheet with out using ctrl-shift-enter and NO HASHES.

Now the problem is how to send Excel matrix in C++ argument?

that is I am looking for something like:

LPOPERX WINAPI  A_Matrix_Function( Excel_Matrix<T> input_matrix )

{

size_t input_rows = input_matrix.rows();

size_t input_columns = input_matrix.columns();

.........

.........

return &o;

}

Please give some light.

Coordinator
Sep 1, 2012 at 3:23 PM

That would be the FP data type: http://xll.codeplex.com/wikipage?title=FP.

Sep 1, 2012 at 11:42 PM

Thanks Keith.

I tried a sample, it builds xll, but Excel crashes with this function. Could you  please highlight the problem in the code:

// matrix1.cpp
#include "header.h"
using namespace xll;
static AddIn xai_array_unique(
    FunctionX(XLL_FP, _T("?xll_array_unique"), _T("xll_array_unique") )
    .Arg(XLL_DOUBLEX, _T("ARRAY.UNIQUE"), _T("gets unique"))
);

_FP* WINAPI
xll_array_unique(const _FP* pa)
{
#pragma XLLEXPORT
    static xll::FP a;
         // Copy what pa points at to a
    a = *pa;
         // STL function to remove duplicates
    double* pe = std::unique(a.begin(), a.end());
         // Size of non-duplicates
    a.reshape(static_cast<WORD>(pe - a.begin()), 1);
    return a.get();
}

Thanks.

Coordinator
Sep 2, 2012 at 12:31 AM
Edited Sep 2, 2012 at 12:34 AM
static AddIn xai_array_unique(
    // return type, C name, Excel name
    Function(XLL_FP, _T("?xll_array_unique"), _T("ARRAY.UNIQUE"))
    // argument type, name, description
    .Arg(XLL_FP, _T("Array"), _T("is an array. "))
);

You are telling Excel the argument type is a double instead of an FP.

Sep 2, 2012 at 3:06 AM

Thanks, it works now. I am more than satisfied with your software. Superb!

Thanks!

Apr 3, 2013 at 11:59 PM
Dear Keith,
Hello, how are you.

I have a problem, that is the adjust.xll says it has expired, could you please help.

Thanks and regards,
Pattabi
Apr 4, 2013 at 2:37 AM
Dear Keith,
How can I use macro.h, because it compiles. How to transfer characters and strings from excel to c++ and then c++ to excel.
Thanks and regards,
Pattabi