20150916_001 VBA Basics

What is the use of "macro" or "macro"?

For a detailed definition of "macro", you can refer to the explanation of Baidu Encyclopedia (click to view). I give it a simple, perhaps less rigorous, definition:

Popular definition of macros: macros are specific code/scripts that can be identified, understood, and executed by some software.

Macro role: Because the macro can be executed by the software, do not need to operate manually, so as to achieve "automatic, batch" business processing, greatly improve work efficiency.

Two, "Macro" is not Excel or office-specific

You know, macros are not unique to Excel, and other components of office, such as PPT, Word, Outlook, and so on, also have macros. Not only office, but also many software features macros. For example, the impression of PS can be a lot of pictures in batch cropping or add watermark processing, which can be regarded as a macro. Even the text editor EditPlus has macro recording capabilities. There are some software dedicated to the implementation of macro functions, similar to the "mouse Keylogger playback" as the keyboard operation recorded and re-playback, more well-known such as: Key Wizard, AutoHotkey and so on.

Iii. What does "macro" look like?

As described in the 2nd section, many software are macros, and different software macros are not the same. The following is a script for AutoHotkey:

#z:: Run www.autohotkey.com

::/note::

Ifwinexist Untitled-Notepad

Winactivate

Else

Run Notepad

Return

Macros in office are called VBA (Visual Basic for application). The VBA syntax is the same in different components (Excel/ppt/word, etc.), but the supported objects and methods are different. The specific code is shown in the following example.

Iv. Macros in the Vba:excel

Finally, I talked about Excel macros. VBA is basically VB (do not know what VB is?) Click this) of the simplified version, if there is VB Foundation, learn VBA is not laborious.

But after all, VBA still belongs to the "development" class work, do not have any programming experience of people (yards Nong) can learn VBA it? The answer is YES! As long as someone takes the door to give a nudge, and then willing to do some work, you will be able to write some of the VBA tools to improve productivity. While I was working in Sina, I had an intern who could revise the VBA tools I wrote, learn Chinese, and girls.

V. Where to write the Vba:excel VBE development environment

Let's make an Excel document with macros. Detailed steps:

1. Create a new workbook, save file as, "file name" ENTER "Macro Test", in "File type" select "Excel Enable Macro Workbook (*.xlsm)", click Save.

2. Press the ALT+F11 key to bring up the VBE window. Right-click on "VBAProject", insert-and-module. Then, "Module 1" appears in the left window.

3. Double-click "Module 1" and enter the following code in the blank area on the right (this code is used to display a text prompt box on the screen) to complete the editing of the VBA macro. See:

Sub Vbatest ()

MsgBox "This is my 1th VBA program. "

End Sub

Note: In order to use more features about macros, it is a good idea to set the Developer tab in Excel. Excel2007 and 2010 Show the Development Tools tab is set up in the following ways:

EXCEL2007:

Click on the Office button in the upper left corner of Excel, click the "Excel Options" button at the bottom of the pop-up menu and check "Show Developer tab on the Ribbon" in the "common" tab to confirm.

EXCEL2010:

Click on the file-to option, locate the "custom Ribbon" in the Excel Options box, and on the right, tick "developer tools" to confirm.

After the above settings, you can see the "Development tools" tab on the main Excel window, there are many features about macro development in the open. For example, clicking on the leftmost "Visual Basic" button also goes into the VBE development interface, just as it does with alt+f11.

Vi. How to run VBA macros

So far, we have successfully written a VBA macro, do you want to see the results of this macro execution? Next explore how to run the macro, Jeanhong work properly. There are usually several ways to do this:

1. Run directly from the development environment. Place the cursor between the Sub/end Sub, press the Green Triangle button on the menu, or the F5 key.

2. Call through the Shape button. On the worksheet, click Insert, insert a rounded rectangle, enter call macro, and simply format:

Right click on the "Call macro" shape, select "Assign Macro" in the pop-up menu, and in the "Specify Macro" dialog box, select the macro you just wrote (name is vbatest), OK.

Click on any cell in the worksheet (the selection of the Cancel button), then mouse over the button will become the shape of the small hand, click on the results of this macro run:

3. Shortcut key call. You can set a shortcut key for a macro, set it correctly, and then press the shortcut key to run the macro. The method is:

On the Developer tab, click the "Macro" button, select "Vbatest", tap the "Options" button on the right, set a shortcut key, OK.

Note: The shortcut keys set here have a high priority, and if they conflict with the software's original shortcut keys, they will overwrite the original.

Vii. set up password protection for the project

We have written a good macro, in order to prevent being arbitrarily modified, you can set the password. Before you can go into the code editing interface to modify. How to do: in the upper left corner of the list tree right-click on the "VBAProject ...", in the pop-up menu select "VBAProject Properties" open the dialog box, select the "Protection" tab, tick "lock project when viewing", and set the password to view the project properties, click "OK". Such as:

20150916_001 VBA Basics