Solver Reference to Add-in

I have an add-in that contains a time series forecasting routine in VBA that 
requires the use of the Solver add-in.  I want to use VBA to establish a 
reference to the Solver add-in for the forecasting add-in.  I have attempted 
the following with limited success.  Any suggestions would be much 
appreciated.

Thanks.

Private Sub Workbook_Open()
    
    'Install the custom menu
    Call ThisWorkbook.CreateMenu
    
    'Check to make sure that Solver add-in is installed
    Call ThisWorkbook.CheckSolver
    
    'Check the version of Excel and warn the user if an older version.
    Call ThisWorkbook.CheckVersion
    
    'Establish a reference to solver if not already established.
    'Creates an error 400 if already installed
    On Error Resume Next
    With ThisWorkbook.VBProject.References
        .Remove .Item("SOLVER")
        .AddFromFile Application.LibraryPath & "\SOLVER\SOLVER.xla"
    End With
    
    With AddIns("Solver Add-In")
        .Installed = False
        .Installed = True
        ThisWorkbook.VBProject.References.AddFromFile _
            Application.LibraryPath & "\SOLVER\SOLVER.xla"
    End With
    On Error GoTo 0

End Sub

Sub CheckSolver()
Dim a
    'Subroutine checks for solver when Excel is opened
    Set a = AddIns("Solver Add-In")
    If a.Installed = False Then
        MsgBox "The Solver add-in is not installed!" & vbCr & _
               "Some functionality may be lost."
    End If
End Sub


0
Chad9044 (93)
7/20/2007 2:32:03 PM
excel 39879 articles. 2 followers. Follow

2 Replies
460 Views

Similar Articles

[PageSpeed] 47

Why not let the user take care of it...

Sub MakeThemDoIt()
  Dim strName As String
  strName = "SOLVER.XLA"
  If IsItOpen(strName) = False Then
     Application.Cursor = xlDefault
     MsgBox "The SOLVER.XLA add-in is required." & vbCr & _
            "Go to the Tools menu and select Add-Ins.   " & vbCr & _
            "Checkmark...  Solver Add-In", vbInformation, "Chad Said So"
  End If
End Sub
'--
Function IsItOpen(ByRef strName As String) As Boolean
  On Error Resume Next
  Dim WB As Excel.Workbook
  Set WB = Excel.Workbooks(strName)
  IsItOpen = (Err.Number = 0)
  Set WB = Nothing
End Function
'-- 
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Chad" <Chad@discussions.microsoft.com> 
wrote in message 
I have an add-in that contains a time series forecasting routine in VBA that 
requires the use of the Solver add-in.  I want to use VBA to establish a 
reference to the Solver add-in for the forecasting add-in.  I have attempted 
the following with limited success.  Any suggestions would be much 
appreciated.
Thanks.

Private Sub Workbook_Open()    
    'Install the custom menu
    Call ThisWorkbook.CreateMenu
    
    'Check to make sure that Solver add-in is installed
    Call ThisWorkbook.CheckSolver
    
    'Check the version of Excel and warn the user if an older version.
    Call ThisWorkbook.CheckVersion
    
    'Establish a reference to solver if not already established.
    'Creates an error 400 if already installed
    On Error Resume Next
    With ThisWorkbook.VBProject.References
        .Remove .Item("SOLVER")
        .AddFromFile Application.LibraryPath & "\SOLVER\SOLVER.xla"
    End With
    
    With AddIns("Solver Add-In")
        .Installed = False
        .Installed = True
        ThisWorkbook.VBProject.References.AddFromFile _
            Application.LibraryPath & "\SOLVER\SOLVER.xla"
    End With
    On Error GoTo 0

End Sub

Sub CheckSolver()
Dim a
    'Subroutine checks for solver when Excel is opened
    Set a = AddIns("Solver Add-In")
    If a.Installed = False Then
        MsgBox "The Solver add-in is not installed!" & vbCr & _
               "Some functionality may be lost."
    End If
