Hi there and welcome to the first lesson on Visual Basic for Applications. So, I'm just going to show you the utility and power of VBA, I've got a couple of examples that we're going to go through. So let me just show you a real simple example, here we've got. I'm just got a cell there and I've made a sub that's just going to take the negative of this. So, I'm going to click in that cell. I've already got this macro made. So I can go up here to the "Developer" tab on the ribbon here and I can click on "Macros" and I can just do make number negative that's a sub that I've already made. You see that it makes it negative. By the way if you don't have the Developer tab, you can go to "File", "Options", "Customize Ribbon" and make sure that the Developer tab is selected. So that's a real simple sub. You can also insert a button here to run it from the spreadsheet. When you insert a button like that it's going to ask you which macro to assign the button to. So I'm going to do my make number negative and you can customize this button, you can change the format if you want, but I've got a cell there and I can click on that and it makes it negative. The next example I want to show you is just a sub that's going to swap the contents of two adjacent cells. I go up here to Macros and I'm going to run my swap subroutine and you see that it just swaps those. So let's run that again, swap and so on. You could also assign a button if you want it to. Another way by the way to bring up the Macros is just to use Alt F8 and that brings up the Macro menu. Okay somewhat more advanced maybe a moderate application of VBA is a search technique. At search tool you can imagine you have thousands of data on a spreadsheet maybe names and addresses or whatever and you need to search for something, you need to output what cell it's in. So here's just a real basic example of that sort application, I highlight a bunch of cells. By the way whatever is highlighted is known as a selection. So that's a selection and this single cell is also a selection. We have something known as an active cell. So of this selection, A1 is the active cell. I can also click there and that's the active cell. So that's some two really important references are going to be the active cell and a selection. So I've got a selection here and then I'm going to click "Run Search". I've programmed it so it such that it asks you for a string you're searching for. So maybe I know part of it maybe like arm where is arm found? I click "OK" it searches through all combinations of three strings and all of these cells in our selection and it says, "Your string was found in row one and column three." So on a couple of weeks you're going to know how to how to do this. All right it's somewhat more complex example or implementation of VBA would be to filter and analyze data. So I've got a bunch of data here and sometimes when an instrument is not working properly it'll just spit out like a 9999 or negative 9999 something like that. So you really want to filter through your data and maybe for some reason we have words in there. You might have thousands of data points that you need to filter, and instead of manually going through line at a time and and deleting the 9999 and so on. You can make a sub that does this, I've got a filter and analyze. What it's going to do is it's going to first remove all the strings. So, all the words it's going to just delete, it's also going to delete all the 9999s. Then where there were blanks it's going to shift everything up and squeeze all the remaining ones together. Furthermore, I have it such that it uses statistical technique to remove any outliers in the data. So, I think this 24.7 is an outlier and I think this 7.7 is an outlier. So I can select the data, so that's my selection, that's the object that this subroutine is going to act on. Also it's going to output the average and standard deviation of the remaining items. So let's go ahead and click "Filter & Analyze". So it goes through that very quickly, you see how it's squeezed all the remaining ones up and it's got rid of all the text and the 9999s. It provides us the average and the standard deviation. Finally, my favorite this is a example of a complex user form. User forms are these things that you're going to learn how to do towards the end of this course. It brings up a user form, so this one is pretty cool in that when I click on "What is the weather here?" It's going to actually interface onto the Internet and pull down the most recent NOA data for the wind, conditions and so on in Denali National Park for example or whatever ones I click. So, let's go ahead and click this button. The current temperature in Denali National Park is eight degrees. So you can do some really complicated things with VBA.