Write Hyperlink in VBA that Links to Function

I am trying to write a sub that creates a hyperlink to a function for each cell in a column. I keep getting the "reference isn’t valid" error message but cannot figure out why. This is an addition to existing code–all the existing code has been working fine in production. All the file paths and sheet names are correct too, and the function works as intended when I run it in the console (Alt+F11, then run through the function line-by-line with F8).

This sub takes a recordset from a SQL query and outputs the results to the worksheet. Then, I try to add a column with a hyperlink to the function below.


Sub outputToWs(rsPosDB As ADODB.Recordset, ws As Worksheet, headerRow As Integer)

Dim i As Integer
Dim colCalc As Integer

...

'Loops through recordset and outputs values to worksheet. I deleted most of the code to avoid noise
i = headerRow
While Not (rsPosDB.EOF)
    i = i + 1
        With rsPosDB
            If Left(!ID, 2) <> "AA" Then
                ws.Cells(i, colCalc).FormulaR1C1 = "=HYPERLINK(""#sendToCalc_Click()"", ""Calc"")"
            End If
            ....
        End With
Wend           
...
End Sub

The function below is what I’m trying to call from the hyperlink I create in the sub above:

Function sendToCalc_Click()
    
    Set sendToCalc_Click = Selection
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Output")
    

    'Copy value in column A to the Calculator workbook    
    Dim calcWB As Workbook
    Set calcWB = Workbooks.Open("C:\Users\myuser\Documents\Calculator v2.xlsm")

    calcWB.Sheets("Calculator").Range("C2") = ws.Cells(Selection.row, 15)
    
End Function

Many thanks in advance

>Solution :

Seems like opening another workbook is something you can’t do in a function called from a hyperlink. Although no error is raised when calling Workbooks.open(), the workbook is not opened and calcWB is Nothing, so trying to set the cell value raises an error.

Function sendToCalc_Click()
    
    Dim ws As Worksheet, calcWB As Workbook, c As Range
    
    On Error GoTo haveError
    
    Set c = Selection
    Set calcWB = Workbooks.Open("C:\Temp\Calculator v2.xlsm")
    Debug.Print "calcWB is nothing?", calcWB Is Nothing
    calcWB.Worksheets("Calculator").Range("C2") = c.EntireRow.Cells(15)
    Debug.Print "Set Cell"
    Set sendToCalc_Click = c
    Exit Function
    
haveError:
    Debug.Print Err.Description
    Set sendToCalc_Click = c
End Function

Output:

WB is nothing?              True
Object variable or With block variable not set

You get the "reference isn’t valid" message because the function fails to return a cell reference for the hyperlink to finish its job.

Leave a Reply