| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Placing an UDF
I have an User Defined Function sent by EXCEL group. Where shall i place
this to use it as a normal worksheet function in any excel file i open. eg like SUM, AVERAGE? Thanks Sridhar |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Placing an UDF
One way:
Put it in a normal code module in your Personal.xls workbook. See http://www.mvps.org/dmcritchie/excel/getstarted.htm In article , yshridhar wrote: I have an User Defined Function sent by EXCEL group. Where shall i place this to use it as a normal worksheet function in any excel file i open. eg like SUM, AVERAGE? Thanks Sridhar |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Placing an UDF
To make a UDF available to all workbooks you have to store it in a special
file called Personal.xls. Personal.xls is a hidden file that opens wth Excel. If you do not already have a Personal.xls file then you'll have to create it. Start Excel It opens with the default file name of Book1 Hit ALT F11, this will open the VB editor Hit CTRL R, this will open the project explorer pane on the left side of the window Look for VBAProject(Book1) in the explorer pane Select it, then right click Select InsertModule Paste your UDF code in the window that opens on the right Hit ALT Q to return to Excel Now, save the file and name it Personal.xls. You have to save the file to a special directory named XLStart. I'm using Windows XP with Excel 2002. The path to my XLStart directory is: C:\Documents and Settings\User\Application Data\Microsoft\Excel\XLStart Yours may be different but you need to find the XLStart directory and save the file to that location. Then, when you want to use the UDF in any workbook you call it like this: =Personal.xls!your_function_name(...) For example, suppose the function name is FindAll: =Personal.xls!FindAll(...) You have to replace the "..." with any arguments your function may take. -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... I have an User Defined Function sent by EXCEL group. Where shall i place this to use it as a normal worksheet function in any excel file i open. eg like SUM, AVERAGE? Thanks Sridhar |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Placing an UDF
Thaks alot Mr. Biff for your detailed explanation.
Sridhar "T. Valko" wrote: To make a UDF available to all workbooks you have to store it in a special file called Personal.xls. Personal.xls is a hidden file that opens wth Excel. If you do not already have a Personal.xls file then you'll have to create it. Start Excel It opens with the default file name of Book1 Hit ALT F11, this will open the VB editor Hit CTRL R, this will open the project explorer pane on the left side of the window Look for VBAProject(Book1) in the explorer pane Select it, then right click Select InsertModule Paste your UDF code in the window that opens on the right Hit ALT Q to return to Excel Now, save the file and name it Personal.xls. You have to save the file to a special directory named XLStart. I'm using Windows XP with Excel 2002. The path to my XLStart directory is: C:\Documents and Settings\User\Application Data\Microsoft\Excel\XLStart Yours may be different but you need to find the XLStart directory and save the file to that location. Then, when you want to use the UDF in any workbook you call it like this: =Personal.xls!your_function_name(...) For example, suppose the function name is FindAll: =Personal.xls!FindAll(...) You have to replace the "..." with any arguments your function may take. -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... I have an User Defined Function sent by EXCEL group. Where shall i place this to use it as a normal worksheet function in any excel file i open. eg like SUM, AVERAGE? Thanks Sridhar |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Placing an UDF
Note that you don't have to store it in a file called Personal.xls.
There's NOTHING special about 'Personal.xls', except that it's the default name that XL uses in the Record Macro dialog (for WinXL, MacXL defaults to 'Personal Macro Workbook'). You can store it in a file with ANY name that's placed in your Startup folder (with the window hidden, if desired, as XL creates Personal.xls). Use the filename as a prefix to the UDF name. OR you can save the file with the UDF as an Add-in, and you don't need the filename prefix when the add-in is loaded (either through the Tools/Add-ins menu, or just store the Add-in in your startup folder). In article , "T. Valko" wrote: To make a UDF available to all workbooks you have to store it in a special file called Personal.xls. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Placing an UDF
There's NOTHING special about 'Personal.xls', except
that it's the default name that XL uses in the Record Macro dialog (for WinXL, MacXL defaults to 'Personal Macro Workbook'). Which is why I explained it the way that I did. -- Biff Microsoft Excel MVP "JE McGimpsey" wrote in message ... Note that you don't have to store it in a file called Personal.xls. There's NOTHING special about 'Personal.xls', except that it's the default name that XL uses in the Record Macro dialog (for WinXL, MacXL defaults to 'Personal Macro Workbook'). You can store it in a file with ANY name that's placed in your Startup folder (with the window hidden, if desired, as XL creates Personal.xls). Use the filename as a prefix to the UDF name. OR you can save the file with the UDF as an Add-in, and you don't need the filename prefix when the add-in is loaded (either through the Tools/Add-ins menu, or just store the Add-in in your startup folder). In article , "T. Valko" wrote: To make a UDF available to all workbooks you have to store it in a special file called Personal.xls. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Placing an UDF
In article ,
"T. Valko" wrote: Which is why I explained it the way that I did. Ah, I got confused by the "have to" and "special" in you have to store it in a special file called Personal.xls" my mistake... |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Placing an UDF
Thanks alot Mr. Biff for your detailed explanation. But i a facing a
problem. When i open excel the personal.xls is also opening in another window. How to hide it? How to store this UDF as an ADDIN Sridhar "T. Valko" wrote: To make a UDF available to all workbooks you have to store it in a special file called Personal.xls. Personal.xls is a hidden file that opens wth Excel. If you do not already have a Personal.xls file then you'll have to create it. Start Excel It opens with the default file name of Book1 Hit ALT F11, this will open the VB editor Hit CTRL R, this will open the project explorer pane on the left side of the window Look for VBAProject(Book1) in the explorer pane Select it, then right click Select InsertModule Paste your UDF code in the window that opens on the right Hit ALT Q to return to Excel Now, save the file and name it Personal.xls. You have to save the file to a special directory named XLStart. I'm using Windows XP with Excel 2002. The path to my XLStart directory is: C:\Documents and Settings\User\Application Data\Microsoft\Excel\XLStart Yours may be different but you need to find the XLStart directory and save the file to that location. Then, when you want to use the UDF in any workbook you call it like this: =Personal.xls!your_function_name(...) For example, suppose the function name is FindAll: =Personal.xls!FindAll(...) You have to replace the "..." with any arguments your function may take. -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... I have an User Defined Function sent by EXCEL group. Where shall i place this to use it as a normal worksheet function in any excel file i open. eg like SUM, AVERAGE? Thanks Sridhar |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Placing an UDF
Select the window with Personal.xls
Goto the menu Window and select Hide. When you close Excel you should get a message asking if you want to save changes made to Personl.xls. Answer with Yes. -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Thanks alot Mr. Biff for your detailed explanation. But i a facing a problem. When i open excel the personal.xls is also opening in another window. How to hide it? How to store this UDF as an ADDIN Sridhar "T. Valko" wrote: To make a UDF available to all workbooks you have to store it in a special file called Personal.xls. Personal.xls is a hidden file that opens wth Excel. If you do not already have a Personal.xls file then you'll have to create it. Start Excel It opens with the default file name of Book1 Hit ALT F11, this will open the VB editor Hit CTRL R, this will open the project explorer pane on the left side of the window Look for VBAProject(Book1) in the explorer pane Select it, then right click Select InsertModule Paste your UDF code in the window that opens on the right Hit ALT Q to return to Excel Now, save the file and name it Personal.xls. You have to save the file to a special directory named XLStart. I'm using Windows XP with Excel 2002. The path to my XLStart directory is: C:\Documents and Settings\User\Application Data\Microsoft\Excel\XLStart Yours may be different but you need to find the XLStart directory and save the file to that location. Then, when you want to use the UDF in any workbook you call it like this: =Personal.xls!your_function_name(...) For example, suppose the function name is FindAll: =Personal.xls!FindAll(...) You have to replace the "..." with any arguments your function may take. -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... I have an User Defined Function sent by EXCEL group. Where shall i place this to use it as a normal worksheet function in any excel file i open. eg like SUM, AVERAGE? Thanks Sridhar |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Placing an UDF
Sorry, I didn't answer your other question:
How to store this UDF as an ADDIN Basically, it's the same process but you give the file a different file extension and you save it to a different location. Although you can save it to other locations MS has a location just for add-ins. Notice I didn't use the words have to or special. I don't want to argue about semantics with anyone! I'll describe the procedure from the very beginning. Start Excel It opens with the default file name of Book1 Hit ALT F11, this will open the VB editor Hit CTRL R, this will open the project explorer pane on the left side of the window Look for VBAProject(Book1) in the explorer pane Select it, then right click Select InsertModule Paste your UDF code in the window that opens on the right Hit ALT Q to return to Excel Now, save the file and name it Personal.xla. You should save the file to the directory named AddIns. I'm using Windows XP with Excel 2002. The path to my AddIns directory is: C:\Documents and Settings\User\Application Data\Microsoft\AddIns Now close Excel. Wait a minute or two then reopen Excel. You need to load the add-in Goto the menu ToolsAdd-Ins You should see the Personal.xla file listed. Select Personal.xla then click OK You should be good to go! Now, you can use this function just like any other function: =MyFunction(...) -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Thanks alot Mr. Biff for your detailed explanation. But i a facing a problem. When i open excel the personal.xls is also opening in another window. How to hide it? How to store this UDF as an ADDIN Sridhar "T. Valko" wrote: To make a UDF available to all workbooks you have to store it in a special file called Personal.xls. Personal.xls is a hidden file that opens wth Excel. If you do not already have a Personal.xls file then you'll have to create it. Start Excel It opens with the default file name of Book1 Hit ALT F11, this will open the VB editor Hit CTRL R, this will open the project explorer pane on the left side of the window Look for VBAProject(Book1) in the explorer pane Select it, then right click Select InsertModule Paste your UDF code in the window that opens on the right Hit ALT Q to return to Excel Now, save the file and name it Personal.xls. You have to save the file to a special directory named XLStart. I'm using Windows XP with Excel 2002. The path to my XLStart directory is: C:\Documents and Settings\User\Application Data\Microsoft\Excel\XLStart Yours may be different but you need to find the XLStart directory and save the file to that location. Then, when you want to use the UDF in any workbook you call it like this: =Personal.xls!your_function_name(...) For example, suppose the function name is FindAll: =Personal.xls!FindAll(...) You have to replace the "..." with any arguments your function may take. -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... I have an User Defined Function sent by EXCEL group. Where shall i place this to use it as a normal worksheet function in any excel file i open. eg like SUM, AVERAGE? Thanks Sridhar |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Placing an UDF
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "yshridhar" wrote: Thanks alot Mr. Biff for your elaborate explanations. What we want is that to clear our doubts and resolve our problems. Thanks alot again for your EXCELlent suggestion and explanations. With regards Sridhar "T. Valko" wrote: Sorry, I didn't answer your other question: How to store this UDF as an ADDIN Basically, it's the same process but you give the file a different file extension and you save it to a different location. Although you can save it to other locations MS has a location just for add-ins. Notice I didn't use the words have to or special. I don't want to argue about semantics with anyone! I'll describe the procedure from the very beginning. Start Excel It opens with the default file name of Book1 Hit ALT F11, this will open the VB editor Hit CTRL R, this will open the project explorer pane on the left side of the window Look for VBAProject(Book1) in the explorer pane Select it, then right click Select InsertModule Paste your UDF code in the window that opens on the right Hit ALT Q to return to Excel Now, save the file and name it Personal.xla. You should save the file to the directory named AddIns. I'm using Windows XP with Excel 2002. The path to my AddIns directory is: C:\Documents and Settings\User\Application Data\Microsoft\AddIns Now close Excel. Wait a minute or two then reopen Excel. You need to load the add-in Goto the menu ToolsAdd-Ins You should see the Personal.xla file listed. Select Personal.xla then click OK You should be good to go! Now, you can use this function just like any other function: =MyFunction(...) -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Thanks alot Mr. Biff for your detailed explanation. But i a facing a problem. When i open excel the personal.xls is also opening in another window. How to hide it? How to store this UDF as an ADDIN Sridhar "T. Valko" wrote: To make a UDF available to all workbooks you have to store it in a special file called Personal.xls. Personal.xls is a hidden file that opens wth Excel. If you do not already have a Personal.xls file then you'll have to create it. Start Excel It opens with the default file name of Book1 Hit ALT F11, this will open the VB editor Hit CTRL R, this will open the project explorer pane on the left side of the window Look for VBAProject(Book1) in the explorer pane Select it, then right click Select InsertModule Paste your UDF code in the window that opens on the right Hit ALT Q to return to Excel Now, save the file and name it Personal.xls. You have to save the file to a special directory named XLStart. I'm using Windows XP with Excel 2002. The path to my XLStart directory is: C:\Documents and Settings\User\Application Data\Microsoft\Excel\XLStart Yours may be different but you need to find the XLStart directory and save the file to that location. Then, when you want to use the UDF in any workbook you call it like this: =Personal.xls!your_function_name(...) For example, suppose the function name is FindAll: =Personal.xls!FindAll(...) You have to replace the "..." with any arguments your function may take. -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... I have an User Defined Function sent by EXCEL group. Where shall i place this to use it as a normal worksheet function in any excel file i open. eg like SUM, AVERAGE? Thanks Sridhar |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Placing an UDF
Thanks alot Mr. Biff for your elaborate explanations. What we want is that
to clear our doubts and resolve our problems. Thanks alot again for your EXCELlent suggestion and explanations. With regards Sridhar "T. Valko" wrote: Sorry, I didn't answer your other question: How to store this UDF as an ADDIN Basically, it's the same process but you give the file a different file extension and you save it to a different location. Although you can save it to other locations MS has a location just for add-ins. Notice I didn't use the words have to or special. I don't want to argue about semantics with anyone! I'll describe the procedure from the very beginning. Start Excel It opens with the default file name of Book1 Hit ALT F11, this will open the VB editor Hit CTRL R, this will open the project explorer pane on the left side of the window Look for VBAProject(Book1) in the explorer pane Select it, then right click Select InsertModule Paste your UDF code in the window that opens on the right Hit ALT Q to return to Excel Now, save the file and name it Personal.xla. You should save the file to the directory named AddIns. I'm using Windows XP with Excel 2002. The path to my AddIns directory is: C:\Documents and Settings\User\Application Data\Microsoft\AddIns Now close Excel. Wait a minute or two then reopen Excel. You need to load the add-in Goto the menu ToolsAdd-Ins You should see the Personal.xla file listed. Select Personal.xla then click OK You should be good to go! Now, you can use this function just like any other function: =MyFunction(...) -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... Thanks alot Mr. Biff for your detailed explanation. But i a facing a problem. When i open excel the personal.xls is also opening in another window. How to hide it? How to store this UDF as an ADDIN Sridhar "T. Valko" wrote: To make a UDF available to all workbooks you have to store it in a special file called Personal.xls. Personal.xls is a hidden file that opens wth Excel. If you do not already have a Personal.xls file then you'll have to create it. Start Excel It opens with the default file name of Book1 Hit ALT F11, this will open the VB editor Hit CTRL R, this will open the project explorer pane on the left side of the window Look for VBAProject(Book1) in the explorer pane Select it, then right click Select InsertModule Paste your UDF code in the window that opens on the right Hit ALT Q to return to Excel Now, save the file and name it Personal.xls. You have to save the file to a special directory named XLStart. I'm using Windows XP with Excel 2002. The path to my XLStart directory is: C:\Documents and Settings\User\Application Data\Microsoft\Excel\XLStart Yours may be different but you need to find the XLStart directory and save the file to that location. Then, when you want to use the UDF in any workbook you call it like this: =Personal.xls!your_function_name(...) For example, suppose the function name is FindAll: =Personal.xls!FindAll(...) You have to replace the "..." with any arguments your function may take. -- Biff Microsoft Excel MVP "yshridhar" wrote in message ... I have an User Defined Function sent by EXCEL group. Where shall i place this to use it as a normal worksheet function in any excel file i open. eg like SUM, AVERAGE? Thanks Sridhar |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Number Placing | Excel Worksheet Functions | |||
| placing.. 1st, 2nd, 3rd... | Excel Discussion (Misc queries) | |||
| Placing bubbles | Charts and Charting in Excel | |||
| placing | Excel Worksheet Functions | |||
Linear Mode
