Excel Solver Error
Excel Solver Error
Excel Solver Error
Excel Solver Error
Excel Solver Error
Excel Solver Error Excel Solver Error Excel Solver Error Excel Solver Error Excel Solver Error Excel Solver Error Excel Solver Error Excel Solver Error
Excel Solver Error Excel Solver Error
Excel Solver Error
Go Back  Xtreme Visual Basic Talk > > > Excel Solver Error


Reply
 
Thread Tools Display Modes
  #1  
Old 02-24-2005, 11:33 PM
PH1SH55 PH1SH55 is offline
Newcomer
 
Join Date: Feb 2005
Posts: 2
Default


Edit by moderator: Split from old thread. If you want to refer to an old thread, please post a link instead.

Excel Solver in VB6 Error Please Help

Hey, sorry for bumping an old post, but I thought it had a more relevant base to ask my question.

So I already have a defined macro, it runs correctly within the spreadsheet and already references the solver.xla
Code:
Sub SOLVER()
'
' Solver Macro
' Macro recorded 2/21/2005 by erickson
'

'
    SolverReset
    SolverOk SetCell:="$H$23", MaxMinVal:=1, ValueOf:="0", ByChange:= _
        "$D$34:$V$42,$D$107"
    SolverAdd CellRef:="$C$107", Relation:=1, FormulaText:="$E$107"
    SolverAdd CellRef:="$D$107", Relation:=5, FormulaText:="binary"
    SolverAdd CellRef:="$E$108", Relation:=3, FormulaText:="0"
    SolverAdd CellRef:="$D$34:$V$42", Relation:=5, FormulaText:="binary"
    SolverAdd CellRef:="$D$38:$D$39", Relation:=2, FormulaText:="0"
    SolverAdd CellRef:="$F$38:$H$39", Relation:=2, FormulaText:="0"
    SolverAdd CellRef:="$D45:$V45", Relation:=2, FormulaText:="1"
    SolverAdd CellRef:="$K$38:$N$39", Relation:=2, FormulaText:="0"
    SolverAdd CellRef:="$S$34:$U$39", Relation:=2, FormulaText:="0"
    SolverAdd CellRef:="$V$38:$V$39", Relation:=2, FormulaText:="0"
    SolverAdd CellRef:="$W$31", Relation:=1, FormulaText:="$Y$31"
    SolverAdd CellRef:="$W$34:$W$42", Relation:=1, FormulaText:="$Y$31"
    SolverAdd CellRef:="$W$45", Relation:=1, FormulaText:="661"
    SolverOk SetCell:="$H$23", MaxMinVal:=1, ValueOf:="0", ByChange:= _
        "$D$34:$V$42,$D$107"
    SolverOptions MaxTime:=100, Iterations:=1000, Precision:=0.000001, AssumeLinear _
        :=True, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
        IntTolerance:=25, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=False
    SolverOk SetCell:="$H$23", MaxMinVal:=1, ValueOf:="0", ByChange:= _
        "$D$34:$V$42,$D$107"
    SolverSolve userFinish:=True
End Sub
Now in takling to frontline systems, just as the guy in the link thread had, they mentioned calling the solver function directly within the workbook open event. Following the instructions in the previous thread I called the macro using the following code.
Code:
Private Sub Workbook_Open()
xlApp.Run ("usermodel.xls!Module1.SOLVER")
End Sub
Unfortunately the above generates a "runtime error '424' object required"

Does it have something to do with the step outlined here:
Quote:
(5) Within the "Object" Drop-Down box, which will currently read "(General)", click on it and choose "Workbook". It will now give you the following Stub:
I was unable to find this so I simply used the example code (on linked thread) within the "thisworkbook" module.

However, when I inserted
Code:
Private Sub Workbook_Open()
    MsgBox ThisWorkbook.Name & ".Workbook_Open() Event Called!"
End Sub
in place of the app.run function it correctly displays the message so I'm guessing the workbook open event is correct... Any ideas would be greatly appreciated, and I apologize but I am very new to this so simple examples of any suggestions you have are greatly appreciated.

Last edited by PH1SH55; 02-25-2005 at 08:07 PM. Reason: split from archived thread
Reply With Quote
  #2  
Old 02-25-2005, 04:19 PM
herilane's Avatar
herilaneExcel Solver Error herilane is offline
Unashamed geek

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

Welcome to the forum.

It looks like you're doing this in VBA and not VB, right? In which case the thread you replied to is not really relevant.

Can you explain what the problem is with the code you have, and what the Workbook_Open procedure has to do with this at all?
Reply With Quote
  #3  
Old 02-25-2005, 08:04 PM
PH1SH55 PH1SH55 is offline
Newcomer
 
Join Date: Feb 2005
Posts: 2
Default

The main application is a VB.NET interface. Since the solver is currently not referenceable in VB.net, frontline systems suggested calling the macro in the workbook open event in VBA, as the thread linked above also described. The idea is when the VB.net application calls the spreadsheet it will run the solver macro upon opening it, thus bypassing the need for the VB.net application to directly call the solver function. The problem I've run into in attempting to do this is an "object required" runtime error as described above.

Predictably, the problem in the code points directly to the line
xlApp.Run ("usermodel.xls!Module1.SOLVER")

in
Code:
Private Sub Workbook_Open()
xlApp.Run ("usermodel.xls!Module1.SOLVER")
End Sub
I hope that clarifies what I am trying to do a bit more? Thanks for your response
Reply With Quote
  #4  
Old 02-25-2005, 08:59 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

Ok... you were really close... Your mistake is confusing VBA with VB.Net code.

When you write 'xlApp' within your VBA Code (the code within the Workbook) it has no clue that you meant the "xlApp" variable in your VB.Net Assembly. It can only look within it's own Workbook Project and look for 'xlApp'. It finds none.

The easy fix would be this:
Code:
Private Sub Workbook_Open() Call Application.Run("usermodel.xls!Module1.SOLVER") End Sub
This works because 'Application', much like 'ThisWorkbook' always refers to the Application for the Workbook in question.

However, since you are a VB.Net programmer, I think you might want to be a little more disciplined about your naming conventions and avoid these pre-defined Excel VBA Globals (which can get one into trouble in certain situations).

What I do is make a new VBA Module and call it 'pvtProject'. What you name it is not important, but I like to put all my Variables that are Private to this Project within it. I then declare the module 'Option Private Module'. Lastly, I create my xlApp variable:
Code:
' Module Name: "pvtProject" Option Explicit Option Private Module ' <-- Equivalent to 'Friend' Scope in .Net. Public Function xlApp() As Excel.Application Set xlApp = ThisWorkbook.Application End Function
Once you do this, now you can refer to your 'xlApp' variable! The 'xlApp' will be forever defined as 'ThisWorkbook.Application'.

Now a VBA'er would likely find this silly, and would prefer to use 'Application' directly, but I like the discpline of naming my Application references as "xlApp". But this is 100% totally up to you.

Does this make sense?

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

Last edited by Mike Rosenblum; 02-25-2005 at 10:19 PM.
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
Excel Solver Error
Excel Solver Error
Excel Solver Error Excel Solver Error
Excel Solver Error
Excel Solver Error
Excel Solver Error Excel Solver Error Excel Solver Error Excel Solver Error Excel Solver Error Excel Solver Error Excel Solver Error
Excel Solver Error
Excel Solver Error
 
Excel Solver Error
Excel Solver Error
 
-->