PDA

View Full Version : [SOLVED] Handling Formula Errors & Formula Length Limit



kmlartigue
08-11-2017, 10:46 AM
Some formulas return a #REF! error which is correct because the account does not exist that it is looking for. What is a good code to use to say if REF error is returned, replace the value in the cell with 0?

SamT
08-11-2017, 11:04 AM
Old Way:
If(IsError(Original Formula),0,Original Formula,?,?))))))))))... however many ))))))))) as needed

Replace "Original Formula" with the original Formula. ",?,?" represents the return from the original Formula.

kmlartigue
08-11-2017, 11:14 AM
That works great, thank you.

Bob Phillips
08-11-2017, 04:48 PM
WE have IFERROR now

=IFERROR(formula,"")

offthelip
08-12-2017, 02:43 AM
I wrote this useful macro to put iferror around an existing formula, I use it all the time:


Sub addiferror()
cform = ActiveCell.Formula
cform2 = Mid(cform, 2, 2555)
cform3 = "=iferror(" & cform2 & ",0)"


ActiveCell.Formula = cform3
End Sub

mdmackillop
08-12-2017, 03:03 AM
Hi Offthelip
I would be concerned if errors were returned of which I was not aware. I'd suggest adding this to allow your 0 entry but highlight the issue and allow dependent cells to function

ActiveCell.NumberFormat = "0;0;""Err"""

offthelip
08-12-2017, 03:55 AM
Thanks md that is a very nice addition.

Paul_Hossler
08-12-2017, 05:56 AM
I wrote this useful macro to put iferror around an existing formula, I use it all the time:


Sub addiferror()
cform = ActiveCell.Formula
cform2 = Mid(cform, 2, 2555)
cform3 = "=iferror(" & cform2 & ",0)"


ActiveCell.Formula = cform3
End Sub





Why the 2555? Did you mean 255?

Or maybe



cform2 = Right(2, Len(cform) -1)

offthelip
08-12-2017, 06:13 AM
The 2555 was just big enough number to accomodate any length of formula that I was ever likely to write. I probably started off thinking of 255 because that is an obvious number, but realised that that might not be sufficient so put in another "5". I don't actually know what the length limit is for a formula in Excel. I have always found it sufficient, even when I use vba to write the formualae.

mdmackillop
08-12-2017, 06:54 AM
https://support.office.com/en-gb/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3


Length of formula contents
8,192 characters
Internal length of formula
16,384 bytes

mdmackillop
08-12-2017, 06:59 AM
@ kmlartigue

Apologies for hi-jacking your thread!

Paul_Hossler
08-13-2017, 06:11 AM
The 2555 was just big enough number to accomodate any length of formula that I was ever likely to write. I probably started off thinking of 255 because that is an obvious number, but realised that that might not be sufficient so put in another "5". I don't actually know what the length limit is for a formula in Excel. I have always found it sufficient, even when I use vba to write the formualae.

NP - it was just an odd number

Aflatoon
08-14-2017, 03:43 AM
Just FYI, you don't actually need a number there at all. Leave it out to just return all the rest of the text:


cform2 = Mid(cform, 2)

SamT
08-14-2017, 05:11 AM
ActiveCell.Formula = "=iferror(" & Mid(ActiveCell.Formula, 2) & ",0)"

offthelip
08-14-2017, 06:50 AM
That is what I like, reduce my 4 lines of code to 1 line. Can I submit the rest of my file of UDFs and useful macros for review please??

mdmackillop
08-14-2017, 07:02 AM
That should be fun! (How many years of VBA?) I'd be embarrassed to post some of mine.

Bob Phillips
08-14-2017, 11:31 AM
I'd be embarrassed to post any of mine with Rory lurking!

Aflatoon
08-14-2017, 12:16 PM
Ha! The other way around more like!

kmlartigue
08-16-2017, 02:42 PM
Thank you, I will try those suggestions out.

kmlartigue
08-16-2017, 02:42 PM
I ended up using this way and it works great.

Old Way:
If(IsError(Original Formula),0,Original Formula,?,?))))))))))... however many ))))))))) as needed

SamT
08-16-2017, 04:44 PM
:yay