Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function Not Updating
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function Not Updating
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. "Lynn" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function Not Updating
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) Lynn wrote: 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? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function Not Updating
Just my two bits, but shouldn't the custom function also be volatile or it
won't recalculate properly? "Dave Peterson" wrote: 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) Lynn wrote: 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? -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function Not Updating
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. JMB wrote: Just my two bits, but shouldn't the custom function also be volatile or it won't recalculate properly? "Dave Peterson" wrote: 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) Lynn wrote: 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? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating Values For Custom Formulas | Excel Discussion (Misc queries) | |||
Custom Function not updating | Excel Worksheet Functions | |||
Custom Function not updating | Excel Worksheet Functions | |||
Updating Custom VBA Functions | Excel Programming | |||
Custom Worksheet Function not updating | Excel Programming |