PDA

View Full Version : Using Share Drive/Point to access Macros



PianoMan5
07-10-2012, 10:25 AM
Excuse my ignorance but I have searched IE and this forum but haven't found an answer. Is there a way save macros I have created to a sharedrive and have any user access that macro?

If I make any updates to the macro, the users will automatically see the update the next time he/she runs it.

CodeNinja
07-10-2012, 11:45 AM
In order to assure if you make changes to a macro they will get the latest version, I think you need to load it every time on load of the application... You can do this with extensibility... here is how:

First, load the reference "Microsoft Visual Basic for Applications Extensibility 5.3 into your references. You can do this in the Visual Basic editor by clicking tools / references and scrolling down to find the reference. Check it, and click ok.

Second, in this workbook, go to workbook_open() sub and enter the following code:

Be sure to change the path and macro name to suit your needs.

'requires 'Microsoft Visual Basic for Applications Extensibility 5.3' reference
Dim vbProj As VBIDE.VBProject
Dim vbcomp As VBIDE.VBComponent
Dim i As Integer
Dim sMacroName As String
Dim sMacroPath As String

Set vbProj = ThisWorkbook.VBProject
sMacroName = "" 'put your macro name here...
sMacroPath = "" 'Put your macro path here...

' check to see it is not already loaded... if it is, delete loaded macro
For i = 1 To vbProj.VBComponents.Count
If vbProj.VBComponents(i).Name = sMacroName Then
ActiveWorkbook.VBProject.VBComponents.Remove ActiveWorkbook.VBProject.VBComponents(sMacroName)
End If
Next i
' Now load the most up to date macro
vbProj.VBComponents.Import (sMacroPath & sMacroName & ".bas")