excel solver like Public Function

Hi,

I'm Trying to write a public function that is like the solver add-in in excel. The reason I cant use solver is because the solver add-in doesn't automatically recalculate as the values in the cells are changing. My spreadsheet is dynamic and the values are constantly changing.

I have three values. These are in cells A1, A2, and A3. I call these value1, value2 and value3 respectively in the code below. Changing the value in A3 changes A1 but not A2. I would like a function that will give me the value of A3 for which A1-A2=0

here is the code i have tried to write but it obviously isn't working. I'd appreciate any help on this. There is probably some simple loop type function that would do the trick. Please help.

Public Function solv(value1 As Double, value2 As Double, value3 As Double) As Double

    Dim diff As Double
    Dim countr As Integer
    
          
    diff = 0.00000001
    
    countr = 0

    While Abs(value1 - value2) > diff
        countr = countr + 1
     
           
    Wend
    solv = value3
    
End Function

Hi Shg,

to use the solver function one has to manually click on the solver button in the excel ribbon and recalculate. Plus on any given spreadsheet you can only use solver for a single set of cells. That is why i would like to have a function that i can use throughout the spreadsheet.

There are many different algorithms for finding roots of equations. How familiar are you with numerical methods? Which numerical method would you use? What kind of function is in A2?


Newton-Raphson method is pretty easy to program when there's only one variable changing, assuming the function can be readily differentiated.
A bisection type algorithm is another that I have used that isn't too difficult to program, but you have to be able to give the algorithm two starting points, one on each side of the root.

let say the range of values for value3 could be .01 - 1

how would this work with the bisection method? could you give an example code?

quick and untested pseudocode, for 2^-10 precision

lox=.01
highx=1
loy=f(lox)
highy=f(highx)
comment -- this method works best when the sign of loy and highy are different, because this makes an easy test for which side of the root you are at
if loy*highy>0 then what to do if both initial guesses are on the same side of the root.
for i=1 to 10 'comment increase the number of iterations for greater precision
newx=(lox+highx)/2
newy=f(newx)
if loy*newy>0 then
lox=newx
loy=newy
else
highx=newx
highy=newy
end if
next i
'comment might include some code here to test and make sure f(newx) is close to 0

how would this work for an unknown function F(value3) that produces value1?

Off the top of my head, I don't think it can. Any root finding algorithm that I can think of needs to be able to calculate F. If you code it right, you can make F as generic as you need to, but I think you are going to need to put something in the code so it can calculate F.

what do u mean when u say "make F as generic as you need to"...if the function is actually some complicated formula but when i code it i enter F(x)= 2*x .... it wouldn't work would it?

what do u mean when u say "make F as generic as you need to".

That means that you code F so that you can pass necessary parameter to it. For example, if you are working with polynomials (F(x)=A+Bx+Cx^2+Dx^3+...), you can pass the parameters A,B,C,D,... to the function rather than hard coding those values into the code.

if the function is actually some complicated formula but when i code it i enter F(x)= 2*x .... it wouldn't work would it?

I don't see why not. Of course you can't code it in algebraic syntax, you have to use VBA syntax. I got the impression from the 1st post that you had already figured out how to code the function into a spreadsheet cell. Call me optimistic, but I figure any function that you can code into a spreadsheet cell can be adapted into one or more lines of VBA code.

can the address, match and index functions from an excel spreadsheet be used in a VBA code? im trying to enter into vba code Application.Worksheet.Function.Address(2, 3) but it doesn't seem to work...is it because some excel functions cannot be used in VBA? How do i know which ones can and which ones cannot?

thanks

You can access worksheet function through the Worksheetfunction property (Application.worksheetfunction.match(arguments). I thought there was a list of worksheet functions available to VBA in the VBA help files. Try putting something like worksheet functions available to VBA in the VBA search engine and see if it comes up.

The match function is commonly called from VBA. The others, not so much, probably because the .cells(i,j) property/method usually works just as well without needing the worksheet function version.

I have a function that has 3 variables ( f(x,y,z) = 2X + y^2 + 1/z ) that i would like to compare to a set of values that i will call "observed values f(obs)"... i would like to find the values for x, y, and Z that will minimize the sum of squares....

please see the attachment for the equation

minimize.JPG

how do i do this optimization problem?
just to be clear...f(obs) are just values listed in Column A...like 1.5, 1.6, 1.8, 1.9, 2.5, 3.6, 5.4, 8, 9.1 ...etc

lets say we had only two observed values...2.5 and 3.5

the question is ....for what values of x,y,and z would we minimize.... [2.5 - (2X + y^2 + 1/z )]^2 + [(3.5 - (2X + y^2 + 1/z )]^2

please let me know if this makes sense

Attachment 159330

please have a look at the attached file with example...

i can do this with solver but i need to do this in realtime..which is why im trying to have vba code for this...

if u use solver and use the following settings....target cell C6, equal to "min", and by changing cells $E$3:$G$3....u get an answer

how do i attach a file to this thread?

as long as the initial value of z is not zero this works in solver....however i need to automate this so i don't have to do this manually using solver because the observed values will change in real time in my actual sheet

exampleoptimize.xlsx

attached spreadsheet

http://www.excelforum.com/excel-prog...unction-2.html

the attachment if for the thread at the above link...

How do u attach a file to an existing thread? I only see the attachment option when I start a new thread

http://www.excelforum.com/excel-prog...84#post2809584

i have posted the attachment on a new thread...the link is above , thats the only way it was allowing me to attach a spreadsheet