Writing Excel Addons - Getting Values from an Excel Spreadsheet

(Page 5 of 8 )

Values can be passed in, in three ways.

1. As a passed-by-value. Eg =CalcMean(4,5,8); xltypenum or xltypestr.
   
   
2. As a Simple reference, say a cell a1 or range a3:b56. xltypesref.
   
   
3.  As a composite Reference. Basically a collection of disjoint cell references. xltypemref

The first and the second are the most common. The third is a bit weird and unless really needed, it is better to filter this out. If you want to use it, the input is an array of cell references (type 2), so you have to use each one.

Excel will filter the data type if you specify it (number, string etc) but if you're expecting a range, then you should fully check the type and ranges of values, reporting errors if appropriate.

Returning Errors

Your function must return an lpxloper (type R) if you wish to return Excel Error codes. To set an error in Res (an xloper) just do

res.xltype := xltypeerr;
res.val.err := number; // returns following values then just return @res from your function

Number Excel Err
0 #NULL
7 #DIV/0
15 #VALUE
23 #REF
29 #NAME
36 #NUM
42 #N/A

With complicated, many parameter functions I think that standard Excel errors are a little unfriendly, so I strongly suggest a GetlastError() function which returns the last error string. Any function which returns an error should set the error string returned by GetLastError(). This will save users a lot of time when they are using your functions for the first time.

Next: Reading Cell Values >>
 

More Windows Scripting Articles
More By David Bolton

Comments On: Writing Excel Addons

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