VBA tutorial
1.1 VBA is
the early 1990s until 90, so that the application is still challenging automation field. For each application to be automated, people have to learn a different language automation, such as: EXCEL macro language can be used to make the EXCEL automation, to make use of WORD BASIC WORD automation, etc. Microsoft has decided to make it an application developed to automate shared a common language ——– Visual Basic For Application ( VBA), that VBA is a very popular application development language VASUAL BASIC subset is actually VBA is 1. VB is designed to create a standard application, and VBA is to make existing applications (EXCEL, etc.) automation
2. VB has its own development environment, and to be parasitic on the existing VBA applications.
3. VB developers to run applications, users do not install the VB, because VB to develop applications that are executable files (*. EXE), and the VBA development process must rely on its applications, such as EXCEL.
Despite these differences, VBA and VB is still very similar in structure. In fact, if you already know VB, VBA will find learning very fast. Accordingly, the study will complete learning VBA VB lay a solid foundation, and, when the Institute created in EXCEL using VBA solution, ie with the WORD ACCESS OUTLOOK FOXPRO PROWERPOINT using VBA to create solutions to most of the knowledge.
* VBA A key feature is that you knowledge learned in some of Microsoft’s products can be transformed into each other.
* VBA EXCEL can be called a It can be used to automate the process so that you can create a custom solution.
In addition, if you like, you can also use EXCEL to do the application development platform.
1.2 EXCEL environment based on the application of automation Perhaps you want to know the advantages
VBA can do? using VBA can be achieved include:
1. to automate repetitive tasks.
2. EXCEL custom toolbars, menus and interfaces.
3 Simplify the use of templates.
4. Custom EXCEL, making it a development platform.
5. create reports.
6. the complexity of data operations and analysis.
using EXCEL as the development platform for the following reasons:
1. EXCEL itself powerful, including printing, document processing, formatting and text editing.
2. EXCEL built a large number of functions.
3. EXCEL interface familiar.
4 can be connected to multiple databases.
develop applications in other languages, half of the work is to write some basic functions of the module, including opening and saving files, printing, copying, etc. The use of EXCEL as a development platform, the As EXCEL already have these basic features, you have to do is use it.
1.3 recording simple VBA macro
in the learning, you should take a few minutes to record a macro.
new term: Close.
2) Select the cell A1. out of ) enter Macro name is to facilitate each of these main macro.
macro name can be up to 255 characters and must begin with a letter in which the available characters include: letters, numbers and underscores. spaces are not allowed in macro names. usually representative of a space with an underscore.
5) Select the > 6) Click ; stop recording You will be surprised that she in the absence of these .
2) Select ;, the A3 cell color changes to red and try to select another cell area consisting of several cells, and then execute the macro office 2007 activation key, in order to deepen the impression.
1.5 to view the recorded code
in the end what is in the control of EXCEL running? you may have some doubts. well, let’s take a look at the VBA statement right.
1) Select ; dialog box.
2) Click the list in the , later described in detail, focusing first on the code to display. code is as follows: (date and name will be different)
Sub change color ()
‘
‘ change color Macro
‘xw recorded macro 2000-6-10
‘
‘
With Selection.Interior
. ColorIndex = 3
. Pattern = xlSolid
. PatternColorIndex = xlAutomatic
End With
End Sub
the future will be very familiar with this code, although now they seem like a strange foreign language, learning VBA or programming language in a way more like learning a foreign language.
Sub change color (): This is the name of the macro.
the middle of the With the end of the structure With structure statement, this statement is a major part of the macro Note the word as small dots, its role is to simplify the statement, instead of a small dot after the word in With, which is part of the structure With addition: Red-figure into a 3 (it can be called Red Alert: 3 alert, ah? ) are interested, you will be 3 to try other numbers.
. Pattern = xlSolid: set pattern within the region due to the macro is recording, so, although you did not set this one, the macro continues to be recorded (as in the br> End With: With the end of the statement.
End Sub: Conclusion
1.6 the macro edit code
recorded in the previous section, we record a macro and look at the code, the code has two actually does not work. Which two? now, to make a change in the macro, delete the extra line, up to and following the same code:
Sub change color ()
‘
‘ to change the color of Macro ‘xw recorded macro 2000-6-10
‘
‘
With Selection.Interior
. ColorIndex = 3
End With
End Sub
completed, try out the worksheet you will find the situation before the results and modify the same in the With statement added before the line:
Range (which the cell, the results are the macro cell A5 red.
can now see and edit the recorded macro is also very simple. need to edit the macro because the following three reasons. A: error in recording had to be modified. II: recording macro to delete a redundant statement, to improve the macro run faster. three: want to increase the macro functions, such as: Add judgments or circulation can not record the statement.
1.7 Record Macro limitations
want to automate many of the EXCEL process can be used to record most of the macro to complete, but the following limitations of the macro recorder by the macro recorder can not complete the work:
1) determine the recorded macro without or cycling capability.
2) poor human-computer interaction discount windows 7 key, the user can not enter, the computer can not give tips.
3) can not be displayed EXCEL dialog box.
4) can not display the custom form.
1.8 Summary
this lesson, you have mastered the basics of VBA, you record macros, edit macros and understand the limitations of recorded macros you very hard. and have been learning VBA or VB for the future laid the foundation for other programming languages. The key is you have to understand a mystery cheap windows 7 key, that is, you know what is the programming. Here are some small exercises, done before they can play yo.
2.1 Assign a shortcut key for the macro You may want to specify macros for frequently used shortcuts. shortcut key combination is the key, when it is pressed to execute a command, for example: CTRL + C
in many programs on behalf of When specifying a shortcut to the macro, you can use shortcut keys to execute macros without going through the EXCEL default shortcut keys, for example: CTRL + C to assign a macro, then CTRL + C will no longer perform the copy command using the following method to print out the list of EXCEL shortcut keys (printed on A4 paper 24 pages long ):
1) open the EXCEL help file and select the > 3) Right-click the title, from the shortcut menu, select specified when creating a macro shortcut key, you can also create and then specify. to create (record) specified when the macro shortcut key, simply record a macro in the macro name entered after the Enter the appropriate key. After recording a macro shortcut key specified is also very simple, just select ; Options (Only open the workbook, the macro is available.)
2) a new workbook.
3) Personal Macro Workbook.
2.3 Personal Macro Workbook
Personal Macro Workbook, is designed for the macro with a special auto-hide feature of the workbook. the first time create a macro to Personal Macro Workbook, it will create a directory called EXCEL is started automatically when the file open and hidden behind the active workbook (in the macro can be used in multiple workbooks, then it should create a Personal Macro Workbook, and save the macro in it. Personal Macro Workbook saved in Profiles Application Data Microsoft Excel XLSTART. can the word In XLSTART folder.
2.3.1 to save the macro to the personal macro workbook
this exercise, a simple macro will be saved to the Personal Macro Workbook, the macro for the text underlined and italics, steps are as follows:
1) create a file called box.
2) Enter Click Because EXCEL in creating the workbook.
6) Click the > have just saved a macro to the personal macro workbook, the workbook can now be used in any of the macro according to the following steps:
1) Close all EXCEL workbook.
2) Any open an EXCEL file. (EXCEL Personal Macro Workbook will automatically open at a time and hide.)
3) In A3, enter your name.
4) Select box now can see the list of macros word is also underlined. Select Text small problems.
2.4 assign the macro to the button
even if the macro shortcut keys can perform faster, but once more the number of macros are also difficult to remember, and, if the macro is used by other people, do not You have to remember they do so many shortcuts?
as EXCEL developer, a major goal is to provide an automated interface for easy operation. By using the this exercise, you create a button and assign it a macro, then use the button to execute the macro. concrete steps are as follows:
1) Open Forms Press the left mouse button, drag the mouse to draw a rectangle that represents the size of the button. on the size of the satisfaction and release the left mouse button, a command button is added to the worksheet, while EXCEL automatically display Assign Macro
6) the title of the button position, and now the title of the button from the default the macro.
the button when the mouse to automatically become a hand, if you want to change the size or title, simply right-click the button settings can be modified and it is clear, you no longer need to remember the name of the macro or shortcut key, the touch of a button.
2.5 assign the macro to the picture or other object
To perform a variety of ways to select the macro, you can assign the macro to the button and other controls, You can also specify to the pictures, custom toolbars, macros can even specify the form to an the picture is very simple, just click a picture, click on the shortcut menu in the method can be selected: the macro assigned to the ; Customize Toolbar br> 3) the 5) Select the Enter the text box, click the toolbar button to run the macro.
2.6 Summary
Summary and thinking: the macro stored in three possible locations. Personal Macro Workbook storage location and characteristics of the implementation of macro mode. Specify the macro event for an object to specify a program, once the object to activate the event, the system will run the specified program.
commonly used objects: workbook, worksheet, range, cells, charts, pictures, pivot table, controls, forms, toolbars, each object has its own special event can respond to (there are some common events such as clicking or double-click, etc.) If you are interested, you can check these EXCEL help file entry. in EXCEL see almost all belong to an object, and in a lot of work done in EXCEL, such as a mouse click, etc., may trigger an event. the next hours we will learn together
3.1 EXCEL About
development process the following questions need to have a rough idea.
1) Who determines which program to use —– difficulty of the operation and interface feel.
2) data sources and stored in the program where this decision —– structure.
3) —– This will determine how the program’s interface and details.
4) the results of data processing - — the value of the final decision-making process.
3.2 recognize the beginning of a different control
Close all workbooks, open a new workbook and save it as from the shortcut menu, select static text.
2) group box: It used to be a combination of other controls.
3) button: used to execute macro commands.
4) check box: it is a choice control, single Click to select and deselect a number of options you can.
5) option button: button, several options are usually used in combination, in a group can select only one option button.
6) list box: used to display and select from several options. only radio.
7) combo box: used to display and select from multiple options can be selected among other items, or enter a value.
scrollbar: not your usual to add a form to a long roll control capability, but a selection mechanism such as adjusting the color of the scroll bar controls the transition, including the horizontal scroll bar and vertical scroll bar.
9), fine-tuning control: is a value selection mechanism, the control by clicking the arrow to select value, such as changing the Windows or time period will be used to fine-tune control.
3.3
to add controls to a worksheet with the EXCEL design interface is simple, to add controls to a worksheet, you can follow these steps:
1) Create a new workbook and save it as > 2) Select On the left mouse button. If you want to control size and easy to control, you can create the control, press ALT drag.
5) Right-click on the label 1, select When finished, click any cell to exit the text editor.
6) Through the above steps can add other controls to a worksheet, not repeat them.
3.4 Set control properties
set control properties, can Follow these steps:
1) previously selected check box control to create, if not immediately create a.
2) Right-click the control, select the ) in the of the control is selected again click the control, A1 appears FALSE.
6) Select the slider control you just created. and bring up the ; Cell link arrow, then increase the value of A1, then A1’s value continues to click continue to increase.
10) Save and close the workbook.
3.5 to create a control named
When control automatically to EXCEL It specifies a name, but not easy to understand and remember the name of the method to control the basic and to the cell or range name in the same way. select a control, and then in the formula bar is located in the Enter the name of the control box, so that gave control to change the name.
3.6 user form
If you want to create professional-level applications, and user input data, you should use UserForm The user can form As the program dialog boxes and windows. add controls to the user the basic form is similar to add controls to a worksheet, but the first step is to create a user form, which can be achieved through the VBA editor Specifically, follow these steps:
1) Open the form, is selected, then the state will remain pressed and if click again, it reverts to the state of not pressed. EXCEL toolbar has several such buttons, such as: in bold, tab control, usually used to organize related information or classification, for example: you may want to use the tab bar to display the sales information of each region, each region can be set to a tab in the default , tab contains two pages, named TAB1 and TAB2, you can add more tabs.
C) pages: looks like a tab bar that contains one or more pages of control. options gives the appearance of similar cards, and multi-page controls the page contains different controls each have their different layout. pages of many examples, such as: menu image format shall be: *. bmp, *. cur, *. gif, *. ico, *. jpg, *. wmf.
F) RefEdit: This is the toolbox by default the last control it look like a text box, users can control through the folded form, in order to select the cell range. Remember the use of fx features, it does take time, but do not have to memorize.
designed in the form of user satisfaction, you can preview them office 2007 ultimate key, is in the VBA editor, select the form, click ; Run on the button on the recorder. Another method is to run the form by pressing F5.
Summary: After completing this class hours, we have the interface design for the basics of the program, we are not unfamiliar with the controls, but also understand How to add controls to a worksheet and form, but the contents of the control, we need to understand side by side since then, we learn from the interfaces to write code, and will eventually integrate the two. so we’re ready to learn to program it! 3.7 Troubleshooting Question 1. how to determine the location of control? How to choose Add to add to a worksheet or a UserForm? Answer: This depends entirely on the personal preferences of users and applications if the user very familiar with the EXCEL, then they may prefer to manipulate the worksheet in this case may wish to directly create the control on the worksheet; if your users are not familiar with EXCEL or you need to interface to the user a feeling of professional, you should use the user form. Question 2. what circumstances the use of tabs instead of multiple pages of controls? answer: If each page has the same layout, you should select the tab bar, or should choose a multi-page. 4.1 code exists here: the module VBA code must be stored in a location, this place is the module, there are two basic types of modules: standard modules and class modules each module a process or function, process or subroutine concept last part of this lesson will discuss the function of the difference between processes and subroutines. new term: module: it is stored together as a unit definition and VBA process of collection. class module: VBA allows you to create your own objects, object definitions contained in the class module. the majority of your work focuses on the standard module (referred to as modules) When you record a macro if the module does not exist, EXCEL automatically create a. EXCEL and VBA do not care about code stored in a module which, as long as the code exists in the workbook can be opened. 4.2 Overview of the module is defined as the process of VBA code of a unit, the process includes a series of tasks for performing a calculation or some kind of statement. workbooks each process has a unique name to distinguish. There are two different processes: subroutine and function procedures. subprogram perform only one or more operations, but not return values when recording a macro view the code, see that subroutine. macro subroutine can be recorded, but not the process of recording function. example of a subroutine shown in Listing 4-1. subroutine in Listing 4-1 Examples of Sub cmdSmallFont_Click () With Selection.Font . Name = br>. FontStyle = is an event procedure. The process name is the name of an object and an event CmdSmallFont Click the name consisting of, separated with underscores between the two, if not understand, can tell you, CmdSmallFont is a command button name. In other words, when you click the command button, it will run this event procedure. function process usually referred to as the function to return a value. This value is usually the result of calculations or test results such as False or True. as I said before, you can create custom functions using VBA in fact you can use your worksheet to create a function in Listing 4-2 is a calculation of 10% of the price of shipping a simple example. Listing 4-2 Simple example of user-defined functions. Public Function Shipping (Price) Shipping = Price * 0.1 End Function Please note that this function uses a parameter (Price ). subroutines and functions can use parameters regardless of the value of Price is how much freight it will determine the amount. Price can be a number and cell references. function returns the calculated shipping, this function can be used in the cell. AB 1 Price 100 2 Shipping = shipping (B1) 4.2.1 creation process to create the first process requires two basic steps. First, you need to add a workbook module then the module needs to add a project for creation of every application, only …Related Articles:
PPC ultimate tutorial ( a )
InDesign CS3 in the tutorial
XP mastering detailed tutorial
Access2003 version of the application of basic tu
