Online Advance Excel & VBA Training in India

1. VBA macros training Learn Advance Excel Training in Delhi & VBA Training from Investment Banking Institute Contact us at www.ibinstitute.in

2. After the course, you will be able to: • Record / Run Macros • Write Code Manually • Perform Loops, Controls and decision making • Create UDF Functions • Protecting macros • Use Events

3. Learning Macros • Why VBA Macros – An Introduction • Where we write macros – Introduction to VB Editor • Recording Macros • Personal Macro workbook • Declaring Variables • Interactive Macros - Msgbox / Inputbox • Decision Making (If, Else) • Looping (Do Loop, For Loop)

4. Why VBA Macros ? • VBA is "Visual Basic for Application“. • It is a programming language that allows users to program macros to accomplish complex tasks within an application like Excel, Word, Power Point, Access, etc. • With VBA for Excel you can develop small procedures (macros) that will make your professional life easier and allow you to do more in less time. • With VBA for Excel you can develop a program that does EXACTLY what you need and VBA is easy to learn. • Programming background is useful but not mandatory to learn macros.

5. Where we write macros ? • VBA macros are written into Visual Basic editor • Developer  Visual Basic • Insert a Module in the editor to write the code • InsertModule • All VBA procedures (macros) start with Sub & end with End Sub • You can run the code by pressing F5 or clicking the Run symbol on the toolbar OR [ ALT + F11 ]

6. Recording Macros • Excel provides Macro Recorder as the most user friendly tool. • It is very useful to see what properties and methods belong to various objects. You can then use this as the basis for building your own code. • When you record a macro, a module is created and the code is written into it. • You can view this code and see exactly how Excel has tackled the problem in VBA. You can modify this code or use it in other procedures. • You should not assume that a recorded macro will work under every circumstance.

7. How to record a macro? • Tools  Macro  Record new macro • You can give the macro another name and a shortcut key if needed • Click OK and a small Stop Recording window appears. • Until you click the Stop Recording button, everything that you do on the spreadsheet is translated into VBA code using the Excel object model • Look in the code window by pressing Alt-F11, and you will find that a new module has been inserted that contains the code for the macro you recorded

8. How to create variables in VBA? • Whenever you want to use a variable, you must create them first. This is your way of telling computer to set aside some memory units so that your variable can be used. • In Excel VBA, you can do this by the DIM statement. • Examples : Dim someNumber As Integer Dim bigNumber As Long Dim otherNumber As Double Dim someText As String

9. Decisions • Programs usually have to make decisions according to data retrieved or input by the user. • It specifies what will happen when different events occur. • We use If-then-end if & Select Case to implement decisions • Examples When there is only one condition and one action, you will use the simple statement: If Application.ActiveCell = 5 Then MsgBox "Cell is 5" Else MsgBox "Cell is not 5" End If

10. Looping • Looping allows a block of code to be repeated until a condition or a specified value is met. • Without looping facilities, programs would be extremely tedious and difficult to maintain. • We use For next ,For each, Do Until, While wend etc. to reduce the code & make it simple • Example • You wanted to display the numbers from 1 to 5. You could write the program as follows: MsgBox "1" MsgBox "2" Msgbox "3" Msgbox "4" MsgBox "5" This code can be reduced and made easier to maintain by using the For..Next looping statement as follows: For n = 1 to 5 MsgBox n Next n

11. Early Exit of Loops • Under some circumstances, you may want your procedure to exit a loop early before it has worked all the way through and satisfied its criteria. • You exit a loop by using an Exit For statement in a For..Next loop or a For Each loop • Here is an example: • Sub test_exit() For x = 1 To 100 If x = 50 Then Exit For End If Next x MsgBox x End Sub

12. The Excel Object Model • In Excel, the whole application is oriented toward a structure of workbooks and spreadsheets, so the object model is written around this. • The objects are arranged in a hierarchy. • The Excel object model contains a large number of objects—for example Workbooks, Worksheets, Ranges, Shapes & Charts • Under the Application object is the Workbook object, and within the Workbook object are Worksheet objects. Within each Worksheet object are Range objects, and so on.

13. The Excel Object Model

14. Excel beyond Microsoft

15. The Application object • Whenever you want Excel to do something or you want to change a property of Excel, you will use the object Application. • Examples • CutCopyMode After each Copy/Paste operation, you should empty the clipboard with the following line of code to make sure that the computer memory doesn't overload. ActiveSheet.Paste Application.CutCopyMode=False • DisplayAlerts When you don't want Excel to ask you things like "A file already exists....." or "Do you want to save this file..." you will use the following line of code at the beginning of your VBA procedure. Application.DisplayAlerts = False Then at the end Application.DisplayAlerts = True • ScreenUpdating When you don't want to see your screen follow the actions of your VBA procedure, you start and end your code with the following sentences: Application.ScreenUpdating = False Application.ScreenUpdating = True

16. The Workbook object • The Workbook object represents an entire workbook loaded into Excel. • Activeworkbook represents the workbook in the active window (the window on top) • Examples • To Open a Workbook • Work?ooks. ope? ?C:\Test.xls? • To activate the Sheet3 of workbook book1 • Workbooks("book1").Worksheets("Sheet3").Activate • To save the workbook • Workbooks("book1").Save • Activeworkbook.save • To print the active sheet of workbook • Workbooks("book1").PrintOut • To close the workbook • Workbooks("book1").Close • Activeworkbook.close

