XLL Macro - read/set Range .Value and .Interior.ColorIndex

Feb 21, 2013 at 6:56 PM
Hello all,
I am trying to enter to Excel C API. I am using for years macros from COM add in written in .net (dll file).
Problem for me is the speed in loading range's data, means .Value and .Interior.ColorIndex into an array for example.
I would like to ask:
  1. how to access in xll code bellow for example range("A1:C2") and set .Value = 1 and .Interior.ColorIndex = 3 for all cells in range. Can you update the code for me?
  2. I would like to know how put range("A1:C2").Interior.ColorIndex to array - simply and fastest way.
Thank you and sorry for maybe stupid question, but I dont want to spend time to study Excel SDK if what I need is may be not reachable in C API.

Thank you very much in advance,
Lukas
// macro - run in Excel by pressing Alt-F8 and typing XLL.HELLO
static AddIn xai_hello("?xll_hello", "XLL.HELLO");
int WINAPI
xll_hello()
{
#pragma XLLEXPORT
    ExcelX(xlcAlert, OPER("Hello"));

    return 1;
}
Coordinator
Feb 21, 2013 at 10:50 PM
Edited Feb 21, 2013 at 10:50 PM
Hi Lukas, You can use xlfSelect and xlCoerce to get data from ranges in Excel. Use xlSet put put data back in a range.
See: http://xll.codeplex.com/wikipage?title=Excel for an example.
Feb 22, 2013 at 10:31 AM
Thank you, for values I can work with, but .Interior.ColorIndex I cannot make happend.
How adjust code to reach that the cell A1 will be colored red (ColorIndex=3) or how to read cell formula,
Is possible to find anywhere table or overview how to access in C API values of properties of range (used in COM) like .Formula or Interior.Colorindex?

Than you again for your time.
L.
Coordinator
Feb 22, 2013 at 2:17 PM
The Excel C API is documented in http://support.microsoft.com/kb/128185.
The zen of Excel is to use functions when possible. Macros hide things from users, but maybe that is your intent.
You can also embed (lightweigh) C++ objects in Excel using handles. See http://xll.codeplex.com/wikipage?title=Handles
Feb 25, 2013 at 6:09 PM
Yes, my aim is to use macro, because it is long slow calculation and I need to change some cell values before it starts.
I really tried to find solution. I hoped, that for example xlfgetcell can return color information.

Can read cells/ranges color (Interior.ColorIndex or interior.color) is critical for my addin, so can you give me please piece of example code?
Thank you for your time.
Lukas
Coordinator
Feb 25, 2013 at 6:16 PM
ExcelX(xlfGetCell, OPERX(63), xCellRef) should do the trick.
Feb 25, 2013 at 7:01 PM
OK, thank you. I nouw know how to read, but how to set?
I dont understand why 63.

I read book from Steve Dalton - Financial Applications Using Excel Add-in Development in CC++(2nd Edition) (2007) and I must say that I really dont know where I can learn for example thos trick=63 :-) but maybe I need to spend years to understand excel zen.

Can you give me advice what I should study to I can be able to convert my actual working dll based an Microsoft.office.Interop.Excel to XLL. Because I know thing it is beyond my capacity :-)

Thank you very much.
Can I make some donation for you and your team?
L.
Coordinator
Feb 26, 2013 at 2:42 AM
Thanks for offering a donation. I wrote the xll library to solve problems for my clients. I like having nice tools to use and hope other people find them useful. I appreciate your feedback so I can keep improving it. I work hard to be lazy.

To be honest with you xf_lg, this might not be the right tool for you. There is a pretty big learning curve to understanding the ancient API that lets you get the highest possible performance out of Excel. The link http://support.microsoft.com/kb/128185 is the Rosetta Stone for translating Excel4 macros into some of the things VBA and COM can do, but it is arcane and seems to be going the way of VBA.

My hope is that the more you look at what I wrote, the more you will discover how many cool things you can leverage off of.

If you want to play along, FORMAT.AUTO does what you want. It is a mess, but ExcelX makes it easier. You have to spelunk your way through MACROFUN.HLP to figure out the magic numbers. It is the price you have to pay to get closer to the silicon.
Feb 28, 2013 at 5:15 PM
I am reading MacroFun and testing commands using ExcelX.
Getting OPER this way doesnt work, I dont know why. There is only empty message box.
OPER H8 = ExcelX(xlfTextref, OPER("H8"), OPER(true));
ExcelX(xlcAlert, ExcelX(xlCoerce, H8));
When I use this, it works. MessageBox presents H8 value.
OPER H8(7,7,1,1);
ExcelX(xlcAlert, ExcelX(xlCoerce, H8));
Thanks, L.
Coordinator
Feb 28, 2013 at 5:24 PM
What value do you get for o when you run the following
OPER H8 = ExcelX(xlfTextref, OPER("H8"), OPER(true));
OPER o = ExcelX(xlCoerce, H8);
ExcelX(xlcAlert, ExcelX(xlCoerce, H8));
Feb 28, 2013 at 6:20 PM
No value, in watch is {...}
But in cell is value=2
Feb 28, 2013 at 6:25 PM
No value, in watch is {...}
But in cell is value=2
Coordinator
Feb 28, 2013 at 6:53 PM
What is o.xltype? Every object in C++ has a value. I'm guessing it is 16 = 0x10 = xltypeErr.

