Excel VBA Introduction Part 11.2 - Application.InputBox

welcome to this vessel tutorial in this video we're going to cover how to use the application dots input box method in Excel VBA we'll start the video with a quick reminder of the basic generic input box function and explain some of its limitations then we'll show you how to avoid those limitations by using the application document instead we'll explain how you can set the return type of that input box and also how that type of input box automatically validates user input we saved an awful lot of time and effort doing that yourself towards latter half of the video we'll explain how you can get that type of input box to return different bits of information of all explain how you can return a formula how you can return a reference to a range of cells and finally for the most complex example how you can return an array of values and then loop over that array to do something useful with it so let's get started in a previous video we've looked at how you can use a basic input box to allow users to enter a value which your program can then use here's a quick reminder of how they work I've created a quick symbol subroutine which has three variables and I've let the user to populate those variables by entering values into input boxes so for example I can say film name equals and then call the input box function I can fill in the single compulsorily prada matter called prompt i'll tell the user that to enter a film name or a flim name as i've talked to they're filming and then I can use the value returned by the input box to populate the cell taps so I could say for instance range b2 dot end exhale down dot offset 1 comma 0 dot value equals film name so just to quickly run this everything to show you what it does it displays the in my box I can enter a film name when I click OK or hit enter that value will be entered into a cell there it is so that's the basic principle of how simple input boxes work now all of this basic type of input box is fairly simple to use it does have a number of limitations firstly the data type returned by this type of input box can only ever be a string so let's say next on the user to populate the film date variable I could say film date equals umber box and open some rackets and say enter a date for the prompt just before I close around black is can you see here at the end of the tooltip it says as a string remember that this input box function will always returns value as a string so we want to work out what will happen if we try to enter a value into that input box and try to populate a date variable just before I do that so let's make a quick copy of this line so we can populate the correct cell so I say range b2 and exhale down offset 0 comma 1 value equals film date so now I'm going to run the subroutine and I'll see a film name in the box I'll type in the same film name and gravity and then I can enter a date now as long as I is what I enter into this input box can be interpreted as a date VBA is quite forgiving about things like this although technically the input box will return that value as a string because I'm trying to store that strutting in a date variable VBA will automatically coerce it into a date datatype so VBA uses a technique called implicit data type conversion if the value of your returning is one datatype but it can be converted into another one automatically VBA will take care of that for us so if I click OK here that will work I look back at the spreadsheet I've got the film naming in again and I've got the film's release date in the correct cell as well the problem with this technique however is what happens if the value you enter into that input box cannot be interpreted as a date if I run the subroutine again I'm just gonna go for the same film name one more time and then type in something which clearly isn't a date and cannot possibly ever be converted into one if I click OK I simply get a runtime error so that's the first limitation of this basic type of input box there's no way to control the data side returned and that can lead to runtime errors the other main limitation of this type of info box is that if you want to enter a value into it you have to manually key it in you have no other choice so for example if I want to populate the film length variable let's say I want to populate film length by displaying another input box and this will be enter the length and again remembering that the input box will return a string I need to make sure that I type in a valid number in order for that number to be stored correctly in this integer variable then I can quickly copy this honor code again and paste it in and change the offset to 0 comma 2 to make that equal to film length okay so if i run the subroutine now and i enter the same film name again gravity and then i put in the same release date Strother the 12 2013 and then if i wanted to enter a number here the length of the film Walter I could already see in the background there was a number that I wanted to use again what would be really nice would be if I could click on that cell to read in that cells value but unfortunately I can't do that this type of input box is an example of what's called a modal dialog box so what that basically means is that while this input box is displayed on-screen I can't interact with the application in the background at all so I have to manually key in the value so that's the earth the second main limitation of this type of input box what we're going to move on to now is show you how to use a different type of input box one that's specific to Microsoft Excel which will solve all of these limitations and give us much more power when we ask users to enter values the other type of input box that you can use in Excel VBA is actually a method of the application object so to demonstrate how that one works let's create a quick new subletting called application info box and what we'll doing here to start with is something very similar to what we started with the previous example we'll have a variable which will store a film name as a string and I like to ask the user to populate out using the application info box so again I can simply say film name equals but this time rather than just saying input box I'm going to say application dot input box and yes believe it or not this does actually return a different type of function so if I open some round brackets you'll see a similar although not identical list of parameters we've still got a prompt which has to be a string so I could say enter a film name but can you see the reserved there is no specified returns either there's no as string at the end of the program is a list here the interesting view is there is an extra parameter here called type which we will come back to in a moment I'm going to leave the type parameter empty to begin with and if I do that then by default this input box will return a string of text so I can do that and then I'm just going to copy the line from the previous routine just to save a bit of time knock some lazy honest and then I'll run the solution again and it will essentially do exactly the same job the input box looks a little bit different you can see the the buttons are in a slightly different place but it essentially performs the same role as this in this basic example if I say gravity again why not let's be consistent and then click OK and we'll see that that's value gets entered into the next cell available just as though it was a standard basic input box now the thing which makes this kind of input box more interesting is altered it without final type parameter so just before we move on and start writing more code I want to quickly mention a little bit about how that type parameter works I'm going to use the object browser to demonstrate this so I'm going to go to the View menu and choose object browser or you can just press f2 on the keyboard as well just quickly first of all I'm going to look at the M the Global's class on the left hand side and scroll down a little bit on the right hand side to find the original input box function so once you've found it in the list there it is what I can do is show you down the bottom here it says input box and returns a string as we saw earlier and you can see that this is a member of the VBA object library so what that means is no matter which office application you're programming in you can always use this basic input box function now the other example we're looking at now the application input box is tucked away inside the application class so if I select application here this references the Excel application of course and if I scroll down the list on the right-hand side now I'll find another instance of the import box which is essentially a method of that class so if I select dabbing of a box you get the updated list of parameters and you can see this is clearly a member of Microsoft Excel so this style of input box can only be used in Excel VBA what I'd like to do is see a little bit more information so I'm going to right click on the word in the box and choose help to launch the help system so you can see in the list of parameters here it explains that the type parameter specifies the return data type of the input box and that's something that we can't do with the basic info box it also mentions that if you miss out this argument then it returns text or a string and that's exactly as we've just used that input box in our small example a little bit further down the page it shows you that the the return the values for the type parameter are essentially numbers and the number dictates what kind of information you will get back from nimber box so if you set the type parameter to be equal to zero you'll get a formula you'll be set it to one you'll get a number and so on and so on unfortunately there's no specific value for returning a date so we can't use this type of info box to help us with entering a date correctly what we can do is use it to help us enter a number correctly so for the next example what we'll do is use an input box to return the film length but we'll make sure that that input box can only accept a number so let's close down the help system and close down the object browser to get a zip back to the module and we'll create one that's copy the film length variable to start with and then we'll create the in the box that allows the user to specify what that film like this so to display an input box which only allows us to enter a number what we're going to do is save film length equals application dot input box and then open some round brackets and we need to pass in a value to the prompt parameter and also to the type parameter so let's say the prompt is enter a length or enter the length and then to get to the type parameter I've got two choices I can either type in a stupid number of Commerce until I actually get to that parameter which looks horrible or better is we can use something called named parameters so what that means is simply putting the name of the parameter before its value so for instance the for the prompt parameter I'd say prompt colon equals enter the length then to move on to the type parameter I can type in another comma there at the end and then enter the word type colon equals let me just scroll the screen across so we can see this in one single screen width so the type if you remember the type to enter a number was a value one no closer parenthesis now that will display an in the box which only allows us to enter a number so having done that all we need to do is populate the correct cell so let's cheat again no it's not cheating I keep on telling myself it's not cheating just a copying page from the previous routine and then that's ability now she allow us to enter a number into the input box so let's give it one quick test to make sure that it works as intended if we do sensible things so I'm going to enter the word gravity here again and then I'm going to the length as I'll just type in the number this time click OK and this should just work as normal so just as though the previous simple input boxes were being used what makes this really interesting though is what happens if you try to enter a value into their symbol box which isn't a number so let's try to do that I'm going to run this ability again by pressing f5 I'll enter the same Ville name I'm nothing if not consistent click OK and I'm going to try to type in something which clearly isn't a number when I click OK here's the beautiful thing this type of info box has built-in validation so if you've told this dialog box that it can only return a number and you put something which isn't a number into it it shows you a simple validation message box so I can't possibly move on until I enter a valid number now which is just absolutely wonderful with a simple input boxes you would have to code all of this validation yourself so that's what makes that's what makes this type remember box so absolutely brilliant the other slightly less obvious thing that makes these type of info boxes so useful is there non-modal so I mentioned previously that when you display the basic info box you can't interact with Excel in the background well with this kind of info box you can so if I run this subroutine one more time rather than typing in the film name and typing in the number this time what I'm simply going to do is click on a cell which contains the value I want to enter so I click on cell for this is B 16 it reads in a reference to that cell into the input box if I click OK then I can do the same thing for entering the length and I can click on a number here let's click on the Valor and that's it come the 93 ok so he's in the reference cell D 18 when I click OK and then return to the spreadsheet you'll see that those references to cells have been converted into the values of those cells 1 being a string and the other being a number and those values have been answered which is so much more convenient than having to type everything in yourself so those are the two main advantages of using the application in the Box the fact that there first of all validated for the correct data type and secondly that you don't have to always manually key in a value you can select cells to feed in the contents into the input box directly now I mentioned earlier that the type parameter doesn't allow us to set a data type as a date which is a bit of a problem for us if we want to enter a date so one way to sort of get around this is to rely on the fact that all dates in Excel are actually just fancy formats for numbers basically so what we could do here is if I copy their film dates variable from the previous reason and paste that in again and then I'm going to say film date equals and I'm just going to copy this line here which sets the type role the return type of this input box to be a number I'm going to change that prompt say enter a date and I might put in a quick bit of information to help user with what formats going to say DD /mm / YY YY so that sort of helps to explain to a user what value to type in or how to type in the date so again I'm just going to copy the line from the previous routine which will populate the correct cell and then that's from the subroutine to see what happens so if I entered a film name again I'm going to enter gravity click OK or hit enter I'm gonna click on a cell reference assignment here to read in the film's runtime rather than type it in myself because I'm feeling a bit lazy and then for entering good dates here's the great thing although technically we're entering a number even if we type in a date in a specific format as long as it can be interpreted as a date Excel will recognize that as a number and enter it in the correct way so I click OK at this point and then return back to Excel you'll see that the values gone in and formatted as a date again that's been cast implicitly into the correct data type because we're storing it in the the date or a variable with a date data type so really the fact that it comes out formatted as a date is all down to the dates that we've used here now the great thing about using this application in the box is that again if we type in something which is not recognized as a valid date if I run the subroutine again I'm going to cook them a cell to populate the year the name and I'm going to click on another cell to populate the length if I type in something which clearly is not a date either a piece of text or even something which looks like it should be a date but actually isn't let's say for example oh I know the 30th of of February 2013 which is not a valid date if I click OK I'll get the same validation for the number in the box so it helps you although you can't change this message unfortunately at least you get some sensible validation that prevents you from continuing until you enter the correct value so let's change this back again to 12/12 2013 and click OK and now everything works perfectly again so we've seen some of the basic data types that this info box can return essentially strings and numbers what I'd like to do now is move on to some of the more interesting things you can get it to a turn I'm going to start out I can get how to return a formula so just before you start writing the code let's have a quick look at how that would work a quick way to get to the help page for the input box is to actually just click on that word and press the f1 key on your keyboard and that'll launch the context-sensitive help system which takes you directly to the correct place so again looking at the type of parameter what we need to do is tell it to return a formula and to do that we need to set the type parameter to be equal to zero a little bit further down it says that if the type is zero the input box returns a formula in the form of text so although we're returning a formula we need to be able to store it in a string variable so what we can do is close down the help system and that's beginning new subroutine I'm going to call it simply enter a formula give you a nice and simple and then we'll declare a variable in here called dim my formula as a string what we can then do is say my formula equals input box sorry application down info box and then opens in parentheses and the prompt will be enter a formula and then the type of course will have to be 0 so type colon equals 0 all right so that allows us to build a formula in the input box what we need to do now is work out what we do with the formula once it's been created all I'd like to do is put the formula into a cell on the worksheet so that's just up a quick look at Excel and pick a cell what we're going to enter the formula I'm going to pick cell g2 why not so if I go back to the the VB editor what I can do there is say range G to dot and there are a couple of different properties that I could use here I could just say dot value equals and then my formula and what that would do is put the contents of the formula as a string of text into that cell if you have a quick read of the input box help page however Microsoft recommend not using the value property they recommend using the formula local property instead and that's take into account differences in the language settings of the the computer this the coast being run on so instead of using value we can use formula local instead as as what's recommended so I can simply change value to the formula local property okay so having done all of that I'm just going to run the subroutine and what I'm going to do to begin with is write a really simple calculation that is going to say d3 plus D for I could carry on I could add more plus symbols and I can build just a complete calculation just as you would as though you were entering this manually into a cell in Excel but if I click OK at this point and then have a quick look back at spreadsheet I'll find that that cell has displace the value which displays the answer basically of the the cell but it also contains the formula which means that if I change any values here then of course the contents of the other result the formula will change automatically just as it would had you enter that manually in Excel so that's the basics of entering a formula into a member box you can use the type property or type parameter set that to be equal to zero and then store the result of that in the Box in a cell we can make this example a bit more complicated by asking the user to enter a function rather than just a simple formula so let's say we wanted the user to enter a sum function which will total up some of the film run time in minutes what we could do is help the user out by starting the sum function for them in the input box and to do that we can use the default parameter so if I head to the end of the type parameter and type in a comma what I can do is look for the default parameter and I'm going to put in default eat colon equals and then open a set of double quotes and say equals sum open brackets before you then close the double quotes so the default parameters what's in the umber box before the use type something in so when we run the subroutine now we will see that the input box gets populated with the start of the sum function now it's important that you click after there's some function there to make sure that you don't just overwrite what was in the info box then all I'm going to do is get the user to select a bunch of cells so I can click and drag on cells and it will read in the full range that I've asked for I haven't quite finished entering this function X I haven't finished adding the closed round bracket but the great thing about this input box again we saw it was validated for when you're entering numbers it's also validated when you're entering a formula if I click OK here the input box will recognize that I haven't finished entering the formula yet so if I click OK I can finish that off by closing the round brackets and then click OK and if I switch back to excel quickly to see the results there we go there's the sum function entered into that cell and of course if any of these values change the result in cell g2 will update as well we could extend this example yet again by asking the user which cell they want their formula to end up in and we can use the input box to actually return a reference to a range object if I click on the word in the box and press f1 just so you can see in the help system again if I use the offset the type parameter equal to eight it tells me that you can return a cell reference as a range object so if I close that to help system down what I'm going to do is to clear another variable I'm going to say dim formula cell as a range so this allows me to store a reference to arrange object what I can do then is after I've asked the user to build the formula itself I can say set formula cell I've got to use the keyword set here because a range is an object not just a symbol data type so we've created videos on both simple data type variables and object variables which are part of this series as well so if you're not confident with how these work those videos are certainly worth the watch but if you're confident what we're going to do on say set formula cell equals application dots input box open parentheses let's have the prompt set to sorry prompt colon equals choose formula cell followed by a comma and we need to set the type colon equals to number 8 so that returns a reference to the range that the user selects what we can then do is say rather than range G 2 dot formula local what we can do is replace range G 2 with a reference to our formula cell so I say formula cell followed by . just so you can see the intellisense will still appear gives you the full list of all the properties and methods of a range object so having done that let's from the subroutine one more time and I'm gonna build the sum function again I'm going to select a smaller range of cells this time close round brackets and then I'm going to click OK now I'm going to be asked to choose the cell that I want my formula to currency so I can click on a cell in the background g5 click OK and hopefully we should see if I switch back to excel but the result of the formula has gone into the cell that I selected earlier so that's how you can get the but box or the application in the box to return a reference to a range object set the type of parameter to be equal to 8 when you use the application input box to return a reference to a range it doesn't just have to be a single cell it can also be a block of cells so what we'll do is write another quick subroutine that allows the user to choose a range of cells to copy and then chooses another cell that they can paste the data into so we're going to say sub copy data and it's gonna have two variables dim copy range as a range and also dim destination range as a range will ask the user first of all what block of cells they want to copy in the first place we'll do that by saying set copy range equals application dots input box open some brackets and the prompt is going to be asking the user to choose cells to copy and again we need to set the type parameter to be equal to the number eight that allows it to return a reference to a range once we've done that we'll ask the user where they want to copy the cells to and we'll do that in a similar way we'll say set destination range equals application dot input box where the prompt will be something like choose destination let's say choose destination cell so I'm going to just pick a single cell and then we'll set the type parameter again to be equal to the number eight all we've got to do then is to apply the correct method to the correct object so we're going to say copy range dot copy and then there's an optional parameter of the copy method in excel that allows you to specify where the cells we copied to so I'm going to put the destination I'll set the destination equal to the destination range so simple as that if I run the Sibley seen what I can do now is pick a block of cells a copy so let's just pick a few of these cells here click OK now I have to choose where to start pasting the data into or where to copy it to so I could pick for instance another cell on sheet 2 perhaps let's pick a random cell and sheet 2 and click OK when I do that their cells we copied it just to quickly check back into Excel what we should see is on sheet 2 a copy of that range of data so if there's again how you can use the the application in the box to allow users to select a range of cells now there's one more return type of this application number box that I want to cover in this video there are a couple of other types I haven't mentioned yet if I click on the word input box and press f1 to bring up the help system I haven't mentioned the number four which is a logical value or a boolean value which is essentially true or false and that's fairly simple to work with I haven't been to the number 16 either which returns L error values so if your cell formerly contained errors like hash n/a this number 16 can get your input box to return those as well what I wanted to focus on here in this final value video is the number 64 which returns an array of values now we've created a separate video all about arrays which is part of this series of this tutorial tutorial series so I'm not going to mention explicitly everything to do with arrays this is going to be the most complex example in the whole video and if you're not comfortable using a race then feel free to stop watching at this point but what I'm going to do is an example is I'm going to get the user to pick a range of cells which you're going to contain numbers so I'm going these to pick a range of cells that contain the film lengths in minutes and those values will be stored in an array which I can then loop over and perform some sort of calculation so all I'm going to do is convert the length in minutes to the length in hours by performing some kind of calculation then I'm going to take the content of the array and spit them out somewhere maybe in another column I'll probably get the user to ask where they want to start returning the results to and again this is going to involve the use of using the application info box to select the original range of cells and also the destination cells so let's get started in the VBA editor by adding another new subroutine so I'm going to say it's sub I'm going to call it return array to start with I'm going to declare the array which is going to hold the list of initial values that the user selects so I'm going to say dim I'm going to call it film lengths then open and close some round brackets that's what dictator this variable will be an array tour will be able to contain more than one value and then I'm going to set the data type to be as a variant so it's an undetermined data type essentially you can hold any data type at all now this is an example of singleton and I mentioned array so we haven't specified how many elements it will hold or how many dimensions it has and again if you don't understand those terms and the video on arrays explains all this in huge amounts of horrific detail what I can then do is say film lengths equals application dot input box open parenthesis and the prompt will be something like choose lengths to convert and the type is going to be equal to the number 64 so that allows us to store an array of values in that array just to quickly show you how this works I'm going to step through the subroutine at this point and while I do that I'd also like to be able to see the locals window so the locals window is going to display the contents of that array as we step through the subroutine so if I use the f8 key to begin stepping through I'll get to the point where I can choose my list of cells so I'm going to select the full list of Valdis there from d3 down to D 15 when I click OK in the locals window what I can see is that I've got my film lengths array populated with a list of values 1 2 13 that represents the number of rows effectively that I've selected so we saw a selected essentially 13 cells and one to one that's the number of columns that I've selected essentially so I only started a single column if I expand that in the in the locals window you can see all the individual compartments that are part of that array and if I expand one of those you'll see finally the value that was stored in it so you can see that the first value that are stored in the first element of that array is the number 143 I switch back to excel you can see that that's the value in that specific cell so that's the VB editor what I want to do is just close the locals window for now and reset the subroutine now we need to be able to loop over that list of values in the array to perform some kind of calculation to loop over the array I'm going to need some kind of counter variable so I'm going to do is declare a simple loop counter variable I'm going to call it loop counter as a long so this is a long integer so a very long whole number basically again this technique is described in much more detail in the video on arrays but once I've stored the array I can say for loop counter equals now if I knew that the array was always going to contain 13 Mathews and they were numbered from 1 to 13 I could simply say 1 to 13 but I don't know that the array will always hold that exact number so what I'm going to do instead is use a couple of functions to find out the lowest number of the array and the highest number of the array so to find the lowest number I can use the l bound function and the L bound function will requires me to reference an array so I can say film lengths and I also need to say which dimension am I looking in so my film lengths array contains two dimensions essentially they're the number of rows and the number of columns and the number of rows with the first dimension so I need to specify that that I want to find the lowest number of the first dimension of the film lengths array well I can then do is say 2 and essentially do exactly the same thing but using the you bound function so that finds the highest number I'm going to say you bound film lengths and again refer to the first dimension hit enter a few times and I'm going to close my loop by saying next loop counter and that section of code there will successfully loop through the entire set of value stored in that array now what I need to do is perform some kind of calculation on each of the values in the array so in order to do that I need to reference the correct element each time I want to perform a calculation to do that I've got to say film lengths let's imagine I wanted to reference the first element of the entire array to do that open a set of round brackets and say 1 comma 1 so that's the first row and the first column now I don't know that I'm always going to be referencing the number 1 because I'm going to loop through the entire column of values so we're using the number 1 there I'm going to replace that with a call to my loop counter variable so because that will increase each time I go through the loop that will make sure I process the entire column I've only got one column in the array so I can get away with always just using the number 1 there and because I've only search as a single column of cells what I want to do now is make that equal to another value so I'm going to change it to be equal to a calculated value and the calculation is quite long and complex we're going to start with is by and working out how many whole hours there are in that film length AB so if I got 143 minutes for the first film the total number of whole hours will be to to calculate that I'm going to function called int which converts the result of the the calculation into an integer or a whole number so it will ignore any decimal places and then the calculation I'm going to perform is to take this value as it is and simply divide it by 60 so that will return the whole number of hours what I'd like to do then is add the word hours to the end of that so I'm going to use the ampersand to concatenate a piece of text and I'm going to add the word hours in a set of double quotes the next thing I want to do is join on the result of the or the remaining minutes so if the film is 143 minutes I wanted to say 2 hours 23 minutes so again I'm going to concatenate another value to the end of that in fact I'm going to use the space underscore continuation character to carry on typing this instruction on the next line I wasn't going to run out of space so the the next part is to concatenate the result of another formula and that's going to be again the value of the film lengths for the element that I'm looking at so I'm going to um pace that again and then I'm going to use the the operator called mod mod it's short for modulus and what that does it returns the remainder of a division so if I say mod 60 that would return the remaining minutes so for the first film that will be the number 23 finally I'm going to use another ampersand to concatenate on the word minutes to the end and that will be the full calculation so having done all that let me just quickly step through this every team while we can see the locals when you against I'll display the locals window I'm going to use the FA key to begin stepping through I'm going to start a range of cells click OK and then let's have a look at the values that are in this array so you can see clearly to begin we've got the number hundred forty-three in the first element as I begin stepping through you'll see that this number here changes to the result of the calculation two hours and 23 minutes and as I go into the next one that will change the next value to two hours 45 minutes and so on and so on so the carry on stepping through each of these values will change to their correct piece of information so when we've got the values calculated all I need to do now if I stop the subroutine and closely meet the locals we know all I need to do now is to spit the results of the array into the correct cells to determine where the results will go we'll ask the user to choose a cell so just like we did earlier on we're going to use a variable I'm going to pull this one a result range as a range and then after we finish looping over our array we're going to ask the user to set the result range by using a an input box so set result range equals application dot input box open some brackets now the prompt will be something like choose where to put results oops put results and then the type of course will have to be equal to the number 8 so that it's a range of cells so ideally what we're going to get the user to do is select a single cell let's say for example they've chosen this range of cells to convert and they choose this cell to put in the results we actually need to refer to the full range of cells the results we'll go into we can't get away with just referencing a single cell so back in the VBA editor what we're going to do is reset the result range after the user selected a single cell we're going to say set result range I can smell that properly result range equal to another range of cells which begins at the result range so cell 1 is going to be the result range cell 2 is going to be from the result range offset a number of rows down and a number of columns across now the number of rows down that I want to offset from the result range imagine the user selected cell f3 and they originally selected that entire column themselves to to convert in the first place so that's 13 cells altogether we saw that we could use the U bound function to calculate the number of elements in the array so if we say you pound of our array that will allow us to offset 13 rows down from there now if we go 13 rows down that actually takes is one row past the end of the list so we're going to go essentially just subtract 1 from the result of the new bound function to return the correct range of cells that our answers go into so back into the VB editor we're going to offset this number of rows down so I can copy and paste that minus 1 followed by a comma and the number of columns I want to offset will be 0 and then close around brackets for the offset function and close an extra set of round brackets for the original range reference so that will successfully in fact if I can bring this on two separate lines I can use the continuation cavities as space underscore and bring this on to another line so you can actually read the whole thing in one one screen with and I'll do the same for the the previous line as well let's do it like this so you can actually see the complete set of code so that will set the result range properly all we have to do now is populate it and doing that is really simple we simply say result range equals film lengths and that will spit out the contents of the entire array into that range of cells having gone to all this effort we should make sure that we actually run this ability and to see if it works so let's run it and pick a range of cells I'm going to pick the full list of films this time will full of serve run time in minutes and click OK then I'm going to choose to start putting the results in at cell f3 so if I click OK at that point more should happen if I switch back to excel is that that full list of cells gets populated with the answers of our conversion function just to run it again just show that it will work with a different range of cells if I run it again I'm going to choose just a small range of cells this time click OK I'm going to put the results in from I know cell g17 as it turns out that's I've clicked on click OK look at the results back again in Excel and I'll see just a converted values for the range small range of cells that I selected in a different output range so hopefully they appreciate that this was intended as a complex example for people who are familiar with or work with arrays already and again as I said we've got enough video that explains everything to do with arrays but that was part of this video tutorial series even if you don't use the array with certain type hopefully you're still think that the application input box is much more useful than just the original basic input box that you've seen previously so that's how it works hope you found the video useful if you've enjoyed this training video you can find many more online training resources at ww-why Salvio UK

Channel: WiseOwlTutorials

Views: 118,509

Rating: 4.9436622 out of 5

Keywords: Microsoft Excel (Software), Visual Basic For Applications, inputbox, application.inputbox, wiseowl

Id: NvGv1n4TW28

Length: 40min 12sec (2412 seconds)

Published: Wed Mar 26 2014