37. VBA - Functions and Subroutines (Programming In Microsoft Access 2013) 🎓

hello again everyone and welcome back to programming in Access 2013 my name is Steve Bishop and today we're going to be continuing our section on VBA or Visual Basic for applications more specifically today we're going to talk about methods that you can have in your code which come in the form of functions and subroutines we've talked about functions and subroutines before but today we're going to get a little bit more in depth into discussing what functions and subroutines can do for us because as we've talked about before functions are code which is grouped together and then return a value back to the user now they don't have to return a value they can return a value but usually functions you want to use a function to do some sort of calculation or perform some sort of action and then return back something back to other parts of your code okay and then of course functions group our code together so that it's easier for us to manage and it makes our code reusable so that we can call it repeatedly subroutines as we've talked about before is also code which is grouped together but it does not return a value subroutines do not return a value functions can return a value and that's primarily the biggest difference between functions and subroutines subroutines also group our bits of code together so that it's easier for us to manage and of course makes our code reusable so it can be called repeatedly and also subroutines are the basically the default of what access creates in the background to handle any of our events when we click on a button or select a combo box drop down or update a value in a text box things like that so when we when we want to create some sort of event handler then that event handler is going to die it's going to be by default created as a subroutine in access all right so let's go ahead and hop out into our database here and we've got a form which has txt value 1 tht value 2 and then we've got four different mathematical operation buttons on here we got addition subtraction multiplication and divide if we look behind the code for our divide button you'll see it looks very very similar to the code that we've wrote and that we've written before that we wrote before okay and that is we're going to take the two values and we're going to divide them and then we're going to splay the message to the message to the user in the form of a message box if there's any sort of error we're going to jump down here to this error handler which is going to take the error number and based upon the error number is going to either display divided cannot divide by zero both values must be numeric or if neither of these two are the error numbers then by default we have this case else which will if if it's neither of these two numbers then it's just going to say could not perform the function please try again all right let's just see that in action let's see what happens when we use the divide we'll do four divided by two of course that's two four divided by zero cannot divide by zero 4 divided by Steve both values must be numeric and if I just leave this blank then we get could not perform function please try again okay now that's great and what are we going to do about these other three buttons because what we can do normally you might think well let's just going back here and we'll just basically copy and paste all that code and put it in here and we will maneuver it a little bit what kind of change it up a little bit we need to change the operator here to a plus sign instead of the divide that we had down below and we also don't have any chance of this cannot divide by zero so let's just go ahead and drop it out of our select case error handler but other than that this function here this the subroutine is identical this code block is pretty much identical to what we've done here in the division and then of course I've gone in and I've said okay here's my multiplication with the multiplication sign and here's my subtraction with the subtraction sign but I've got a lot of duplicate code here I've got a lot of things that are happening repeatedly over and over again wouldn't it be nice if we could just put this all together into one and then call that one that one subroutine and have it perform the operation okay so let's let's see what that would look like I'm going to take my division here I'm going to go ahead and copy this and I'm going to create a new sub new sub routine we do that by using either the we're going to start with a private keyword private sub and I'm just going to call this sub math and then I'm just going to paste the code that we want to perform inside of the math subroutine now I can take all this code here and I can replace it with just the word math okay go ahead and indent this a little bit make it a little nicer place that with math and replace this with meth and lastly we'll handle the ad okay so now rather than having this code be redundant Lee typed out we just simply have one single call in each one of these button click handlers to go and point to the math subroutine that we created down here which has the one bit of code that we need to run so let's just kind of go ahead and set a breakpoint on the divide so that you can step through this here 4/2 it's going to drop into the same subroutine that we created before but now when I step through the code you'll see when it hits this math this call for the math subroutine it's going to go ahead and drop down here to the private sub math that we created and it's going to do our operation for us display to the user we didn't have an error so it's not going to go through the error handling and it's going to jump back up to the end of our subroutine where you know it's going to continue on back up here after the math call that we did which just puts us at the end of our subroutine the bTW and div underscore click subroutine so it's going to put us at the end of that and that's the end of our code so it's done executing all right so that's great but we have one little thing that we need to start worrying about because four plus two is not to force I guess that is two but you can see we're still doing a division for each one of those buttons so what we need to do is we need to be able to tell our little subroutine here some way to change this operator okay we need to give some way of saying I want this operator to be something else okay the way we can do that is I'm going to outside of these subroutines I'm going to dim a variable called operator okay oh and I need to say as let's just for the sake of making it simpler to understand we're going to we're going to label the string and now what we can do is before we make our math call we're going to set this variable to something that we can then use later on down here to determine which one of these operators we want to do we'll see how that works here I'm going to show you operator equals let's do a plus for add do operator equals divide I'm just going to go ahead and copy this here this is going to be multiplied and this is going to be subtract now since this is being stored as a string I can't just go in here and say operator okay because this would just be a string and you'll see we get an expected end of statement there's something wrong here because operator is a string and it recognizes that this is a variable this is not an actual arithmetic operator to be doing so what we need to do here is we need to build another select case statement and use operator as our first variable - to do this select case against so I'm going to also end our select it's always a good idea that when you start a code block you also put the ending tag at the end - so do case is equal to plus sign and then we'll do just copy and paste this here for different operations so plus minus multiply and divide oops divide there we go okay so now what's going to happen is we're going to do a select case on that operator variable and notice that we're setting it up here on our button click events to set the operator variable to basically tell the Select case which of the operators is being selected so let's now we can take this message box here and I'll go ahead and cut this paste it into the divide change this to multiply change this one to subtract and change our addition to addition now that is going to now flow through our case select here our select case op an operator and based upon what we've set up above here in our operator variable it's going to now come down to the Select case on figure out which which one of these operations needs to happen and then if we run into any errors during any of this we should jump down to our problem code block here and we should be able to pick out which error is happening all right so this looks like it's going to work let's go ahead and run our application - / - that's good - multiply by four is eight that's great four minus two is two that's great an addition is 42 all right I tripped you all up here you guys all thought this is going to be perfect right now why do we get 42 here you may be wondering well that's because access has actually concatenated the four and the two together with the addition sign and if I go back into my code here and this is going to trip you guys up you need to make sure that if value1 is a string and value two is a string then VBA turns the addition sign into a concatenation sign okay so you need to be aware of this the way you have to handle this is you have to make sure that value one and value to our numbers they must be numeric and the way you can do that is you can use a simple little function here I'm going to change these I'm going to convert these values into doubles okay I'm going to make sure that no matter what somebody types up in those text boxes we are converting it into a double okay so it'd be the same thing as if I was saying you know dim value 1 as double and then I was taking value 1 and setting it to me dot txt value 1 okay this is just kind of a shorter way of basically doing that and returning it as a double value so we don't need to have this dim and setting the values like that okay so now we're implicitly we're saying you need to convert this text box this string into a double okay now when we do our math 2 plus 4 is 6 and we're good to go all right so that is how we can use a subroutine to our advantage but what about functions is there something we can do here that might be more useful with a function and there is okay what we can do is remember a function is going to return a value so what I can do is I can actually make a function private function calculation okay and calculation and then we're going to return it as a double and what we're doing here is we're going to say essentially this calculation is going to have a variable called calculation defined as a double automatically for us in our function code okay so when we create a private function called calculation here and then we say as double what's going to happen is we get this defined variable automatically by access which is going to be called whatever the function name is and of the type of whatever we say in the as double so I don't need to type in the dim calculation as double I can leave that out now what we can do is we can take all of this and we can actually do something really kind of cool I'm going to take all of this code cut it out of our subroutine paste it in here and instead of saying message box and have each one of these things return back I'm going to take the and I'm going to say message box and calculation and that's all we have to have in our subroutine and now what I can do is I can change this message box instead of displaying the message box to our user I'm going to actually just define that calculation variable in each one of these statements so now what's happening here is that our math subroutine is going to call calculation within our funk within our application here it's going to call the calculation function which is going to come down here and now what's going to happen is it's going to go through our operator check to find out which one of these operations needs to be performed and then it's going to assign the value returned from this operation into our calculation variable that is automatically created in VBA and is assigned to this calculation function so now what's going to happen is it's going to I'm going to go ahead and set a breakpoint on the division here and we'll just step through this and you'll see how this works 4 divided by 2 okay I'm going to change your operator to division then it's going to make the call to our math subroutine our math subroutine is just another call which is going to display a message box of the value returned from our calculation function so when we do that oh I have to change this from exit sub I apologize I made a goof here I need to change this to function because remember this is no longer a subroutine it is a function so we need to exit our function so it caught me on that all right now we're good to go now we've come across our case is a division so we set the variable calculation to meet exp value 1 divided by me txt value 2 and our select exits the function and now we return back a value of 2 because what happened is this is going to return back to the message box whatever the value whatever the variable that was returned back called calculation its return back and message box displays the value returned by our calculation and that pretty cool so that's how you can use subroutines to group your functions together and make them slimmer functions do essentially the same thing as subroutines but now we can return a specific value back and the value that's going to be returned we can is already created as a variable in the function and so we can just assign that variable whatever the value is that we want to return back and that's how that works all right I hope you guys have found this enjoyable and understand and you guys can fully understand this please feel free to send me any messages with requests for other videos you'd like to see or if you have any questions about videos that I've already done

Channel: Programming Made EZ

Views: 71,349

Rating: 4.934732 out of 5

Keywords: Microsoft, Access, 2013, Programming, VBA, Visual Basic, Applications, tutorial, lesson, guide, database, SQL, beginner, advanced, software, microsoft access, table, query, form, code, coding, development, visual basic for applications, computer programming, Subroutine

Id: Zx0itIyaDuk

Length: 18min 27sec (1107 seconds)

Published: Sat Feb 15 2014