Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

"sub or function not defined" compile error in Excel macro

I am recieving the compile error of "sub or function not defined" when attempting to do an execution on a macro in Excel, however I do not understand why.

The debugger is listing this line of code as the problem:
"Function GetPossibleAnswers(ByVal iResponseGroupID As Integer, ByRef arAnswers() As Variant)"

Am I not able to pass by reference a variant array in this manner?

Here is the complete code:

Function NoNulls(vValue, bIsNumber)
'Purpose: Automatically convert null values to zero or empty string
' bIsNumber denotes if a 0 or an empty string should be used if null encountered
' otherwise returns non-null value
 If IsNull(vValue) Then
   If bIsNumber Then NoNulls = 0 Else NoNulls = ""
   NoNulls = vValue
 End If
End Function

Function GetPossibleAnswers(ByVal iResponseGroupID As Integer, ByRef arAnswers() As Variant)
  Const SQLCONNECTION As String = "Driver={SQL Server};Server=localhost;Database=QA;UID=SA;PWD="
  Dim strSP
  Dim rsAnswers As New ADODB.Recordset
  Dim objSQLDB As New ADODB.Connection
  Dim iCounter
  strSP = "spGetResponseGroupData " & iResponseGroupID
  rsAnswers.Open strSP, objSQLDB
  If Not rsAnswers.EOF Then
    iCounter = 0
    While Not rsAnswers.EOF
      arAnswers(i, 0) = NoNulls(rsQuestion("AnswerID"), 1)
      arAnswers(i, 1) = NoNulls(rsQuestion("Answer"), 0)
      arAnswers(i, 2) = NoNulls(rsQuestion("Explanation"), 0)
      arAnswers(i, 3) = NoNulls(rsQuestion("Scorable"), 1)
      iCounter = iCounter + 1
  End If
  Set rsAnswers = Nothing
  Set objSQLDB = Nothing
End Function

Sub test()
  Dim arAnswers(10, 4) As Variant
  Call GetPossibleAnswers(3, arAnswers())
  For intI = 0 To UBound(arAnswers())
    MsgBox arAnswers(intI, 1)
  Next intI
End Sub
1 Solution
DivaqsAuthor Commented:
By the way, I did try
"Function GetPossibleAnswers(ByVal iResponseGroupID As Integer, ParamArray arAnswers() As Variant)"

And still receive the same error.
A few things:
1) You have rsQuestion in the GetPossibleAnswers Function
2) You have i in the GetPossibleAnswers Function
3) You have the a different variable name in your second GetPossibleAnswers function params in your second comment.

It looks like a case of mixed up variable names to me...
I hope this helps.

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now