UDF and range arrays as input

[XonsArgi]

Hi guys. I'm new here and i'd like to see if anyone know how to make this work.
In a Module i made this function:

Function PProduct(arr() As Variant) As Double
Dim soma As Double
Dim i As Long
soma = 1
For i = LBound(arr, 1) To UBound(arr, 1)
soma = soma * arr(i, 1)
Next i
PProduct = soma
End Function

And when i call this function from a cell like:
=PProduct(A1:C3)

I got the #VALUE! problem.

The range A1:C3 is number only, populated with 1 2 3; 4 5 6; 7 8 9

In an attempt to solve the problem i wrote this sub

Sub test()
Dim arr() As Variant
arr = Sheet1.Range("A1:C3")
Debug.Print PProduct(arr)
End Sub

wich returns 28

What do I have to do to make this UDF work in a worksheet cell call?

 

[shg]

Welcome to the board.

Function PProduct(arr As Variant) As Double
  PProduct = WorksheetFunction.Product(arr)
End Function

E.g.,

	A​	B​	C​	D​	E​	F​
1​	5​	3​	2​		1620​	E1: =PRODUCT(A1:C3)
2​	3​	1​	3​		1620​	E2: =PProduct(A1:C3)

Or

Function PProduct(arr As Variant) As Double
  Dim v As Variant
  
  PProduct = 1
  For Each v In arr
    If VarType(v) = vbDouble Then PProduct = PProduct * v
  Next v
End Function

 

[Rick Rothstein]

Does this do what you want...

Function PProduct(Rng As Range) As Double
  PProduct = WorksheetFunction.Product(Rng)
End Function

 

[XonsArgi]

I think i didn't made myself clear. This is only a example function i'm using Rick, I'm actually tryin' to do a system bigger than that.

shg your code will work only when i use a range as input in the function, but i would like to call this function not only from the worksheet cell, but from the vba a well. And i will have arrays as input, not only ranges.
What I really want is a way to use a Range as input parameter as well as arrays as input parameters.

 

[XonsArgi]

Still lookin for the answer if there's one

 

[XonsArgi]

Still waiting

 

[XonsArgi]

As someone may want to know, I used a different approach for the answer.

As I need to have a different behavior in cell call and VBA i used option private module in the beginning of the module, and in a different module i call this function for public cell call. It's not as i would like to work, but it's well enought.

 

[Rick Rothstein]

but i would like to call this function not only from the worksheet cell, but from the vba a well. And i will have arrays as input, not only ranges.

I think the following will address the question you are looking to solve...

Function MyFunction(Arr As Variant)

  If TypeOf Arr Is Range Then Arr = Arr

[B][COLOR="#008000"]  ' At this point in the code, if a range was
  ' passed into the Arr argument, the Arr will
  ' now contain a two-dimensional array of values
  
  ' Rest of your code goes here[/COLOR][/B]  

End Function

 

[XonsArgi]

Thank you, Rick. That's exactly what i was looking for.