2

I want to populate a column with hyperlinks that execute a VBA macro, which I have achieved by using the HYPERLINK function:

=HYPERLINK("#AddReview()";"Add Review")

Since the HYPERLINK function will not invoke the Workbook_SheetFollowHyperlink method, I created my own function as follows (simplified):

Public Function AddReview()

  MsgBox Selection.Cells.Row

End Function

When the hyperlink is clicked the function does execute successfully, but the problem is that the function executes 3 times until it fails on the third run with the message Reference isn't valid.

Does anybody have an idea why it would keep executing? I have not been able to find an answer.

Elmar Naude
  • 35
  • 3
  • I've recreated your issue - what I find interesting is the fact I placed `Stop` above your `MsgBox ...` line and execution when right past it. – K.Dᴀᴠɪs Nov 15 '18 at 08:19
  • Is there any particular reason you cannot use the other 'Hyperlink Method'? - The one that would fire the event `FollowHyperlink`? – K.Dᴀᴠɪs Nov 15 '18 at 08:29
  • 2
    I'm guessing you are uncovering something about the call stack. The function is always called 3 times in quick succession irrespective of STOP or Wait commands within. Even conditional logic for Exit Function are ignored. – QHarr Nov 15 '18 at 08:30
  • 1
    @QHarr Yeah, even used `End` which would typically halt all execution - yet the msgbox still showed three times. – K.Dᴀᴠɪs Nov 15 '18 at 08:33
  • 2
    @K.Dᴀᴠɪs Annoying isn't it, cos you can't even inspect the stack! – QHarr Nov 15 '18 at 08:34
  • @K.Dᴀᴠɪs I generate the sheet programmatically, so using the function makes my life alot easier. The solution provided by BigBen solved the problem though. Would still be interesting to know why this happens. – Elmar Naude Nov 15 '18 at 10:23

1 Answers1

2

Not sure of the root cause, but similar to this answer, using Set AddReview = Selection fixes the behavior.

Public Function AddReview()
    Set AddReview = Selection
    MsgBox Selection.Cells.Row
End Function
BigBen
  • 46,229
  • 7
  • 24
  • 40