Built-in Functions

Common Built-in Functions

【Excel VBA】

Common built-in functions in Excel VBA mainly include mathematical functions, date/time functions, random number generation functions, data type conversion functions, and string manipulation functions, etc.

The mathematical functions provided by Excel VBA are shown in Table 10-1.

Table 10-1 Mathematical Functions in Excel VBA

Continued Table

The date/time functions provided by Excel VBA are shown in Table 10-2.

Table 10-2 Date/Time Functions in Excel VBA

For string functions, please refer to Chapter 6. For data type conversion functions, please refer to Chapter 2.

In Excel VBA, the Rnd function can generate random numbers. To generate non-repeating random numbers, the Randomize function can be used to generate a random seed.

The following code tests some Excel VBA functions randomly. The sample file path is Samples\ch10\Excel VBA\BuiltInFunctions.xlsm.

Sub Test()
    Const PI = 3.1415926

    ' Mathematical functions
    Debug.Print Exp(2)
    Debug.Print Sin(PI / 4)

    ' Date/time functions
    Debug.Print Date
    Debug.Print Year(Now)

    ' Random number generation function
    Randomize
    Debug.Print Rnd()
End Sub

Running the procedure outputs the calculation results of the test functions in the Immediate Window.

7.38905609893065
0.707106771713121
2021/9/1
2021
0.1633657

【Python】

Built-in functions in Python include data type conversion functions, data manipulation functions, data input/output functions, file operation functions, and mathematical calculation functions, etc.

Data type conversion functions include bool, int, float, complex, str, list, tuple, dict, etc. They have been introduced when discussing variable data types, so they are not repeated here.

Data manipulation functions include type, format, range, slice, len, etc. Except for slice, all have been introduced. The slice function defines a slice object to specify the slicing method. Passing this slice object as a parameter to an iterable object enables slicing of that iterable object.

Below, we create a list; the first slice object takes the first 6 elements, and the second slice object takes every other element in the range 2–8. We then use these two slice objects to slice the list.

