VBA #VALUE!" error while using a Function

Question

Still one hair remaining... not for long

This :

Function Around(Compare As String)

Around = (Range(Compare).Value = Range(Compare).Offset(-1, 0).Value) Or (Range(Compare).Value = Range(Compare).Offset(1, 0).Value)

End Function

generates a #VALUE! in the cell that calls it

I cannot figure out why

Any clues ?

I think #Value error while accessing user defined function in VBA does not apply here.

This won't work if the Range(Compare) evaluates to something in row 1. The offset of -1 tries to access row 0, which doesn't exist. That's the only way I can get a #VALUE error out of this (although I'm only working with one worksheet - the unqualified Range's are also potentially problematic). — Comintern
– Comintern, Commented Mar 4, 2019 at 15:25
Good point. The error appears within the sheet, even far from top cell - and this is within a single sheet, I've done it in a minimalist way. — PhB
– PhB, Commented Mar 4, 2019 at 16:25

dwirony · Accepted Answer · 2019-03-04 17:26:32Z

0

I'm guessing you're typing the formula in like =Around(E8), when you need to type it in as =Around("E8") since Compare is a String:

If you want to type it without quotation marks, then you need to declare Compare as a Range and change some syntax:

Function Around(Compare As Range)

    Around = (Compare.Value = Compare.Offset(-1, 0).Value) Or (Compare.Value = Compare.Offset(1, 0).Value)

End Function

answered Mar 4, 2019 at 17:26

dwirony

5,4604 gold badges26 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PhB Over a year ago

Of course... feel silly not to have though about it, it is obvious when reading the code. Thanks a lot !

PhB Over a year ago

And btw, to get it working like a standard Excel function, I've also added the volatile instruction =Function Around(Compare As Range) Application.Volatile Around = (Compare.Value = Compare.Offset(-1, 0).Value) Or (Compare.Value = Compare.Offset(1, 0).Value) End Function

Collectives™ on Stack Overflow

VBA #VALUE!" error while using a Function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related