VBA practical development guide (4)

VBA tutorial

1. VB is designed for creating standard applications, while VBA is to automate existing applications (such as Excel)
2. VB has its own development environment, while VBA must be parasitic on existing applications.
3. to run applications developed by VB, you do not need to install VB because the applications developed by VB are executable files (*..

 

Despite these differences, VBA and VB are very similar in structure. in fact, if you already know VB, you will find learning VBA very fast. correspondingly, after learning VBA, it will lay a solid foundation for learning VB. in addition, after learning to use VBA to create a solution in Excel, you already have most of the knowledge to use VBA to create a solution in word access outlook FoxPro prowerpoint.

* A key feature of VBA is that the knowledge you have learned can be converted to each other in some Microsoft products.
* VBA can be called "Remote Control" of Excel ".

What is VBA? More specifically, it is an automated language that can automate common programs and create custom solutions.

 

In addition, if you want to, you can also use Excel as a development platform to implement applications.

1.2 advantages of application automation in Excel

 

Maybe you want to know what VBA can do? Functions that can be implemented using VBA include:

 

1. Automate repeated tasks.
2. Customize the Excel toolbar, menu, and interface.
3. simplify the use of the template.
4. Customize EXCEL to make it a development platform.
5. Create a report.
6. perform complex operations and Analysis on data.

Excel is used as the development platform for the following reasons:

 

1. Excel is powerful, including printing, file processing, formatting, and text editing.
2. Excel has a large number of built-in functions.
3. Familiar with the Excel interface.
4. You can connect to multiple databases.

To develop applications in other languages, half of the work is to write some basic functional modules, including opening, saving, printing, and copying files. using Excel as a development platform, because Excel already has these basic functions, you only need to use it.

1.3 recording a simple macro

 

Before learning VBA, you should take several minutes to record a macro.
New term: "macro" refers to a series of VBA statements that can be executed in Excel.
The following macro to be recorded is very simple, just to change the cell color. Complete the following steps:

 

1) Open the new workbook and confirm that other workbooks are closed.
2) Select cell A1. Call up the "common" toolbar.
3) Select "tool"-"macro"-"Recording new macro ".
4) Enter "change color" to replace the default macro name with the macro name. Click OK. Note that "Recording" is displayed in the status bar, especially the "stop recording" toolbar. Replacing the default macro names is mainly to facilitate separate these macros.
★The macro name can contain a maximum of 255 characters and must start with a letter. The available characters include letters, numbers, and underscores. Spaces are not allowed in macro names. It is usually underlined to indicate spaces.
5) Select "cell" in "format", select red in the "pattern" option, and click "OK ".
6) Click the "stop recording" toolbar to end the macro recording process.

※If the "stop recording" toolbar does not appear, select "tool"-"macro"-"Stop recording ".

After recording a macro, You can execute it.
　
1.4 execute macros
　
When a macro is executed, the Excel statement is executed according to the macro statement, just as the VBA code is "Remote Control" on the Excel ". However, VBA's "Remote Control" not only makes operations easy, but also gives you some functions that cannot be implemented using Excel Standard Commands. In addition, once you are familiar with Excel's "remote control", you will wonder how you got it without the "remote control. To execute the macro you just recorded, follow these steps:

 

1) select any cell, such as A3.
2) Select "tool"-"macro"-"macro" to display the "macro" dialog box.
3) Select "change color" and select "run". Then, the color of cell A3 changes to red. Try to select an area consisting of other cells and several cells, and then execute the macro to deepen your impression.

1.5 view the Recording Code

What is controlling the running of Excel? You may have some questions. Well, let's look at the VBA statements.

 

1) Select "tool"-"macro"-"macro" to display the "macro" dialog box.
2) Click "change color" in the list and select "edit.

 

In this case, the VBA editor window (VBE) is opened ). For details about the editor, focus on the displayed code. The Code is as follows: (the date and name are different)

Sub change color ()
'
'Change the color of macro
'Xw record macro 2000-6-10
'

'
With selection. Interior
. Colorindex = 3
. Pattern = xlsolid
. Patterncolorindex = xlautomatic
End
End sub

The code will be very familiar in the future, although it looks like a strange foreign language now. Learning VBA or programming language is more like learning a foreign language to some extent.

Sub color change (): This is the macro name.

The five elements starting with "'" in the middle are called "Comments", which are automatically generated during macro recording.

The structure starting with and ending with is a with structure Statement, which is the main part of a macro. Note that the word "selection" represents the highlighted area (that is, the selected area ). With selection. Interior: It reads "inside the selected region". This entire statement sets "attributes" inside the region ".

Where:

. Colorindex = 3: Set the internal color to red. Note: There is a small dot, which is used to simplify the statement. The dot replaces the word that appears after the with, and it is part of the with structure. In addition, red is digitalized to 3. (can the red alarm be called "3 ?) If you are interested, change 3 to another number.

. Pattern = xlsolid: Set the internal pattern of the region. Because it is a recording macro, although you have not set this item, the macro still records it (because this item exists in the "pattern" option, but you have set it for it ). Xlsolid indicates solid color.

. Patterncolorindex = xlautomatic: indicates that the background color of the internal pattern is the automatic color.

End with: end with statement.

End Sub: Conclusion of the entire macro

1.6 edit the recording code.

 

In the previous section, we recorded a macro and checked the code. two sentences in the Code do not actually work. Which two statements? Now, make a modification in the macro to delete unnecessary rows until it is the same as the following code:

Sub change color ()
'
'Change the color of macro
'Xw record macro 2000-6-10
'

'
With selection. Interior
. Colorindex = 3
End
End sub

After that, experiment in the worksheet. You will find that the results are the same as those before the change. Add a line before the with statement:

Range ("A5"). Select

If you try to run the macro, no matter which cell you choose, the result of macro operation will make the A5 cell red.

Now we can see that the recording macro editing is also very simple. You need to edit macros for the following reasons. 1. You have to modify an error during recording. Ii. Redundant statements need to be deleted in the recorded macro to improve the macro running speed. 3. You want to add the macro function. For example, add statements that cannot be recorded, such as judgment or loop.

1.7 limitations of recording macros

Recording macros can be used to automate many Excel processes. However, macro recorders have the following limitations:

 

1) The recording macro has no judgment or loop capability.
2) the human-computer interaction capability is poor, that is, the user cannot input and the computer cannot give a prompt.
3) The Excel dialog box cannot be displayed.
4) The custom form cannot be displayed.

Conclusion 1.8

In this lesson, you have mastered some basic knowledge of VBA. You will record, edit, and understand the limitations of recording macros. you work very hard. it has laid the foundation for learning VBA, VB, and other programming languages in the future. the key is that you have learned the answer, that is, what programming is.