17. Exercise Pre-work : Create a blank excel file in C drive with the name Income.xlsx Tasks : 1. Open a workbook 2. Copy Incomestatement from current workbook 3. Paste Incomestatement in first sheet worksheet of Income.xlsx 4. Save workbook 5. Close workbook

18. The Worksheet object • This object represents the actual worksheet that you work on. • In the hierarchy of the Excel object model, it sits below the Workbook object because all Worksheets are part of a Workbook. • Examples • To add a worksheet • Worksheets.add • To change the tab color of the sheet • Worksheets("Sheet1").tab.color = vbGreen • To rename a worksheet • Worksheets("Sheet1").Name = "Balance" • To select a worksheet Worksheets("Balance").Activate • To hide or unhide a worksheet • Worksheets("Sheet1").Visible= True Worksheets("Sheet1").Visible= False • To delete a sheet • Worksheets("Sheet1").delete

19. Exercise Tasks : 1. Add a new sheet 2. Name the sheet as income statement 3. Copy the income statement from previous sheet to new sheet 4. Change the tab color to green 5. Hide the previous sheet

20. The Range object • This object communicates with a range of cells or individual cells and makes changes in them. • Examples: • To select a range of cells in code • Ra?ge??F?9:G??"?.“ele?t • To change the background color of the cell • Ra?ge??A?"?.I?terior.?olor = vbRed • To clear the contents of the range of cells • Ra?ge??A?:D??"?.ClearCo?te?ts • To copy and paste the data in range • Range(" F19:G20").Copy Range(" H21").PasteSpecial • To write data into a spreadsheet • Ra?ge??F?6"?.Value = ?? • To merge the cells • Ra?ge??F?6:H?6??.?erge

21. Famous ranges and cells • IM21 The legal drinking age cell • AK47 The assault weapon cell • AH:HA The discovery range • AM:FM The radio range • BY:BY The farewell range • IQ100 The average intelligence cell • HO:HO The Santa Claus range • GO2 The destination cell • EX2 The second former spouse cell • AC:DC The electric range • I1:U1 The tied game cell

22. Exercise Tasks : 1. Copy and paste the income st. to different range in the same sheet 2. Color the header of the Income statement in red color 3. Color the particulars in green color 4. Merge the cells on the top 5. Add the title INCOME STATEMENT 6. Color the negative values in red color

23. User defined functions A User defined function (or UDF) - A function accepts some inputs and returns a result - It can only return a value to the cell when it is called - It must not modify the contents or formatting of any cell Example : Function Area(Length As Double, Width As Double) Area = Length * Width End Function Exercise : Create a function to calculate tax by taking income and investment as input and return tax payable as output Inputs : Income , Investment Output : Tax Payable

24. Error Handling in VBA • VBA tells you when the code is wrong but what if the logic is wrong or what if the user gives a wrong answer. We have two methods to handle these errors Method 1 (When you want VBA to do something if there is an error) • The first thing you create is an address where VBA will jump if there is an error. In this example the address is addJump with the NOT OPTIONAL colon at the end. Below addJump is what is supposed to happen of there is an error and above is the procedure that should run if there are no errors. • Example Sub proTestErrorHandler() On Error GoTo addJump Workbooks.Open "xxxxxx" Exit Sub addJump: MsgBox "An error has occurred, call Peter at 1 514-257-0734" End Sub

25. Error Handling in VBA Method 2(When you want VBA to do nothing & continue with rest of the code) • If you just want errors to be ignore you write On Error Resume Next at the beginning of the procedure. Copy/Paste the following procedure in a module of your own and run it. It will generate a message box saying An error has occurred but we have ignored it. • Example Sub proTestErrorHandlerIgnore() On Error Resume Next Workbooks.Open "xxxxxx" MsgBox "An error has occurred but we have ignored it." End Sub

26. VBA Code General Tips • Do not hesitate to use the Macro recorder to avoid typos. • Write your code in lower case letters. If the spelling is right, VBE will capitalize the necessary letters. If it doesn't.... check your spelling. • Declare all your variables (Dim) at the beginning of the procedure, it will simplify the testing of your code. • You can insert a commentusi?g “ ‘“ i? the co??e?t. It ?ill co??ert text of the comment in green color • Exa?ple : ‘This is a ?o??e?t

27. Nine tips to learn VBA #1 Think Thru before Coding #2 Use the Macro Recorder #3 Break Your Work in to Smaller Chunks #4 Take up Challenges #5 Reuse code : Search Google for available codes #6 Keep a Good Reference Handy #7 Use VBA only when you need it #8 Join Excel forums and ask queries #9 Use F1 for quick VBA help

28. Pivot Tables • It sums up large amount of information in a small amount of space • PivotTables allow you to pivot data using drag-and-drop techniques and receive results immediately. • You can rotate rows & columns to view the data in an interactive way. • You can link to external data sources • It helps in organizing, analyzing & comparing data • Example

29. Memory Game Macro Pivot table clearcontents VBA For Loop Add Inputbox Do Loop Value Msgbox If condition Double Dim Macro Recorder Font.color Long Function Tab.color Integer Sub Stop recording String Variables Discussexcel.com The object model Personal macro workbook User defined function Workbooks Worksheets events Range Active cell Visible VB Editor This workbook Activate Module Merge Alt+F11 Protection Interior.color Alt+F8

30. Resources 1. www.discussexcel.com 2. http://groups.google.com/group/excel-macros 3. www.ozgrid.com 4. www.cpearson.com 5. www.chandoo.org 6. www.exceluser.com

31. QUERIES ?