Adding a Reference to Excel VBA using code?

[Bobmax]

Hi

I am writing some code to automate Solver solutions on a spreadsheet. In order to make it work, I have to add the Solver Add-In form Excel and enable the Solver Reference in the VBA editor (Tools/References).

My question is: Is it possible to automate the updating of the reference in VBA using VBA code? (I have to send this file to users who are not terribly computer literate).

I couldn't find any references to this in my searches.

Thanks in advance - Bob

 

[CautionMP]

Sub AddAddIn()
Dim MyAddIn as AddIN
Set MyAddIn = Application.AddIns("solver.xla")
MyAddIn.Installed = True
CleanUp:
Set MyAddIn = Nothing
End Sub

Hopr this helps,
CMP

I am sorry I have not succeeded in answering all of your questions.
In fact, I apologize for not completely answering any of them.
The answers I have however do serve to raise a whole new set of questions I had not previously thought of. In some ways, I am as confused as you are but I believe my confusions are (as always) on a higher plane and

 

[Bobmax]

Thanks for the help CMP. Your code turns on Solver in the Workbook (but you need to enter "Solver Add-in" instead of "solver.xla"). I was Ok with this - perhaps I didn't explain my problem fully.

When you install the Add-in, it loads a Solver Reference in the VBA editor (Tools/References) for Solver but DOES NOT activate it! I need write code for activating this Solver Reference so that VBA recognises the Solver commands otherwise the code fails when run from the workbook for a user who does not have this Reference activated.

Can you assist with this?

Many thanks for your suggestion, though. Bob

 

[CautionMP]

You will need to add a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 (C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB) library. This will allow you to manipulate the References collection.

You should then be able to [tt]AddFromFile()[/tt] or [tt]AddFromGUID()[/tt] to add the solver Reference. Here is an example, you will need to update the file infromation for the Solver:

Sub AddReference()
On Error GoTo AddReference_Err
Dim MyProject As VBProject
Dim MyRef As Reference
Set MyProject = Application.VBE.ActiveVBProject
'Adds Microsoft Visual Basic for Applications Extensibility 5.3
Set MyRef = MyProject.References.AddFromFile("[u]C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB[/u]")

AddReference_Err:
If Err.Number = 32813 Then
  'Do nothing, reference already exists
End If

CleanUp:
Set MyRef = Nothing
Set MyProject = Nothing
End Sub

Hope this Helps,
CMP


I am sorry I have not succeeded in answering all of your questions.
In fact, I apologize for not completely answering any of them.
The answers I have however do serve to raise a whole new set of questions I had not previously thought of. In some ways, I am as confused as you are but I believe my confusions are (as always) on a higher plane and

 

[Bobmax]

Thanks for all your help CMP, I'll give it a whirl! Bob