0

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.

Zack E
  • 696
  • 7
  • 23
PhB
  • 3
  • 1
  • 1
    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 Mar 04 '19 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 Mar 04 '19 at 16:25

1 Answers1

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:

img1

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

img2

dwirony
  • 5,487
  • 3
  • 21
  • 43
  • Of course... feel silly not to have though about it, it is obvious when reading the code. Thanks a lot ! – PhB Mar 05 '19 at 08:00
  • 1
    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 – PhB Mar 05 '19 at 08:03