Convert incell formula to VBA code

[spydey]

So I have a pretty complex formula that I am using in some workbooks.

I would like to convert them to VBA, but it is way over the 255 character limit that .Formula allows.

I know of and have used the replace option before, where, for example, you have a formula: =If(AAA,bbb,CCC)

You replace "AAA" with what you need that section to be, then again with "bbb", then again with "CCC", etc.

However, due to the length of the formula, this would be extremely tedious to do many iterations of replace.

I am curious, is there a better way to do it? Perhaps write the actual formula in VBA native code? I find that might be difficult given my limited abilities.

Any insight is very helpful and appreciated!

Thanks.

-Spydey

 

[pgc01]

I would like to convert them to VBA, but it is way over the 255 character limit that .Formula allows.

Hi Spydey

That limit is not for the .Formula, it's for the .FormulaArray.

This means you don't have to worry about the length of your formula

 

[spydey]

Hi Spydey

That limit is not for the .Formula, it's for the .FormulaArray.

This means you don't have to worry about the length of your formula

That is fantastic!! I wasn't aware .... although, now that I think of it, my formulas are entered using CSE, so they are array formulas. So I would be limited to the 255 character limit, wouldn't I?

If so, are there any workarounds?

Thanks @pgc01 for your input. I appreciate it!

-Spydey

 

[pgc01]

Yes, the cse formulas will have that limit.

In that case the workaround is the one with the replace that you posted.