End Sub


0
jim.coneXXX (771)
7/20/2007 4:11:58 PM
Makes sense.  Thanks Jim.

Chad

"Jim Cone" wrote:

> Why not let the user take care of it...
> 
> Sub MakeThemDoIt()
>   Dim strName As String
>   strName = "SOLVER.XLA"
>   If IsItOpen(strName) = False Then
>      Application.Cursor = xlDefault
>      MsgBox "The SOLVER.XLA add-in is required." & vbCr & _
>             "Go to the Tools menu and select Add-Ins.   " & vbCr & _
>             "Checkmark...  Solver Add-In", vbInformation, "Chad Said So"
>   End If
> End Sub
> '--
> Function IsItOpen(ByRef strName As String) As Boolean
>   On Error Resume Next
>   Dim WB As Excel.Workbook
>   Set WB = Excel.Workbooks(strName)
>   IsItOpen = (Err.Number = 0)
>   Set WB = Nothing
> End Function
> '-- 
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
> (Excel Add-ins / Excel Programming)
> 
> 
> 
> 
> "Chad" <Chad@discussions.microsoft.com> 
> wrote in message 
> I have an add-in that contains a time series forecasting routine in VBA that 
> requires the use of the Solver add-in.  I want to use VBA to establish a 
> reference to the Solver add-in for the forecasting add-in.  I have attempted 
> the following with limited success.  Any suggestions would be much 
> appreciated.
> Thanks.
> 
> Private Sub Workbook_Open()    
>     'Install the custom menu
>     Call ThisWorkbook.CreateMenu
>     
>     'Check to make sure that Solver add-in is installed
>     Call ThisWorkbook.CheckSolver
>     
>     'Check the version of Excel and warn the user if an older version.
>     Call ThisWorkbook.CheckVersion
>     
>     'Establish a reference to solver if not already established.
>     'Creates an error 400 if already installed
>     On Error Resume Next
>     With ThisWorkbook.VBProject.References
>         .Remove .Item("SOLVER")
>         .AddFromFile Application.LibraryPath & "\SOLVER\SOLVER.xla"
>     End With
>     
>     With AddIns("Solver Add-In")
>         .Installed = False
>         .Installed = True
>         ThisWorkbook.VBProject.References.AddFromFile _
>             Application.LibraryPath & "\SOLVER\SOLVER.xla"
>     End With
>     On Error GoTo 0
> 
> End Sub
> 
> Sub CheckSolver()
> Dim a
>     'Subroutine checks for solver when Excel is opened
>     Set a = AddIns("Solver Add-In")
>     If a.Installed = False Then
>         MsgBox "The Solver add-in is not installed!" & vbCr & _
>                "Some functionality may be lost."
>     End If
> End Sub
> 
> 
> 
0
Chad9044 (93)
7/20/2007 5:00:03 PM
Reply:

Similar Artilces:

change a reference variable
Hi, After the following statements, can I change i to be a reference to k? Thanks. Tony int j, k; ... int& i = j; // make i to be a reference to j >After the following statements, can I change i to be a reference to k? Yes. int j, k; j = 0; k = 1; int& i = j; // i now refers to j (0) i = k; // i now refers to k (1) Dave -- MVP VC++ FAQ: http://www.mvps.org/vcfaq >>After the following statements, can I change i to be a reference to k? > >Yes. What am I talking about? A reference can't be changed - only initialised. Scrub my stupid last reply. Dave -- ...

INDIRECT reference or ?
Excel 2007 all MS updates I need to search ANY column of numbers, say Column AL, to determine if any of the values are below or above a value that I'm wanting to find. Here's an example: in Column AL the values are 1.20, 1.50, 1.75, 1.10, 1.95, 1.55. I'd like to find all cells that have a value of less than 1.50 (not less than or equal in this example). Hence, for Column AM, I'd normally use something like =IF(AL1<1.50,"True","False") for each of the cells of AM1, AM2, AM3, AM4, AM5, AM6 etc... I'd get a "True" for 1.20, 1.1...

