VBA mathematical functions

Last Updated on January 11, 2023 by token

Description of math functions in Excel VBA

Below I present the names and descriptions of all standard math functions in Excel VBA. As you can see, functions like VBA RND don’t need arguments. Functions like VBA ROUND require 2 arguments – the value and the number of decimal places to which you will round your value. Numbers with decimal places in VBA are entered using a dot “.”, not a comma “,”.

VBA mathematical functions – an example of use

Time to test our math functions. Let’s create a simple program that enters the results of all mathematical functions into the cells of a spreadsheet.

Sub FunExample ()

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)

End Sub

Below the use of trigonometric functions:

Sub FunExample ()

Range ("A10"). Value = Sin (0)
Range ("A11"). Value = Cos (0)
Range ("A12"). Value = Tan (0)
Range ("A13"). Value = Atn (1)

End Sub

If you like to use VBA math functions in conjunction with worksheet functions, I invite you to the next parts of VBA course. You will learn how to use VBA to call any mathematical function from an Excel spreadsheet.

Tasks (You can enter the solution in the comment)

3.1 Convert the number 33,445 to an integer. Try using three different functions for this.

3.2 For the number -21, write the VBA code, in which you will calculate absolute value, square root, and round to 3 decimal places. Finally, multiply the number by 5.

3.3 For the ambitious: Given the tangent function, calculate the cotangent function of π / 4.