Open the "development tools" tab

Macro and Visual Basic Editor

Since you have some knowledge about how office 2010 applications publish their object models, you may want to try to call object methods, set object attributes, and respond to object events. To do this, you must write code in a location that can be understood by office. The Visual Basic Editor is usually used. Although the editor is installed by default, many users do not know the existence of the editor before it is enabled in the functional area.

Open the "development tools" tab

All office 2010 applications use the functional area. There is a "development tool" in the functional area"Tab, where you can access the Visual Basic Editor and other developer tools. Because office 2010 does not display "development tools" by default"You must use the following procedure to enable this tab:

Enable the "development tools" tab

1. In the "file"On the tab, select "option"To enable "option"Dialog box.

2. Click "Custom functional area" on the left side of the dialog box".

3. On the left side of the dialog box, select a command from the following locations"Under, select "Common commands".

4. In the "Custom functional area" on the right of the dialog box"Under, select "Main tab" from the drop-down list box"And then select "development tools"Check box.

5. Click OK".

Note:
In Office 2007, the "development tools" tab is displayed by clicking the Office button and clicking "options", And then in the "option"Common Dialog Box"Select the 'development tools' tab on the functional area in the category"Check box

Enable "development tools"Tab, you can easily find the "Visual Basic"And "macro"Button.

Figure 1. buttons on the "development tools" tab

Security Questions

To protect office 2010 users from viruses and dangerous macro code, you cannot save macro code in standard office 2010 documents that use the standard file extension. The code must be saved in a file with a special extension. For example, you cannot store Macros in standard word 2010 documents with the extension. docx. Instead, you must use special word 2010 documents with the extension. docm and macros enabled.

When the. docm file is opened, the Office 2010 security feature may still block macro operations in the document and notify you or not to notify you. Check the settings and options in the trust center of all office 2010 applications. Macros are disabled by default, but you are notified that macros are disabled and the option to re-enable macros for this document is provided.

You can create a "trusted location", "Trusted Document", or "trusted publisher" to specify a specific folder where macros can be run. It is best to use the "trusted publisher", which applies to the digital signature document that you distribute. For details about security settings in a specific office 2010 application, open the "option"Dialog box, click "Trust Center"And then click "trust center Settings".

Note:
By default, some office 2010 applications (such as Outlook 2010) Save macros to the master template on your local computer. Although this policy reduces local security issues on your computer when you run your own macros, if you want to distribute your macros, You need to deploy the policy.

Recording macro

Click "development tools""Macro" on the tab"The "macro" is opened"Dialog box that allows you to access the VBA subroutine or macro that you can access from a specific document or application. "Visual Basic"Button to open the Visual Basic Editor, where you can create and edit the VBA code.

"Development Tools" in Word 2010 and Excel 2010"Another button on the tab is "Recording macro"Button, which automatically generates VBA code that can reproduce the operations you perform in the application. "Recording macro"It is a great tool to learn more about VBA. Read the generated code to gain a deeper understanding of VBA and build a solid bridge between understanding office 2010 as a user and understanding the software as a programmer. The only thing that needs to be pointed out is that the generated code may be confusing, because the macro editor must make assumptions about your intent, and these assumptions are not necessarily accurate.

Recording macro

1. Open a new workbook in Excel 2010, and click development tools in the functional area"Tab. Click "Recording macro"And accept "Recording macro"All default settings in the dialog box, includingMacro1As the macro name, SetCurrent workbookAs the storage location.

2. Click OK"Start recording macros. Note how the button text changes to "stop recording". Click the button after you complete the operation.

3. Click cell B1, and then type the programmer's first classic string:Hello World. Stop typing and view "stop recording"Button, because Excel 2010 is waiting for you to finish typing a value in the cell.

4. Click cell B2 to complete operations in cell B1, and then click "stop recording".

5. Click "development tools""Macro" on the tab", Select "macro1"(If not selected), then click "edit"View the macro1 code in the Visual Basic Editor.

Figure 2. macro code in Visual Basic Editor

