Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
solver add in / excel solver / vba
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
solver add in / excel solver / vba
Investigate Solver SDK:
http://www.solver.com/developer.htm Perhaps it has what you need. -- Gary''s Student - gsnu2007 "jerome drean" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
solver add in / excel solver / vba
Thanks Gary
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
solver add in / excel solver / vba
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "jerome drean" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
solver add in / excel solver / vba
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) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
solver add in / excel solver / vba
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "jerome drean" wrote in message ... 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) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
solver add in / excel solver / vba
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
solver add in / excel solver / vba
,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. -- Dana DeLouis "jerome drean" wrote in message ... 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
solver add in / excel solver / vba
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to replace Excel solver with some free solver codes in Excel V | Excel Programming | |||
Using Excel Solver in VBA | Excel Programming | |||
Interesting Solver problem (Solver encounters an error) | Excel Discussion (Misc queries) | |||
USING SOLVER IN EXCEL | Excel Programming | |||
Resetting Solver Manually to Fix Solver Bug | Excel Programming |