VBA formula

[Suresh]

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.

 

[Vasant Nanavati]

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

 

[Bob Phillips]

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)

 

[David McRitchie]

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.