This is the very last screencast of Part Two of the course. Let me go on over quite complex example here. This is more for you, Engineering and Math types of people. Well, we're going to be doing is, we're going to be creating a user form that's going to allow the user to select a temperature for a particular reaction. And depending upon the temperature that governs something known as the equilibrium constant for the chemical reaction. And that goes into this equation here along with the pressure in bar. This is a nonlinear equation. It's quite complicated to solve explicitly for X, so we're going to use the bisection method 20 rounds to determine conversion given K_e and pressure. And again, K_e is going to come directly from a table here that depends upon the temperature. So let me just show you what we're going to be making. We click on this button. It brings up this calculator. I can put in a pressure here. The user can then select temperature. There's only six different temperatures that they can choose from, but then they can click Calculate and it determines the conversion of this chemical reaction. They can reset this and then they can quit. Also, we're going to implement input validation. So if the user leaves that blank, or puts a string in there, or puts a negative pressure in here, then it's not going to work. Also, the pressure has to be between 0.001 and 1000 bar. So it's going to only accept pressures in those ranges. So in no way do we ever bring up the editor or any sort of errors because we're protected against all possible errors. So that's what we're going to be creating in this screencast. On the course website, I have a file called Conversion Solver STARTER.slxm. So go ahead and download that and we can work through this together. Here's the user form. I've got fields here for, this is called pressure. This dropdown menu I've called temp select. I have a Calculate button. I have a Reset button, a Quit button, and Conversion. So this is the output. In module one, I've already got a sub named Open User Form. So this is going to be the code for opening the user form. Let's go ahead and code this first. Now our conversion form, this is known as conversion form, has a combo box here called temperature select. So we have to populate the items in this first and those items are going to be those six different temperatures that we had. So I've added those as add items here on our temperature select, that's our combo box. I've also added this just a default text, which is going to be 300. And now, after we do that, we're just going to open or show the conversion form and let's go ahead and just run this to make sure our combo box is working. I can select those six different items. So our combo box is working and the default value is 300. Let's go ahead and code the quick button, Unload Conversion Form. Let's code the reset button. So we unload it and then show it again. Now what we need to do is, we need to code the calculate button and again this is a, we're going to have to use the bisection technique for this. We're going to use depending upon the temperature that will govern the K_e, pressure is entered into that pressure text box on the user form. So we're going to have P here. We're going to have K_e. This is an F(x)=0 type problem, where X is the conversion. And if you recall, the flowchart for the bisection technique is shown here. Low is going to be zero and high is just going to be just under one, which is going to be 0.999. Conversion can never be above one or below zero. And for mathematical reasons, we can't have conversion exactly one. So I'm going to use 0.999 as my high for conversion. We enter into 20 rounds of iteration. Mid is just the average of low and high. We calculate F low, calculate F mid, we compare F low to F mid. If that product is negative, then that means that the solution is between low and mid and in that case, the new high is the old mid. Otherwise, the solutions on the upper half of the bound low to high and the new low, is the old mid and we keep going and going and going and we converge on a solution. So we're going to implement this bisection technique into this user form. If you want more information about the bisection technique, you can see week five of part one of the course. Let's go ahead and put the code in behind the calculate button. First thing I'm going to do is dim my variables. We have a low, mid, and high. We have K_e and i as an integer. Going to dim F low, F mid and F high as doubles index, that's going to be the index obtained from the combo box as an integer. The next thing we're going to do is, we're going to set low, the initial low to zero, high to 0.999. We're going to obtain this index, idx, from the temperature select combo box, that's going to be the list index. And now we've got a bunch of cases depending upon the index. So, recall that K_e here, the equilibrium constant, depends upon the different temperatures. So we're just depending upon the index of that combo box. We're defining what K_e is for each of those situations. For example, if index equals zero, that's the case when temperature is 300, then that's what the equilibrium constant is. So we obtain the equilibrium constant. The next step here is the bisection method. We're doing 20 iterations of the bisection method. We are calculating all of these things F low, F mid, F high, we don't actually need F high. And then we're doing our comparison to see which side of the interval, left-half or right-half, the solution lies in. Once we go through the 20 rounds of iteration, we will have converged on a solution. That will be a solution to this equation here. Notice here, for each of the functions, I'm using this external function, func, that uses pressure. This was entered into a text box on the user form. And we're using K_e, that was defined up here, and we're either using low or mid or high. And I'm subcontracting out this work to a function down here just to sort of simplify things. And finally, the conversion, that's the name of the text box on the user form, that's the output, is equal to, we format the number and I'm going to multiply the conversion in a fractional conversion to percentage conversion. And we're going to format that to the 10th place. On the sheet there's a button, so you can go ahead and assign that to open user form subroutine. And let's go ahead and see if this works. We can click on the conversion calculator. It populates this combo box with the six different temperatures, and now I can put in a pressure here, like 10 bar, click Calculate, and it gives me the conversion. I can reset this and then I can quit. You notice here that when we reset, it doesn't repopulate that box here. So let's go ahead and fix that problem first. I'm going to click on the reset here. Right now Reset is unloading conversion form but then showing conversion form. I also want to repopulate everything here. The temp select combo box, so I'm going to go back into here. And actually, I'm just going to instead of showing conversion form again, I'm just going to do open user form because that'll repopulate the temperature select combo box and then it'll show it. So, I can just copy this name and in the reset button instead of conversion form show, I'm going to run the open user form subroutine. Now, if I type in something and calculate, then I can reset and it starts afresh. So at this point everything's working fine. The problems though, if I put in something like high, then it brings up this debug box and the user, if this was professionally provided to somebody, they might click debug and it brings up the editor if the editor is not open, and we don't want that to happen. So we're going to add a few things into our code to validate the input. First thing I'm going to add at the very top is, if pressure is empty, then we're going to let the user know and we're going to exit this sub. If the user input is a string, like I just did in my example that I did, then we're going to use the Not IsNumeric. If you want more information on input validation in user forms, there's a screencast on that. So if this happens, if they enter a string, then we're going to let the user know and we're going to exit the sub. The last thing I've added in here is pressure has to be between 0.001 and 1000, so if that's not the case, then we're going to let the user know and we're going to exit the sub. So with this input validation now, if the user puts in something like a string and tries to do that, it's giving them a warning and it's not opening up that debug box, in which case the user can actually get into the visual basic editor and it kind of freaks them out and it's not very professional. And if I can put it in like negative numbers here and it lets them know that. And also, if we leave that blank, it's going to notify them that it can't be empty. So this is advanced example of how we can implement something like a solving technique, like the bisection method, implement that into a user form in a pretty professional manner here. Thanks for watching and thanks for a great course. This is the last screencast of part two of the course. Hope you enjoyed.
