Getting started with VBA

In Excel The most commonly used function in a workbook should be Sum It is built in; of course, in Excel There are many built-in functions. However, we often use the functions Excel Is not provided directly, or we do not know, this can be used by ourselves VBA Define a function that you need to use to participate in the calculation of cells in the workbook, which may be a common problem in daily work. The following describes how to implement a UDF with the aggregate function. Sum The function system has been built in. The function to be implemented may be named Udsum .

First, you should useVBAUndoubtedly, the most directExcelFunction extensions come fromVBA. In fact, we canExcelOfMicrosoft Visual BasicAdd a public function to the editor module to implement the UDF.ExcelDirect image usage in CellsExcelBuilt-in functions are also used.

How to openVisual BasicEditor?

1. StartMicrosoft Excel;
2. Use the menu tool -- macro --Visual BasicEditor" OpenMicrosoft Visual BasicEditor window;
3. Right-click any project in the tree directory in the "project" browser window and run the "insert-module" command;

OKIn this module editor, you can start editing user-defined functions.

Before that, we should consider the parameter form of the function, and the built-inSumFunction analogy, we can find thatSumThe function parameter should be one or more cells, whileVBAIsRangeObject (aboutRangeObject can be referredMicrosoft Visual BasicIn helpExcel VBAObject Model), that is, the parameter of the function to be customized should beRangeObject. Therefore, we can1"Editor to customize the following functions:

1 Public Function Udsum (R As Range) As Double
2
3 Dim I As Integer
4 Dim J As Integer
5 Dim RTN As Double
6
7 RTN = 0
8
9 For I = 1 To R. Rows. Count
10 For J = 1 To R. Columns. Count
11 RTN = RTN + R. cells (I, j)
12 Next J
13 Next I
14
15 Udsum = RTN
16
17 End Function

The function is simple, traversingRangeAll cells of the object. Add the numbers and return them.

Save the changes and return to the workbook. Book1 Interface, in a continuous cell (such B2: C3 ), And then in another cell (such E4 ) = Udsum (B2: C3) ", That is, we use our custom aggregate function. Udsum To calculate the region B2: C3 The sum of numbers in. After verification, we can find Udsum Built-in functions can be implemented. Sum . In addition, because Udsum The parameter accepted by the function is Range Object, so this function can also be called = Udsum (testregion) Of course, you should define a name" Testregion Indicates a specific cell area.

In the above description, we may also notice that,UdsumThe function parameter isContinuousCell area, while the built-inSumA function can accept data in the area of a discontinuous cell in the form of multiple parameters, such= Sum (B2: C3, B5: C6, B8: C9). That is to say, we should modify the FunctionUdsumTo accept a certain number of parameters.VBAOfParamarrayFeatures. Modified UDFUdsumAs follows:

1 Public Function Udsum (paramarray X ()) As Double
2
3 Dim I As Integer
4 Dim J As Integer
5 Dim K As Integer
6 Dim RTN As Double
7
8 RTN = 0
9
10 For I = 0 To Ubound (X)
11 For J = 1 To X (I). Rows. Count
12 For K = 1 To X (I). Columns. Count
13 RTN = RTN + X (I). cells (j, k)
14 Next K
15 Next J
16 Next I
17
18 Udsum = RTN
19
20 End Function
21

It can be noted that the modified function parameter does not specify a type, and unspecified data types are usedVariantType processing. The displayed type is obviouslyRangEIs the array of a range object. However, because paramarray in VBA must be declared as a variant array, it can only appear in the variant type, but in the function body, we still process it as a range array, traverse all cells in all discontinuous areas of the array, and add the arrays to return.

So far, we can use something similar= Sum (B2: C3, B5: C6, B8: C9). That is to say, we have implemented a user-defined function with the same functions as the built-in sum function.

Of course, the purpose of this essay is not only to implement a aggregate function, but to discuss the implementation process of a user-defined function. After the opposite, it can solve many common problems.