Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-06-06
6
Medium Priority
?
696 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 750 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 750 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When asking a question in a forum or creating documentation, screenshots are vital tools that can convey a lot more information and save you and your reader a lot of time
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

610 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