Description of mathematical functions in Excel VBA
Below are the names and descriptions of all standard math functions in Excel VBA. As you can see functions such as RND () do not require arguments. Functions such as ROUND () require 2 arguments – the value and the number of decimal places to which we round our value. Numbers with decimal places in VBA are introduced usingdots “.”, not a comma, as it does in the Excel spreadsheet.
VBA mathematical functions – an example of use
It’s time to test our functions. Let’s create a simple program, introducing to the cells of our sheet the results of all mathematical functions.
Range("A1").Value = Abs(-1.651)
Range("A2").Value = Exp(1)
Range("A3").Value = Fix(4.98)
Range("A4").Value = Int(4.84)
Range("A5").Value = Log(4)
Range("A6").Value = Rnd(123.123)
Range("A7").Value = Round(123.123, 1)
Range("A8").Value = Sqr(4)
Range("A9").Value = Sgn(-123)
Below, the use of trigonometric functions:
Range("A10").Value = Sin(0)
Range("A11").Value = Cos(0)
Range("A12").Value = Tan(0)
Range("A13").Value = Atn(1)
If you would like to use the VBA math functions in conjunction with the worksheet functions, I invite you to the next part of our VBA training. You will learn how to use any VBA function from the Excel spreadsheet using VBA.
Tasks (You can enter the solution in a comment)
- Replace the number 33.445 with an integer. Try to use three different functions for this purpose.
- For the number -21, perform the following actions in the VBA code, which you will display in the form of the MsgBox window: absolute value calculation, square root, and rounding to 3 decimal places. At the end, multiply the number by 5.
- For ambitious people: With the tangent function at their disposal, calculate the cotangent function with π / 4.