Solved

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

Posted on 2013-06-06
6
639 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
  • 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now