Microsoft Office Tutorials and References
In Depth Information
The Name of the Active Worksheet and Workbook
If you prefer to have the UDF act as the built-in RAND function would, that is, to recalculate
whenever another worksheet formula is recalculated or a cell is edited, you can insert the statement
Application.Volatile like so:
Function StaticRandom() As Double
Application.Volatile
StaticRandom = Int(Rnd() * 100)
End Function
Be aware that if the UDF is used in a lot of cells, Application.Volatile will add to the
workbook’s overall calculation effort, possibly resulting in longer recalculation times.
The name of the Active Worksheet and Workbook
A very common request is for a formula to return the name of the active worksheet or workbook.
This is a case where a UDF is still a worthy alternative even though there are formulas that can
handle this request, and the Application.Volatile statement would be included.
For the worksheet name, this formula is an option but it’s not easy to memorize or to enter correctly:
=MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,32)
Although the formula automatically updates itself when a sheet tab name changes, the workbook
must be named (saved at least once) or the formula will return a #VALUE! error.
The following code shows a UDF with the Application.Volatile statement that covers all the
bases. It updates itself when the worksheet tab changes, and the workbook does not need to be
named or saved for the UDF to work. Another advantage is that the formula =SheetName() is easy
to remember and to enter:
Function SheetName() As String
Application.Volatile
SheetName = ActiveSheet.Name
End Function
For the formula that returns the active workbook’s name, a lengthier and more difficult one to enter
properly is:
=MID(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1))+1,FIND
(“]”, CELL(“filename”,A1))-FIND(“[“,CELL(“filename”,A1))-1)
The workbook would need to be saved at least once for this formula to work.
The NameWB() function is much easier to remember and enter, and it’ll also do the job whether or
not the workbook has been saved:
= NameWB()
Its UDF is:
Function NameWB() As String
Application.Volatile
NameWB = ActiveWorkbook.Name
End Function
Search JabSto ::




Custom Search