Long array formulas in VBA...

[Matty]

Hi,

I understand there is a restriction in terms of the length of array formulas that can be used when implementing them via VBA. But what options are available to me to circumvent this shortfall?

I have seen a solution in **** Kusleika's blog, whereby the formula is broken down into smaller sections for it to work, but is this really my only option?

Any advice welcome...

Thanks,

Matty

 

[Matty]

Bump...

 

[ukmikeb]

Matty

Yes, either using Replace as in the Daily Dose Blog or Substitute.

Rather than use the "X_X_X" I tend to put a meaningful name.

Good luck

 

[Matty]

Thanks ukmikeb.

The particular formula I have is a bit of a monster and I need to split it up into more than two parts. Any idea how I could do this?

I've had a play, but I just keep getting the 'Unable to set the FormulaArray property of the Range class' message with whatever I try, which isn't very helpful at all...

Thanks,

Matty

 

[ukmikeb]

Matt

Post it on here.

I'll see what I can do.

If I can't help I'm sure someone else will.

 

[Colin Legg]

It depends on the formula. If there's a repeating section then you could use a name to represent it, thus shortening the formula.

You might also be able to improve the formula itself.

Why not post the formula (formula might be an easier starting point than the code itself) so we can have a try?

 

[Matty]

Hi Chaps,

Don't worry, this is now sorted. I've simplified the formula by using a helper Column which means it no longer needs array entry.

Thanks for your interest.

Matty