Clarification.... I wrote:
"Spint" wrote:
is there any way i can return a value from the function
and also inside the function some calculated value to be
set to particular cell.

No, not in a VBA function per se. You could do that in a VBA
sub(routine), but not one that is invoked by an Excel calculation
(through a VBA function) directly or indirectly.

Generally, Excel does not permit a VBA function to change the
state of Excel worksheets directly or indirectly.

I might not have said it clearly. I always have trouble explaining this.
It is __not__ as simple as "you cannot do this in a function" or "you can to
this only in a sub(routine)". It depends on the context in which the code
is executed.

Let me try again....

Generally, you cannot change Excel state -- for example, by changing the
value of a cell directly -- in VBA code when it is invoked from an Excel
formula directly or indirectly. It does not matter whether that VBA code is
in a function or sub(routine).

Conversely, you can change Excel state in VBA code that is not invoked from
an Excel formula directly or indirectly; for example, by executing a macro.

Consider the following example....

Function myFunc()
Call sub1
myFunc = 123
End Function

Sub mySub()
Call sub1
End Sub

Sub sub1()
Range("A1") = func1()
End Sub

Function func1()
Range("B1") = 456
func1 = 789
End Function

We get an error when we invoke sub1 and func1 indirectly using the Excel
formula =myFunc().

But there is no error when we invoke sub1 and func1 indirectly by executing
the macro mySub directly, for example by pressing alt+F8 in Excel.