Finding value of X to satisfy equation

Assuming that numerical methods are required for something like this (these problems often require numerical methods). The usual approach in a spreadsheet for root finding algorithms is to use the built in Solver/Goal seek utilities (I prefer Solver because it is more robust). But Solver/Goal Seek only work with spreadsheet formulas -- they don't work on VBA code, and it appears that you are doing all of your calculations in VBA and then writing the results into the spreadsheet.

VBA does not have a built in numerical methods library you can use in VBA, so I see two possible approaches to this:
A) Put the necessary formulas in the spreadsheet, then you can use the built in Solver. This has the advantage that you only need to worry about coding/formulating f(x) -- you don't need to worry about coding the actual root finding algorithm.
B) Write your own root finding algorithm. For single variable problems like this, root finding algorithms are not overly difficult to code. Choose your desired algorithm (brute force, bisection, Newton, or other). Then write your implementation.

In the other thread, you indicated that you much prefer to use VBA for things. Shall we assume that you want to go with option B? Assuming you want to go with option B, the steps I would expect to go through:
1) Code to define f(x). Note that it needs to be in a form that we can set to 0 (f(x)-K, if it is that simple). I don't know if you intend to embed f(x) inside of the existing procedure or write a separate function procedure that the main procedure can call or exactly how you want this to look.
1a) If you are using Newton's method, and the function is easily differentiated, then you will need to do the calculus to come up with the expression for df/dx. If yoiu don't want to bother with the exact derivative, then you will need to approximate the derivative in some way.
1c) Any other calculations that need to be defined for your chosen root finding algorithm.
2) Select your initial guess(es) for your desired root finding algorithm, compute f(x) (and other functions as needed), compute new x, test for convergence, and repeat.

