How to refer to an object property in a formula

  • Thread starter Thread starter James
  • Start date Start date
J

James

I wanted to creat a simple cell forumala such as:

=if(activesheet.name = "OCT03","current month","prior
month")

I get the #NAME result.

So is is possible to reference an object property directly
in a formaula rather than having to use a macro?
 
No, XL can't directly access VBA properties. you'll have to either
use a UDF or use built-in functions.

UDF:

Public Function SheetName(rng As Range) As String
Application.Volatile
SheetName = rng.Parent.Name
End Function

call as:

=IF(SheetName(A1) = "OCT03", "current month","prior month")


Built-in:

=IF(MID(CELL("filename",A1), FIND("]", CELL("filename",A1))+1,
255)="OCT03", "current month", "prior month")
 
Back
Top