|
An issue with "References" in VBA
Hi all,
I created a file which is going to be used by many people that uses SOLVER -- I put in code that I obtained from a site (Jon Peltier's site) that automatically installs SOLVER every time the file is opened (the code is within a module that is called in the Private Sub Workbook_Open()). The issue is that when I sent the file to a user and the user sent the file back to me (after inputting data into the file and using SOLVER), when I open the file, I get the following error: "Compile error: Can't find project of library" Below is the code -- the line of code preceded by ** is the line that is highlighted when the error comes up (8 lines down). Does anyone have any idea on why this would occur? Public Function CheckSolver() As Boolean '' Adjusted for Application.Run() to avoid Reference problems with Solver '' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved. '' Returns True if Solver can be used, False if not. Dim bSolverInstalled As Boolean ** If gbDebug Then Debug.Print Now, "CheckSolver " '' Assume true unless otherwise CheckSolver = True On Error Resume Next ' check whether Solver is installed bSolverInstalled = Application.AddIns("Solver Add-In").Installed Err.Clear If bSolverInstalled Then ' uninstall temporarily Application.AddIns("Solver Add-In").Installed = False ' check whether Solver is installed (should be false) bSolverInstalled = Application.AddIns("Solver Add-In").Installed End If If Not bSolverInstalled Then ' (re)install Solver Application.AddIns("Solver Add-In").Installed = True ' check whether Solver is installed (should be true) bSolverInstalled = Application.AddIns("Solver Add-In").Installed End If If Not bSolverInstalled Then MsgBox "Solver not found. This workbook will not work.", vbCritical CheckSolver = False End If If CheckSolver Then ' initialize Solver Application.Run "Solver.xla!Solver.Solver2.Auto_open" End If On Error GoTo 0 End Function Public Function CheckAntoolpak() As Boolean '' Adjusted for Application.Run() to avoid Reference problems with Analysis ToolPak '' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved. '' Returns True if Analysis ToolPak can be used, False if not. Dim bantoolpakInstalled As Boolean If gbDebug Then Debug.Print Now, "Checkantoolpak " '' Assume true unless otherwise CheckAntoolpak = True On Error Resume Next ' check whether Analysis ToolPak is installed bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed Err.Clear If bantoolpakInstalled Then ' uninstall temporarily Application.AddIns("Analysis ToolPak").Installed = False ' check whether Analysis ToolPak is installed (should be false) bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed End If If Not bantoolpakInstalled Then ' (re)install Analysis ToolPak Application.AddIns("Analysis ToolPak").Installed = True ' check whether Analysis ToolPak is installed (should be true) bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed End If If Not bantoolpakInstalled Then MsgBox "Analysis ToolPak not found. This workbook will not work.", vbCritical CheckAntoolpak = False End If If CheckAntoolpak Then ' initialize Analysis ToolPak Application.Run "Analysis ToolPak.xla!Analysis ToolPak.Auto_open" End If On Error GoTo 0 End Function -- Robert |
An issue with "References" in VBA
Hi Robert,
Sounds like a different version of sofware problem. You have office 9, the other user 10 opens and saves. Now all reference are made to the new library 11 you don't have. Look for missing librairies under Tool?References. Regards JY "robs3131" wrote in message ... Hi all, I created a file which is going to be used by many people that uses SOLVER -- I put in code that I obtained from a site (Jon Peltier's site) that automatically installs SOLVER every time the file is opened (the code is within a module that is called in the Private Sub Workbook_Open()). The issue is that when I sent the file to a user and the user sent the file back to me (after inputting data into the file and using SOLVER), when I open the file, I get the following error: "Compile error: Can't find project of library" Below is the code -- the line of code preceded by ** is the line that is highlighted when the error comes up (8 lines down). Does anyone have any idea on why this would occur? Public Function CheckSolver() As Boolean '' Adjusted for Application.Run() to avoid Reference problems with Solver '' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved. '' Returns True if Solver can be used, False if not. Dim bSolverInstalled As Boolean ** If gbDebug Then Debug.Print Now, "CheckSolver " '' Assume true unless otherwise CheckSolver = True On Error Resume Next ' check whether Solver is installed bSolverInstalled = Application.AddIns("Solver Add-In").Installed Err.Clear If bSolverInstalled Then ' uninstall temporarily Application.AddIns("Solver Add-In").Installed = False ' check whether Solver is installed (should be false) bSolverInstalled = Application.AddIns("Solver Add-In").Installed End If If Not bSolverInstalled Then ' (re)install Solver Application.AddIns("Solver Add-In").Installed = True ' check whether Solver is installed (should be true) bSolverInstalled = Application.AddIns("Solver Add-In").Installed End If If Not bSolverInstalled Then MsgBox "Solver not found. This workbook will not work.", vbCritical CheckSolver = False End If If CheckSolver Then ' initialize Solver Application.Run "Solver.xla!Solver.Solver2.Auto_open" End If On Error GoTo 0 End Function Public Function CheckAntoolpak() As Boolean '' Adjusted for Application.Run() to avoid Reference problems with Analysis ToolPak '' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved. '' Returns True if Analysis ToolPak can be used, False if not. Dim bantoolpakInstalled As Boolean If gbDebug Then Debug.Print Now, "Checkantoolpak " '' Assume true unless otherwise CheckAntoolpak = True On Error Resume Next ' check whether Analysis ToolPak is installed bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed Err.Clear If bantoolpakInstalled Then ' uninstall temporarily Application.AddIns("Analysis ToolPak").Installed = False ' check whether Analysis ToolPak is installed (should be false) bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed End If If Not bantoolpakInstalled Then ' (re)install Analysis ToolPak Application.AddIns("Analysis ToolPak").Installed = True ' check whether Analysis ToolPak is installed (should be true) bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed End If If Not bantoolpakInstalled Then MsgBox "Analysis ToolPak not found. This workbook will not work.", vbCritical CheckAntoolpak = False End If If CheckAntoolpak Then ' initialize Analysis ToolPak Application.Run "Analysis ToolPak.xla!Analysis ToolPak.Auto_open" End If On Error GoTo 0 End Function -- Robert |
An issue with "References" in VBA
Robert,
Look for a missing reference, and uncheck it then find one that you do have. This sort of problem often happens if you are developing your code in, say, Office 2003, and the user is using Office 2000.... HTH, Bernie MS Excel MVP "robs3131" wrote in message ... Hi all, I created a file which is going to be used by many people that uses SOLVER -- I put in code that I obtained from a site (Jon Peltier's site) that automatically installs SOLVER every time the file is opened (the code is within a module that is called in the Private Sub Workbook_Open()). The issue is that when I sent the file to a user and the user sent the file back to me (after inputting data into the file and using SOLVER), when I open the file, I get the following error: "Compile error: Can't find project of library" Below is the code -- the line of code preceded by ** is the line that is highlighted when the error comes up (8 lines down). Does anyone have any idea on why this would occur? Public Function CheckSolver() As Boolean '' Adjusted for Application.Run() to avoid Reference problems with Solver '' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved. '' Returns True if Solver can be used, False if not. Dim bSolverInstalled As Boolean ** If gbDebug Then Debug.Print Now, "CheckSolver " '' Assume true unless otherwise CheckSolver = True On Error Resume Next ' check whether Solver is installed bSolverInstalled = Application.AddIns("Solver Add-In").Installed Err.Clear If bSolverInstalled Then ' uninstall temporarily Application.AddIns("Solver Add-In").Installed = False ' check whether Solver is installed (should be false) bSolverInstalled = Application.AddIns("Solver Add-In").Installed End If If Not bSolverInstalled Then ' (re)install Solver Application.AddIns("Solver Add-In").Installed = True ' check whether Solver is installed (should be true) bSolverInstalled = Application.AddIns("Solver Add-In").Installed End If If Not bSolverInstalled Then MsgBox "Solver not found. This workbook will not work.", vbCritical CheckSolver = False End If If CheckSolver Then ' initialize Solver Application.Run "Solver.xla!Solver.Solver2.Auto_open" End If On Error GoTo 0 End Function Public Function CheckAntoolpak() As Boolean '' Adjusted for Application.Run() to avoid Reference problems with Analysis ToolPak '' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved. '' Returns True if Analysis ToolPak can be used, False if not. Dim bantoolpakInstalled As Boolean If gbDebug Then Debug.Print Now, "Checkantoolpak " '' Assume true unless otherwise CheckAntoolpak = True On Error Resume Next ' check whether Analysis ToolPak is installed bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed Err.Clear If bantoolpakInstalled Then ' uninstall temporarily Application.AddIns("Analysis ToolPak").Installed = False ' check whether Analysis ToolPak is installed (should be false) bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed End If If Not bantoolpakInstalled Then ' (re)install Analysis ToolPak Application.AddIns("Analysis ToolPak").Installed = True ' check whether Analysis ToolPak is installed (should be true) bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed End If If Not bantoolpakInstalled Then MsgBox "Analysis ToolPak not found. This workbook will not work.", vbCritical CheckAntoolpak = False End If If CheckAntoolpak Then ' initialize Analysis ToolPak Application.Run "Analysis ToolPak.xla!Analysis ToolPak.Auto_open" End If On Error GoTo 0 End Function -- Robert |
An issue with "References" in VBA
As Bernie said, check for missing references.
You should remove any mention of me from the second bit of posted code. I only did the work for SOLVER, and I have no idea whether the Analysis Toolpak misbehaves in the same way that SOLVER does. They were not written in parallel, nor even by the same teams of people, so using my SOLVER code on the ATP is not likely to work. In fact, there is a separate VBA version of the ATP for using the ATP functionality from VBA. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "robs3131" wrote in message ... Hi all, I created a file which is going to be used by many people that uses SOLVER -- I put in code that I obtained from a site (Jon Peltier's site) that automatically installs SOLVER every time the file is opened (the code is within a module that is called in the Private Sub Workbook_Open()). The issue is that when I sent the file to a user and the user sent the file back to me (after inputting data into the file and using SOLVER), when I open the file, I get the following error: "Compile error: Can't find project of library" Below is the code -- the line of code preceded by ** is the line that is highlighted when the error comes up (8 lines down). Does anyone have any idea on why this would occur? Public Function CheckSolver() As Boolean '' Adjusted for Application.Run() to avoid Reference problems with Solver '' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved. '' Returns True if Solver can be used, False if not. Dim bSolverInstalled As Boolean ** If gbDebug Then Debug.Print Now, "CheckSolver " '' Assume true unless otherwise CheckSolver = True On Error Resume Next ' check whether Solver is installed bSolverInstalled = Application.AddIns("Solver Add-In").Installed Err.Clear If bSolverInstalled Then ' uninstall temporarily Application.AddIns("Solver Add-In").Installed = False ' check whether Solver is installed (should be false) bSolverInstalled = Application.AddIns("Solver Add-In").Installed End If If Not bSolverInstalled Then ' (re)install Solver Application.AddIns("Solver Add-In").Installed = True ' check whether Solver is installed (should be true) bSolverInstalled = Application.AddIns("Solver Add-In").Installed End If If Not bSolverInstalled Then MsgBox "Solver not found. This workbook will not work.", vbCritical CheckSolver = False End If If CheckSolver Then ' initialize Solver Application.Run "Solver.xla!Solver.Solver2.Auto_open" End If On Error GoTo 0 End Function Public Function CheckAntoolpak() As Boolean '' Adjusted for Application.Run() to avoid Reference problems with Analysis ToolPak '' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved. '' Returns True if Analysis ToolPak can be used, False if not. Dim bantoolpakInstalled As Boolean If gbDebug Then Debug.Print Now, "Checkantoolpak " '' Assume true unless otherwise CheckAntoolpak = True On Error Resume Next ' check whether Analysis ToolPak is installed bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed Err.Clear If bantoolpakInstalled Then ' uninstall temporarily Application.AddIns("Analysis ToolPak").Installed = False ' check whether Analysis ToolPak is installed (should be false) bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed End If If Not bantoolpakInstalled Then ' (re)install Analysis ToolPak Application.AddIns("Analysis ToolPak").Installed = True ' check whether Analysis ToolPak is installed (should be true) bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed End If If Not bantoolpakInstalled Then MsgBox "Analysis ToolPak not found. This workbook will not work.", vbCritical CheckAntoolpak = False End If If CheckAntoolpak Then ' initialize Analysis ToolPak Application.Run "Analysis ToolPak.xla!Analysis ToolPak.Auto_open" End If On Error GoTo 0 End Function -- Robert |
An issue with "References" in VBA
Thanks for your reply, though I still have a couple of questions:
1 - I went into References and saw the following missing item checked: "MISSING: Ref Edit Control" What I'm not clear on is after I uncheck this, when you say that I need to then find the one I do have -- I'm not sure what you mean. Do I need to scroll down the list of all Reference choices and look for "Ref Edit Control"? By the way, I did do that, but didn't find it. 2 - Is there any way to automate the unchecking of any/all missing references and then rechecking the correct reference? -- Robert "Bernie Deitrick" wrote: Robert, Look for a missing reference, and uncheck it then find one that you do have. This sort of problem often happens if you are developing your code in, say, Office 2003, and the user is using Office 2000.... HTH, Bernie MS Excel MVP "robs3131" wrote in message ... Hi all, I created a file which is going to be used by many people that uses SOLVER -- I put in code that I obtained from a site (Jon Peltier's site) that automatically installs SOLVER every time the file is opened (the code is within a module that is called in the Private Sub Workbook_Open()). The issue is that when I sent the file to a user and the user sent the file back to me (after inputting data into the file and using SOLVER), when I open the file, I get the following error: "Compile error: Can't find project of library" Below is the code -- the line of code preceded by ** is the line that is highlighted when the error comes up (8 lines down). Does anyone have any idea on why this would occur? Public Function CheckSolver() As Boolean '' Adjusted for Application.Run() to avoid Reference problems with Solver '' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved. '' Returns True if Solver can be used, False if not. Dim bSolverInstalled As Boolean ** If gbDebug Then Debug.Print Now, "CheckSolver " '' Assume true unless otherwise CheckSolver = True On Error Resume Next ' check whether Solver is installed bSolverInstalled = Application.AddIns("Solver Add-In").Installed Err.Clear If bSolverInstalled Then ' uninstall temporarily Application.AddIns("Solver Add-In").Installed = False ' check whether Solver is installed (should be false) bSolverInstalled = Application.AddIns("Solver Add-In").Installed End If If Not bSolverInstalled Then ' (re)install Solver Application.AddIns("Solver Add-In").Installed = True ' check whether Solver is installed (should be true) bSolverInstalled = Application.AddIns("Solver Add-In").Installed End If If Not bSolverInstalled Then MsgBox "Solver not found. This workbook will not work.", vbCritical CheckSolver = False End If If CheckSolver Then ' initialize Solver Application.Run "Solver.xla!Solver.Solver2.Auto_open" End If On Error GoTo 0 End Function Public Function CheckAntoolpak() As Boolean '' Adjusted for Application.Run() to avoid Reference problems with Analysis ToolPak '' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved. '' Returns True if Analysis ToolPak can be used, False if not. Dim bantoolpakInstalled As Boolean If gbDebug Then Debug.Print Now, "Checkantoolpak " '' Assume true unless otherwise CheckAntoolpak = True On Error Resume Next ' check whether Analysis ToolPak is installed bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed Err.Clear If bantoolpakInstalled Then ' uninstall temporarily Application.AddIns("Analysis ToolPak").Installed = False ' check whether Analysis ToolPak is installed (should be false) bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed End If If Not bantoolpakInstalled Then ' (re)install Analysis ToolPak Application.AddIns("Analysis ToolPak").Installed = True ' check whether Analysis ToolPak is installed (should be true) bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed End If If Not bantoolpakInstalled Then MsgBox "Analysis ToolPak not found. This workbook will not work.", vbCritical CheckAntoolpak = False End If If CheckAntoolpak Then ' initialize Analysis ToolPak Application.Run "Analysis ToolPak.xla!Analysis ToolPak.Auto_open" End If On Error GoTo 0 End Function -- Robert |
An issue with "References" in VBA
Hi Jon,
Sorry - I will remove that comment from the ATP code. Btw - what I did was take the code you created to add Solver and I adjusted it to apply to ATP. So far there have been no issues with the ATP add code. Is there any custom made code that you know of that does add ATP? If so, I would appreciate it if you could let me know. Thanks! -- Robert "Jon Peltier" wrote: As Bernie said, check for missing references. You should remove any mention of me from the second bit of posted code. I only did the work for SOLVER, and I have no idea whether the Analysis Toolpak misbehaves in the same way that SOLVER does. They were not written in parallel, nor even by the same teams of people, so using my SOLVER code on the ATP is not likely to work. In fact, there is a separate VBA version of the ATP for using the ATP functionality from VBA. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "robs3131" wrote in message ... Hi all, I created a file which is going to be used by many people that uses SOLVER -- I put in code that I obtained from a site (Jon Peltier's site) that automatically installs SOLVER every time the file is opened (the code is within a module that is called in the Private Sub Workbook_Open()). The issue is that when I sent the file to a user and the user sent the file back to me (after inputting data into the file and using SOLVER), when I open the file, I get the following error: "Compile error: Can't find project of library" Below is the code -- the line of code preceded by ** is the line that is highlighted when the error comes up (8 lines down). Does anyone have any idea on why this would occur? Public Function CheckSolver() As Boolean '' Adjusted for Application.Run() to avoid Reference problems with Solver '' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved. '' Returns True if Solver can be used, False if not. Dim bSolverInstalled As Boolean ** If gbDebug Then Debug.Print Now, "CheckSolver " '' Assume true unless otherwise CheckSolver = True On Error Resume Next ' check whether Solver is installed bSolverInstalled = Application.AddIns("Solver Add-In").Installed Err.Clear If bSolverInstalled Then ' uninstall temporarily Application.AddIns("Solver Add-In").Installed = False ' check whether Solver is installed (should be false) bSolverInstalled = Application.AddIns("Solver Add-In").Installed End If If Not bSolverInstalled Then ' (re)install Solver Application.AddIns("Solver Add-In").Installed = True ' check whether Solver is installed (should be true) bSolverInstalled = Application.AddIns("Solver Add-In").Installed End If If Not bSolverInstalled Then MsgBox "Solver not found. This workbook will not work.", vbCritical CheckSolver = False End If If CheckSolver Then ' initialize Solver Application.Run "Solver.xla!Solver.Solver2.Auto_open" End If On Error GoTo 0 End Function Public Function CheckAntoolpak() As Boolean '' Adjusted for Application.Run() to avoid Reference problems with Analysis ToolPak '' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved. '' Returns True if Analysis ToolPak can be used, False if not. Dim bantoolpakInstalled As Boolean If gbDebug Then Debug.Print Now, "Checkantoolpak " '' Assume true unless otherwise CheckAntoolpak = True On Error Resume Next ' check whether Analysis ToolPak is installed bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed Err.Clear If bantoolpakInstalled Then ' uninstall temporarily Application.AddIns("Analysis ToolPak").Installed = False ' check whether Analysis ToolPak is installed (should be false) bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed End If If Not bantoolpakInstalled Then ' (re)install Analysis ToolPak Application.AddIns("Analysis ToolPak").Installed = True ' check whether Analysis ToolPak is installed (should be true) bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed End If If Not bantoolpakInstalled Then MsgBox "Analysis ToolPak not found. This workbook will not work.", vbCritical CheckAntoolpak = False End If If CheckAntoolpak Then ' initialize Analysis ToolPak Application.Run "Analysis ToolPak.xla!Analysis ToolPak.Auto_open" End If On Error GoTo 0 End Function -- Robert |
An issue with "References" in VBA
I have no experience programming to the ATP. I don't know why the code I
posted for Solver should be relevant. I just want to make sure I don't get credit for something I didn't do. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "robs3131" wrote in message ... Hi Jon, Sorry - I will remove that comment from the ATP code. Btw - what I did was take the code you created to add Solver and I adjusted it to apply to ATP. So far there have been no issues with the ATP add code. Is there any custom made code that you know of that does add ATP? If so, I would appreciate it if you could let me know. Thanks! -- Robert "Jon Peltier" wrote: As Bernie said, check for missing references. You should remove any mention of me from the second bit of posted code. I only did the work for SOLVER, and I have no idea whether the Analysis Toolpak misbehaves in the same way that SOLVER does. They were not written in parallel, nor even by the same teams of people, so using my SOLVER code on the ATP is not likely to work. In fact, there is a separate VBA version of the ATP for using the ATP functionality from VBA. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "robs3131" wrote in message ... Hi all, I created a file which is going to be used by many people that uses SOLVER -- I put in code that I obtained from a site (Jon Peltier's site) that automatically installs SOLVER every time the file is opened (the code is within a module that is called in the Private Sub Workbook_Open()). The issue is that when I sent the file to a user and the user sent the file back to me (after inputting data into the file and using SOLVER), when I open the file, I get the following error: "Compile error: Can't find project of library" Below is the code -- the line of code preceded by ** is the line that is highlighted when the error comes up (8 lines down). Does anyone have any idea on why this would occur? Public Function CheckSolver() As Boolean '' Adjusted for Application.Run() to avoid Reference problems with Solver '' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved. '' Returns True if Solver can be used, False if not. Dim bSolverInstalled As Boolean ** If gbDebug Then Debug.Print Now, "CheckSolver " '' Assume true unless otherwise CheckSolver = True On Error Resume Next ' check whether Solver is installed bSolverInstalled = Application.AddIns("Solver Add-In").Installed Err.Clear If bSolverInstalled Then ' uninstall temporarily Application.AddIns("Solver Add-In").Installed = False ' check whether Solver is installed (should be false) bSolverInstalled = Application.AddIns("Solver Add-In").Installed End If If Not bSolverInstalled Then ' (re)install Solver Application.AddIns("Solver Add-In").Installed = True ' check whether Solver is installed (should be true) bSolverInstalled = Application.AddIns("Solver Add-In").Installed End If If Not bSolverInstalled Then MsgBox "Solver not found. This workbook will not work.", vbCritical CheckSolver = False End If If CheckSolver Then ' initialize Solver Application.Run "Solver.xla!Solver.Solver2.Auto_open" End If On Error GoTo 0 End Function Public Function CheckAntoolpak() As Boolean '' Adjusted for Application.Run() to avoid Reference problems with Analysis ToolPak '' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved. '' Returns True if Analysis ToolPak can be used, False if not. Dim bantoolpakInstalled As Boolean If gbDebug Then Debug.Print Now, "Checkantoolpak " '' Assume true unless otherwise CheckAntoolpak = True On Error Resume Next ' check whether Analysis ToolPak is installed bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed Err.Clear If bantoolpakInstalled Then ' uninstall temporarily Application.AddIns("Analysis ToolPak").Installed = False ' check whether Analysis ToolPak is installed (should be false) bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed End If If Not bantoolpakInstalled Then ' (re)install Analysis ToolPak Application.AddIns("Analysis ToolPak").Installed = True ' check whether Analysis ToolPak is installed (should be true) bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed End If If Not bantoolpakInstalled Then MsgBox "Analysis ToolPak not found. This workbook will not work.", vbCritical CheckAntoolpak = False End If If CheckAntoolpak Then ' initialize Analysis ToolPak Application.Run "Analysis ToolPak.xla!Analysis ToolPak.Auto_open" End If On Error GoTo 0 End Function -- Robert |
An issue with "References" in VBA
I've had this issue, with MISSING RefEdit references when moving a file from
2007 to 2003. It shouldn't be a problem, it's never been a problem with RefEdits in previous versions. I also found that simply unchecking the bad reference worked. I don't know if the problem returns for the same or other files. If this is happening, it's time to clear the temp directory: C:\Documents and Settings\<username\Local Settings\Temp and also delete any *.exd files in this directory: C:\Documents and Settings\<username\Application Data\Microsoft\Forms - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "robs3131" wrote in message ... Thanks for your reply, though I still have a couple of questions: 1 - I went into References and saw the following missing item checked: "MISSING: Ref Edit Control" What I'm not clear on is after I uncheck this, when you say that I need to then find the one I do have -- I'm not sure what you mean. Do I need to scroll down the list of all Reference choices and look for "Ref Edit Control"? By the way, I did do that, but didn't find it. 2 - Is there any way to automate the unchecking of any/all missing references and then rechecking the correct reference? -- Robert "Bernie Deitrick" wrote: Robert, Look for a missing reference, and uncheck it then find one that you do have. This sort of problem often happens if you are developing your code in, say, Office 2003, and the user is using Office 2000.... HTH, Bernie MS Excel MVP "robs3131" wrote in message ... Hi all, I created a file which is going to be used by many people that uses SOLVER -- I put in code that I obtained from a site (Jon Peltier's site) that automatically installs SOLVER every time the file is opened (the code is within a module that is called in the Private Sub Workbook_Open()). The issue is that when I sent the file to a user and the user sent the file back to me (after inputting data into the file and using SOLVER), when I open the file, I get the following error: "Compile error: Can't find project of library" Below is the code -- the line of code preceded by ** is the line that is highlighted when the error comes up (8 lines down). Does anyone have any idea on why this would occur? Public Function CheckSolver() As Boolean '' Adjusted for Application.Run() to avoid Reference problems with Solver '' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved. '' Returns True if Solver can be used, False if not. Dim bSolverInstalled As Boolean ** If gbDebug Then Debug.Print Now, "CheckSolver " '' Assume true unless otherwise CheckSolver = True On Error Resume Next ' check whether Solver is installed bSolverInstalled = Application.AddIns("Solver Add-In").Installed Err.Clear If bSolverInstalled Then ' uninstall temporarily Application.AddIns("Solver Add-In").Installed = False ' check whether Solver is installed (should be false) bSolverInstalled = Application.AddIns("Solver Add-In").Installed End If If Not bSolverInstalled Then ' (re)install Solver Application.AddIns("Solver Add-In").Installed = True ' check whether Solver is installed (should be true) bSolverInstalled = Application.AddIns("Solver Add-In").Installed End If If Not bSolverInstalled Then MsgBox "Solver not found. This workbook will not work.", vbCritical CheckSolver = False End If If CheckSolver Then ' initialize Solver Application.Run "Solver.xla!Solver.Solver2.Auto_open" End If On Error GoTo 0 End Function Public Function CheckAntoolpak() As Boolean '' Adjusted for Application.Run() to avoid Reference problems with Analysis ToolPak '' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved. '' Returns True if Analysis ToolPak can be used, False if not. Dim bantoolpakInstalled As Boolean If gbDebug Then Debug.Print Now, "Checkantoolpak " '' Assume true unless otherwise CheckAntoolpak = True On Error Resume Next ' check whether Analysis ToolPak is installed bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed Err.Clear If bantoolpakInstalled Then ' uninstall temporarily Application.AddIns("Analysis ToolPak").Installed = False ' check whether Analysis ToolPak is installed (should be false) bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed End If If Not bantoolpakInstalled Then ' (re)install Analysis ToolPak Application.AddIns("Analysis ToolPak").Installed = True ' check whether Analysis ToolPak is installed (should be true) bantoolpakInstalled = Application.AddIns("Analysis ToolPak").Installed End If If Not bantoolpakInstalled Then MsgBox "Analysis ToolPak not found. This workbook will not work.", vbCritical CheckAntoolpak = False End If If CheckAntoolpak Then ' initialize Analysis ToolPak Application.Run "Analysis ToolPak.xla!Analysis ToolPak.Auto_open" End If On Error GoTo 0 End Function -- Robert |
| All times are GMT +1. The time now is 10:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com