Hi all,


I wish to write a Excel formula (VB function) that would take a source
range, and a destination cell as parameters.
The function would do some calculations and would return the result. But
would also write some additional text to the destination cell.

I am not sure what I am doing wrong, could someone please help me ....

I have written a small function similar to the one that I actually need.



Function test(src As Variant, dest As Variant) as Variant
test = "100"
Range(dest).Formula = "=sum(" & Range(src).Address & ")"
End Function




Thanks.

A function, when called from a worksheet, cannot "do" anything besides
return a value to the calling cell (or pop up a message box).

--

Vasant



"Suresh" <no-emails wrote in message
...
Hi all,


I wish to write a Excel formula (VB function) that would take a source
range, and a destination cell as parameters.
The function would do some calculations and would return the result. But
would also write some additional text to the destination cell.

I am not sure what I am doing wrong, could someone please help me ....

I have written a small function similar to the one that I actually need.



Function test(src As Variant, dest As Variant) as Variant
test = "100"
Range(dest).Formula = "=sum(" & Range(src).Address & ")"
End Function




Thanks.

When you call the function, what are you passing as arguments. You should be
passing strings (A1, B1:B100 as examples). If you pass a range object, it
will fail.

You could test for it

Function test(src As Variant, dest As Variant) As Variant
Dim srcRange As Range
Dim sumRange As Range

If TypeName(src) = "Range" Then
Set srcRange = src
ElseIf TypeName(src) = "String" Then
Set srcRange = Range(src)
Else
test = "Invalid src type"
Exit Function
End If

If TypeName(dest) = "Range" Then
Set sumRange = dest
ElseIf TypeName(dest) = "String" Then
Set sumRange = Range(dest)
Else
test = "Invalid dest type"
Exit Function
End If

srcRange.Formula = "=sum(" & sumRange.Address & ")"
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Suresh" <no-emails wrote in message
...
Hi all,


I wish to write a Excel formula (VB function) that would take a source
range, and a destination cell as parameters.
The function would do some calculations and would return the result. But
would also write some additional text to the destination cell.

I am not sure what I am doing wrong, could someone please help me ....

I have written a small function similar to the one that I actually need.



Function test(src As Variant, dest As Variant) as Variant
test = "100"
Range(dest).Formula = "=sum(" & Range(src).Address & ")"
End Function




Thanks.

Hi Suresh,
If you can reverse the process so that the cell where the
value should be changed is the cell with the function then you would
be okay; otherwise, one would have to use a macro and
that macro would probably be a change event macro.
http://www.mvps.org/dmcritchie/excel/event.htm#change
A change event occurs when you change the value of a cell,
a change of value due to a formula will not register as a change.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ...
A function, when called from a worksheet, cannot "do" anything besides
return a value to the calling cell (or pop up a message box).

--

Vasant



"Suresh" <no-emails wrote in message
...
Hi all,


I wish to write a Excel formula (VB function) that would take a source
range, and a destination cell as parameters.
The function would do some calculations and would return the result. But
would also write some additional text to the destination cell.

I am not sure what I am doing wrong, could someone please help me ....

I have written a small function similar to the one that I actually need.



Function test(src As Variant, dest As Variant) as Variant
test = "100"
Range(dest).Formula = "=sum(" & Range(src).Address & ")"
End Function




Thanks.