How to output an array that a function returns in VBA?

Question

Visual Basic

Hello. I read that a function in VBA can display the calculated value only in the cell from where it was called. To display some auxiliary variables, you need to make the function return an array. If you need to display only the main value, we call it as usual from one cell. If additional variables are needed, select, for example, 2 cells, write the desired function, press Ctrl + Shift + Enter. But instead of the main value and the additional one, I have the main + main value displayed, although the function shows the main + additional


A, it is necessary that it be 14390 and 0.97
The code itself:

Function adequacy(MinPrice As Range, MaxPrice As Range, DeliveryPricesRange As Range)
 Dim sglStart As Single, w&, r&, name, ratio As String, final As Object, objRegExp As Object, rg As Range, a
    Dim Index As Integer
    Dim MinusRub As Long
    Dim Price(1 To 2, 1 To 1)
    Price(1, 1) = 0
    MinusRub = 40
    Col = 7
    IfMore30 = 3
    ratio = MinPrice / MaxPrice
    Price(2, 1) = ratio
    If ratio < 0.5 Then
        For Index = Col To 1 Step -1
            Select Case Index
                Case Is = 7
                    Price(1, 1) = CLng(DeliveryPricesRange(Index))
                    If Price(1, 1) <> 0 Then
                        Exit For
                    End If
                Case Else
                    Price(1, 1) = CLng(DeliveryPricesRange(Index)) - MinusRub
                    If Price(1, 1) + 40 <> 0 Then
                        Exit For
                    End If
            End Select
        Next Index
    Else
        For Index = 1 To IfMore30
            Price(1, 1) = CLng(DeliveryPricesRange(IfMore30 - Index + 1))
            If Price(1, 1) <> 0 Then
                Exit For
            End If
        Next Index
    End If

    adequacy = Price
End Function

PS you need to check how I create an array 2 * 1, or 1 * 2, the problem was this

Answer 1

If the array is two-dimensional, then the column comes first, and then the row, i.e. a(2,3) is the 2nd column 3rd row. Most likely this is the issue. Try this demo code:

Public Function a()
    Dim s(1 To 5, 1 To 5)
    For i = 1 To 5
        For j = 1 To 5
            s(i, j) = CStr(i) + " " + CStr(j)
        Next j
    Next i
    a = s
End Function