Excel 2007 and macros/functions

[jodleren]

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

 

[michael.beckinsale]

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

 

[Chip Pearson]

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)

 

[jodleren]

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]

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()