Solved

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

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

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.
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

920 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

15 Experts available now in Live!

Get 1:1 Help Now