[thrive_leads id=’2430′]
Welcome to the third video in this three-part Excel training for engineers. In this video I’ll show you how to use automation to avoid critical mistakes in Excel. Thanks so much for watching and giving feedback. I love hearing about how many of you are learning new techniques in Excel. Of course, as you learn more and more about using Excel, you can increase your efficiency, perform reliable calculations, and solve challenging engineering problems. All of these things make you more valuable to your employer. I know that’s certainly been the case for me as I’ve used Excel to build advanced engineering models over the last 10 plus years.
Okay, so here we are again in our worksheet. We’re using Excel to design the bolted joint that holds the lid on our pressure vessel. Now if you watched the last video, you saw how we calculated the required bolt diameter. First, we entered a guess value for the total bolt area in this cell. Then from that guess value we entered these five different formulas for the maximum stress, minimum stress, mean stress, alternating stress, and then the bolt equivalent stress. Then what we did was we created an equation to compare the bolt equivalent stress that we calculated from our guessed area to the bolt equivalent stress that we calculated as a function of the endurance strength divided by the safety factor.
Then we used Goal Seek to get that difference between these two cells down to zero, and we calculated that difference in this cell. What Goal Seek did is it automatically iterated through different values of the bolt area until this difference was driven to zero. Then finally we calculated an area per bolt. Then once we had that area per bolt, we used the index and match functions to look through this table of bolt data that I imported into the third sheet here. We looked through the tensile stress area column to find a tensile stress area that was greater than the area that we required, which was this value right here, and then we selected the corresponding nominal major diameter as our specified bolt diameter.
That’s really just kind of a brief overview of what we did in that video. If you want all the details, you’ll have to go back and watch that second video, and you can see the link down below on the page. Now that we’ve got all our calculations done here in this spreadsheet, you’d think everything was good to go, but there are some potential sources of error in this spreadsheet that we should address in order to avoid getting inaccurate results.
The first potential source of error in this spreadsheet is that we’ve got this number of fasteners here that needs to be entered into cell C5. The reason why that’s a potential source of error is because right now we have nothing that’s limiting a user from entering a decimal value into this cell. Anyone could easily enter a value such as 6.5, and without the correct number of decimal places shown in that cell or having the cell selected and looking up here in the formula bar, we can’t see that it’s a decimal value. Obviously that doesn’t make sense because you can’t have half a bolt. Now what we really like to do is force users to only enter a whole number into the cell and we can do that through something called Data Validation.
So if we go up here to the Data tab and then select Data Validation, we open up this window. Now this window allows us to set criteria on a cell that forces anyone using the spreadsheet to only enter a value that meets those criteria. For instance in our situation we only want to allow a whole number so we would select Whole Number from this drop-down under Allow. We also want to specify a minimum number of fasteners for this cell. It probably makes sense to not let that be anything less than three. We want that value to be greater than or equal to three.
Now along with that data validation and forcing that input value to meet this criteria, we can also put in a message so that when someone selects that cell or hovers over it, they’ll get this feedback from Excel guiding them as to what values they can enter as valid numbers. For our input message we could enter something like, “Please input a whole number greater than two.” Along with that we can also have an error message. So if someone does try to enter in a value that’s not valid for the cell, we can show an error message that says, “You did not enter a whole number greater than two.” Now if we click OK we can go back to the spreadsheet and see how that looks.
With that cell selected, we’re getting that input message, “Please input a whole number greater than two,” and if I try to input something that’s not a whole number, say I try to input 6.5 again, I get this error message that says, “You did not enter a whole number greater than two.” Now at that point I can have the opportunity to retry and put in a new value, so I can change it to nine, and it accepts that.
The second source of error that’s in here is the potential to not rerun Goal Seek when a value is updated. Goal Seek is something that we had to manually run every time we wanted to update the area in this spreadsheet. To do that we went to What-If-Analysis and we selected Goal Seek. We had to manually enter in the set cell, the value, and the cell that we wanted to change, then click OK. The Goal Seek tool did its thing and the results updated.
But watch what happens if I change one of the values for pressure. Say I change this pressure value from 25 mega pascals to 12 mega pascals. You can see that there was some updating going on in this column here, but one of the things that happened was that this equivalent stress difference is no longer zero. That’s because that Goal Seek is a manual operation. We have to go into that tool and update it.
I could go in and I could update that every time, and now I’ve got a valid result again. But what would happen if someone were in here and they changed one of the values and didn’t realize that they had to rerun Goal Seek to get a valid result? Well, the first thing we could do is we could use some conditional formatting to highlight a cell when Goal Seek hasn’t been run. In other words, if the absolute value of this cell is greater than some threshold that’s very small like .01, we could highlight that cell in red to indicate that Goal Seek needs to be run.
To set that up we go back to the Home menu, with that cell selected open Conditional Formatting, select Highlight Cells Rules, then select Greater Than. If the value in the cell is greater than something like .01, we’ll give it a light red fill with dark red text. Now that value could also be a negative number, so we’ll use Conditional Formatting to create another rule, this time a less than rule. If the value in the cell is less than negative 0.01, again, it’ll highlight that cell. So if I make an update here, I change this to 25, obviously this becomes non zero so now it’s highlighted in red to indicate that something’s wrong. Now it doesn’t necessarily tell us what needs to be done, so along with that maybe we would want to create an if statement to tell us what needs to be done when the cell turns red.
Right here we could use the IF function and that’s just equals if, and then we set up our logical test. You want to look at the absolute value of this cell. We use the ABS function. Then we want to determine if it’s greater than 0.01, and if that’s true, we want to return a message to this cell and it’s going to be text so we have to put it within double quotes and the message could say something like, “Rerun Goal Seek to update calculations.”
That’s the value if the logical test comes back true. If it comes back false, we just want to return a blank or nothing to the cell. So we just enter double quotes with nothing between them. Now when I hit Enter, we get that message that says, “Rerun Goal Seek to update calculations.” Of course, we can make this font red, just to make it stand out a little more.
Now if I changed my maximum pressure back to 12, which is what we were at before when we ran Goal Seek, the equivalent stress difference will go back to zero and add formatting and the message go away. It only pops up when I make a change to one of these cells that impacts the result.
That’s some partial automation. We’re at least getting a message or a warning that tells us that something’s wrong, but we still have to go through the manual task of running Goal Seek. It would be really nice on the other hand if we could just have that Goal Seek run for us automatically anytime we make a change to this worksheet to go back and calculate that area result that we’re looking for.
[thrive_leads id=’2430′]
We can actually do that through VBA which is Visual Basic for Applications. It’s the built in software language that comes in all Office products. We create VBA programs or VBA subroutines through the VBA editor. The way you access it is through the Developer tab by selecting Visual Basic. Now if you’re not seeing the Developer tab on your screen, you can go to File, Options, Customize Ribbon and select the checkbox right here next to developer.
Another way to open up the Visual Basic editor is just by typing Alt F11 on the keyboard, and that opens up this window. We see over on the left side of this window there’s a section that shows the workbook that we have open right now called Pressure Vessel Hardware Calculations 3, and then it shows all the objects that we can write code in within that workbook. We got Sheet1 which is about, Sheet2 for calculations, and Sheet3 for the bolt data.
What we want to do is we want to add code to the Calculations worksheet. We’ll double click on that to open the code window for it. That’s open right here. Now that that’s open, I’m just going to minimize this side of the screen here so we can actually see where we’re writing our code a little better. We want to create a worksheet change event macro. I’ll go up to this drop-down and select Worksheet, and by default it pops up with a selection change event.
What we actually want to use is a change event, so we’ll change that selection here to Change, and now we see we’ve created a section for some code that will run anytime the worksheet changes. I’ll delete this first one that was created down here. We don’t need that. What we want to do is we want to enter our subroutine between the line that says Private Sub Worksheet Change ByVal Target As Range and End Sub. Those are the opening and closing lines of our subroutine. The code that’s going to automate Goal Seek here is actually in between those two lines. I’ve got that in a text editor. I’m just going to copy and paste that in, and let’s talk about what this is actually doing.
The first line of code that I copied in is declaring a variable called isWorking and it’s setting that variable to be a Boolean. A Boolean can either have a value of true or false. The next major part of this code is this if statement that starts with if and not surprisingly ends with and if. This is what’s known as a conditional statement. In order to have a conditional statement, we have to have a condition first that we’re checking.
That’s being done right here in this line. In fact, it’s this whole thing right here. The first thing that’s doing is it’s looking for a range or a name cell called Objective. Now Objective is actually that cell that we’re trying to drive to a value of zero. I have to actually go back into the worksheet and give that cell that name, which I haven’t done yet. It’s right here, it hasn’t been named yet, so we’ll have to give that the name Objective to match what’s already in the code.
Then I’ll bring the VBA editor window back on screen here. It’s going to look at the value that’s in that cell, and then it’s going to use the round function to round that value to three decimal places. That’s just accounting for the error. Goal Seek is going to get the value to zero within some tolerance, and we’re assuming here that it’s going to have zeros in the first three decimal places. We’re going to check to see if that is not equal to zero. If that’s true and the Boolean isWorking is also not true, then the condition has been satisfied and we’re going to move on to the next line.
The first thing that’s going to happen is the value isWorking is going to be set to True. Then we’re going to set up Goal Seek. We’re going to take that cell called Objective and we’re going to perform a Goal Seek on it to set it to a value of zero. We’re going to do that by changing the cell called A total. Then once that Goal Seek is done running, we’re going to set that Boolean isWorking back to False.
Now the reason why we have this isWorking equals True, isWorking equals False, and then checking whether that value is true or false is because we’re using a worksheet change event in conjunction with Goal Seek. If we didn’t have this isWorking variable in here and we weren’t turning it on before we run a Goal Seek and off before we run a Goal Seek, Goal Seek would try to run every time Goal Seek changed to cell. Goal Seek would modify the cell, our worksheet change event would sense that as a change to the worksheet and try to start Goal Seek again. That Goal Seek would change the cell which would be sensed as a change to the worksheet and the subroutine would try to start Goal Seek again. As you can see, we’d get into this infinite loop of running Goal Seek.
So by adding this isWorking Boolean in here we’ve eliminated that from happening because we’re only going to allow Goal Seek to start which is this line here if isWorking is False. But as you can see isWorking is going to be set to true in the first line of this conditional statement. Once we get into this if statement, we’re not going to try to start Goal Seek multiple times. We’ll just run it once.
Now that’s kind of a long explanation for just four or five lines of code, but you can basically take this code, copy and paste it into your worksheet where you’re using Goal Seek. All you would have to do is update the names used here, here, and here with the names of the cells that you want to set to a specific value and the name of the cell that you want to change to achieve that value. Then of course if you have a goal other than zero, you can update that here. But otherwise this is code that you can copy and paste into your own worksheets to automate this Goal Seek process.
The code is in there. Let’s go back to our worksheet and let’s change one of the values and see what happens. Let’s set this maximum pressure back to 25 mega pascals. We can see that Goal Seek just automatically ran and set this equivalent stress difference, our objective back to zero. By using some of these automation methods you can create bulletproof spreadsheets that will help you be more efficient and accurate.
In this series we’ve covered just a sample of the ways that you can get the most out of Excel for your engineering projects. We started with some techniques to minimize confusion and frustration in your spreadsheets, progress through solving complex systems of equations, and finish by automating our spreadsheet to prevent errors. Hopefully you’ve seen how powerful Excel can be as an engineering tool and how harnessing its capabilities can help you efficiently solve challenging engineering problems which can ultimately lead to greater career opportunities. Maybe you’re interested in learning even more.
Well, in the next few days I’m going to send you information on the Excel Master Class For Engineers. It’s my step-by-step method for engineers to learn career advancing Excel skills. The course is designed to help you learn to use Excel in the unique way that engineers do. We’ll cover a variety of Excel concepts with many engineering related examples. In it I’ll show you how to increase your efficiency in Excel, communicate effectively through charts, gain confidence with advanced Excel functions, build bulletproof spreadsheets, find optimal design solutions, automate and customize Excel with VBA, and perform advanced calculus operations. Registration will only be open for a limited time so be on the lookout for an email from me containing all the details on the course.
Thanks so much for joining me through this video series, and before you go, please leave a comment below with any questions about this video and let me know how you want to use automation in your spreadsheets. Thanks again for watching and I’ll talk to you soon.
[thrive_leads id=’2430′]
