Vba tutorial rapidshare

The Document object contains Paragraph objects and so on. Object models roughly mirror what you see in the user interface. They are a conceptual map of the application and its capabilities.

The definition of an object is called a class, so you might see these two terms used interchangeably. Technically, a class is the description or template that is used to create, or instantiate, an object.

Once an object exists, you can manipulate it by setting its properties and calling its methods. If you think of the object as a noun, the properties are the adjectives that describe the noun and the methods are the verbs that animate the noun.

Changing a property changes some quality of appearance or behavior of the object. Calling one of the object methods causes the object to perform some action. The VBA code in this article runs against an open Office application where many of the objects that the code manipulates are already up and running; for example, the Application itself, the Worksheet in Excel, the Document in Word, the Presentation in PowerPoint, the Explorer and Folder objects in Outlook.

Once you know the basic layout of the object model and some key properties of the Application that give access to its current state, you can start to extend and manipulate that Office application with VBA in Office.

In Word, for example, you can change the properties and invoke the methods of the current Word document by using the ActiveDocument property of the Application object. This ActiveDocument property returns a reference to the Document object that is currently active in the Word application. The following code does exactly what it says; that is, it saves the active document in the application. Read the code from left to right, "In this Application, with the Document referenced by ActiveDocument, invoke the Save method.

You instruct a Document object to Save and it does not require any more input from you. If a method requires more information, those details are called parameters. The following code runs the SaveAs method, which requires a new name for the file. Values listed in parentheses after a method name are the parameters.

Here, the new name for the file is a parameter for the SaveAs method. You use the same syntax to set a property that you use to read a property. The following code executes a method to select cell A1 in Excel and then to set a property to put something in that cell.

The first challenge in VBA programming is to get a feeling for the object model of each Office application and to read the object, method, and property syntax.

The object models are similar in all Office applications, but each is specific to the kind of documents and objects that it manipulates. In the first line of the code snippet, there is the Application object, Excel this time, and then the ActiveSheet , which provides access to the active worksheet.

After that is a term not as familiar, Range, which means "define a range of cells in this way. In other words, the first line of code defines an object, the Range, and runs a method against it to select it. The result is automatically stored in another property of the Application called Selection. The second line of code sets the Value property of Selection to the text "Hello World", and that value appears in cell A1.

The simplest VBA code that you write might simply gain access to objects in the Office application that you are working with and set properties. For example, you could get access to the rows in a table in Word and change their formatting in your VBA script.

That sounds simple, but it can be incredibly useful; once you can write that code, you can harness all of the power of programming to make those same changes in several tables or documents, or make them according to some logic or condition. For a computer, making changes is no different from making 10, so there is an economy of scale here with larger documents and problems, and that is where VBA can really shine and save you time.

Now that you know something about how Office applications expose their object models, you are probably eager to try calling object methods, setting object properties, and responding to object events. To do so, you must write your code in a place and in a way that Office can understand; typically, by using the Visual Basic Editor. 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. Click on a cell and assign the UserReportQuery macro to the button. Please note that the secondary subroutine, ProcessReport, could be anything.

But first This example builds on the previous example and has quite a few new elements. This could be used in many, many ways. The value and versatility of this functionality is more so defined by what the secondary subroutine does.

For example, maybe you have a file that is used to generate 3 different weekly reports. These reports are formatted in dramatically different ways. For loops are very useful if you need to perform repetitive tasks on a specific range of values - arrays or cell ranges. Save and navigate back to the Developer tab of Excel and select the Macros button. Run the LoopExample macro. The For-Next loop is one of the most powerful functionalities of VBA; there are numerous potential use cases.

This is a more complex example that would require multiple layers of logic, but it communicates the world of possibilities in For-Next loops. Maybe you have a list of all products sold at your bakery in Column A, the type of product in Column B cakes, donuts, or muffins , the cost of ingredients in Column C, and the market average cost of each product type in another sheet. You need to figure out what should be the retail price of each product. A For-Next loop would allow you to do this type of calculation.

See if you can answer these questions. I'm Chloe Tucker, an artist and developer in Portland, Oregon. As a former educator, I'm continuously searching for the intersection of learning and teaching, or technology and art. If you read this far, tweet to the author to show them you care. Tweet a thanks. Learn to code for free. Get started. Forum Donate. In this chapter, learn how to create a simple macro. A loop in Excel VBA enables you to loop through a range of cells with just a few codes lines.

In Excel VBA, you can refer to a specific variable element of an array by using the array name and the index number. We call it the Application object. The application object gives access to a lot of Excel related options.

Become a VBA pro! You can find related examples and features on the right side of each chapter at the bottom of each chapter. Below you can find an overview scroll down for more information. You will often need this structure in more complicated programs as we will see later. This section is for advanced programmers who are comfortable with the above concepts.

In this section you will learn how to develop the Forms and interact with the other applications, handling the files and other advanced programming concepts. By end of this sessions you will be comfortable with VBA and be confident to develop the tools to automate complex tasks and complex Dashboards.

When we are working with variables, it is important to understand the Scope of a Variable. The Scope describes the the accessibility or life time or visibility of a variable. We write the procedures to perform certain tasks and some times we may required to write another procedure with small variations.

In this situation we can take advantage of the Passing Arguments. When we work with one item we required to use one variable, if you want work with more than one item, we can go for arrays. Arrays are the variables which allow us to store more than one value. Collection is an object contains group of objects having similar characteristics with same properties and methods. For example,if you want to loop through all worksheets in a workbook, you can refer worksheets collection of the workbook and do whatever you want to do with that particular worksheet.

Any Windows Application is equipped with set of objects called windows controls. The Main control is called a Form, it is the primary window contains different types of controls which allow user to interact with the computer. Event programming is the most useful tool which helps to monitor specific user actions within Excel.

Although Excel VBA is not a truly object oriented programming language, it does deal with objects. Yes, we can interact with the other Applications using VBA,i. Visual Basic for Applications language reference for Office. In the last couple of months, I have gone through the tutorials available in some other websites and able to write some simple VBA codes. This site, just a couple of days before I found out, appears very interesting and I would like to study the tutorials authored by you.

Before proceeding, I would like to whether any changes in the tutorials would be required for Excel wherein the interface has been totally changed.