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.

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.


---
Regards,
Norman

"David Andrews" wrote in message
news:V%z8i.1968$FU4.119@trndny02...
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.

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

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 wrote:

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.

--

Dave Peterson

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
*****

"David Andrews" wrote in message
news:V%z8i.1968$FU4.119@trndny02...
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.

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
'<<=============


---
Regards,
Norman


"David Andrews" wrote in message
news:5RA8i.3810$WE4.2054@trndny01...
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
*****

"David Andrews" wrote in message
news:V%z8i.1968$FU4.119@trndny02...
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.