"Go" Getting Started with VBA programming (II)

Detailed description of what VBA programming is created by Vietdung90, last modified 2016-10-19

It was not until the early 90 that the application automation was a challenging area. For every application that needs to be automated, people have to learn a different automation language. For example, you can use Excel's macro language to automate Excel, using Word Basic to automate Word, And so on. Microsoft has decided to let it develop applications that share a common automation language--------Visual Basic for Application (VBA), which can be thought of as a very popular application development language vasual Basic A subset of the. In fact, VBA is the version of the "parasitic" VB application. The differences between VBA and VB include the following:

1. vb is designed to create standard applications, while VBA is to automate existing applications (Excel, etc.)

2. vb has its own development environment, and VBA must be parasitic on existing applications.

3. To run the VB development of the application, users do not have to install VB, because VB developed the application is executable (*. EXE), and a program developed by VBA must rely on its "parent" application, such as Excel.

Despite these differences, VBA and VB are structurally similar. In fact, if you already understand VB, you will find that learning VBA is very fast. Correspondingly, after learning VBA will lay a solid foundation for the study of VB. And when you learn to create a solution in Excel with VBA, you already have a word Use VBA to create a solution in ACCESS OUTLOOK FOXPRO prowerpoint
Most of the knowledge of the case.

* A key feature of VBA is that the knowledge you have learned can be transformed from one Microsoft product to another.

* VBA can be called the "Remote Control" of Excel.

What exactly is VBA? Rather, it is an automated language that automates commonly used programs and can create custom solutions.

In addition, Excel can be used as a development platform to implement applications if you wish.

Advantages of application Automation in an Excel environment maybe you want to know what VBA can do? features that you can implement with VBA include:

1. Automate the repetitive tasks.

2. Customize Excel toolbars, menus and interfaces.

3. Simplify the use of templates.

4. Customize Excel to make it a development platform.

5. Create the report.

6. Complex operation and analysis of the data.

The following are the reasons for using Excel as a development platform:

1. Excel itself is powerful, including printing, file processing, formatting and text editing.

2. Excel has a number of functions built into it.

3. Excel interface is familiar.

4. Can be connected to a variety of databases.

In other languages to develop applications, half of the work is to write some basic functions of the module, including the opening and saving of files, printing, copying and so on. And with Excel as the development platform, because Excel already has these basic features, all you have to do is use it.

Record a simple macro before you introduce learning VBA, you should take a few minutes to record a macro.

New term: "macro" refers to a series of VBA statements that Excel can execute.

The following macro that will be recorded is very simple, just changing the cell color. Please complete the following steps:

1. Open a new workbook and confirm that the other workbook is closed.

2. Select A1 cell. Bring up the Standard toolbar.

3. Select Tools-Macro-record new macro.

4. Enter change color to replace the default macro name as the macro name, click OK, and note that recording is displayed in the status bar, especially the Stop Recording toolbar. Replacing the default macro names is primarily convenient for each of these macros.

★ The macro name can be up to 255 characters long and must begin with a letter. The characters that are available include letters, numbers, and underscores. Spaces are not allowed in macro names. Usually underscores are used to represent spaces.

5. Select the Format cell, select red in the Pattern option, and click OK.

6. Click the Stop Recording toolbar button to end the macro recording process.

※ If the Stop Recording toolbar does not begin to appear, select tools-Macro-stop recording.
After you have recorded a macro, you can execute it.

Execute a macro when you execute a macro, EXCEL follows the macro statement as if the VBA code is "remote" to EXCEL. However, the "Remote control" of VBA not only makes the operation easy, but also enables you to get some features that are not possible with EXCEL standard commands. And, once you're familiar with EXCEL's "remote control," You'll wonder how you're going to survive without these "remotes". To perform the macro you just recorded, follow these steps:

1. Select any cell, such as A3.

2. Select Tools-Macros-macros to display the Macros dialog box.

3. Select "Change Color" and select "Execute", then the color of A3 cell will turn red. Try selecting a different cell and a range of cells, and then execute the macro to deepen the impression.

View recorded code what exactly is the control of Excel running? You may have some doubts. OK, let's take a look at the VBA statement.

1. Select Tools-Macros-macros to display the Macros dialog box.

2. Click "Change Color" in the list and select the "Edit" button.
At this point, the VBA Editor window (VBE) opens. For more details about the editor, focus on the displayed code. The code is as follows: (date and name will be different)
Sub Change Color ()
‘
' Change the color Macro
' XW recorded macros 2000-6-10
‘
‘
With Selection.interior
. ColorIndex = 3
. Pattern = Xlsolid
. Patterncolorindex = xlautomatic
End with
End Sub

This code will be very familiar to the future, although now they look like a strange foreign language. Learn VBA or programming language in a
Somewhat more like learning a foreign language.

Sub change Color (): This is the name of the macro.
The five elements in the middle that begin with "'" are called "annotations", which are generated automatically when the macro is recorded.
The structure with the end with the start to end with is the with struct statement, which is the main part of the macro. Note the word "selection", which represents the highlighted area (that is, the selected area). With Selection.interior: It reads "interior of the selected area". This entire statement sets some "properties" inside the zone.

which
. ColorIndex = 3: Set the interior to red. Note: There is a small dot, its function is to simplify the statement, small dots instead of appearing in the word after with, it is part of the with structure. In addition: The red is digitized to 3. (Can the red alert be called: No. 3rd Alert, eh? If you are interested, you will try to change 3 to another number.

. Pattern = Xlsolid: Sets the interior pattern for the area. Since you are recording a macro, you have not set this item,
The macro still records it (because there is one in the "pattern" option, but you never set it). Xlsolid represents pure
Color.

. Patterncolorindex = xlautomatic: Indicates that the inner pattern shading color is auto-color.

End with: ends with statement.

End Sub: Ending of the entire macro

Edit recorded code in the previous section, we recorded a macro and looked at the code, and two sentences in the code did not actually work. What's the two sentences?
Now, make a change in the macro and delete the extra rows until the code is the same as the following:
Sub Change Color ()
‘
' Change the color Macro
' XW recorded macros 2000-6-10
‘
‘
With Selection.interior
. ColorIndex = 3
End with
End Sub

When you're done, experiment with the worksheet. You will find that the result is the same as before the change. Add a row before the WITH statement:

Range ("A5"). Select
When you try to run the macro, the macro runs to make A5 cells red, regardless of which cell you start selecting.

Now you can see that editing a recorded macro is also very simple. There are three reasons why you need to edit a macro because of the following.

One: An error occurred in the recording and had to be modified.

Second: There are extra statements in the recorded macro that need to be deleted to increase the speed of the macro.

Three: Want to increase the function of the macro. For example, adding a judgment or a loop that cannot be recorded.

Limitations of recording macros many of the EXCEL processes you want to automate can be done with recorded macros. However, the macro recorder has the following limitations.

The work that cannot be done through the macro recorder is:

1. The recorded macro has no judgment or circulation ability.

2, the human-computer interaction ability is poor, that is, the user cannot enter, the machine cannot give the hint.

3. The Excel dialog box cannot be displayed.

4. Unable to display the custom form.

Summary of this chapter, you have mastered the basic knowledge of VBA, you will record macros, edit macros and understand the limitations of the recording macro, and has been to learn VBA and even VB and other programming languages to lay the foundation. The point is that you've learned a riddle, that is, you know what programming is. Here are some little exercises.

"Go" Getting Started with VBA programming (II)