I explored Newton-Raphson type algorithms in this thread (https://www.excelforum.com/tips-and-...ml#post4688682 ) The final post includes a couple of simple UDFs that show how to set up a Newton-Raphson loop for a polynomial in VBA. The main thing that would be different is how you will define f(x) (and maybe f'(x)), but the basic algorithm would still be the same.

Did I miss the formulation for f(x) in your current code? How do you want to define f(x)? Do you want to use something like Newton's method, or a different algorithm?

I could not understand what you were trying to do with your f(x). The numerator and the denominator look the same to me, but that could just be me not understanding.

Hi, thank you for getting back to me. I tried this.

Please Login or Register  to view this content.

The idea is that it takes a random value for reacted. In this case I have chosen 0.01. as it needs to be greater than 0. It then works out the products and reactants and uses them to find amount reacted. It then adds 0.01 to the reacted value and repeats the loops until Amount_reacted - Total_K equals 0. Unfortunately it do sent seem to work. I think its because of the loops within the loops. Is there an easy fix to this? or should I start trying another approach? The numerator and denominator are very similar but for the numerator you add two of the value. For the denominator you subtract.

It looks like you are preferring an inefficient brute force method. That's okay, though I would suggest at some point you will want to learn more efficient root finding algorithms like Newton's method or a bisection method.

What do you mean that it "doesn't work"? Does it run but fails to choose the correct solution? Does it generate an error? Endless loop?

One possible problem I see. Between floating point and other imperfections, it is highly unlikely that a brute force step=0.01 method will ever result in amount_reacted-Total_K equal to exactly 0. If the test criteria is never exactly equal to 0, then the loop will never end. I would suggest that you need a better test criteria to determine when the loop should terminate.

In the "typo" category. I notice that, at the end of the loop, you are assigning a value to the variable "amounted_reacted", but your loop termination criteria is based on the variable "amount_reacted". Unless I missed something, the value of amount_reacted never changes during the loop, so the test criteria can never get closer to 0.

I cannot test anything at the moment, but try changing those kinds of things and see if your algorithm gets closer to working.

I tried changing the 0.01 to 1, then 10, then 100 to see if it it gave me an answer. I also changed the allowed difference range. The code continued to select c3 but not put a value in it. I even tried changing the parameters so that if difference>0.01 instead of less than just to see if I got a numerical answer. The cell was still blank. I think there is an issue with the loop and the reacted value isnt increasing every loop. I did consider trying a newton raphson approach. Here is the code I have at the moment for this section:

Please Login or Register  to view this content.

As with a lot of these problems, the first thing I did was to find the section of code of interest and add a stop statement so I can enter debug mode. In your case, I found the Finding no. moles reacted comment right before the Do Until ...<0.01 and put a stop statement in

Please Login or Register  to view this content.

I don't know how familiar you are with VBA's debugging tools (http://www.cpearson.com/excel/DebuggingVBA.aspx if you need to review), but adding the stop statement let's me execute the procedure and enter debug mode right at the start of the loop. I then look over the variables and add any watches I want (I thought activecell.address might be an interesting watch to keep track of what cell is active during the looping. I also add a watch for amount_reacted-total_K so I can see when the loop condition should test true). My watches tell me that D22 is the active cell and the test value is -7.5E-3 (already less than 0.001). As I step through the code, it skips right over the find total number of moles reacted loop (as suggested by my watch) and enters the "Do until activecell.value="moles reacted" loop (which looks like it merely selects the next row in the column). In this loop, as the active cell moves down column D, it is never going to encounter a cell that contains "moles reacted", so it loops until the bottom of the sheet and errors.

At this point, it looks to me like the procedure is not quite keeping track of where it should be in the spreadsheet. I think you will need to spend some time understanding exactly what you want to do and how you want the procedure to move through the spreadsheet.

If you are going to strongly prefer VBA over spreadsheet formulas, you are eventually going to want to become more familiar with using the Range object to eliminate most if not all of your .Select and Activecell references. Most of the more experienced VBA programmers never use activecell or select, because there are better and more efficient ways to refer to cells in a sheet. A few links that talk about the Range object and how to reference cells from within VBA without .Select:
https://powerspreadsheets.com/excel-vba-range-object/
http://www.tushar-mehta.com/excel/vb...rder/index.htm
https://peltiertech.com/how-to-fix-a-recorded-macro/
VBA help file: https://docs.microsoft.com/en-us/off....Range(object)

A little bit more debugging. I changed the Do Until statement so it was exactly equal to 0 again so that it would at least enter the main loop and I could see what was going on in the loop. First thing I notice is that this version left out the "reacted=0.01" statement, so reacted is 0 for this run through. Added back in the reacted=0.01 statement and stepped through it and discovered that Products and Reactants are empty and never initialized. This means that, when the code gets to those assignment statements "products=products*P2" and "reactants=reactants*R2", these values return 0, which means that the final amount_reacted division statement results in a 0/0 overflow error. It's still not clear to me exactly what your overall f(reacted) function looks like, but it doesn't seem right, yet.

VBA is a pretty robust programming language, so it doesn't get confused. VBA is doing exactly what you have told it to do. The usual break is that you think you told it to do something different. Part of debugging is determining where your instructions to VBA do not match the instructions you intended to give.

As I noted in post #7, when I ran it before making any changes (other than the stop statement), it got stuck in an infinite loop selecting each cell in column D until it got to the bottom of the spreadsheet and errored trying to go off the edge of the spreadsheet world. I think that was because D22 was not supposed to be the activecell at that point in the program, I expect it should have been one of the cells near the top of column C. I also think that part of the issue was that, because the Until condition was already True at the top of the main loop, it skipped right over the main loop and never even tried calculated products or reactants.

What you probably need at key points in the program is something like Activesheet.Range("C5").Select so that you have certain places in the code where you know what the activecell is.

At this point, I would encourage you to add the stop statement and become familiar with the debugging tools. Then step through the start of the main loop and see if you can figure out what you need it to do as it enters the main loop.

Ive managed to fix the loop so that it runs. Its still not giving me the value I want but I will keep working on it. Thank you for all your help

One obvious syntax error: The And operator does NOT execute two statements on the same line, so the statement

Please Login or Register  to view this content.

does not end up in Products and Reactants each containing the value 1. And is a boolean operator for testing two boolean statements with And logic. This statement takes the value 1(True) and the expression reactants=1 (reactants is 0 so this evaluates to False), then evaluates the overall boolean expression (True and False is False) and assigns that result to Products. Products now contains the value False/0, so it has not changed (and reactants has not changed either).
The way to join to statements on one line in VBA is by separating the statements with ":" so

Please Login or Register  to view this content.

Even with that change, the code does not seem to be converging on anything. The values for Products and Reactants and amount_reacted all seem to grow without bound, so the loop runs until it reaches an overflow error. I find -- especially with a brute force algorithm like this -- that I must understand the behavior of the objective function in order to put together a good, working algorithm. I edited your code to provide a means of analyzing the objective function in the spreadsheet:

Please Login or Register  to view this content.

Running the procedure through 20 loops now gives me a table of values that shows how the objective function (total_K-amount_reacted) and the other key variables are behaving during each iteration. If desired, you can insert a scatter chart (column S against column O) to visualize the behavior on a chart. It should be immediately obvious after only a few iterations that the values are growing without bound.

Putting my chemist hat on for a moment, it looks like you have a simple reaction like A+B->2C where 1 mole of A combines with 1 mole of B to give 2 moles of C. Your "experiment" shows 35 moles of A and 20 moles of B, and your goal is to figure out where the reaction ends up from that starting place (how many moles of C is produced and how many moles of A and B will be left over at the end). How am I doing so far? In terms of the variables in your program, Reacted and amount_reacted somehow measure the extent of reaction. Reactants and Products, I would guess, are related to the moles of reactants and products present during that iteration/test case. Am I understanding so far?

If I have mostly understood, then the observation that reactants, products, etc. grow without bound suggests to me that your procedure is failing to account for a material balance step. It seems like your procedure needs to somehow constrain the reactants, products, amount_reacted, etc. by the 55 total moles of reactants available or the 20 moles of limiting reactant available (or something). I don't fully understand the meanings of your variables or the exact procedure you are wanting to use to determine the extent of reaction, but it feels like you need to think more carefully through your chemistry until you get better expressions/loops for products, reactants, amount_reacted, etc.

I think I understand the problem better now, and I think I see the problems with your current code.

First, review the chemistry side of the problem. We have
1) a gas reaction A + B -> 2C where the number of moles of reactants is the same as the number of moles of products (important, because additional things need to be considered for reactions where the total moles will change).
2) We are given the number of moles for each product and reactant at the beginning of the experiment (this time, it is all reactants and no products, but we need not be limited to this case).
3) We have an expression for the equilibrium constant K=PRODUCT(for i=1 to number of products)((n0i+ai*x)^ai)/PRODUCT(for i=1 to number of reactants)((n0i-ai*x)^ai) that expresses the equilibrium constant in terms of the original number of moles of each species (n0i), the stoichiometry for each species (ai) and the number of moles reacted (x).
4) We have a value for K.

