How to add references & add-ins via VBA

[santoshpc]

I've faced this problem several times, especially when it comes to deployment, i.e. when you want to release your excel file to multiple users, running on different office versions and you want your macro to just work!

I searched for a couple of hours yesterday and finally found a few articles (one from this forum itself) that I combined to make a perfect solution.

I wanted to put up this post to help out others like me and save people some valuable time, which can be used to actually write the macros rather than worry about such issues. Also, I felt that I should give something back to this forum, without which I couldn't have got my file up and working.

I'll demonstrating this with the two most common addins that people deal with: Solver & Analysis, but the code can be easily modified for other addins as well.

[VBA]Sub AddinsNReferences()

On Error Resume Next

'First install the add-in on Excel
AddIns("Analysis ToolPak").Installed = True
AddIns("Analysis ToolPak - VBA").Installed = True
AddIns("Solver Add-in").Installed = True

Dim i As Long
Dim flpth As String
Dim theRef As Variant

'All the possible references to the project for the add-ins. XLAM works for 2007/2010 & the others work for the earlier versions.
'The reference files' locations differ based on where the office/excel has been installed. Therefore, I've first determined the Excel location and then I'm modifying it to add the reference.
For i = 1 To ThisWorkbook.VBProject.References.Count
With ThisWorkbook.VBProject.References(i)
If Trim(UCase(.Name)) = "EXCEL" Then
flpth = Left(.FullPath, Len(.FullPath) - 9) + "Library\Analysis\ATPVBAEN.XLA"
ThisWorkbook.VBProject.References.AddFromFile flpth
flpth = Left(.FullPath, Len(.FullPath) - 9) + "Library\Analysis\ATPVBAEN.XLAM"
ThisWorkbook.VBProject.References.AddFromFile flpth
flpth = Left(.FullPath, Len(.FullPath) - 9) + "Library\SOLVER\SOLVER.XLA"
ThisWorkbook.VBProject.References.AddFromFile flpth
flpth = Left(.FullPath, Len(.FullPath) - 9) + "Library\SOLVER\SOLVER.XLAM"
ThisWorkbook.VBProject.References.AddFromFile flpth
Exit For
End If
End With
Next i

'Now we remove the extra references that we added earlier that belong to other versions of office/excel
For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
Set theRef = ThisWorkbook.VBProject.References.Item(i)
If theRef.isbroken = True Then
ThisWorkbook.VBProject.References.Remove theRef
End If
Next i

On Error GoTo 0
End Sub[/VBA]

If you found my post useful, post something to let me know

[Aflatoon]

One caveat: your code assumes that access to the vbproject is trusted.

[lipse]

Taking advantage of thread, theres any way to concede "trust privileges" automatically using vba? or show a form to user?

[Aflatoon]

Depending on version and user permissions, it may be possible to set the access. Easier to use an error handler and inform the user how to do it, or simply late bind.

[santoshpc]

I've written a work-around for the VBA project access issue. Essentially, I'm 1st checking to see if there is any admin security key for excel present at HKLM, if that exists then I'll over-write it. Also, irrespective of whether the HKLM key is there or not I'm moving onto the HKCU excel security key and over-writing that as well.

Before I proceed, I have to give credit to Kevin aka zorvek for his post on experts exchange. I had got the HKCU part from using a tool called systracker but I could not have stumbled onto the HKLM key without his post.

Lastly, This function is pretty easy to use (I can't think of any other way to make it easier for the user :P). Pass True to enable VBA Project Access and False to disable.

Function VBAP_Access(ByVal val As Boolean)

Dim SHost As Object
Dim Path As String

On Error GoTo UserIntv
Set SHost = CreateObject("WScript.Shell")

On Error Resume Next
'First, check if the HKLocalMachine key for excel security is present
Path = "HKLM\SOFTWARE\Microsoft\Office\" + Trim(Application.Version) + "\Excel\Security\AccessVBOM"
SHost.RegRead (Path)
If Err.Number = 0 Then 'If the key is present then enable/disable access to the VBA Project
If val = True Then
SHost.RegWrite Path, 1, "REG_DWORD"
Else
SHost.RegWrite Path, 0, "REG_DWORD"
End If
End If
Err.Clear

On Error GoTo UserIntv
'Second, overwrite the HKCurrentUser key to enable/disable access to the VBA Project
Path = "HKCU\SOFTWARE\Microsoft\Office\" + Trim(Application.Version) + "\Excel\Security\AccessVBOM"
If val = True Then
SHost.RegWrite Path, 1, "REG_DWORD"
Else
SHost.RegWrite Path, 0, "REG_DWORD"
End If

On Error GoTo 0
Exit Function
UserIntv:
Err.Clear
MsgBox ("Please Enable 'Access to VBA Project Object Model' in Options -> Trust Settings or Security -> Macro Security")
On Error GoTo 0
End Function

As usual, suggestions will be taken constructively and if you find this function useful then let me know

[Aflatoon]

That assumes that the user has rights to do that sort of thing.

[santoshpc]

Yeah, this method would fail if the user doesn't have permission to edit the registry.