T
Tay
After I have created the NumberAsText in one spreadsheet, how do I enable the
same to be used in other spreadsheet?
same to be used in other spreadsheet?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Gord Dibben
You would be best of placing the code into your Personal.xls file which will
open whenever Excel is opened.
The code will work on whichever workbook is active.
Or place the code into a new workbook and save as an Add-in which you would load
through Tools>Add-ins.
Gord Dibben MS Excel MVP
open whenever Excel is opened.
The code will work on whichever workbook is active.
Or place the code into a new workbook and save as an Add-in which you would load
through Tools>Add-ins.
Gord Dibben MS Excel MVP
R
Rick Rothstein \(MVP - VB\)
After I have created the NumberAsText in one spreadsheet, how
You could File/Export the module you placed this function in from the VBA
IDE; then, in subsequent macros, you can File/Import the module into your
any new spreadsheet's VBA project that needs it.
Rick
do I enable the same to be used in other spreadsheet?
You could File/Export the module you placed this function in from the VBA
IDE; then, in subsequent macros, you can File/Import the module into your
any new spreadsheet's VBA project that needs it.
Rick
T
Tay
where can I find the Personal.xls. I am using excel 2003.
G
Gord Dibben
You may not have one.
If you never recorded a macro to your Personal Macro Workbook you won't.
Start up Excel and any workbook.
Tools>Macro>Record new macro.
When the record macro dialog opens select "Personal Macro Workbook" from "store
in" dropdown.
Do a couple of things like copy a cell from one place to another or similar.
Hit the stop recording button. Do not hit the "x" to stop recording.
You now have a Personal.xls stored in your Office XLSTART folder.
It will open hidden whenever Excel starts.
Your macros will be stored in there.
Hit Alt + F11 then CTRL + r to see the Personal.xls workbook and its modules.
Gord Dibben MS Excel MVP
If you never recorded a macro to your Personal Macro Workbook you won't.
Start up Excel and any workbook.
Tools>Macro>Record new macro.
When the record macro dialog opens select "Personal Macro Workbook" from "store
in" dropdown.
Do a couple of things like copy a cell from one place to another or similar.
Hit the stop recording button. Do not hit the "x" to stop recording.
You now have a Personal.xls stored in your Office XLSTART folder.
It will open hidden whenever Excel starts.
Your macros will be stored in there.
Hit Alt + F11 then CTRL + r to see the Personal.xls workbook and its modules.
Gord Dibben MS Excel MVP
Y
yshridhar
Follow this procedure which i received from Mr. Biff
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 Insert>Module
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 Tools>>Add-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(...)
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 Insert>Module
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 Tools>>Add-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(...)