Re: [Quantlib-dev] Some questions about forcing recalculation

[Eric E. <eri...@na...>]

Hi Andrew,

Please review the documentation below:

    http://www.objecthandler.org/references.html
    http://www.quantlibaddin.org/observer.html

On Wed, August 27, 2008 10:29, a.p. wrote:
>
> "I wouldn't say that triggers are inconvenient - I would say that your Excel
> application should be carefully designed to ensure that object dependencies
> are maintained through the use of triggers."
>
> In fact object dependencies are absolutly maintained through the use of
> triggers, but this usage is inconvenient. For example, you've created an
> object ("my_obj") and calculate some functions (maybe dozens) with this
> object as a parameter. So the user of created application should directly
> reference to the cell with returning string ID (for instance "my_obj#001")
> through trigger argument.

In Book1.xls, create the object with ID "my_obj", then close Book1.xls.

Now, from anywhere else in the Excel session, when that object is required as
an input to a function, the object can be identified by the raw (literal)
string "my_obj" - not "my_obj#001" and not a reference to the range in the
closed Book1.xls from which the object was constructed.

The fact that Book1.xls is closed, prevents "my_obj" from being recreated
and spares you the hassle of ensuring that dependents of "my_obj" are updated
after "my_obj" is recreated.

(In fact ObjectHandler 0.9.6 includes an enhancement to prevent such problems
but let's not get into that now).

In this simple example you don't even need any triggers of any kind.

> In case of you example:
>
> "Construct objects as a separate step, when the application is initialized
> or in response to menu events.  Workbooks containing constructors should be
> opened once, calculated and closed to prevent unnecessary object
> reconstruction."
>
> it becomes impossible at all, cause object workbook have been closed.

Sounds like you're using a reference to the range in the closed book from
which "my_obj" was constructed.  Use instead the string "my_obj".

> Is
> this another way to reference all necessary objects as trigger argument when
> the application is initialized or maybe some other algorithm to prevent user
> from referencing trigger cell every time he calculates new function? Maybe,
> i missed smth, but i don't know what to do :(

So far we haven't even gotten into a case where triggers are required.  An
example of that is in the docs I referenced above, please get back to me if
it's unclear.

> "The XLL can use xlcOnRecalc to trap the recalculation event but I would
> strongly advise against that in this situation."
>
> Absolutely agree with you.

Ooops.  I mentioned xlcOnRecalc in response to this comment of yours:

> For example, i tried
> to constuct a VBA UDFs with QLXL functions call and force recalculation in
> case of hitting F9 or Shift+F9, but i couldn't.

But I misread - you're trying to implement the UDF not in the XLL but in VBA.
I wouldn't do that either ;-)

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