Develop functions during function writing

Overview: The function process is a custom function, which is widely used in plug-ins. The application scope is small, and the process can only return one or more combinations. The sub process can return values and modify referenced objects. For example, the cell A1 value is introduced, set the format of the Center for A1. Function can get the work name, but the worksheet name cannot be modified. The funcion process can be not a practical parameter, similar to the worksheet functions Rand and now. However, most of them require one or more parameters. The maximum value is 255.

I. Function Syntax Parsing

[Public | private | friend] [static] function name [(Arglist)] [as type]

[Statements]

[Name = expression]

[Exit function]

[Statements]

[Name = expression]

End Function

Public (optional) indicates that all other processes of all modules can access this function process. If this process is used in a module containing option private, it is unavailable outside the project.

Private (optional) indicates that only other processes including the declared modules can access the function.

Optional. Smart use in the class module, indicating that the function process is visible throughout the process, but invisible to the controllers of the object instance.

Static (optional) indicates that the local variable value of the function process will be retained between calls. The static attribute does not affect the variables declared outside the function, even if these variables are used in the process.

Name is required. Function Name

Arglist Parameters

(Optional) expression. The return value of the function. The function name = value. This value is returned.

Call method: You can use formulas in a worksheet. You can use internal functions in a worksheet as well as nested functions.

Called by other processes. VBA. Function Name (parameter)

Recursion can be implemented in the same way as the sub process.

Ii. Process Parameters

A) sub process parameters and Applications

I. Parameter Syntax:

[Optional] [byval | byref] [paramarray] varname [()] [astype] [= defaultvalue]

Opntional (optional) indicates that the parameter is not a required keyword. If this option is used, all subsequent parameters must be optional and must be declared using the optional keyword, if paramarray is used, optional cannot be used for any parameter.

Byval (optional) indicates passing the parameter by value

Byref (optional) indicates that the parameter is transmitted by address. byref is the default value.

Paramarray (optional) is used only for the last parameter of Arglist. The fatal last parameter is a variant optional array. You can use the paramarray keyword to provide any data parameters. Paramarray keywords cannot be used together with byval byref or optional.

Defaultvalue (optional) any constant or constant expression is valid only for the optional parameter. If it is of the object type, the default value displayed can only be nothing.

Verify operation permission case

Sub name (name asstring)

Dim I asbyte, RNG as range

For I = 1to sheets. Count

If thisworkbook. Sheets (I). Name = "" then: goto OK

Next I

Msgbox "List of non-licensed personnel", 64

Exit sub

OK:

If Len (name) <2 or Len (name)> 10 then msgbox "length intelligence is 2 to 4. Please reenter", 64: Exit sub

Set RNG = thisworkbook. Sheets (""). Range ("A1: A10"). Find (name)

If RNG is nothing then msgbox "You do not have permission to operate" else msgbox "you have permission to operate"

End sub

Sub Confirm permission 1 () 'manually specify the name

Call name (application. inputbox ("Enter name", "Confirm permission", "", 2 ))

End sub

Sub validation permission 2 () 'is determined based on the value of the current table A1.

Call name (activesheet. Range ("A1 "))

End sub

Sub Confirm permission 3 ()

Msgbox application. Username

Call name (application. username)

End sub

Ii. Pass by value and by address.

Byval variable as long

Byval indicates passing parameters by value. The real parameters passed by the main program cannot be changed.

Byref indicates that passing parameters by address can change the default value of real parameters passed by the main program.

B) parameters of the function Process

Function can only be passed to reference a real parameter. You cannot change the attribute and value of the passed real parameter. You can return the corresponding value.

C) Develop custom functions

I. Develop a function process without Parameters

1. Get the local IP Address

Function IP () 'Get IP Address

Dim item

'Use WMI Technology to obtain the IP address currently set for the NIC

