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

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
Who is Participating?
BembiConnect With a Mentor CEOCommented:
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.
Robberbaron (robr)Commented:
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.
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.
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... 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.
jgbaderAuthor Commented:
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.
Robberbaron (robr)Connect With a Mentor Commented:
according to

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.