If you are calling this from a function and not a macro, you need to declare the AddIn object Uncalced().
Feb 28, 2013 at 7:14 PM
xltype=256

Just to explain, I need acces to named ranges. So I would like to use something like that:
OPER InputTable = ExcelX(xlfTextref, OPER("InputTable"), OPER(true));
where InputTable is in excel = Sheet1!$A$1:$C$2

Thank you
Feb 28, 2013 at 7:15 PM
and it is from macro.
Coordinator
Feb 28, 2013 at 7:27 PM
That is xltypNil. Are you sure there is something in the cell? xlfGetName might be useful here.
Feb 28, 2013 at 7:47 PM
Yes.

Thank for your time, really. I need study more and I will be back with some issues I am sure.
It is not easy for me, because I wrote my COM dll in VB.net. I was programming in C, but it is 16years ago :-) and C++ is new for me, but I have to manage this, because I feel that I can reach speed I need for my add-in only with native c++ xll.

I wish you the best,
L.
Mar 6, 2013 at 3:25 PM
Hi Keith,
I need your advice again, I can't move. Code is:
static AddInX xai_macro(
    // C function name, Excel macro name
    _T("?xll_macro"), _T("XLL.MACRO")
);

double ColorIndex(string sSheet, string sCell)
    /*
    SHOULD RETURN THE FILL (BACKGROUND) COLOR OF THE CELL 
    sSheet is name of sheet
    sCell is cell adress in R1C1 or named.range
    */
{
    double dColorIndex = -1;
    string sRef;
    string sActSheet;
    
    try {
        // oRngTest Only for testing and hava OPER of active sheet
        OPERX oRngTest(0,0,1,1);
        ExcelX(xlcAlert, ExcelX(xlCoerce, oRngTest)); // works fine
        ExcelX(xlcAlert, ExcelX(xlfGetCell, OPERX(63), oRngTest)); // works fine
        OPERX oActSheet = ExcelX(xlSheetNm, oRngTest);

        sRef = "!" + sCell; // = Sheet1!R1C1
        ExcelX(xlcWorkbookSelect, OPERX(sSheet.c_str()));
        
        // Creating OPER from cell adress with xlfTextref
        OPERX oCell = ExcelX(xlfTextref, OPERX(sRef.c_str()), OPERX(false));
        ExcelX(xlcAlert, ExcelX(xlCoerce, oCell)); // works ok
        ExcelX(xlcAlert, ExcelX(xlfGetCell, OPERX(63), oCell)); // NOT WORKING

        // OR

        // Creating OPER from oCell adress with .rwFirst and .colFirst
        OPERX oCell2(oCell.val.sref.ref.rwFirst, oCell.val.sref.ref.colFirst, 1, 1);
        ExcelX(xlcAlert, ExcelX(xlCoerce, oCell2)); // works fine
        ExcelX(xlcAlert, ExcelX(xlfGetCell, OPERX(63), oCell2)); // NOT WORKING => why oRngTest works and this doesn't

        ExcelX(xlcWorkbookActivate, oActSheet); 
        
        
    }
    catch (const std::exception& ex) {
        XLL_ERROR(ex.what()); 
        return -1;
    }
    
    return dColorIndex;
}

int WINAPI
xll_macro(void)
{
#pragma XLLEXPORT
    try {
        if (xlCoerce, OPERX(ColorIndex("Sheet1", "R1C1")) == 43) {
            ExcelX(xlcAlert, OPERX("Green Colored"));
        }
    }
    catch (const std::exception& ex) {
        XLL_ERROR(ex.what()); 
    }
    return 1; // success
}
Procedure ColorIndex I need call very offen, because in my financial model a use colors.

when I works with OPERX only within 1 sheet it works, but when I change sheets then OPERs are able only to give values.



Thank you for your time.
BR L.
Coordinator
Mar 6, 2013 at 3:50 PM
If xlfGetCell is working some of the time, I'm sure you can figure out how to get it working all of the time. If you want to run with the big dogs, you are going to have to learn how to use your tools. My guess is that your references aren't right, but I do not have the time to debug this for you.