Help in creating a loop function

**giorgior27** · 04-14-2021, 07:23 AM

Hello,
I'm new to excel VBA and I'm trying to create a function to increment values until a condition is met. How can I do that?

My example is the following:

x_start	x	z
y_start	y

z is a value that depends on x and y values and other cells in the sheet.
As example I want to define x_start and y_start and then increment x and y by an integer numeber (suppose 2) until z is > 1. (While doing this all the other cells are fixed).

I can do that with goal seek maybe but would like to define a formula so that every time I change x_start or y_start the value x or y is recomputed.

**MrShorty** · 04-14-2021, 01:20 PM

Your request is kind of vague and generic, so I'm not sure what specific response to give. How much of your question is about loop structures? How much is about the broader root finding algorithm? How much is just how to make a UDF? Have you looked z=f(x,y) and determined that a numerical algorithm is needed -- that you cannot solve for x or y at a given z algebraically?

If your question is more about how to make a UDF, I might suggest my tutorial here: https://www.excelforum.com/tips-and-...uild-udfs.html

If your question is more about loops in VBA, this looks good: https://trumpexcel.com/vba-loops/

If your question is more about loops in root finding algorithms, I put together a simple UDF that uses the Newton-Raphson method to find roots of cubic polynomials (2D function) (https://www.excelforum.com/tips-and-...ml#post4688682 ) that should illustrate the use of a For..Next loop in one root finding algorithm.

Function NRcuberoot(xi As Double, a As Double, b As Double, c As Double, d As Double) As Double
'Because this receives parameters as doubles, it should be faster than the other function.
'However, it is limited to cubic (or smaller) polynomials. It is not flexible.
Dim xnew As Double, xold As Double, f As Double, df As Double
Dim i As Integer
'initialize loop
xnew = xi
For i = 1 To 100 'max iteration is 100
    xold = xnew
    f = a + b * xnew + c * xnew ^ 2 + d * xnew ^ 3
    df = b + 2 * c * xnew + 3 * d * xnew ^ 2
    xnew = xnew - f / df
    'If converged exit loop and finish
    If Abs(xnew - xold) < 0.000001 Then Exit For
Next i
'normally I would include a block to return an error if the loop did not converge (if i>=100)
NRcuberoot = xnew
End Function

I would also ask if you are required to use VBA for this? For years, VBA has been talked about as a dying programming language. MSFT has even started trying to steer people towards a new "office add-ins" model based on other, more enduring programming languages (like Javascript). Where you are new to VBA, it may be advantageous to consider whether your time would be better spent learning a programming language that is less likely to be decommissioned soon. As an old dog not learning new tricks, I have no idea how one would build a UDF in the new model, but I could see some value in learning the new ways rather than learning the old ways.

I know that isn't very specific, but maybe it will provide an opportunity for further feedback. How can we help you move forward?

**giorgior27** · 04-14-2021, 06:31 PM

Thank you for your answer and sorry if the post was not clear.

I tought a loop function would be easier to use because I was not able to find a good approximation of the function based on data.
If you have any other suggestion to achieve the same result we can use other solutions. I'm not forced to use VBA, was just an idea to solve this problem.

My idea was something like that

Function (Z, X_start, Y_start)
x_new = x_start                        %% x_new is the value in the cell B1
y_new = y_start 
    
     While z < 1
     x_new = x_new+1                %% z is compute using many other data and x_new, y_new
     y_new = y_new+1                %% Here I want to update value in B column (x_new, y_new) and compute z in the sheet, if z<1 continue 
     end while
 
end function

Code should be very simple, but again if you have some other solution we can try. The fact is that as you can see I don't know VBA so would like to implement this logic and the first think that came to my mind was VBA.

I will look at the links you gave me to see if they can help.

The function you provided could be use in my case ?

**MrShorty** · 04-14-2021, 09:15 PM

The function you provided could be use in my case ?

The function I wrote in post #2 is specific to a 2D cubic polynomial y=ax^3+bx^2+cx+d. It could be adapted to many other 2D functions where the expression for dy/dx is not too difficult to come up with. Extending the NR method to 3D and higher, though, is rather laborious. It can be done, if you are interested in exploring the possibility, but, at some point, it is just easier to figure out how to call Excel's Solver using the programming language you choose to use. Or use a programming language (like Mathcad or maybe python) that has a built in 3D NR algorithm type solver library.

Of course, all of that assumes that such numerical algorithms are necessary to find the roots of z. You have not shared anything about z=f(x,y), so it is difficult to make any specific recommendations. I would expect that some knowledge of f(x,y) would be necessary in order to choose an appropriate root finding algorithm and then implement it.

**giorgior27** · 04-14-2021, 09:15 PM

I'm trying to adapt one code that I found to my case but it shows error

Function Increment (att As Range, n As Range, r As Range) As Double
    Dim i As Long
    Range(n).Value = Range(att).Value
    Application.ScreenUpdating = False
    For i = 1 To 100
        Do While Range(r).Value < 1
            Range(pre).Value = Range(pre).Value + 1
        Loop
    Next i
    Increment = Range(pre).Value
    Application.ScreenUpdating = True
End Function

**MrShorty** · 04-14-2021, 09:20 PM

att, n, and r are defined as Range objects in the function statement, so you don't refer to them using the Range(item) syntax. n.Value or att.Value or r.Value are the appropriate syntax for determining the value of each of these range objects.

Note that, if you intend for this to be a UDF (a function procedure called from a spreadsheet cell like a regular spreadsheet function), a UDF cannot modify cells in the spreadsheet. So a UDF cannot take a cell value in the att cell and write it to the n cell.

**giorgior27** · 04-14-2021, 09:28 PM

Thanks again for your help.

About the function is difficult to estimate, I tried using matlab and best approximation was given by exponential of the type
a*exp(b*x) + c*exp(d*x)
Polynomial even of high order was giving bas result

That's why I was thinking to something more "trial and error"

About the code I found a sub that did something similar to my case and tried to adapt it for a function. Didn't know these things.

**MrShorty** · 04-15-2021, 11:03 AM

As I said, understanding z=f(x,y) is key to solving a problem like this. You reference trying to regress different approximations, which suggests that f(x,y) is not defined by an expression, but, rather, defined by a table/list of individual data points.

If that kind of function provides a "close" approximation of the tabulated data, that suggests that z=1 is not a single point, but rather a contour line, which suggests that there will be many possible x,y pairs where z>1. Are there other constraints or considerations on the solution, or will any x,y pair where z>1 be a suitable solution? Or is the problem more about finding the approximate location of the z=1 contour?

Making a bunch of assumptions about the problem, here's a quick sheet I put together that uses a simple lookup function on tabulated data points (x down the left, y across the top, z in the body) to find the approximate contour where z is just larger than 1. I created some data (rows 6 to 17) by randomly choosing some values for a, b, c, and d (row 4) in the approximation you give. Then sorted the table so that z is descending in each column, then used a simple lookup algorithm to find the row where z is just larger than 1 (row 20) and extracted the corresponding x value (row 21) for each y value (rows 6 and 22). I could further refine the solution, if desired, by coming up with an interpolation algorithm to get a better estimate for the z=1 contour.

I'm not sure if that is even close to what you are trying to do, but it doesn't require any VBA or any loops. Just a simple lookup on tabulated data. If that doesn't help, please help us understand exactly what you need to do.