Space in memory that can hold some information while your programs running to demonstrate how they work we're going to write a really simple example which allows us to add a new film to the end of our current list and then displays a message telling us which film was added so to get started let's head back into the visual basic editor and I can do that using the VB button on the Developer tab of the ribbon or press Alt + f11 on my keyboard and I've actually already started writing the basic subroutine that will do this so we've gone to the correct worksheet and then.
We've essentially said to the next blank cell at the end of column B if you've watched our video on ranges and selecting cells hopefully all the elements of this instruction will make sense what I'd like to do now is populate the cell that I've ended up in with the name of my new film so I'm going to refer to the active cell I'm going to set its value to be equal to let's see gravity which I keep on meaning to get around to watching but haven't quite managed yet so that will populate the cell with the name of the.
Film then what I'd like to do is display a message telling me which film has just been added so you may not we haven't covered this before in videos actually but I'm going to show you how to use a basic message box so the word MSG box and remember you can press ctrl + space on the keyboard to display this intellisense list and then the prompt for my message will be gravity was added to the list simple as that so if we run this routine just to test it we can hit the room but not.
Press f5 on the keyboard we should see that it works fairly sensibly we get the new film a litter then a list and message telling us that that Fillmore's added if I hit OK that will end the subroutine but what if I wanted them to change the name of the film that I've just added at this point if I wanted to add a different film that say American hustle for instance I've got two different places which I need to change that information and I can get a little bit marine so what I'd really like to do is rather than having to change this twice I'd like to store the name of the.
The variable what I'm going to do is head up to the top of my sub routine and add a new line which begins with the name of my variable new film name now valuable names can't contain spaces so make sure you avoid doing that what I can then do is store a piece of information inside that variable so to set the value of a variable or assign a value to it you make the variable equal to and then I'm going to store the literal string American hustle what I.
Can do now is later on in the subroutine rather than adding the same explicit string I can remove that reference and simply refer this line of code to whatever information is stored in the new film name variable so if I type that out again new film name and I need to do something similar to the message box line this is a little bit more complex if I remove American hustle from the existing message because I still want to be able to see the words was added to the list what I'd like to.
Do is add the phrase was added to the list to the end of the new film name so we're going to cover this in a bit more detail in a video on message boxes and input boxes but for now I'll show you the basic technique for concatenation which is where you join bits of text together you can do that using the ampersand symbol which is usually Shift + the number 7 on your keyboard so the entire message will be comprised of the new film name and the phrase was added to the list okay so there's the basics.
We've created a variable and assigned a value to it and then we've referred back to the value stored in the variable at two other places in our subroutine I'm going to step through this routine now using the f8 key just so you can see a couple of useful techniques when you're using variables if I begin the subroutine by pressing f8 once you can hover the mouse over the name of your variable and it tells you what's contained in the variable at any stage so to begin with the variable is empty.
Hopefully as you'd expect as I continue to press f8 when I execute this line subsequently it tells me that new film name contains the phrase American hustle so I can continue stepping through using the f8 key and this would be more helpful I suppose if you could actually see what was going on in the background there we go so we've got to the correct cell and then the value of the new cell will be set to be whatever it is stored in the new film name variable and there it is the final step is to display the message which also reads in the name of.
Posts Related:
The variable again there it is and then one final effort to end the subroutine
So there's the probably the most basic way to use variables in VBA one big improvement we could make to our system as it stands is rather than always explicitly stating which film name we should add to the list we could actually replace that so that we ask the user which film they wanted to add we can do that using something called an input box we saw a basic message boxes earlier on which display information on.The screen an input box is similar but it allows the user the opportunity to type something in as well so to set the value of our variable using an input box I'm going to replace this explicit string of text with a reference to the input box function and you can see this in the intellisense list now an input box usually needs an instruction or a question which informs the user what to do so I can add one of those by opening a set of parentheses and a set of double quotes and then provide the prompt to my user I'll tell them to type in a new film name close the double quotes in closer parentheses.
And that will display the input box on-screen so if I use the fa key to begin stepping through we can see that the new film name variable is currently empty but if I step through that line we'll see the input box appears on screen containing my instruction we'll see what a new film could I add 12 years of slave that's getting some pretty good press at the moment 12 years a slave and if I click OK at this point you can see that that value has now been stored in our variable so continue to step through the same thing will happen as last time we'll get to the end of column B in the next buying cell pass the value of our film into that blank cell and then.
Display the message telling us that that film has been added so that's how input boxes work in a basic fashion and we'll have a much longer video which explains how those work along with messy boxes a later stage so far we've created variables essentially by making up a new word typing it into our code and then assigning a value directly to it it's nice and simple to use variables that way but it can leave you open to some potential sources of bugs that can be quite tricky to track down so to try to demonstrate what I mean by that I'm.
Going to accidentally deliberately misspell one of the times I refer to my variable so instead of a new film name it's now called a new flim name I can then try to execute my subroutine and to begin with everything appears too well normally I can type in a new film name let's see the railway man supposedly quite good and if I click OK I'll end up with a message telling me that the railway man was added to the list however you can clearly see in the background in Excel that that film has not been added to the next blank cell so.
What's actually going on there if I click OK I think the easiest way to see what's going on it is to show you another useful tool for when you're stepping through a subroutine containing variables if you head to the View menu in the visual basic editor there's an option called the locals window and what the locals window does is it shows you what the values of any variables are while you're stepping through a subroutine so all be empty while you're not stepping through a routine but as.
Soon as you press f8 to begin a subroutine the locals window will appear to show you a list of all of the variables that belong to it you can see their values so currently both empty and you can also see something called that type which we're going to get back to shortly you can probably see here already that I've got two variables listed new film name and new flim name and as I begin to step through my subroutine using the f8 key to step through typing a new film name I'll go.
For the same one again the railway man I should put a shorter one to know easier to type the railway man when I click OK I'll hit enter when I come back to my subroutine and look in the locals window it shows me that the value of that variable is still set to the railway man as a continue pressing f8 step-through when I reach the active cell devalue line which is going to refer to the new flim name variable we can clearly see that variable is still empty so even a.
Slight typo generates a completely new variable a completely new space in memory which holds a different value to the other one so when I press f8 essentially puts and the value of the contents of an empty variable into my cell however the third line or that the third reference the variable reads the railwayman because that's what was set earlier on hence we see the correct message so how can we avoid running into problems like that with misspelled variable names the solution to our.
Problem here is to use a type of variable referred to as an explicitly declared variable and that's simply a
Variable way you're required to tell your program you're going to use it before you assign a value to it the type of variable that we've used so far is something called a non declared variable and that type of variable is where you just make up a new word and immediately assign a value to it I guess you can think of the difference as the different ways you could get a hotel room an explicitly declared variable would be like phoning up in advance or booking.Online and choosing what type of room you want whereas a non declared variable would be you just turning up on the day and asking for a room so to use none declare our sorry explicitly declared variables you have to add an extra little bit of code to the very top of your module so if I get myself a couple of blank lines above my subroutine and it's type in two different words option explicit if I move the cursor to a new line or hit enter I'll see that I get a horizontal line separating out that directive from the.
Rest of my code and what that means at this point is that I can't use any subroutine that contains non declared variables within this module you'll see if I try to run my subroutine the first thing that happens is it has a problem with my non declared variable called new fill name it says my variable has not been defined so if I click ok stop running my subroutine what I need to do is declare my new film name variable before I can.
Assign a value to it to declare a variable we need to add a new line of code to the top of our subroutine by convention you declare all your variables before you write any other instructions to begin declaring you write the word dim which is short for word dimension although that's probably the least important thing you need to know about variables and then you think of a sensible name for your variable which actually we've already decided on we're going to call our variable new film name what you can then do.
Optionally although I would recommend doing this is worthwhile doing is to say what type of data you want to be allowed to store in this variable so you can restrict your variable to only containing certain kinds of information to do that what we need to do is type in the word as and then pick from what's a rather long list actually we're only going to use a limited set of the options in this list one we're going to choose is the option called string so string is anything which contains any kind of text so there we go there's a.
Declaration for our variable called new film name if I just Reese Pell my variable name correctly later on and and in fact there's a much easy way to make sure you never get your variable names wrong from this point on in fact if I delete it completely if you declare variables like this you'll find that they get added to the intellisense list if I press ctrl + space and start looking for the word new film name it becomes a new entry within the intellisense that's one big major.
Advantage of having declared variables okay so let's step through the subroutine using f8 and we can see that again the locals window shows me the name of the new variable and it's a slightly different piece of information in the value column previously said the word empty there now it has a pair of double quotes so what's referred to as an empty string it also tells us that the data type itself is a string as I continued.
Stepping through we'll find that everything else works normally basically from this point on second into the railway man and that's added to the year the variable and continuing to press f8 step all the way through I'll eventually end up with a message and indeed it tells me the truth this time the railway man was added to the list the really big advantage of using explicitly declared variables and in fact the reason we were going to do this in the first place is to help us pick out little bugs resulting from misspelled variable names now if you're.
Using the intellisense list like we've just discussed then that shouldn't be an issue but it is still possible to get things wrong so let's watch what happens now if I've misspelled my variable as soon as I try to run my subroutine I get a warning message it tells me that I've got a variable that has not been defined or declared and not only that but in the background of my code it also picks out and highlights exactly where the misspelled variable name is and trust me that's such a lifesaver if you've ever spent hours picking through code to find a misspelled variable name that's.
Causing your program not to work properly this is absolutely brilliant so explicitly declared variables are a good thing I'm going to show you now just very quickly when I restore my variable name correctly so that things are working on how you can ensure that option explicit always appears at the top of each new module that you create so I genuinely think it is it should be essential in all the code that you write if you head to the Tools menu in the.
Visual basic editor and choose options on the dialog box which appears the second check box on the first page of editor the second check box says require variable declaration so if I check that box and then click OK what that means is any new module that I insert into a project now is going to automatically have option explicit list at the top and just me that's a good thing now that we've seen the basics of our variables work it wouldn't be too much trouble to extend our program to allow.
Us to include more details the next time we add a new film so if I just quickly switch back into excel so you can see I've deleted the film names that we added previously the next time add a new film I'd also like to include not just its name but its release date and its length in minutes so in order to do that I need to declare a couple of new variables and they need to understand a little bit more about the variable data types so far we've only used the data type called string and what that allows me to do is store text but now I need to be able to store a bait and some kind of.
Number so the best way to find out about variable datatypes is use the help system built into the visual basic editor you can either click on the Help button on the toolbar or press the f1 key on your keyboard or of course head to the help menu and choose the VBA help option when the system opens you can search for data type and then hitting Enter should return an article that summarizes the data types in VBA you it's quite a technical article but basically all it does is list out the various data types.
And their names in the first column how much memory they require to store their values and finally a technical description of the range of values that you're allowed to store within them so let's start with the easy one I would store the release date of the film and there is a data type here called date and the range of dates that it allows me to store is more than sufficient for what I want to include so that's the data type I'll use for the release date of the film when it comes to the length in minutes that's a little bit more tricky because most of the data types in VBA are to do with some kind of number.
They range from the very basic byte which allows you to store only whole numbers with a quite small maximum limit of 255 then we go through integer long wonderfully named long long and then through into the decimal value so single double and currency I'm also a decimal as well of course the idea behind the various different types of numbers is that what you ideally want to do is use the data type with the minimum storage size or memory footprint to hold the values you want I think bytes might be pushing things a.
Little bit because the some of the size of bite can only hold it 200 up to 255 so what I'm going to do instead is use the date so called integer whose maximum is far in excess of what I actually need but it means that I won't run into problems if I do exceed the maximum of bite so I'm going to use date for the release date and integer the length within minutes how close ups us down and then they just head back to our code so now we can.
Declare a couple of new variables to hold the new values that we want so I can say dim and then say film date for want of a better name and the data type of this one as we saw always going to be as date the next one will be dim let's call this film length and we'll saw this one as an integer so there's those are the two new variables declared what I want to do now is provide a way for my user to give that piece of information to the program so we're going to use a couple of new input boxes so just after the first one once again.
I'm going to use the intellisense list so that I can find my film date variable I'm going to make that equal to input box open parenthesis and typing the release date then on the next line again I'm gonna use the intellisense list to find my film length variable and make that equal to another input box which will ask me to type in the length in minutes I could spell length that would help so there we are those are the.
Variables being populated the last step would be to make sure that both the values of those variables get transferred into the appropriate cells so a little further down here where we we would pass the film's name into the active cell what I want to do then is say active cell dot offset 0 comma 1 dot value equals film date if I use my intellisense lists to help me type it in and make sure I don't spell it incorrectly so that will populate the column 1 to the right at the film name and then a quick cheat here I think I.
Was not really cheating if it speeds up writing code in my book column two cells to the right will be the film length so there we are if I wanted to step through this subroutine just so you can see the locals window now gets populated with more than one variable if I use the f8 key using you feel named filmed eight film length it's worthwhile noting that different data types provide you with different default values for your variables so you can see that numerical datatypes have a default.
Value of zero the film vaids has a default time of zero effectively and as I pass through the program using f8 to step through let's see I'll go with gravity again capital G please I'm gonna see the gravity get stored in a new film name variable as earlier when I go to the release date the release date of gravity in the UK was the 8th of November 2013 so let's enter that we see that the value gets stored and then one more length in minutes was 90 minutes.
Okay so the rest of the subroutine will just work as normal now the valleys will get passed into the appropriate cells in fact if I just quickly show you this in the background let's put an exciting you'll probably work out what's going to happen already anyway so gravity goes into I'll sell the film date will go in next door and finally the release date we're going in the final column we'll still get our message telling me that gravity was up the list and that's the end of the subroutine so that's how easy.
It is to extend and use more variables in a program you can declare pretty much as many variables as you need to store all the values that you're interested in one thing you have to be quite careful of when you've declared variables with specific data types it's to make sure that only the correct type of data gets stored in them so for instance we've got a variable here which can only accept a date and if I try to pass in something which isn't a date I'm going to receive some kind of error let me show you the sort of thing you're likely to see if I run the subroutine to enter a new film.
Myself any other thing we'll do just for the purposes of this demonstration if I then try to enter a release date which isn't a valid date let's say something like the 31st of Feb 2013 as soon as I hit OK I'm going to receive my error message it's a runtime error so an error that occurs while your program is running hopefully obviously and the type of problem is something called a type mismatch or a data type mismatch and you will receive that sort of error when the type of information you've tried to store in a particular variable is not of.
The correct type if I hit the debug button it will tell me the line or show me the line which is caused the error to occur that should be hopefully fairly obvious it's the one where I've tried to store it invalidate into a variable which can only contain a date it's a fairly common error you're likely to see with specifically types variables another common error you're likely to see when using data types with variables is where you exceed the limit of a numerical data type so as you saw earlier on an integer datatype can hold a maximum value of 32,767 if we try to.
Exceed that value I'm going to see some other kind of error so if I run my subroutine again let's put the hobbits in and we'll enter a valid release date this time which was in the UK at least it was the 13th of December 2012 and I'm going to went to a really long running time anything that's longer than 32,767 will cause this error to occur I know obviously the hobbit didn't take that long to run although kind of felt like it at various points but if I click the ok' button now we're gonna see another type of run tomorrow this sum is an overflow error and you'll see that.
Whenever you exceed the limits of a numerical data type again if I click the debug button that will show it's this line which is caused to failure in this place and yeah again that's another error that you have to watch out for with with variables with different data types so far in this video we've populated all of our variables by using input boxes that's by no means the only way that you can populate a variable we're going to add one more bit of information to our film list for the one piece of data we haven't added when.
We've included new films is the ID number of the new film so I'm going to remove the details of any additional films I've added again and the next time we add a new film it's going to fill in all the current data but also work out what the new ID number is and we're going to use a variable to to perform that basic calculation so back into the visual basic editor I'm going to declare a new variable and I'm going to do that somewhere towards the top of my sub regime so there's a dim new ID or let's call it film ID actually film ID as and.
Again because it's going to be some kind of number I'm going to use let's see let's say integer I'm pretty certain I'm not going to get around to adding for over 32,000 films to this list so film idea is integer and then I need to set its value somewhere the place that I'm going to do that is just after I've reached the end of column B since I got to that position I'm going to store the film ID or the new film ID by making it equal to try again by making this equal to the active.