VBA and macros

VBA and Macros
======
The beginning of the content sounds very dull, please do not distract, resistance to look down, interest is always slowly accumulated.
------------------------------------------------------------------------------
1, hand in hand, record a part of their own macro
Referring to VBA, I believe many people think of the macro, but VBA and macro is not the same thing? I think they're not the same thing, but it doesn't matter what we're going to do is get to know the macros first.
What is a macro?
I understand that a macro is a set of fixed actions, which are executed one after the other, when the conditions that let him execute are met.
Some people compare our recorded macro to a martial arts athlete's martial arts routine, which meets certain conditions (matches).
OK, don't idle, move your lovely right hand, click the mouse, open an Excel file, record a macro to study the research, the steps are as follows:
　　 (1) Select any cell;
(2) Open the menu, tools, macro, record a new macro, bring up the Macro Record dialog box, enter a macro name (mysub), OK;
(3) Set the cell font to Fangsong, red;
　　 (4) Click Stop Recording Macro.
In this way, a simple set of cell format macro is recorded.
--------------------------------------------------------------------------------------------------------------- ----
2, mouse click a point, Macro to change the new clothes cell
Let's execute this macro.
　　 (1) Select any cell or range of cells you want to set (in order to see the effect, the cell preferably has characters);
(2) Open menu, tools, macro, macro, Display Macro dialog box (shortcut: ALT+F8);
(3) Select the macro that we just recorded and click "Execute".
This way we see that the font already selected for the cell has been modified to Fangsong, red. Try to record a few macros by yourself, then execute them and deepen your impressions.
Amazing, huh? When you need to repeat some of the same actions again and again, do you think Jean Honglai will do it for you? This is a good slave, no money, and no pay, earn it?
Well, put away your greedy smile and keep looking down.
What is a macro? Now I believe you can come up with 1000 kinds of statements, by you, stupid and lazy maple can not you smart, also no more say.
Does the process of executing macros make you feel very troublesome? Want to use shortcut keys to control macros? This can be set in the Record Macro dialog box before recording the macro. You can also set it after recording a macro, press Alt+f8 to display the Macro dialog box, click Options, and set it in the Macro Options dialog box.
--------------------------------------------------------------------------------------------------------------
3, Root to ask the bottom, find the nest of the macro
If you forget, please open the macro Record dialog again, under the macro name, there is a "save in" option, point to drop the arrow, we can see that the macro can be saved in three different locations, namely:
　 (1) Current workbook (system default): Macros are saved in the current workbook module, and macro works only when the workbook is open.
(2) New workbook: Create a new workbook to save.
(3) Personal Macro Workbook: This is a special workbook designed for macros that has auto-hide features, and if you want to make a macro available in multiple workbooks, you should create a Personal macro workbook and save the macro in it.
The macro we recorded above is saved in the current workbook, and today we're only going to show you the macros that are saved in the current worksheet.
--------------------------------------------------------------------------------------------------------
4, brush the layers of fog, see the Truth
What does a macro do to control the operation of Excel? is not feeling deep in the thick fog, feeling confused? Let's cheer up, push it out of the clothes and open the guy.
　　 (1) Press Alt+f8 shortcut key to open the Macro dialog box;
(2) Select the macro that we just recorded, click the "Edit" button.
Is it a light? This is a new world.--vba Editor Window (VBE), which we'll talk about later, focus on the displayed code first. The code is as follows:

1. 
   
2. Sub mysub ()
   
3. ' MySub Macro
   
4. ' Macro recorded by Ggsmart, Time: 2009-2-27 '
   
5. With Selection.Font
   
6. . Name = "Imitation _gb2312"
   
7. . Size = 12
   
8. . Strikethrough = False
   
9. . Superscript = False
   
10. . Subscript = False
    
11. . Outlinefont = False
    
12. . Shadow = False
    
13. . Underline = Xlunderlinestylenone
    
14. . ColorIndex = xlautomatic
    
15. End with
    
16. Selection.Font.ColorIndex = 3
    
17. End Sub
    

Copy Code

Now you may feel that these messy code will be very strange, even ask yourself whether to come to the world of aliens, anyway Feng originally felt like this, but do not feel afraid, in the future we will slowly familiar with him, like close the eyes can be accurate to the big red apple to his mouth.
--------------------------------------------------------------------------------------------------------
5, further, to the macro to install the doorbell
Over and over again in the Macro dialog box to select the macro name, press the "Execute" button to run the macro trouble is not let you depressed some want to vomit blood? It's easy to specify shortcut keys, but can someone use their worksheets quickly? Even if you are, if the macro is more, or put a long time, you remember which shortcut key control which macro?
As an Excel developer, one of the main goals is to provide an easy-to-use interface for automation, a "button" is one of the most common interface components, we can assign a macro to a specific button, execute a macro with a button, or use the macro example we just recorded. You can add buttons to worksheets in a workbook by using the Forms toolbar. After you create a button, you can assign a macro to it, and then your users can click the button to execute the macro. In this exercise, you create a button, assign it a macro, and then use that button to execute the macro. The steps are as follows:
　　(1) Open the Form toolbar, which is a toolbar, such as a menu, view.

