Как записать функцию СУММПРОИЗВ в макросе

Question

Sub СуммПроизв()
   For i = 4  to 60
      Cells(i, 7) = WorksheetFunction.SumProduct(Range(Cells(1, 17), Cells(1, 80)), Range(Cells(i, 17), Cells(i, 80)))
   Next i

так не работает

Answer 1

Это особенность применения данной функции. При расчете SUMPRODUCT в VBA конструкция Range(cells(),cells())не работает. Но диапазон можно записать по-другому:

Range(столбец & строка & ":" & столбец & строка)

В этом случае функция отработает корректно:

Sub СуммПроизв()
    Dim i As Long

    For i = 4 To 60
        Cells(i, 7) = Application.SumProduct(Range("Q" & i & ":CB" & i), Range("Q" & i & ":CB" & i))
    Next i
End Sub

Но в целом подход неправильный. Вызов функций листа в коде - процесс медленный. Если уж применяется VBA, то и считать можно только своими средствами:

Sub SumProduct_()
    Dim aData(), aResult()
    Dim i As Long, j As Long

    aData = Range("Q4:CB60").Value
    ReDim aResult(1 To UBound(aData), 1 To 1)

    For i = 1 To UBound(aData)
        For j = 1 To UBound(aData, 2)
            aResult(i, 1) = aResult(i, 1) + aData(i, j) ^ 2
        Next j
    Next i

    Range("G4").Resize(UBound(aData), 1).Value = aResult
End Sub

На небольших диапазонах выигрыш по времени при замене функции листа на обработку в массиве практически незаметен.

БЛОГ НА HUSL

Как записать функцию СУММПРОИЗВ в макросе

Ответы (1 шт):