Error calling xlfGetDef Excel API function from Objecthandler's FunctionCall::callerName()

[Slava Mazur]

Greetings,

Has anyone experienced such a behavior? It happens intermittently for
some spreadsheets in some circumstances and I cannot figure out why.
Also I couldn't find any details about xlfGetDef function.
If anyone knows could you please point me to?

Here are some details.  

The following function call inside FunctionCall::callerName() returns
xName->xltype = 16 meaning error:

Excel(xlfGetDef, &xName, 1, FunctionCall::instance().callerAddress());

The function returns an empty string in such a case. This may result in
duplicate object keys defined in different cells, which is totally
undesirable. So my questions are:

1. Does anyone know why the function fails?
2. Why ObjectHandler doesn't handle a possible error condition?
3. Wouldn't it better to return FunctionCall::instance().callerAddress()
instead of an empty string in such a case?

Thanks,

Slava Mazur




-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
QuantLib-dev mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-dev

[Eric Ehlers-2]

Hi Slava,

Functions of the Excel C API are documented in the macrofun help file that
shipped with Excel 4:

    http://support.microsoft.com/kb/128185

The help file identifies the functions by their old names in the Excel 4 macro
language, xlfGetDef was called GET.DEF.  xlfGetDef retrieves the name, if any,
that is associated with the specified range.

ObjectHandler names the ranges from which addin functions are called as a way
of tracking objects.  The names are created by the call to xlfSetName in the
constructor of class CallingRange.  FunctionCall::callerName() wraps xlfGetDef
and returns the name which has been associated with the range that invoked
whatever function is currently executing.

If no name is associated with the given range then xlfGetDef returns a value
of type xltypeErr (16).  This isn't an error condition as far as
FunctionCall::callerName() is concerned and the function returns an empty
string.  The possible return values of FunctionCall::callerName() are handled
in RepositoryXL::getCallingRange().  It would not make sense for
FunctionCall::callerName() to return FunctionCall::callerAddress() in the
absence of a name.

If two different objects are somehow acquiring duplicate keys, that's
certainly an error condition which should be prevented.  I haven't seen the
error, is there any chance that you could troubleshoot further and maybe come
up with an example that would enable me to recreate the problem?

Regards,
Eric

-------------------------
Eric Ehlers
nazcatech sprl | Brussels | http://www.nazcatech.be
Distributed computing for pricing analytics - Use Microsoft Excel as a client
to the Grid

On Fri, August 15, 2008 19:07, Slava Mazur wrote:
> Greetings,
>
> Has anyone experienced such a behavior? It happens intermittently for some
spreadsheets in some circumstances and I cannot figure out why. Also I
couldn't find any details about xlfGetDef function.
> If anyone knows could you please point me to?
>
> Here are some details.
>
> The following function call inside FunctionCall::callerName() returns
xName->xltype = 16 meaning error:
>
> Excel(xlfGetDef, &xName, 1, FunctionCall::instance().callerAddress());
>
> The function returns an empty string in such a case. This may result in
duplicate object keys defined in different cells, which is totally
undesirable. So my questions are:
>
> 1. Does anyone know why the function fails?
> 2. Why ObjectHandler doesn't handle a possible error condition?
> 3. Wouldn't it better to return FunctionCall::instance().callerAddress()
instead of an empty string in such a case?
>
> Thanks,
>
> Slava Mazur





-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
QuantLib-dev mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-dev

[Slava Mazur]

Hi Eric,

Thank you for the information and for the insides of ObjectHandler
functionality which have been really helpful to pinpoint the problem.

Objects are stored in objectMap_ using object ID as a key (see
std::string Repository::storeObject).

ObjectXL class returns a user defined id ignoring the calling range and
this id is used as a key in the object map (see ObjectXL::id() and
RepositoryXL::storeObject()).

This is obviously a problem - if we create two different objects in
different Excel cells they will definitely substitute each other in the
object map.

