Custom Function Not Updating

[Guest]

I have over 100 sheets in my workbook & need to refer to the sheet name in a
cell on each worksheet. I created the following function:

Function sn()
sn = ActiveSheet.Name
End Function

I grouped my sheets & entered the formula. On all sheets it displayed the
name of the first sheet. When I double click the cell & press enter, it
updates but this defeats the purpose of creating the function - I will still
have to go to each sheet & edit the formula cell.

Also - if I change the name of a sheet, the function does not display the
new name.

Is there some trick to updating custom functions?

 

[Guest]

try adding the line "Application.Volatile" to your sn() function. This will
force Excel to run your function each time a calculation event is triggered
in Excel.

Calling your sn() function a few times isnt very demanding on computer but
when you have 100 sheets (!), i.e. triggering 100 calls to this function on
each recalc event might result in temporary freezes of Excel, while Excel are
busy evaluating the function 100-times.

Check the documentation on Application.Volatile if you want more specific
(and maybe accurate) info.

 

[Dave Peterson]

How about:

Function sn()
sn = application.caller.parent.Name
End Function

Application.caller is the cell with the formula. The cell's parent is the
worksheet.

But you could use this, too:

http://www.contextures.com/xlfaqFun.html#SheetName
If the workbook has been saved.

(From Debra Dalgleish's site)

 

[Guest]

Just my two bits, but shouldn't the custom function also be volatile or it
won't recalculate properly?

 

[Dave Peterson]

In xl2003, I renamed a worksheet and the results of the function didn't change.
So, yep, if you want to use that UDF, making it volatile would seem reasonable.

But that means that it still could be one calculation behind. I'd use that
formula from Debra's site.