Solved

Make 2003 Excel VBA run in 2010: Problem with Application.Run

Posted on 2013-06-06
6
660 Views
Last Modified: 2014-03-06
First, I am totally a novice at VBA and have been trying to modify some code I've seen on the Internet. Below is the code that works in 2003 plus the code that I modified that should also work in 2010...obviously I am doing something wrong. Could someone help me?

Thanks, Jack
====================================================================


Code that works in Excel 2003
=======================
Sub CharterSolverM()
'
SolverOk SetCell:="Charter_m_BillRate", MaxMinVal:=1, ValueOf:="0", ByChange:="Charter_m_VaryRate"
SolverAdd CellRef:="Charter_m_BillRate", Relation:=1, FormulaText:="Charter_MaxBill"
SolverSolve True
End Sub

Open in new window

Code that fails in 2003 after my modifications
============================
Sub CharterSolverM()
Dim lResult
Dim sSolver As String
sSolver = f_sSolverName
' CharterSolver Macro
'
Application.Run sSolver & "!solverOK", SetCell:="Charter_m_BillRate", MaxMinVal:=1, ValueOf:="0", ByChange:="Charter_m_VaryRate"

Application.Run sSolver & "!solverAdd", CellRef:="Charter_m_BillRate", Relation:=1, FormulaText:="Charter_MaxBill"

Application.Run sSolver & "!solversolve", True 
End Sub

Open in new window

*** this code fails with a Compile Error: Named argument not found
0
Comment
Question by:jgbader
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39230861
sorry need more info.

Are SolverOk, SolverAdd , SolverSolve macros within the current workbook ?
Assume each macro access settings of the Solver addin.


it looks like the 2003 version assumes the macros are in current workbook, where the 2010 version has them in a different workbook ?

from Help on the Run Command.
Remarks
You cannot use named arguments with this method. Arguments must be passed by position.

Did you try using  
otherWBName!solverOK, SetCell:="Charter_m_BillRate", MaxMinVal:=1, ValueOf:="0", ByChange:="Charter_m_VaryRate"

Open in new window

probably needs the otherWBName to be hardcoded.
0
 
LVL 35

Expert Comment

by:Bembi
ID: 39230868
Can you post the excel sheet with your solver definition?
Just easier to follow up than to construct something, your macro fits to.
I can not really fllow up your situation only by the macro sequence...

Application.run usually starts a makro and you can add arguments, which has to be defined as parameters in the macro.

Without the underlying sheet it is diffucult to follow up, what you are referencing.
0
 

Author Comment

by:jgbader
ID: 39235168
Here is the spreadsheet...sorry for the delay.  I am trying to use Application Run so the code will run in both Excel 2003 and 2010.
Temp-Margin-Calculator.xls
0
 
LVL 35

Accepted Solution

by:
Bembi earned 250 total points
ID: 39238071
1.) I guess, there is no need to use Application.Run, additionally the syntax is not correct because it expects a makro (a sub), which offers a list or arguments.

2.) The problem what I can see ist just the fact, that you have to change the references, as solver.xla only exists in Excel 2003, where Excel 2010 uses solver.xlam.

The Objects are the same, so no need for changing the macro. Just coirrect the reference in Excel 2010 to the correct solver.xlam.

When I change the reference, your (old) macro code runs fine.

In VBA, goto Tools - References

Ususally the Reference is already there as "Solver", which points to the Office14 directory (for 2010), while the solver.xla for Excel 2003 points to Office11 (Excel 2003) directory.
0
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 250 total points
ID: 39240346
according to http://peltiertech.com/Excel/SolverVBA.html

1/ you need to remove the reference to Solver
2/ run solver much as you are but no need to reference the file.  It is actually running a built in function it seems.

for me then this runs but fails as Range("Target_Margin_Value") does not exist in your sample.(i hard coded to 5 and it starts solver on next line)
  Application.Run "solverOK", Range("Target_Margin_class3"), 3, Range("Target_Margin_Value").Value, Range("Target_Margin_Variable_class3")
  Application.Run "solversolve", True
  Application.Run "solverfinish", 1

Open in new window


my preference is to write the code like this so I know what i am assigning but it is just style. Error then throws on better line for debug.
  Dim setCell As Range, maxMinVal As Integer, valueOf As Variant, byChange As Range
  
  Set setCell = Range("Target_Margin_class3")
  maxMinVal = 3  'ie match target
  valueOf = Range("Target_Margin_Value").Value
  byChange = Range("Target_Margin_Variable_class3")
  Application.Run "solverOK", setCell, maxMinVal, valueOf, byChange

Open in new window

0

Featured Post

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question