Deploying macro

[BBB]

Searched on google (deploy macro, excel, etc) - couldn't find any info on
this so going to the experts. I want to deploy some macros. The catch is I
don't want to simply include these in a spreadsheet. I want these macros to
be available for any spreadsheet. It would be nice if I could place these
macros someplace convenient for the user to get to them (toolbar, macro
list).

Any pointers, insight would be most appreciated.

BBB

 

[Kalle]

save this workbook as an AddIn (*.xla)

once referenced it will be available to all Workbooks.

 

[Chip Pearson]

Macros must reside in a workbook of some sort; there is no way to
have (executable) code outside of a workbook. The best way, in
your case, would be to save the code in an add-in and distribute
that.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[BBB]

Did that. Thanks. So now when I create a new spreadsheet ... how do I now
launch those macros? They are not listed in my macros list.

Sorry ... probably just showing my ignorance.

 

[Tom Ogilvy]

http://msdn.microsoft.com/library/backgrnd/html/msdn_addins97.htm
http://msdn.microsoft.com/library/officedev/odeopg/deovrcreatingexceladdin.htm
http://www.microsoft.com/exceldev/tips/addins.htm
These are about distributing applications

 

[BBB]

So once I save my macro code as an add-in. How to I utilize those macros
from a new spreadsheet? I do not see them in my macros list.

 

[Chief S.]

Did that. Thanks. So now when I create a new spreadsheet ... how do I now
launch those macros? They are not listed in my macros list.

The AddIn needs to create its own interface -- for example, a menu or
some buttons that the user can access to run the macros.

Here's a simple example that is a slimmed-down version of an AddIn I
distribute to coworkers.

Option Explicit

Private Sub Auto_Open()
'Create the menu when the AddIn is launched.
ExampleMenu True
End Sub

Private Sub Auto_Close()
'Remove menu when the AddIn is closed.
ExampleMenu False
End Sub

Private Sub ExampleMenu(ByVal makeIt As Boolean)
Dim ctrl As CommandBarControl
Dim helpPos As Integer
Dim mac As Variant
Dim menuName, menuCapt As String
Dim i, j As Integer
'Menu constants
menuName = "Example"
menuCapt = "E&xample"
mac = Array( _
"Macro&1", False, _
"Macro&2", False, _
"Macro&3", True, _
"Macro&4", False)
'Create menu
With CommandBars("Worksheet Menu Bar")
'Delete the menu if it exists, and locate Help menu.
For Each ctrl In .Controls
If ctrl.Caption = menuCapt Then
ctrl.Delete
ElseIf ctrl.Caption = "&Help" Then
helpPos = ctrl.Index
End If
Next
If makeIt = False Then Exit Sub
'Create new menu and store it as an object.
.Controls.Add(Type:=msoControlPopup, Before:=helpPos, _
temporary:=False).Caption = menuCapt
Set ctrl = .Controls(menuName)
End With
'Add commands to menu.
For i = 0 To (UBound(mac) - 1) Step 2
With ctrl.Controls.Add(Type:=msoControlButton)
.Caption = mac(i)
.BeginGroup = mac(i + 1)
j = InStr(1, mac(i), "&")
If j > 0 Then mac(i) = Left(mac(i), j - 1) & _
Right(mac(i), Len(mac(i)) - j)
.OnAction = "Example" & mac(i)
End With
Next
End Sub

Private Sub ExampleMacro1()
MsgBox 1
End Sub

Private Sub ExampleMacro2()
MsgBox 2
End Sub

Private Sub ExampleMacro3()
MsgBox 3
End Sub

Private Sub ExampleMacro4()
MsgBox 4
End Sub

 

[Bob Phillips]

You need to install the addin, from Tools>Addins, not just load the xla
file.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)