'Can VBA ranges in Excel be operated on?

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.

excel vba

Solution 1:^[1]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	tinazmu

'Can VBA ranges in Excel be operated on?

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]