Another "generic" exception handling subroutine ( Problems with .nil

Another "generic" exception handling subroutine ( Problems with .nil

Post by rony » Thu, 25 Nov 2004 07:57:54


That's possible, because (given your limits in the do-loop) a cell-object should be always available
and returned. Now, if that returned cell-object does not contain a value (string, number, formula),
then the fact that no value is available for that cell will be indicated by returning ".nil". The
default string value of the ".nil" object is the string "The NIL object", which is what you see on
the console.


Hmm, if you activate exception handling with "ANY" you may possibly intercept "non-fatal" exceptions
in other parts of your program ("ANY" catches all exceptions and even activates some of the new ones
e.g. the NOVALUE exception). For this reason I changed the exception handling routine.

While doing this, I found out that the exception directory object returned by the BIF condition("o")
has variable entries, sometimes the entries "additional" or "traceback" may be missing (hence
requesting those entries will return ".nil"!).

The following exception subroutine will handle these conditions as well. It will always add "_SIGL_"
and "_SOURCELINE_" to give the signal line number and the respective sourcecode. In addition it will
list all the directory entries of the exception object in ascending order (first the directory
indices will get sorted in a list, which then is used to iterate in sorted order over the objects
collected in the directory).

------------- cut here -----------
signal on any name any -- change first "any" e.g. to "syntax"
say 3+b -- this will raise the NOVALUE exception, because of "b"
say aha

pp: return "[" || arg(1) || "]"

d=condition("o") -- get exception object

-- add explicitly SIGL and sourceline(SIGL) to exception object
d~"_SIGL_"= SIGL -- add value of SIGL ("signal line") to exception object
d~"_sourceline_"= sourceline(SIGL) -- get and add sourceline

l=.list~new -- create a sorted list of directory indices
do val over d -- iterate over directory indices (strings)
l_idx = l~first -- get first list item
bInsert=.true -- to be inserted?
do while .nil <> l_idx & bInsert -- loop over list
if l[l_idx] > val then do -- list-item larger than value in hand?
l~insert(val, l~previous(l_idx)) -- insert value in hand before
bInsert=.false -- insertion took place, no need anymore
leave -- leave loop
l_idx = l~next(l_idx) -- go to next list element
if bInsert then l~insert(val) -- insert value

do i over l -- list directory entries in list-order
say i~right(13)":" pp(d~at(i))
say "---"

-- list the entries in the two following collection objects, if available
do i=1 to words(indices)
idx=word(indices, i) -- get index

if d~hasentry(idx) then -- if entry available
say idx":"
do k over d~at(idx)
say " " k
say "---"
exit -1
------------- cut here -----------

The above program will output:

------------- cut here -----------
_SIGL_: [2]
_SOURCELINE_: [say 3+b -- this will raise the NOVALUE e

Another "generic" exception handling subroutine ( Problems with .nil

Post by jerry chap » Thu, 25 Nov 2004 08:25:03

n the Excel file I am reading the values at cells(9,1) and cells(10,1) are
As I said in my previous note the "say value" command prints out "The NIL
object" for cells(9,1) & cells(10,1).
So I presume the command:
if myxl~cells(i,1)=.nil then leave
should have caused me to leave the loop for i=9.

"rony" < XXXX@XXXXX.COM > wrote in message
news:co0f9k$1bhb$ XXXX@XXXXX.COM ...
should be always available
(string, number, formula),
by returning ".nil". The
which is what you see on
intercept "non-fatal" exceptions
activates some of the new ones
handling routine.
by the BIF condition("o")
may be missing (hence
It will always add "_SIGL_"
sourcecode. In addition it will
(first the directory
order over the objects
because of "b"]
provoke a syntax error, which


Another "generic" exception handling subroutine ( Problems with .nil

Post by jerry chap » Thu, 25 Nov 2004 09:24:05

just got it. The signal on any routine was causing my problem. I changed
to your new one and it seems to work now. It also answers the question of
why it used to work.
"jerry chapman" < XXXX@XXXXX.COM > wrote in message
news:jFPod.21745$ XXXX@XXXXX.COM ...


Another "generic" exception handling subroutine ( Problems with .nil

Post by David Rugg » Fri, 26 Nov 2004 04:03:58

fter reading your post, might I suggest the following code:

::routine excelvalue public
/* routine to trap invalid values and formulas
Argument one has to be an Excel cells OLE Object
Argument two is optional, if true the formula is tested and returned
any other value and and the value is tested and returned
if .nil would be returned, an empty string ('') is returned instead
If a Syntax error occurs the string '#VALUE?' will be returned */

use arg ExcelCellObj
formula = (arg(2) = .true)

signal on syntax
if formula then do
val = ExcelCellObj~formula()
else do
val = ExcelCellObj~value()
if val = .nil then do
val = ''
return val

return '#VALUE?'

This not only traps empty cells and prevents the .nil, it also traps
cases where there is an error in the excel formula which will trigger a
syntax error when you attempt to read it via the value method.

Here is the documentation for the routine:
This routine handles two problems found with the Excel OLE object:
1) If you send a value method to a cell that has an invalid formula it
will generate
a syntax error
it returns the string '#VALUE?'
2) If you send a formula or value method to a cell that is blank you
get the .nil
it returns an empty string ''
This routine accepts two arguments:
1) Required, an object returned by the cells method of Excel
2) Optional, set to true if you want the formula, anything else returns
the value
Syntax Example:
val = excelvalue(excelobj~cells(aa, 'A'))

HTH: David

jerry chapman wrote:

David Ruggles
Network Engineer Safe Data, Inc.
(910) 285-7200 XXXX@XXXXX.COM