Write sub process

Overview: every program in VBA contains a process. The recording macro is a process, and a custom function is also a process. After mastering the compilation and thinking of a single process, you can combine large and medium-sized plug-ins or professional programs.

I. Classification and calling methods

I. Category

There are three types of processes: subprocesses, function processes, and attribute processes.

1. Sub-Process

Sub-process ()

End sub

2. Function Process

Function process (RNG asrange)

End Function

3. Attribute Process

Property get property process () as Variant

End Property

Ii. Call Method

1. Execute Alt + F8

If a sub process exists in the thisworkbook and standard module windows of the worksheet. The macro dialog box is displayed, and then the execution is selected.

2. Button execution

Create a button in the worksheet and associate the button with the sub process to implement the button execution program.

Development Tool --- insert -- form control --- button and then specify your own macro.

3. Menu call

No further description.

4. event triggering

For some programs that require self-starting, events are usually triggered without manual intervention. For example, when a workbook is enabled, a program is automatically executed or the form is closed, execute a program when you move the mouse over the form. Subsequent implementation.

5. Use formulas to call a worksheet

Insert module-then enter the code

Function score (RNG)

Score = IIF (RNG> = 60, "pass", "fail ")

End Function

Enter = score (B1) in the C1 formula)

Then enter 30 in B1, and the system will fail.

Iii. Insert procedure

1. Non-event Process

First, insert a module, and then insert the process. Then, enter the module name and set it to public to create a shell for the process.

2. Event Process

VBA supports many types of events. Most Event code requires parameters. You can use the drop-down list of objects and processes provided by VBE.

Double-click the sheet1 drop-down list box and select the worksheet code window to generate the change event code by default. Most of the time should be selected from the drop-down menu, which is not easy to make errors

Move the mouse over events

Insert --- Right-click the user form and view the code. By default, the click event is generated. Select the mousmove event and enter the code.

Iv. Process naming rules

The process name can be the same as the private variable in the process, but it cannot be the same as the common variable.

Ii. sub compilation process

The sub process is the sub process declared by the sub statement. All macro recording and generation processes are sub processes. The function process or attribute process cannot be produced by recording macros.

Syntax:

Private | public | friend [Static] sub name [(Arglist)]

[Startements]

[Exit sub]

[Statements]

End sub

Sub statement Parameters

Public. Indicates that all other processes of all modules can access this sub process. If used in a module containing option pirvate, this process is unavailable outside the project.

Private is optional. Indicates that the sub process can be accessed in other processes of the module containing its declaration.

Optional. Intelligent usage in the class module indicates that the sub process is visible throughout the project, but the control over the object instance is invisible.

Static (optional) indicates the value of the local variable in the sub process. Static attributes do not affect the variables declared outside sub, even if these variables are used in the process.

Name is required. Sub process name.

(Optional) Arglist, which indicates the list of variables of the parameters to be passed to the sub process during the call. Multiple variables are well separated.

Optional. The statement group specified in the sub process

V. Module-level process

It can only be called in the current module.

Use private before sub Declaration

Only called in the current Module

Private sub process 1 (name as string)

Msgbox name

End sub

Private sub process 2 ()

Call process 1 ("Call process 1 ")

End sub

Not in the macro dialog box. The process name cannot be viewed in the dialog box opened by Alt + F8

Vi. engineering level process

It refers to the process that can be called at Will anywhere in the current project. In the sub statement, the potential identifier is public, which can be called if not in the current process, and can appear in the macro list. If not modified, the default value is public. support parameters can be called in any module or form.

VII. Roles and differences between exit sub and end

Release public variables?

Exit sub does not release the variable and exits the current process.

End release variable

Terminate all programs

Exit sub exit the current process

End to exit all programs. That is, the event has ended.

B) sub Process Execution Process

I. Perform sub in one step by pressing F8. Skip variable definition statement

Ii. Use a colon to execute multiple codes in one line

Private sub executes multiple codes in one row ()

If 60 = 60 then msgbox "pass": Exit sub

End sub

Iii. Use tags to change the execution process

Rules:

But it is a combination of symbols other than punctuation marks.

End with a colon

It is case-insensitive and must be at the leftmost end of a row.

Work with Goto

Private sub create a summary table ()

For I = 1 to sheets. Count

If sheets (I). Name = "" thengoto err

Next I

Sheets. Add

Activesheet. Name = "summary table"

End

Err:

Msgbox "summary table already exists"

End sub

Iv. nested call method of sub process

1. Call statement call

[Call] Name [(argumentlist)] Call process name parameter call can be omitted

2. Run method call

Application. Run process name application can be omitted

V. recursion of the process

Create five tables using private sub ()

If sheets. Count> 5 then exit sub

Sheets. Add, sheets (sheets. Count), 1

Call to create five tables

End sub

Set the clock:

Sub time ()

[A1] = worksheetfunction. Text (now (), "HH: mm: SS ")

Application. ontime now () + timevalue ("00:00:01"), "Time"

End sub

Sub termination ()

Application. ontime now () + timevalue ("00:00:01"), "time", false

End sub

C) process cases

I. constituency statistics

Sub selection statistics ()

Dim MSG as string

MSG = MSG & "number of cells" & selection. Count & CHR (10)

MSG = MSG & "number of digits" & worksheetfunction. Count (selection) & CHR (10)

MSG = MSG & "non-empty cell" & worksheetfunction. countblank (selection) & CHR (10)

MSG = MSG & "sum of constituency" & worksheetfunction. sum (selection)

Msgbox msg

End sub

Private sub worksheet_selectionchange (byval target as range)

Call constituency statistics

End sub

Ii. Convert cell data into uppercase letters: sub process with Parameters

Sub conversion (target)

Selection (1) = strconv (target, vbpropercase)

End sub