Back to Solver !!! Need Help
Back to Solver !!! Need Help
Back to Solver !!! Need Help
Back to Solver !!! Need Help
Back to Solver !!! Need Help
Back to Solver !!! Need Help Back to Solver !!! Need Help Back to Solver !!! Need Help Back to Solver !!! Need Help Back to Solver !!! Need Help Back to Solver !!! Need Help Back to Solver !!! Need Help Back to Solver !!! Need Help
Back to Solver !!! Need Help Back to Solver !!! Need Help
Back to Solver !!! Need Help
Go Back  Xtreme Visual Basic Talk > > > Back to Solver !!! Need Help


Reply
 
Thread Tools Display Modes
  #1  
Old 11-15-2004, 03:37 PM
ssk ssk is offline
Freshman
 
Join Date: Sep 2004
Posts: 44
Default Back to Solver !!! Need Help


Hi Mike,

Following up on your earlier advice about using macros, I created a macro in excel. I saved it in a workbook and during the exceution of the program I open the specific workbook. Problem is that macro gives me "out of memory error".

One more thing : for a start I am calling the macro exactly as you suggested. I am not passing any parameters to it.

I have also tried another approach of creating the same macro but storing it in Personal workbook. This is because in Excel VBA 2003, they have mentioned that such macros can be called from any open workbook.

Presently both approaches dont work. Please help.

Thanks,
SSK
Reply With Quote
  #2  
Old 11-15-2004, 04:06 PM
herilane's Avatar
herilaneBack to Solver !!! Need Help herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

Could you post the code in the macro?

If you call Solver manually, does it manage to solve the same problem without any errors?
Reply With Quote
  #3  
Old 11-15-2004, 04:28 PM
ssk ssk is offline
Freshman
 
Join Date: Sep 2004
Posts: 44
Default

Hi Helen,

Yes, if I call the solver manually it gives me the right solution. In fact, The solver code itself is recorded as a macro. If I run the Macro from the workbook, it gives me the right answer.

SSK
Reply With Quote
  #4  
Old 11-16-2004, 08:10 AM
herilane's Avatar
herilaneBack to Solver !!! Need Help herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

Again, could you post the actual code please?
Reply With Quote
  #5  
Old 11-17-2004, 11:47 AM
ssk ssk is offline
Freshman
 
Join Date: Sep 2004
Posts: 44
Default

Hi,

Here is the code :

Below is the macro code. I recorded the macro in a excel file called solver. This one is just a simple approach to get things started.

Code:
Sub Macro1() ' ' Macro1 Macro SolverOk SetCell:="$DE$3", MaxMinVal:=2, ValueOf:="0", ByChange:="$D$3:$D$5" SolverSolve (True) SolverReset End Sub

This is the code in the program I am using :

Code:
solverwkb = myApp.Workbooks.Open("c:\jobs\solver.xls") myApp.Run("solver.xls!Macro1") ' I also tried it with just myapp.Run("Macro1")

So in the above case I have already made a workbook called solver.xls which has the recorded macro in it. I open the wokbook from the program and try to run that macro.

Another approach I have done is to store the macro in the personal workbook, as I thought I can call it from any workbook in that case. Even that does not work.

I first need to get the solver running and then to think how to pass the parameters like the target cell and stuff.

Thanks for your help.

SSK
Reply With Quote
  #6  
Old 11-17-2004, 01:58 PM
herilane's Avatar
herilaneBack to Solver !!! Need Help herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

First we need to figure out whether it's calling the macro that causes trouble (unlikely) or running solver (more likely).
Put a simple MsgBox as the first line of Macro1. If the MsgBox comes up, you know that the macro gets called correctly at least, and we can look at the solver part.

Last edited by herilane; 11-17-2004 at 03:31 PM.
Reply With Quote
  #7  
Old 11-17-2004, 03:19 PM
ssk ssk is offline
Freshman
 
Join Date: Sep 2004
Posts: 44
Default

I put in a MsgBox inside the Macro and it was called twice. So it went like this :

