Excel VBA - Declaring Variables

hi and welcome to another Brett weeks comm excel screencast in this tutorial I'm going to be introducing the concept of declaring variables in your macros variables are a named storage location within your computer's memory so we can use variables to take that next step beyond the macro recorder and into a bit of true programming practice if you're watching this video on YouTube and you'd like to see the complete code from this workbook in the infobox below click the link head over to Broadway's comm and there you'll see a link to download the workbook for this tutorial so to introduce the concept of variables we're just going to create a real simple macro what I've done is I've created a dynamic range under the data heading and I want to create a macro that's going to tell me how many data points are in that range and the sum of all the data points in that range here over here you'll see a link to a website that's this is oz grid comm if you want to check out variable data type definitions I recommend this page it's got all of them listed and described you can see what kind of data types that you can use in Excel and what are the most common and best to use so let's head over to the visual basic editor and start writing our code we're going to call this sub some range and our description is to count the data points and sum the data points in range our dynamic range which is called data in worksheet one so we have a dynamic range named data we want to count the data points and then sum them usually we declare variables at the start of a macro just to make it easier to read and easier to understand so the way to declare a variable in Excel VBA is with the dim keyword which is short for dimension and if the first variable we want to declare is going to be called RNG and we're going to declare it as an object in VBA so we can declare variables in VBA as datatypes objects and a couple other things that are a bit out of the scope of this lesson so once we type the as keyword Excel will bring up an automatic list of all the different variables that we can use for this variable we want it to be a range we want to declare this variable as a range which is an object in Excel the next variable we want to declare is another range named CLL and we also wanted to claim a worksheet variable so we want to declare WS as worksheet next we want to declare datatype variable name data sum this is going to be the sum of all the numbers in our range and we'll declare that as an integer datatype we'll also declare a data points variable also is integer and this is going to be the number of data points in our range finally we're going to declare an msg variable as a string we're going to use this variable to create the string we want to put into the message box to tell us what the sum of the the range was and how many data points there were and also we're going to queer going to declare a constant so a constant is just what it sounds like it's going to stay the same it's going to be a value that does not change within the macro so the declare constant it's constable in this case it's going to be the title and then we're going to declare it a string and then we're going to set the variable equal to and this is just going to go and go and range a 1 this is going to be the title of our worksheet it's going to be Brett week's comm or slash Excel screencast so there's our constant so we've declared our variables now we're going to we're going to quickly write our code so we're going to set the object variables and now to assign a value to an object variable we have to use the set keyword so we're going to set and then the first variable we're going to set is our worksheet variable so we're going to set WS equal to this workbook the worksheets and then sheet one so now that variable equals this statement so next we're going to set our range variable and this is going to be we're going to use our variable that we just created as the worksheet and then the range and then our dynamic range is name data next we're going to insert the worksheet title into cell a1 and this is going to be our constant so we again with worksheet we're going to use the width end construct so with our worksheet that we've declared and the range 1 we're going change the value to our constant the name of our constant we're going to change the font to bold and we're going to change the size of the font to 20 and then we're going to end our with statement next we're going to create the loop that's going to sum the values in the range so we're going to create a for each loop so for each we're going to use our cell variable that is defined as the range object so this is going to say for each cell in our defined range which is our dynamic range the variable data sum equals data sum plus cell dot value and then we're going to move through each cell in our dynamic range and at the end the data sum variable is going to hold the total sum of all of the cells in that range so next we're going to set the data points variable and notice when we have a data type variable it's different from setting this variable then to set an object variable we don't need the set keyword we can just go ahead and put data points equals and in this case we're going to equal we're going to make it equal a worksheet function we're just going to use a function the count a function see I've spelled that wrong use the count a function and and the values we want to count are the cells in our dynamic range so WS range data next we're going to build our message to put into our message box that says how many data points there are and what is the sum of our dynamic range so we're going to set our message variable and remember this is a string variable so we're going to set that variable equal to and we're going to do a little bit of formatting with this message so that when we put it in the message box it's going to look a little bit nicer than what Excel will do on default which usually doesn't turn out looking that nice data points we're going to put a VB tab that's going to tab that's just going to tab over in our message box and then we want to put the get variable data points the variable data points does not go in quote quotations it has to be on its own so Excel can pick up the value because we don't want to put the word data points into our message box we want the value the data points variable is holding so we're going to continue to build our message message and this vbc our LF is just going to be insert a line break into our message box so some data points we're going to put another tab and then I forgot to throw out an end symbol here so we'll continue and then finally it's our message so far and another tab again just for formatting and then our data some variable finally we are going to display the message box and the prompt of the message box what we want the message box the body of the message what to say is our string variable msg and then we can give it we can give the header a title put some of that arrange and there we have our macro let's go back and take it for a test drive what we should have happen is when we push this some data range button which have already placed on the worksheet and assigned to our some range macro is we should get a message box that has the count of the data points in this dynamic range and also sums the data points so we have one two eight and seven so should we should get a sum of 18 and we should have our title inserted in cell a1 so I'm going to push the macro button we're going to run it we can see the message box comes up it's nicely formatted we have the number of data points and we have the sum of the data points if we add two to here click it again now we have number of data points is five and sum of data points is twenty which is just what we want it looks like it's working properly we go back to the VBA editor I just want to show you one more thing when we start running this macro you'll notice after I run past my variable declaration statements we can see that Excel has initialized these variables and what I mean by initialize these variables is Excel initializes each variable based on the data type or the object we assigned to it and set it to its default value so an object an object variable in Excel it's default value is equal to nothing so all of our object variables are equal to nothing when they first initialize because we haven't set them to anything our data variables if it's a numeric variable it gets set to zero is default and again zero is default and as a string it gets set to an empty set and our constant you'll know has already been assigned the value that we wanted to assign it so that's why when we were looping here and we started with our data sum plus the cell value we knew that when we start out our data set our data sum variable is going to equal zero so we can build upon it and create the sum of the range so that's a basic introduction to variables in VBA thank you for watching this screencast on brettly's comm have a great day

Channel: ExcelScreencasts

Views: 40,791

Rating: 4.5555553 out of 5

Keywords: Excel VBA, Declaring Variables, Visual Basic for Applications, Microsoft Excel, software

Id: 32Xun0bv48I

Length: 11min 16sec (676 seconds)

Published: Fri Oct 07 2011