Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Cell Functions
This problem is in two parts (excel 2003);
1. I have written a small macro that calculates the number of sheets in a workbook (writen in a macro module): Function NumberOfSheets() As Integer NumberOfSheets = ActiveWorkbook.Worksheets.Count End Function and am using this function in a cell a worksheet using the following notation: "=NumberOfSheets()" issue is that the worksheet will not 'recall' the function unless I select and modify the cell (or the like). How do I make this function update dynamically per all other cell functions - or at least if a sheets is deleted or created? 2. Similar to the above I am wanting to write a macro than enumerates each sheet in order as they are presented in the workbook: Function SheetNumber() As Integer Dim WS As Worksheet Dim i As Integer Set WS = ActiveSheet For i = 1 To NumberOfSheets If WS.Name = ActiveWorkbook.Worksheets(i).Name Then SheetNumber = i Exit Function End If Next i SheetNumber = -1 End Function The 'for next' loop seems a pretty clumsy way to do this, but I can't think of a better alternative. Any ideas. This function also needs to dynamically refresh so am hoping solution above is applicable to this. Thanks Jack Clift |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Cell Functions
Function NumberOfSheets() As Integer
Application.Volatile NumberOfSheets = ActiveWorkbook.Worksheets.Count End Function if all you have in the workbook are worksheets Function SheetNumber() As Integer Application.Volatile SheetNumber = Activesheet.Index End function this falls apart if you have none worksheets in the workbook. Function SheetNumber() As Integer Application.Volatile if sheets.count = worksheets.count then SheetNumber = Activesheet.Index else ' your looping method end if End function application.Volatile makes the function volatile. It gets recalculated on every calculate event that would normally include that cell. -- Regards, Tom Ogilvy "Jack Clift" wrote: This problem is in two parts (excel 2003); 1. I have written a small macro that calculates the number of sheets in a workbook (writen in a macro module): Function NumberOfSheets() As Integer NumberOfSheets = ActiveWorkbook.Worksheets.Count End Function and am using this function in a cell a worksheet using the following notation: "=NumberOfSheets()" issue is that the worksheet will not 'recall' the function unless I select and modify the cell (or the like). How do I make this function update dynamically per all other cell functions - or at least if a sheets is deleted or created? 2. Similar to the above I am wanting to write a macro than enumerates each sheet in order as they are presented in the workbook: Function SheetNumber() As Integer Dim WS As Worksheet Dim i As Integer Set WS = ActiveSheet For i = 1 To NumberOfSheets If WS.Name = ActiveWorkbook.Worksheets(i).Name Then SheetNumber = i Exit Function End If Next i SheetNumber = -1 End Function The 'for next' loop seems a pretty clumsy way to do this, but I can't think of a better alternative. Any ideas. This function also needs to dynamically refresh so am hoping solution above is applicable to this. Thanks Jack Clift |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Cell Functions
Thanks Tom,
I tried your solution below: SheetNumber = Activesheet.Index but for some reason it returns 5 with a workbook with 4 worksheets. Any ideas - the solution is certainly neater and will use it if I can solve the above. With regard the application.volatile - how do I make the cell initiate a calculate event? At the moment the cell function reads like: =sheetnumber() & " of " & NumberOfSheets() and it is not recalculating. Thanks for your time JC "Tom Ogilvy" wrote: Function NumberOfSheets() As Integer Application.Volatile NumberOfSheets = ActiveWorkbook.Worksheets.Count End Function if all you have in the workbook are worksheets Function SheetNumber() As Integer Application.Volatile SheetNumber = Activesheet.Index End function this falls apart if you have none worksheets in the workbook. Function SheetNumber() As Integer Application.Volatile if sheets.count = worksheets.count then SheetNumber = Activesheet.Index else ' your looping method end if End function application.Volatile makes the function volatile. It gets recalculated on every calculate event that would normally include that cell. -- Regards, Tom Ogilvy "Jack Clift" wrote: This problem is in two parts (excel 2003); 1. I have written a small macro that calculates the number of sheets in a workbook (writen in a macro module): Function NumberOfSheets() As Integer NumberOfSheets = ActiveWorkbook.Worksheets.Count End Function and am using this function in a cell a worksheet using the following notation: "=NumberOfSheets()" issue is that the worksheet will not 'recall' the function unless I select and modify the cell (or the like). How do I make this function update dynamically per all other cell functions - or at least if a sheets is deleted or created? 2. Similar to the above I am wanting to write a macro than enumerates each sheet in order as they are presented in the workbook: Function SheetNumber() As Integer Dim WS As Worksheet Dim i As Integer Set WS = ActiveSheet For i = 1 To NumberOfSheets If WS.Name = ActiveWorkbook.Worksheets(i).Name Then SheetNumber = i Exit Function End If Next i SheetNumber = -1 End Function The 'for next' loop seems a pretty clumsy way to do this, but I can't think of a better alternative. Any ideas. This function also needs to dynamically refresh so am hoping solution above is applicable to this. Thanks Jack Clift |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Cell Functions
Tom,
Have spent a bit more time on this - neither my nor your solution is going to work as it relies on the 'activesheet' and hence updates all sheets with this number, not each sheet to its own. Any ideas? is there someway to use the 'Me' keyword to differentiate between which instance is calling the function "Tom Ogilvy" wrote: Function NumberOfSheets() As Integer Application.Volatile NumberOfSheets = ActiveWorkbook.Worksheets.Count End Function if all you have in the workbook are worksheets Function SheetNumber() As Integer Application.Volatile SheetNumber = Activesheet.Index End function this falls apart if you have none worksheets in the workbook. Function SheetNumber() As Integer Application.Volatile if sheets.count = worksheets.count then SheetNumber = Activesheet.Index else ' your looping method end if End function application.Volatile makes the function volatile. It gets recalculated on every calculate event that would normally include that cell. -- Regards, Tom Ogilvy "Jack Clift" wrote: This problem is in two parts (excel 2003); 1. I have written a small macro that calculates the number of sheets in a workbook (writen in a macro module): Function NumberOfSheets() As Integer NumberOfSheets = ActiveWorkbook.Worksheets.Count End Function and am using this function in a cell a worksheet using the following notation: "=NumberOfSheets()" issue is that the worksheet will not 'recall' the function unless I select and modify the cell (or the like). How do I make this function update dynamically per all other cell functions - or at least if a sheets is deleted or created? 2. Similar to the above I am wanting to write a macro than enumerates each sheet in order as they are presented in the workbook: Function SheetNumber() As Integer Dim WS As Worksheet Dim i As Integer Set WS = ActiveSheet For i = 1 To NumberOfSheets If WS.Name = ActiveWorkbook.Worksheets(i).Name Then SheetNumber = i Exit Function End If Next i SheetNumber = -1 End Function The 'for next' loop seems a pretty clumsy way to do this, but I can't think of a better alternative. Any ideas. This function also needs to dynamically refresh so am hoping solution above is applicable to this. Thanks Jack Clift |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Cell Functions
Jack,
You want the index of the WS that the UDF is on ? Public Function GetMyIndex1(AnyRange As Range) As Long GetMyIndex = AnyRange.Parent.Index End Function 'Or Public Function GetMyIndex2() As Long GetMyIndex = Application.Caller.Parent.Index End Function Add error trapping. NickHK "Jack Clift" wrote in message ... Tom, Have spent a bit more time on this - neither my nor your solution is going to work as it relies on the 'activesheet' and hence updates all sheets with this number, not each sheet to its own. Any ideas? is there someway to use the 'Me' keyword to differentiate between which instance is calling the function "Tom Ogilvy" wrote: Function NumberOfSheets() As Integer Application.Volatile NumberOfSheets = ActiveWorkbook.Worksheets.Count End Function if all you have in the workbook are worksheets Function SheetNumber() As Integer Application.Volatile SheetNumber = Activesheet.Index End function this falls apart if you have none worksheets in the workbook. Function SheetNumber() As Integer Application.Volatile if sheets.count = worksheets.count then SheetNumber = Activesheet.Index else ' your looping method end if End function application.Volatile makes the function volatile. It gets recalculated on every calculate event that would normally include that cell. -- Regards, Tom Ogilvy "Jack Clift" wrote: This problem is in two parts (excel 2003); 1. I have written a small macro that calculates the number of sheets in a workbook (writen in a macro module): Function NumberOfSheets() As Integer NumberOfSheets = ActiveWorkbook.Worksheets.Count End Function and am using this function in a cell a worksheet using the following notation: "=NumberOfSheets()" issue is that the worksheet will not 'recall' the function unless I select and modify the cell (or the like). How do I make this function update dynamically per all other cell functions - or at least if a sheets is deleted or created? 2. Similar to the above I am wanting to write a macro than enumerates each sheet in order as they are presented in the workbook: Function SheetNumber() As Integer Dim WS As Worksheet Dim i As Integer Set WS = ActiveSheet For i = 1 To NumberOfSheets If WS.Name = ActiveWorkbook.Worksheets(i).Name Then SheetNumber = i Exit Function End If Next i SheetNumber = -1 End Function The 'for next' loop seems a pretty clumsy way to do this, but I can't think of a better alternative. Any ideas. This function also needs to dynamically refresh so am hoping solution above is applicable to this. Thanks Jack Clift |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Cell Functions
Thanks alot nick. I implemented the latter (GetMyIndex =
Application.Caller.Parent.Index) and it *sort*of works (well, it works better). Problem that I still have is this function is returning 1 more sheet than actually exists (an no, there are not any hidden sheets - have checked). It looks something like this: Sheet 1 returns index 1 Sheet 2 returns index 3 Sheet 3 returns index 4 Sheet 4 returns index 5 Could this be the result of a corrupted workbook (it crashed on me this afternoon)? If so, any ideas on how to restore? Any idea would be helpful. Unless it is 'start again'; I have spent hours getting the formatting right... "NickHK" wrote: Jack, You want the index of the WS that the UDF is on ? Public Function GetMyIndex1(AnyRange As Range) As Long GetMyIndex = AnyRange.Parent.Index End Function 'Or Public Function GetMyIndex2() As Long GetMyIndex = Application.Caller.Parent.Index End Function Add error trapping. NickHK "Jack Clift" wrote in message ... Tom, Have spent a bit more time on this - neither my nor your solution is going to work as it relies on the 'activesheet' and hence updates all sheets with this number, not each sheet to its own. Any ideas? is there someway to use the 'Me' keyword to differentiate between which instance is calling the function "Tom Ogilvy" wrote: Function NumberOfSheets() As Integer Application.Volatile NumberOfSheets = ActiveWorkbook.Worksheets.Count End Function if all you have in the workbook are worksheets Function SheetNumber() As Integer Application.Volatile SheetNumber = Activesheet.Index End function this falls apart if you have none worksheets in the workbook. Function SheetNumber() As Integer Application.Volatile if sheets.count = worksheets.count then SheetNumber = Activesheet.Index else ' your looping method end if End function application.Volatile makes the function volatile. It gets recalculated on every calculate event that would normally include that cell. -- Regards, Tom Ogilvy "Jack Clift" wrote: This problem is in two parts (excel 2003); 1. I have written a small macro that calculates the number of sheets in a workbook (writen in a macro module): Function NumberOfSheets() As Integer NumberOfSheets = ActiveWorkbook.Worksheets.Count End Function and am using this function in a cell a worksheet using the following notation: "=NumberOfSheets()" issue is that the worksheet will not 'recall' the function unless I select and modify the cell (or the like). How do I make this function update dynamically per all other cell functions - or at least if a sheets is deleted or created? 2. Similar to the above I am wanting to write a macro than enumerates each sheet in order as they are presented in the workbook: Function SheetNumber() As Integer Dim WS As Worksheet Dim i As Integer Set WS = ActiveSheet For i = 1 To NumberOfSheets If WS.Name = ActiveWorkbook.Worksheets(i).Name Then SheetNumber = i Exit Function End If Next i SheetNumber = -1 End Function The 'for next' loop seems a pretty clumsy way to do this, but I can't think of a better alternative. Any ideas. This function also needs to dynamically refresh so am hoping solution above is applicable to this. Thanks Jack Clift |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Cell Functions
Jack,
If you are sure there are no hidden sheets, chart sheets or the older macro/dialog sheets, then I can only think there is something corrupt about the WS. Try one of the Code Cleaners available: http://www.dailydoseofexcel.com/arch...er-version-50/ NickHK "Jack Clift" wrote in message ... Thanks alot nick. I implemented the latter (GetMyIndex = Application.Caller.Parent.Index) and it *sort*of works (well, it works better). Problem that I still have is this function is returning 1 more sheet than actually exists (an no, there are not any hidden sheets - have checked). It looks something like this: Sheet 1 returns index 1 Sheet 2 returns index 3 Sheet 3 returns index 4 Sheet 4 returns index 5 Could this be the result of a corrupted workbook (it crashed on me this afternoon)? If so, any ideas on how to restore? Any idea would be helpful. Unless it is 'start again'; I have spent hours getting the formatting right... "NickHK" wrote: Jack, You want the index of the WS that the UDF is on ? Public Function GetMyIndex1(AnyRange As Range) As Long GetMyIndex = AnyRange.Parent.Index End Function 'Or Public Function GetMyIndex2() As Long GetMyIndex = Application.Caller.Parent.Index End Function Add error trapping. NickHK "Jack Clift" wrote in message ... Tom, Have spent a bit more time on this - neither my nor your solution is going to work as it relies on the 'activesheet' and hence updates all sheets with this number, not each sheet to its own. Any ideas? is there someway to use the 'Me' keyword to differentiate between which instance is calling the function "Tom Ogilvy" wrote: Function NumberOfSheets() As Integer Application.Volatile NumberOfSheets = ActiveWorkbook.Worksheets.Count End Function if all you have in the workbook are worksheets Function SheetNumber() As Integer Application.Volatile SheetNumber = Activesheet.Index End function this falls apart if you have none worksheets in the workbook. Function SheetNumber() As Integer Application.Volatile if sheets.count = worksheets.count then SheetNumber = Activesheet.Index else ' your looping method end if End function application.Volatile makes the function volatile. It gets recalculated on every calculate event that would normally include that cell. -- Regards, Tom Ogilvy "Jack Clift" wrote: This problem is in two parts (excel 2003); 1. I have written a small macro that calculates the number of sheets in a workbook (writen in a macro module): Function NumberOfSheets() As Integer NumberOfSheets = ActiveWorkbook.Worksheets.Count End Function and am using this function in a cell a worksheet using the following notation: "=NumberOfSheets()" issue is that the worksheet will not 'recall' the function unless I select and modify the cell (or the like). How do I make this function update dynamically per all other cell functions - or at least if a sheets is deleted or created? 2. Similar to the above I am wanting to write a macro than enumerates each sheet in order as they are presented in the workbook: Function SheetNumber() As Integer Dim WS As Worksheet Dim i As Integer Set WS = ActiveSheet For i = 1 To NumberOfSheets If WS.Name = ActiveWorkbook.Worksheets(i).Name Then SheetNumber = i Exit Function End If Next i SheetNumber = -1 End Function The 'for next' loop seems a pretty clumsy way to do this, but I can't think of a better alternative. Any ideas. This function also needs to dynamically refresh so am hoping solution above is applicable to this. Thanks Jack Clift |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Cell Functions
Nick,
it must have been a corrupted file - manually I shuffled the order and added new sheets, then deleted and finally reordered them and the code now works as expected. It does make me a bit nervous about the robustness of the file in general - but that is not your problem. Thanks for the help Regards JC "NickHK" wrote: Jack, If you are sure there are no hidden sheets, chart sheets or the older macro/dialog sheets, then I can only think there is something corrupt about the WS. Try one of the Code Cleaners available: http://www.dailydoseofexcel.com/arch...er-version-50/ NickHK "Jack Clift" wrote in message ... Thanks alot nick. I implemented the latter (GetMyIndex = Application.Caller.Parent.Index) and it *sort*of works (well, it works better). Problem that I still have is this function is returning 1 more sheet than actually exists (an no, there are not any hidden sheets - have checked). It looks something like this: Sheet 1 returns index 1 Sheet 2 returns index 3 Sheet 3 returns index 4 Sheet 4 returns index 5 Could this be the result of a corrupted workbook (it crashed on me this afternoon)? If so, any ideas on how to restore? Any idea would be helpful. Unless it is 'start again'; I have spent hours getting the formatting right... "NickHK" wrote: Jack, You want the index of the WS that the UDF is on ? Public Function GetMyIndex1(AnyRange As Range) As Long GetMyIndex = AnyRange.Parent.Index End Function 'Or Public Function GetMyIndex2() As Long GetMyIndex = Application.Caller.Parent.Index End Function Add error trapping. NickHK "Jack Clift" wrote in message ... Tom, Have spent a bit more time on this - neither my nor your solution is going to work as it relies on the 'activesheet' and hence updates all sheets with this number, not each sheet to its own. Any ideas? is there someway to use the 'Me' keyword to differentiate between which instance is calling the function "Tom Ogilvy" wrote: Function NumberOfSheets() As Integer Application.Volatile NumberOfSheets = ActiveWorkbook.Worksheets.Count End Function if all you have in the workbook are worksheets Function SheetNumber() As Integer Application.Volatile SheetNumber = Activesheet.Index End function this falls apart if you have none worksheets in the workbook. Function SheetNumber() As Integer Application.Volatile if sheets.count = worksheets.count then SheetNumber = Activesheet.Index else ' your looping method end if End function application.Volatile makes the function volatile. It gets recalculated on every calculate event that would normally include that cell. -- Regards, Tom Ogilvy "Jack Clift" wrote: This problem is in two parts (excel 2003); 1. I have written a small macro that calculates the number of sheets in a workbook (writen in a macro module): Function NumberOfSheets() As Integer NumberOfSheets = ActiveWorkbook.Worksheets.Count End Function and am using this function in a cell a worksheet using the following notation: "=NumberOfSheets()" issue is that the worksheet will not 'recall' the function unless I select and modify the cell (or the like). How do I make this function update dynamically per all other cell functions - or at least if a sheets is deleted or created? 2. Similar to the above I am wanting to write a macro than enumerates each sheet in order as they are presented in the workbook: Function SheetNumber() As Integer Dim WS As Worksheet Dim i As Integer Set WS = ActiveSheet For i = 1 To NumberOfSheets If WS.Name = ActiveWorkbook.Worksheets(i).Name Then SheetNumber = i Exit Function End If Next i SheetNumber = -1 End Function The 'for next' loop seems a pretty clumsy way to do this, but I can't think of a better alternative. Any ideas. This function also needs to dynamically refresh so am hoping solution above is applicable to this. Thanks Jack Clift |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Cell Functions | Excel Programming | |||
Using custom functions within custom validation | Excel Discussion (Misc queries) | |||
Use custom VBA functions in cell formulas? | Excel Programming | |||
Custom Functions in C/C++ against in VBA | Excel Programming | |||
Custom Functions | Excel Programming |