Download (direct link):
Drop down combo-boxes return the value selected in the 7th column of the associated edit box and the position (counting from 1) of the selected item in the list in the 7th column of the combo-box item line.
The difficulty of manually putting together dialogs, with trial-and-error positioning and sizing of components, cried out for the kind of graphical design interface that Excel 5.0 first introduced and that VBA provides in current versions. (This is one of the reasons for not using the C API to create dialogs.)
Given that there may be times where it is more appropriate or convenient to package a simple dialog interface into your XLL, the task is made much easier using an XLM macro sheet to prototype the dialog. The steps are:
1. Open a new Excel workbook.
2. Insert an XLM macro sheet by right-clicking on one of the worksheet tabs and selecting Insert.. ./MS Excel 4.0 Macro.
Accessing Excel Functionality Using the C API
3. Place a label in cell A1 in the macro sheet, say, DlgTest, and define this as a name for cell A2.
4. Place the formula =DIALOG.BOX(DIALOG_DEFN) in cell A2 - (the range name DIALOG_ DEFN is created in a later step).
5. Place the formula =RETURN() in cell A3.
6. Create a table to contain the definition of the dialog (see above) and name the range DIALOG_DEFN. Do not include a title row in the definition. The location of the table is not important.
7. Via the Insert/Name/Define... dialog, define the name DlgTest as a command and assign a keystroke to it for easy running.
By modifying the contents of your named definition range and executing the command macro, you can fairly easily design simple dialogs that can be recoded in C/C++ within the DLL. (This is still a laborious process compared to the use of graphical design tools such as those that now exist in VB.)
Creating a static initialisation of an array of xlopers in C/C++, to hard-code your table, is complicated by the fact that C only provides a very limited ability to initialise unions, such as val in the xloper. Section 6.9 Initialising xlopers on page 157 provides a discussion of this subject and an example of a dialog definition table for a simple username and password dialog.
A more complex example dialog is included in the example project on the CD ROM in the Background.cpp source file. It is used to configure and control a background thread used for lengthy worksheet function execution. The workbook used to design this dialog, XLM_ThreadCfg_Dialog.xls, is included on the CD ROM. It also generates cpp_xloper array initialisation strings that can be cut and paste into a C++ source file.
8.13.3 Restricting user input to dialog boxes: xlcDisablelnput
Overview: Restricts all mouse and keyboard input to the dialog rather
Enumeration value: 32908 (x808c)
Callable from: Commands only.
Return type: Various. See table below.
Arguments: 1: Disable: Boolean. True disables input to Excel, false
Warning: Commands that call this function passing true should call passing false before returning control to Excel.
8.14 TRAPPING EVENTS
The C API provides a few simple Excel event traps which can easily be associated with DLL commands. The C API enables the setting of traps within the DLL for only a few
Excel Add-in Development in C/C++
of its events, namely:
• data coming in from an external DDE source;
• the user double-clicking on a cell in a worksheet;
• the user entering data into a cell in a worksheet;
• the user pressing a certain key combination;
• the user or the system initiating a recalculation;
• the user selecting a new worksheet window;
• the system clock reaching a specified time.
Excel generates many events that cannot be trapped (directly) by the DLL using the C API. For example, it is not possible to trap a change of selection on the worksheet or, most sadly, the opening or closing of a workbook. The most straightforward, albeit slightly messy, way to have your DLL called when a non-C API event occurs is to set a trap within VBA and use this to call into your DLL. For more details of VB events see
section 3.4 Using VBA to trap Excel events on page 45. For details of how to call into
your DLL from VB, see section 3.6 Using VBA as an interface to external DLL add-ins on page 48.
8.14.1 Trapping a DDE data update event: xlcOnData
Overview: Instructs Excel to call a specified command whenever DDE data
is received for a specified worksheet or from a specified source application. The command is called before Excel performs any recalculation of the worksheet resulting from the new data.
Enumeration value: 32907 (x808b)
Callable from: Commands only.
Arguments: 1: DataSourceSink: A string determining either the DDE data
source application or the worksheet to which the data is being
2: Command: The name of the command to be run as passed to Excel in the 4th argument to xlfRegister or the name of some other command macro or VB function.