For each item ingetobject ("winmgmts: \" & "." & "Root \ cimv2"). execquery ("select * fromwin32_networkadapterconfiguration ")

If typename (item. IPaddress) <> "null" then IP = item. IPaddress (0)

Next

End Function

2. Return the cell address with a formula.

Functionfunadd ()

Dim rngas range, cell as range

For eachrng in activesheet. usedrange 'traverses the used regions of the current table

Ifrng. hasformula then' if there is a cell Formula

Ifrng. address <> application. thiscell. Address then 'If the address of the variable RNG is not equal to the address of the cell where the current formula is located

If cellis nothing then', if the variable cell is initialized

Set cell = RNG

Else

Set cell = appcation. Union (cell, RNG) 'combines the two cell Objects represented by the variable cell and RNG into an object and assigns the value to the cell.

End if

End if

End if

Next RNG

 

If cellis nothing then funadd = "" else funadd = cell. Address (0, 0) ', the result is returned.

 

End Function

Ii. Develop a function process with a parameter

1. Convert the RMB amount to uppercase.

Function capital (cell as string)

Dim RMBs as string

If cell = "" Or notisnumeric (cell) then capital = "": exit function 'If the parameter is null or not a value, the exit process is returned.

If cell = 0 then capital = "Zero yuan integer": exit function 'If the parameter is 0, the string is returned and the function is exited.

'Convert the value to a medium value, replace the vertex with the dollar, and replace the negative sign with the negative one.

RMBs = Replace (replace (application. text (round (cell, 2), "[dbnum2]"), ". "," Yuan "),"-"," negative ")

'Add points and points, and replace the final "zero" with RMB.

RMBs = IIF (left (right (RMBs, 3), 1) = "Yuan", left (RMBs, Len (RMBs)-1) & "" & right (RMBs, 1) & "points", IIF (left (right (RMBs, 2), 1) = "Yuan", RMBs & "", IIF (RMBs = "0 ", "", RMBs & "Yuan ")))

'Replace zero and zero corners with null

RMBs = Replace (replace (RMBs, "", ""), "","")

Capital = RMBs 'Return

End Function

2. Create a worksheet directory

Function Worksheet (optional serial number) as string' declares a function. One parameter is optional.

Application. Volatile 'declared as a nonvolatile Function

'If no parameter is input, the variable sequence number is assigned to the address of the current table.

If ismissing (No.) Then no. = activesheet. Index

If no.> sheets. Count then', if the parameter is greater than the number of worksheets

Worksheet = ""

Else

Worksheet = sheets (serial number). Name

End if

End Function

Formula:

= Hyperlink ("#" & Worksheet (row (A2 ))&"! A1 ", Worksheet (row (A1 )))

3. Shutdown Function

Function Shutdown (optional close_time as byte = 10)

Shutdown = close_time

Shell "shutdown-s-t" & close_time 'closes the calculator within the specified time and calls the doscommand

End Function

Methods:

Isnumeric
Yo about determining whether a parameter is a number

Replace
Is a replacement function, but it is very different from the worksheet function replace, and is very similar to the substitute Function

Ismissing
Used to determine whether the optional parameters of a function have been passed to the process.

Index
Attribute indicates the sequence number left-right of the value worksheet in all worksheets.

Run the shell command to execute the doscommand.

 

Iii. Develop a function process with two parameters

1. The data exported by the system is divided into two parameters. The second parameter is optional.

'The first parameter is a cell reference, and the second parameter indicates the number of Columns after the partition.

Function breakdown (RNG as range, optional style as byte = 1) asstring

Application. Volatile

On errorresume next 'error prevention

Dim I asinteger, STR as string

'Convert the cell value into an array using a comma as the separator, and then assign a value to the STR variable from the array, depending on the second parameter.

STR = Split (RNG. Text, ",") (worksheetfunction. Roundup (style/3, 0)-1)

If stylemod 3 = 1 then', if the second parameter is divided by 3, the remainder is 1.

For I = 1to Len (STR) 'traverses every character in Str

If isnumeric (mid (STR, I, 1) Then exitfunction' if a number is encountered, the process ends.

Breakdown = breakdown & Mid (STR, I, 1) 'concatenates all retrieved strings and returns values left and right.

Next I

Elseifstyle mod 3 = 2 then' if the remainder of the parameter divided by 3 is 2

For I = 1 to Len (STR)

'If a number or decimal point is encountered, it is taken out and serialized as the return value.

If VBA. isnumeric (mid (STR, I, 1) or mid (STR, I, 1) = "." Then breakdown = breakdown & Mid (STR, I, 1)

Next I

Elseifstyle mod 3 = 0 then

For I = 1 to Len (STR) step-1 'traverse every character of STR, from right to left

If isnumeric (mid (STR, I, 1) Then exitfunction' the process ends if a number is encountered.

Breakdown = mid (STR, I, 1) & breakdown

Next I

End if

If err <> 0 then breakdown = "" 'returns NULL if an error occurs.

End Function

Call: = breakdown ($ A3, column (A1 ))

2. Chinese ranking

Function ranking (Region, score) 'declares a function with two parameters.

Application. Volatile

Dim dicas object, RNG, I as integer 'Declares variables, including dictionary objects

Set DIC = Createobject ("scripting. Dictionary") 'Declares dictionary object variables

For eachrng in Region 'traverse the region

'If the variable RNG is equal to the score, the variable I is assigned 1. If the variable RNG is greater than the score, the RNG value is appended to the dictionary.

If RNG = score then I = 1 else if RNG> score then DIC (RNG * 1) = 1

Next

'If variable I is greater than 0, then the data in the region is equal to the score, then the ranking result is equal to the number in the dictionary + 1 (the dictionary object ignores duplicate values)

If I> 0 then ranking = DIC. Count + 1 else ranking = "out of range" 'If the score and any data in the region do not want to wait, return out of range

End Function

Createobject ("scripting. dictionary) is used to create a dictionary object. It features that the Members do not repeat, while the Chinese ranking requires that duplicate values be ignored, that is, the first person in the four is counted as the first person with 100 points, the two are tied for the second place with 99 points.

Both parameters of the function support manual input, not limited to cell reference.

Split is an array function that converts a string into an array by a character as a separator.

Iv. Develop two function processes with optional parameters

Declaration method: function COL (optional RNG as range, optional style as string = "A") declares the function name. There are two optional parameters followed by = "a" to determine whether the user has entered the value.

Gains:

When the fee object variable in the function is ignored, you can use ismissing to determine. If it is a cell object, you can only use nothing to determine. If RNG is nothing

When the two parameters of the address attribute use 0, the address can be converted to a relative reference, which is conducive to obtaining the column label

Date type variable. It cannot be determined by nothing. Judge whether it is equal to 0

V. Develop function processes with Uncertain Parameters

The declared functionconnect (paramarray RNG () as variant) has multiple parameters, including 1-, and uses for loop traversal during processing. The size of this array is obtained using the ubound function.

Gains:

Ubound returns
Long data, whose value is the maximum subscript available for the specified array dimension

For I = 0 to ubound (RNG) because it is a variable volume, the default subscript FOR A for loop should be 0

Declare uncertain parameter rules:

1. The declared parameter must be at the last position.

2. The declared parameter must be of the variant data type.

3. the Intersect function of application allows the function to only calculate the overlap between the data area and all the code areas of the parameter, so as to prevent the entire column, the whole row or the entire worksheet from being crashed as a parameter, however, it also brings a disadvantage that the parameter intelligently references the area of this worksheet. When other worksheet or workbook areas are used

Vi. Development belt has three parameters to switch to the third function process as optional

Declaration method: function three meals (condition zone as range, color cell as range, optional statistical zone)

Gains:

Color must be used to calculate the cell background color, and colorindex cannot be used. It can be used in 2003. But cannot be used in 2010

The range. Resize (num1, num2) attribute is used to adjust the size of a specified area. Parameter 1 indicates the number of rows in the new region. The number of columns in the new region is ignored. Will not change

Redim preserve array (1 to 2) If no target is found, the size of the array needs to be reset and the value of the original array needs to be retained. Therefore, redim Preserve must be added to the loop to declare the array.

Transpose (array) sets the horizontal array to a vertical array

The Vlookup function can return only one target value that meets the condition.

Iii. Function compilation help

When you write a function in a module or other place and want it to be used by the user, double-click the function to help blank information. Therefore, it is inconvenient for users to write help information for functions.

Application. macrooptions method to compile help information

Application. macrooptions (macro, description, hasmenu, menutext, hasshortcutkey, shortcutkey, category, statusbar, helpcountextid, helpfile)

Macro macro name or user-defined function name

Description macro description

Hasmenu ignores this parameter

Menutext ignores this parameter

If hasshortcutkey is true, a shortcut key is specified for the macro, and shortcutkey must be specified. If this parameter is set to false, the shortcut key is not specified for the macro. If the macro already has a shortcut key, false is invalid.

Shortcutkey: if the preceding parameter is true, the shortcut key must be specified.

Category is an integer that specifies an existing macro function category. If a string is provided, it is displayed as a category name in the insert function dialog box. If this category name is not used, use this name to define a new category. If the category name is the same as a built-in name, the User-Defined Function is mapped to this built-in category.

Statusbar macro status bar text

Helpcountextid is an integer that specifies the context ID assigned to the macro help topic.

Helfiie contains the Help file name for the help topic defined by helpcontextid

Description of parameters displayed in the argumentdescriptions function parameter dialog box

 

Iv. Summary

The function process is a user-defined function. You can develop your own function as needed.

 

For functions, internal worksheet functions must be faster than user-defined functions.

 

When developing a user-defined function, you should try to provide users with some options to make the function operation simpler and the formula shorter. Optional parameter concepts

 

For some calculation results, if multiple results can be returned, all formats should be listed as much as possible for the user to choose. For example, this date function

 

For a function with multiple results, the function should be declared as an array and all results should be displayed to the user. To reflect flexibility, the colleague needs to specify a default value. For example, the custom function vlookupcol.

 

To enable user-defined functions to be used in any workbook, the workbook should be a macro file. And align to load or as to the self-starting folder.