1 00:00:00,670 --> 00:00:05,360 In this video we're gonna discuss the mathematical capabilities of VBA. 2 00:00:05,470 --> 00:00:12,190 And as far as math is concerned VBA is just as capable as excel if not more so rather than having huge 3 00:00:12,190 --> 00:00:14,950 formulas written into the formula bar and excel. 4 00:00:15,070 --> 00:00:20,710 We can isolate the steps of large mathematical equations and use variables to break out the data in 5 00:00:20,710 --> 00:00:23,000 a way that makes sense intuitively. 6 00:00:23,020 --> 00:00:27,910 So I've outlined some of the main mathematical functions you'll use in VBA and you can see you can do 7 00:00:27,910 --> 00:00:32,670 the typical math functions like addition subtraction division and multiplication in VBA. 8 00:00:32,690 --> 00:00:35,590 Those are pretty straightforward just like you would do in Excel. 9 00:00:35,590 --> 00:00:41,080 You can simply use the plus side minus slash or asterisk to represent the function that you want to 10 00:00:41,080 --> 00:00:42,110 perform. 11 00:00:42,130 --> 00:00:47,770 So in this example column we can see what we would type in Visual Basic and in representation we can 12 00:00:47,770 --> 00:00:51,220 see how we would normally write that function on paper. 13 00:00:51,220 --> 00:00:58,970 And so these match up pretty closely for our main functions here other functions aren't quite as intuitive. 14 00:00:59,000 --> 00:01:02,120 So when we start moving to exponents we start to see that. 15 00:01:02,180 --> 00:01:06,590 So first we can have a general exponent just using the carrot here like you do an Excel. 16 00:01:06,620 --> 00:01:08,450 That's pretty straightforward as well. 17 00:01:08,450 --> 00:01:13,880 But by using exponential is what you might do in scientific fields or in engineering you'll actually 18 00:01:13,880 --> 00:01:18,530 be typing E X P and identifying the number as shown here. 19 00:01:18,530 --> 00:01:23,440 So e XP 1 is represented as this on paper. 20 00:01:23,450 --> 00:01:25,290 Same with the square root function. 21 00:01:25,390 --> 00:01:30,830 You'll type Eskew R with your number and this would be the result of what that would look like. 22 00:01:32,320 --> 00:01:35,020 Absolute Value again very similar. 23 00:01:35,020 --> 00:01:40,480 You take a B S for absolute identifier number and this is what that representation would look like. 24 00:01:40,480 --> 00:01:45,970 So again this example column is what we type in VBA and this is what that would actually represent on 25 00:01:45,970 --> 00:01:53,050 paper we can also adjust numbers by rounding them or truncating the decimals entirely. 26 00:01:53,080 --> 00:01:57,850 So starting with round we would first list the full number with all of the decimals and then tell the 27 00:01:57,850 --> 00:02:00,500 system how many decimals we want to round two. 28 00:02:00,520 --> 00:02:06,610 So in this example we're trying to round the number five point seven eight nine and we only want one 29 00:02:06,610 --> 00:02:11,920 decimal when we're done rounding it integer and fixed do similar things. 30 00:02:12,040 --> 00:02:14,410 They truncate the decimal from the number. 31 00:02:14,500 --> 00:02:20,800 The difference between the two is that integer will always push positive numbers towards zero so a positive 32 00:02:20,800 --> 00:02:27,220 two point four would become two but it pushes negative numbers away from zero so the negative two point 33 00:02:27,220 --> 00:02:30,360 four would become negative three fix. 34 00:02:30,370 --> 00:02:36,730 On the other hand always tends towards zero regardless of whether the number is positive or negative 35 00:02:36,760 --> 00:02:41,260 and we'll see an example of this in a second with this negative two point four that we're running both 36 00:02:41,350 --> 00:02:43,950 into and fixed on. 37 00:02:43,960 --> 00:02:50,260 We can also randomly generate a number between 0 and 1 by using this random function which is already 38 00:02:50,560 --> 00:02:52,590 open parentheses close parentheses. 39 00:02:52,870 --> 00:02:58,870 Since this only generates between 0 and 1 we have to do some math using this random function if we want 40 00:02:58,870 --> 00:03:04,710 to get a random number between our own lower and upper bound say between the numbers 1 and 6. 41 00:03:04,720 --> 00:03:10,330 And I've written this out for you over here so you'll start by subtracting your lowest possible number 42 00:03:10,330 --> 00:03:15,040 from your upper number so you'll want to replace this upper number with whatever your upper bound is 43 00:03:15,070 --> 00:03:19,540 and the lower number with whatever your lower bound is then you'll add 1. 44 00:03:19,540 --> 00:03:26,110 Multiply that by your randomly generated number that's between 0 and 1 and you'll add back in your lower 45 00:03:26,110 --> 00:03:27,720 number as the last step. 46 00:03:27,730 --> 00:03:32,560 And that will randomly generate a number between your upper and your lower bounds and you can break 47 00:03:32,560 --> 00:03:38,110 this formula down step by step in VBA to get a better idea of how it works. 48 00:03:38,140 --> 00:03:44,290 The last thing that we regularly do with VBA is to convert text strings into actual numbers that we 49 00:03:44,290 --> 00:03:46,090 can perform math with. 50 00:03:46,090 --> 00:03:51,670 If you have a string of texts that you try to perform calculations with VBA can occasionally error out 51 00:03:51,730 --> 00:03:57,100 or miscalculate so it's best practice to perform mathematical functions on actual numbers. 52 00:03:57,160 --> 00:04:04,500 So data that has been stored as a number so we can use this c int function to convert the text numbers 53 00:04:04,500 --> 00:04:11,290 say we have a text string that is the number four and we can actually convert that into actual data. 54 00:04:11,290 --> 00:04:13,090 Number four. 55 00:04:13,090 --> 00:04:19,270 So generally speaking VBA can do everything Excel can do and that includes basic trigonometry like sine 56 00:04:19,300 --> 00:04:24,710 cosine tangent and pi and basic financial or statistical functions. 57 00:04:24,760 --> 00:04:30,470 So just as an example we'll go ahead and we'll take a look at each of these functions in VBA. 58 00:04:30,580 --> 00:04:36,700 But know that VBA is capable of doing any sort of math that Excel is capable of doing as well. 59 00:04:36,820 --> 00:04:39,780 So I'll go ahead and open this up and we can see here. 60 00:04:39,790 --> 00:04:47,490 I've pre written this math manipulator code so I'm going to have my screen like so and zoom out a little 61 00:04:47,490 --> 00:04:53,100 bit on this window and I'll give you a second to pause and copy this if you want to run it yourself. 62 00:04:53,100 --> 00:04:58,920 Otherwise I'll talk through this line by line and we'll give it a test run to see if our results end 63 00:04:58,920 --> 00:05:04,600 up as expected so we'll give you a second to pause that should have been enough time to pause. 64 00:05:04,610 --> 00:05:07,980 Now I'm going to briefly talk through each of these lines one by one. 65 00:05:08,180 --> 00:05:14,870 So starting with this line here all this is saying is that I want sell in row three and column six. 66 00:05:14,900 --> 00:05:19,910 So in this case if I come back to my worksheet here we're gonna go down to row three and come over to 67 00:05:19,910 --> 00:05:21,040 column six. 68 00:05:21,050 --> 00:05:22,960 In this case F three. 69 00:05:23,150 --> 00:05:27,410 So we know this should probably represent our additions here which is just five plus four. 70 00:05:27,410 --> 00:05:29,780 There's nothing particularly fancy about that. 71 00:05:29,960 --> 00:05:31,750 The next is subtraction. 72 00:05:31,760 --> 00:05:34,700 Again nothing fancy about this division. 73 00:05:34,730 --> 00:05:38,750 Again nothing fancy about this multiplication. 74 00:05:38,810 --> 00:05:41,970 Nothing fancy exponent nothing fancy. 75 00:05:42,350 --> 00:05:45,820 Here's where we start to get into the a little bit more complicated section. 76 00:05:45,830 --> 00:05:54,980 So XP 1 should be our exponential of 1 hour askew are or should be our square root of 9 a B.S. of negative 77 00:05:54,980 --> 00:05:58,190 1 means the absolute value of negative 1. 78 00:05:58,210 --> 00:06:03,590 And again if you actually are in these parentheses and you hit space these functions will tell you what 79 00:06:03,590 --> 00:06:04,490 their input should be. 80 00:06:04,490 --> 00:06:09,400 So for these three you're just inputting a singular number that you want to run the function on. 81 00:06:09,450 --> 00:06:15,700 Now if around if I come back into my parentheses and hit space you can see we have two inputs this time. 82 00:06:15,710 --> 00:06:19,950 The first input is the number and this is the number that we're trying to round. 83 00:06:19,950 --> 00:06:23,550 And the second input is the number of digits after the decimal. 84 00:06:23,550 --> 00:06:30,930 And so in this case we've put one which means we want one digit after the decimal moving down into INT. 85 00:06:30,950 --> 00:06:37,160 This is what we were talking about earlier where ints and fix are both very similar but fixed will always 86 00:06:37,160 --> 00:06:43,090 tend towards zero whereas ints will tend away from zero when it's a negative number. 87 00:06:43,100 --> 00:06:48,680 And so what that means in this case when we run this we should expect that the result of fix here will 88 00:06:48,680 --> 00:06:54,500 be negative 2 because it tends towards zero but the results of our int function here should be negative 89 00:06:54,500 --> 00:06:58,450 3 because it should tend away from zero. 90 00:06:58,460 --> 00:07:05,360 Moving down again I've created a variable here and I've told VBA This is a string type variable so I've 91 00:07:05,360 --> 00:07:08,210 set it as a string the number four. 92 00:07:08,270 --> 00:07:13,940 And normally if we have all of these string variables and we're trying to do math and perform math on 93 00:07:13,940 --> 00:07:18,380 these string variables I can cause some problems in our code later on. 94 00:07:18,380 --> 00:07:24,080 So it's best practice to go ahead and use the CND function here whenever we reference this particular 95 00:07:24,080 --> 00:07:24,840 string. 96 00:07:24,890 --> 00:07:30,440 So instead of being a string of the number four so surrounded in quotes like it is here it will be a 97 00:07:30,440 --> 00:07:36,200 regular number for like we see anybody else anywhere else in our math here and it should just be four 98 00:07:36,200 --> 00:07:39,290 plus 2 which again nothing fancy. 99 00:07:39,290 --> 00:07:42,320 So we should see that performed correctly. 100 00:07:42,380 --> 00:07:48,440 Now I'll walk through this line by line and I'll do that by using either debug step into or just using 101 00:07:48,440 --> 00:07:50,450 the f a shortcut on your keyboard. 102 00:07:50,530 --> 00:07:57,230 So make sure you're clicked into the subroutine and go ahead and press F eight so we can see we're about 103 00:07:57,230 --> 00:07:59,180 to start running this sub. 104 00:07:59,180 --> 00:08:05,600 The first thing we should see is the addition result which is nine so that checks out with what we would 105 00:08:05,600 --> 00:08:12,020 expect second would be our subtraction negative 6 which is what we would expect the next will be our 106 00:08:12,020 --> 00:08:23,740 division our multiplication our exponent now our exponential one our square root of nine our absolute 107 00:08:23,740 --> 00:08:30,890 value of negative 1 and now we're getting into our rounding here so we should see this five point seven 108 00:08:30,920 --> 00:08:38,180 eight nine rounded to one decimal place so that checks out we should see are negative two point four 109 00:08:38,240 --> 00:08:39,720 truncated the decimal. 110 00:08:40,010 --> 00:08:46,980 But in this case trending away from zero senescence into and it's a negative number so we see that results 111 00:08:47,010 --> 00:08:49,040 in negative 3 for fix. 112 00:08:49,050 --> 00:08:56,030 We should see it 10 towards zero since fixed always tends towards zero so we should see negative 2 and 113 00:08:56,030 --> 00:09:03,930 in this particular cell here we should see just a random number between 0 and 1 so that checks out now 114 00:09:03,930 --> 00:09:08,580 at the end here we've created this variable see we that is the number 4. 115 00:09:08,580 --> 00:09:15,480 But as a string and not as an actual integer so we're going to convert that to an integer here and perform 116 00:09:15,480 --> 00:09:18,710 some simple math and that checks out. 117 00:09:18,790 --> 00:09:20,800 So f eight one more time to end your code. 118 00:09:21,400 --> 00:09:25,810 So in this video we talked about the main mathematical functions that you might use in VBA. 119 00:09:25,840 --> 00:09:28,710 And we gave examples of some of those functions. 120 00:09:28,720 --> 00:09:33,370 So now we should be able to perform more complex math work within VBA itself. 121 00:09:33,370 --> 00:09:38,250 Just keep in mind that VBA can do things like sine cosine tangent. 122 00:09:38,350 --> 00:09:40,910 It does recognize the value of pi. 123 00:09:40,930 --> 00:09:46,690 It can do basic financial basic statistical math like you would in Excel usually. 124 00:09:46,690 --> 00:09:52,000 So anything you can do in Excel you can convert over to visual basic and a good way to do that would 125 00:09:52,000 --> 00:09:58,200 be to go ahead and record a macro or use the object library to find the matching function.