Our goal is to find the value for x. We want to use a numerical method even if the solution could be found explicitly because we know there will be problems that cannot be solved explicitly. At this point, we are using a brute force method.

Let me walk you through fixing this without actually giving a final solution (sorry, I think the educational value in actually discovering the problems yourself will be worth a lot to you in the future).

First, think through the algorithm as it currently stands:
step 1) set a starting value for x (Reacted in the program).
step 2a) Compute ni for each species reactants are calculated as previous moles reactants * (initial moles reactant i) - (reacted)*(stoichiometry))^stoichiometry. The first time through the loop, reactants has never been assigned a value, so it is 0, and the final result for ni is 0, so the final result for reactants is 0.
step 2b) compute ni for each species products are calculated. Formula is similar and, because products has not been initiated, products is 0 and the result will still be 0.
step 3) test for the 0, 0 condition and, when both reactants and products are found to be 0, initialize these variables to 1 and go back to the start of the loop.
step 4) new loop where reactants and products are starting at 1, and we can compute a new result for products and reactants based on the new starting values for reactants and products. We can now get reasonable values for reactants and products based on our current value for reacted (x).
step 5) Check for convergence (we probably did not get lucky to stumble upon the solution with our first guess for reacted, so choose a new value for reacted and loop again.
step 6) new loop where reactants and products contain the values from the previous iteration (hint hint, look very closely at this) and compute new values for products and reactants.
products = previous loop's products*(this loop's product calculations)
reactants = previous loop's reactants * (this loop's reactant calculations)
[hint: should each loop be using the previous loop's values for products and reactants???]
step 7) and continue looping until convergence (or until the inevitable overflow error because products and reactants become very large values).

