A Brief History of VBA and an environment

Overview: It mainly describes the usage and disadvantages of VBA, and then explains how to set up the development environment of VBA and how to improve programming efficiency by using VBE.

I. A Brief History of VBA

A) macro introduction:

The macro should be named macro. It is a set of commands that automatically execute certain operations. It contains two processes, namely, excel4 or the macro language of XLM and the VBA macro in excel5. The Macros in excel4 are composed of macro table functions, and the functions in the macro table are input to control the execution of the program. Later, Microsoft's mad VBA was used as the macro language, and the VBA Editor, VBE, was introduced, you can use a recording macro to generate Code. The Code is stored in the Code module of the VBE environment. You can use the combination key Alt + F8 to return the macro that calls the recording.

VBA is an object-oriented programming language that allows you to write code in what you see is what you get.

B) Advantages and Disadvantages of VBA

I. Advantages: Recording, WYSIWYG, calling of ready-made objects, wide application, and convenient communication

Ii. Disadvantages: long learning cycle, large number of professional words, and low popularity

C) Main usage of VBA

Process large-scale operations, split and merge workbooks/worksheets, process repetitive tasks, simplify built-in formulas, customize program interfaces, and develop protected professional programs

2. Build a development environment for VBA

A) Configure Excel settings

File-Option-Security Options-trust center settings-

I. Macro settings

Start all macros and trust access to the VBA project Object Model

Ii. trusted location

Add a new location, select the folder directory to be trusted from the top, and check the folder to indicate that the subdirectories are also trusted.

B) how to load macro files

Disable the trust center settings. Click "load macro". Click "Excel add-on" under "manage" and click "go" to browse... Find the macro xla format in the folder you trust. Then you can add the macro to the Excel file.

C) start to use the VBE development environment and query tools.

Open the VBE Editor: Alt + F11

Click F1 to bring up the help interface, which is different from the help of Excel. You can enter names in the text box and press Enter. It means you can use the help to query the objects and methods you need. This is a local query. The word offline is displayed in the lower right corner.

When the code is entered, F5 is executed.
Finally, you can develop it. Tears.

D) Compilation Error

VBE provides a good error prompt Function

I. identifier error sub 11 () 11 is an error

Ii. Syntax missing error if then/goto is a combination of directly input if [a1] = 10, an error will be reported

Iii. invalid character range function can only use double quotation marks ''to input range (" A1 ") = 10

IV. If the list separator mid ([a1]) = 10, the list separator error is returned. You must specify parameters for the mid function.

V. Running Error

For a simple example, there is no time.

Calculate the number of digits in column A by using sub ()

Dim I asbyte

I = worksheetfunction. Count (range ("A: "))

Msgbox "the number of numbers in column A is:" & I

End sub

If column A has 1000 numbers, an error is returned. Because of the byte type, it can only represent 0-255.

Iii. Clever setup of the VBA editor to improve programming efficiency

First, check the file-option in version 2010. On the homepage, you can see the development tool Alt + F11 In the toolbar and right-click the workbook to view the code.

VBA is divided into three parts: Excel objects, forms, and modules.

A) code window

A code window is a place for storing VBA code. It is the core component. A code window includes a worksheet, a workbook, a form, a module, and a class module code window.

B) objects and process windows

Objects and process windows refer to the object list and process window list at the top of the code window.

C) Immediate window

The immediate window has two functions to display the results generated when the engraving code is displayed. It looks like the console in eclipse and the Code for executing a single sentence. It is hidden by default. You can use Ctrl + GTO call it out.

D) toolbox

Important tools. The default status includes 15 tools. You can use these tools to set interfaces similar to any other software. If not, you can customize new tools by email.

The method of calling a tool is different from that of calling any component on the front-end. It is based on userform.

It seems really not powerful.

E) functions of different code windows in VBE

I. worksheet code window

The Worksheet code window is used to store the worksheet Event code. This code is only called in the current table. The normal sub process is saved in the worksheet time code. Although it can also be executed, other modules or worksheets can be called, but it is inconvenient. We recommend that you store the worksheet time code in the worksheet code window. Function and sub processes are stored in the module.
CTRL + R call up the project resource manager. Double-click the worksheet to display the window and code.

Ii. Workbook code window

The workbook code window is named thisworkbook, which is used to store the time code at the job level. Although it can also store the function process and common sub process, this window only stores the Code related to the workbook event.

Iii. form code window

The form code window is used to store Code related to forms and controls. Its code can only be used in forms, and cannot be executed in any other windows.

Iv. module code

The module code window is used most frequently during work. Store the sub and function processes in the module code window. These processes can be executed in the current module or in other windows. You can use the Module Program for worksheet events, workbook events, form events, and class modules. Modules and modules can call each other

V. Class Module

The class module is the module of attributes and methods of user-defined classes. You can create a class module by inserting the class module on a single machine.

The code of the class module is usually used for application-level events. It is called in the corresponding events of the application.

Vi. Other settings are used for smart prompts and display prompts. Add. You can intelligently prompt methods and properties, and display method parameters.