How to Write Macro in Excel using VBA

[Trog]

Visual Basic for Applications is the programming language of Microsoft Office applications. VBA to automate tasks, create complete applications, secure your entries and documents, create new menus and new features to boost your software effectively.

VBA uses the same language as Microsoft Visual Basic. The difference between VBA and VB is that VB is a complete package that allows you to develop applications independently and freely distributable, so that an application made in VBA is completely linked to the software in which it was created (an application created in Excel VBA does cannot embark on a post if Excel is not installed).

Before that Excel uses this programming language, the software used its own programming language and an application was called macro. This term has remained, but a macro in VBA Excel produced nothing but a procedure such that they are carried out under VB. A programmer in VBA has no problem to switch to VB and vice-versa.

VBA is accessible to all. However, a good knowledge of Excel is necessary before embarking on the creation of application. Indeed, it is important to master the main VBA manipulates objects as objects Workbook (workbook) Worskheet (Spreadsheet), Range (cell range), etc. This should not be a hindrance for those who want to start because words can quickly familiar, are needed. VBA, powerful language, flexible and easy to use all.

VBA Macro Recorder :

Excel, Word, PowerPoint has a great tool: the macro recorder. It creates a macro in VBA and transforms all orders placed by the user in the host application. It automates without any knowledge of programming some of your tasks and also to become familiar with VBA.

How To Use

Here is an example to use this tool. For that here is the example. I want to put in the same format all the tables that I create. Rather than reproduce the same commands every time, I'll use the macro recorder.

1. I select my first table. Indeed, if I select it after launching the recording, the macro selection at each recurrence of this launch and still runs on the same cells.
2. Start the recording via the menu "Tools - Macro - Record New Macro.
3. Macro name: It is important to explicitly rename the macro. The macro name must begin with a letter and must not contain spaces. Use the underscore to separate words.
4. Shortcut key: It is possible to create a keyboard shortcut to start the macro by typing a letter. You can use uppercase and run the macro by the Ctrl + Shift + letter.
5. Save the macro: workbook containing the macro. The macro will then run if the workbook where the macro was recorded is open. If you choose "Personal Macro Workbook", a new workbook is created and saved in the folder so that your macros are available whenever you use Excel.
6. Description: You can give a description to your macro.
7. After clicking OK, the macro will record all the commands that I use. Here, I change the format of my table (Color, Font, Frame).
8. Then I stop the recording by clicking on the button Stop Recording or Tools-Macro Stop Recording. The macro is created.
9. I have a new table that I want to replicate the same format:
10. I select my new table and I'll use the macro you created earlier (if the workbook in which it was registered to be opened). I start by selecting Tools-Macro-Macros"
11. I select the macro you want, then I click Run
12. I could have run the macro the keyboard shortcut Ctrl + f "I had set at its inception.

Some precautions to take when using it:

• Well-think of the commands that you want to register to avoid creating unnecessary code.
• Know-how in the macro workbook save for future use.
• Rename macros explicitly.

Limitations of the Macro Recorder:

• It can only translate the orders placed by the user in the host application and usually writes more lines of code needed. When writing macros, we will see how to use and reduce the code created by the recorder. Macros created are visible and editable via the macro editor VBE (Visual Basic Editor).

[Trog]

Terms used in VBA

VBA manipulates objects in the host application. Each object has properties and methods.

Objects: Each object represents an element of the application. Excel, a workbook, worksheet, cell, button, etc. . are objects. For example, Excel is the Application object, the Workbook object Workbook, Worksheet object spreadsheet etc . All objects of the same type form a collection, for example, all worksheets in a workbook. Each item is identified by name or by index. ach object can have its own objects. For example, Excel has workbooks that have leaves that have cells. To refer to a cell, it might as well use: .

Properties: A property is a peculiarity of the object. The value of a cell, its color, size, etc . are the properties of the Range object. The objects are separated from their properties by a point. A property can also refer to a state of the object. When one uses multiple properties or methods of the same object, we will use the statement block With End With Purpose Instructions. This instruction often makes the code easier to read and faster to execute.

Events

For a macro is triggered, there must be an event (a click of a button, opening a workbook, etc ...) to occur. Without events, nothing can happen. Events are related to objects. The main items that can trigger a macro are:

• A workbook
• A worksheet
• A dialog box

Each of these objects has its own module. To get there, start the macro editor. To create an event procedure associated with a workbook, select the folder and then click the 3 or simply double-click. You can access the module related to the object. Select "Workbook" in a list and then click the desired event from the list 2.

List of events of the Worksheet object:

• Activate : When a sheet is activated
• BeforeDoubleClick - When double-clicking
• BeforeRightClick - when clicking the right mouse button
• Calculate - after recalculation of the worksheet
• Change - when editing a cell
• Deactivate - when a leaf is turned off
• Some procedures have activation parameters such as Cancel, which can cancel the procedure, SaveAsUi which, in the proceedings Workbook_BeforeSave displays the Save As, sh which represents the worksheet, Target representing the selected object (cell, graphic, hyperlink), Wn which represents the active window.
• A macro can also be triggered by clicking on a graphic application . It Create an item then click Assign Macro from the popup menu. Click on the name of the desired macro and click OK. A simple click on the object will launch the macro.
• There are also procedures related to the activation dialog boxes (see the course on UserForms). A macro can also be triggered at a given time (OnTime) or when the user presses a button (onkey).