Another problem is CallingRange::updateCount_. Since it's a non-static
member then it's possible to generate the same ObjectXL::idFull_ for
different objects with the same user defined id (see
ObjectXL::setCallingRange()).

Note that if a user doesn't specify their id, everything works fine
because in this case internally generated id takes into account a
calling range (see again ObjectXL::setCallingRange()).

I think this can be fixed in two ways. For my quick fix I changed the
logic of ObjectXL::id() - it generates id upending calling range key
regardless of whether a user passed a valid id or not.

However, as far as I understood, one of motivations for the current
design was to hide calling range keys from the users and handle them
internally. In this case one needs to figure out a caller range key and
generate a key to find an object in the map out of a user defined id and
the calling range key. I'm not sure how to do that. And also, in this
case one needs to make CallingRange::updateCount_ static. Otherwise a
user will be able to generate the same object ids for different objects,
which although will be handled correctly internally (since calling
ranges are different) would look misleading from the user's perspective.

Eric, I know I owe you my reply regarding XLL containers. Thank you for
your appreciation and interest. I still don't have any feedback from my
employer regarding the source code (perhaps because of vacation time and
Olympic Games of course :), but it looks like they won't object. As for
technical details, I'll definitely get back to you as soon as I sort out
all existing issues with ObjectHandler.

Regards,

Slava Mazur

-----Original Message-----
From: Eric Ehlers [mailto:[hidden email]]
Sent: Tuesday, August 19, 2008 5:21 AM
To: Slava Mazur
Cc: [hidden email]
Subject: Re: [Quantlib-dev] Error calling xlfGetDef Excel API function
from Objecthandler's FunctionCall::callerName()

Hi Slava,

Functions of the Excel C API are documented in the macrofun help file
that
shipped with Excel 4:

    http://support.microsoft.com/kb/128185

The help file identifies the functions by their old names in the Excel 4
macro
language, xlfGetDef was called GET.DEF.  xlfGetDef retrieves the name,
if any,
that is associated with the specified range.

ObjectHandler names the ranges from which addin functions are called as
a way
of tracking objects.  The names are created by the call to xlfSetName in
the
constructor of class CallingRange.  FunctionCall::callerName() wraps
xlfGetDef
and returns the name which has been associated with the range that
invoked
whatever function is currently executing.

If no name is associated with the given range then xlfGetDef returns a
value
of type xltypeErr (16).  This isn't an error condition as far as
FunctionCall::callerName() is concerned and the function returns an
empty
string.  The possible return values of FunctionCall::callerName() are
handled
in RepositoryXL::getCallingRange().  It would not make sense for
FunctionCall::callerName() to return FunctionCall::callerAddress() in
the
absence of a name.

If two different objects are somehow acquiring duplicate keys, that's
certainly an error condition which should be prevented.  I haven't seen
the
error, is there any chance that you could troubleshoot further and maybe
come
up with an example that would enable me to recreate the problem?

Regards,
Eric

-------------------------
Eric Ehlers
nazcatech sprl | Brussels | http://www.nazcatech.be
Distributed computing for pricing analytics - Use Microsoft Excel as a
client
to the Grid

On Fri, August 15, 2008 19:07, Slava Mazur wrote:
> Greetings,
>
> Has anyone experienced such a behavior? It happens intermittently for
some
spreadsheets in some circumstances and I cannot figure out why. Also I
couldn't find any details about xlfGetDef function.
> If anyone knows could you please point me to?
>
> Here are some details.
>
> The following function call inside FunctionCall::callerName() returns
xName->xltype = 16 meaning error:
>
> Excel(xlfGetDef, &xName, 1, FunctionCall::instance().callerAddress());
>
> The function returns an empty string in such a case. This may result
in
duplicate object keys defined in different cells, which is totally
undesirable. So my questions are:
>
> 1. Does anyone know why the function fails?
> 2. Why ObjectHandler doesn't handle a possible error condition?
> 3. Wouldn't it better to return
FunctionCall::instance().callerAddress()
instead of an empty string in such a case?
>
> Thanks,
>
> Slava Mazur