View code

The macro you created should be similar to the following code.

Vbc # C ++ F # JScript Replication

Sub Macro1()'' Macro1 Macro''    Range("B1").Select    ActiveCell.FormulaR1C1 = "Hello World"    Range("B2").SelectEnd Sub

Measure the test taker's knowledge about the similarities and differences between the code segment of the text in cell A1. In this Code, select cell B1 and apply the string "Hello World" to the activated cell. The quotation marks on both sides of the text specify the string value relative to the value.

Do you still remember how to click cell B2 to display "stop recording" again"Button? This operation is also displayed as a line of code. The macro recorder records each key-click.

A line of code that starts with an marker and marked green by the editor is a comment that describes the code or reminds you and other programmers of the purpose of the code. VBA ignores any row or part of a row starting with a single quotation mark. It is very important to write clear comments in the code, but this discussion is not within the scope of this article. References to this code later in this Article do not include the four annotation lines.

When the macro recorder generates code, it uses complex algorithms to determine the methods and attributes you need. If you cannot identify a given attribute, there are many resources to help you. For example, in the macro you recorded, the macro recorder generates a referenceForumular1c1Property Code. Not sure what this means?

Tip:
Please note that you should knowApplicationObjects are hidden in all VBA Macros. Each line of your recorded code startsApplication..

Help with development tools

In the recording macro, selectForumular1c1And then Press F1. The help system will run a quick search, and confirm that the corresponding topic is located in Excel 2010 "development tools" section of Excel 2010 "help", and then listFormular1c1Attribute. You can click the link to learn more about this attribute.Excel Object Model ReferenceThe link is located near the bottom of the window. Click the link to view the long list of objects that excel 2010 uses to describe the worksheet and its components in its object model. Click any of the objects to view the attributes and methods that are applied to a specific object and to cross-reference other related options. Many "help" entries also contain short code examples that can help you. For example, you can accessBordersObject To learn how to set borders in VBA.

Vbc # C ++ F # JScript Replication

Worksheets(1).Range("A1").Borders.LineStyle = xlDouble

Edit code

The border code looks different from the recording macro. The confusing thing about the object model is that you can use multiple methods to process any given object (cell A1 in this example ).

Sometimes, the best way to learn programming is to make minor changes to some running code and then view the results. Try now. Open macro1 in the Visual Basic Editor and make the following changes to the Code.

Vbc # C ++ F # JScript Replication

Sub Macro1()    Worksheets(1).Range("A1").Value = "Wow!"    Worksheets(1).Range("A1").Borders.LineStyle = xlDoubleEnd Sub

Tip:
Copy and paste code as much as possible to avoid typing errors.

You can try it without saving the code. Return to the Excel 2010 document and click "development tools""Macro" on the tab", Click "macro1"And then click "run". Cell A1 now contains textWow!And there is a two-line border around it.

Figure 3. Result of your first macro

You have just recorded a macro, read the object model document, and developed a VBA program that can execute an operation through simple programming. Congratulations!

Macro not running? Read the debugging suggestions in VBA.

Programming tips and tips start with examples

The VBA community is very large. searching on the Web almost always gives you examples of VBA code similar to the operations you want to perform. If you cannot find a good example, break down the task into smaller units, search for each unit, or consider more common but similar problems. You can save hours from the example.

This does not mean that writing strict free code is waiting for you to use on the web. In fact, some codes you find may have defects or errors. However, the examples you find online or in the VBA document can provide you with a good start. Remember that learning programming takes time and consideration. Before you are busy using another solution to solve your problem, please ask yourself if VBA is the correct choice to solve this problem.

Easy to handle

The programming process may quickly become complicated. It is important to break down the problem into the smallest possible logical unit and then write and test each unit independently, especially for beginners. If you have too much code in front of you and you become confused or confused, stop and leave the problem blank. When you face the problem again, you can copy a small problem to the new module to solve the problem, let the code run, and test it to ensure that it can run. Then move to the next part.

Defects and debugging

