anyway to make a macro for all of excel

M

mithu

is there anyway to make a macro that applies to all of excel..
basically to every workbook that i open? like can i make a
permeniant button in excel.. that will be there for every spreadsheet
that i open?
 
D

Dave Peterson

Lots of people keep a workbook named personal.xls in their XLStart folder.

They keep all their macros that they want available whenever excel opens in that
file.

To give myself access to the macros (instead of using tools|macro|macros
dialog), I use one of these.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

=====
Ps. If you're going to share this file with others, then don't call it
personal.xls. The recipients could already have that file name in use.

Call it something like:
MithuUtils.xls
 
B

Bob Flanagan

If you save your macro in your personal.xls file, it will be available to be
run in all workbooks.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
 
M

mithu

ok so here is something interesting i found.

i dont if this is with every version of excel.. but if i put a macro
in personal.xls
lets say i put

sub emailfile()
....
end sub

that works fine.

but if i put a function that does not work.
for instance i put

Function NumberIt(CompanyCell As Range) As Double
C = CompanyCell.Value
For x = 1 To Len(C)
If IsNumeric(Mid(C, x, 1)) Then n = n & Mid(C, x, 1)
Next x
NumberIt = --n
End Function

i use this to extract a number from a cell from vlookup
so i would use this like =vlookup(numberIt(a1).......
this doesnt work if i put it in personal.. but it does if i put it in
the macro sheet of the book..

really wired.. you guys know anyway to get functions to work on every
workbook?
 
M

mithu

never mind i get it you have to call it from the personal.xls file

so i would have to put

=vlookup(personal.xls!numberit(a1)....


thanks guys
 
G

Gord Dibben

That's the main reason I put my macros and functions in an Add-in.

You don't have to preface with the filename.

Save a copy of Personal.xls as File Type>MS Excel Add-in(*.xla)

Stick it in your Office\Library folder and Load it through Tools>Add-ins.

Leave it checked and it is always available.

Remove Personal.xls from your XLSTART folder.

One note: the Macros don't show up in Tools>Macro>Macros.


Gord Dibben MS Excel MVP
 
M

mithu

Thats a great idea Gord,

Thanks for the tip

That's the main reason I put my macros and functions in an Add-in.

You don't have to preface with the filename.

Save a copy of Personal.xls as File Type>MS Excel Add-in(*.xla)

Stick it in your Office\Library folder and Load it through Tools>Add-ins.

Leave it checked and it is always available.

Remove Personal.xls from your XLSTART folder.

One note: the Macros don't show up in Tools>Macro>Macros.

Gord Dibben MS Excel MVP





- Show quoted text -
Click to expand...
 
Top