Help with Range.("XX").Value = (Newbie)

[David Andrews]

I have a function that sums 3 values:

********
Public Function sum(UserRange) As Double

sum = 0

For Each cell In UserRange
sum = sum + cell.Value
Next cell

Range("H5").Value = 7 : THIS DOESNT WORK ???


mysum = sum

End Function
******
The call for it is in a cell (G4) , =sum(G1:G3)
I am trying to set the value of another cell in this function using
Range("H5").Value = 7,but it does nothing.

 

[Norman Jones]

Hi David,

A user defined function (UDF) can only return
a value to the calling cell; it cannot change its
envirinment or change another cell.

 

[Guest]

You are correct. It doesn't work. A function returns a value, but it can't
change an arbitrary cell on the worksheet.

 

[Dave Peterson]

You got your answer why your function won't work.

But I wouldn't name a UDF =sum() since excel has its own =sum() function.

 

[David Andrews]

thanks for the responses. I wasted alot of time on this..

Can one of you help with the syntax for passing a few different cell values
to a calling function

Cell Call:
=compute(C1,C3,C5)

*****
function compute( ??????)

'get the value of the 3 cells and do a calculation

end function
*****

 

[Norman Jones]

Hi David,

Perhaps try something like:

'=============>>
Public Function SumOfSquares( _
ParamArray myCells()) _
As Double
Dim i As Long, j As Long
Dim var As Variant

For i = LBound(myCells) To UBound(myCells)
If IsArray(myCells(i)) Then
For j = 1 To myCells(i).Count
var = myCells(i)(j)
If IsNumeric(var) Then
SumOfSquares = SumOfSquares + var ^ 2
End If
Next j
Else
SumOfSquares = SumOfSquares + myCells(i) ^ 2
End If
Next i
End Function
'<<=============