-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
QuantLib-dev mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-dev

[Eric Ehlers-2]

Hi Slava,

On Tue, August 19, 2008 19:53, Slava Mazur wrote:
> Thank you for the information and for the insides of ObjectHandler
functionality which have been really helpful to pinpoint the problem.

OK!  I'll help however I can to promote the use of OH as a standalone app.

> Objects are stored in objectMap_ using object ID as a key (see
> std::string Repository::storeObject).
>
> ObjectXL class returns a user defined id ignoring the calling range and this
id is used as a key in the object map (see ObjectXL::id() and
RepositoryXL::storeObject()).
>
> This is obviously a problem - if we create two different objects in
different Excel cells they will definitely substitute each other in the
object map.

ObjectHandler is not as badly broken as that!  ;)

Try an experiment:
- Start Excel
- Load your favorite OH-derived XLL - ExampleXLLStatic, QLXL,
  your own app, whatever
- In cell A1, create an object with ID foo
- In cell A2, create an object with ID foo

The constructor in A2 fails with this error message:

    ohCustomer - Cannot create object with ID 'foo' in cell
    [exampleStatic.xls]Feuil1!L2C1 because an object with that ID already
    resides in cell [exampleStatic.xls]Feuil1!L1C1

See RepositoryXL::storeObject().

All the QLXL constructors have an autogenerated parameter Overwrite which you
can set to True if you really want to do that.

> Another problem is CallingRange::updateCount_. Since it's a non-static
member then it's possible to generate the same ObjectXL::idFull_ for
different objects with the same user defined id (see
> ObjectXL::setCallingRange()).

idFull_ takes the form abcd#0000 where

1) abcd is either the string supplied by the user, or the value obj_12345
generated by OH if the user passed a null string.  As far as the user is
concerned, the 12345 is just a value which OH guarantees to be unique.  In
practice it is the key of the calling range.

2) 0000 is the update count for the calling range

As explained above, 1) is guaranteed to be unique, whether the value is
provided by the user or autogenerated by OH.

But note that 2) is for informational purposes only and we don't rely on the
uniqueness of 2) to ensure the uniqueness of idFull_.  Any time an object ID
is used for internal processing, it is passed to getStub() which strips off
the #0000 suffix if present.

Also class ObjectXL last appeared in OH version 0.9.0 and is replaced by
ObjectWrapperXL in 0.9.6.  Once you get down to implementation you will
probably want to work from the svn trunk.

> Note that if a user doesn't specify their id, everything works fine because
in this case internally generated id takes into account a calling range (see
again ObjectXL::setCallingRange()).

Yes, an autogenerated ID is guaranteed unique by incorporating
CalingRange::key(), which I represented as 12345 above.

> I think this can be fixed in two ways. For my quick fix I changed the logic
of ObjectXL::id() - it generates id upending calling range key regardless of
whether a user passed a valid id or not.

Based on the above I think no fix is necessary, unless you have found a bug
which breaks the test I described.  Your resolution would be effective in such
a situation, though I wouldn't use it as a permanent solution since I think
the user would be surprised to find his key altered.  I understand you have
this as just a quick fix.

> However, as far as I understood, one of motivations for the current design
was to hide calling range keys from the users and handle them internally. In
this case one needs to figure out a caller range key and generate a key to
find an object in the map out of a user defined id and the calling range
key. I'm not sure how to do that. And also, in this case one needs to make
CallingRange::updateCount_ static. Otherwise a user will be able to generate
the same object ids for different objects, which although will be handled
correctly internally (since calling ranges are different) would look
misleading from the user's perspective.

I haven't explicitly set out to hide keys from the user.  They can be accessed
by function ohObjectCallerKey() and are written to the log file by function
ohLogAllObjects().  I find that very helpful for troubleshooting but I doubt
anyone else ever used it so yes in general the keys are not displayed to the
user.

