I need help about a subject. I won't state my real problem here since it will take long time to you. But if I understand the below problem's solution, I guess I can solve my real problem.


I want to define my own function in excel. Let name of that function be "myfunc". Now, let use this function in a cell in a sheet. I want it return "1" in the initial cell. When I pass this function to below cell, then I want it return "2". As I pass it below I want return value increase 1 by 1. How can be defined such function in vba.


I will be very glad if you help me. Thanks a lot.

I wrote such a function:

Function myfunc()
a=ActiveCell.Row
myfunc=a
End Function


However, that does not work as I want. In active cell it works fine, but if I fetch the cell from it corner to below cell, it does not work. How can I do that? Thanks alot. That is important to me.

hi Oercim,

Am Tue, 5 Mar 2013 03:54:27 -0800 (PST) schrieb oercim:

However, that does not work as I want. In active cell it works fine, but if I fetch the cell from it corner to below cell, it does not work. How can I do that? Thanks alot. That is important to me.

look that there is a empty cell above and try the macro:

Function myfunc(Optional rngC As Range) As Long
If rngC Is Nothing Then Set rngC = Application.Caller
myfunc = rngC.Offset(-1, 0) + 1
End Function

Better way:
Write 1 in the selected cell and drag it down with pressed CTRL-Button


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hello Claus, that worked very well. I am very glad. Thanks for the help. I just want to learn ne last thing. I searched in google, but I couldn't understand "Caller" at your code. What is its function? What does it do?

Hi Oercim,

Am Tue, 5 Mar 2013 04:55:29 -0800 (PST) schrieb oercim:

Hello Claus, that worked very well. I am very glad. Thanks for the help. I just want to learn ne last thing. I searched in google, but I couldn't understand "Caller" at your code. What is its function? What does it do?

the Application.Caller is the cell from which you call your function.

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thanks a lot Claus. That was very useful for me. Best wishes.