Writing Excel Addons - Putting values into Excel Cells
(Page 7 of 8 )
Although there is an equivalent (sort of) of xlcoerce, called xlset, it can only be called from a command (menu or toolbar button) and not from a function. It's very anti-social anyway: to just dump a bunch of values into a spreadsheet it might just overwrite a morning's unsaved work- and won’t that improve your popularity!
A not so well known feature of Excel called Function Arrays (or formula arrays) is what is needed. If you aren’t familiar with them, try this on an empty Excel sheet.
- Select a rectangular area with the mouse. Now click on the editing line (just below the toolbars, above the cells) and type in =g1. At this point you should see =g1 in the edit line and the selected area should still be selected. If you cleared the selection by mistake, go back and try it again.
- Now hold down ctrl and shift keys and press enter. The =g1 should be pasted into all cells in the previously selected area.
You should also notice some things about this if you click on any cell in this area.
- The edit line shows the equation in brackets.
- You cannot change or clear the cell.
- It didn’t adjust the cell reference.
This is the only way (so far as I could work out) to put values into Excel cells. Your excel function must build up an array of xlopers, with the header pointing to the body. If your function returns an array, you must use a Function Array to show the result. Excel is quite clever with this. If you return a 3 x 5 area and the user pastes a Function Array into a 4 x 6 rectangle, the extra cells will all show N/A.
Memory Management
If an Excel4v call returns an xloper with a pointer (strings or xltypemulti for instance) then, when your code has finished with the value your code must always call xlfree on the xloper. In fact as a general rule, calling xlfree on any xloper does no harm at all.
There are two memory allocation cases that your code MUST handle.
The first memory allocation case occurs when you have called a routine that returns an xloper with data in it, e.g. xlcoerce to convert a xltypesref/xltyperef to an xltypemulti (array). Excel allocates its own memory and when you are finished with the data, you should OR in the value $1000 (4096 decimal) to the xltype- this $1000 value is known as xlbitXLfree. When you call xlfree, it frees up the ‘Excel allocated’ memory.
In the second case, if your code returns an array of xlopers which Excel shows as a function array, you must OR in the value $4000 (16384 decimal) to the xltype field, before the function exits. After copying the values Excel will do a call-back to your xlAutoFree function (you did implement one didn’t you?) with an lpxloper to your data. You can then free it. If you created the array with n elements, in (n+1) xlopers, where arrayname[0] is the header which points to arrayname[1] then the pointer returned points to arrayname[0] and freemem(call back pointer) will then free the correct pointer.
Next: An example Add-In >>
More Windows Scripting Articles
More By David Bolton
/ 7 