Did you find and fix that problem? (fix this before going on)

Understanding that reacted represents the total moles that participate in the reaction, consider the available starting material (35 moles reactant A, 20 moles of reactant B), I look at the starting value for reacted (40). The theoretical maximum moles reacted would be to consume all 20 moles of reactant B, so 40 (and going up) seems like an unreasonable place to start. I would start with reacted=0 and increase from there. Starting at reacted=0, I let the program run and it finds a solution at reacted = 36 or 37 where the K-amount_reacted is about 27 (well within the 50 you are using for the convergence criteria). However, again, this value seems unreasonable, as the theoretical maximum number of moles reacted should be 20.

I add the little With..End With block to output the results of each iteration to the spreadsheet so that I can look more closely at the behavior of the functions. Charting the results of each iteration, I see that the function starts to drop towards 0 as it approaches 20 (maybe reasonable, since K is fairly large suggesting that most of the available reactants will react). I change my starting value to 19 and my step to 0.1, and the procedure ends at reacted=19.7 or 19.8. from this, I expect that the real solution to the problem lies somewhere between 19.5 and 20, and, with the right choice of starting value and step, you can find the solution.

All of that to get to only 3 changes that need to be made:
1) fix the problem with products/reactants
2&3) change the start value and step value for reacted so it can find the root between 19 and 20.

Did you get it figured out?

However if i chose another starting value for the reactants ie 16 then at 19.7 the value is unreasonable.

Right, it is unreasonable. I think you did not see what I was hoping you would find when you thought through this. According to the output in the spreadsheet, at reacted = 19 of the last run:
products = 4E92
reactants = 5E48.
When it tries to calculate new reactants, it is going to calculate 5E48*(35-1*19)^1*(20-1*19)^1=7E49
When it tries to calculate new products, it is going to calculate 4E92*(0+2*19)^2=5E95.
What I wanted you to see is that your loop needs to reset reactants and products at the start (or end) of the loop. It is not reasonable to carry the previous loop's values for products and reactants from iteration to iteration. At the start of your loop, put "reactants=1:products=1" to reset the products/reactants calculation.

It is certainly possible to code a divergence check into your loop, but that normally should not be necessary -- especially with a problem like this. A brute force algorithm should find the same root whether you start at 0, 10, 16, or 19. That it is not finding the same root is an indication that the OF is not correctly calculated. I would not worry about a divergence check until you have fixed the calculation of the objective function so that it is calculating the products, reactants, amount_reacted, etc. correctly. Once you are calculating your objective function correctly, then you should be able to proceed without needing to test for OF>large number.

I want to put another plug for a better root finding algorithm here. Are you dead set on using this brute force algorithm? The way your procedure is set up, it should not take too much more to use a bisection algorithm:

1) first run through OF loop with reacted=0, store 0 and f(0)
2) second run through with reacted=20, store 20, f(20)
2a) You can usually test at this point to see if there is a root between the max and min and exit the loop if no solution is possible.
3) bisect the guess (midpoint of 0 and 20 is (0+20)/2=10) and run through the loop with reacted=10.
4) test to see which side of the root 10 is on. If 10 is on the left side of the root, then replace the existing left point (reacted=0) with the 10 point, go back to step 3 and calculate a new midpoint, and repeat. If the 10 is on the right side of the root, then replace the existing right point (reacted=20) with the 10 point, go back to step 3, calculate a new midpoint, and repeat.

https://en.wikipedia.org/wiki/Bisection_method