MsgBox
Error
Msgbox
Error

So it means that the Macro is called, and I think it is called twice because once due to opening of the workbook and second when I call it in the next line after opening.

SSK
Reply With Quote
  #8  
Old 11-17-2004, 05:14 PM
herilane's Avatar
herilaneBack to Solver !!! Need Help herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

When you call the macro, is the right workbook active, and the right worksheet?
Reply With Quote
  #9  
Old 11-17-2004, 05:56 PM
ssk ssk is offline
Freshman
 
Join Date: Sep 2004
Posts: 44
Default

The answer is Yes : In terms of the trial application, where I call Solver just for basic understanding.

The answer is No : In terms of the application, I am having one workbook open with the related data which I need to run Solver on, and second workbook open with the Macro in it.

SSK
Reply With Quote
  #10  
Old 11-17-2004, 06:08 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

SSK,

I think you need to provide a sample Workbook so that others can kick it around... If there is no sensitive information on the Workbook, then I would just .Zip it up and attach it to this thread.

If you prefer, you could make a new small Workbook example, get that working (at least working when called from Excel VBA) then .Zip that and attach it so that we can give it a try.

Unfortunately, I, myself, will be pretty much unavailible for about 10 days or so, so I might not be able to try it for quite a while, but I know that Helen and others would gladly give it a shot.
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #11  
Old 11-17-2004, 06:17 PM
ssk ssk is offline
Freshman
 
Join Date: Sep 2004
Posts: 44
Default

On second thoughts, I dont really know if the worksheet having the cells is active or not. I have been trying some other things around which made me realize it.

SSK
Reply With Quote
  #12  
Old 11-17-2004, 06:21 PM
ssk ssk is offline
Freshman
 
Join Date: Sep 2004
Posts: 44
Default

Hey Mike,

Been a long time......Well there is no Sensitive information on the worksheets. What I would really like to attach is the Workbook I create, through which I want to access the Macro (Solver). I dont know if I should be also attaching some code that creates the workbook, so that it becomes easier for people to kick it around ???

SSK
Reply With Quote
  #13  
Old 11-18-2004, 07:25 AM
italkid's Avatar
italkid italkid is offline
Down...

Retired Moderator
* Expert *
 
Join Date: Dec 2002
Location: Belgium.
Posts: 6,731
Default

Attach the workbook with the data and the macro you want to run on that data in...
Reply With Quote
  #14  
Old 11-18-2004, 09:37 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Quote:
Originally Posted by ssk
Hey Mike,
Been a long time......
Hi SSK, sorry, yeah, I don't have nearly as much time as I used to. And I'm away the next 10 days...

But I agree with Kid 100% here: just the Workbook with the Macro attached that you are trying to run. The "creation code" is not necessary.
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #15  
Old 11-18-2004, 11:22 AM
ssk ssk is offline
Freshman
 
Join Date: Sep 2004
Posts: 44
Default

Hi Guys,

I am attaching the workbook with this post. A bit about the application. Please havea look at the sheet named SOLVER-DATA. I have it color coded for better understanding of the application.

>> The Yellow cell O2 is the target cell for Solver. This cell is to be minimised. This cell has an equation which sums the square of the differences between thoeretical and actual values.

>> The column (O10 - O30) with Light Yellow color is the square of differences between each theoretical and actual value.

>> The column (N10 - N30) wiht Aqua color contains the theoretical values and is affected by the change of values of changing cells.

>> The Pale Blue (D3 - D5) are the changing cells for Solver.

>> The Tan color is the time used to calculate the Theoretical values.

Such is the application. The time, changing cells are used to calculate the theoretical values. The the Least Square is the sum of squares between theoretical and actual values.

Solver will be used to minimise the Least Square (Yellow) value. It will do so by changing the Changing Cells (Pale Blue). I have to pass each column (Eg: D35, E3:E5, F3:F5,....) of Changing cells to Solver along with the Target cell which always remains O2.

