Divaqs
asked on
"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 = ""
Else
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;D atabase=QA ;UID=SA;PW D="
Dim strSP
Dim rsAnswers As New ADODB.Recordset
Dim objSQLDB As New ADODB.Connection
Dim iCounter
objSQLDB.Open SQLCONNECTION
strSP = "spGetResponseGroupData " & iResponseGroupID
rsAnswers.Open strSP, objSQLDB
If Not rsAnswers.EOF Then
iCounter = 0
While Not rsAnswers.EOF
arAnswers(i, 0) = NoNulls(rsQuestion("Answer ID"), 1)
arAnswers(i, 1) = NoNulls(rsQuestion("Answer "), 0)
arAnswers(i, 2) = NoNulls(rsQuestion("Explan ation"), 0)
arAnswers(i, 3) = NoNulls(rsQuestion("Scorab le"), 1)
rsAnswers.MoveNext
iCounter = iCounter + 1
Wend
rsAnswers.Close
End If
Set rsAnswers = Nothing
objSQLDB.Close
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
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 = ""
Else
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;D
Dim strSP
Dim rsAnswers As New ADODB.Recordset
Dim objSQLDB As New ADODB.Connection
Dim iCounter
objSQLDB.Open SQLCONNECTION
strSP = "spGetResponseGroupData " & iResponseGroupID
rsAnswers.Open strSP, objSQLDB
If Not rsAnswers.EOF Then
iCounter = 0
While Not rsAnswers.EOF
arAnswers(i, 0) = NoNulls(rsQuestion("Answer
arAnswers(i, 1) = NoNulls(rsQuestion("Answer
arAnswers(i, 2) = NoNulls(rsQuestion("Explan
arAnswers(i, 3) = NoNulls(rsQuestion("Scorab
rsAnswers.MoveNext
iCounter = iCounter + 1
Wend
rsAnswers.Close
End If
Set rsAnswers = Nothing
objSQLDB.Close
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"Function GetPossibleAnswers(ByVal iResponseGroupID As Integer, ParamArray arAnswers() As Variant)"
And still receive the same error.