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 “,”.

Function	Description
ABS ()	Returns the absolute value of a number
EXP ()	The function returns an exponential value. For example, EXP (1) will return an Euler number ~ 2.71828182845905
FIX ()	Returns an integer result. Rounds up for negative numbers.
INT ()	Like FIX () returns an integer result. Rounds down for negative numbers.
LOG()	Returns the logarithm of the argument
RND ()	Returns a random number in the range <0; 1>
ROUND ()	Rounds a number to x decimal places
SQR ()	The root of the argument
SGN ()	Returns the sign of a number, so 1, -1, or 0
SIN ()	Trigonometric sine function
COS ()	Cosine trigonometric function
TAN ()	Trigonometric tangent function
ATN ()	Arcustangent trigonometric function

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

Excel VBA course - Math VBA functions — Excel VBA course – Math VBA functions

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.