calling a function

I am having problems with my find match function in VBA, this workbook
is linked to another workbook, and the reference cells come from that
workbook, this doesn't always calculate because there has been no event
that triggers the calculation,
Right now I have a macro that goes back to that work book and rewrites
a formula in one of the cells just to trigger the calculation, is there
a more efficient way to do this?

To force a particular sheet or range to recaulculate, you may use the
Calculate method.

For example,

workbooks(book_name).Worksheets(sheet_name).calculate

Regards,
Edwin Tam
[email protected]
http://www.vonixx.com


"dmexcel" wrote:

> I am having problems with my find match function in VBA, this workbook
> is linked to another workbook, and the reference cells come from that
> workbook, this doesn't always calculate because there has been no event
> that triggers the calculation,
> Right now I have a macro that goes back to that work book and rewrites
> a formula in one of the cells just to trigger the calculation, is there
> a more efficient way to do this?
>
>

I would have thought that if the other workbook doesn't recalculate, then
there is nothing to recalculate, so your forcing a recalculation is
superfluous. Why do you think you need to force it?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"dmexcel" <[email protected]> wrote in message
news:[email protected]...
> I am having problems with my find match function in VBA, this workbook
> is linked to another workbook, and the reference cells come from that
> workbook, this doesn't always calculate because there has been no event
> that triggers the calculation,
> Right now I have a macro that goes back to that work book and rewrites
> a formula in one of the cells just to trigger the calculation, is there
> a more efficient way to do this?
>

Hi,
If I enter the numbers manually, it works fine,
If I reference the numbers say =thisworkbook,thissheet,this range
When the numbers change, it doesn't always recalculate unless I double
click on the cell or do something to it, to trigger the function

A couple of thoughts:
1) If this is a function procedure called from a worksheet, make sure all inputs are passed to the function as arguments. This way, Excel will know how the function fits into its dependency tree and can calculate it correctly.
2) If this is part of a Sub procedure, then make the procedure part of the appropriate calculate/change/other event procedure.

Does that help?

HHMM!,
I am sure that is what I have to do but don't know how
Andy