EXCEL VBA

 

 

 

Additional information:

• Price: 1500 AED
• Duration: 15 Hours ( 5 sessions)
• Timings: Flexible

Course Overview

VBA, which stands for Visual Basic for Applications, is a programming language developed by Microsoft — you know, the company run by the richest man in the world. Excel, along with the other members of Microsoft Office 2003, includes the VBA language (at no extra charge). In a nutshell, VBA is the tool that people use to develop programs that control Excel.

For example, you might create a VBA program to format and print your month-end sales report. After developing and testing the program, you can execute the macro with a single command, causing Excel to automatically perform many time-consuming procedures.

Here are some brief descriptions of some common uses for VBA macros. One or two of these may push your button.

INSERTING A TEXT STRING

If you often need to enter your company name into worksheets, you can create a macro to do the typing for you. You can extend this concept as far as you like. For example, you might develop a macro that automatically types a list of all salespeople who work for your company.

AUTOMATING A TASK YOU PERFORM FREQUENTLY

Assume you’re a sales manager and need to prepare a month-end sales report to keep your boss happy. If the task is straightforward, you can develop a VBA program to do it for you. Your boss will be impressed by the consistently high quality of your reports, and you’ll be promoted to a new job for which you are highly unqualified.

AUTOMATING REPETITIVE OPERATIONS

If you need to perform the same action on, say, 12 different Excel workbooks, you can record a macro while you perform the task on the first workbook and then let the macro repeat your action on the other workbooks. The nice thing about this is that Excel never complains about being bored. Excel’s macro recorder is similar to recording sound on a tape recorder. But it doesn’t require a microphone.

CREATING A CUSTOM COMMAND

Do you often issue the same sequence of Excel menu commands? If so, save yourself a few seconds by developing a macro that combines these commands into a single custom command, which you can execute with a single keystroke or button click.

CREATING A CUSTOM TOOLBAR BUTTON

You can customize the Excel toolbars with your own buttons that execute the macros you write. Office workers tend to be very impressed by this sort of thing.

CREATING A CUSTOM MENU COMMAND

You can also customize Excel’s menus with your own commands that execute macros you write. Office workers are even more impressed by this.

CREATING A SIMPLIFIED FRONT END

In almost any office, you can find lots of people who don’t really understand how to use computers. (Sound familiar?) Using VBA, you can make it easy for these inexperienced users to perform some useful work. For example, you can set up a foolproof data-entry template so you don’t have to waste your time doing mundane work.

DEVELOPING NEW WORKSHEET FUNCTIONS

Although Excel includes numerous built-in functions (such as SUM and AVERAGE), you can create custom worksheet functions that can greatly simplify your formulas. You’ll be surprised by how easy this is. Even better, the Insert Function dialog box displays your custom functions, making them appear built in. Very snazzy stuff.

CREATING COMPLETE, MACRO-DRIVEN APPLICATIONS

If you’re willing to spend some time, you can use VBA to create large-scale applications complete with custom dialog boxes, onscreen help, and lots of other accoutrements.

CREATING CUSTOM ADD-INS FOR EXCEL

You’re probably familiar with some of the add-ins that ship with Excel. For example, the Analysis ToolPak is a popular add-in. You can use VBA to develop your own special-purpose add-ins.

CourseOutline

Excel Object Model

• The protection sheet from VBA
• Object Workbook, Workbooks collection
• Worksheet Object, Collection Worksheets
• Validation sheets
• Practical methods of the Range object
• Copy, paste, paste special
• Property CurrentRegion
• Find, replace
• Sorting ranges
• Charts (Object Chart)

Events

• Application-level events

Boards

• Dynamic arrays
• Table arrays Variant
• Optimizing arrays and memory
• Multi-dimensional arrays

Object-Oriented Programming

• Classes and Objects
• Creating classes
• Creating and Destroying Objects
• Create methods
• Create property
• Validation data using property
• The default properties and methods
• Error handling in the classroom

Create and manage collections

• Create a collection
• Adding and removing items
• References to the components (using a key and an index)

Advanced structures and functions VBA

• Passing parameters by value and reference (ByRef and ByVal)
• Procedures with a variable number of parameters
• Optional Parameters and Defaults
• Procedures of unknown number of parameters (ParamArray)
• Type conversion (Conversion)

File Operations

• Opening and closing text files
• Reading and writing text and binary data
• Processing of records in the CSV file
• Efficient processing of text files

Use VBA functions in other applications

Extras

• Create your own add-ons
• Create a toolbar for addition
• Installing your own add-ons and their protection