Global VBA in Excel?

[dmkAlex]

I've done it before but forgot it because it's been a while.

I want to create a bunch of routine (sub's) which I can use in all my other worksheets.

For example, I want to create a button to auto fit all the columns in any workbook I open. I don't want to duplicate that sub in every workbook.

I recall I have to use the workboot hide function and save it under a certain name in a certain directory so it is called up, but hidden, everytime I start Excel.

Thanks.

 

[Bong]

Can't you just save the module in "VBAProject(PERSONAL.XLS)"?

_________________
Bob Rashkin

 

[kodr]

I thought there was a FAQ on this, but I can't find it.

Basically, you start a blank work book, add your code, and save as a .xla file.

You can then add this file as an Addin "Tools/Add-Ins"

Here is some code you can run to add a new menu item.

Sub menu_editor()

Dim cbrMenuBar As CommandBar
Dim ctlMenu As CommandBarPopup
Dim ctlMenuItem1 As CommandBarButton
Dim ctlMenuItem2 As CommandBarButton
Dim ctlMenuItem3 As CommandBarButton

Set cbrMenuBar = CommandBars("Worksheet Menu Bar")
Set ctlMenu = cbrMenuBar.Controls.Add(msoControlPopup)
ctlMenu.Caption = "&New Menu Item"
Set ctlMenuItem1 = ctlMenu.Controls.Add(msoControlButton)
With ctlMenuItem1
    .Caption = ("Name of Sub 1")
    .OnAction = "Sub1"        ' name of sub to run
End With
Set ctlMenuItem2 = ctlMenu.Controls.Add(msoControlButton)
With ctlMenuItem2
    .Caption = ("About")
    .OnAction = "subAbout"
End With
Set ctlMenuItem3 = ctlMenu.Controls.Add(msoControlButton)
With ctlMenuItem3
    .Caption = ("Help")
    .OnAction = "subHelp"
End With



End Sub

And here to reset the menu bar

Sub commandbar_reset()


CommandBars("Worksheet Menu Bar").Reset

End Sub

 

[dmkAlex]

Yes, it's "Personal.xls".

I think you have to "hide" it and save it to a particular directory (at least that's how it was in pervious version (97?)).

What directory?

Thanks.


Alex

 

[dmkAlex]

Now it's coming back to me . . .

The directory is XLSTART.

Still want to clarify . . .

Do I save it as Personal.xls, or xla, or xlt?

Thanks.

Alex

 

[xlbo]

save as Personal.xls but easiest way is to:

Go Tools>MAcro>Record New MAcro
rather than leaving it to save the macro in the current workbook, use the dropdown list to get the macro saved in Personal.xls (it will be 1 of the options)

Do some stuff and then stop recording

You now have a Personal.xls

delete the code lines form the module and close down Excel - you will get a message asking if you want to save changes to your Personal.xls - you do

et voila - next time you open Excel, you will have a Personal.xls file

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dmkAlex]

That did it.
Thanks.

Alex

 

[firefytr]

Btw, the Personal.xls file will always (by default) be hidden. An addin might not be the way to go if you want them globally available, unless you plan on distributing this code to other local machines or over a network.

-----------
Regards,
Zack Barresse