Can VBA ranges in Excel be operated on?-CodePudding

I'm looking to write a user-defined Excel function that takes four inputs (2 values and 2 user-selected ranges) and plugs them into this formula:

wA and MA are single values, whereas I'd like to be able to select a range of wi and Mi and have the function perform the division and summation.

Here's what I've tried:

Function ConvertWtoX(wA As Double, MA as Double, wi As Range, Mi As Range) As Variant

      ConvertWtoX = ((wA/MA)/Application.WorksheetFunction.SumProduct(wi * (1 / Mi))
      'I have also tried SumProduct(wi/Mi)

End Function

Entering ranges into SUMPRODUCT within the sheet has returned the values I want, even with the division component, but it doesn't seem to translate into the VBA code. From what I can tell, the Range objects don't play nicely with operations like division.

Is there a way around this problem? I'm very new to programming in general, so any advice is welcome.

CodePudding user response：

Include your input data so that those trying to help don't have to come up with test data. Also SumProduct(wi*(1/Mi)) is asking VBA to perform the multiplication (and inversion), and it can't do that for ranges, you can try:

      ConvertWtoX = ((wA / MA) / ActiveSheet.Evaluate("=SumProduct(" & wi.Address & ",1/" & Mi.Address & ")"))

I don't know if it will perform well, but it should calculate.

CodePudding user response：

A SumProduct Division UDF

Both ranges have to be of the same size. Choosing the first cell of the second range is enough.

Function ConvertWtoX( _
    ByVal wA As Double, _
    ByVal MA As Double, _
    ByVal wi As Range, _
    ByVal Mi As Range) _
As Variant
    Application.Volatile
    
    Dim rCount As Long: rCount = wi.Rows.Count
    Dim cCount As Long: cCount = wi.Columns.Count
    
    ' Correct the second range.
    Dim mirg As Range: Set mirg = Mi.Cells(1).Resize(rCount, cCount)
    
    Dim wData As Variant, mData As Variant
    If rCount   cCount = 2 Then ' one cell
        ReDim wData(1 To 1, 1 To 1): wData(1, 1) = wi.Value
        ReDim mData(1 To 1, 1 To 1): mData(1, 1) = mirg.Value
    Else
        wData = wi.Value: mData = mirg.Value
    End If
    
    Dim r As Long, c As Long
    Dim Total As Double
    
    For r = 1 To rCount
        For c = 1 To cCount
            Total = Total   wData(r, c) / mData(r, c)
        Next c
    Next r
    
    ConvertWtoX = wA / MA / Total

End Function