Can VBA ranges in Excel be operated on?

user_18115563 0
Viewed 44

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:

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.

vba excel
2 Answers

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.

user_116950490

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
user_98140690
Related