In this video we're gonna discuss the mathematical capabilities of VBA.
And as far as math is concerned VBA is just as capable as excel if not more so rather than having huge
formulas written into the formula bar and excel.
We can isolate the steps of large mathematical equations and use variables to break out the data in
a way that makes sense intuitively.
So I've outlined some of the main mathematical functions you'll use in VBA and you can see you can do
the typical math functions like addition subtraction division and multiplication in VBA.
Those are pretty straightforward just like you would do in Excel.
You can simply use the plus side minus slash or asterisk to represent the function that you want to
perform.
So in this example column we can see what we would type in Visual Basic and in representation we can
see how we would normally write that function on paper.
And so these match up pretty closely for our main functions here other functions aren't quite as intuitive.
So when we start moving to exponents we start to see that.
So first we can have a general exponent just using the carrot here like you do an Excel.
That's pretty straightforward as well.
But by using exponential is what you might do in scientific fields or in engineering you'll actually
be typing E X P and identifying the number as shown here.
So e XP 1 is represented as this on paper.
Same with the square root function.
You'll type Eskew R with your number and this would be the result of what that would look like.
Absolute Value again very similar.
You take a B S for absolute identifier number and this is what that representation would look like.
So again this example column is what we type in VBA and this is what that would actually represent on
paper we can also adjust numbers by rounding them or truncating the decimals entirely.
So starting with round we would first list the full number with all of the decimals and then tell the
system how many decimals we want to round two.
So in this example we're trying to round the number five point seven eight nine and we only want one
decimal when we're done rounding it integer and fixed do similar things.
They truncate the decimal from the number.
The difference between the two is that integer will always push positive numbers towards zero so a positive
two point four would become two but it pushes negative numbers away from zero so the negative two point
four would become negative three fix.
On the other hand always tends towards zero regardless of whether the number is positive or negative
and we'll see an example of this in a second with this negative two point four that we're running both
into and fixed on.
We can also randomly generate a number between 0 and 1 by using this random function which is already
open parentheses close parentheses.
Since this only generates between 0 and 1 we have to do some math using this random function if we want
to get a random number between our own lower and upper bound say between the numbers 1 and 6.
And I've written this out for you over here so you'll start by subtracting your lowest possible number
from your upper number so you'll want to replace this upper number with whatever your upper bound is
and the lower number with whatever your lower bound is then you'll add 1.
Multiply that by your randomly generated number that's between 0 and 1 and you'll add back in your lower
number as the last step.
And that will randomly generate a number between your upper and your lower bounds and you can break
this formula down step by step in VBA to get a better idea of how it works.
The last thing that we regularly do with VBA is to convert text strings into actual numbers that we
can perform math with.
If you have a string of texts that you try to perform calculations with VBA can occasionally error out
or miscalculate so it's best practice to perform mathematical functions on actual numbers.
So data that has been stored as a number so we can use this c int function to convert the text numbers
say we have a text string that is the number four and we can actually convert that into actual data.
Number four.
So generally speaking VBA can do everything Excel can do and that includes basic trigonometry like sine
cosine tangent and pi and basic financial or statistical functions.
So just as an example we'll go ahead and we'll take a look at each of these functions in VBA.
But know that VBA is capable of doing any sort of math that Excel is capable of doing as well.
So I'll go ahead and open this up and we can see here.
I've pre written this math manipulator code so I'm going to have my screen like so and zoom out a little
bit on this window and I'll give you a second to pause and copy this if you want to run it yourself.
Otherwise I'll talk through this line by line and we'll give it a test run to see if our results end
up as expected so we'll give you a second to pause that should have been enough time to pause.
Now I'm going to briefly talk through each of these lines one by one.
So starting with this line here all this is saying is that I want sell in row three and column six.
So in this case if I come back to my worksheet here we're gonna go down to row three and come over to
column six.
In this case F three.
So we know this should probably represent our additions here which is just five plus four.
There's nothing particularly fancy about that.
The next is subtraction.
Again nothing fancy about this division.
Again nothing fancy about this multiplication.
Nothing fancy exponent nothing fancy.
Here's where we start to get into the a little bit more complicated section.
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
1 means the absolute value of negative 1.
And again if you actually are in these parentheses and you hit space these functions will tell you what
their input should be.
So for these three you're just inputting a singular number that you want to run the function on.
Now if around if I come back into my parentheses and hit space you can see we have two inputs this time.
The first input is the number and this is the number that we're trying to round.
And the second input is the number of digits after the decimal.
And so in this case we've put one which means we want one digit after the decimal moving down into INT.
This is what we were talking about earlier where ints and fix are both very similar but fixed will always
tend towards zero whereas ints will tend away from zero when it's a negative number.
And so what that means in this case when we run this we should expect that the result of fix here will
be negative 2 because it tends towards zero but the results of our int function here should be negative
3 because it should tend away from zero.
Moving down again I've created a variable here and I've told VBA This is a string type variable so I've
set it as a string the number four.
And normally if we have all of these string variables and we're trying to do math and perform math on
these string variables I can cause some problems in our code later on.
So it's best practice to go ahead and use the CND function here whenever we reference this particular
string.
So instead of being a string of the number four so surrounded in quotes like it is here it will be a
regular number for like we see anybody else anywhere else in our math here and it should just be four
plus 2 which again nothing fancy.
So we should see that performed correctly.
Now I'll walk through this line by line and I'll do that by using either debug step into or just using
the f a shortcut on your keyboard.
So make sure you're clicked into the subroutine and go ahead and press F eight so we can see we're about
to start running this sub.
The first thing we should see is the addition result which is nine so that checks out with what we would
expect second would be our subtraction negative 6 which is what we would expect the next will be our
division our multiplication our exponent now our exponential one our square root of nine our absolute
value of negative 1 and now we're getting into our rounding here so we should see this five point seven
eight nine rounded to one decimal place so that checks out we should see are negative two point four
truncated the decimal.
But in this case trending away from zero senescence into and it's a negative number so we see that results
in negative 3 for fix.
We should see it 10 towards zero since fixed always tends towards zero so we should see negative 2 and
in this particular cell here we should see just a random number between 0 and 1 so that checks out now
at the end here we've created this variable see we that is the number 4.
But as a string and not as an actual integer so we're going to convert that to an integer here and perform
some simple math and that checks out.
So f eight one more time to end your code.
So in this video we talked about the main mathematical functions that you might use in VBA.
And we gave examples of some of those functions.
So now we should be able to perform more complex math work within VBA itself.
Just keep in mind that VBA can do things like sine cosine tangent.
It does recognize the value of pi.
It can do basic financial basic statistical math like you would in Excel usually.
So anything you can do in Excel you can convert over to visual basic and a good way to do that would
be to go ahead and record a macro or use the object library to find the matching function.