Writing Excel Addons - Caution- Recalculation Alert!

(Page 4 of 8 )

If the inputs to your function are calculated in any way, your code should check the status of the Excel call (Eresult in the example above) as well as the result of the Excel function call which will be in xres above. Remember, you call Excel4V to cals the specified function.

Eresult gives 0 if ok, or a bit set to indicate errors. You should always handle the values 1 and 64. 1 is xlabort, indicating the user pressed the esc key. 64 is an uncalculated cell reference. It's not an error, but happens when your function uses functions or calculations on the worksheet that are fully evaluated after your routine is first called. If this happens, you code must free up any memory already allocated in the call and exit. Excel will call your function again.
 
Structure your code like this

1. Get Input Parameters.
   
2. If an error 64 or 1 (abort key) occurs, exit after freeing up any memory already allocated (if any) in this function.
   
3. Do main function processing.

If step 3 is going to be very time consuming, say more than 1/10th of a second, your code should check the abort status by calling xlAbort periodically.

Startup

When the Add-in is first added to Excel, each of the exported functions must be registered. This involves calling the Excel Register routine for each routine. There are eight parameters passed as XlOpers.

• Parameter Type Use.
• Module text String Full name of DLL with path.
• Procedure String or number Function name or number to call.
• Type Text String Argument and return types.
• Function Text String Function name in function Wizard.
• Argument Text String Optional Text String that describes parameters in function Wizard.
• Macro Type Number 0,1, or 2.
• Category String or Number Category under which function appears.
• Shortcut Text String Only used for commands, pass in null for function.

The most important parameter to register is the text type. This uses the letters A-R to represent the return value and the supplied parameters. Values are passed as you’d expect in a C Api by reference (i.e. pointer- which is always an lpxloper) or by value.

E.g. the Excel spreadsheet =MyFunction(a1:b1) passes a reference while =InterestRate(“GBP”) uses a value.

Example

    DLLName :=GetName; // function to return name/path from Excel
    pxModuleText.SetStr(s); // Note (1)
    pxProcedure.SetStr('AddDate'); // Function Name
    pxTypeText.SetStr('JJDD!'); // (2) Type Return = J, Input = JDD
    pxFunctionText.setStr('AddDate'); // Name in Fx Wizard
    pxArgumentText.SetStr('Date,Period,Currency'); // Parm text in FX Wiz
    pxMacrotype.SetNum(1); // Type = Sinmple func (3)
    pxCategory.SetStr(AddInCategory); // Category for Func. wiz

    EResult:=Excel4V(xlfregister,@res,8, // Pass in 8 parms
      [pxModuletext.thelpxloper,
       pxProcedure.thelpxloper,
       pxTypeText.thelpxloper,
       pxFunctionText.thelpxloper,
       pxArgumentText.thelpxloper,
       pxMacroType.thelpxloper,
       pxCategory.thelpxloper,
       zlpxloper]); // Null
   asm pop sink; end; // Never Remove
   Result := trunc(res.val.num); // ID Code (4)

The px variables are all of the txloper class, which simplifies initializing xlopers.

Notes

1. The DLLName and path should be obtained from Excel, via the XlGetName function- you can see this used in the example code. Do not hardcode it as it includes the path.
   
   
2. See Register (page 219 in EdK) for fuller details of Text Type. The first parameter is the return parameter (J=32 bit integer) the next 3 are inputs (JDD = Integer, String, String) and the ! means that Excel always calls the function when it recalculates. Without the !, Excel calls the function once, latches the result and uses the latched result on later calls.
   
   
3. Type 1 = Function. Other types include unlisted function (type 0) and commands (type 2). If you want functions that don’t show up in the Function Wizard, use 0.
   
   
4. After successful registering, the numeric value in res (res.val.num) contains an integer id code for this function- the handle. Save this and pass it to the Unregister function when the DLL is closed. Forget this and you’ll have trouble using newer versions of the library..

Next: Getting Values from an Excel Spreadsheet >>
 

More Windows Scripting Articles
More By David Bolton

Comments On: Writing Excel Addons

>>> Be the FIRST to comment on this article!