Application of custom functions in Excel (usage of XOR in Excel and sum of text-type numbers)

In Excel, can I call a self-created custom function in the worksheet just like calling a built-in function, and display a dialog box? The answer is yes. The following is an example:

Question: ① sum a series of numbers in the text type (sum () returns 0)

② A series of numeric operations (no XOR () in Excel built-in functions ())

1. In Excel, set column A and column C to numeric type, and column C to text type.

2. Select view ---> toolbar --> visiual basic ---> visiual basic editor window ----> insert ---> module and enter the following content

Function myxor (byval range as range)
Dim I as integer, J as integer
For I = 1 to range. Rows. Count
For j = 1 to range. Columns. Count
Myxor = myxor XOR range (I, j)
Next
Next
End Function

Function mysum (byval range as range)
Dim I as integer, J as integer
Mysum = 0
For I = 1 to range. Rows. Count
For j = 1 to range. Columns. Count
Mysum = mysum + val (range (I, j ))
Next
Next
End Function

As shown in:

3. Now, we have defined two functions: myxor () and mysum ()

Return to sheet1, enter "=" in cell A6, select "insert function", and select "user defined" in category ", the two functions we just defined are displayed in the list box. as shown in:

4. In order to enter A1 ~ in A6 ~ For the XOR result of A5, select the newly defined myxor function. The following dialog box is displayed in Excel:

5. Select a range, such as A1 ~, like other built-in functions ~ A5: The function is correctly calculated and inserted into cell A6.

6. Similarly, we can use the custom function mysum () to sum the text numbers in Column C and assign values to cells A8.

7. All in all, using VBA to compile functions can meet some of our specific needs. The calculation method and processing process are no different from those of VB.