Goal Seek functionality

[Guido De Bouver]

Hello all,

for a project, I need to program a multidimensionel version of the standard Goal Seek function.

The standard Excel "Goal Seek" functionality changes one cell to make another cell zero.

I would need to change multiple cells to make multiple cells zero.

I was wondering if I could do that using an UDF ??

Something along the lines of MyUDF(A1:A5, B1:B5).

In the above example, I would change cells A1:A5 to drive B1:B5 to zero.

This fomula would then be set to C1 and would find the values of cells A1:A5 where.B1:B5 was returned zero.

I see as a problem that my UDF located in C1 would need to change the source range A1:A5 to initiate the Excel calculations yp modify the cells B1:B5.

I can get around the math to resolve this problem, but I realize that Excel wont like to write to range A1:A5 when the formula is set in cell C1.

Any ideas if this could potentially be done in an UDF ?

kind regards

guido

[Mark Houldsworth]

Perhaps read in array, do all of the math in c#, and then return array answer?

Mark Houldsworth, PhD 

Chief Technologist

PASTORE HOULDSWORTH & CO.

HOUSTON TX AND RUIDOSO NM

Mobile: (832) 443-4010

_________________________________

RISK CONSULTING - NAVWARE MODEL LICENSING - CUSTOM SOFTWARE DEVELOPMENT

Linkedin Profile:https://www.linkedin.com/in/mark-houldsworth-phd-2b198512/

www.pastorecompany.com

 

postal address:

Astrodome Station

8205 Braesmain Drive

P.O. Box 20086

Houston, Texas 77225-0086

-------------------------------------------

CONFIDENTIALITY MESSAGE: This email message and(or) its attachments are intended for the addressee(s) indicated above.  This email contains information that is confidential and protected from disclosure.  Any review, dissemination, or use of this email or its contents by persons other than the addressee(s) is prohibited.  If you have received this email message in error, please notify the sender immediately and delete the email message.

 

RISK DISCLOSURE: This email message and(or) its attachment(s) may contain analysis, projections, and forward-looking statements.  In accepting this communication, the recipient of this email acknowledges the inherent risk and uncertainty associated with such aforementioned analysis and forward-looking statements; and the recipient understands that the actual business results and reported statistics may differ materially from any projections included in the email or its attachment(s).  Pastore Houldsworth & Co., LLC makes no warranty as to the accuracy or completeness of any forward-looking information contained herein.

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Guido De Bouver]

 The problem is that all formula's are inside the calling Excel spreadhseet, and all these formula's are variable and can be adjusted on the fly.

So I would need to change the source range in order to evaluate the formula's

 

[Vladimir Krilov]

Maybe this topic can help?

public static class Functions

{

        public static string dnaFunction()

        {

ExcelAsyncUtil.QueueAsMacro(() =>

{

Application app = (Application)ExcelDnaUtil.Application;

Worksheet ws = app.ActiveSheet;

ws.Range["D3"].Value = "Test";

});

return "Function result";

}

}

[Guido De Bouver]

Thanks all for your much appreciated help,

I think I found a solution to do this, however I would require my UDF to save the content of the current workbook.

The solution would be to :

--> solve the content of the calling workbook

--> load the saved workbook in a structure like Aspose.Cells. This allows me to efficiently evaluate all Excel's formula's

--> use the mathematics from a .NET mathematical library, such as Extreme.numerics to solve the functions

--> return back the solution to the calling workbook under the calling UDF, whilst keeping all other cells as they were.

So, this would require that I can save the calling workbook using ExcelDna.

Is this possible ? Could I save to memory instead of disk for performance reasons ?

kind regards,

guido

[Guido De Bouver]

TEXT SHOULD READ

The solution would be to 

--> SAVE the content of the calling workbook

--> load the saved workbook in a structure like Aspose.Cells. This allows me to efficiently evaluate all Excel's formula'

--> use the mathematics from a .NET mathematical library, such as Extreme.numerics to solve the function

--> return back the solution to the calling workbook under the calling UDF, whilst keeping all other cells as they were.

So, this would require that I can save the calling workbook using ExcelDna

[Govert van Drimmelen]

Hi Guido,

 

Are you trying to do something that the built-in Solver tool cannot do?

Of course it doesn’t work as a UDF, but it can optimise using multiple input values, and has a very robust algorithm.

 

-Govert

--

[Guido De Bouver]

Yes, I am trying to do something the Excel Solver cant do.

I realize the embedded can do terrible things, but it wont do what is needed here.

basically, I need to leave the whole content of the spreadshete untouched and return some optimal values from a UDF.

I think the above described mechanism from my previous post will work - but I would need to find out how to save the content of the calling worksheet - hopefully to a stream, if not possible to file

thanks

[Govert van Drimmelen]

What if you saved a copy of the book into a temp file, opened it in another Excel process, ran solver, and copied the resulting values back?

 

-Govert

--

[Guido De Bouver]

thanks Govert, 

Basically that is what I want to automate icompletely in an UDF.

So the first step is to save the content of the calling worksheet.

I realize I can get to the name of the file, however that does not inlcude all modifs that have been applied since the last save.

Is there a way to save the content of the current worksheet to a stream inside ExcelDna ? 

If a stream wont work - I will settle for a file :-)

I realize this will be slow, however, correct update mechanism will ensure it wont get called only when absolutely needed.

kind regards from Belgium

guido

 

[Govert van Drimmelen]

The only options I know of for dealing with the file and other contents of the Workbook are those in COM object model, exactly the same as you’d have in VBA.

I don’t think there’s anything relevant in the C API that you can’t do with the COM object model.

 

So you either have to prompt the user and force a Save (or Save As) to read directly from the file, or read and process everything in the Workbook by reading all the formulas from the cells.

 

-Govert

 

 

From: exce...@googlegroups.com <exce...@googlegroups.com> On Behalf Of Guido De Bouver
Sent: 25 June 2018 12:59
To: Excel-DNA <exce...@googlegroups.com>
Subject: Re: [ExcelDna] Re: Goal Seek functionality

 

thanks Govert, 

--

[Guido De Bouver]

Ok, thanks for your help.

I will assume the file was saved and will solve the functions as per the method ( reading in Aspose.Cells and resolving using Extreme.Mathematics ).

I will update this forum on the resulting source code.

guido

[Terry Aney]

@Govert If I save to a temp file what would be the flow?  Something like this?  (psuedo code so could have names a bit off)

var isSaved = activeWorkbook.IsSaved;
var currentName = activeWorkbook.FullPath; (or whatever full name property is available)

var tempFile = GetTempFileName();

activeWorkbook.SaveAs( tempFile );

// .. process tempFile ...

activeWorkbook.FullPath = currentName;

activeWorkbook.IsSaved = isSaved;

Going to test out in a bit, but thought I'd ask for documentation.

[Naju Mancheril]

I think there are a few difficulties here:

1. One difficulty is detecting that B1:B5 have been recalculated with the latest A1:A5.
1. Even if you know that there is no async/RTD function in B1:B5, is there some way to synchronously trigger a recalc and be sure that the "fresh" B1:B5 values have been updated?
2. A second difficulty is navigating the search space of A1:A5 values.
1. With one dimension, there are only two possible directions to move (left or right)
2. With five dimensions, which direction are you going to go? I would think that you need some convexity assumption about the function structure to know whether you are headed in the correct direction.