isolate macros

  • Thread starter Thread starter pabs
  • Start date Start date
P

pabs

I have a excel file and in it a series of Macros. Let's call it
main.xls

Right now I run a form that executes all the macros from that file.
(main.xls)

The file is used by someone else who enters data to it daily. so when
I need to modify the macro I have to merge my changes with the file he
is modifying.

this is becoming a problem since I plan on making many other
modifications in the newar future

what would be the best way to seperate the two?

I could have a seperate file (call this one execute.xls) with all the
macros and launch my form from that new file. So if I need to modify
any macro I don't need the updated main.xls file.

is there a better way?

another option that came to mind is to make a small app in VB..how easy
would it be to make it an app that could execute the macros and modify
the xls files??


any help here would be great

thanks

Pabs
 
Hi Pabs

It's very difficult to modify and update your macros, if they're in a
shared workbook. There's a very useful method, which is called
"Add-In". All you have to do is, put your makros in a worksheet and
save it to a shared path as an Add-In. After that, install the Add-In
both on the machines. That's all.
 
After you install the add-in, you have 2 ways to call the procedures :

1. You can add a button, which calls a funtion or sub. In this case yo
need to write a sub, which must have the name "public auto_sub" an
add a button on the bar; should be look like this :

Public Sub auto_open()
'**************************************************
'* Add automaticly a custom menu item to menu bar to start
'* the application while installing the add-in
'**************************************************

Dim cmd As CommandBar, cmdctrl As CommandBarControl


If CommandBars(1).FindControl(msoControlButton, 1, "Hauptmenu") I
Nothing Then
Set cmdctrl = CommandBars(1).Controls.Add(msoControlButton, 1)
With cmdctrl
.BeginGroup = True
.Caption = "Hauptmenu"
.Tag = "Hauptmenu"
.DescriptionText = "Hauptmenu"
.Width = 20
.Height = 20
.Enabled = True
.OnAction = "ShowMainMenu"
.Visible = True
.Style = msoButtonCaption
End With
End If
End Sub

and of course you have to remove the button, if you deinstall th
Add-In with an auto_close Sub

Public Sub auto_close()
'**************************************************
'* remove the button from the menubar
'**************************************************
CommandBars(1).FindControl(msoControlButton, 1
"Hauptmenu").Delete
End Sub

2. The second way is much easier, since you don't need a button.
After you install an Add-In, all the subs and functions are availabl
to Excel. To call them, you should try this :

Application.Run "Add-InName.xla!Procedure_Name"

if you have parameters to transmit, you can list them in the correc
order as well, like follows :

Application.Run "Add-InName.xla!Procedure_Name", Parameter1
Parameter2,...,ParameterX

I hope it help
 
Back
Top