Custom Function Failing After Recursion

[Abhinay]

Hi,

I am implementing a custom function on excel which will extract data from SQL server using query table. The query table on refresh loops (recursion) and the query fails with "Application-defined or object-defined error" on the second iteration.

Below is the code:



Function CreditGetTradeNotional(cheyneReference As String)

Dim stringConn As String

Dim stringSQL As String

Dim query As QueryTable


On Error GoTo ErrHand

'stringConn = "PROVIDER= SQLNCLI;Server=CCM-UATSQL5E2\E2Analysis;Database=Analysis; Trusted_Connection=yes;"

'Use the SQL Server OLE DB Provider.
stringConn = "OLEDB;PROVIDER=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False; DATA SOURCE=CCM-UATSQL5E2\E2Analysis;INITIAL CATALOG=Analysis"

'Connect to the Pubs database on the local server.
'stringConn = stringConn & "Persist Security Info=False; DATA SOURCE=CCM-UATSQL5E2\E2Analysis;INITIAL CATALOG=Analysis"

'Use an integrated login.
'stringConn = stringConn & " Trusted_Connection=yes;"

If (ActiveCell.Column = 2) Then

stringSQL = "select notional from tblDefaultSwap where cheynereference = " & "'" & cheyneReference & "'"

'Range("B7").Select


Set query = ActiveSheet.QueryTables.Add(Connection:=stringConn, Destination:=Range("B7"), Sql:=stringSQL)
query.FieldNames = False
query.FillAdjacentFormulas = False
query.RefreshOnFileOpen = True
query.BackgroundQuery = True
query.RefreshStyle = xlInsertDeleteCells
query.SaveData = True
query.AdjustColumnWidth = True
query.PreserveColumnInfo = False
'End With

If Not query.Refreshing Then
query.Refresh BackgroundQuery:=False
End If

End If


ErrHand:

Set CreditGetTradeNotional = Nothing

MsgBox "Connection not propertly defined.", vbExclamation
Err.Clear

Exit Function


End Function

 

[GlennUK]

Please explain why you've written this as a Function? This appears to be a Sub in design.

 

[Abhinay]

I want to use it like a function on excel.

=CreditGetTradeNotional(A7)