Math functions in VBA

This page gives an overview of the VBA Math functions with example code showing how they are used. A link is provided for each function to its documentation on microsoft learn VBA site.

Code VBA IntelliSense makes these function accessible under submenu VBA, see screenshot. intellisense menu with Math functions.

Hovering a menu you see an explanation what a procedure does, and what the inserted code will look like, depending on changes you make in the Function Call Builder dialog.

Abs - Absolute value

The Abs function Absolute value

From the Code VBA toolbar, select VBA » Math » Abs » 


  dblAbs = Abs(50.3)  ' Returns 50.3.'
dblAbs = Abs(-50.3) ' Returns 50.3.'

Sqr - Square root

The Sqr function returns a Double specifying the square root of a number.


dblSqr = Sqr(Number:=4)  ' Returns 2.'
dblSqr = Sqr(Number:=23)  ' Returns 4.79583152331272.'
dblSqr = Sqr(Number:=0)  ' Returns 0.'
dblSqr = Sqr(Number:=-4)  ' Generates a run-time error.'

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/sqr-function

Raise to a power

The Exp function returns a Double specifying e (the base of natural logarithms) raised to a power.


dblExp = Exp(Number:=1.3)
' Define angle in radians.'
dblAngle = 1.3  
' Calculate hyperbolic sine.'
dblHSin = (Exp(dblAngle) - Exp(-1 * dblAngle)) / 2

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/exp-function

Log - Natural logarithm

The Log function returns a Double specifying the natural logarithm of a number.


Static Function Log10(X)
  Log10 = Log(X) / Log(10#)
End Function

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/log-function

Rnd - Pseudo-random number

The Rnd function returns a Single containing a pseudo-random number.


sngRnd = Int((6 * Rnd) + 1)  ' Generate random value between 1 and 6.

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/rnd-function

Sin - Sine of an angle

The Sin function returns a Double specifying the sine of an angle.


dblAngle = 1.3  ' Define angle in radians.'
dblCosecant = 1 / Sin(dblAngle)  ' Calculate cosecant.'

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/sin-function

Cos - Cosine of an angle

The Cos function returns a Double specifying the cosine of an angle.


dblAngle = 1.3  ' Define angle in radians.'
dblSecant = 1 / Cos(dblAngle)  ' Calculate secant.'

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/cos-function

Tan - Tangent of an angle

The Tan function returns a Double specifying the tangent of an angle.


dblAngle = 1.3  ' Define angle in radians.'
dblCotangent = 1 / Tan(dblAngle)  ' Calculate cotangent.'

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/tan-function

Atn - Arctangent of a number

The Atn function returns a Double specifying the arctangent of a number. The input to the Atn() function must be a double. The return value will be a double between -pi/2 and pi/2 radians.


Debug.Print Atn(Tan(WorksheetFunction.Pi/3))
Debug.Print WorksheetFunction.Pi/3
'Output:'
' 1.0471975511965976'
' 1.0471975511965976'

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/atn-function