Add Outlook Profile remotely??
Does anybody know of a tool, utility, etc that would allow me as an administrator to connect to a W2K, XP machine on the network and add e-mail profiles that show no matter what user logs on? I have multiple users who use multiple machines and want their regular e-mail profile set up instead of using Outlook Web Access. I would like to be able to remotely add their e-mail profile to the computer they want it on. I have 247 W2K, XP machines, using Exchange server on a AD network. Thanx! Use a login script to set the ImportPRF registry entry to the location of a ..prf file that you...

Add / Delete a record . . .
Greetings, I got in �sheet1� a database with theses headers �Name, Phone, Address� and below are the records rows 2-150. In �sheet2� I have the a data entr�e form (not VBA) its on the sheet; in cell A2 (Name), B2 (Phone), C2 (Address) and with three buttons at the side Find, Delete, Add. Cell A3:C3 is the "Results Area" I would like the "Find" button return a search from "Sheet1" for any of the 3 Headers and display the result in Cell A3:C3. The "Delete" button should the delete the result in "sheet2" cell A3:C3 and the entire row / recor...

solver #12
How can I use solver, or some other excel application to force the sum of a column to equal a specified number, by allowing the numbers being summed to either be changed to 0 or remain exactly the same? It is essentially to determine which 50 or so of 100+ deposits are included in an enormously large sum (1 large deposit total). the only way to do that is use VBA and think of an algoritm to solv your sum. If you want to calculate all possebility's don't be surprised to wai for several years before it would have the best solution if there i any. it's an exploding situation ...

solver...
I have four cells that I enter numbers into and the rest of the sheet has formulas setup. Is there a way to have excel try different numbers until it finds the max value in a certain cell? I believe I would use solver but I can't get it to work. I enter numbers in cells B5, B8, B11, and B14. I want cells K2, K5, K8, K11, K14, K17, and K20. Is there a way to do this? Thanks. ...

Add New Worksheet Using VBA
Hi Can anyone tell me how to add a new worksheet and rename it using VBA. Thanks for the help. Cheers!! -- Hani Muhtadi ------------------------------------------------------------------------ Hani Muhtadi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26794 View this thread: http://www.excelforum.com/showthread.php?threadid=477226 Worksheets.Add.Name = "mysheet" -- HTH RP (remove nothere from the email address if mailing direct) "Hani Muhtadi" <Hani.Muhtadi.1x40mb_1129658709.0692@excelforum-nospam.com> wrote in message news...

