|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 12:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com