Again I think that the uniqueness of object IDs is already enforced, without
the need to make updateCount unique.  Please review this and let me know - if
you have found a bug in the current implementation then we can work out a fix.

> Eric, I know I owe you my reply regarding XLL containers. Thank you for your
appreciation and interest. I still don't have any feedback from my employer
regarding the source code (perhaps because of vacation time and Olympic
Games of course :), but it looks like they won't object. As for technical
details, I'll definitely get back to you as soon as I sort out all existing
issues with ObjectHandler.

No hurry at all.  I'm glad to hear that the early signs are encouraging and I
look forward to getting into the details when the time comes.

Regards,
Eric

-------------------------
Eric Ehlers
nazcatech sprl | Brussels | http://www.nazcatech.be
Distributed computing for pricing analytics - Use Microsoft Excel as a client
to the Grid


-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
QuantLib-dev mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-dev

[Slava Mazur]

Hi Eric,

Thank you for the explanation. Looks like the issue is boiled down to a
new ObjectHandler requirement missed by me at some point: from now on
one cannot define two different objects in different Excel cells and
having the same user id. Depending on "overwrite" parameter the result
would be either exception or the second object substitutes the first one
in the object map.

Please confirm that the above statement is correct.

If so, then yes, I agree, ObjectHandler is not as badly broken. What is
badly broken, however, is backward compatibility, since now I have to
redesign all my spreadsheets which worked fine with previous version of
ObjectHandler (the one that used '~' as a key separator) to make sure
uniqueness of all object ids defined. Right?

Thanks again,

Slava Mazur

-----Original Message-----
From: Eric Ehlers [mailto:[hidden email]]
Sent: Wednesday, August 20, 2008 7:23 AM
To: Slava Mazur
Cc: [hidden email]
Subject: RE: [Quantlib-dev] Error calling xlfGetDef Excel API function
from Objecthandler's FunctionCall::callerName()

Hi Slava,

On Tue, August 19, 2008 19:53, Slava Mazur wrote:
> Thank you for the information and for the insides of ObjectHandler
functionality which have been really helpful to pinpoint the problem.

OK!  I'll help however I can to promote the use of OH as a standalone
app.

> Objects are stored in objectMap_ using object ID as a key (see
> std::string Repository::storeObject).
>
> ObjectXL class returns a user defined id ignoring the calling range
and this
id is used as a key in the object map (see ObjectXL::id() and
RepositoryXL::storeObject()).
>
> This is obviously a problem - if we create two different objects in
different Excel cells they will definitely substitute each other in the
object map.

ObjectHandler is not as badly broken as that!  ;)

Try an experiment:
- Start Excel
- Load your favorite OH-derived XLL - ExampleXLLStatic, QLXL,
  your own app, whatever
- In cell A1, create an object with ID foo
- In cell A2, create an object with ID foo

The constructor in A2 fails with this error message:

    ohCustomer - Cannot create object with ID 'foo' in cell
    [exampleStatic.xls]Feuil1!L2C1 because an object with that ID
already
    resides in cell [exampleStatic.xls]Feuil1!L1C1

See RepositoryXL::storeObject().

All the QLXL constructors have an autogenerated parameter Overwrite
which you
can set to True if you really want to do that.

> Another problem is CallingRange::updateCount_. Since it's a non-static
member then it's possible to generate the same ObjectXL::idFull_ for
different objects with the same user defined id (see
> ObjectXL::setCallingRange()).

idFull_ takes the form abcd#0000 where

1) abcd is either the string supplied by the user, or the value
obj_12345
generated by OH if the user passed a null string.  As far as the user is
concerned, the 12345 is just a value which OH guarantees to be unique.
In
practice it is the key of the calling range.

2) 0000 is the update count for the calling range

As explained above, 1) is guaranteed to be unique, whether the value is
provided by the user or autogenerated by OH.

But note that 2) is for informational purposes only and we don't rely on
the
uniqueness of 2) to ensure the uniqueness of idFull_.  Any time an
object ID
is used for internal processing, it is passed to getStub() which strips
off
the #0000 suffix if present.

