VBA Code duplication

[tleth]

Hello Mr Excel

Im working on a set of spreadsheets that uses VBA which are all identic.

Is there a way I can put this code in some common file, module or something 3rd and then include that in some way in the other spreadsheets so I can makes updates to the code in just one place?

The VBA consists of subs, functions and event handlers.

Thanks
Leth

 

[evnoort]

Hi tleth,

You can put all your code in an excel add-in an xla file and then use it everywhere.

Regards,

Erik

more details here: http://www.cpearson.com/excel/createaddin.aspx

 

[tleth]

Hi

The spreadsheets are placed on a network share and is to be used by several users.

From what I read about addins each user has to install it them selfs, thats not viable for this.

 

[evnoort]

Hi tleth,

then you can put your code in a module in a "tools" workbook. When opening a workbook this "tools" workbook needs also to be opened then you can use the code in the module.
When closing the workbook also close the tools workbook...but this open could be difficult when adressing ranges etc.

Don't know of any other options, anyone?

I would still use the first xla option. It can be automatically registered with code under a button.

grts, Erik

 

[evnoort]

Here an example to look for a specific xla and adding it to excel

Private Sub cmdInstall_Click()
    Dim a As AddIn
    Dim newAddin As AddIn
    Dim found As Boolean
    
    found = False
    For Each a In AddIns
        Debug.Print a.Path, a.Name
        If StrComp(a.Name, "recovery_generator.xla") = 0 Then
            ' first check which path is used
            res = MsgBox("Currently using: " & vbCrLf & _
                a.Path & " for the location of *.xla, " & _
                vbCrLf & "Is This OK?", vbYesNo)
            If res = vbYes Then
                found = True
                If Not a.Installed Then
                    a.Installed = True
                    Exit For
                End If
            Else
                ' change the location
                found = True
                MsgBox _
                    "There's already an Add-in activated, please remove this from your Add-in list:" & vbCrLf & _
                    "1. Close Excel" & vbCrLf & _
                    "2. Remove recovery_generator.xla from the directory: " & a.Path & vbCrLf & _
                    "3. Re-open this main sheet" & vbCrLf & _
                    "4. Select 'recovery_generator.xla' from the 'Tools->Add-ins' screen " & vbCrLf & _
                    "5. Click 'Yes' when asked to remove the add-in from the list" & vbCrLf & _
                    "6. Press the button 'Install Recovery Add-in' on the sheet 'Tools' and follow the procedure" & vbCrLf & _
                    "(See the file help.txt in your ARE file location)"
                Exit For
            End If
        End If
    Next a
    If Not found Then
        fileToOpen = Application.GetOpenFilename(Filefilter:="Add-ins(*.xla)," & ThisWorkbook.Path & "\recovery_generator.xla", Title:="Open Add-in")
        If fileToOpen <> False Then
            ' change the location
            Set a = AddIns.Add(fileToOpen, False)
            a.Installed = True
        End If
    End If
End Sub

 

[tleth]

In order for that code snippet to work, isn't it required for the users of the spreadsheet to place the .xla file into their Application.UserLibraryPath on their own?

Or is it possible to install the addon if both the .xla and the .xlsm reside in the same folder on the network share?

From http://msdn.microsoft.com/en-us/library/bb221976(v=office.12).aspx I can see that the above Path is a read only string, so setting it to the network share isn't possible.

//Leth

 

[evnoort]

In my case the xla is registered to:
C:\Documents and Settings\enoort\My Documents\setup\ARE\Tools
and all the workbooks that work with it in
C:\Documents and Settings\enoort\My Documents\setup\ARE\abc\baseline

but I think a network path should work....