How can you update an array formula using VBA?

I have an excel application with two workbooks. I have written a VBA macro
in workbook 1 that includes a .open command to open workbook 2. Workbook 2
successfully opens, however, none of the array formulas residing in workbook
2 are updated (they all seem to default to a zero value). I have tried
changing the screenupdating and calculation properties. Any suggestions?

Ashleigh,

Have you tried forcing a calc?

Application.CalculateFull

HTH,
Bernie
MS Excel MVP


"Ashleigh Gardner" <Ashleigh [email protected]> wrote in
message news:[email protected]...
> I have an excel application with two workbooks. I have written a VBA
macro
> in workbook 1 that includes a .open command to open workbook 2. Workbook
2
> successfully opens, however, none of the array formulas residing in
workbook
> 2 are updated (they all seem to default to a zero value). I have tried
> changing the screenupdating and calculation properties. Any suggestions?

Bernie,

Yes, I tried both Application.Calculate and Application.CalculateFull. When
I use .Calculate, all array formulas default to 0. I have to maually go into
the workbook, select the desired cell, and place the cursor in the formula
box before the array formula will update. Using .CalculateFull caused
similar results except all worksheets seemed to default to the array formula
values of worksheet 1 (I have several worksheets that conatin array formulas).

~Ashleigh

"Bernie Deitrick" wrote:

> Ashleigh,
>
> Have you tried forcing a calc?
>
> Application.CalculateFull
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Ashleigh Gardner" <Ashleigh [email protected]> wrote in
> message news:[email protected]...
> > I have an excel application with two workbooks. I have written a VBA
> macro
> > in workbook 1 that includes a .open command to open workbook 2. Workbook
> 2
> > successfully opens, however, none of the array formulas residing in
> workbook
> > 2 are updated (they all seem to default to a zero value). I have tried
> > changing the screenupdating and calculation properties. Any suggestions?
>
>
>