There are two main types of programming errors:SyntaxErrors, that is, violation of the syntax rules of the programming language;RuntimeError, that is, it seems that the syntax is correct, but fails when VBA tries to execute the code.

Although it may be frustrating to fix these errors, syntax errors are easily captured; if you type a syntax error in the Code, the Visual Basic Editor will beep and flash.

For example, in VBA, the string value must be enclosed in double quotation marks. To learn what happens when using single quotes, go back to the Visual Basic Editor and paste "wow!" in the code example! "Replace string with 'Wow! '(That is, convert wordsWowEnclosed in single quotes ). If you click the next line, the Visual Basic Editor will respond. The error "compile error: expected: expression" (compilation error: expected: expression) is not helpful, but the row that generates this error turns red to inform you that there is a syntax error in this row. Therefore, this program will not run.

Click OK"And then change the text back"Wow! ".

Running errors are hard to be captured because the programming syntax looks correct, but the Code fails to be executed in VBA.

For example, open the Visual Basic Editor andValueProperty name changedValuex, Intentionally introduced runtime errors becauseRangeThe object does not have a property named valuex. Return to the Excel 2010 document and open "macro"Dialog Box to run macro1 again. You should see a visual basic message box indicating a running error. The error text is "Object doesn' t support this property of method" (the object does not support this property or method ). Although the text is clearly explained, click debug to view more information.

When you return to the Visual Basic Editor, the editor is in a specific debugging mode, that is, highlight failed code lines in yellow. As expected, the rows including the valuex attribute are highlighted.

Figure 4. Running visual basic debugging program

You can change the running VBA codeValuexChange backValueAnd then click debug"The green play button in the menu. The program should be able to run normally again.

It is better to know how to use debugging programs with greater care for longer and more complex programs. You should at least understand how to set breakpoints to stop code execution when you want to view the code, and how to add monitoring points to view the values of different variables and attributes when the code is running, and how to complete the code step by step. All these options are in "debug"In the menu, and the debugging program users often remember the keyboard shortcut.

Proper use of reference materials

To open the built-in developer reference in office 2010 help, click the question mark in the functional area or Press F1 to open the help reference from any office 2010 application. Then, in the "Search"Click the drop-down arrow to filter the content. Click "Developer reference". If you do not see the content in the left pane, click the small book icon to open it, and then expand the object model reference.

Figure 5. The screening function on the development tool help applies to all office 2010 applications

The time spent in browsing the object model reference will be rewarded. After you understand the basic knowledge of the VBA syntax and Object Model of the office 2010 application you want to use, your skills will be improved, you will shift from subjective speculation to method-based programming.

Of course, the Microsoft Office Developer Center is an excellent portal for reading articles, tips, and community information.

Search forums and Groups

All programmers may encounter difficulties from time to time, even after reading every reference article they can find. They will suffer from insomnia at night because they are thinking about solutions to problems. Fortunately, the Internet has promoted the development of the developer community that helps each other solve programming problems.

Several discussion groups are displayed when you search for "office developers Forum" on the Internet. You can also search for "office development" or problem descriptions to find forums, blog articles, and articles.

If you have done your best to solve the problem, do not be afraid to post your problem to the developer forum. These forums welcome posts from newer programmers, and many experienced developers are happy to help.

The following rules must be observed when posting content to the developer forum:

• Before posting, you should find the FAQ or guidelines that Forum members want you to follow on the website. Ensure that the posted content complies with these guidelines and is located in the correct area of the Forum.

• Including clear and complete code examples. If your code is part of a long code segment, consider editing your code to elaborate on others.
• Describe your problem clearly and accurately, and give an overview of all the steps you have taken to resolve the problem. Spend some time writing good posts, especially when you are in a hurry or in a hurry. Introduce the situation in a meaningful way for the readers who read the question statement for the first time.
• Be polite and express your gratitude.

Learn more about programming

Although this article is very short and only covers the surface of VBA and programming, I hope it can help you get started.

This section briefly discusses some key topics.

