Solver fails when called through OLE object

P

perco754

Board Regular
Joined
Apr 17, 2009
Messages
91
Dear Excel Experts,

I am using Excel (2003) Solver in my application for some minor optimization problem. The application has been used within our business without problems since several years back. The Excel file contains macros that is triggered by changing a certain cell value. The macro SolveTheProblem() that calls solver and an example of values used in the sheet is defined and showed below (I wanted to attach a screen dump showing the complete Excel sheet but don't know how-to).

Now my Windows application (c++ RAD Studio) will open the Excel template and fill some cells with values to be included in the optimization. However, the return from Solver contains an error message: "an unexpected internal error occured, or available memory was exhausted" i.e. no solution.
Now, if I instead copy the cell values into the very same Excel template and run the SAME macro manually it returns a correct solution. :eeek:

Since the last time I worked with development of this particular application I've had Visual Studio 2010 installed (used VS 2005 before) BUT here I'm talking about RAD Studio and NOT VS at all. But maybe this version will affect the OLE objects used by RAD studio, or?

I'm very lost and totally confused about this, any help appreciated!

BR,
Per


Public Sub SolveTheProblem()

Dim row As Integer, rowTarget As Integer, columnTarget As Integer
Dim cellTarget As String
Dim vars As String, leftSide As String, rightSide As String
Dim targetValue As String
Dim resultInt As Integer

solutionFound = False
targetValue = CStr(Cells(1, 1).Value)
targetValue = Replace(targetValue, ",", ".")
rowTarget = Cells(2, 1).Value
columnTarget = Cells(3, 1).Value

If columnTarget = 2 Then
cellTarget = "$B$" + CStr(rowTarget)
Else
cellTarget = "$C$" + CStr(rowTarget)
End If

vars = "$B$" + CStr(rowTarget) + ","
For row = 1 To 17
If Cells(row, 4).Value > 0 Then
vars = vars + "$B$" + CStr(row) + ","
End If
Next row

vars = Left(vars, Len(vars) - 1)
'SolverReset
Application.Run "Solver.xla!SolverReset"

Application.Run "Solver.xla!SolverOptions", 100, 400, 0.00001, False, False, _
1, 2, 1, 5, False, 0.0001, False

Application.Run "Solver.xla!SolverOk", cellTarget, 3, targetValue, vars

Application.Run "Solver.xla!SolverAdd", "$b$1:$b$16", 3, "0"
Application.Run "Solver.xla!SolverAdd", "$b$22", 1, "0.999"

For row = 1 To 17
If Not row = rowTarget Then
If Cells(row, 4).Value > 0 Then
leftSide = "$C$" + CStr(row)
rightSide = "$D$" + CStr(row)
Application.Run "Solver.xla!SolverAdd", leftSide, 2, rightSide
End If
End If
Next row

resultInt = Application.Run("Solver.xla!SolverSolve", True)

Cells(31, 2) = resultInt

If resultInt < 2 Then
Application.Run "Solver.xla!SolverFinish", 1
Else
Application.Run "Solver.xla!SolverFinish", 2
End If

End Sub


 <table width="288" border="0" cellpadding="0" cellspacing="0"><col style="width:54pt" span="4" width="72"> <tbody><tr style="height:14.25pt" height="19"> <td style="height:14.25pt;width:54pt" height="19" width="72" align="right">A
0.05</td> <td style="width:54pt" width="72" align="right">B
0</td> <td style="width:54pt" width="72" align="right">C
0</td> <td style="width:54pt" width="72" align="right">D
0</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19" align="right">12</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19" align="right">2</td> <td align="right">0.007</td> <td align="right">741.7482</td> <td align="right">0</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td align="right">0.005</td> <td align="right">529.8202</td> <td align="right">0</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td align="right">0.02001</td> <td align="right">2120.34</td> <td align="right">0</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td align="right">0.05</td> <td align="right">5298.202</td> <td align="right">0</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td align="right">0.107</td> <td align="right">9095</td> <td align="right">0</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td align="right">0.03</td> <td align="right">3178.921</td> <td align="right">0</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td align="right">1.246636</td> <td>
</td> <td>
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td align="right">105964</td> <td>
</td> <td>
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td align="right">85000</td> <td>
</td> <td>
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td align="right">0.11201</td> <td>
</td> <td>
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td align="right">0</td> <td>
</td> <td>
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19" align="right">1</td> <td align="right">0</td> <td>
</td> <td>
</td> </tr> <tr style="height:14.25pt" height="19"> <td style="height:14.25pt" height="19">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>
 
Last edited:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Sorry, I forgot to mention that when running the macro you should provide the following data input

A1 contains target value, for example set it to 0.01
A2 contains target row number, set it to 10
A3 contains target column, set it to 2

Doing this we will have the target cell address = B10 and its target value = 0.01.

Run the macro and watch the result (should be B10.Value = 0.01)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
DataBlake
  • Question Question
Dictionary Overlap Problem
Replies
1
Views
842
DataBlake
DataBlake
A
  • Question Question
Worksheet Display Not Updating Through Loop Process
Replies
3
Views
778
Ark68
A
K
  • Question Question
How to reduce betting blokes that can guarantee max 1 incorrect
2
Replies
14
Views
2K
Kishan
K

Forum statistics

Threads
1,224,587
Messages
6,179,738
Members
452,940
Latest member
Lawrenceiow

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top