Where does one store a called macro that would be used by many excel
programs? And how is this procedure done, as well as how is the macro coded
Public or Private?

On Dec 19, 10:51 pm, Rick <R...@discussions.microsoft.com> wrote:
> Where does one store a called macro that would be used by many excel
> programs? And how is this procedure done, as well as how is the macro coded
> Public or Private?

Place the macro in i.e. personal.xla placed in the folder XLStart. Any
files in XLstart will be loaded when Excel is started.

If the macro is Private it won't be visible in the Run macro dialog
(Alt-F8), if you _allways_ calls the macro from menus or other macros
you could set it Private. But if you need to activate the Macro from
Run macro dialog, it should be Public.

/Tommy, DK

hi
2 ways.
1. store the macros a file called personal.xls and save it to the xlstart
folder.
i am using xp and the xlstart folder is located at ...
c:\program files\microsoft office\Office 10\xlstart\
it varies per ref level. the personal.xls file is hidden and opens when xl
opens and all macros in the personal file are available to use anywhere,
anytime.
this is how i do it and have been for 12~+ years. you can assign the macros
to sheet command buttons, toolbar icons or just run them from the menu tree...
tools\macro\macros....
you can also use the call command.
2. instead of a personal.xls file, make it and addin. works but i have never
used this technique so i can't say much about it.
as to public or private...
all of my macros in my personal.xls are just plain ol', run-of-the-mill subs.
public or private are optional. in vb help, type the word "sub" then click
on "calling subs and function procedures." it will explain the difference.
but as to my personal macros, "sub" does the trick.

hope this helped.
regards
FSt1



"Rick" wrote:

> Where does one store a called macro that would be used by many excel
> programs? And how is this procedure done, as well as how is the macro coded
> Public or Private?

Thanks for the info, I'll give it a try.

"FSt1" wrote:

> hi
> 2 ways.
> 1. store the macros a file called personal.xls and save it to the xlstart
> folder.
> i am using xp and the xlstart folder is located at ...
> c:\program files\microsoft office\Office 10\xlstart\
> it varies per ref level. the personal.xls file is hidden and opens when xl
> opens and all macros in the personal file are available to use anywhere,
> anytime.
> this is how i do it and have been for 12~+ years. you can assign the macros
> to sheet command buttons, toolbar icons or just run them from the menu tree...
> tools\macro\macros....
> you can also use the call command.
> 2. instead of a personal.xls file, make it and addin. works but i have never
> used this technique so i can't say much about it.
> as to public or private...
> all of my macros in my personal.xls are just plain ol', run-of-the-mill subs.
> public or private are optional. in vb help, type the word "sub" then click
> on "calling subs and function procedures." it will explain the difference.
> but as to my personal macros, "sub" does the trick.
>
> hope this helped.
> regards
> FSt1
>
>
>
> "Rick" wrote:
>
> > Where does one store a called macro that would be used by many excel
> > programs? And how is this procedure done, as well as how is the macro coded
> > Public or Private?

By default if you do not specify public or private then it's public.

Sub Whatever()
is exactly the same as
Public Sub Whatever()

I personally always specify whether it is public or private. That way I know
that I took the time to actually think about what would be the appropriate
designation for the sub or function...
--
HTH...

Jim Thomlinson


"FSt1" wrote:

> hi
> 2 ways.
> 1. store the macros a file called personal.xls and save it to the xlstart
> folder.
> i am using xp and the xlstart folder is located at ...
> c:\program files\microsoft office\Office 10\xlstart\
> it varies per ref level. the personal.xls file is hidden and opens when xl
> opens and all macros in the personal file are available to use anywhere,
> anytime.
> this is how i do it and have been for 12~+ years. you can assign the macros
> to sheet command buttons, toolbar icons or just run them from the menu tree...
> tools\macro\macros....
> you can also use the call command.
> 2. instead of a personal.xls file, make it and addin. works but i have never
> used this technique so i can't say much about it.
> as to public or private...
> all of my macros in my personal.xls are just plain ol', run-of-the-mill subs.
> public or private are optional. in vb help, type the word "sub" then click
> on "calling subs and function procedures." it will explain the difference.
> but as to my personal macros, "sub" does the trick.
>
> hope this helped.
> regards
> FSt1
>
>
>
> "Rick" wrote:
>
> > Where does one store a called macro that would be used by many excel
> > programs? And how is this procedure done, as well as how is the macro coded
> > Public or Private?