Moving workbook with VBA between computers

[Revolvr]

Hi all,

I have an Excel workbook with VBA macros. If I give my workbook to someone
else to use on a different PC, I get compile errors. The only way out is
ctrl-alt-del.

Bringing up with macros disabled, I see three references that are missing:
funcres, atpvbaen and solver (I am using the solver in the VBA).

I can resolve the missing references on one PC, but then if I transfer it
back to the other PC the references are missing again.

So I tried some auto-open code to re-create all of the references. This
doesn't work apparently because the code can't compile without these
references. Kind of a Catch-22?

Is there anything I can do here to allow multiple users on different PC's to
use my workbook????

The code I run on open (but doesn't) is:


Private Sub Workbook_Open()
Application.Windows.Arrange xlArrangeStyleTiled

Dim wb As Workbook
Dim ReferencePath As String

On Error Resume Next
' Set a Reference to the workbook that will hold Solver
Set wb = ActiveWorkbook

With AddIns("Solver Add-In")
.Installed = False
.Installed = True
End With

With AddIns("Analysis ToolPak")
.Installed = False
.Installed = True
End With

With AddIns("Analysis ToolPak - VBA")
.Installed = False
.Installed = True
End With

ReferencePath = Application.LibraryPath & "\SOLVER\SOLVER.XLA"
wb.VBProject.References.AddFromFile ReferencePath

ReferencePath = Application.LibraryPath & "\Analysis\funcres.XLA"
wb.VBProject.References.AddFromFile ReferencePath

ReferencePath = Application.LibraryPath & "\Analysis\atpvbaen.XLA"
wb.VBProject.References.AddFromFile ReferencePath

End Sub

 

[stacyjhaskins]

Are you all using the same version of Excel? I've noticed some VB variables
are not defined in earlier versions of Excel.

 

[Revolvr]

All PC's run Office 2003 except for one which runs Office 2000. The OS is
either Win2000 or WinXP. What seems to be happening is the paths to the
Analysis and Library files are different with different installations. VBA
apparently cannot find the paths dynamically.