>>> a = list(range(10))
>>> a
[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
>>> slice1 = slice(6)       # Take the first 6 elements
>>> a[slice1]
[0, 1, 2, 3, 4, 5]
>>> slice2 = slice(2, 9, 2)  # Take every other element in the range 2–8
>>> a[slice2]
[2, 4, 6, 8]

Data input/output functions include input and print, which were introduced in Chapter 1 and are not repeated here. File operation functions include file and open, used to open files.

Mathematical calculation functions are shown in Table 10-3.

Table 10-3 Mathematical Calculation Functions

Several examples are given below to illustrate the use of mathematical calculation functions.

>>> abs(-3)                 # Absolute value
3
>>> pow(3, 2)               # Square of 3
9
>>> round(2.78)             # Round 2.78
3
>>> a = list(range(-5, 5))  # Create a list
>>> a
[-5, -4, -3, -2, -1, 0, 1, 2, 3, 4]
>>> max(a)                  # Maximum value of list elements
4
>>> min(a)                  # Minimum value of list elements
-5
>>> sum(a)                  # Sum of list elements
-5
>>> sorted(a, reverse=True) # Sort list elements in reverse order
[4, 3, 2, 1, 0, -1, -2, -3, -4, -5]
>>> def filtertest(a):      # Define a function: filter rule is element value > 0
... return a > 0
... 
>>> b = filter(filtertest, a) # Filter list a using the defined rule
>>> list(b)                 # Display filter result class="text-secondary">as a list
[1, 2, 3, 4]

Python Standard Module Functions

Python has many built-in standard modules, each containing many encapsulated functions to provide specific functionalities. Below, we mainly introduce the math, cmath, and random modules, which provide mathematical operations, complex number operations, and random number generation, respectively.

1. Mathematical Operation Functions in the math Module

The math module provides a large number of mathematical operation functions, including general mathematical operation functions, trigonometric functions, logarithmic functions, exponential functions, hyperbolic functions, number theory functions, and angle-radian conversion functions, etc.

Before using the mathematical operation functions in the math module, you need to import the math module first. The syntax for importing the math module is as follows:

>>> class="text-secondary">import math

The dir function can list all mathematical operation functions provided by the math module.

>>> dir(math)
['__doc__', '__loader__', '__name__', '__package__', '__spec__', 'acos', 'acosh', 'asin', 'asinh', 'atan', 'atan2', 'atanh', 'ceil', 'copysign', 'cos', 'cosh', 'degrees', 'e', 'erf', 'erfc', 'exp', 'expm1', 'fabs', 'factorial', 'floor', 'fmod', 'frexp', 'fsum', 'gamma', 'gcd', 'hypot', 'inf', 'isclose', 'isfinite', 'isinf', 'isnan', 'ldexp', 'lgamma', 'log', 'log10', 'log1p', 'log2', 'modf', 'nan', 'pi', 'pow', 'radians', 'remainder', 'sin', 'sinh', 'sqrt', 'tan', 'tanh', 'tau', 'trunc']

Mathematical operation functions in the math module are shown in Table 10-4.

Table 10-4 Mathematical Operation Functions in the math Module

2. Complex Number Operation Functions in the cmath Module

Functions provided by the cmath module can be used for complex number operations. After importing the cmath module, the dir function can list all functions in the module.

>>> class="text-secondary">import cmath
>>> dir(cmath)
['__doc__', '__loader__', '__name__', '__package__', '__spec__', 'acos', 'acosh', 'asin', 'asinh', 'atan', 'atanh', 'cos', 'cosh', 'e', 'exp', 'inf', 'infj', 'isclose', 'isfinite', 'isinf', 'isnan', 'log', 'log10', 'nan', 'nanj', 'phase', 'pi', 'polar', 'rect', 'sin', 'sinh', 'sqrt', 'tan', 'tanh', 'tau']

Most meanings of complex number operations are the same as those of real number operations, except that the parameters are complex numbers.

3. Random Number Generation Functions in the random Module

The random module provides various random number generation functions. The syntax for importing the random module is as follows:

>>> class="text-secondary">import random class="text-secondary">as rd

Use the random method to generate a random number between 0 and 1.

>>> rd01 = rd.random()
>>> print(rd01)
0.8929443975828429

Use the randrange method to randomly select a number from a specified sequence. The randrange method can specify the start, end, and step of the sequence. Below, we specify the sequence as 10–50 with a step of 2 and randomly select a number from this sequence.

>>> print(rd.randrange(10, 50, 2))
26

A loop can be used to continuously generate random numbers. Below, we continuously generate 10 random numbers from this sequence and form a list.

>>> lst = []
>>> for i in range(10):
... lst.append(rd.randrange(10, 50, 2))
... 
>>> lst
[14, 12, 46, 36, 40, 34, 18, 46, 22, 30]

The uniform method can generate a uniformly distributed random number within a specified range. Below, we generate 10 uniformly distributed random numbers between 1 and 2 and form a list.

>>> lst = []
>>> for i in range(10):
... a = rd.uniform(1, 2)
... lst.append(float("%0.3f" % a))
... 
>>> lst
[1.59, 1.974, 1.589, 1.918, 1.904, 1.666, 1.418, 1.024, 1.429, 1.643]

The choice method can randomly select a number from a specified iterable object. Below, we create a list and use the choice method to randomly select a number from it.

>>> lst = [1, 2, 5, 6, 7, 8, 9, 10]
>>> print(rd.choice(lst))
9

The shuffle method can shuffle the data in an iterable object, i.e., randomly reorder it.

>>> rd.shuffle(lst)
>>> lst
[2, 7, 5, 1, 8, 6, 10, 9]

The sample method can randomly select a sample of a specified size from a specified sequence. Below, we randomly select 6 numbers from list lst to form a new sample.

>>> samp = rd.sample(lst, 6)
>>> samp
[6, 1, 5, 2, 8, 7]