Vba programming in excel 2010 pdf

For example, an Excel Worksheet is an object, cell in a worksheet is an object, range of cells is an object, font of a cell is an object, a command button is an object, and a text box is an object and more. Property Each VBA object has its own properties that control its appearance. When we talk about range as an object typical properties are:- Column Width, Row Height, Font, Text, Value, Formula, Borders Method While most objects only provide characteristics to describe them, other objects can perform actions.

For example, a house can be used to protect people when it is raining outside. In computer programming, an action that an object can perform is referred to as method. Procedure A procedure is a section of code created to carry an assignment, separate from a spread sheet, whose action can be used to complement a spread sheet.

You cre6ate the procedure by writing code. Comment A line of text within a procedure, that you can use to describe each line of code or the entire procedure. To comment a line out place an apostrophe at the beginning of the line. The comment will turn green. Module Is a file that you can write and edit blocks of code and other VBA code.

Collections and container Objects. Collections are a set of related objects having the same properties. Container objects are used to store and manipulate collections of data objects, allowing access to individual members of the collection in a simple and consistent manner container objects may or may not be related to each other.

To launch the VBE editor from Excel you can do one of the following Click on the visual basic icon on the developer tab. The developer tab is not shown by default and you have to select it from: - File — options — Customise Ribbon.

As an alternative use the following keystroke. A project is a collection of modules the name of the project is the same that of the corresponding work book. Each work sheet can also contain Modules.

The Properties Window This lists the properties of the selected object, you can change the properties for an object in this window. The Code Window As it suggests this is the place when you open a module you can create or edit the code view and compile the VBA code. Object Programming What is object programming? In excel objects have certain properties, methods, and events. Properties control the appearance and other attributes of an object. Methods are built in procedures that you use to perform specific actions on an object.

Events are actions such as mouse click double click or open and close a workbook. Most objects in VBA have events associated with them. For example a worksheet object has an event called activate. The Properties Window From the properties control panel you can change the behaviour and appearance of an object by modifying the properties in that panel. The name of the selected object appears in the object list pane, there are two tabs alphabetic and categorized.

The alphabetic tab displays the property names alphabetically and categorized tab displays the properties grouped on the basis of the tasks they perform. To open a code window you double click the object or module in the project explorer.

The code window contains two lists, the object list and the procedure list. The object list displays all objects associated with the current module. The procedures list displays all the procedures in the current module or all the events of the object selected in the object list, the definition of the objects default procedure appears in the code window, for example if you select the object worksheet from the object list the following code appears in the window.

This event occurs when the user selects a cell in the worksheet. Code written within this procedure will execute every time this event occurs. For example if you write code within this event procedure to display a specific message that message will appear every time you select a different cell. Type in the following to give it a try: MsgBox "you selected another cell" The general syntax for changing object properties through code is Object.

Some methods need a value as input to complete their actions. For example, the open method of the workbook object takes a file name as input so it knows specifically what workbook to open. The input value is called an argument. An argument is a variable, constant or expression that provides additional information to a method, so that it can execute properly.

To use a method in VBA code, you would use the following syntax. You can do this by associating the code with an event object. This association is created using an event procedure.

An event procedure is a code that is executed when an event occurs. For example, you can write code for the activate event of a worksheet to display a message indicating that you cannot change the data in the worksheet this procedure will activate when the user activates the worksheet.

To program an event, double click the object to display a code window. Select an event from the procedure list and enter the code, the code will run automatically when a user triggers the event for that procedure.

You cannot edit the data in the list. From the ribbon bar navigate to the developers tab, from the controls group select insert then click on the button icon. Draw the button to the size required. Next the macros dialog box opens, in the macro name box type in calculate, next click on new. Insert the code below in the code window. Sub Calculate ActiveCell. Later on we will look at the looping stamen to automatically add up all values in the columns.

The object Browser As already discussed the object browser is a window that displays the classes, properties, methods events, and constants in the various object library, below shows the result of the excel library being chosen, searching for the activate method that shows the classes objects available.

Below shows the activate event for the worksheet object. Unit summary In this unit you have learnt that VBA is a programming language been introduced to the Visual basic Editor, Terminology use in VBA and gained an understanding of how objects, properties, methods and events play a role in Object-orientated programming.

You have learnt how to modify properties and use methods by using the code window, you also learned how to associate code to an event, and finally in this section you added a button to execute some VBA code.

In the last part of this section we looked at how the object browser worked, familiarised ourselves with the interface and icon sets and performed a search to find out information about properties and methods. When inputting data the program uses a temporary storage space called a variable, the variable consists of a name and data type. The name is used to identify itself in the program and the data type indicates the type of data to be stored. Currency 8 bytes ,,,, For example in a procedure that calculates a commission value, you would create a variable to contain that value.

