UDF returns array that is too large or too small for the calling range

Question

The following function returns an array to a worksheet. I mark an area, type my function and Ctrl+Shift+Enter to get the cells filled with data from a recordset.

But if the selected area for my CSE function is larger than the returned recordset, I receive a #N/A. And if it is smaller, no warning is indicated.

Is there an easy way to replace the #N/A with "" values, and if a range of the array function smaller than the returned array - to display a warning?

Here is my current working function that returns an array from the recordset:

Function SQL(dataRange As Range, CritA As String, CritB As Double) As Variant

Application.Volatile

        Dim cn As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim currAddress As String
        Dim varHdr, varDat, varOut As Variant
        Dim nc, nr, i, j As Long

        SQL = Null

        currAddress = ActiveSheet.Name & "$" & dataRange.Address(False, False)

        strFile = ThisWorkbook.FullName
        strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
        & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=0"";"

        Set cn = CreateObject("ADODB.Connection")
        Set rs = CreateObject("ADODB.Recordset")

        rs.CursorLocation = adUseClient ' required to return the number of rows correctly
        cn.Open strCon

        strSQL = "SELECT * FROM [" & currAddress & "]" & _
                 "WHERE [A] =  '" & CritA & "' AND [B] >= " & CritB & " " & _
                 "ORDER BY 10 DESC"

        rs.Open strSQL, cn

        ' Process Column Headings
        nc = rs.Fields.Count
        ReDim varHdr(nc - 1, 0)
        For i = 0 To rs.Fields.Count - 1
            varHdr(i, 0) = rs.Fields(i).Name
        Next

        ' Get Rows from the Recordset
        nr = rs.RecordCount
        varDat = rs.GetRows

        ' Combing Header and Data and Transpose

        ReDim varOut(0 To nr, 0 To nc - 1)
        For i = 0 To nc - 1
            varOut(0, i) = varHdr(i, 0)
        Next




        For i = 1 To nr
            For j = 0 To nc - 1
               varOut(i, j) = varDat(j, i - 1)



            Next
        Next

      ' Optional alternative - write Output Array to Sheet2
      '  With Sheet2
      '      .Cells.Clear
      '      .Range("A1").Resize(nr, nc) = varOut
      '  End With

          SQL = varOut


        Erase varOut
        Erase varHdr
        Erase varDat

        rs.Close
        Set rs = Nothing
        Set cn = Nothing
End Function

Jean-Fran&#231;ois Corbett · Accepted Answer

If your output array is smaller than the calling range, you can just fill the unused portions of the output array with "".

If the calling range is too small, you can show a message box, or return an Excel error value, or... Depends what you want.

Example of how to do these things.

Function test()

    'Get interesting content
    Dim contentOut As Variant
    contentOut = [{1,2;3,4}] ' or a database connection, or whatever

    'Figure out size of calling range which will receive the output array
    Dim nRow As Long: nRow = Application.Caller.Rows.Count
    Dim nCol As Long: nCol = Application.Caller.Columns.Count

    'Error if calling range too small
    If nRow < UBound(contentOut, 1) Or nCol < UBound(contentOut, 2) Then
        'Popup message
        MsgBox "your range is too small."
        ' or return #VALUE! error
        test = CVErr(xlValue)
        ' or both or whatever else you want there to happen
        Exit Function
    End If

    'Initialise output array to match size of calling range
    Dim varOut As Variant
    ReDim varOut(1 To nRow, 1 To nCol)
    'And fill it with some background value
    Dim iRow As Long
    Dim iCol As Long
    For iRow = 1 To nRow
        For iCol = 1 To nCol
            varOut(iRow, iCol) = "" ' or "funny bear", or whatever
        Next
    Next

    'Put content in output array and return
    For iRow = 1 To UBound(contentOut, 1)
        For iCol = 1 To UBound(contentOut, 2)
            varOut(iRow, iCol) = contentOut(iRow, iCol)
        Next
    Next
    test = varOut
End Function

UDF returns array that is too large or too small for the calling range

Answers (2)

Related Questions