IsErr|IsError Function Returns True in VBA but not when used in a Cell

[kaletm]

Hi,

I am using the Application.WorksheetFunction.IsErr(range) worksheet function from VBA and it is returning True for a particular cell that does not really contain an error. I try using the function as a normal formula from a cell =ISERR(ref) and it returns false. The same applies for the function IsError

This cell contains 538 characters. I have a hunch this has something to do with it. This cell will display pounds(#####) when formatted as text but shows as it should when formatted as s general. But this hunch could be completely wrong but it's the only thing special about the cell.

I'm positive it's not faulty code. If I try selecting the cell and then from the IDE immediate window type:

?Application.WorksheetFunction.IsErr(activecell)
True is returned.

Any help would be greatly appreciated. If it helps, below the comment line is the cells content

'****************************************************
ESTABLISH ZERO ENERGY STATE BEFORE STARTING PM TASK, ZERO ENERGY STATE REQUIRES SHUTTING OFF AND DRAINING ALL STORED ENERGY FROM AIR, HYDRAULIC FLUID, COOLANT, VACUUM, OR ELECTRICAL ENERGY, AND TESTING THE MACHINE BEFORE BEGINNING WORK. PLEASE PROVIDE DESCRIPTION AND LOCATION OF THOSE ENERGY SOURCES YOU SECURE BEFORE BEGINNING WORK. PLEASE REFER TO MSDS FOR PROPER PPE ON CHEMICALS BEING USED IN OR AROUND THE EQUIPMENT. ENSURE ALL MACHINE GUARDS ARE IN PLACE AND IN GOOD CONDITION. PROVIDE FEEDBACK OF ANY ITEMS THAT HAVE CHANGED.

[kaletm]

A follow-up: I did some more testing with this and found this error occurs when the character length of the cell reaches 256 characters. Since this apears to be a clear cut bug with Excel, I decided to workaround this problem and just do the slightly less aesthetic approach of using a temp worksheet to create an =IsErr formula in a cell and use the .value property of the cell to check for errors. Ugly but at least it works.

[shg]

... and just do the slightly less aesthetic approach of using a temp worksheet to create an =IsErr formula

Why do you need to do that rather than just use IsError(cell.Value)?

[Andy Pope]

to resolve your code issue use

Please Login or Register  to view this content.