E learning excel vba programming lesson 2

E learning excel vba programming lesson 2

• 1.
  What we learnedin 1 st Session 1. VBA is EVENT DRIVEN LANGUAGE 2. OBJECT-BASED LANGUAGE 3. What is IDE ( Integrated Development Environmetn) 4. How to go to VBA IDE 5. Various Features of IDE - VBE Menu - Project Window - Code Window - Properties Window - Immediate Window 6. Option Explicit - Why Option Explicit - How to set Auto for Option Explicit 7. Code Modules - General Purpose Code Modules - Work Book Code Modules - Work Sheet Code Modules Ameetz.com
• 2.
  How to CreateModules To create a new general purpose module you can use the Insert - >Module option from the VBE menu toolbar: After inserting the first new general purpose module, you’ll have a new entry in the VBAProject window. Now you have a new collection called Modules and the new module you just created will be listed as one of the members of the collection. Any more modules you add will be listed as new members of the collection. You can double-click on any of them and view its contents in the Code Window. The one property that a module has is its Name. You can give more meaningful names than just “Module1” or “Module2” by changing the name in the properties window while the module is the current object of affection active object. Ameetz.com
• 3.
  How to CreateModules..contd… There’s no rule for naming modules except that they must start with an alpha character and can't contain certain special characters, I like to give mine names that start with “atz” (for ameetz) followed by some description of the use of the code within them. Examples might be names like: atzUtilities , atzDeclarations , atzOps_Operations There is no practical limit to the number of modules. The maximum size of any individual module is 64K. However we can insert lot of coding into one module hence there will be memory limit issues. Ameetz.com
• 4.
  How to CreateModules..contd… Workbook Events Just what are the workbook events? You can get a complete list of them from the code window while the There is one and only one code module per workbook Workbook Code module that is associated with Workbook Event handling. At content is displayed: You the technical level, this module, along with the can display that content by worksheet event handling modules are Class Modules. double-clicking the That need not concern you. Just be aware that if you ThisWorkbook object in the want to do any coding that deals with events that VBAProject window. You’ll occur at the workbook level, you do it in this get a display similar to module. above picture Ameetz.com
• 5.
  How to CreateModules..contd… If you use the left pull-down of the workbook’s code module you’ll see that there is a specific Workbook entry. If you choose that item, the VBE will automatically insert a stub (just the beginning declaration and end statement for the procedure) for the Workbook_Open() event. You can delete that entry if you don’t need code to deal with something you want to happen when the workbook is opened. With your cursor placed inside of any Workbook related procedure, even just a stub, you can then use the pull-down on the right to find a list of all the available event handlers for the workbook, as shown below : NOTE: If the cursor is not in a workbook event handling procedure, the list on the right will show you a list of non-workbook event procedure names in the module. Ameetz.com
• 6.
  How to CreateModules..contd… WORKSHEET CODE MODULES There is one and only one code module per worksheet that is associated with Worksheet Event handling. However, each sheet has its very own code module that is separate and distinct from all of the others even though they may all have event for a given event for those worksheets. At the technical level, this module, just like the event handling module for the workbook are Class Modules. Remember that if you want to do any coding that deals with events that occur at the worksheet level, you do it in these modules. Worksheet Events Just what are the worksheet events? You can get a complete list of them from the code window while any Worksheet Code module content is displayed: You can display that content by double-clicking any worksheet object in the VBAProject window. The code module for that sheet will be displayed as given below: Ameetz.com
• 7.
  For worksheets, whenyou choose the Worksheet item in the left pulldown list, the default event is the Worksheet_SelectionChange(ByVal Target As Range) event. This even triggers any time you make a new selection on the sheet – such as simply moving to another cell. The new cell becomes the selection, and thus you’ve had a selection change. As with the Workbook events, you can now get a complete list of Worksheet Events available to be programmed against by using the right-side pull-down (indicated as “(Declarations)”). This list is much shorter than the Workbook’s list, as there are 9 events (from Excel 2003) provide considerable versatility in dealing with worksheets. Out of the list, the Change() event is probably the one that most often has code associated with it. A Change() occurs when a user alters the contents (value) of one or more cells on the sheet. Worksheet formula recalculations don’t trigger this event, but they do trigger the Calculate() event. Ameetz.com
