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?
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)
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.