(2) Click the button control on the Forms toolbar, press the left mouse button where you want the button to be placed on the worksheet, drag the mouse to draw a button, and when you release the mouse, Excel automatically displays the Assign Macro dialog box.

(3) Select MySub from the Specify Macro dialog box and click OK. In this way, the macro is assigned to the command button.

recess, break a section of ads: buttons, worksheets, etc. these objects are like many children of Excel, children too much, how to call this group of children is also a problem. In order not to appear called "Son" on seven or eight to run out together, with many rural parents to give children up "eldest, second, old three ... , Excel also labeled the children with this sequential naming method. For example, the first button is "button One", then draw one is "button two", then "button three", "ammonium Shi" and so on. This way, you will call the third son when you call "button three". If you think the name of the country is too strong, if you do not like the name is not very good to reflect the operation is not clear after clicking on it, you can double-click the button surface to activate it, you can re-paste the button to the appropriate display label. To remind you that the label here is just what it looks like in front of you, what is it actually called? We can click on it to see what is in the Name box. 　

　

Why does it show the same, and the actual name is the other one? Perhaps you will feel confused now, but do not care about it, later, when we learn the attributes, everything will naturally understand.

Let's try a trial button operation:
(1) Select a cell or range of cells;
(2) Click the button.
　　　　

button is like the doorbell downstairs, the beautiful Chang ' e Fairy lives in 502, Pig came downstairs, to find the number 502, gently a press, "Ding-dong, ding-dong ...", Chang ' e in the upstairs to the eight quit the door, eight commandments do not have to shout loudly downstairs "Chang ' e, open the door." "Also do not spend two cents to the Chang ' E to make a phone call, of course, not to follow the water from the wall climbed into the window of the Chang ' E, convenient and real?
This remote command does make cumbersome operations simple and convenient, allowing us to get some functionality that cannot be achieved using Excel standard commands. If you are an eight-ring, when you are familiar with how to use the same kind of remote doorbell, you may be surprised that they did not have the doorbell, the long time to climb the sewer line how to survive the day.
There are many ways to open the door for the Chang ' e Fairy, there are a number of ways to do the same macro can be selected, we can also assign a macro to pictures, AutoShapes, these are relatively simple, and assigned to the form button is the same, and we can also assign a macro to an "event", such as clicking on a worksheet, double-click a Open the workbook and so on, you want to Ann what the doorbell, choose what kind of music, with you.
It should be emphasized that the "incident" we have just said is an important concept, the eight rings rang the doorbell, is an "event", this "event" triggered the opening of Chang ' E, only the doorbell rang, Chang ' e will go to open the door. Besides "method" "Object" "attribute" is the next we will often come into contact with, slowly walk, pay attention to point, along the way will be wonderful unceasingly.
-----------------------------------------------------------------------------------------------------------
6. Summary
Here, we should have a simple understanding of the macro, right? A macro is actually a simple VBA Sub procedure that is saved in a module, starts with a sub, ends with an End Sub, executes from the first sentence, and executes until the end sub ends. As we said before the martial arts athletes to practice that part of the routine action, always embrace, zama, out fist and then kick the leg, never can not zama on the punch, no punch kicked the leg. The macro that we recorded today also always first set the font, and then change the font color, this order will never mess.
After all this, do you feel that these operations are not so old-fashioned? Do you feel that the operation of this macro does not meet your needs?
Feng want to tell you is, the macro code is not equal to VBA, it is only the simplest use of VBA, although many Excel procedures can be used to record macros to complete, but through the macro code is still unable to complete a lot of work, such as:
(1) Can not establish formula, function;
(2) No function of judgment or circulation;
(3) Human-computer interaction is not possible;
(4) The user form cannot be displayed;
(5) cannot interact with other software or files.
... ..

So, you look at the above content do not think that the macro is VBA, not to think that they have VBA, in fact, you just touch on the edge of VBA, or even the edges are not stained, and the macro today we are just a simple introduction.

Long way of its repair, but also waiting for us to seek, the road at the foot, martial arts master always from the practice of simple routines to start, do not be tired of zama monotonous, quiet heart more practice, this will be a foundation for you to practice martial arts, Confucius said, to warm so know new, remember to review the consolidation, do not learn the back , the next time Feng See you when you forget everything, well, today to come here, let's cheer ~ ~ ~ ~

VBA and macros