Call VBA function in a cell

[yinkajewole]

How can i call a vba function in the middle of a cell value in the formula bar.
for instance, if i have a function that calculate an area in vba. if i put =area(3,4) on the formula bar it gives me the result. But now, assuming I want to sum the result of two areas in a cell, how do i go about this.
This did not work =sum(area(3,4),area(6,5))

 

[Norie]

That should work, what happens when you try it?

 

[yinkajewole]

I'm sorry, I did not specify my need completely.

However, this is what I intend to do. I want to rank the cell J25 in all the sheets and this is my formula =RANK(J25,'Sheet1:Sheet10'!J25,0).
While the sheet1 is the first sheet and sheet10 is the last sheet.
What I want now is that instead of typing the sheet name myself, I wrote a VBA function that gives me the first and last sheet names.

Function FirstP()
FirstP = Application.Sheets.Item(1).Name
End Function
Function LastP()
LastP = Application.Sheets(Sheets.Count).Name
End Function

So, substituting the VBA function into the formula gives me error =RANK(J25,FirstP():LastP()!J25,0).

The reason for all of these is that there are many workbooks I want to work on but they have different Sheet names.

 

[Norie]

All your functions return are strings and I'm afraid you can't use them as you have to reference sheets.

 

[yinkajewole]

and there is no workaround for it?

 

[Norie]

You could try INDIRECT I suppose but I'm not sure how that would work.

Perhaps something like this.

=RANK(J25,INDIRECT(FirstP() & ":" & LastP() & "!J25"),0)

 

[yinkajewole]

You could try INDIRECT I suppose but I'm not sure how that would work.

Perhaps something like this.

=RANK(J25,INDIRECT(FirstP() & ":" & LastP() & "!J25"),0)

This did not work either. error "#REF!"

 

[Norie]

I've had a look and found out that INDIRECT can't be used for 3D references.