How do I deploy macros?

[n2nuk]

I have created a macro that calls other macros to perform a number of formatting jobs on all 50+ worksheets on an open spreadsheet.

The macro runs fine and I now need to deploy this code to all our users who are using this spreadsheet.

Is there an easy way to do this?

Also I was going to ask users to temporariliy set the security level to low to run the macro, is there way programmatically to do this? (I dont want users forgetting to set security back to high)

Many thanks

Naz

 

[SkipVought]

Hi,

If the code module is in the workbook of interest, it is deployed when other users open that workbook.

If the code module is in some other workbook, is it just the code, or are there sheets in that workbook that have necessary information?

I deploy modules, by giving the module a unique descriptive name, EXPORTING to a network drive. The users can IMPORT the module into their PERSONAL.xls workbook and have it available at all times for their use.

Skip,
[sub]
[red][/red]
[/sub]

 

[n2nuk]

The code is in some other workbook - its only the code I need to supply.

I like your suggestion regarding importing the code module but cant see anywhere on the macro dialog screen to import modules or is this done from the code editor?

I think asking my users to go to the code editor will be a step too far!

 

[SkipVought]

alt+F11 toggles between the VB Editor and the Active Sheet.

In the VB Editor, ctr+R displays the Project Explorer.

Richt-click in your project to select EXPORT among the choices.

Skip,
[sub]
[red][/red]
[/sub]

 

[duGly]

Also I was going to ask users to temporariliy set the security level to low to run the macro, is there way programmatically to do this? (I dont want users forgetting to set security back to high)

Naz,

Unfortunately, there is no way to programmatically change the security setting in an MS Office app. Actually, I suppose that's fortunate, because doing so would allow any hacker to destroy your computer by disabling your security setting before allowing the malicious code to run.

However, I would not have your users set their security setting to low. Even just to run your code. Who can absolutely guarantee that the code they run has not been modified since the time you penned it?

Instead, I would suggest adding a digital certificate to your VBA project. This assures your user that the code was written by you (or, at least, on the computer which generated your digital certificate), and will allow your users to keep their security setting on high. VBA's help files give instructions on creating, attaching, exporting, and importing a digital certificate, but please let me know if you need further assistance with it.

[red]— Artificial intelligence is no match for natural stupidity.[/red]

 

[cresbydotcom]

I invoke from other workbooks regularly.

If you open two workbooks at once - in the area of the toolbars area click customize and create new toolbar then select commands/ macros and choose macro and drag and drop an icon to that toolbar. Right click the icon and pick choose macro all open workbooks - pick a macro from your named file and edit the icon.

As long as the macro file does not move folder it should invoke the macro. If you want the macro to work on another spreadsheet then consider the VBA command

Workbooks.Item("filenameblahblah").Activate
Activesheet."your command here"
I find VBA help useful - if contorted. Install it!

 

[Gavona]

This might help

http://www.exceltip.com/st/Insert_a_new_module_from_a_file_using_VBA_in_Microsoft_Excel/507.html

Gavin

 

[Gavona]

Found it! I used this to enable the end user to transfer some code in a workbook I sent them to their Personal.xls

Private Sub CommandButton1_Click()
' CopyOneModule()

Dim FName As String
With ThisWorkbook
    FName = .Path & "\code.txt"
    .VBProject.VBComponents("GK_Footer").Export FName
End With
'On Error GoTo ERRORHANDLER
Workbooks("Personal.xls").VBProject.VBComponents.Import FName
Kill FName
GoTo Endthis
ERRORHANDLER:
MsgBox ("Routine failed - Personal.xls probably does not exist")
Endthis:
End Sub

Gavin