• 8.
  The ‘Target’ and‘Cancel’ Objects Often in worksheet event stubs provided by the VBE you will see reference to two special objects (sometimes more or others also): Cancel and/or Target. Target represents the Range [which is an object that represents a single cell, a group of cells, one or more rows and/or one or more columns] that is active at the time the event took place. Think of Target as the actual object itself. Anything you do to Target is done to the actual Range that it represents. Changes made to Target will appear on the sheet itself. The Cancel object is a Boolean type object. A Boolean object can only have one of two conditions assigned to it: TRUE or FALSE. By default a Boolean object is FALSE (and has a numeric value of zero). If your code sets Cancel = TRUE then the underlying event action is cancelled: the DoubleClick never takes place or the RightClick never gets completed. These are handy events to use to take very special actions with – you can have someone double-click in a cell (and set Cancel = True) to begin a series of events unique to that cell. A real world example of this type of thing in one application I developed is that in a data area matrix that has dates in the top row, a double-click on a date causes all rows with an empty cell in that column to become hidden: a kind of auto filter based on empty cells for that one column. Ameetz.com
• 9.
  Procedures: Function andSub Code modules contain code, and that code is placed into procedures, and procedures fall into two categories: Sub (or subroutines) and Function(s). FUNCTIONS The difference between a Sub and a Function is simply that a function can return a value to the procedure that called it. That procedure can be another Function, a Sub or even to a worksheet cell. When it is done using a worksheet formula, the Function is known as a User Defined Function, or UDF. Potentially all Functions are UDFs. One other distinction between Functions and Subs is that (generally) Functions can only affect a single cell in a workbook, while Subs can do their work and affect almost any aspect of a workbook or worksheet. When it is used as a UDF, it can only affect the cell that it is called from; it cannot alter the contents of other cells. Ameetz.com
• 10.
  SUBS Sub procedures arejust like Functions, except that they do not return a value in the same way that a Function does. They can accept arguments, or not, just like a Function does. VBA Sub Procedure Example Ameetz.com
• 11.
  VBA Function ProcedureExample The word Macro is slang for the word VBA procedure. • A procedure is defined as a named group of statements that are run as a unit. A statement is simply 1 complete line of code. • VBA procedures are used to perform tasks such as controlling Excel’s environment, communicating with databases, calculating equations, analyzing data etc. Ameetz.com
• 12.
  What is aVBA Procedure? Contd.. • A VBA procedure unit or block consists of a procedure statement (Sub or Function) and an ending statement with statements in between. • A VBA procedure are constructed from three types of statements: executable, declaration and assignment statements. The statements between a procedure’s declaration and ending statement (i.e. Sub and End Sub) perform the procedure's task; what you are trying do. •For example, the procedure to the right commands Excel's Sort feature on the active worksheet when it is run. • Procedures are typed and stored in a Module. • Procedures are executed or run (same meaning) in order to apply their statements. When a procedure is run, its statements (i.e. lines) are executed in a top-down line by line fashion performing operations. Think of reading a book page. Note that typing a procedure in a module does not run it. You must do this after typing it by variety of different methods. Until you run it, it is just basically text sitting in a document. • VBA has two types of procedures: Sub procedures and Function procedures. Ameetz.com
• 13.
  Sub Procedures Sub proceduresare written when you want to command Excel like creating a chart, analyzing data, coloring cells, copying and pasting data. Function Procedures Function procedures are created when you want to make your own custom worksheet functions or perform a calculation that will be used over and over again. Note that Sub procedures can also do calculations. What to Write So if you want to do a task in Excel, you write a Sub procedure, if you want to write a custom worksheet function, you write a Function procedure. Are their grey areas between the two, yes, but do not worry about them when first starting out Ameetz.com
• 14.
  In this example,you create an Excel macro that converts selected formulas to their current values. Sure, you can do this without a macro, but it’s a multistep procedure. To convert a range of formulas to values, you normally complete the following steps: 1. Select the range that contains the formulas to be converted. 2. Copy the range to the Clipboard. 3. Choose Edit Paste Special. 4. Click the Values option button in the Paste Special dialog box, which is shown in Figure 2-1. 5. Click OK. 6. Press Esc. This clears the cut-copy mode indicator (the moving border) in the worksheet. Ameetz.com
• 15.
  Here comes thehands-on part. Follow these instructions carefully: 1. Select the range of cells that contains your formulas. The selection can include both values and formulas. In my case, I chose the range A1:D10. 2. Choose Tools Macro Record New Macro. The Record Macro dialog box appears, as shown in Figure 2-3. 3. Enter a name for the macro. Excel provides a default name, but it’s better to use a more descriptive name. ConvertFormulas is a good name for this one. 1 No Spaces Ameetz.com