Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 and macros/functions
Hello
I have a small function, so I can get the name of the present sheet... It goes: Function GetSheetName() As String 'ByVal Sheet As Worksheet GetSheetName = ActiveSheet.Name End Function and as formula it is used: =GetSheetName(); however, that does not work in excel 2007 why? WBR Sonnich |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 and macros/functions
On Jul 8, 9:52*am, jodleren wrote:
Hello I have a small function, so I can get the name of the present sheet... It goes: Function GetSheetName() As String 'ByVal Sheet As Worksheet * GetSheetName = ActiveSheet.Name End Function and as formula it is used: =GetSheetName(); however, that does not work in excel 2007 why? WBR Sonnich jodleren Amend as below so that the function responds to changes: Function GetSheetName() As String Application.Volatile GetSheetName = ActiveSheet.Name End Function Another way is as follows: Function GetSheetName As String Application.Volatile GetSheetName = Application.Caller.Parent.Name End Function Both the above tested with XL2003 & XL2007 Regards Michael Beckinsale |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 and macros/functions
GetSheetName = ActiveSheet.Name That is probably not what you want. With that code, the function will return the name of whatever sheet happens to be active when Excel decides it is time to calculate. There is no certainty that ActiveSheet will be the same as the sheet on which the formula resides. This can cause problems that may be hard to diagnose. Instead, use Application.Caller, which, when called from a worksheet cell, will return a Range reference to the cell whence the function was called. Function GetSheetName() As String GetSheetName = Application.Caller.Worksheet.Name End Function Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 8 July 2009 08:44:26 -0700 (PDT), "michael.beckinsale" wrote: On Jul 8, 9:52*am, jodleren wrote: Hello I have a small function, so I can get the name of the present sheet... It goes: Function GetSheetName() As String 'ByVal Sheet As Worksheet * GetSheetName = ActiveSheet.Name End Function and as formula it is used: =GetSheetName(); however, that does not work in excel 2007 why? WBR Sonnich jodleren Amend as below so that the function responds to changes: Function GetSheetName() As String Application.Volatile GetSheetName = ActiveSheet.Name End Function Another way is as follows: Function GetSheetName As String Application.Volatile GetSheetName = Application.Caller.Parent.Name End Function Both the above tested with XL2003 & XL2007 Regards Michael Beckinsale |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 and macros/functions
On Jul 8, 9:23*pm, Chip Pearson wrote:
GetSheetName = ActiveSheet.Name That is probably not what you want. With that code, the function will return the name of whatever sheet happens to be active when Excel decides it is time to calculate. There is no certainty that ActiveSheet will be the same as the sheet on which the formula resides. This can cause problems that may be hard to diagnose. Instead, use Application.Caller, which, when called from a worksheet cell, will return a Range reference to the cell whence the function was called. Function GetSheetName() As String * * GetSheetName = Application.Caller.Worksheet.Name End Function Well, the result is the same: #NAME? Error. When I search for help on the error it tries to download something, but fails. BTW, the same problem aplies to Visual Studio 2008, all help requests are replied by "information not found". /S |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 and macros/functions
Did you put the subroutine in a General module in that workbook's project? (It doesn't belong behind any worksheet and it doesn't belong behind the ThisWorkbook module.) Did you spell the name of the function correctly in the cell? Did you allow macros to run when opened the workbook? If you put the function in a different workbook (like personal.xls), then you have to tell excel where to find it: ='personal.xls'!GetSheetName() jodleren wrote: On Jul 8, 9:23 pm, Chip Pearson wrote: GetSheetName = ActiveSheet.Name That is probably not what you want. With that code, the function will return the name of whatever sheet happens to be active when Excel decides it is time to calculate. There is no certainty that ActiveSheet will be the same as the sheet on which the formula resides. This can cause problems that may be hard to diagnose. Instead, use Application.Caller, which, when called from a worksheet cell, will return a Range reference to the cell whence the function was called. Function GetSheetName() As String GetSheetName = Application.Caller.Worksheet.Name End Function Well, the result is the same: #NAME? Error. When I search for help on the error it tries to download something, but fails. BTW, the same problem aplies to Visual Studio 2008, all help requests are replied by "information not found". /S -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007, I write macros in 2003 is 2007 similar for VBA? Pros andcons please | Excel Programming | |||
Excel 2007 macros - how to merge 5 macros together into one | Excel Discussion (Misc queries) | |||
Excel Functions and Macros | Excel Discussion (Misc queries) | |||
excel functions in macros | Excel Programming | |||
Excel: Deleting Macros and Functions | Excel Programming |