#VALUE Error on Custom Function

Question

Function HasStrike(Rng as Range) As Boolean
HasStrike = Rng.Font.Strikethrough
End Function

I am using a custom function to see if a column has a strikethrough. If the column has a strikethrough, it returns a boolean and I delete the row. However, I am getting a #VALUE error. The column I am checking only has the word "MASS" in it. Not sure what's going on.

User Screenshot

Does it work if you check one cell? Under what case does it fail? Can you show some screenshot examples? — Chrismas007, Feb 26 '15 at 21:13
Yes, it has worked before. I'm running a VB macro that ties to the boolean. — kmiao91, Feb 26 '15 at 21:15
Are you calling the function on a single cell or on a group of cells? — basodre, Feb 26 '15 at 21:16
@bsapaka You're right, it shouldn't return an error. but it is. — kmiao91, Feb 26 '15 at 21:18
Which is why I asked for screenshots of some content. Otherwise this is not reproducible. — Chrismas007, Feb 26 '15 at 21:19
@kmiao91 did you try it in a new/blank workbook? Is the function in a module and not a sheet? Seconded that we need more info — brietsparks, Feb 26 '15 at 21:20
http://imgur.com/2n9Uupw,1VkbYXz#1 I am pulling the cell from any column in image 1 Function is indeed in a module since it is a custom function. — kmiao91, Feb 26 '15 at 21:21
Where are you using the function? Is it a `Public Function`? — Chrismas007, Feb 26 '15 at 21:31
What is the code calling it? Are you passing it a single cell Range or something else? — Kelvin, Feb 26 '15 at 21:52
I have the function in one cell. It calls it from another cell in a separate worksheet. Then it does it for the entire column, cell by cell by dragging the formulas down. — kmiao91, Feb 26 '15 at 21:53

Tim Williams · Answer 1 · 2015-02-27T20:37:35.853

0

Your function will return #VALUE if the cell content has mixed formatting: some text is struck out and some not.

Select one of the "problem" input cells and go to the "Format Cells" >> Font tab. Is "Strikethrough" checked or does it look like below? That would indicate mixed formatting.

enter image description here

Try this maybe:

Function HasStrike(Rng As Range) As Boolean
    Dim v
    Application.Volatile '<< EDIT: added this
    On Error Resume Next
    v = Rng.Font.Strikethrough
    On Error GoTo 0
    HasStrike = v Or IsNull(v)
End Function

edited Feb 27 '15 at 20:37

answered Feb 27 '15 at 00:14

Tim Williams

154,628
8
97
125

Hmmm - It seems to have half worked. The function returns Boolean but the boolean isn't right. I'm getting false for cells that do have strikethroughs – kmiao91 Feb 27 '15 at 18:26
1

You have to make sure to recalculate the function - it may not trigger a recalculation if you just change the formatting (or the VBA code for the function), since you didn't change the *value* of the input cell. Maybe add `Application.Volatile` at the top of the function. – Tim Williams Feb 27 '15 at 20:37

#VALUE Error on Custom Function

1 Answers1