Also class ObjectXL last appeared in OH version 0.9.0 and is replaced by
ObjectWrapperXL in 0.9.6.  Once you get down to implementation you will
probably want to work from the svn trunk.

> Note that if a user doesn't specify their id, everything works fine
because
in this case internally generated id takes into account a calling range
(see
again ObjectXL::setCallingRange()).

Yes, an autogenerated ID is guaranteed unique by incorporating
CalingRange::key(), which I represented as 12345 above.

> I think this can be fixed in two ways. For my quick fix I changed the
logic
of ObjectXL::id() - it generates id upending calling range key
regardless of
whether a user passed a valid id or not.

Based on the above I think no fix is necessary, unless you have found a
bug
which breaks the test I described.  Your resolution would be effective
in such
a situation, though I wouldn't use it as a permanent solution since I
think
the user would be surprised to find his key altered.  I understand you
have
this as just a quick fix.

> However, as far as I understood, one of motivations for the current
design
was to hide calling range keys from the users and handle them
internally. In
this case one needs to figure out a caller range key and generate a key
to
find an object in the map out of a user defined id and the calling range
key. I'm not sure how to do that. And also, in this case one needs to
make
CallingRange::updateCount_ static. Otherwise a user will be able to
generate
the same object ids for different objects, which although will be
handled
correctly internally (since calling ranges are different) would look
misleading from the user's perspective.

I haven't explicitly set out to hide keys from the user.  They can be
accessed
by function ohObjectCallerKey() and are written to the log file by
function
ohLogAllObjects().  I find that very helpful for troubleshooting but I
doubt
anyone else ever used it so yes in general the keys are not displayed to
the
user.

Again I think that the uniqueness of object IDs is already enforced,
without
the need to make updateCount unique.  Please review this and let me know
- if
you have found a bug in the current implementation then we can work out
a fix.

> Eric, I know I owe you my reply regarding XLL containers. Thank you
for your
appreciation and interest. I still don't have any feedback from my
employer
regarding the source code (perhaps because of vacation time and Olympic
Games of course :), but it looks like they won't object. As for
technical
details, I'll definitely get back to you as soon as I sort out all
existing
issues with ObjectHandler.

No hurry at all.  I'm glad to hear that the early signs are encouraging
and I
look forward to getting into the details when the time comes.

Regards,
Eric

-------------------------
Eric Ehlers
nazcatech sprl | Brussels | http://www.nazcatech.be
Distributed computing for pricing analytics - Use Microsoft Excel as a
client
to the Grid




-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
QuantLib-dev mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-dev

[Eric Ehlers-2]

Hi Slava,

On Wed, August 20, 2008 15:01, Slava Mazur wrote:

> Hi Eric,
>
> Thank you for the explanation. Looks like the issue is boiled down to a
> new ObjectHandler requirement missed by me at some point: from now on
> one cannot define two different objects in different Excel cells and
> having the same user id. Depending on "overwrite" parameter the result
> would be either exception or the second object substitutes the first one
> in the object map.
>
> Please confirm that the above statement is correct.

Confirmed!

> If so, then yes, I agree, ObjectHandler is not as badly broken. What is
> badly broken, however, is backward compatibility, since now I have to
> redesign all my spreadsheets which worked fine with previous version of
> ObjectHandler (the one that used '~' as a key separator) to make sure
> uniqueness of all object ids defined. Right?

Probably yes.  I remember the ~ thing, that was a long time ago.  I can't
remember whether unique IDs were enforced in whatever version of OH that was,
but if you tell me they weren't I believe you.  I apologize for the
frustration caused by the lack of backward compatibility.

Regards,
Eric

-------------------------
Eric Ehlers
nazcatech sprl | Brussels | http://www.nazcatech.be
Distributed computing for pricing analytics - Use Microsoft Excel as a client
to the Grid


-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
QuantLib-dev mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-dev