The process of defining a variable and its data type is called declaration; there are two types of declaration in VBA either implicitly or explicitly. Implicit Declaration You can use a variable without declaring it. This is called implicit declaration, consider the following code. As you can see the display box is empty, this is because of a spelling mistake, and the spelling mistake for the variable is not recognised because we have chosen to run implicitly.

So from this small demonstration we can identify two basic flaws in using implicit declarations. Prone to errors and spelling mistakes causing the code to fail when executed.

By default the variant data type is assigned whilst declaring it as an integer would be far more efficient. To make sure this action takes place type Option Explicit at the top of the code window as shown below. With your cursor inside the code press F5 to run the code as you can see from the dialog box we get a compile error complaining the variable is not found. To make this code run we have to declare the variables add the following code line to the sub routine.

Dim answer As Integer the code will now run to display the correct result. You can declare more than one variable at a time, for example Employee surname and salary amount as. When you use an expression in code it returns a value in one of the data types provided by VBA. Use the syntax as follows cells row, column operates similar to the index function the main difference between the cell object function is unlike the excel functions that call column first then row, the cell object calls the row first then the column.

The code below pastes into cell D2 Sub Push Cells 2, 4. Value MsgBox myvar End Sub The two examples above show how the push code places code into a cell and that the pull function extracts data from a cell. User interaction functions These functions help to accept user input or display output the two most common user interface functions are the inputbox and the msgbox You can use the input box function to get data from the user, use the following syntax.

Message boxes display exactly that messages text or numbers these can be code written or from variables as shown below. Try typing in the code below comment out each line to describe what it does.

Worksheets "sales" wks. The property of the tab is changed by referencing the object variable and setting the property to the appropriate colour. Example 2 define the sheet to change Sub changetabsales ThisWorkbook. Worksheets "sales". Select ActiveWorkbook. Sheets ActiveSheet. The accessibility or scope is determined where the variable has been declared. Declarations Section The top of the code window includes a declaration section; variables placed here can be used by all procedures within the module remember option explicit is also set in this area.

Types of Scope Three types of scope are available in VBA as already stated the scope of a variable is determined by the way you declare it. Procedure-level, when you declare a variable within a procedure it is not accessible outside of the procedure. A procedure level variable is only available within the procedure it is written. Private module-Level, when declaring a variable in the declaration section of a module using Dim or Private keyword the variable is known as a private module-level variable, the variable can be used by any procedure within the module but cannot be accessed by a procedure outside the module.

Public Module Level, when a variable is declared in the declaration section with the prefix keyword public, the variable is called a public module-level variable these variables can be called from any procedure or module. You can also specify a type for the procedure it can be one of the following, Sub, function or property function procedure is similar to a sub function but whereas the sub procedure executes code the function procedure returns a value.

Private indicates the procedure can only be used in the current Module. A procedure can have one or more statements that are executed sequentially. To call the procedure you use the Call keyword followed by the name of the procedure you wish to execute. A procedure that calls another procedure is called a calling procedure, the procedure it refers to is known as the called procedure.

When you call a procedure you can pass information to it through arguments. A called procedure runs its code and then returns control to the next line in the calling procedure. The value that the function produces is stored in a predefined variable. The scope of the function will be either private or public. Though optional if not declared the variables are set to variant remember we said earlier code is more efficient when specified. Value Cells 8, 5.

The result is now shown as see below. Else Construction If Else constructions allow you to test for one or more conditions and run one or more statements depending on each condition. Else Statement in VBA. You can use a single-line version if you have just one test and one statement to run. If you have a more complex set of conditions and actions, you can use the multiple- line version. Must evaluate to True or False, or to a data type that is implicitly convertible to Boolean.

Then Required in the single-line form, optional in the multiple-line form. Statements Optional. One or more statements following If Then that are executed if condition evaluates to True.

Elseifcondition Required if ElseIf is present. Elseifstatements Optional. One or more statements following ElseIf Then that are executed if elseifcondition evaluates to True. Elsestatements Optional. One or more statements that are executed if no previous condition or elseifcondition expression evaluates to True.

End If Terminates the If Else block. Sub test4 If Cells 4, 5. You can execute various statements for each condition each based on the variable. Use one of two loop structures depending on the number of iterations that are required. Fixed iterationThis runs a set of statements for a predetermined number of times.

Example For… next loop Indefinite Iterationthis runs a set of statement until a defined condition is met. Next statement allows you to repeat a set of actions on the individual elements of a collection or array.

