Thanks Steve! You just sold a book! I found it online and will definately order it (unless you can get me an advance copy:). I work for a major fixed income firm in NY and this book is perfect for me. Can't wait to get it. In the meantime, I have 2 other books on order, one of which is the SDK. I've been reading that online, but the book will have the CDROM.
I've got this code working almost perfectly. The one problem I have is that when I double click a cell in excel I get an error message that The macro 'xlCmdOnDoubleClick' cannot be found. If I'm interpreting xResult correctly after the call to xlfRegister I'm getting a stack overflow error, but I'm not sure about that. I think I might just have a simple syntax issue, but I haven't been able to resolve it. Any hints? Anyone?
__declspec(dllexport) void xlCmdOnDoubleClick(void)
{
Excel(xlcAlert, 0, 2, TempStr(" Doubleclick event"), TempInt(2));
}
__declspec(dllexport) short int xlAutoOpen(void)
{
static XLOPER xDLL;
XLOPER xResult;
int i, nSuccess;
Excel(xlcAlert, 0, 2, TempStr(" xlAutoOpen event"), TempInt(2));
Excel(xlGetName, &xDLL, 0);
for (i=0; i<rgFuncsRows; i++) {
Excel(xlfRegister, 0, 15,
(LPXLOPER) &xDLL,
(LPXLOPER) TempStr(rgFuncs[i][0]),
(LPXLOPER) TempStr(rgFuncs[i][1]),
(LPXLOPER) TempStr(rgFuncs[i][2]),
(LPXLOPER) TempStr(rgFuncs[i][3]),
(LPXLOPER) TempStr(rgFuncs[i][4]),
(LPXLOPER) TempStr(rgFuncs[i][5]),
(LPXLOPER) TempStr(rgFuncs[i][6]),
(LPXLOPER) TempStr(rgFuncs[i][7]),
(LPXLOPER) TempStr(rgFuncs[i][8]),
(LPXLOPER) TempStr(rgFuncs[i][9]),
(LPXLOPER) TempStr(rgFuncs[i][10]),
(LPXLOPER) TempStr(rgFuncs[i][11]),
(LPXLOPER) TempStr(rgFuncs[i][12]),
(LPXLOPER) TempStr(rgFuncs[i][13]));
}
//Register function for double click event
nSuccess = Excel(xlfRegister, (LPXLOPER) &xResult, 2,
TempStr(" C:\\CPP\\Don_XLLs\\xllEvents\\Debug\\xllEvents.xll"),
TempStr(" xlCmdOnDoubleClick"));
if (nSuccess != xlretSuccess) //Excel() returned an error
{Excel(xlcMessage, 0, 2, TempBool(1), TempStr(" xlAutoOpen event: DblClick assignment failed"));}
//Attach xlCmdOnDoubleClick to double click event
nSuccess = Excel(xlcOnDoubleclick, (LPXLOPER) &xResult, 2, TempMissing(), TempStr(" xlCmdOnDoubleClick"));
if (nSuccess != xlretSuccess)
{Excel(xlcMessage, 0, 2, TempBool(1), TempStr(" xlAutoOpen event: DblClick assignment failed") ); }
else
{Excel(xlcMessage, 0, 2, TempBool(1), TempStr(" xlAutoOpen event: DblClick is on") );}
return 1;
}
--
Don H
Post by Steve DaltonHi Don
The answers to both questions are yes, although the ins and outs aren't
always that straightforward and there's some mixing of C API and COM which
is almost guaranteed to crash Excel. The official MS line is, perhaps
rightly, quite conservative (see KBA 301443 for example). The Excel 97 SDK
book has some limited guidance and if you look hard enough you can find a
few things on the internet and in other KBAs. (The SDK text is online on
the MS site). I might be breaking protocol to mention this, but I have a
book coming out in October which has a short section (really just an
introduction) on this subject.
Steve
Steve,
Thanks for the help. I'm glad I could confirm that, and I'll try to make
the events you've outlined work for me. I noticed all the events seem to be
exposed in OLE View. Can I make an XLL using automation that doesn't use
MFC? (Based on my reading I think the answer is yes) Also, can I combine use
of automation and the C API in the same XLL? (Again, I think this is yes).
Thanks again!
Don H
Post by Steve DaltonHi Don
Sadly, the C API doesn't provide access to all the events that VB does.
The
Post by Steve Daltononly events that you can capture using the C API are
1. data coming in from an external DDE source
2. the user double-clicking on a cell in a worksheet
3. the user entering data into a cell in a worksheet
4. the user presses a certain key combination
5. the user or the system initiates a recalculation
6. the user selects a new worksheet window
7. the system clock reaching a specified time
The C API command functions are xlcOnData, xlcOnDoubleclick, xlcOnEntry,
xlcOnKey, xlcOnRecalc, xlcOnWindow, xlcOnTime respectively. You can
find
Post by Steve Daltonout more about the syntax by checking the XLM macro language equivalents
of
Post by Steve Daltonthese functions.
Steve
Post by Don HHello All,
I'm an expert VBA developer for Excel, but I'm new to C++ and building
XLLs. I've learned to build an XLL that makes custom worksheet functions
available in Excel, but I'm having trouble capturing events. Can anyone
help
Post by Steve Daltonme write the C++ equivalent of this VBA code in my XLL?
Post by Don HPublic WithEvents xlApp As Application
Option Explicit
Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook)
MsgBox "NewWorkbook event"
End Sub
--
Don H