Post : During a procedure, the messages used to communicate with the user. There are messages that provide information and others who request them. The MsgBox can simply give information. The procedure is then halted until the user has not clicked.

Text can be displayed on multiple lines using the carriage return code chr (13) or the return code line chr (10). You can add an icon for the type of message to display.

Variables : During a procedure, variables are used to store all kinds of data (numeric, text, logical values, dates, etc.). They can also refer to an object. According to the data that the variable will, he will assign a different type.

[Trog]

Workbooks.

The workbooks are designated by the word Workbook. They can be opened, closed, saved, enabled, hidden, deleted .a statement by VB. The workbooks are designated by the word Workbook. They can be opened, closed, saved, enabled, hidden, deleted a statement by VB.

Tables

Unlike traditional variables that contain a single value, an array is a variable that can contain a set of values of the same type. To store the names of all students, declare a table rather than declare as many variables as to students. The syntax for declaring an array is Dim Variable (No. items) As Type. It is possible to resize a table by the word Redim. Indeed, the number of elements or dimensions that must contain a table is not always known.

Text Function

VBA has functions for extracting a string of text. The Len function returns the number of characters in a text.

Macro Recording :

To learn Excel VBA programming the best way to start is probably the recording macros in Excel. It's easy to record your own macros. And with a light touch, out even better. Click OK. Now the macro will record everything that happens to your Excel workbook. If you select cell H1, the same action will be recorded. Then, when you run the macro, that cell is selected (surprise ...!). Then, while recording, selects cell H1, change the format to number - 2 decimal places. We stopped recording.

Some Tips and Tricks

Making custom programs, each company has its peculiarities and characteristics, often acquired standardized software packages that do not meet initial expectations and they just did not bring a real solution to the needs of the business through macros in Excel each program developed is easily adapted to each type of company, providing unparalleled flexibility and versatility.

Quick and easy operation of the programs developed under the Excel environment, where the company buys a new software has to devote a certain portion of time to acquire the knowledge necessary for the proper management of software, the great advantage that gives us the macros is that they are developed in Excel, a tool known by a large audience and widespread in most companies.

Tasks and complex calculations, often left to use known techniques to solve problems, we could provide critical data for the highly complex calculations that lead through these tasks in Excel macros and calculations to pass history, making the Excel sheet itself the work for us.
Increased effectiveness and efficiency at work, as we reduce our hours of work in performing manual tasks by turning them into machines, taking the time gained in other cases.

Editing the Macro

In the first row we have the product name in the bottom row of the reference product, the back row and finally the price the next row is blank, so on up to 500 products. Click OK. Now the macro will record everything that happens to your Excel workbook. If you select cell H1, the same action will be recorded. Then, when you run the macro, that cell is selected (surprise ...!). Then, while recording, selects cell H1, change the format to number - 2 decimal places. We stopped recording.

Enter the VBA editor (Tools-Macro - Visual Basic Editor). Sign Modules - Module 1. To the right is what you have recorded VBA. The code makes Excel Select cell H1, and then apply a number format to two decimal places. That is, the macro will only act on the cell H1.

By changing the code a bit we can make VBA change the format to any cell you have selected. Cleans up the code to make it the following markup.

Command for that is : Selection.NumberFormat = 0.00. Now go back to the sheet, and test your new macro. Select a cell range, apply the shortcut (CTRL + L). You can run the macro from Tools - Macro - Macros.

Automated Programs : Automating tasks using macros in Excel vba give us many advantages such as the eradication of human calculation errors, saving hours of work, resolution of complex calculations, effectiveness, efficiency .

File Migration : Each step involves a mouse click while visual effort and concentration to perform this task, assuming that for every operation we invest an average of 3 seconds, get it to match the format required for each product within 24 seconds, the 500 products we get there we would invest in such work 3 hours and 20 minutes without stopping to make such steps.

If you run the macro in just 5 seconds that made the work, while ensuring that there is no error, which with the greatest chance happen to us if we do it manually. If instead of having 500 products we had 5,000, 20,000 or even 50,000 products that task could not be done manually, unless us to spend weeks or even months to complete the report, thanks to programming with vba macros in Excel tasks such repetitive, tiresome and tedious tasks become automated allowing spending our time on other issues.

Macro on Older version of Microsoft Office

The macros that we will see throughout this course we are going to be programmed directly with the Visual Basic Editor that comes bundled with the same Excel.

• To put into operation we perform the following steps:
• In the menu Tools - Macro we make the choice: Visual Basic Editor.
• Once you select will open a new window with two windows attached to the left. The upper window is called: Project and Property inferior. In the first elements appear as part of each project (group of macros and Excel spreadsheets) and the second are the properties of objects that can be incorporated in our macros. Later we will see how to use these two windows and all its features.
• Choose the option M or Module on the Insert menu. Observed as shown in the upper window we see a new folder called M or modules and inside a new element called M or dulo1. Within this module is where we keep the macros you create.
• You can also see how the right side of the window is now completely white. This is where we can write the instructions that form part of our Macro.

[vinod_jogur]

thank U sir..