Monthly Archives: June 2013

MACROFUN.H

I’ve added the header macrofun.h to make it a bit more convenient to call functions in MACROFUN.HLP. If there were truth in naming it would be called macronofun.h.

Creating spreadsheets by hand is labor intensive and they tend to acquire cruft as time goes by. When a client gave me a spreadsheet I created a year ago, it somehow had over 4,000 styles. See http://support.microsoft.com/kb/213904 for helpful advice if you ever run across this.

My first thought was to create golden copies using XML but I quickly realized I would need some sort of macro language to help generate common modules. I came across XBL and quickly decided poking flaming stakes through my eyes would be preferable to using this.

There are a bunch of Office tools available but I decided to just use straight C++ and calls to Excel. C++ makes a perfectly fine macro language, thank you very much. See xls.cpp in the test folder for examples of how to do this.

There are now variadic macros XLL_XLC, XLL_XLF, and XLL_XL_ that make it slightly easier to call macro functions. E.g., XLL_XLC(Selection) expands to Excel<XLOPERX>(xlcSelection). I’ve also added some enumerations and inline functions for common tasks. The call FormatFont(_T("Courier"), 12, FS_BOLD|FS_ITALIC, OLIVE_GREEN) would format the current selection in 12 point Courier bold italic in an ugly color.

It is good practice to use styles when creating spreadsheets. If you mark cells as Input, Output, Calculation, etc, then you can easily change the presentation of the sheet.

Advertisements

Handles and 64-bit builds

I get a lot of grief about handles. They look ugly, they can leak, they don’t produce unicorns or rainbows. Now that 64-bit builds are working, even Excel gives me grief about them.

The good news about 64-bit is that there is little new news. Basically the only thing that has changed are that pointers are now 64-bit. If you have taken a look at xll/handle.h, you will see that I just pack the pointer bits into a 64-bit IEEE double and hand that to Excel. With 32-bit pointers, that is not a problem, they can be exactly represented as an integer. Cramming 64-bit pointers into 64-bit doubles doesn’t always work out so nice.

See http://xllfloat.codeplex.com for the gory details, but not all doubles can make a round trip to Excel and back unscathed.

// 64-bit pointers might map to denormalized doubles
int fpclass = _fpclass(static_cast<HANDLEX>(px.l_));
ensure (fpclass == _FPCLASS_NN || fpclass == _FPCLASS_PN);

Now if you see a popup (assuming the error flag in ALERT.FILTER is set) about fpclass you should think of it as winning the lottery.
I’m not sure how to fix this yet, but I’m open to suggestions.