 Hello everyone and welcome back to another video. In this video we're going to be focusing on using numbers in Excel VBA. So it's going to be a simple look at the various calculations we're able to do and we'll also step into looking at the different numeric data types available to us when we're trying to store numbers. And I sort of jumped ahead there but we'll also be looking in obviously how you can store numbers and do calculations from that. So what we'll use is we're going to use a message box just to display the result of these initial calculations. So if you're not familiar with using message boxes then I strongly suggest you check out the previous videos in this series which we'll go over that in a bit more detail so you can understand how it works. But ultimately we're just using the message box to display this result so you'll just see a pop-up that comes on the screen just to demonstrate the output we're getting from our calculation. So we're just going to do MSG box and so the first one we're going to look at is if we want to do an addition. So an addition is simply just going to be using the addition sign you're probably familiar with on your keyboard. So if you wanted to do say 5 plus 5 all you need to do is 5 plus a symbol and a 5. And I did do spaces there but as you'll see if I just did 5 plus 5 like so and hit enter our code automatically corrects it or VV does and this is actually the correct format you need. You need the space between the number, the symbol and then obviously the following number. If we just to do F5 you can see that we get our result of 10. We're not limited just to the two of those we can actually add more here so we could do 5 plus 5 plus 5 plus symbol in the right place 5 and then obviously the result of that is going to be different so we're now got 25. So that's great. Alternatively you don't always want to do add every single 5 that you want you might want to do a multiplication so what is 5 times 4. In order to do that a lot easier and again apologies if it is going over some basic math that everyone's familiar with but hopefully it just gives you insight on obviously how you do these basic calculations within VBA. So to do 5 multiplied by 4 we're just going to hold the shift key down and the push number 8 to get this an asterisk symbol and then another space and we want to do 4 to get our same answer of 20 so 5 times 4. If we hit F5 just to run the code you can see we now got our correct answer there of 20 in our pop-up box or our message box. Another one or going into the third option is you might want to do a subtraction so let's do simply 5 minus 4 so 5 minus 4, go F5 we can see is the answer of 1 and the last one we have is a division so let's go a bigger number here so let's say 10 divided by 2 let's keep it all very nice and simple and push F5 and you can see we get the result of 5 so that's how you can create some really basic calculations in Excel and obviously you can make this as complicated as you need so if we had maybe 1548 divided by 4 obviously something a bit harder to do in your head there you go it's obviously going to spit out the correct answer for us there. So this is how we can work with basic numbers and obviously this is just using basic calculation in a pop-up box there. To take this bit further when we're actually working with our code we're unlikely going to want to keep typing in these numbers for various parts of the code we actually want to store them as values or variables so that we can call upon them when required. To do that all we need to do is set our number of variables so the first one could be number 1 equals let's say 5 number 2 equals say 5 again and now all we need to do to get our calculation is we can simply do message box and we can just do number 1 times by number 2 and then when we hit F5 you can see we get the result of 25 and obviously as soon as we've changed any of these numbers so let's go to 10 by 10 hit F5 again you can see obviously it's going to update and do that calculation for us. Alternatively we can then change this symbol as desired for any of the other symbols to get again the same desired output so obviously this time what 10 plus 10 is now going to give us the answer of 20. As we did cover in one of the previous videos we're actually able to set numbers and text. We can just leave setting our variables as we have here so you can simply say number 1 equals 10 and VBA is automatically going to look at that and identify that as being a number. The problem with this is when it comes to having a large number of variables that you're trying to store or obviously excessive amount of code because the variable storage function is going to be a lot larger than actually the potential variable that you need. For argument's sake we have many options available to us but for instance we have one variable type called byte and byte is the ability to store a number between 0 and 255 so obviously that only uses up one byte of storage so let's just go over that again. There's a variable type called byte it uses only one byte of memory and it's able to store values of 0 to 255. So for us with our number 10 here we could store this value with only using one byte of memory by assigning it to that byte variable. By not doing that and keeping it as a variable format we're actually going to be using a lot more of memory to store that potentially up to 4 or 5 times more and as you can probably imagine with many numbers that slight increase is obviously going to have a big impact on both the memory and the time for VBA to do these calculations. So what we'll do is just go over a couple of the different variable types and I'll also bring them up on the screen just so you've got them as that reference point as well. The key ones we're going to be using is both integer and long. So these are for like whole numbers. So an integer will be able to store values from minus 32,768 all the way through to 32,767. So an integer is going to probably be one of the ones you use the most. And for us we know that 10 is obviously in that range. So a good practice for us would be to say dim where we're going to be able to set now what the variable type is. So dim number one as integer. Oh, you'll have to spell it right, as integer. And you can see that is now going to store that value as an integer. So we're only going to be using two bytes of memory for that number one value rather than the four. Next one we can do is go dim number two as integer and I can spell it over again. So we've now stored both of our values correctly as integers. So let's move that up and just tie this all up. Just so it looks all nice and pretty to have that in. So you can see what information we've got there to slightly update this and obviously to sort of save some lines as well. Other good practice is rather than writing this out twice because we know both of our numbers are integers. We can simply get rid of that row, do a comma and go dim number two as integer. And that does exactly the same thing. So it's just going to define both of these as integers on the same row there. So we don't need to do that dim obviously twice. If we were wanting to do a bigger number then obviously plus 32,767 or minus 32,000 then we'd need to be looking at an option called long. And a good way to demonstrate that is if we go into number one and we try and store this as a large value. So let's say 32,768 plus that number and try to run this. You can see that we get this overflow error. And the reason we get that overflow error is because the number one of this value of 32,768 has overflowed or it's gone over the capacity of an integer value. So if we end that to come out of it and this time change this to long and now we go rerun that code, you can see we get our desired value. So one thing just to remember when you're working with numbers is to understand obviously the range of the numbers you're going to be using. And obviously that can be quite a hard task to do because if you're going to be doing a calculation to derive at that number it's just an extra thought you need to think. What is your range of numbers going to be and what's the appropriate variable to set to that number. And I appreciate we're getting a bit far ahead of ourselves now because we're trying to go with the basic concepts. I just wanted to touch on this briefly now just so we can revisit down the future. Obviously it's something that you're kind of familiar with. So let's go back to integer and stick with those values into integer. There we go. And this has put this back to the number 10. So another thing we need to be aware of and what we can actually do here is we will also now store our answer as a variable. So let's just go in here and just say, okay, answer as integer. Then we can come down here and we can go answer equals 10 plus 10. And then rather than doing our calculation in the message box here let's just simply put answer. Okay. So when we now run that there's going to be no visible difference in our result. It's still going to give us the answer of 20. But let's say now that we want to do a division and let's take the number, let's say do 10 and we're going to divide this by the number 4. So obviously 10 divided by 4. And let's do F5 and see what happens. So we get the answer of 2. So for those that are aware, obviously 10 divided by 4 isn't exactly 2. It would be 2.5. And the reason that we're getting the whole number of 2 is because we've asked the VVA to store our answer as an integer. So an integer is a whole number. So if there's any decimal places, they are going to be missed. Well, not missed, but they're not going to be included. So we're only going to take that whole value. So if we actually want to have a decimal place we now need to store that as a different variable type. So for us, we're going to now need to use double. So for integer, we are only going to be allowed to store or keep a whole number. And if we want to use a decimal or include decimal places, then we'll need to use the variable type of double. So all we need to do there is if you're going to answer and change this to double. And now we get for our answer and do run that code. You can see we've now been able to pass and keep that 2.5. So another one we need to be aware of there. And I'll just try and do another example to see if we can capture that again. So let's say we want to do answer is number 1. And it's going to be plus number 2. There we go. Okay, let's just quickly run this to make sure it's working. Yeah, we can see it's still working because 10 plus 10 is 20. But let's say we want to add to this 4.5. So our answer is obviously stored as a double. So let's see if this now works. So we can see that, again, our output is technically incorrect. We've asked the calculation to do 10 plus 4.5. And even though our answer has been given the variable type as double, we haven't assigned this to our number 2. It's still an integer. So what's going to happen is when this process of storing our numbers happens, number 2 is going to look at our 4.5. It's going to disregard the 0.5 and just store the value of 4. So when it comes to actually doing our calculation, it's actually doing 10 plus 4, which gives us the answer of 14. So ultimate failsafe in our scenario here, where we're playing around with decimal places and whole numbers, is if you go through double and then you want to answer one more time with F5, you can now see we get our desired output of 14.5. So covered quite a bit there and hopefully that wasn't causing any confusion at all. So the best thing I suppose to suggest is for you to have a play around with those numbers, just getting used to obviously working with your whole numbers, your integers, and doing your various calculations, and also playing around with the double variable so that you're able to pass the decimal places in terms of your values to feed in and the answer you'd like to have come out. If you have any questions with that at all, please just drop a comment below this video and I will get back to you as soon as I can. And if you enjoyed this video, please do give the video a like. It's not only greatly appreciated by myself, but obviously it helps that all important YouTube algorithm. And lastly, if it's your first time coming across the channel or you have seen our videos before, please do give the channel a follow and make sure you hit that bell notification button as well. That way you'll be notified of all of our future videos, including these VBA videos in our VBA series. So thank you very much for watching and we'll see you in the next video.