| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 - User def. function
I have defined a function like
Function GetSheetName() as String Application.Volatile ' I tried this but no difference GetSheetName=ActiveSheet.Name End func and when I use it in a cell on a workheet - then sometimes it works and sometimes it won't - why ? KS, Denmark |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2003 - User def. function
That is not a safe way to get the name of the sheet where the formula is; some other sheet might be active.
Use Function SheetName() As String SheetName = Application.Caller.SheetName End Function -- Kind regards, Niek Otten "KSO" <keld DOT soerensen@os DOT dk wrote in message ... I have defined a function like Function GetSheetName() as String Application.Volatile ' I tried this but no difference GetSheetName=ActiveSheet.Name End func and when I use it in a cell on a workheet - then sometimes it works and sometimes it won't - why ? KS, Denmark |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2003 - User def. function
Hi Niek
It won't work either - I get runtime error 424 But I can see it's NOT a safe metode. KS, Denmark "Niek Otten" wrote: That is not a safe way to get the name of the sheet where the formula is; some other sheet might be active. Use Function SheetName() As String SheetName = Application.Caller.SheetName End Function -- Kind regards, Niek Otten "KSO" <keld DOT soerensen@os DOT dk wrote in message ... I have defined a function like Function GetSheetName() as String Application.Volatile ' I tried this but no difference GetSheetName=ActiveSheet.Name End func and when I use it in a cell on a workheet - then sometimes it works and sometimes it won't - why ? KS, Denmark |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2003 - User def. function
Do you put the code in a General Module? It should not be in a Sheet module. I didn't get the error.
-- Kind regards, Niek Otten "KSO" <keld DOT soerensen@os DOT dk wrote in message ... Hi Niek It won't work either - I get runtime error 424 But I can see it's NOT a safe metode. KS, Denmark "Niek Otten" wrote: That is not a safe way to get the name of the sheet where the formula is; some other sheet might be active. Use Function SheetName() As String SheetName = Application.Caller.SheetName End Function -- Kind regards, Niek Otten "KSO" <keld DOT soerensen@os DOT dk wrote in message ... I have defined a function like Function GetSheetName() as String Application.Volatile ' I tried this but no difference GetSheetName=ActiveSheet.Name End func and when I use it in a cell on a workheet - then sometimes it works and sometimes it won't - why ? KS, Denmark |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2003 - User def. function
I have to use the function on 10 defferent sheets so I placed the code in a
added module - I think that's what you'r poiting out - but still I get run time error 424 "Niek Otten" wrote: Do you put the code in a General Module? It should not be in a Sheet module. I didn't get the error. -- Kind regards, Niek Otten "KSO" <keld DOT soerensen@os DOT dk wrote in message ... Hi Niek It won't work either - I get runtime error 424 But I can see it's NOT a safe metode. KS, Denmark "Niek Otten" wrote: That is not a safe way to get the name of the sheet where the formula is; some other sheet might be active. Use Function SheetName() As String SheetName = Application.Caller.SheetName End Function -- Kind regards, Niek Otten "KSO" <keld DOT soerensen@os DOT dk wrote in message ... I have defined a function like Function GetSheetName() as String Application.Volatile ' I tried this but no difference GetSheetName=ActiveSheet.Name End func and when I use it in a cell on a workheet - then sometimes it works and sometimes it won't - why ? KS, Denmark |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2003 - User def. function
Sorry for that; SheetName turned out to be already in Laurent Longre's Morefunc add-in and that one was called in my case. Mine
uses wrong syntax. -- Kind regards, Niek Otten "KSO" <keld DOT soerensen@os DOT dk wrote in message ... I have to use the function on 10 defferent sheets so I placed the code in a added module - I think that's what you'r poiting out - but still I get run time error 424 "Niek Otten" wrote: Do you put the code in a General Module? It should not be in a Sheet module. I didn't get the error. -- Kind regards, Niek Otten "KSO" <keld DOT soerensen@os DOT dk wrote in message ... Hi Niek It won't work either - I get runtime error 424 But I can see it's NOT a safe metode. KS, Denmark "Niek Otten" wrote: That is not a safe way to get the name of the sheet where the formula is; some other sheet might be active. Use Function SheetName() As String SheetName = Application.Caller.SheetName End Function -- Kind regards, Niek Otten "KSO" <keld DOT soerensen@os DOT dk wrote in message ... I have defined a function like Function GetSheetName() as String Application.Volatile ' I tried this but no difference GetSheetName=ActiveSheet.Name End func and when I use it in a cell on a workheet - then sometimes it works and sometimes it won't - why ? KS, Denmark |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2003 - User def. function
I can now see the problem to know witch sheet the functions is called from,
but Application.Caller don't give me some usefull information here - then it can't be done without installing this add-in you'r talking about .... Any other idias ? KS, Denmark "Niek Otten" wrote: Sorry for that; SheetName turned out to be already in Laurent Longre's Morefunc add-in and that one was called in my case. Mine uses wrong syntax. -- Kind regards, Niek Otten "KSO" <keld DOT soerensen@os DOT dk wrote in message ... I have to use the function on 10 defferent sheets so I placed the code in a added module - I think that's what you'r poiting out - but still I get run time error 424 "Niek Otten" wrote: Do you put the code in a General Module? It should not be in a Sheet module. I didn't get the error. -- Kind regards, Niek Otten "KSO" <keld DOT soerensen@os DOT dk wrote in message ... Hi Niek It won't work either - I get runtime error 424 But I can see it's NOT a safe metode. KS, Denmark "Niek Otten" wrote: That is not a safe way to get the name of the sheet where the formula is; some other sheet might be active. Use Function SheetName() As String SheetName = Application.Caller.SheetName End Function -- Kind regards, Niek Otten "KSO" <keld DOT soerensen@os DOT dk wrote in message ... I have defined a function like Function GetSheetName() as String Application.Volatile ' I tried this but no difference GetSheetName=ActiveSheet.Name End func and when I use it in a cell on a workheet - then sometimes it works and sometimes it won't - why ? KS, Denmark |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2003 - User def. function
This one seems to work. It needs a cell on that sheet as argument
Function test(a As Range) Application.Volatile test = Application.Caller.Worksheet.Name End Function But you may find Morefunc useful anyway; have a look! http://xcell05.free.fr/ -- Kind regards, Niek Otten "KSO" <keld DOT soerensen@os DOT dk wrote in message ... I can now see the problem to know witch sheet the functions is called from, but Application.Caller don't give me some usefull information here - then it can't be done without installing this add-in you'r talking about .... Any other idias ? KS, Denmark "Niek Otten" wrote: Sorry for that; SheetName turned out to be already in Laurent Longre's Morefunc add-in and that one was called in my case. Mine uses wrong syntax. -- Kind regards, Niek Otten "KSO" <keld DOT soerensen@os DOT dk wrote in message ... I have to use the function on 10 defferent sheets so I placed the code in a added module - I think that's what you'r poiting out - but still I get run time error 424 "Niek Otten" wrote: Do you put the code in a General Module? It should not be in a Sheet module. I didn't get the error. -- Kind regards, Niek Otten "KSO" <keld DOT soerensen@os DOT dk wrote in message ... Hi Niek It won't work either - I get runtime error 424 But I can see it's NOT a safe metode. KS, Denmark "Niek Otten" wrote: That is not a safe way to get the name of the sheet where the formula is; some other sheet might be active. Use Function SheetName() As String SheetName = Application.Caller.SheetName End Function -- Kind regards, Niek Otten "KSO" <keld DOT soerensen@os DOT dk wrote in message ... I have defined a function like Function GetSheetName() as String Application.Volatile ' I tried this but no difference GetSheetName=ActiveSheet.Name End func and when I use it in a cell on a workheet - then sometimes it works and sometimes it won't - why ? KS, Denmark |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2003 - User def. function
Too early up for a Sunday. I'll get back to bed.
Of course you don't need the argument. -- Kind regards, Niek Otten "Niek Otten" wrote in message ... This one seems to work. It needs a cell on that sheet as argument Function test(a As Range) Application.Volatile test = Application.Caller.Worksheet.Name End Function But you may find Morefunc useful anyway; have a look! http://xcell05.free.fr/ -- Kind regards, Niek Otten "KSO" <keld DOT soerensen@os DOT dk wrote in message ... I can now see the problem to know witch sheet the functions is called from, but Application.Caller don't give me some usefull information here - then it can't be done without installing this add-in you'r talking about .... Any other idias ? KS, Denmark "Niek Otten" wrote: Sorry for that; SheetName turned out to be already in Laurent Longre's Morefunc add-in and that one was called in my case. Mine uses wrong syntax. -- Kind regards, Niek Otten "KSO" <keld DOT soerensen@os DOT dk wrote in message ... I have to use the function on 10 defferent sheets so I placed the code in a added module - I think that's what you'r poiting out - but still I get run time error 424 "Niek Otten" wrote: Do you put the code in a General Module? It should not be in a Sheet module. I didn't get the error. -- Kind regards, Niek Otten "KSO" <keld DOT soerensen@os DOT dk wrote in message ... Hi Niek It won't work either - I get runtime error 424 But I can see it's NOT a safe metode. KS, Denmark "Niek Otten" wrote: That is not a safe way to get the name of the sheet where the formula is; some other sheet might be active. Use Function SheetName() As String SheetName = Application.Caller.SheetName End Function -- Kind regards, Niek Otten "KSO" <keld DOT soerensen@os DOT dk wrote in message ... I have defined a function like Function GetSheetName() as String Application.Volatile ' I tried this but no difference GetSheetName=ActiveSheet.Name End func and when I use it in a cell on a workheet - then sometimes it works and sometimes it won't - why ? KS, Denmark |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 2003: Having trouble with conditional formatting with custom user function (Repost) | Excel Worksheet Functions | |||
| Problem using newly created user function in Excel 2003 | Excel Discussion (Misc queries) | |||
| Is there a "last saved on date/user" macro/function for Excel 2003 | Excel Discussion (Misc queries) | |||
| can i email an excel spreadsheet from office 2003 to a mac user | New Users to Excel | |||
| Excel 2003 crashes when opening a file from one particular user | Excel Discussion (Misc queries) | |||
Linear Mode