For example, if you select a range of cells, then this command allows you to repeat a set of commands on each cell in the range. Another examplewould be to repeat a set of actions on the worksheets in a workbook. The following is the typical syntax for the For Each.. Value Next End Sub Example 2 In this exercise if a cell has a number in the range A1:A50 the procedure will Double the cell value, if it contains a number,otherwise clear the cell.

Now we introduce you to Conditional LoopsRepetition while a certain condition is satisfied or until a certain condition is satisfied. Check for the condition before running the loop: Do Whilecondition Statements Loop Execute the commands once before checking the condition: Do Statements Loop Whilecondition Use the keywords Until or While to define the condition, placing them either at the top orat the end of the Do…Loop.

Loop using Exit Do. Save your file before testing the code. It is very easy to get stuck in a conditional loop. You must try to terminate the procedure if you are stuck.

The programming of User Forms can be time-consuming as every action that the User Form performs has to be coded, the OK button does not do anything until you write the code contained in its click event.

You need to be familiar with User Form objects, there is no macro recorder. Naming objects It is awkward having to use the default object names when you are completing the event procedures for each control; is the OK button CommandButton1 or is it CommandButton2?

Follow the published standard conventions for Control names, add the three-character lower case prefix to your names and you will never have any problems identifying your control objects in code.

Switch from the excel view to the visual basic editor, from the insert menu select user form. The screen will now look as shown below. If the toolbox is not displayed from the menu bar select view toolbox. Now we are going to add a List Box click on the icon add the list box below employee name input box.

To show as below. Next change the name property to lstempdepartment, locate RowSourceenter the following details E4:E10, the list Box will be populated from this data Range.

Next add one more label rename the caption to Earnings, add a text box change the name property to txtempearnings. Now we shall finish it off navigate to the properties of the form in the caption box type the following. Employee Information now for the commandButton1 in the properties change the caption to Enter Data change the name property to CmdAdd now for the commandButton2 in the properties change the caption to Close change the name property to CmdClose your form should now look like the one below.

We have now created our user form click the save button to update and save your work In the next section we will look at events and how to transfer data from the user form to the excel data sheet. Procedures are executed when an event is triggered. User forms and their controls respond to the events that are associated with them. Below is a list of events and their triggers.

Value Cells 12, 2. Value Cells 12, 3. Offset nextrow, 0. Value Range "a1". Offset nextrow, 1. Value If optMale. Offset nextrow, 2. Offset nextrow, 3. If so, you may want to use a series of dialogs like a Wizard , or use a Multipage control. The user should be able to tab through the dialog box and access the controls sequentially.

It's important to remember that an add-in will never be the active workbook. Excel's spelling checker doesn't work with UserForms. And does it explain all the controls? This can be because a mistake in the code, called a bug or if a statement in the code tries to call an invalid operation.

Examples of this can be a misspelt variable that will cause an error. John Walkenbach, aka Mr. Spreadsheet, shows you how to do it in this easy-to-follow guide. If you're already an experienced Excel user, this book will make you an Excel master.

Read Now ». The series is designed to leverage our success with John Walkenbach's popular Excel power user titles. The Ribbon UI introduced in Excel is a dramatic shift in user interface design. Fortunately, the developer has a fair amount of control over the Ribbon. Although Excel allows the end user to modify the Ribbon, Demonstrates how to use all the new features of Excel to maximize your formulas Shows how to develop custom worksheet functions with VBA, debug formulas, create financial formulas, and more Serves as an indispensable reference no With this collection of favorite Excel tips and tricks, you get a unique look at ways to make Excel more efficient than you ever imagined.

If you plan to develop VBA macros, you should have some understanding of Excel's history. Many different versions of Excel have been released, and quite a few are still commonly used. Because of this, compatibility between versions can This guide gives you the technical know-how to go from reporting data with simple tables full of dull numbers to creating high-impact reports and dashboards that will wow management both visually and substantively. This section is an introduction to VBA programming, which is essential if you want to write rather than record VBA macros.

It isn't intended to be a complete instructional guide. With this comprehensive guide, "Mr. Spreadsheet" shows you how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf.

John Walkenbach, known as Mr. Spreadsheet, is author of more than 30 books. His website on Excel is the most frequently visited Excel site after Microsoft.

Excel Power Programming will be promoted on John's website. Spreadsheet's Bookshelf series maximizes John's selling potential by capitalizing on his franchise status. The book includes an analysis of Excel application development and a complete introduction to VBA. The author goes on to demonstrate all the techniques you need to create large and small Excel applications.

It is an indispensable business tool. About The Book: This book is indispensable for Excel intermediate users, power users, and would-be power users.