How do I reference a cell as a row number in a formula?
The formula I currently have is: =SQRT(SUM(F5280:F6240)/(N3-M3)) the row numbers 5280 and 6240 are values found in cells M3 and N3, respectively. However I need to copy this formula down a column, and I currently must go through and edit each formula to change those two values to the neighboring cells. i.e. that formula is in cell O3, and when copied to O4 the desired rows of column F to be referenced will be the numerical values in M4 and N4. =SQRT(SUM(F____:F____)/(N3-M3)) please fill in the blanks? =SQRT(SUM(INDIRECT("F"&M3&":F"&N3))/(N...

Generating a cell reference
I cannot find the solution to my question anwhere. If I enter the letter A in cell b3, enter the number 10 in cell d3, enter the letter A in cell b5, and enter the number 20 in cell d5, then how do I create cell references from these entrys that can be used in formulas, such as sum(a10:a20)? I hope this makes sense, I really would like to accomplish this. Thanks for any advice and assistance. Glen Try this: =SUM(INDIRECT(B3&D3&":"&B3&D5)) You did say "A" was in B3 *and* B5, so I used B3 twice, since you wanted: <<"sum(a10:a20)">>...

Refer to a control on a sub report
I cannot get the data from my subreport to my main report. I have looked that the posts and tried them and at http://allenbrowne.com/casu-18.html but I still get #NAME. So here is my information and maybe someone can tell me what I am missing. I have a main report with 5 subreports. The one I want data from is the 5th one (does that make a difference?) Properties of the Report (5): Name: rptSContribFactor Source Object: Report.rptSContribFactor Properties of the TextBox on rptSContribFactor Name: txtFRN Control Source: FixRemovedNada MainReport Info: ...

Ignore error msgs in formula references
I have an Average formula that refers to cells linked to another spreadsheet. The linked spreadhsheet gets updated each month, so that for months with no data yet, a #Div/0! error appears. I need to average a group of cells that display updated numbers as well as this error msg & I can't remove any of them from the calculation. I have found that you can use the IF function to display a string value in a cell w/an error message, but only if you can control the formula causing the Div/0! error...I can't, as that is the linked spreadsheet this was the topic in the help files I...

Solver slow
I have a large workbook that includes a macro that first sets calculation mode to manual and then starts the Solver. All data and calculations used by the Solver are located in a single worksheet. It is really slow. It is like if the Solver is doing an "Application.Calculate" at each iteration, calculating all worksheets, which would make sense. - Am I right thinking that the Solver use "Application.Calculate"? - If yes, is it possible to specify to the Solver to calculate only a given worksheet? Thanks. ...

Can't add additional users in deployment manager
Ok, all of a sudden I'm getting this error when trying to add another user for CRM: User 1 ( Guest CRM ) failed : Access is denied. (80070005) - SOAP Server Application Faulted We have a 10 user license, and are only using 8, so it's not licensing. I had no problems setting up the users who are already there. Anyone have any clue as to what this means? Thanks in advance! -Brian Allen I came across this same problem. I seem to recall that the users I was setting up were on a different network segment or were originally pre-Windows 2000 users. I'm not exactly sure what t...

refer to data on multiple worksheets using hlookup/look up data on many worksheets?
Hi I have data on many worksheets in the same workbook. Is it possible to look up data on multiple worksheets using Hlookup, which means can the second argument in the formula refer to multiple sources? If it is not possible, is there any other way I can do so? I would like to select and display data according to a fixed order, whereby the data is located in one of many worksheets in the same workbook. For eg, I have stock returns of many firms in many worksheets. Say I would like to display in a single worksheet the returns of Firm D, Firm Z, Firm R; whereby the data of these 3 fir...

protection + add/remove rows
hi every body if i have a table containing a data entering columns and one or mor result column i want to protect the table but in the same time i want to allow add delete rows and ceep the result columns cells protected is there anyway to do it ?? thanx an regard -- Message posted from http://www.ExcelForum.com In xl2002 and higher, you can protect the sheet and allow the user to insert/delete both rows and columns. If you're using an earlier version, maybe you can provide a macro(s) that inserts/deletes columns/rows. In your macros, you'd unprotect the worksheet, do the wor...

Missing or broken reference
I have an application written in Microsoft Access for windows 95, version 7.0. I recently bought a new PC that uses Access 2003. When I try to run the application I get an error message of: your db contains a missing or broken reference to the file MSJETSQL.TLB version 3.0. How can I fix this? Thanks, Jim Hi, My name is Amy Vargo. Thank you for using the Microsoft Access Newsgroups. This is an old library that cannot be referenced in Access 2003. Check your references. If you create a new module, then go to Tools, References, in the Visual Basic Editor window, look at the c...

Contacts template
Hey I'm using the Contacts templat ein one of my db:s. The AddFromOutlook command works well. Í would like to add some additional fields from Outlook - Department Office and Alias fields? Anyone who knows how to do this? I´ve studied some info on MSDN but couldn´t find anything like it.. Regards Anders ...

excel numbering add-in
When I open and invoice template in excel I get a pop up message advising me to install the numbering add-in for excel. I can not make this pop up go away, excel works fine without it. and I am unable to find it or down load it to make the program pop up go away. any suggestions would be appreciated I believe the add-in's name is TMPLTNUM.XLA and it should be installable if you rerun Setup. I think Excel 2000 was the last version to have it. -- Jim Rech Excel MVP "Fran" <Fran@discussions.microsoft.com> wrote in message news:41348456-DE78-49A7-8CF3-588DE8464F1C@m...

Add-ins #6
When I open a file by double-clicking it, my add-ins are not loading. But when I open Excel prog first, all add-ins load properly. Any thoughts? Thanks in advance. "Bo" wrote: > When I open a file by double-clicking it, my add-ins are not loading. But > when I open Excel prog first, all add-ins load properly. > > Any thoughts? > > Thanks in advance. Nevermind, a reboot resolved the issue. When all else fails, reboot! ...

Solver parameter constraint question
Here is my question: Let's say cell A1 is the cell to be changed and I want to restrict th value of it to 1, 2, 3, 4, 7, 10, 100, or 500. A1 must be chosen fro one of those values, how can I set up the constraints for thi situation? Thank you -- Morriga ----------------------------------------------------------------------- Morrigan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=709 View this thread: http://www.excelforum.com/showthread.php?threadid=37692 Hi: Try this. =IF(OR(A1={1,2,3,4,7,10,100,500}),"Correct","No good") Mat ...

SQL Server 2000, Exchange, COM-Add in for Outlook XP; Dynamically link ACCESS xp & SQL SERVER???
Can SQL Server 2000 manage the contacts in Exchange? Is there any connectivity between SQL Server and Outlook XP? Furthermore, can SQL Server 2000 sorta replace Exchange, i.e. can it communicate DIRECTLY with MS Outlook? I know MS Outlook XP can use mail merge functions utilizing a SQL Server connection, but can contacts be inputed in Outlook and then be reflected in SQL Server 2000? Perhaps using a COM-Addin??? I ask because my web hosting service has MS SQL SErver 2000 installed. But for an email server, they use Unix qmail. Ideally, I want to figure out if I should outsource the email se...

Solver
Solver is pretty good but it appears to have a few drawbacks. It is not a function, so you have to run it by hand every time. And it leaves all previously calculated values changed. I am cooking up a hydraulic flow sheet in which each line provides for entry of some values like channel depth and width, and produces values like velocity and energy. I need to show a column for the depth at which the energy is minimized. So I varied depth in Solver and got a believable value for the min. energy and the depth that produces it. Is there a function which will do that? There are a number of solver ...

add gridlines automatically to an openend xxx.html
Greetings, I have an html file that displays data in a table format. I can open this file with Excel and it will display all the data in the proper format. I would like to automatically apply grid lines to this data as I open the file. The html file name will vary (generated by Webview table generating tool). Is it possible to do this? I have a macro that will apply the grid lines to the spread sheet but I have to go to tools/macro/VBE and then add the code and run it. I have general users opening these html files with Excel and they just want to see the data with grid lines arou...

MS Office References
Hi, I am finding the task of finding complete reference information on the various office dialog boxes like looking for a needle in a haystack. I would like to see a reference for the various office programs that lists the available dialog boxes and all elements pertaining to those boxes that can be customized and the appropriate syntax for that element. For example wdDialogFileOpen, wdDialogFileSaveAs, msoFileDialogSaveAs, etc. Does such a reference exits? If so, where is it located? -- Thanks for your help. Eileen What exists is a VBA help topic called "Built-i...

Store object reference
Our solution required a very complex property object, which Visio own custom property cannot fullill. For this, we have created a class which will store data of that particular shape. I want to store class object of my data class somewhere in shape property. I was using one another tool which has property call "TagVariant" which we using for store a variant or object reference. Is there any way in shape class where we can store a class object reference. Thanks Shahzad Godil consider solution xml, there are examples in the v2003 sdk for storing at both the shape (cell) and ...