Stop executing remaining macros if recordset.EOF is TRUE

Question

I have an Excel VBA code which has the following function. Of course there are other subs/ functions based on the recordSet which are used to populate the different spreadsheets in the workbook.

Issue: When the recordSet is Empty then a run-time error '13' Type: mismatch comes up. How can I add to this code to check if empty then stop populating the sheet.

Function GetSqlResultsAsArray(qry As String) As Variant
    
    Dim conn As New ADODB.Connection
    Dim recordSet As ADODB.recordSet
    
    'connection string here
    conn.Open ConnectionString:=""
    
    Set recordSet = conn.Execute(qry)
    If Not recordSet.EOF And Not recordSet.BOF Then
         GetSqlResultsAsArray = recordSet.GetRows
    End If
    conn.Close

End Function

The next relevant code is: I want to stop resultsArray getting populated if the above code returns empty or null to void the Run-time error '13'. I do not know how to amend the below to do this.

Private Sub GetResultsData()

    'sub used to bring back a recordset with all results data based on the user criteria slected
    
    resultSql = GetResultSqlStatement(customer, product, startDate, endDate, productType)
    
    productSql = GetProductSqlStatement(product, customer, startDate, endDate)
    
   
    
    Dim resultsArray() As Variant
    
    resultsArray = GetSqlResultsAsArray(resultSql)
    
    resultsArray = TransposeArray(resultsArray)

    'Format data
    
    wsData.Range("A2").Resize(lr + 1, lc + 1).Value = resultsArray
    wsData.ListObjects.Add(xlSrcRange, wsData.Range(wsData.Cells(1, 1), wsData.Cells(lr + 2, lc + 1)), , xlYes).Name = "DataTable"
   
End Sub

Answer 1

Move your array to a parameter and change your ADO function to a boolean.

Function GetSqlResultsAsArray(qry As String, ResultsArray()) As Boolean
    
    Dim conn As New ADODB.Connection
    Dim recordSet As ADODB.recordSet
    
    'connection string here
    conn.Open ConnectionString:=""
    
    Set recordSet = conn.Execute(qry)
    
    If Not recordSet.EOF And Not recordSet.BOF Then
        GetSqlResultsAsArray = True
        ResultsArray = recordSet.GetRows
    End If
    conn.Close

End Function

Then you can capture whether there are any results in the calling procedure by testing the value of HasRecords.

HasRecords = GetSqlResultsAsArray(resultSql, resultsArray)

If HasRecords Then
     resultsArray = TransposeArray(resultsArray)
    'Format data
    wsData.Range("A2").Resize(lr + 1, lc + 1).Value = resultsArray
    wsData.ListObjects.Add(xlSrcRange, wsData.Range(wsData.Cells(1, 1), 
    wsData.Cells(lr + 2, lc + 1)), , xlYes).Name = "DataTable"
End If

Or you can skip the HasRecords variable.

If GetSqlResultsAsArray(resultSql, resultsArray) Then
     resultsArray = TransposeArray(resultsArray)
    'Format data
    wsData.Range("A2").Resize(lr + 1, lc + 1).Value = resultsArray
    wsData.ListObjects.Add(xlSrcRange, wsData.Range(wsData.Cells(1, 1), 
    wsData.Cells(lr + 2, lc + 1)), , xlYes).Name = "DataTable"
End If