Variable

In the simple example provided in this article, you are processing the objects created by the application. You may want to create your own object to store values or reference other objects temporarily used in the application. These objects are called variables.

To use variables in VBA, you must useDimThe statement tells VBA which type of object the variable represents. Then, you can set its value and use it to set other variables or attributes.

Vbc # C ++ F # JScript Replication

    Dim MyStringVariable As String    MyStringVariable = "Wow!"    Worksheets(1).Range("A1").Value = MyStringVariable

Branches and loops

The simple program in this article executes a line from top to bottom. The true functionality of programming comes from the option that you must determine which code lines to execute based on one or more specified conditions. You can further expand these features so that you can repeat one operation multiple times. For example, the following code extends macro1.

Vbc # C ++ F # JScript Replication

Sub Macro1()    If Worksheets(1).Range("A1").Value = "Yes!" Then        Dim i As Integer        For i = 2 To 10            Worksheets(1).Range("A" & i).Value = "OK! " & i        Next i    Else        MsgBox "Put Yes! in cell A1"    End IfEnd Sub

Type or paste the code in the Visual Basic Editor and run it. Follow the instructions in the displayed message box and extract the text in cell A1 fromWow!ChangeYes!And then run it again to view the loop function. This code snippet demonstrates variables, branches, and loops. Read it carefully after you see it run, and try to determine what happens when each line is executed.

All office 2010 applications: Sample Code

The following are some scripts to be tried. Each script solves the actual problem of an office 2010.

Create an email in Outlook 2010

Vbc # C ++ F # JScript Replication

Sub MakeMessage()    Dim OutlookMessage As Outlook.MailItem    Set OutlookMessage = Application.CreateItem(olMailItem)    OutlookMessage.Subject = "Hello World!"    OutlookMessage.Display    Set OutlookMessage = NothingEnd Sub

Note that you may want to automatically create an email in Outlook 2010. You can also use a template.

Delete blank rows in an Excel 2010 Worksheet

Vbc # C ++ F # JScript Replication

Sub DeleteEmptyRows()    SelectedRange = Selection.Rows.Count    ActiveCell.Offset(0, 0).Select    For i = 1 To SelectedRange        If ActiveCell.Value = "" Then                Selection.EntireRow.Delete        Else            ActiveCell.Offset(1, 0).Select        End If    Next iEnd Sub

Note that you can select a cell column and run this macro to delete all rows with blank cells in the selected column.

Delete an empty text box in PowerPoint 2010

Vbc # C ++ F # JScript Replication

Sub RemoveEmptyTextBoxes()    Dim SlideObj As Slide    Dim ShapeObj As Shape    Dim ShapeIndex As Integer    For Each SlideObj In ActivePresentation.Slides        For ShapeIndex = SlideObj.Shapes.Count To 1 Step -1            Set ShapeObj = SlideObj.Shapes(ShapeIndex)            If ShapeObj.Type = msoTextBox Then                If Trim(ShapeObj.TextFrame.TextRange.Text) = "" Then                    ShapeObj.Delete                End If            End If        Next ShapeIndex    Next SlideObjEnd Sub

Please note that this code cyclically accesses all slides and removes all text boxes without any text. Counting variables decrease rather than increase, because each time the Code deletes an object, the object is removed from the set, thus reducing the count.

Copy contacts in Outlook 2010 to word 2010

Vbc # C ++ F # JScript Replication

Sub CopyCurrentContact()   Dim OutlookObj As Object   Dim InspectorObj As Object   Dim ItemObj As Object   Set OutlookObj = CreateObject("Outlook.Application")   Set InspectorObj = OutlookObj.ActiveInspector   Set ItemObj = InspectorObj.CurrentItem   Application.ActiveDocument.Range.InsertAfter (ItemObj.FullName & " from " & ItemObj.CompanyName)End Sub

Note that this code copies the contacts currently opened in Outlook 2010 to the opened Word 2010 document. This code runs only when Outlook contains the contacts currently open for check.