solver add in / excel solver / vba

[jerome drean]

as some may know you can use the excel solver from vba - i wonder if it is
possible to use the solver from vba without interacting with the worksheet
object / range - ie i dont see why we cannot access the functionalities of
the solver by giving him an optimisation problem using a function defined in
vba
this comment could be applicable to many other cases such as graphs for
example

i hope i make sense to some of you

 

[Gary''s Student]

Investigate Solver SDK:

http://www.solver.com/developer.htm

Perhaps it has what you need.

 

[jerome drean]

Thanks Gary

 

[Jon Peltier]

Describe what you're doing. I've found that I can generally set up anything
in a hidden worksheet, and avoid the fancier standalone Solvers.

- Jon

 

[jerome drean]

Hi Jon

Lets assume that you want to optimise a function f (x1,x2,x3...)

option 1
You can use a worksheet and in cell a1 write =f(b1,b2,b3)
where b1 will contain x1
b2 will contain x2
b3 will contain x3
then use the solver by "Tools->solver ..."
this is plain excel

option 2
do the same in vba by creating a worksheet / workbook and dumping the
relevant information in the relevant cell and call solver ok ... like
Sub solvermatchvol()
SolverOk SetCell:="$a$1", MaxMinVal:=2,
ValueOf:="0",ByChange:="$b$1:$b$3"
SolverSolve UserFinish:=True
end sub
this is plain vba interacting with excel

option 3
try to use the functionalities of the solver directly in vba without any
interaction with any cells (this would be similar to have an external dll and
link to it from vba)
since excel is so rich of functionalities (optimisation, graphics) i always
wondered why excel does not give the user the option to call the functions
from vba not using any excel object such as worksheet / range etc...

It was a general comment to see if other users are trying to use vba without
excel standard objects the reason being that excel is great for manipulating
small amount of data but slow with a lot of calculations and lots of data
therefore it is more efficient to control the calculation from vba totally.
This having been said it is alwasy possible to hide worksheet and dump in
them necessary results to perform the task but it is not very nice solution.

Alternatively it is possible to write code in C but it is more complicated
and slow to program since it is difficult to access all classes

Another example can be:
the user gives a few parameters to a sub and wants to see a graph that is
based on the genration of large amount of data from the few parameters --> it
would be nice to call the graphical tool of excel by passing the data from
vba in a 2*2 matrix
without dumping the data in excel (even more when the data is greater than
65k rows)

 

[Jon Peltier]

Some Excel worksheet functions work fine in VBA on VBA data. For example,
the minimum of an array can be found by

ArrayMin = WorksheetFunction.Min(myArray)

Big deal, that's not really what you mean.

The thing is, Excel contains a pretty flexible data structure called a
worksheet. You can, of course, plot arrays of data in an Excel chart, but
you are limited by the chart interface, which can only handle 1024
characters for the series formula. Even in the absence of this limit, a
series can contain no more than 32k points, so your 65k rows are twice too
many. Put the data into the sheet, and you can access all 32k points.

Some folks get pleasure in not sullying their worksheets with all the data
their workbook uses (i.e., your comment about "not a nice solution"). But it
usually takes more time to develop such a scheme, and the scheme is harder
to comprehend, and harder to fix when you break it. A worksheet is visible,
tangible, and reliable, and the data doesn't enter the bit bucket in the sky
when you quit. Such a robust and quick to develop solution is at least as
"nice" as an esoteric approach that doesn't leave scraps of data in the
workbook.

- Jon

 

[jerome drean]

Jon
Dont get me wrong i think excel is probably the best program ever written
and that is why users (like me) are asking more and more from it
of course it is driven by lazyness,a full solution would be to write a
window application that uses exel when necessary and not using excel as a
starting point for a full application.
Thanks for sharing your view thought.

 

[Dana DeLouis]

,a full solution would be to write a

window application that uses excel when necessary..

You may find the following of interest from LINDO.
In the past, I used Excel as my application, and ran Lindo from Excel using
vba.
Kind of a compromise between the two as it saved a lot of typing time when
the data on my spreadsheet changed..

http://lindo.com/products/api/dllm.html

Another similar situation to your request is Excel's Fourier Transform
function. It requires the data to be from a worksheet, and not from a vba
array.
Some of the problems are that vba has to read/write from the worksheet
Also, complex data on the sheet has to be in "String" format, which slows
the program even more. In addition, Microsoft kept the Debug.Print
statements in the ATP (1 or 2 versions prior to Excel 2007) and this REALLY
slowed the program down !!!
I have my own vba Fourier Program, and it's much more efficient. I don't
know why Excel doesn't have this by now, as it's easy to do. This way, both
the Real & Complex data can be kept in separate arrays, and called directly
in vba. No conversion to strings, and no read/write to a worksheet.

 

[jerome drean]

Dana I found your comment very useful thanks
It is true that a big drawback of excel vba in a more structured environment
is it slowness that forces many financial programmers to get involved in c /
c++ excel add-in for no extra reasons that it is faster but it takes more
time to develop- i wonder how much of the speed consideration has been put in
c# - being able to call excel function from vba having all the data in vba
would be very useful i agree (and faster as you made the point)