Excel VBA Introduction Part 11.1 - Input Boxes

welcome to this was our tutorial in this video we're going to talk about using input boxes in Excel VBA what we'll cover in this video are all the basic techniques you'll need to understand how to get user input so I'll start by talking about how to display an input box on screen and how to perform basic customization of that input box then we'll move on and talked about how you can capture the result of an input box otherwise there's not much point in displaying one and we'll mention what happens when you cancel from an input box and how you can test whether that's happened and finally we'll talk about some technical details about what happens when you return different data types using input boxes so let's get started a convenient way to ask for an information in VBA is to use something called an input box and I'm going to start by showing you how to make one of those simply appear on-screen so I've started a subroutine which is going to ask the user what their name is and to do that I'm going to display an input box you can either type in the wording by box or if you prefer press ctrl + space on the keyboard to display the intellisense list and then look for the word input box in the list once it's highlighted type in a space and that will display the parameter list it shows you that there are seven different parameters for a number box there's only one compulsory one this is the one called prompt the reason you can tell it's compulsory is because it's not in the square brackets so the prompt is the instruction or the question that the user sees anger a Caesar please type in your name hit enter and then all I need to do is run the subroutine to make my input box appear and there it is you can see where the prompt is and if I type in my name when I click the ok button or hit enter the input box returns the information that has been typed in the problem we've got at the moment of course though is that the information doesn't get returned to anywhere it's actually effectively just gone as soon as I click that okay button so in a moment we're going to look at how we can store the information returned by an input box but first let's have a quick look at other things we can do to customize it you can customize an input box in a couple of Simbel ways let's have a look back at the parameter list if I click at the end of the line of code I've already written typing a comma that Reedus plays the parameter list and shows me that I'm on the title parameter and the title is what appears in the title bar of the input box let's have something similar like personal details if I run the subroutine again now we should see their personal details appears in the title instead of what was there before which was Microsoft Excel another simple thing we can do is change something called the default parameter I give myself a little bit more space in my screen width and type in a comma at the end of the current input box I see them on the default parameter now and this is what information appears in the info box before a user type something in so it might be I know the default name of a person or maybe something simple that helps the user understand what to do so maybe I'm going to enter enter name here and then finally if I run the input box one more time we'll see the enter name here appears as the default value for the info box so there we go a couple of simple ways that you can modify customizing info box now let's deal with the important part how you capture the results of an input box so I've reverted to a slightly more simple version of the info box I've taken away the default parameter what I'd like to do with this input box is when a user types in their name and clicks ok I would like the name of the user to be entered into cell a1 on this blank worksheet so to make that work what we need to do is at the start of this sentence or instruction say range a1 dot value equals input box please type in your name now there's one small additional problem we need to solve here as well if I try to move away from this line will see that the VBA editor indicates we have a syntax error and the reason this is happening is because my input box now needs to enclose these arguments in a set of parentheses it's a basic syntax rule in VBA that when you'll want to in fact anything was going to say just an input box but in fact when anything appears on the right-hand side of an assignment operator an equal sign you need to enclose the is all the arguments in a set of parentheses it's not one to remember but it's one of those things you just get used to is one of those general syntax rules of VBA so anyway now having done that if we run this subroutine we'll find that when I type in my name I can remember my name there is only gold and either hit enter or click ok we'll see when I switch back into excel the range a1 has been populated with the value that i typed in now what if you didn't want to store the result of your input box in a physical cell on the worksheet another convenient place to store input box results is in a variable which is essentially a space in memory to store information while your program runs now we've created a complete video on how variables work so I'm not going to go into too much detail here why I'm going to do is show you to do that I'm going to declare a variable I'm going to call your name and I'm going to store a data type called string so dim your name as string the reason I'm using string by the way is because if I look at the parameter list for my input box again it shows me that the input box returns its data as a string so it makes sense to match the data types so the return value of an input box is a string and that's going to get stored in a memory space which can also hold a string now all I need to do is instead of passing the information into it the value of cell a1 replace that with a reference to my variable called your name I'm not going to do anything more with that just for now all I'd like to do is show you when I step through the routine that this information gets stored in my variable a couple of simple ways I can do that if I use the View menu I can display something call the locals window and there's another trick that I can show you while we're stepping through our routine so I'm going to press the f8 key to begin stepping through the locals window shows me that the your name variable currently contains an empty string and I'll see that again if I hover the mouse over the word your name as I step through the routine however when I reach the stage where I can type in my name wasn't my own name again when I click OK I'm gonna turn to the VP editor the locals window shows me my name is stored in the variable and if I hover them is over the name of the variable that also shows me the same thing so that's the basics of how you get information into a variable using an info box so now if I ever want to refer back to the information that the user had typed in all I need to do is refer back to my variable rather than hunt for the cell in which I stored the information so let's do something really simple like display a message box on the screen I'm going to your message box which shows me a simple welcome message hello and your name so it's an incredibly simple basic system but if I run the whole thing and start to finish and I type in my name and click OK I'll end up seeing a welcome message using the value that I typed in earlier so far each time I've written my input box I've always clicked the OK button to return its value but what happens if you click the cancel button on an input box instead if I use the fa key to step through to display my own per box I can type in my name and the old and if I click the cancel button what you'll see when you return that to the VB editor if you look in the locals window you'll see that the variable contains an empty string if I hover the mouse over the variable name as well I'll see you know be Tring so essentially when you click the cancel button the input box ignores anything you've typed in and returns an empty string which means I'll see an empty message as well hello nothing same is true if I run this one again and type in my name Andy if instead of clicking cancel I clicked the cross here in the top right hand corner exactly the same thing happens so what that means is it really you should be testing the results of your input box to make sure that the user has actually passed something in and you can do that with a really simple if statement so just after my input box has appeared I'm going to say if your name equals an empty string which is double quotes in double quotes then I'm going to display a message box which says you didn't enter a name and I might display a VB exclamation symbol in there as well to warn the user finally I'll finish off the if-statement by adding an else clause and an ENDIF which means that if I did actually enter a name I will still see the same message that we saw earlier so if I run the subroutine again from start to finish type in my name click OK I'll see the correct message run it again type in my name and click cancel it's something I didn't enter a name so it's a simple very very simple way in fact to test whether your input box has returned something and because a user always has the opportunity to click cancel it's definitely something to consider doing so as one final and slightly more complex example of using ember boxes I've come back to my list of film from 2012 I'm going to write a simple system the less they user add a new film to the end of our list three input boxes one to ask for the film name one for the release date and one for the length and the values passed into those input boxes will all get stalling the correct parts of the spreadsheet so I've already been to start on the subroutine I've created a simple variable which can hold a string and use the number box to populate that variable then these two lines find the correct place on the spreadsheet and pass the value of the variable into a cell the next job is to ask the user for the film's release date so I'm going to declare a new variable which is what we called film date as a date and then after I've asked for the film's name I'm going to ask for film date I'm going to make that equal to an info box and I'm going to type in the phrase type in the release date before I close the parentheses here what I want to do is quickly point out and remind you with the fact that an input box can return a string I mentioned this before in the parameter list which an install this string in a variable which I've said can only store a date now in many programming languages this would actually cause a bit of an issue I could not return a string into a date variable because there would be something called a data type mismatch now VBA is quite forgiving when it comes to things like this as long as what I type into the input box can be converted into a date so as long as basically what I type in looks like a date then VBA will take care of the conversion for us automatically vba has a feature called implicit data type conversion now that's considered in the creative world of program that's considered quite sloppy or lazy programming but we can rely on this little fact to help us make our code a little bit more easy to type and so although it's technically cheating we're going to use a string returned by the info box install that in a date variable what I would then need to do is make sure that I can pass that information into a cell in the spreadsheet so active cell to offset 0 comma 1 so one column to the right dot value will be equal to film date I should also do the same thing for the film's length as well should nice lights up a new variable at the top called film length and that will store an integer which is a type of number a whole number I'm going to cheat a little bit here by copying and pasting my input box line and I say film length equals in box type in the length and again that input box will return a string but as long as what I type in can be converted into an integer VBA will do that for us in final lines right here for this first version of this routine is to again cheat slightly make the cell that is two columns to the right equal to the film length so there we go let's see if the system works I'm going to use the f8 key to step through to begin with so I'm going to use f8 start stepping through we can see the variables have their default values of a an empty string a zero date and a number 0 for the appropriate data types as I step through we can populate let's see I'm going to put gravity in still haven't seen it yet but I will eventually get around to that so the word gravity gets stored in that string variable now if I type in a sensible release date in the UK forever - gravity was released on the 8th of November 2013 so as long as what I type in here can be converted into a date we won't run into any problems where I would run into problems if I typed in something silly like I know and elephant I couldn't store the word elephant in a date variable so you do have to make sure that what you type in definitely is a date or at least can be converted into a date so ather November 2013 click OK and that works happily again if I do the same for a number if I type to type in some text here and try to store that into a number variable that will cause problems but as long as what I type in is a number and can be converted into that in terms of data types I'll be absolutely fine so the rest of this everything will just work as normal all the values will get stored in the correct place and if I switch back into Excel I'll see that my new film has been added to the list so there you go that's a quick actual bit of information for you about different data types with input boxes you can use it in the box to return data to variables which aren't just strings now although this can be quite convenient do to store the results of a number box in two different data types it can also lead to some other potential problems remember when we looked at cancelling an input box earlier on we saw that the input box returns an empty string now that's absolutely fine I begin stepping through this this is absolutely fine to return an empty string to a string variable however if I click cancel for the film date input box instead what's going to happen is I'm going to get a runtime error which shows me a data type mismatch and the reason for that is I can't store an empty string in a date variable so we're going to need to have some other solution if we want to be able to capture all or handle the fact that a user might cancel from entering dates and the same will be true for the number as well as a canceled entering the number it's going to end the submitting at this point then look at the solution now there are a couple of different solutions to this type of problem one way and probably the best way actually is to use something called error handling and we're going to cover that in a later video in much more detail what I'd like to show you here is rather than relying on implicit data type conversion let's show you how to use some Google explicit data type conversion so what this means is let's let's focus just on the film date variable rather than trying to store the result of input box immediately in the film they variable what we're going to do is have two different versions of that variable one which can hold a string which I'm going to call dim str film date as string and the other which I'm just going to slightly rename as dat film date as a date now obviously we've seen the problem whereby if we return a value from an input box that doesn't fit into a date variable we get the runtime error so just sort of solve that problem we're going to use our string version of that variable so that whatever gets returned from that input box it will always be a string and it can always always be stored in that variable because it can contain a string what we can do then is check if STR film date equals an empty string then just like we did earlier on when we were testing the result of that of the user's name if they left the the input box bangle they clicked cancel we could test if it was an empty string now if that's true what I would like to do is display a message to the user saying you didn't enter a valid date and then we'll just end the submitting at that point so quick and simple we'll just exit the sub okay so that's what will happen if the user did not enter a valid date the next thing we would need to do then assuming that we pass this test and we haven't exited the subroutine is we would want to make sure that the value stored currently in the string variable can be converted into a date variable so to do that we're going to make the date variable that film date equal to the converted version of this string variable and there's a whole family of conversion functions in VBA if you look for in the intellisense list the list of functions beginning with letter C there's a farrier there's a function that converts one datatype into into any other so there's one here called C bool which converts into boolean there's one here called C date and that's what I'm going to use convert into a date and there are several of the ones as well C string C integer and so on and so on you will find them all in this intelligence list but basically for any data type you can store in VBA there will be a conversion for it so we're going to use the one called C date and we're going to pass into that this string variable strname so this is explicit type conversion we're doing it deliberately and all that's a little bit complex I admit but it's the sort of thing you have to start considering if you're using different data types if we begin stepping through this routine now oops one more thing I hadn't done haven't renamed that final reference my film date variable I want to refer to the date version so the date version of my film variable will go into the cell on the spreadsheet so one more time let's try to step through the routine so I'm going to look for I'm going to type in an info box under the film name I'm going to put in gravity again just because I've got the details to hand and I'm going to press f8 again to set the release date 8 November 2013 when I click OK you'll hopefully see in the locals window that the string variable contains the string 8th of November 2013 it's enclosing double quotes indicating that is definitely a string now my statement finds that I did not enter an empty string so it doesn't exit the subroutine which means it reaches this stage where I'm going to try to convert the string into a date deliberately and when I hit f8 you'll see that's exactly what happens the rest of this everything then just works as normal by typing the number 90 and then carry on using FA to step through will find that the film has been added to the end of the list just very quickly watch what happens if I didn't enter a valid date so I I'll just go ahead five to run this one I'm gonna enter gravity click ok but this time I'm going to click cancel for the release date and this time instead of seeing a runtime error I get a nice sensible message or I can click OK and the subroutine just ends so it's a little bit more technical than it first appears isn't they're using in verb boxes particularly if you're trying to store values of different data types and this is just one technique for getting around that problem store the result of an input box always in a string and then you can try to convert that string into the valid data type afterwards so thanks for watching hope you found the video useful if you've enjoyed this training video you can find many more online training resources at ww-why Zeljko UK

Channel: WiseOwlTutorials

Views: 123,474

Rating: 4.9477806 out of 5

Keywords: Microsoft Excel (Software), Visual Basic For Applications, inputbox, variables, wise owl

Id: d07yuwGHZpo

Length: 20min 35sec (1235 seconds)

Published: Mon Jan 27 2014