PDA

View Full Version : Possible to Run Goal Seek on Array elements Within VBA?



Teatro
02-20-2017, 11:48 AM
Is it Possible to Run Goal Seek on array elements within VBA, instead of on an Excel sheet range?

I have a Sub that makes an array which takes initial values from an excel input sheet, does many calculations, and prints values back into a report region on an excel sheet; the output is roughly 200 rows x 28 columns of calculated values. I used to do this with formulas in every cell on the sheet, but it was of course very, very slow. I converted to an all-vba Sub that does the calculations quickly and pastes the finished values into the report range in excel.

The problem is that I also need to run Goal Seek on various cells from time to time in order to make use of the model and Excel can't do it on a sheet cell that just has a value in it, it needs a cell with a formula. With my fancy, efficient array, I can't goal seek anymore because the cells are just numbers pasted in by my Sub!!!!!
Is there a way to run some version of Goal Seek NOT on excel sheet ranges but on array members, like on MyArray(107,23) by testing an input value that is actually on the excel sheet, like Range("B2")? What would that code look like? The ultimate goal would be to call a macro just like calling Goal Seek, enter ranges for Set Cell, To Value, and By Changing Cell just like Excel Goal Seek does, and have it run the operation on MyArray, but any help at all would be most welcome.

Paul_Hossler
02-26-2017, 09:33 PM
The method MS uses is pretty simplistic:

https://support.microsoft.com/en-us/help/100782/xl-method-used-by-goal-seek-to-find-a-solution


So to start with, I guess you would need

1. A function to perform the calculation, e.g. f(x) = 123*x^2
2. The desired 'goal', e.g. 10,000
3. A sub that would guess a starting x, try it in g(x), and adjust until it closes to the 'goal' +/- a tolerance OR so many iterations have passed, and a place to put the x value that give an x such that 0 = f(x) - 10,000


However there are other numerical methods like Newton Raphson method (that are faster) to iterate for a solution, that is difficult/ not possible to solve directly

https://en.wikipedia.org/wiki/Newton's_method

The attachment is a workbook with Newton-Raphson VBA function in it. The initial guess does not seem to be too sensitive, but you do need your function(s) written in VBA, and probably a different NR goal seek function of each of those since the NR function calls your f(x) internally