Hi all,

I am writing VBA in an excel workbook that has a third-party add-in
loaded. The add-in basically provides function wrappers for calls to
their DLL that talks to an accounting system in Oracle. Anyway, I am
wrapping on of their wrappers to add a new function, and in this
wrapper it is necessary for me to use the evaluate method on some of
their functions. The logic in this routine works and the call to
Application.Evaluate works as expected. However, my wrapper initially
returns a #Value error. If I recalculate the workbook, the #Value
error persists. However, if I put the excel cursor on the cell that
has my custom wrapper in it and then calculate, the value appears
correctly.

Now this very well maybe a problem with the vendor's add-in, but before
I try to drill into their organization to find someone who knows what
they are talking about, I thought I would post here to see if this
sounds like an Excel thing. Specifically, I thought the Calculate
method applied to the entire application. If that's the case, then why
does it matter where the cursor happens to be in the UI when I hit F9
to calc?

Thanks in advance for any help,
Johnny

do you use ActiveCell in any of the code? If so, that may be the reason.

If you are trying to reference the cell with the formula, you would use

set rng = Application.Caller

to get a reference to that cell (as an example); not ActiveCell.

--
Regards,
Tom Ogilvy

"Johnny" wrote in message
oups.com...
Hi all,

I am writing VBA in an excel workbook that has a third-party add-in
loaded. The add-in basically provides function wrappers for calls to
their DLL that talks to an accounting system in Oracle. Anyway, I am
wrapping on of their wrappers to add a new function, and in this
wrapper it is necessary for me to use the evaluate method on some of
their functions. The logic in this routine works and the call to
Application.Evaluate works as expected. However, my wrapper initially
returns a #Value error. If I recalculate the workbook, the #Value
error persists. However, if I put the excel cursor on the cell that
has my custom wrapper in it and then calculate, the value appears
correctly.

Now this very well maybe a problem with the vendor's add-in, but before
I try to drill into their organization to find someone who knows what
they are talking about, I thought I would post here to see if this
sounds like an Excel thing. Specifically, I thought the Calculate
method applied to the entire application. If that's the case, then why
does it matter where the cursor happens to be in the UI when I hit F9
to calc?

Thanks in advance for any help,
Johnny

This is a shot in the dark, but you could try adding Application.Volatile to
the function. This will force the cell to recalculate each time a calculation
is run. It sounds to me like the cell is not being marked as Dirty. Entering
the formula for a cell and then leaving marks the cell as dirty by default
and forces it to re-evaluate which is why you seem to be getting the odd
behavior. The only note is that this means the the function will not be
extremely efficient as it will recalculate with every calculation. Not a big
deal if you are using the function sparingly.
--
HTH...

Jim Thomlinson


"Johnny" wrote:

Hi all,

I am writing VBA in an excel workbook that has a third-party add-in
loaded. The add-in basically provides function wrappers for calls to
their DLL that talks to an accounting system in Oracle. Anyway, I am
wrapping on of their wrappers to add a new function, and in this
wrapper it is necessary for me to use the evaluate method on some of
their functions. The logic in this routine works and the call to
Application.Evaluate works as expected. However, my wrapper initially
returns a #Value error. If I recalculate the workbook, the #Value
error persists. However, if I put the excel cursor on the cell that
has my custom wrapper in it and then calculate, the value appears
correctly.

Now this very well maybe a problem with the vendor's add-in, but before
I try to drill into their organization to find someone who knows what
they are talking about, I thought I would post here to see if this
sounds like an Excel thing. Specifically, I thought the Calculate
method applied to the entire application. If that's the case, then why
does it matter where the cursor happens to be in the UI when I hit F9
to calc?

Thanks in advance for any help,
Johnny

I'm not using ActiveCell, Activate, or Select in any of the code. I
already have the Application.Volatile method in this function. The
funciton is only used four times in the entire workbook (which has many
sheets, and a fair amount of code, a couple of user forms, etc.) The
function is a validation mechanism that tests to see that the data
pasted in by the user matches what is currently in the database in a
summary fashion (the user actually uses another program to pull the
accounting system data that is ultimately pasted into the workbook, but
this independent test is necessary to make sure the user doesn't screw
up). I have a parameter that determines what test to perform, hence
the need for only one function.

I have a strong feeling that the vendor's code is the culprit, but what
really bugs me is that if I step through this code line-by-line, all
the evaluations work precisely as expected and the code returns as
expected. It only spits out the #VALUE error when a user subsequently
recalculates the sheet when the cursor is not on the cell.

Regarding Excel marking the particular cell as Dirty, is there anyway
to inspect/govern this process? I'm obviously going to have to do some
sort of workaround, and the only thing that comes to mind right now
(aside from attempting to figure out the vendor's undocumented API, or
going straight to Oracle and figuring out the vendor's table structure,
and write my own COM DLL, then reference that in Excel) is to put a sub
in the OnCalc event (if that even exists) and loop the culprit cells,
activating/selecting them, and calcing the application in between.
That's really going to have a performance hit and is totally a hack,
but I digress...

I'm going to go exclusively to Visual C#.Net and write my own UIs from
the ground-up after this one! Excel's quirks have really piled up on
me lately!!!! I wonder if that's jumping from the frying pan into the
fire from the perspective of getting stuff to users that they
thoroughly understand quickly?!?!?

Thanks,
Johnny