Any Ideas about how it can be done ??? If I store a Macro in a separate workbook, then I have to call the Macro from the SOLVER-DATA sheet and pass it parameters and store the return values of the changing cells.

Please reply in case I have not been clear.

SSK
Reply With Quote
  #16  
Old 11-18-2004, 11:25 AM
ssk ssk is offline
Freshman
 
Join Date: Sep 2004
Posts: 44
Default

Here is the Attachment with the Excel Workbook.

SSK
Attached Files
File Type: zip Trial_Sheet.zip (258.1 KB, 4 views)
Reply With Quote
  #17  
Old 11-18-2004, 05:07 PM
italkid's Avatar
italkid italkid is offline
Down...

Retired Moderator
* Expert *
 
Join Date: Dec 2002
Location: Belgium.
Posts: 6,731
Default

SSK,
Could you please upload the code you want to run on that data too...
Reply With Quote
  #18  
Old 11-19-2004, 10:27 AM
ssk ssk is offline
Freshman
 
Join Date: Sep 2004
Posts: 44
Default

Hi,

Here is the code :

Below is the macro code. This is the macro I recorded and modified a bit.
below that is the code which I am trying to use to call solver.

Code:
Sub Macro1() ' ' Macro1 Macro SolverOk SetCell:="$DE$3", MaxMinVal:=2, ValueOf:="0", ByChange:="$D$3:$D$5" SolverSolve (True) SolverReset End Sub



This is the code in the program I am using :


Code:
solverwkb = myApp.Workbooks.Open("c:\jobs\solver.xls") myApp.Run("solver.xls!Macro1") ' I also tried it with just myapp.Run("Macro1")


Thanks for your help.

SSK
Reply With Quote
  #19  
Old 11-20-2004, 12:11 PM
ssk ssk is offline
Freshman
 
Join Date: Sep 2004
Posts: 44
Default

Any Ideas as to How I can proceed further from here ?? I hope the application has been understood.

SSK
Reply With Quote
  #20  
Old 11-21-2004, 10:39 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

SSK,

There are a number of things going on here...

(1) Your Macro1 code, as written, is dependent on which Worsheet is active, and so I suggest that you add a line to it so that it reads as follows:
Code:
Sub Main() ThisWorkbook.Worksheets("SOLVER-DATA").Activate ' <-- *** Added *** SolverOk SetCell:="$DE$3", MaxMinVal:=2, ValueOf:="0", ByChange:="$D$3:$D$5" SolverSolve (True) SolverReset End Sub

(2) You need to add this code to your workbook. It can't run if it's not acutally there, so I'm not 100% sure what you were testing. The Workbook also needs a reference to the Solver.xla to be added.


(3) Your SetCell is specified as SetCell:="$DE$3", which is WAY off in outer Space, well off to the far-far-right on your spreadsheet? Did you mean to change "D3" here and not "DE3"?

But your ByChange cells are listed as "D$3:$D$5" so I guess "D3" can't actually be the intended target (can't be a Change Cell and a Target Cell at the same time).

Lastly, the Cell D5, although listed as a Change Cell, is not connected to the Spreadsheet in any way that I can tell. No other cells seem to pull from the D5 Cell. (Use the Auditing Toolbar to confirm this).


So there are a few things to straighten out here, but I think these errors are relatively minor typos and/or omissions. If you fix it up and re-post, we'll take another crack at it. (Well, I'll be back next week, but I'm sure others will take a shot...)

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Back to Solver !!! Need Help
Back to Solver !!! Need Help
Back to Solver !!! Need Help Back to Solver !!! Need Help
Back to Solver !!! Need Help
Back to Solver !!! Need Help
Back to Solver !!! Need Help Back to Solver !!! Need Help Back to Solver !!! Need Help Back to Solver !!! Need Help Back to Solver !!! Need Help Back to Solver !!! Need Help Back to Solver !!! Need Help
Back to Solver !!! Need Help
Back to Solver !!! Need Help
 
Back to Solver !!! Need Help
Back to Solver !!! Need Help
 
-->