Although it is installed by default, many users do not know that it is even available until it is enabled on the ribbon. All Office applications use the ribbon. One tab on the ribbon is the Developer tab, where you access the Visual Basic Editor and other developer tools.
Because Office does not display the Developer tab by default, you must enable it by using the following procedure:. On the File tab, choose Options to open the Options dialog box. Under Choose commands from on the left side of the dialog box, select Popular Commands. Under Customize the Ribbon on the right side of the dialog box, select Main Tabs in the drop down list box, and then select the Developer checkbox. In Office , you displayed the Developer tab by choosing the Office button, choosing Options , and then selecting the Show Developer tab in Ribbon check box in the Popular category of the Options dialog box.
After you enable the Developer tab, it is easy to find the Visual Basic and Macros buttons. To protect Office users against viruses and dangerous macro code, you cannot save macro code in a standard Office document that uses a standard file extension. Instead, you must save the code in a file with a special extension.
For example you cannot save macros in a standard Word document with a. When you open a. Examine the settings and options in the Trust Center on all Office applications. The default setting disables macro from running, but warns you that macros have been disabled and gives you the option to turn them back on for that document.
You can designate specific folders where macros can run by creating Trusted Locations, Trusted Documents, or Trusted Publishers. The most portable option is to use Trusted Publishers, which works with digitally signed documents that you distribute. For more information about the security settings in a particular Office application, open the Options dialog box, choose Trust Center , and then choose Trust Center Settings.
Some Office applications, like Outlook, save macros by default in a master template on your local computer. Although that strategy reduces the local security issues on your own computer when you run your own macros, it requires a deployment strategy if you want to distribute your macro. When you choose the Macro button on the Developer tab, it opens the Macros dialog box, which gives you access to VBA subroutines or macros that you can access from a particular document or application.
Another button on the Developer tab in Word and Excel is the Record Macro button, which automatically generates VBA code that can reproduce the actions that you perform in the application.
Record Macro is a terrific tool that you can use to learn more about VBA. Reading the generated code can give you insight into VBA and provide a stable bridge between your knowledge of Office as a user and your knowledge as a programmer. The only caveat is that the generated code can be confusing because the Macro editor must make some assumptions about your intentions, and those assumptions are not necessarily accurate.
Open Excel to a new Workbook and choose the Developer tab in the ribbon. Choose Record Macro and accept all of the default settings in the Record Macro dialog box, including Macro1 as the name of the macro and This Workbook as the location. Choose OK to begin recording the macro. Note how the button text changes to Stop Recording. Choose that button the instant you complete the actions that you want to record.
Choose cell B1 and type the programmer's classic first string: Hello World. Stop typing and look at the Stop Recording button; it is grayed out because Excel is waiting for you to finish typing the value in the cell. Choose cell B2 to complete the action in cell B1, and then choose Stop Recording.
Be aware of the similarities to the earlier code snippet that selected text in cell A1, and the differences. In this code, cell B1 is selected, and then the string "Hello World" is applied to the cell that has been made active. The quotes around the text specify a string value as opposed to a numeric value. Remember how you chose cell B2 to display the Stop Recording button again? That action shows up as a line of code as well. The macro recorder records every keystroke.
The lines of code that start with an apostrophe and colored green by the editor are comments that explain the code or remind you and other programmers the purpose of the code. VBA ignores any line, or portion of a line, that begins with a single quote. Writing clear and appropriate comments in your code is an important topic, but that discussion is out of the scope of this article. Subsequent references to this code in the article do not include those four comment lines.
When the macro recorder generates the code, it uses a complex algorithm to determine the methods and the properties that you intended. If you do not recognize a given property, there are many resources available to help you.
For example, in the macro that you recorded, the macro recorder generated code that refers to the FormulaR1C1 property. Not sure what that means? Be aware that Application object is implied in all VBA macros. The code that you recorded works with Application. Select FormulaR1C1 in the recorded macro and press F1. The Help system runs a quick search, determines that the appropriate subjects are in the Excel Developer section of the Excel Help, and lists the FormulaR1C1 property.
You can choose the link to read more about the property, but before you do, be aware of the Excel Object Model Reference link near the bottom of the window. Choose the link to view a long list of objects that Excel uses in its object model to describe the Worksheets and their components.
Choose any one of those to see the properties and methods that apply to that particular object, along with cross references to different related options. Many Help entries also have brief code examples that can help you. For example, you can follow the links in the Borders object to see how to set a border in VBA. The Borders code looks different from the recorded macro. One thing that can be confusing with an object model is that there is more than one way to address any given object, cell A1 in this example.
Use the Interior property to return an Interior object. Then use the ColorIndex property of the Interior object to set the background color of a cell. Our range has two areas.
Using Option Explicit forces you to declare all your variables. There are three scoping levels: procedure level, module level, and public module level. You can achieve this by using the Static keyword. As always, we will use easy examples to make things more clear. First, we declare two variables of type Integer. For example, when we want to square the numbers in the range A1:A3. VBA comes with multiple events shown in the above screenshot, events like change, deactivate and many more.
Whenever that event happened the macro will run. Whatever the code written in this module will apply for all the sheets in the workbook. Suppose if we write an event code for the workbook then whenever that event happened in any of the sheets of the workbook the macro will run.
Module: Normally we will write code in modules in VBA. We need to insert the module by clicking on the Insert menu and select the module option to insert a module. Similarly, we can insert a class module and user form also. These are the modules where we can write our code. Depend on the type of requirement we should choose the module and write the code in it. Now we will see a sample macro code using a normal module. When we insert a new module, the empty window will look like below.
Step 1: Any program in VBA starts with sub keyword and program name with open and close brackets as below. In the above macro, I wrote a sample code like when I run the macro first three cells should fill with Name, Age and Address.
You can use the PMT function to compute the above. It will open a VBA editor, from where you can select the Excel sheet where you want to run the code. To open VBA editor double click on the worksheet. It will open a VBA editor on the right-hand side of the folder. It will appear like a white space. In this step we are going to see our first VBA program. To read and display our program we need an object.
In next step you have to run this code by clicking on the green run button on top of the editor menu. When you run the code, another window will pops out. Download the above Excel Code. Skip to content. Why VBA? Just like in excel, you can use. Logical operators — The concept of logical operators covered in the earlier tutorials also apply when working with VBA.
0コメント