Excel Macro Basics

Transcript

EXCEL MACROS EXPERIENCE THE JOURNEY R. BHAARATH PRE-REQUISITE: This course Material is intended for all users who have sufficient Knowledge of Microsoft Excel and possess a fair degree of exposure to its various features like 1. Referencing and Formatting Concepts 2. Range Name and Inter-workbook Linking 3. Conditional Formatting, 4. Using Functions in Excel such as VLookup, Sum If, If, AND, OR etc. 5. Text Functions 6. Charting Methods. 7. Text To Columns 8. Sorting and Sub Totals 9. Data Analysis Tools. 10. Filters and Advanced Filters 11. Using Pivot Tables and Pivot Charts 12. Knowledge of Array Functions etc. COVER  A Scenic view during the journey from Hassan to Mangalore COPYRIGHT © 2008-2015 No Part of this Course Material Publication may be reproduced and / or used for any commercial purposes whatsoever without the express permission of Bharath Infotech. Bharath Infotech reserves the sole rights to the Intellectual property of this material. PRE-REQUISITE: This course Material is intended for all users who have sufficient Knowledge of Microsoft Excel and possess a fair degree of exposure to its various features like 1. Referencing and Formatting Concepts 2. Range Name and Inter-workbook Linking 3. Conditional Formatting, 4. Using Functions in Excel such as VLookup, Sum If, If, AND, OR etc. 5. Text Functions 6. Charting Methods. 7. Text To Columns 8. Sorting and Sub Totals 9. Data Analysis Tools. 10. Filters and Advanced Filters 11. Using Pivot Tables and Pivot Charts 12. Knowledge of Array Functions etc. COVER  A Scenic view during the journey from Hassan to Mangalore COPYRIGHT © 2008-2015 No Part of this Course Material Publication may be reproduced and / or used for any commercial purposes whatsoever without the express permission of Bharath Infotech. Bharath Infotech reserves the sole rights to the Intellectual property of this material. Table of Contents PRE-REQU PRE-REQUISIT ISITE: E: ............................................................................................................. 2 Automation using Macros ...................................... ......................................... ... 6 Macros Macros ....................................................................................................................... 6 Macro Macro Securi Security ty ........................................................................................................... 7 What What Is a Digital Digital Certific Certificate? ate? ..................................................................................... 8 What Is a Signature? Why Do We Need Them? ......................................................... 8 How to Obtain Obtain a Digital Digital Signature Signature?? ............................................................................ 9 To Install Install the SelfCer SelfCertt Tool ...................................................................................... ...................................................................................... 10 Locatin Locating g SelfCer SelfCert.exe t.exe ............................................................................................... 10 Create a Digital Certificate Using SelfCert.exe ......................................................... 10 The Macro Macro Recorde Recorderr ................................................................................................. 13 Relativ Relativee and Absolute Absolute Recordi Recording.............................................................................. ng.............................................................................. 14 Where Where Macros Reside............................................................................................... Reside............................................................................................... 15 Locating the Personal Macro Workbook ................................................................... 16 The Dangers Dangers of Macro Macro Shortcuts Shortcuts .............................................................................. 17 Programming Spreadsheets with VBA .................... 18 The Visual Visual Basic Basic Editor Editor ............................................................................................ 18 Parts of the VBA Editor............................................................................................ Editor............................................................................................ 19 Modules Modules and Macros Macros ................................................................................................ 21 Understanding Macro Code .................................... ............................... ..... 22 The Anatomy Anatomy of a Macro Macro .......................................................................................... 22 Runnin Running g the Macro from from VBA ................................................................................. 24 Objects –  The Grammar Grammar ........................................................................................... 25 Using Using Properti Properties es and and Methods Methods .................................................................................. 26 The Object Browser Browser.................................................................................................. .................................................................................................. 27 The With With Statement Statement .................................................................................................. 29 Entering Text in the Current Cell .............................................................................. 30 Moving Moving to Other Other Cells Cells .............................................................................................. .............................................................................................. 31 Editing Editing Specifi Specificc Cells Cells ............................................................................................... 32 Using Using the Imm I mmedia ediate te Window Window .................................................................................. 33 Formatti Formatting ng Cells Cells ....................................................................................................... 35 3 Using Variables ........................................................................................................ 37 Constants.................................................................................................................. 39 Object Variables ....................................................................................................... 39 Decision Making with VBA ...................................... 43 The IF Statement ...................................................................................................... 43 The Select Case Statement........................................................................................ 44 Procedures, Functions and Parameters .................. 46 Functions.................................................................................................................. 46 Pass by Value (ByVal) and Pass by Reference (ByRef) ............................................ 47 Optional Arguments ................................................................................................. 49 Creating Custom Functions ...................................................................................... 50 Repeating Actions with a Loop ............................... 54 FOR –  NEXT LOOP ................................................................................................ 56 FOR EACH –  NEXT LOOP..................................................................................... 58 IN-BUILT Functions in VBA .................................... 61 The Message Box Function ...................................................................................... 61 The Input Box Function............................................................................................ 65 Text Related VBA Functions .................................................................................... 68 Date and Time Related VBA Functions .................................................................... 70 Range Related VBA Functions ................................................................................. 71 Using Worksheet Functions in VBA ......................................................................... 73 Using the FORMULA Property for the Range .......................................................... 76 Arrays in VBA ......................................................... 77 Using LBound and UBound ..................................................................................... 78 Using ReDim and ReDim Preserve for Dynamic Arrays ........................................... 78 Assigning Range Values Directly To an Array and Vice Versa ................................. 79 Dynamic Arrays using Variants ................................................................................ 80 Using the Split Function to convert a string to arrays ................................................ 81 Using the Join Function to convert a 1 dimensional array to a string ......................... 82 Using Excels Features through VBA ....................... 82 The Sort Method ...................................................................................................... 82 The AutoFilter Method ............................................................................................. 86 4 The Text to Column Method .................................................................................... 88 The Advanced Filter Method .................................................................................... 92 5 Automation using Macros No one wants to repeat the same task over and over again. Excel users especially hate the drudgery of repetitive work like data entry and formatting cell after cell after cell. Fortunately, Excel offers a serious time-saving tool called macros - miniature programs that automatically perform a series of steps within any workbook. Excel macros are written in a proper programming language called VBA (short for Visual Basic for Applications). VBA is a scaled-down version of  the incredibly popular Visual Basic p rogramming language, and it's fined-tuned for Excel and other Office applications. Fortunately, you don't need to be a programmer to use VBA. In fact, you can create a simple macro using a special Excel tool called the macro recorder. The macro recorder works like a tape recorder, but instead of recording audio, it records keystrokes and mouse actions as you perform them. Macros Macros can automate everything from simple tasks to complex operations, making your life immensely easier. Here are examples of  tasks people commonly use macros for:      Quickly inserting a group of cells you need often (like a company header for a report). Applying complex formatting to multiple rows or columns. Cleaning up text, rearranging cells, or transferring information from one place to another. Printing data in a specific format. This operation could involve switching to a custom view, selecting a part of a worksheet, and choosing specific print options (like paper size or the number of  copies). Analyzing a selection of cells, perhaps by adding a set of functions or even by generating a specialized chart. These examples are just the tip of the macro iceberg. When you create a macro, you can use the full range of Excel's features. It's like having a personal assistant, skilled in the ways of Excel, ready to carry out whatever instructions you provide. Excel gives you two different ways to create a macro:  Write the macro by hand, using pure VBA code. This option isn't as difficult as you may think, but it isn't easy either. The problem 6 isn't learning the syntax, or rules, of the VBA language (which are fairly basic); it's learning how to find all the Excel features you want to use. When you want to enter text in a cell , create a new worksheet, or print a selection, you need to find the right Excel object that can handle the job. Objects are programming tools that let you get at features you need. If you want to write macro code that spell checks your spreadsheet, you need to know the name of the spell checker object.  Record the macro using the Excel macro recorder. You turn the recorder on, and then go about your business, entering text, navigating a worksheet, and selecting choices from Excel's ribbon. While you work, Excel records each operation and translates it into the corresponding VBA code. When you're finished, you can stop the recorder, save the macro, and replay it to repeat all the actions you just performed. Using the macro recorder is the simplest approach, particularly if you've never used a programming language before. However, writing macros by hand is much more powerful and flexible. It lets you create macros that can make decisions, repeat actions in a l oop, and even prompt whoever's viewing your worksheet to input some information. Note: There's a mid method between writing macros yourself and having Excel record your actions. You can record a macro with the macro recorder, and then tweak the VBA code with the Visual Basic editor in order to make it more powerful. Macro Security Before embarking into Macros, we need to remember that there are certain inbuilt security features in Excel. By default the security level for Macros will be high. This is good as it prevents undetected malicious code from entering into Excel. But, at the same time, this poses a serious problem to us as we will not be able to use Microsoft Excels macro feature. Macro security is available under Tools – Macros – Security. Security settings are: 1. 2. 3. 4. Very High High Medium Low  7 While “Very High” does not allow any macros unless it is hard coded into the system, High security allows macros only from Trusted sources. This implies sources from Microsoft or its associates. To get a trust ed Digital certificate for each macro Microsoft is to be contacted. Low level of security entertains all macros and does not intimate the user. This is a potential risk. Any newly sent Excel file could contain potentially harmful viruses. Medium level security allows all macros but while opening the file it clearly prompts the user whether to enable or disable macros. If the Excel file is from an unknown party, the user could disable the macro. If  the file is known, then the user could enable the macros. This option is then, by far the best option and we advocate the user to keep this option while practicing macros and throughout all the initial stages. What Is a Digital Certificate? Digital signatures and certificates of authenticity can be applied to executable programs, ActiveX controls, or Office Visual Basic for Applications macros. These signatures provide you with the assurance that what you are about to use comes from a realiable source and that it has not been tampered with. Digital certificates help to eliminate macro viruses from being introduced into your Office documents, your computer, and your local network. A digital certificate is an ID that is carried with a file. To validate a signature, a certifying authority validates information about the software developers and then issues them digital certificates. The digital certificate contains information about the person to whom the certificate was issued, as well as information about the certifying authority that issued it. When a digital certificate is used to sign programs, ActiveX controls, and documents, this ID is stored with the signed item in a secure and verifiable form so that it can be displayed to a user to establish a trust relationship. What Is a Signature? Why Do We Need Them? Office has introduced digital signatures to help users distinguish legitimate code from undesirable and potentially damaging code. If you open an Office document and see a macro security warning with digital signature information, you can feel reasonably confident that the person (or corporation) signing the macros also created them. You can choose to trust all macros signed by this person by clicking to select the Trust all 8 macros from this source check box. From then on, Office will enable the macros without showing a security warning for any future documents containing macros signed by this trusted source. A digital signature is the public certificate plus the value of the signed data encrypted by a private key. The value is a number generated by a cryptographic algorithm for any data that you want to sign. This algorithm makes it nearly impossible to change the data without changing the resulting value. So, by encrypting the value instead of the data, a digital signature allows the end user to verify the data was not changed. How to Obtain a Digital Signature? To obtain a digitial signature, first, one must obtain a digital certificate. One option is to get a fully certified certificate from a certificate authority. Both individuals and commercial entities can obtain a commercially authenticated certificate for their code. To learn about the application process and requirements, see Introduction to Code Signing at the Microsoft Authenticode Web site. A list of Certificate Authorities is provided at the following Microsoft Web site: http://msdn2.microsoft.com/en-us/library/ms537361.aspx (http://msdn2.microsoft.com/en-us/library/ms537361.aspx) A Certificate Authority can issue you a digital certificate for code signing for a fee. The Certificate Authority will do an in -depth identification check before issuing a digital certificate for signing code. Be sure to get a digital certificate that can sign code with Microsoft Authenticode (Verisign calls this Class 2 or 3; Thawte calls this Developer Certificates ), rather than one that can only sign e -mail. If you try to use a digital certificate that is not authorized to sign code, Office will warn that the digital certificate is not trustworthy. You can create your own certificate for personal use or testing purposes with the SelfCert.exe tool provided in Office. This unauthenticated certificate will allow you to sign your own macros, and to trust this digital certificate so that all macros you si gn will not generate a security warning. This type of certificate is not validated by a Certifying Authority, therefore, other users will see a warning not to trust it. A malicious virus might be digitally signed by a digital certificate by the name of "Microsoft Corp." However, the security warning will warn you 9 that this is not an authenticated certificate, and therefore the certificate cannot be from Microsoft. To Install the SelfCert Tool If you do not see a program icon for Digital Signature for VBA Projects in your Office folder, to install the tool, follow these steps: 1. Quit all Office programs. Click Start, point to Settings, and then click Control Panel. 2. In Control Panel, double-click Add/Remove Programs. 3. On the Install/Uninstall tab, click to select Office product . If you are using a stand-alone version of one of the Office programs, click to select the appropriate product in the list. Click Add/Remove. 4. In the Setup dialog box, click Add or Remove Features. 5. In the Microsoft Office Update Features dialog box, click the plus sign (+) to expand the features list next to Office Tools. If  the sign is already a minus sign (-), the features list is already expanded. 6. Click the symbol next to Digital Signature for VBA projects, and then click Run from My Computer in the list that appears. Click Update Now. Locating SelfCert.exe By default you can find Selfcert.exe in the following location; C:\Program Files\Microsoft Office\Office If it is not there run Office setup and choose a custom installation and then advanced customization. Expand the Office Shared Features Section and select Digital Certificate for VBA Projects to run from your computer. For Office 2000 you can find this under the Office Tools section. Create a Digital Certificate Using SelfCert.exe Simply run SelfCert.exe. It will prompt you to name the certificate. Use your name, company name etc. 10 As explained in the dialog a selfcert is for personal use only Signing your code Back in the VBA Editor (ALT+F11) where you created the macro choose Tools-> Digital Signature. You‟ll see that the current VBA project isn‟t signed. Press the Choose… button and you‟ll get a screen to select a certificate. Now you can choose the certificate you just created. Currently the project is unsigned  11 Select the certificate you just created and click OK  Now the project is signed  Running the signed macro for the first time In the main Excel Macro window choose Tools-> Macro-> Security… and make sure it is set to “Warnings for signed macros; all unsigned macros are disabled” in Excel Macro 2007 and for any previous versions it reads “High. Only signed macros from trusted sources will be allowed to run. Unsigned macros are automatically disabled.”  Now that we‟ve signed the code and verified that the security settin gs are set correctly you must close Excel Macro. You‟ll get prompted if you 12 want to save changes to your VBA project. Choose “Yes”. Once Excel Macro is fully closed start it again. Now that we‟re all set you can run the macro by Tools -> Macro-> Macros… (ALT+F8). Since it is the first time that you are using the certificate you‟ll be prompted what to do. Select that you‟ll always trust the certificate from this publisher and you‟re then done! Now your own macros will run without any security prompts and you can still enjoy Excel Macro with your security settings on high. The Macro Recorder The macro recorder is easy to use, but keeps a few key points in mind while you're recording so that you end up with a great collection of  really useful tools:     Excel captures every command or keyboard shortcut you use. In other words, don't do anything that you don't want recorded as part of the macro, unless you're willing to edit the VBA macro code after the fact. Also, try to avoid switching to another program while you're recording an Excel macro. Even though the macro recorder ignores anything you do outside of Excel, it is easy to confuse yourself and inadvertently add macro code you don't want by jumping back and forth. You don't need to work fast. The macro recorder doesn't record anything in between each action you perform. Try to be generic. The ideal macro is general enough that you can reuse it in a wide range of scenarios. If you make a macro so specific that you can use it only once, the macro won't get much use, and all your hard work will be wasted. use, and all your hard work will be wasted. To record a macro, you need to select Tools – Macro – Record New Macro. A dialog box appears. You need to give an appropriate name. The default name is Macro1, Macro2 etc. There is a provision for giving a keyboard shortcut for the macro. There is also a provision to record the macro in this workbook, new workbook or personal macro workbook. Once this is done, recording starts and after completing the work, the stop recording button must be clicked to complete the process. Once recorded, the macro is available in the Tools-Macro option and can be run by selecting this or by the shortcut key. 13 Before you jump into the macro recording studio, it helps to understand the difference between recording modes and to know where Excel saves your macros. Those topics are covered in the next two sections. Relative and Absolute Recording When you click a command, Excel's macro recorder knows exactly what you're doing. However, sometimes Excel needs a little guidance from you in order to decide how to interpret some of your actions. When you type in text, Excel can interpret your action in two different ways, depending on which of two recording modes you're in:   Absolute reference mode. In absolute reference mode, Excel stores the absolute references for the cells that you're modifying. When you play the macro again, the macro affects only these cells. Relative reference mode. In relative reference mode, Excel tracks how far you move from your starting position. That means when you play the macro again, Excel takes your current location into account. You can most easily understand the difference between the two modes by following an example. Imagine you move to cell A1 and then start recording a macro. You then move two columns to the right to cell C1, type in the number 42, and save the macro. 14 If you used absolute reference mode, here's the series of instructions that Excel stores in your macro: 1. Move to cell C1. 2. Enter the number 42. On the other hand, if you used relative reference mode, Excel stores this list of instructions: 1. Move two columns to the right (from wherever the active cell is) . 2. Enter the number 42. You'll see the difference if you clear the worksheet, move to cell E10, and play the macro. If you used absolute reference mode when you recorded the macro, then the macro always returns to cell C1 to enter its information. If you used relative reference mode, Excel enters the number 42 two cells to the right of the current cell, in cell G10. Tip: Absolute reference works if your data always needs to be in the same position. This need arises most often when you have to add some sort of header information at the top of a worksheet. Relative reference works if you need to repeat a task in several pla ces like bold, italicize, and enlarge the font etc. The recording can be switched in between to absolute or relative. In the stop recording toolbar, if there is a shade around the Excel icon then the referencing is relative. Otherwise, it is absolute. Where Macros Reside Once you've crafted the perfect macro, you need to make sure Excel stores it so that you can find it later on. In Excel, every macro's attached to a workbook and saved in that file. When you open a workbook that contains a set of macros, Excel makes them available instantly. Excel beginners often assume that before you can use a macro in a particular workbook, you need to store the macro inside that workbook. In fact, macros have a much greater range. As soon as you open a workbook that contains any macros, Excel makes those macros available to every other workbook that's currently open. In other words, imagine you're editing a workbook named SalesReport.xls, and you open another workbook named MacroCollection.xls, which contains a few useful macros. You can use the macros contained in MacroCollection.xls with SalesReport.xls without 15 a hitch. Once you close MacroCollection.xls, those macros are no longer available. This design makes it easy to share and reuse macros across workbooks. It is up to you whether you want to store macros in your most commonly used workbooks or create standalone workbooks that hold collections of useful macros. Companies evolve specific procedures for macro storage and prefer separate macro workbooks. When you record a macro, Excel gives you three slightly different storage options:    This Workbook. If you choose this option, Excel stores your macro in the current workbook. New Workbook. If you choose this option, Excel automatically creates a new workbook and stores your macro there. Personal Macro Workbook. If you choose this option, Excel stores your macro in a special hidden workbook named Personal.xls. The Personal.xls workbook opens automatically whenever you start Excel (although it remains hidden), so macros in this workbook are always available no matter what workbook you're using in that computer / server. If the file is taken outside, you will not find the personal.xls file. Locating the Personal Macro Workbook Where's the personal macro workbook hidden? It makes sense to save your most useful macros in your personal macro workbook, so that they're always available, without requiring any extra steps. Excel stores the personal macro workbook in a file called Personal.xls, which lives in a folder named “XLStart”. You can find the XLStart folder in a location inside the Microsoft folder. The XLStart folder is usually right next to your templates folder. Keep in mind that Excel doesn't actually create the personal macro workbook until you add your first macro to it. So, unless you've recorded at least one macro for the personal macro workbook to hold, there's no point hunting for it. Useful Tip: One useful place to put macros is in an Excel template. That way, every time you or anyone else creates a new workbook based on the template, you have immediate access to the macros. In order to put 16 macros in a template, record them inside the template using the ThisWorkbook option. Note that ThisWorkbook implies the Work Book in which the code is written. To imply the macro for the current Work Book you need to use ActiveWorkbook. The Dangers of Macro Shortcuts Using shortcut keys can be dangerous because Excel doesn't warn you i f  you choose a shortcut key that correspond to another Excel task. If this sort of conflict occurs, Excel always uses the shortcut key for the macro. This habit can cause confusion if other people use your macros and you replace a common shortcut key. Imagine their surprise when they hit Ctrl+S to save a document and end up triggering a macro that turns all negative numbers positive. Here are some common key combinations that you should never assign to macro shortcuts because people use them too frequently: o o o o o o o o o o Ctrl Ctrl Ctrl Ctrl Ctrl Ctrl Ctrl Ctrl Ctrl Ctrl + + + + + + + + + + S (Save) P (Print) O (Open) N (New) X (Exit) Z (Undo) Y (Redo/Repeat) C (Copy) X (Cut) V (Paste) To avoid problems, always use Ctrl + Shift + letter macro key combinations, because these combinations are much less common than the Ctrl + letter shortcut keys. And if you're in doubt, don't assign a shortcut key when you create a new, untested macro, especially if you think it may conflict with an Excel shortcut key. You can always assign one later if the macro proves extremely useful. Note1: The macro recorder stores code, not your specific actions. That means it doesn't matter whether you activate a feature using a shortcut key or the Quick Access toolbar or Menu. In all cases, the macro code is the same. 17 Note2: If you get an error message informing you that Excel has turned off the macros in your workbook (which it does automatically when you close and reopen it), you need to t ake a few extra steps. Select Tools – Macros - Security and set it to medium. You then need to close and reopen the file in question. Note3: Once you play a macro, you're stuck with the changes. Sadly, the Undo feature can't reverse a macro, so make sure you save your worksheet before trying out an untested macro or on a crucial data. Tip: If you created a shortcut key for your macro, you can trigger the macro just by pressing the appropriate key combination, without using the Macro dialog box. Also, you can attach a shortcut key to an already created macro. Just select Tools – Macros (Alt + F8). When the Macro dialog appears, choose the corresponding macro and select options. Then enter (or change) the shortcut key. Programming Spreadsheets with VBA When you use Excel's macro recorder, you actually build a small program. Excel translates every action you take, from selecting a menu item to editing a cell, into a line of VBA code (short for Visual Basic for Applications), and inserts it into your new macro. The obvious benefit is that you can use the macro recorder without knowing the first thing about programming. There are limitations, however, to this. You'll find that you can record only actions that you can perform yourself. If you want to create a macro that inserts a column with 100 identical cell values, you need to go through the drudgery of typing in each cell value so that the macro recorder knows what to capture. A more serious problem is that when you record a macro, you can respond only to the worksheet that's in front of you. If you want to make a more flexible macro that has the ability to examine a variety of cells a nd the intelligence to respond to different conditions, you need to tap into some of VBA and its advanced capabilities. The Visual Basic Editor Before you can modify a macro, you first need to find it. The tool you use to edit macros isn't actually part of Excel. Instead, it's a separate application called the Visual Basic editor. The Visual Basic editor is 18 designed to edit the macros created for Office applications like Word and Excel. To show the Visual Basic editor window from inside Excel, press Alt + F11 or choose Edit in the macro dialog window. When you do, Excel launches the standalone window shown in Figure. Figure-1: The Visual Basic editor window is divided into three main regions: the Project window, the Properties window, and the Document window. The Document window is where your code appears (it starts off  blank). The Project window lists all your projects (and the personal macro workbook). The Properties window shows individual settings you can change for the currently selected project. Module Window Project Explorer Window Coding Area Property Window Immediate Window Parts of the VBA Editor The Project Explorer window considers each open workbook as a project to be displayed. That apart, all add-ins and the Personal Macro 19 Workbook will be displayed, if they are open. Under each workbook, there will be worksheets and Modules. This is as shown below: This Workbook object and each of the worksheet objects are principal Excel objects for Event handling. Properties for each of the worksheets, workbook and each of the modules can be seen in the properties window. Select any one of the Excel objects say, a worksheet in the project Explorer window and press the F4 key to see the properties window for that object: Properties of the worksheet object are displayed here. Going back to the project Explorer, if we double click on any of the modules, a c ode window will appear showing the code details and is as shown: 20 This shows all the macros written in that module. On the top right drop down you have the option of choosing the appropriate subroutine and going directly to it. Also, on the bottom of the editor, there may be a window called as the immediate window. If this is not present it will be available in the View Option or, by typing „Ctrl + G‟. This window is aptly named. Any single line VBA instructions can be given and the result in Excel is instantaneous. This is used as a testing tool and any one time code can be directly given in this area. Modules and Macros Modules contain macro code. Ordinarily, Excel creates a new module the first time you record a macro, and names it Module1. Excel then pl aces every macro you record into that module. If you want, you can separate macros into different modules for better organization. However, the number of modules you create, or the module you use for a given macro, has no effect on a macro's function. Every module contains one or more VBA subroutines. Each subroutine is a named unit of code that performs a distinct task. In the VBA language, subroutines start with the word Sub followed by the name on a separate line, like all VBA commands. They end with the statement End Sub. Here's an example: 21 Sub FirstMacro () „Your macro code goes here. End Sub This small snippet of VBA code illustrates two important principles. First, it shows you how to start and end any subroutine (by using the statement's Sub and End Sub). Secondly, this code also shows you how to create a comment. Comments are special statements that Excel ignores completely; they're notes to you (like explaining in plain English what the following or preceding line of code actually does). To create a comment, you just place an apostrophe (') at the beginning of the line or a “rem” word at the beginning of the line . Note: Good Programmers always leave comments in their code. Comments are the best way to clarify what you want the code to do, so you can remember when you review it a few months later. In Excel, each macro is a separate subroutine or Procedure. (In some cases, you may want to break a complex macro down into more than one subroutine, but the macro recorder doesn't do this for you). When the macro recorder goes to work recording a new macro, it generates a new subroutine using the name of the macro that you assigned. It also adds any description you entered when you were creating the macro. Then, it places all the code it generates into the subroutine. To take a look at the subroutines in a module, double -click the module in the Project window. You can scroll through this window to see all the macro procedures it contains. Understanding Macro Code The Anatomy of a Macro A good place to start learning about the features of a macro is to record a Macro. Consider a Recorded and slightly edited Macro as shown below called as the FormatRow  Following is the complete VBA code for the FormatRow macro. To make it easier to analyze, each line of code has been numbered, and the code has been simplified a little from what Excel generated automatically: 1 2 3 Sub FormatRow () ' ' FormatRow Macro 22 4 5 6 7 8 9 10 11 12 ' Macro recorded 4/6/2005 by R. Bhaarath ' ' Keyboard Shortcut: Ctrl + Shift + F ' ActiveCell.Rows.EntireRow.Select Selection.Interior.ColorIndex = 35 Selection.Interior.Pattern = xlSolid ActiveCell.Offset (2, 0).Select End Sub Line 1 starts the macro and defines its name. Lines 2-7 are simply comments. They appear in green writing in the editor and don't actually do anything (other than convey information to the person reading the code). The action gets started with line 8. Here, the code accesses a special object called Active Cell, and uses it to select the entire current row. Next, line 9 changes the background color of the selected cells (to light green), and line 10 sets the type of background fill (to solid). Both of  these lines use the Selection object. Finally, line 11 returns to the Active Cell object, and uses its Offset command to jump down two rows from the current cell. Line 12 marks the end of the macro code routine. Altogether, this macro doesn't do much, but the code is quite dense, and fairly difficult to read on first sight. The problem isn't the VBA language. In fact, the only language-specific details in this example are the Sub and End Sub statements (not to mention all those odd periods, which are explained later on). The real complexity comes from understanding all the different objects that are available. To write a macro like this one, you need to know that there's an Active-Cell object that lets you select rows and move from row to row, and a Selection object that lets you adjust formatting. These details make up Excel's object model. If you want to perform any task in a macro, from printing a document to saving a worksheet, you need to first figure out which object can do your bidding. 23 Running the Macro from VBA The Macro can be directly run from VBA by pressing F5 or the Run Button. Furthermore for purposes of analysis, the F8 button is very useful as it will enable running Macros line by line. Further, you can have toggle break points at any line of code while running the macro. The macro stops at that line of code awaiting your approval to continue running by pressing the F5 or F8 keys. This is as shown below: Toggle Breakpoint Editing Toolbar Yet another interesting set of i cons is the Comment and uncomment block to give or remove comments for a collection of statements. There also bookmarks for rechecking that area of code. The listing and information icons help while writing code. The complete word icon also helps in completing the code when half written. It is a good idea to go through all these icons in the editing tool bar for easy programming and checking. It will help a lot while doing serious programming. As it is quite self explanatory, we will not be discussing these details here. 24 Objects – The Grammar In many programming languages, including VBA, everything revolves around objects. So what exactly is an object? In the programming world, an object is nothing more than a convenient way to group together some related features. In the FormatRow macro, the two objects are: one named ActiveCell, and one named Selection. The ActiveCell object bundles together everything you may want to do with the current cell, including editing, selecting, and moving from cell to cell. The Selection object offers other features for modifying a group of  selected cells, including ways to change their borders, background colors, and font. Programmers embraced objects long ago because they're a great way to organize code (not to mention a great way to share and reuse it). You may not realize it at first, but working with the ActiveCell object is actually easier than memorizing a few dozen different commands to accomplish the same tasks. Once you learn about ActiveCell, you immediately know what object you need to use for any task related to the current cell. Visual Basic is an object-oriented language. This means that all the items in Excel are thought of as objects. There are more than a hundred of them. Common Excel objects are: The Excel application (the largest object) Workbook Worksheet Range Chart Legend Style • • • • • • • You can use objects in a number of different ways. Altogether, you interact with objects in three ways:    Properties. Properties are pieces of information about an object. You change properties to modify the object or how it behaves. The FormatRow macro uses the Color Index property to change the background color of a row. Methods. Methods are actions you can perform with an object. The FormatRow macro uses the Select method to select the current row. Events. Events are notifications that an object sends out that you, as the macro programmer, can respond to. 25 Using Properties and Methods So how do you change properties or use methods? The answer is the period. Imagine you have a Cell phone object that provides a Call method. In this case, you use the following syntax to call: Cellphone.Call The same technique works with properties, but it tends to look a little different. With properties, you typically want to perform one of two actions. Either you want to retrieve information about the property, or you want to change the property. To change the property value, you use the equal sign (=). The following line of code changes the number of  people in a car by modifying the Passengers property: Tip: Think of the equal sign as an arrow pointing to the left. It takes whatever information's on the right side (in this case, the number 2) and puts it into whatever is on the left side. Car.Passengers = 2 Application.Workbooks refers to all workbooks currently open. Workbooks.Item (1) refers to the first workbook and is usually abbreviated to Workbooks (1) A workbook (“Sales.xls”) refers to the workbook by name. A workbook usually contains worksheets, each of which contains ranges of cells. So, you might get cell B3 referred to as Workbooks ("B1.xls").Worksheets ("Sht1").Range ("B3").Select If you do not specify a particular workbook or worksheet, Visual Basic will use the active workbook and the active worksheet. If that is according to your wishes, then the long description above could be reduced to just Range ("B3").Select as in the macro you recorded. Each object has its own characteristics. In general, properties control the appearance of objects. Thinking again about the Range object, typical properties would be • • • Column Width Formula Value • • • Font Text Offset Objects have methods that perform actions on them. If you were considering the Range object, then examples of methods would be: • • • Activate Clear Copy • • • Cut Delete Select The syntax of many statements in Visual Basic is Object.Method 26 The Object Browser Excel‟s VBA has several objects. These objects follow a hierarchy. This hierarchy starts with the fundamental object called as the Application object. All objects are sub objects to it. The Excel object Model is as shown below: 27 Objects above can call objects below. The worksheet object can call the range object using the dot (.) operator. The Object Browser is a collection of all Excel and VBA objects and their properties and methods that are present. When more references are added, the scope of the object browser increases. On pressing the Function Key F2, the object browser opens and is as shown below: Any keywords can be searched in the object browser to see if it is a property or method. Search can also be done on the classes in the left and appropriate member properties and methods will appear on the right. Even if it is not used much in the earlier stages, it becomes very useful as you start to gain proficiency in writing code. Note: In a macro, more than one statement can be written in a single line as shown. But this is often avoided as it becomes difficult to remove errors and modify the procedure: Sub Line () 28 A=5: B = 10: C = A + B: MsgBox C End Sub The With Statement Once you find the right object, you'll probably need to use several of its properties or methods. To save the effort of typing in the object name each time, you can use a “With” block. The “With” - block starts by identifying an object that you want to use. The following statements (up until the final End With) don't need to include the object name. Instead, they can skip to the period, and use it to start the line. The FormatRow macro uses the following statements to set the formatting of the current selection: Selection.Interior.ColorIndex = 35 Selection.Interior.Pattern = xlSolid You can rewrite this statement using the “With” statement as follows: With Selection.Interior .ColorIndex = 35 .Pattern = xlSolid End With Either way, the result is the same. But it helps to be familiar with the “With” statement because the macro recorder uses it frequently. In fact, if you look at the original FormatRow code that the macro recorder generated, you'll find that it uses a “With” block. Consider the following statement (line 9 from the FormatRow macro): Selection.Interior.ColorIndex = 35 In this example, two objects are at work. The Selection object contains another object named Interior. Technically, Interior is a property of the Selection object. Unfortunately, the Selection object doesn't give you any way to change the background color of the selected cells. To do that, you need to use the ColorIndex property, which is a part of  the Interior object. That's why this statement has two periods. The first one accesses the Interior object, and the second period accesses the ColorIndex property. To make life even more interesting, consider line 8, which has three periods: ActiveCell.Rows.EntireRow.Select 29 In this case, the ActiveCell object has a property called Rows, which is also an object. The Rows object has a property named EntireRow, which is yet another object. The EntireRow object is the one you want. It provides the Select method that highlights the entire current row. Note: Coming to the ColorIndex Property, different colors have been marked for index numbers. This list is as shown below: Colors can also be identified using the RGB property. Any custom color in an application such as Paint displays the RGB colors. That particular color can be incorporated into Excel using the R, G and B number codes. For Example, to have a blue font we can write: Selection.Font.Color = RGB (0, 0, 255) To have a Yellow Background (Interior) we can write: Selection.Interior.Color = RGB (255, 255, 0) Entering Text in the Current Cell A few examples can go a long way to showing you how a typical macro works. First of all, check out the following macro subroutine, which represents one of the simplest possible macros you can create. It inserts the text in the current cell (replacing whatever content may already be there). Sub RbsMacro () 30 ActiveCell.Value = "My First Macro" End Sub With a little more effort, you can edit the current cell value instead of  replacing it with new content. Imagine you want to take the current text value, and add the message "Note: " before the text. To accomplish this feat, you can use the following macro code: Sub RbsMacro () ActiveCell.Value = "Note: " & ActiveCell.Value End Sub In this macro, Excel begins by joining together two pieces of text: the word "Note: " and whatever content's in the current cell. It then takes that combined piece of text and places it back into the cell. In this macro, the ampersand symbol (&) is key. It's a concatenation operator, which means it glues together different pieces of text. You can use a similar approach to adjust a cell that has a number in it. In this case, you can use all the ordinary numeric operators, like +, /, *, and ^. Here's an example that multiplies the current cell value by 2, subtracts 1, and enters the new value: Sub RbsMacro () ActiveCell.Value = (ActiveCell.Value * 2) - 1 End Sub Note: When using arithmetic operators, make sure the current cell contains a valid number. Otherwise, your code fails with a cryptic "type mismatch" error, which is a reminder that Excel can't perform numeric calculations with text. Moving to Other Cells The ActiveCell object is the starting point for everything you want to do with the currently selected cell. However, it doesn't let you change the content of other cells. If you want to do that, you need to access these cells by using the Offset property. The property looks a little more complicated than other properties because it needs two pieces of information: a row offset and a column offset (in that order). The row offset tells Excel how many rows down you want to move. The column offset tells Excel how many columns to the right you want to move. If you want to move up or left, you need to use a negative number. The following macro places the phrase Top cell in the current cell, and then places the phrase Bottom cell in the cell that's immediately underneath it. 31 Sub RbsMacro () 'Change the top cell. ActiveCell.Value = "Top cell" 'Change the bottom cell. ActiveCell.Offset (1, 0) = "Bottom cell" End Sub You need to note one important factor about this code. Although it changes two cells, it doesn't actually move to the second cell. Instead, when the macro ends, you're still positioned in the top cell. If you actually want to move to the new cell, you need to use the Activate or Select method, as shown here: Sub RbsMacro () 'Change the top cell. ActiveCell.Value = "Top cell" 'Move down one cell. ActiveCell.Offset (1, 0).Select 'Now this changes the bottom cell. ActiveCell.Value = "Bottom cell" End Sub The following table gives examples of ways in which to refer to a cell, or range of cells, in the A1 reference style using the Range method. Reference Refers to: Range ("B1") Cell B1 Range ("B1:C6") Range B1 to C6 Range ("B1:D7, F8:J20") Two areas of cells Range ("C: C") Column C Range ("7:7") Row seven Range ("B: D") Columns B to D Range ("2:6") Rows two to six Editing Specific Cells Using Value and Offset, you can romp around your worksheet changing cells as you please. The example macros you've seen so far use relative references, which means they start working in the current position in the worksheet. However, in some situations you want to move to a specific cell. In order to do this in macro code, use the Range object. The basic technique is easy. You supply the cell address (like A2) as an argument to the Range object, and then use “Activate” or “Select” to 32 move to the cell. If you just want to change the cell, you can use the Value property to alter its content without leaving your current position. Here's an example that shows both techniques: Sub RbsMacro () 'Change cell A1. Range ("A1").Value = "This is A1" 'Move to cell A2 and change it. Range ("A2").Select ActiveCell.Value = "This is A2" End Sub Interestingly, you can even modify multiple cells at once using a range reference (like A1:A2). In this case, if you set the value, that value appears in every selected cell. Sub RbsMacro () „Insert the text "Hello" in ten cells Range ("A1:A10").Value = "Hello" End Sub For a little more excitement, take a look at the next macro. It starts by creating a new worksheet for your workbook, and then it fills in several cells in that new worksheet. Sub RBsMacro () 'Create the worksheet using the Add method. ActiveWorkbook.Worksheets.Add 'Enter several cell values. Range ("A1").Value = "Bharath Infotech‟s Material" Range ("A2").Value = "Generated by your Trainer Bhaarath" Range ("A3").Value = "Generated" & Now () End Sub The last line uses a VBA Function called Now () to provide the date and time along with the word “Generated “ Using the Immediate Window The Immediate window can be effectively used to test code. Type the following details in the immediate window to see if the actions are carried out in Excel: Range ("A1:A10").Value = "Hello" Range ("B1:D7, F8:J20").Select Range ("C: C").Select Range ("7:7") .Select Range ("B: D").Select ActiveCell.Value = "Note: " & ActiveCell.Value 33 To obtain values in the immediate window, the statements must be preceded by a question mark (?). For example: ? Range (“B:B”).Count ? Range (“2:2”).Count ? ActiveCell.Value A NOTE ON SPECIAL CELLS SELECTION: Selection of special cells can be done in Excel. For example, to select only cells containing formulae, we type: Cells.SpecialCells (XlCellTypeFormulas).Select For selecting formulas in a Range i.e. say some selected cells, we type: Selection.SpecialCells (XlCellTypeFormulas).Select To select other types of cells the following details can be used: XlCellTypeBlanks XlCellTypeVisible XlCellTypeConstants XlCellTypeComments XlCellTypeAllValidation XlCellTypeAllFormatConditions – – – – – – Blank Cells Visible Cells Constant Numbers and Text All cells with Comments Cells with all validation Cells with Conditional Formats To select only Text cells among Constants or Formulae, you need to provide the optional value apart from the cell type. Example: Selection.SpecialCells (XlCellTypeConstants, XlText).Select The above line selects only Text Constants. But for this Instruction: Selection.SpecialCells (XlCellTypeFormulas, XlText).Select Excel would select all formulas whose resulting value is a Text. Values such as XlText are Visual Basic Constants. Other such values are: XlSpecialCellsValue XlErrors XlLogical XlNumbers XlTextValues Value 16 4 1 2 Suppose you would like a combination of Errors and Logical Values (False/True) then you would write: Selection.SpecialCells (XlTypeFormulas, 16 + 4).Select 34 Formatting Cells Conceptually, using macros to format cells is just as easy as using them to edit text. The difference is that you need to think about many more properties, because you can format a cell in dozens of different ways. You use the Selection object to perform any formatting you want. The Selection object includes top-level properties like Horizontal Alignment, Vertical Alignment, and Merge Cells, as well as some objects like Interior (which lets you set fills and patterns) and Font (which lets you configure the typeface and font size). Here's an example: Sub Formtr () „Select the cells to format. Range ("A1:C1").Select With Selection 'Note that the alignment properties take special constant values. .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .MergeCells = True End With 'Change the font of the selected cells. With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 14 End With End Sub This code selects a range of three cells (A1 to C1) and changes the alignment and font. Consider another Example: Sub Macro1 () Selection.NumberFormat = "\R\s #, ##0.00" End Sub Selection is an object that implies a pre-selected range before actually running the macro. Any Range object (and that includes selection) has the Number Format property to set custom formatting to a cell. In this case the entire pre-selected range is custom formatted. Notice that R and s are preceded by the slash “ \ ” character. This ensures that the letters R and s are not format specifiers. The letter s could stand for seconds in custom time formatting. This confusion is avoided. Likewise for all characters, the slash character is to be used. To have the slash character itself to appear as a formatted text, you need 35 to give 2 slashes. The first one prevents the second from behaving as a format specifier. Excel / VBA TIP: The Indian Style of Account Formatting The Indian system of accounting uses “Rs” as the currency symbol. Besides the comma separator appears after every two numeral digits barring the thousandth separator. For Example : Five Crores, 50000000 is to be formatted as Rs 5,00,00,000.00 Custom formatting uses “;” to distinguish positive, negative and zero numbers as well as text. Example: \R\s #,##0.00 ; (\R\s #,##0.00) ; \R\s - ; @ This is: Positive Numbers; Negative Numbers ; Zero ; Text Furthermore, * followed by a character would fill in that character to enable the number format to fill the column width. This feature is app licable to * followed by space as well. This is the format that is given as the accounting format in Excel: \R\s * #,##0.00 ; (\R\s * #,##0.00) ; \R\s * - ; @ But this can be changed by providing our own conditional custom formats. This way numbers up to less than a billi on can be set in Indian currency format. The format is as shown below: [>9999999]\R\s * #\,##\,##\,##0.00; First Section: For Numbers above Ninety Nine Lakhs [>99999] \R\s * #\,##\,##0.00; Second Section: For Numbers One Lakh and above \R\s * #,##0.00 Last Section: For other Numbers below One Lakh The entire statement would be: Selection.NumberFormat = "[>9999999]\R\s * #\,##\,##\,##0.00 ; “ & _  “[>99999] \R\s * #\,##\,##0.00;\R\s * #,##0.00" The entire statement being in quotes can be split by the concatenation operator ( & ). To let VBA know that you have the statement continuing in the second line you need to use the underscore “_ “operator. A space should be given before the underscore. The underscore cannot be used within quotes. So this format gives you currency in Indian style. Unless individual and total figures run over a billion, this number format would be adequate. 36 Using Variables Every programming language includes the concept of variables, which are temporary storage containers where you can keep track of  important information. In an Excel macro, you can use variables to get around problems that you just can't avoid with the macro recorder. Imagine you want to swap the content in two cells . On the surface, this operation seems fairly straightforward. All you need to do is copy the text in one cell, place it in the other, and insert the other cell's text in the first cell. Unfortunately, once you paste the new cell content into the second cell, you end up overwriting the content you want to put in the first cell. The easiest way around this problem is to use a variable to keep track of the information you need. To create a variable in VBA, use the oddly named Dim keyword (short for dimension, which is programmer jargon for "create a new variable"). After the word Dim, you enter the name of the variable. Here's how you'd create a variable named RB: Dim RB Once you've created the variable, you're free to put information in it and take information out. To perform both these operations, use the familiar equal sign, just as you would with properties. Here's an example that stores some text in a variable: RB = "Test this Text" The following macro puts it all together. It uses a variable to swap the content of two cells. Sub SwapWithCellRight () 'Create the variable you need. Dim RB 'Store the content that's in the current cell. RB = ActiveCell.Value 'Copy the value from the cell on the right into the current cell. ActiveCell.Value = ActiveCell.Offset (0, 1).Value 'Copy the value from the variable into the cell on the right ActiveCell.Offset (0, 1).Value = RB End Sub The RB variable used above is called as a variant. A variant data type can store Integers, Strings or any type of data. But it takes more memory and is not specific. Specific data-types are available such as Integer, Double, Single, String etc. To declare them we do it as shown: Dim Rb1 as Integer Dim Rb2 as String, Rb3 as String Dim Rb4 as Double etc. 37 They can then be used as other normal variables but they can only store that particular type of data. Different data types for variables are as shown below: VBA VARIABLE TYPES Byte: whole number between 0 and 255; requires 1 byte of storage Boolean: truth value ( True, False); 2 bytes % Integer : whole number between – 32768 and +32767; 2 bytes &  Long: whole number between – 2147483648 and +2147483647; 4 bytes @ Currency: fixed point number with 15 places before and four after the decimal point; 8 bytes Decimal: This is not an independent data type, but a subtype of  Variant ; the precision is 28 places; the number of places to the right of the decimal point depends on the size of  the number: A number whose integer part is ten digits will have the remaining 18 places 28 to the right of the decimal point; the allowed range of numbers is ±10 ; 12 bytes # Double: floating point number with 16-place accuracy; 8 bytes ! Single: floating point number with 8-place accuracy; 4 bytes Date: for dates and times; the date is limited to the period between 1/1/100 and 12/31/9999, the time to the range 00:00 to 23:59:59; 8 bytes $ String: a character string; the number of characters is limited only by the amount of  RAM (up to 2, 147, 483, 647 characters); 10 bytes plus 2 bytes per character Object : objects; the variable stores a pointer to an object; 4 bytes Variant : Default variable type, assumes one of the above variable types according to what is required (with automatic conversion); the memory requirement is at least 16 bytes, and with character strings 22 bytes plus 2 bytes per character Be wary of using the correct data type. Each data type has limits. For instance we find that the integer variable can take value only between 32767 and + 32767, failing which, a Data Overflow Error occurs. (Run Time Error 6) Consider the following example: Sub ErrorMacro () Dim n1 As Integer n1 = 65536 MsgBox "The cell value is” & n1 End Sub The variable n1 is an integer that takes a value above 32767 causing an error. But if n1 = 6653 only, you will find no runtime error. Note: The MsgBox function is a message box function. Details of this are explained under Built-in functions. 38 A variable declared in a procedure is local to that procedure and other procedures cannot change its value. If you want other procedures only in that module to have access to that variable, declare it at the top of  the module, before any Sub statements. The variable then becomes a module level variable. If, however, a module level variable is declared using a public keyword, then the variable becomes a procedure level variable. If you declare a variable at module and procedure levels, the procedure level variable is used within its procedure and the module-level variable is used in all other procedures. Although declaration of variables is not a must it is good programming practice to use the Dim statement to declare them. If you want declaration of variables to be mandatory in macros, then on the top of  the module the following declaration can be given: Option Explicit If this declaration is provided, the procedure will not work unless and until all the requisite variables are dimensioned with the Dim statement. Constants Values that don‟t change should be set up as constants rather than variables. This prevents them being changed by accident. The line: Const pi = 3.14159 will create the constant pi that can then be used in an expression such as Rec = pi/2 (Note: VBA has several Built-in Constants such as VBOk, VBYes, etc.) Object Variables Normally, numbers and character strings are stored in variables. However, variables can also refer to objects. Unlike normal variables whose main purpose is to store data, object variables simply point to where the existing data is located. Therefore to assign an object to a variable we always need to use the SET keyword. As stated earlier, Objects could be Range, Worksheet, and Workbook etc. Example: Dim Rng as Range Set Rng = Range (“A1:D10”) All Object related properties and object related methods work for the object variable (using the Dot Operator) only after we assign the 39 respective object to the variable. If the Set keyword is not used VBA will return an “Object Required Error” at runtime. Note: The Range property of a range object indicates values within the object. Supposing we have a list of cells say B6:G1055, the list contains 1050 rows and 6 columns. Suppose we set this range as an object: Set Rng = Range(“B6:G1055”) Then this list range is now Rng. To navigate within this list we can use the Range property effectively: Rng.Range(“A1”).Select – will select the first cell in the Range Rng namely, the cell B6. Rng.Cells(1,6).Select – will select the first row and sixth column of this range Rng namely, the cell G6 will be selected. To select an entire column or row, you could use the End property. The End property of a Range object has the following arguments: XlUp XlDown XlToRight XlToLeft XlRight and XlLeft are alignment constants and should not be used here. They should be used for formatting purposes for the Horizontal Alignment. So the terminologies for the contants become XlToRight and XlToLeft respectively. In this case, to select the bottom of the cells use: Rng.End (XlDown).Select or Rng.Cells (Rng.Rows.Count,1).Select To select the top right cell use: Rng.End (XlToRight).Select or Rng.Cells (1,Rng.Columns.Count).Select To select the entire first column of the list use: Range (Rng.Range (“A1”), Rng.End (XlDown)).Select To select the entire First Column of the list use: Range (Rng.Range (“A1”), Rng.End (XlToRight)).Select To select the nearest full range rectangle (for lists) use: Selection.CurrentRegion.Select 40 The Worksheet Object: The worksheet object, like the range object has its own properties and methods such as add, delete etc. A few examples are illustrated here for understanding worksheet objects better: To add a new worksheet, use the following statements: Sheets. Add or Sheets. Add After: =ActiveSheet The above statement will work fine except in early versions of Excel. The following is the work around for adding a worksheet to the end of a workbook: Sheets. Add. Move After:=Sheets (Sheets. Count) The following line of code will add a new sheet after the sheet name "Result" and name the newly added sheet as "RB1" Worksheets. Add(After:=Worksheets("Result")).Name = "RB1" The following statement illustrates how to copy a sheet and at the same time relocate the copy to the end of the workbook. Sheets("My Sheet").Copy After :=Sheets(Sheets.Count) If you wanted to copy it after a specific sheet, then you would put that sheet's name in place of Sheets.Count. Please note that you do not have to select the sheet in order to copy i t. The following line of code copies the sheet RB1 to a new workbook and makes that workbook the active workbook: Sheets("RB1").Copy The following line of code copies the sheet RB1 to another workbook by the name B1.xls after a specified worksheet, say sheet Sh1: Sheets ("RB1").copy after:=workbooks("B1.xls").worksheets("Sh1") In case, instead of copying the sheets to another location, the move method can be used to literally move the sheets to another location. In place of wherever the copy method is used, the move method needs to be added. Visible is a property of the worksheets and can take three values viz, -1 (visible), 0 (Hidden) or 2 (Very Hidden). Setting a value to be hidden can be reset in Excel but if the value were to be set as very hidden, the value can only be reset in VBA. For example: Worksheets(“RB1”).visible = 2 - will set the sheet to very hidden Worksheets(“RB1”).visible = 0 - will set the sheet to simply hidden Worksheets(“RB1”).visible = 1 - will reset the sheet to visible 41 The WorkBook Object: Similar to the worksheet object, the workbook object too has its own properties and methods. A few simple examples for the workbook object is discussed in this section. The simple way to open a file is with a statement like the following: Workbooks. Open filename:="c:\data\myfile.xls", UpdateLinks: =False Setting the UpdateLinks value to False prevents Excel from asking if you want to update links (and links are not updated). If you want to open the workbook as read only, then add "ReadOnly:=True" to the above statement. You could also write the above statement slightly shorter, by not specifying the names of the arguments. If you do this, then the values supplied must be in the order that the method expects them. Workbooks. Open c:\data\myfile.xls", False To add a new workbook, use the following statement: Workbooks. Add You can also assign an object variable to refer to the new workbook when you create it. Dim Wb As Workbook Set Wb = Workbooks. Add MsgBox Wb.Name Wb.SaveAs Filename: ="RB.xls" To save a workbook: ActiveWorkbook.Save To save the workbook in a different name: Workbooks(“RB1.xls”). Saveas “RB2.xls” To save and close a workbook: ActiveWorkbook. Close SaveChanges: =True To close a workbook without saving: ActiveWorkbook. Close SaveChanges: =False 42 Decision Making with VBA Conditional logic is another programming step, and it's code runs only if  a certain condition is true. There's no limit to the number of ways you can use conditional logic. You could want to perform a different calculation based on the value of a cell, apply different formatting based on the number of cells, or create a different printout depending on the date. All these operations and many more are possible when using conditional logic. All conditional logic starts with a condition, a simple expression that can turn out to be true or false (programmers call this process evaluating to true or false). Your code can then make a decision to execute different logic depending on the outcome of the condition. To build a condition, you need to compare a variable or property using a l ogical operator like = (equal to), < (less than), > (greater than), and <> (not equal to). ActiveCell.Value = 10 is a condition. It can be true (if the current cell contains the number 10), or false (if the current cell contains something else). The IF Statement On its own, a condition can't do anything. However, when used in conjunction with other code, it can become tremendously powerful. Once you've created a suitable condition, you can put it inside a special structure called the If block. The “If block” evaluates a condition, and runs a section of code if the condition is True. If the condition isn't True, Excel completely ignores the code. Here's a macro that looks at the current cell value. If that value exceeds 100, then Excel changes it to 100. If the cell value is less than 100, nothing happens, and the current value remains. Sub RbsMacro () If ActiveCell.Value > 100 Then „This value is too big. Change it to the maximum of 100. ActiveCell.Value = 100 End If  End Sub Note that the If block always starts with If followed by Then in the same line and ends with End If . Everything else is conditional and runs only if the condition is true. The “IF-Block” is capable of evaluating several conditions. Here's an example that considers the current value of a cell. Depending on the cell's value, the “If ” block uses a different calculation to arrive at the sales commission, which it places in another cell. 43 Sub RbsMacro () If ActiveCell.Value > 1000 Then „Use the 5% commission rate. ActiveCell.Offset (0, 1).Value = ActiveCell.Value * 0.05 ElseIf ActiveCell.Value > 500 Then „Use the 2.5% commission rate. ActiveCell.Offset (0, 1).Value = ActiveCell.Value * 0.025 Else „Give a basic $5 commission. ActiveCell.Offset (0, 1).Value = 5 End If  End Sub Here, only one segment of code runs. Excel works its way through the If  block, testing each condition until one matches. If the cell value is greater than 1,000, it runs the first conditional block of code, and then jumps down to the closing End If statement. If the cell value is less than 1,000 but greater than 500, the first condition is false, and Excel tries the second option. This is given by the ElseIf clause. This clause statement must also end with a Then. If both the condition does not match, Excel runs the code in the final Else clause. These examples scratch only the surface of what careful conditional logic can do. You can use “And” and “Or” keywords to combine conditions, put one conditional block inside another, and much more. For Example, to check the largest of three numbers, the following code is written: Sub Largest () Dim A as Integer, B as Integer, C as Integer A = 10 B = 20 C = 30 If A>=B And A>=C Then MsgBox “The Largest is: “& A ElseIf B>=A And B>=C Then MsgBox “The Largest is: “& B Else MsgBox “The Largest is: “& C End If  End Sub The Select Case Statement Apart from the simple If-Then-Else-End If statement, a select case statement is also in use. This is often used when decision is based on a single variable that has many options. Consider this example: 44 Sub Vote () Dim I As Integer I = InputBox ("Provide your age", "Age") Select Case I Case Is < 20 MsgBox "You aren't qualified to vote" Case Is <= 60 MsgBox "You are so busy that you don't have time to vote" Case Else MsgBox "You are retired but too tired to vote" End Select End Sub In the above procedure, we introduce an Input Box to obtain the age from the user. The Input Box here has a Title – “Age” and a prompt . The value provided by the user gets stored in a variable I. Select Case I – implies that the computer selects from one of the many cases depending on the value of I. If the first two cases are not selected, the option used is Case Else. After executing code the procedure ends provided we give the End Select statement. The Case option could also use specific values Example: Case “A” More than one value can also be checked. In the example given below, a select case procedure is written based on the active cell‟s value being A, B, C, D or E: Sub RBsMacro () Select Case ActiveCell.Value Case "A", "B", "C" ActiveCell.Offset (0, 1).value = “First Three” MsgBox “Excellent” Case "D", "E" ActiveCell.Offset (0, 1).value = “Next Two” MsgBox “Good” Case Else ActiveCell.Offset (0, 1).value = “Last set” MsgBox “Can Improve” End Select End Sub If the values are always in sequence, with no gaps you can write Case "A" To "C" As can be seen, multiple statements to be executed can be placed between the Case tests. 45 Procedures, Functions and Parameters Generally one macro procedure can call another procedure either within the same module or outside of it. Calling is done by simply mentioning the macro name. Consider the Example below: Sub RBTest () Dim A as Integer, B as Integer, C as Integer A = 10 B = 20 C = 30 RBMacro End Sub Sub RBMacro () MsgBox “Good to have you study this” End Sub What happens when the first Macro RBTest is executed? It simply calls the macro RBMacro and the Message in RBMacro is displayed. A Subroutine can also take arguments. The same Macro above can be edited as shown: Sub RBTest () Dim A as Integer, B as Integer, C as Integer A = 10 B = 20 C = 30 RBMacro (A, B, C) End Sub Sub RBMacro (N1 as Integer, N2 as Integer, N3 as Integer) MsgBox “Good to have you study this” MsgBox “The sum of the values is:” is:” & N1 + N2 + N3 End Sub Note: Note: What if you have the same Macro Name in two different Modules. Then, to call a specific specifi c macro, one needs to include the Module Name as well. This is how that is done: Call Module1.Macro3 Module1.Macro3 (The call is optional but advisable) Functions In this case the sum is also al so displayed. But a subroutine merely can be used to display a Message Box. It cannot ca nnot be used to return any values. 46 Instead of a subroutine for RBMacro, it is better to have a Function. In a Function, values can be returned. In this t his example, the original Subroutine RBMacro can be given as shown: Function RBMacro (N1 as Integer, N2 as Integer, N3 as Integer) MsgBox “Good to Have you study this” MsgBox “The sum of the values is:” is:” & N1 + N2 + N3 End Function Note: Since calculation is carried from right to left, the values N1, N2 and N3 will be added first. By slightly changing the above 2 Macro procedures, we can obtain the macros as shown where-in the resulting values are returned: Sub RBTest () Dim A as Integer, B as Integer, C as Integer, RES as Integer A = 10 B = 20 C = 30 RES = RBMacro (A, B, C) MsgBox “The sum is” & RES End Sub Function RBMacro (N1 as Integer, N2 as Integer, N3 as Integer) MsgBox “Good to have you study this” RBMacro = N1 + N2 + N3 End Function In a function, the name of the function functi on itself is the variable that will return the result of the Function. In the above function the sum of the variables are returned to the variable RES. Pass by Value (ByVal) and Pass by Reference Refer ence (ByRef) The Functions RBMacro has three arguments passed into it. By default the arguments passed are by reference only. But they can also be passed by Value. In general, when we pass arguments through a function ( A, B and C were passed in the example above ), the arguments are passed by Reference only. If you see the example above, the Function RbsMacro R bsMacro takes these arguments as N1, N2 and N3 respectively. Actually these values N1, N2 and N3 refer to the same variables A, B and C. So, when we pass 47 arguments by Reference or by default, Changing the values N1, N2 or N3 changes the values A, B or C. C. But when we pass arguments by value, the variables referred to in the function will only take the values of the original variables that are passed as arguments. They will however, not refer to the original variables. So, when we Pass By Value, any changes made in these values will not affect the original variable values. values. To illustrate the statements above let us further edit our macro RBTest and this time, we shall have two different types of functions: functi ons: Sub RBTest () Dim A as Integer, B as Integer, C as Integer Dim RESByVal%, RESByRef% „Using % implies that you dimension as Integer A = 10 B = 20 C = 30 'Call by Value RESByVal = RBByVal (A, B, C) „Displaying values of A, B and C to see if they are still 10, 20 and 30. MsgBox "Result val of ” & A & "," & B & "," & C & "is" & RESByVal 'Call by Reference RESByRef = RBByRef (A, B, C) „Displaying values of A, B and C to see if they have changed. MsgBox "Result ref of" & A & "," & B & "," & C & "is" & RESByRef  End Sub Function RBByVal (ByVal n1%, ByVal n2%, ByVal n3%) n1 = n1 + 100 n2 = n2 + 100 n3 = n3 + 100 RBByVal = n1 + n2 + n3 End Function Function RBByRef (n1%, n2%, ByRef n3%) n1 = n1 + 100 n2 = n2 + 100 n3 = n3 + 100 RBByRef = n1 + n2 + n3 End Function You will find that on passing arguments by default or by reference, the values A, B and C change to t o 110, 120 and 130 respectively. respectively. So, if you 48 are specific about not altering the original variables, it is advisable to pass the variables by Value Optional Arguments Optional Arguments are arguments which need not be passed in a function. Even if you have seen Excel Functions, optional arguments are present and appear in the yellow palette in square brackets. Even in VBA when a user made function has an optional arguments, it will appear in Square brackets in the yellow palette. To use an Optional argument in function a typical example is as shown: Function RBsFunction (n as Single, n1 as String, Optional n3 as String) To identify if the optional argument has been passed or not, there is a VBA built-in function called “ IsMissing”. Let us modify our RBTest Macro one last time with the use of optional arguments: Sub RBTest () Dim A as Integer, B as Integer, C as Integer Dim RES1%, RES2% „Using % implies that you dimension as Integer A = 10 B = 20 C = 30 RES1 = RB (A, B, C) MsgBox "Total result of" & A & "," & B & "," & C & "is" & RES1 RES2 = RB ( A, B ) MsgBox "Optional result of" & A & "," & B & "," & C & "is" & RES2 End Sub Function RB (n1%, n2%, Optional n3%) n1 = n1 + 100 n2 = n2 + 100 „Checking if optional value n3 is missing If IsMissing (n3) Then n3 = 0 End If  RB = n1 + n2 + n3 End Function In the example, first RES1 is computed and this value would be 360 and A, B and C would be 110, 120 and 130 respectively. While obtaining the value of RES2, the C value is not entered. Consequently only A and B are added and the result RES2 will equal A + B or in other words 210 + 220 which means RES2 will be 430. 49 Creating Custom Functions So far, you've seen how you can use code to create powerful macros that take control of Excel. But you have another option for plugging your logic into Excel: You can create custom functions (user-defined functions). Essentially, a custom function accepts some information (through arguments), performs a calculation, and then provides a result. Once you've created your custom function, you can use it in a formula in a cell, in exactly the same way that you use Excel's built -in functions. You create custom functions in the same place that you create macros i n modules. In fact, a number of macros and functions can exist side-byside in a module. Here's an example of one of the simplest possible custom functions: Function Name () Name = ThisWorkbook.Name End Function The above function Name will be available in functions in Excel under User Defined functions after you enter the above function in a module. Note that the Function should not be declared Private. By default, it is public and so the keyword can be safely omitted. Every function needs to provide a result, which is what appears in the cell when you use the function. To set the result, you use the name of  the function, followed by an equal sign and the value, as shown here: GetName = ThisWorkbook.Name The file name of the active Workbook is allocated to the function name in the above statement. This then, is the result of the function. To use the function in your worksheet, just create a formula that uses the function. The GetName ( ) function is particularly simple because it doesn't use any arguments. But arguments can be used just like normal functions. Consider the following custom function, which takes two arguments length and width and calculates the total area by multiplying them together: Function Area (Length, Width) Area = Length * Width End Function Now, to use this function in a cell in your worksheet, you can type this formula: =Area (100, 50) 50 Consider the example below where in the Tax is calculated for different income groups. This is based on the Tax structure in India during 200708. Surcharge has not been included for those who are very specific: Public Function Tax(Amt As Double, Optional Age%, Optional Gender$) 'Custom Function - User Defined Function with if else „Checking if Age and Gender are Missing and working accordingly If IsMissing (Age) Then Age = 0 If IsMissing (Gender) Then Gender = "M" If Age >= 65 Then 'Senior Citizen Category If Amt <= 185000 Then Tax = 0 ElseIf Amt <= 250000 Then Tax = 0.2 * (Amt - 185000) Else Tax = 0.2 * (250000 - 185000) + 0.3 * (Amt - 250000) End If  ElseIf Gender = "F" Then 'Feminine Gender Category If Amt <= 145000 Then Tax = 0 ElseIf Amt <= 150000 Then Tax = 0.1 * (Amt - 145000) ElseIf Amt <= 250000 Then Tax = 500 + 0.2 * (Amt - 150000) Else Tax = 500 + 20000 + 0.3 * (Amt - 250000) End If  Else 'Normal Category - No Concessions If Amt <= 110000 Then Tax = 0 ElseIf Amt <= 150000 Then Tax = 0.1 * (Amt - 110000) ElseIf Amt <= 250000 Then Tax = 4000 + 0.2 * (Amt - 150000) Else Tax = 4000 + 20000 + 0.3 * (Amt - 250000) End If  End If  End Function The Function uses Tax slabs for Normal people, Women and seniors. 51 There are various tax slabs at 10%, 20% and 30%. For a normal citizen there is no tax for salaries up to 1.1 Lakh. Then on, salaries above 1.1 Lakh would get 10% Tax slab. Salaries above 1.5 Lakhs would incur a 20% tax slab while that part of the the salary above 2.5 Lakhs would incur a tax slab of 30%. The situations are slightly different for Women and senior citizens. You could then try the Tax function out in Excel to get the desired results. Remember that Age and Gender are just optional values VBA Tip1: When you try the above function, you will find that there is neither help nor any description available. Do you want the Function to have a description? Open the Macro Dialog Box in Excel under Tools (Use Alt + F8) . Type the name of the custom function under the Macro Name area. Select the options tab (You could also set shortcuts this way but that is for Subroutines and has no relation with Functions). In the description, type the description as to what the function will do. Close the macros dialog box. Now open the function from the menu ans see the description. VBA Tip 2: The Function you have created appears in the User Defined Section only. This is because they fall by default into the UDF (User Defined Function) category. Can this be changed? The answer is YES. Excel does not provide a direct way to assign a custom function to a category. The only way is to execute a line of VBA Code. This can be done in the immediate window or, inside a subroutine. The statement below however, needs to be executed only once. Every time the workbook is re-opened, the function will continue to appear in the specified category unless it is re-allocated otherwise. The line of code is: Application.MacroOptions Macro: =”Tax”, Category:=1 The category 1 refers to the Financial Category. The list of category values is as provided below: Number 1 2 3 4 5 6 7 8 9 Category Name Financial Date & Time Math & Trigonometry Statistical Lookup & Reference Database Text Logical Information 52 * Note: A Function need not return a value. When no return value is there, call the function without brackets. But when a return value is taken, use brackets. This applies for all Buil t-In functions like Message Box and Input Box etc. VBA Tip 3: Some Excel functions are static while others are volatile. As you may be aware the Rand (), RandBetween () and Now () functions are volatile. They keep recalculating whenever any cell is recalculated or when we press the F9 Key for recalculation. Custom Functions are however, static by nature. Whenever any value is entered in another cell, they do not recalculate. They recalculate only when total recalculation is done (Ctrl + Alt + F9). Let us write a function to calculate the random number between two values (effectively the RandBetween () function). But you can incorporate this function in Excel and compare it with RandBetween to see if it i s volatile. The function below incorporates two Excel Built-in functions Rnd () – Similar to Excels Rand () function and Int () which, is similar to Excel. Public Function Random (High As Long, Optional Low As Long) If IsMissing (Low) Then Low = 1 Random = Low + Int ((High - Low) * Rnd ()) End Function There are two differences here. 1. 2. Unlike RandBetween, you would be specifying the higher value first and then the lower value (which is 1 by feault and can be omitted). While RandBetween is Volatile, this function is not. Typing a value in any other cell or pressing F9 will show you the change between the two. To evoke a change in the Random () function you need to press “Ctrl + Alt + F9 “. For some instances, however, like time changes you may actually want to have volatility for the function. To make a function as volatile, you may need to use the following Application p roperty: Application.Volatile True A volatile Random function would then be as shown: Public Function Random (High As Long, Optional Low As Long) If IsMissing (Low) Then Low = 1 Random = Low + Int ((High - Low) * Rnd ()) Application.Volatile True End Function 53 Note: When we call a function with arguments, the arguments are normally called in the exact order separated by commas. But if we need to give it out of place, we could use the := operator. In a message box the title option comes thirs and prompt, first. We can write as: MsgBox Title:=”Heading”, Prompt:=”Hello There” Repeating Actions with a Loop Computers work particularly well when you need to automate a tedious task. While you may tire out after typing in your 100th cell value, an Excel macro has no such weakness, and can perform thousands of  operations without pausing. The loop is one of the best tools for repeating operations. A loop is another type of block, one that repeats itself over and over again. Here's an example: Do „Selects the current Active Cell ActiveCell.Select „Changes the Interior Color Selection.Interior.ColorIndex = 35 „Moves to the Cell below it ActiveCell.Offset (1, 0).Select Loop When Excel reaches the final Loop statement at the bottom of this loop, it automatically jumps back to the beginning and repeats your code. However, there's one problem. This process continues infinitely! That means if you make the mistake of running this in a macro, your worksheet is locked up indefinitely (until you press the emergency-stop key combination, Ctrl + Break). To avoid this situation, you should build all loops with an exit condition. This condition signals when the loop should end. For a typical Do Loop there are two possible Exit conditions. They are: 1. Do While (Condition is True) …… Loop 2. Do Until (Condition becomes True) ……Loop While the first Loop runs as long as the Condition is True, the second Loop runs as long as the Condition is False. In these two cases the condition is checked in the beginning itself. But in the third and fourth Exit Conditions below, the condition is checked at the end of the Loop: 54 3. 4. Do ……… Loop While (Condition is True) Do ……… Loop Until (Condition becomes True) What this implies is that even if the Looping Condition is such that entry into the Loop is not allowed, the statements in the Loop in the Third and Fourth cases would be executed at least once. Here is a loop example that stops as soon as the active cell is empty: „Execute the loop if the active cell is not empty Do Until ActiveCell.Value = "" ActiveCell.Select „Changes the Interior Color Selection.Interior.ColorIndex = 35 „Moves to the Cell below it ActiveCell.Offset (1, 0).Select Loop This technique is quite useful. Suppose you would want to add the value of all cells in a column that is bold then a similar loop can be executed. This time we shall use a While loop and we shall put the while at the end as in Do ……… Loop While Sub AddBoldValue () Dim Sum as Double Dim Str as String Dim Rng as Range „Initializing – obtaining values Sum = 0 Str = InputBox (“Provide starting cell address, example: E5”) Set Rng = Range (Str) „Loop Starts Do „Checking for the Font If ActiveCell.Font.Bold = True Then „Sum keeps adding the cell value to itself  Sum = Sum + ActiveCell.Value End If  „Moves to the Next Cell Activecell.Offset (1, 0).Select „Loops Exit Condition Loop While ActiveCell.Value <> “” „The value of sum is put in the cell 2 cells below the last cell ActiveCell.Offset (1, 0).Value = Sum End Sub 55 FOR – NEXT LOOP Apart from the Do Loops, Excel‟s VBA has a powerful For – Next Loop. This loop is used when looping has to be done a specific number of times or until a value would increment itself up to a specific limit. A Typical Syntax for a For…Next Loop is as shown: For Counter = Start Value To End Value [Step Increment] Statements inside the Loop Next [Counter] There may be several worksheets in a workbook. Let us run a procedure that would tell the names of all the sheets using a loop. Let the variable “No” represent the Number of worksheets. Let the variable Counter be the incrementing variable for the Loop. The procedure is as below: Sub RBSheetCount () Dim No as Integer, Counter as Integer „Allocate Number of Worksheets in the Active Workbook to No No = Worksheets.Count For Counter = 1 To No MsgBox WorkSheets (Counter).Name Next Counter End Sub In the above program, the variable Counter first takes the value of 1. The Message Box displays the name of the first Worksheet. Then Counter becomes 2 and so on until the Counter finally reaches the value “No” which, is the number of worksheets. This will be that last time the Loop runs. After this the Loop exits and the statement(s) after the Loop is executed. One Loop can be nested inside another. Consider the following Loop Example: Sub RBLoop () Dim i As Integer, j As Integer, ctr As Integer 'Work on a new sheet i=0 ctr = 0 Do While i < 10 i=i+1 'i is the row and keeps increasing For j = 1 To i Step 1 'For each value of i, j is the column and j keeps increasing 'The value of i is printed in all the columns using cells (row, column) object Cells (i, j).Value = i 56 'ctr counts the number of times the inner loop is carried out ctr = ctr + 1 Next j Loop MsgBox "loops” & ctr & “times." End Sub A FOR-NEXT Loop is nested inside a DO WHILE – LOOP. Look at the Step 1 in the program. It is not necessary but if you want the value of j to increase from 1 to 3 and then to 5 you need to give step 2. But if the step size is 1, then it can be omitted as the Loop keeps the step size 1 as default. Can you explain the above procedure? Let us work one more example on a nested loop. This time we will nest one for loop inside another for loop. Remember we carried out the “Add bold values” to a single column of cells. This time we will carry out a similar functionality but this time, it is not going to be just one column. We will request the user to give the range address and using this address we will carry out the same procedure. We will have t wo variables Row and Col which represent the number of rows and column s for the range. The variables RR and CC will be the row and column counters respectively. Now, on to the procedure: Sub RBSumBold () Dim Sum as Double Dim STR as String Dim Rng as Range Dim Row%, Col%, RR%, CC% Sum = 0 STR = InputBox (“Provide Range Address. Example: „A6:G50‟”) Set Rng = Range (STR) „Obtain the Number of Rows and Columns in the Range Rng Row = Rng.Rows.Count Col = Rng.Columns.Count For RR = 1 To Row For CC = 1 To Col If Rng.Cells (RR, CC).Font.Bold = True Then Sum = Sum + Rng.Cells (RR, CC).Value End If  Next CC Next RR  MsgBox “The Sum of Bold Values is:” & Sum End Sub 57 FOR EACH – NEXT LOOP While the conventional For Next Loop is used for Data type Variables such as Integers, Double, String or Variant, the For Each Next loop is exclusively used for objects in a collection. In a work book, “WorkSheets” is a collection which has several “WorkSheet” objects. A Range contains a “Cells” collection which is a collection of “Cell” objects. This Loop is a very useful Loop to traverse through each and every object in any given collection. Visual Basic itself figures out the number of times the loop should execute. The syntax is as shown: For Each Object (Variable) in Collection Statements inside the Loop Next Object (Variable) Let us now try out an example to do the same thing as last time, i.e. to sum the Bold values. The reason we are first trying out the same example is to illustrate the ease with which a For Each– Next Loop can be done Example - 1. Sub RBSumBold () Dim Sum as Double Dim STR as String Dim Rng as Range, Cl as Range Sum = 0 STR = InputBox (“Provide Range Address. Example: „A6:G50‟ ”) Set Rng = Range (STR) „Loops through each cell in the range of cells For Each Cl in Rng.Cells If Cl.Font.Bold = True Then Sum = Sum + Cl.Value End If  Next Cl MsgBox “The Sum of Bold Values is:” & Sum End Sub Here is another example that checks if a sheet by a given name exists in a workbook. Example – 2. Sub RbisSheet () Dim ws as worksheet Dim Flag as Integer Dim Na as String „We now initialize the values. Flag will be 1 if name is found 58 Flag = 0 Na = InputBox (“Provide the Sheet Name”,” Sheet”) For Each ws in Worksheets If ws.Name =Na Then Flag = 1 End If  Next ws If Flag = 1 Then MsgBox “Sheet Exists” Else MsgBox “Sheet Missing” End If  End Sub Flag values are often set in good programs as Indicators. Tip: In the last example which was done, there may have been 10 worksheets. The name might have matched in the first instance of the loop itself and the flag would have been set to 1. But still, until all the sheets are checked the loop progresses. This is a waste of time and memory for the computer. There may also be situations when we need to exit from a loop in a program under special circumstances. For this, Visual Basic has two Exit statements. These are: 1. The “Exit For” Statement – Used for all FOR Loops (For Next and For Each Next) 2. The “Exit Do” statement – Used for all DO Loops In th above Example, after the Flag =1 statement is set and be fore the End If Statement we need to introduce a line as shown: Exit For This will stop the loop and VBA will proceed to the line of code after the Loop. Sometimes for a condition we may need to exit from the macro itself. We the need to use: Exit Sub To Exit from a Function instead of a Subroutine, we need to use: Exit Function 59 Tip: In the example before, we were trying to see if a sheet exists. Suppose we wanted to delete the sheet, we would have written: Ws.Delete where Ws is the Worksheets object that points to the worksheet. But whenever a sheet is deleted the system will generate a prompt. It is sometimes undesirable when we need to automate the process. We can switch off and on alerts before and after the delete statement as shown: Application.DisplayAlerts = False Ws.Delete Application.DisplayAlerts = True This will ensure that prompts and alerts do not occur during deletion. Note: A small reminder on arguments in Functions We find that a function can take different types of arguments. It can also take an array argument provided it is of a variant type. This part of  it can be discussed in Arrays. A function can also take a range of cells as an argument. Let us assume we have a pre-selected range of cells. For all values over 100 we need to multiply the value by 2 and add 150. Other values may be left as it is. We will pass this entire pre-selected Range variable as an argument in the function. Sub rangers () Dim Rng1 As Range Set Rng1 = Application. Selection „Calling the Function climbs Rng1 End Sub Function climbs (Nm As Range) Dim c As Range Dim Bhaarath As Double, Infotech As Double 'Bhaarath is the product factor & Infotech is the sum factor Bhaarath = 2: Infotech = 150 For Each c In Nm.Cells If c.Value > 1000 Then c.Value = c.Value * Bhaarath + Infotech End If  Next c End Function This is equivalent to paste special with operations such as multiply and add but with specialized conditions in it. 60 IN-BUILT Functions in VBA Just like Excel VBA has got several Built-In Functions in it. For example, the built-in function “UCase” is similar to Excels “Upper” Function in converting the string to Upper case. Some of the ones u sed by you till now are MsgBox, InputBox, and IsMissing etc. Some of the most common functions will be discussed here starting from the Message Box Function. The Message Box Function This function is one of the most widely used functions in VBA. This is also an excellent debugging tool because one can insert it at any point in the code to halt your code and display a required variable value. Like every function this function also returns a value . The value returned by a Message Box function depends on the users response to the Dialog Box that is displayed. Let us start by discussing the syntax of this function: MsgBox (Prompt, [buttons], [title], [help file], [context]) All values in square brackets are optional. Prompt is the body of the message box and is mandatory. “Buttons” requests for an option which when entered, indicates the type of buttons to appear in the Message Box dialog box. Title (Optional) is the text on the Message Box title bar. The other two are help provision related and deals with providing help. There are several options for Buttons. Most of them are listed below: Button Option Value What the Button Displays vbOKOnly 0 Display OK button only. vbOKCancel 1 Display OK and Cancel buttons. vbAbortRetryIgnore 2 Display Abort, Retry, and Ignore buttons. vbYesNoCancel 3 Display Yes, No, and Cancel buttons. vbYesNo 4 Display Yes and No buttons. vbRetryCancel 5 Display Retry and Cancel buttons. The Type option is used for the Type of Message Box: 61 Type Option Value Description vbCritical 16 Display Critical Message icons. vbQuestion 32 Display Warning Query icon. vbExclamation 48 Display Warning Message icon. vbInformation 64 Display Information Message icons. Either the option or the value can be typed. For Example: MsgBox “RB‟s Macro session is boring”, 3,”Choice” OR MsgBox “RB‟s Macro session is boring”, vbYesNoCancel,” Choice” If you combine this with the type value option, say vbQuestion then the combined value is 32 + 3 = 35. We then write: MsgBox “RB‟s Macro session is boring”, 35,”Choice” The result will look as shown: The dafault selection will usually always be the fi rst button. Here are the values for the other defaults. These values must be added to get the right touch: Default Value Description vbDefaultButton1 0 Default. vbDefaultButton2 256 Second button vbDefaultButton3 512 Third button vbDefaultButton4 768 Fourth button Let us now try and add these values to choose. Let us say we want to give Information. So our Type Option is Information whose value is 64 62 We want vbYesNoCancel whose value is 3 We want No to be our default. This is the second button. For this the value for DefaultButton2 is 256 Our Total Value then is 256 + 64 + 3 = 323 MsgBox “RB‟s Macro session is boring”, 323,”Choice” The second button is default in this Information type Message. This way, my session will not be boring by default ! Also, in the above case the message box actually gives us an option to say yes, no or cancel. To exercise this option we must have the message box return a value. Whenever the function returns a value, brackets must be used. The values returned are based on which button we click. Consider this statement: K = MsgBox (“RB‟s Macro session is boring”, 3,”Choice”) If K is 6 then you must have clicked yes. Here is an index of what values will be returned upon clicking the desired button option. Constant Value vbOK 1 vbCancel 2 vbAbort 3 vbRetry 4 vbIgnore 5 vbYes 6 vbNo 7 Example: Sub Choose () K = MsgBox (“RB‟s Macro session is boring”, 3,”Choice”) If K = 6 Then MsgBox “You clicked yes” Elseif K = 7 Then 63 MsgBox “You Clicked No” Else MsgBox “You clicked cancel” End If  End Sub Note: How do we come to the next line while displaying a message box? There are several character constants to help us achieve this. VbCr – Carriage Return i.e Chr(13) VbLf  – Line Feed i.e. Chr(10) VbCrLf  – Carriage Return and Line feed i.e. Chr(13) & Chr(10) VbNewLine While all of them work in a Message Box, a userform entry works best with both Carriage Return and Line Feed ( i.e. vbCrLf only). It would be advisable to use vbCrLf for all cases. MsgBox “Hello” & vbCrLf & “Bhaarath” & vbNewLine &  _  “How are you?”, 512 + 48 + 2,”Hi” The above Message Box will appear as shown. Notice the exclamatory Message Box with the default button being the third button: Note: If you wish to wish to use double quotes to highli ght text in a message box, then use the following approach: MsgBox “Your Trainer is” & Chr (34) & " Saralaa Ramji" & Chr (34) Saralaa Ramji will now appear in quotes in the Message Box. 64 The Input Box Function This function is almost as widely used in VBA as a Message Box. The function InputBox functions similarly to MsgBox, but it enables the input of a character string. The dialog box is equipped with the buttons OK and CANCEL. If the user terminates the dialog with CANCEL, then the function returns an empty string. Result = InputBox ("Please input a number") The syntax of this function: InputBox (Prompt, [Title], [Default], [Xpos], [Ypos], [Help file], [Context]) Prompt – This is what you tell the user. It is required. Title – Title of the Input Box (is Optional). Default – This is the default value that appears where the user has to input data. If omitted the user input area is blank. Xpos & Ypos – The position of the Input Box window relative to the top left hand corner of the application in units called as Twips. If omitted, it will be horizontally centered and vertically placed at one -third the distance from the top. Generally this position is widely accepted and providing values are omitted. Help File and Context Files are also optional and are similar to those in Message Box statements. Example: Age = InputBox (“How old are you?”, “Age”, 20) 65 If the value 20 (the default value) is omitted, the user input area will be blank. If the Tile “Age” is omitted the default title will be “Microsoft Excel”. The value obtained is usually a string but numbers are automatically returned to number variables (Conversion is not required). VBA TIP: The Input Box method The normal Input Box function can only return variables. How about returning a Range of cells? Yes. This is done using the Input Box method. This is a special method invoked from the application object. (The only alternative to accepting cell ranges is a user form). How does it work? The syntax is shown below: Application.InputBox Prompt, - Same as the Input Box Function. [Title], - Same as the Input Box Function. [Default], - Same as the Input Box Function. [Left], [Top], - Same as the Input Box Function [HelpFile], - Same as the Input Box Function [HelpContextId], - ID Number of the Help Context. Same as the Input Box Function [Type] Implies the return data-type. Can be any one or a combination of data-types. - The values for the various data-types are as shown in the table below: Value Meaning 0 A formula 1 A number 2 Text (a string) 4 A logical value (True or False) 8 A cell reference, as a Range object 16 An error value, such as #N/A 64 An array of values If Type = 1 + 2 we can accept both a number and a text. But only the type given is specifically accepted. Type-8 accepts Ranges and Type-16 accepts arrays. 66 VBA Tip 2: Input Box Method Types 8 and 64 for Range / Arrays Consider the Input Box method written below: Set Rng = Application.InputBox (“Select Range”,”Cells” , Type:=8) The resulting dialog box will be: Although no scroll feature is attached, a range of cells can be selected. The range variable Rng would be then pointing to this range. One can also use this feature to enable the user to select a single cell. This is a powerful tool to enable the user to di rectly give the working range. In the case of Type 64, a variant becomes an array variable. Array variables are discussed in the next section. The example will be: AR = Application.InputBox (“Give values”, ”Array”, Type:=64) The resulting dialog Box needs to be filled as shown: There are four array elements the user has entered in this case. 67 Text Related VBA Functions Visual Basic has several Text related functions which will be discussed: 1. UCase and LCase: Converts Text to upper cae and lower case respectively. For example, st = UCase(“rb Macro Session”) rt = LCase (st) 2. => st = “RB MACRO SESSION”  => rt = “rb macro session”  InStr & InStrRev: Searching for a string inside another string. While InStr function tells the position of the first available string, InStrRev tells the position from the last available string. The syntax for InStr is as shown: InStr( [Start], String to be searched, string sought, [compare] ) Start is optional and by default searches from the beginning. Compare by default differentiates between upper & lower cases and the default value is -1. But if we provide 1, the comparison will be text comparison. In this case upper and lower cases are assumed as the same. An example: I1 = InStr (1,”Serendipity”,”e”) => I2 = InStr (,”Serendipity”,”E”) => I3 = InStr (,”Serendipity”,”E”, 1) => I1 will be 2. I2 will be NULL I3 will be 2 The InStrRev function has a slightly different syntax and is: InStrRev (String Searched, String Sought,[Start],[Compare]) Example: I4 = InStrRev (“Serendipity”, “e”) I5 = InStrRev (“Serendipity”, “I”, 1) => I4 will be 4 I5 = 9 As you can see, InStr locates the first available string from the beginning while InStrRev locates the first available string from the reverse direction. 3. Left, Right and Mid: These are similar to Excels functions but the number of characters is not optional in this case. While the function Left (“Who”) is “W” in Excel, it will generate an error in VBA. It should be Left (“Who”, 1). Some Examples: Str1 = Lef t (“R.Bhaarath”, 3) => Str1 = “R.B” Str2 = Right (Str1, 1) => Str2 = “B” => Str3 = Mid (“R.Bhaarath”, 3, 5) Str3 = “Bhaar” 68 In the last example, we find that we can obtain values from a starting character (3) using Number of characters (5). 4. Replace: This is similar to Excels Substitute Function. We can replace one value for another within a string. The syntax is: Replace (Main String, String to be replaced, New String) In the previous example string Str1 is “R.B”. If we do not want the dot, we can replace it with nothing. => Str4 = Replace (Str1, “.”, “”) 5. Str4 = “RB” Trim, RTrim & LTrim: This is similar to the functions in Excel. While RTrim and LTrim trims the right end and left end of the string respectively, Trim removes spaces on both the ends. Unlike Excels Trim however, this function does not ensure that groups of  only one space is provided. The function leaves any spaces as it is. Example: Str5 = Trim (“ R.B Class “) => Str5 is “R.B Class” 6. Len (String) determines the string length just as in Excel. 7. StrComp: This is the String compare function which compares between 2 strings string1 and string2. Its syntax is: Int1 = StrComp (String1, String2, [Compare]) Compare is an optional parameter and is very similar to that in the InStr Function. The StrComp function returns a number depending upon the two strings. The return value of the function is shown: If Example 8 StrComp returns string1 is less than string2 -1 string1 is equal to string2 0 string1 is greater than string2 1 K = StrComp (“rb”,”RB”) K = StrComp (“rb”,”rb”) K = StrComp (“a”,”b”) => => => K=1 K=0 K = -1 StrReverse: This function reverses a given string and returns it. For Example: 69 Str6 = StrReverse (“R.Bhaarath”) => Str6 = “htaraahB.R” Date and Time Related VBA Functions 1 Now: This function is similar to excel and no arguments are required between brackets. A function generated from it is not volatile. The function returns th current date and time. Example: Now () 2 Day: This function gives the day number for a given date. For example: Day (#5/5/2007#) will return the day number i.e. 5. 3 Month: This function gives the month number for a given date. For example: Month (#5/5/2007#) will return the month number i.e. 5. 4 MonthName: This function gives the name of the month for a given month number. For example: Monthname (Month (Now ())) will return the name of the month when you try this out. 5 WeekDay: This function gives the weekday number for a given date. This assumes Sunday is the first day of the week by default. For example: Weekday (#5/5/2007#) will return the week day number 7. The date falls on a Saturday. If however, our counting starts from a Friday then you need to give: Weekday (#5/5/2007#, vbFriday) .The result will be 2. 6 Weekdayname: This function gives the week day name for a given week day number based on the weeks starting day for you. The default is a Sunday which is normally used. For example: Weekdayname (WeekDay (Now ()) will return the name of the weekday of the day you try this out. 7 Year: This function gives the Year for a given date. For example: Year (#5/5/2007#) will return the year 2007. Note that VBA‟s calendar is only from 1/1/1900 and dates prior to that will only yield an error. 8 DateValue: To convert a date in string format into a date value, this function is used. We can remember that date is always stored as a number in Excel. Here, DateValue (“5-5-7”) will return the date as a date value. This is storable in a date variable. 70 9 DateSerial: DateSerial: If we have the year value, month value and day values separately, how do we form a date in VBA? The answer is the DateSerial. This function is simple and the syntax is as shown: DateSerial (Year, Month, Day). This will return the date. 10 Timevalue: Timevalue: This function converts the time stored as a string st ring to a time value. For example: Timevalue ("15:35") yields the result 3:35:00 PM 11 Hour: Hour: This function gives the Hour based on the time. For Example: Hour (Now ()) provides the current hour. 12 Minute: Minute: This function gives the Minutes based on the time. For Example: Minute (Now ()) provides the current minutes. 13 Second: Second: This function gives the Seconds based on the time. For Example: Second (Now ()) provides the current seconds. 14 TimeSerial: TimeSerial: Knowing the hour, minute and seconds in VBA, the time value can be obtained using this function. This function is as shown: TimeSerial (Hour, Minute, Second) yields the Time value. 15 Wait: Wait: This is not n ot a function but a method of the application object. The statement if given, pauses the VBA program up to a stipulated time. This could be for answering a quiz etc. The syntax is Application. Wait (Time). Until that time value occurs the program will have to wait. For example, the application applicati on waits for 5 seconds in the procedure shown below: Sub Waiter () Dim HR%, MN%, SC% HR = Hour (Now ()) MN = Minute (Now ()) SC = Second (Now ()) Application. Wait (TimeSerial (TimeSerial (HR, MN, SC +5) „Application waits for 5 seconds from now. MsgBox “Waited 5 Seconds” End sub Range Related VBA Functions 1 Union: Union: This function returns the Union of two ranges. This is quite useful in many situations. Assume there is an existing range variable RBRange which refers to a range of cells. We need to find if the selected cells are within this range or not. Consider the 71 example given below. This is not a full procedure. If it is not a full procedure from sub to end sub, it is usually u sually called as a code snippet: 'Set a range variable equal to the selected selected range Set Test = Application. Selection Selection 'See if the selected range is in the named range If Union (RBRange, Test).Address = RBRange.Address Then MsgBox "The selection is within the range" Else MsgBox “The Selection is not fully within the range” End If  2 Intersect: Intersect: While one range may not be truly inside another they may or may not intersect. This function can check that. The result of an intersection is the common range of the two ranges. If  the two ranges do not intersect, the result is null (or nothing). This is demonstrated in the code snippet shown below: 'Set a range variable equal to the selected selected range Set Test = Application. Selection Selection 'See if the selected range is in the named range If Intersect (RBRange, Test).Address is Nothing Then MsgBox "The two ranges have nothing in common" Else partly within the range” MsgBox “The “The Selection is partly End If  Note that the union example assumes that the selection is smaller than the given range. It does not go to check if the given range is within the selection. If we logically l ogically combine both of these functions, it is easy to establish if any relationship between two ranges exists or not. Another important aspect to note is that both the Union Uni on and Intersect functions are under the application object and both of  them can have more than just two range arguments. 3 Using the ISEMPTY function This is a simple function that checks if a single cell is empty or not. The syntax is ISEMPTY (cell) and will return a TRUE if the cell is empty and FALSE if the cell has any value. 72 4 Using the DIR function This function gives the name of the workbook for the full path. For example if there is a file “d: \ example.xls”, example.xls”, then DIR (“D:\example.xls”) will provide the result as “example.xls”  But the best use of this function f unction is in determining if the file or workbook really exists. If there is no file by the name example.xls then the above function will return blank. To check if a file is indeed present in the particular path, this function is best suited. Using Worksheet Functions in VBA It is possible to use many of Excel‟s Worksheet Functions in VBA. Some functions are however not available. The application object has a worksheet function sub -object that contains these methods. For Example, if we need to find the third largest number in the range A1:B10, this can be accomplished using the large function in Excel. Excel . This given as: = LARGE (A1:B10,3) To use this functionality in VBA, we give as: K= Application.WorksheetFunction.Large (Range (“A1:B10”), 3) The variable K will then contain the third largest value in the Range A1:B10. Similar to large, many other functions such as Max, Min, VLookup, Index, Match, SumIf, Countif etc. can be used through the application object. Whenever there is an inbuilt VBA Function, use it first. fi rst. Should that not be available, then use Excels worksheet functions through VBA. Even if that is not possible, you need to go for custom functions. The disadvantage of using this worksheet functions is that the result appear as values. Should you specifically want formulae to appear on the cells so that any change in the precedent cells might occur, then the only other option is to use the formula property for the range. 73 A Simple Case Study Security codes have been provided for access to different city branches. The security wants to find the year when access was provided to verify the code. This is a typical Reverse V-Lookup problem. The Table is clearly shown below: Year Chennai Bangalore Hyderabad Mysore Bombay Goa Poona Delhi Calcutta 1991 R8506 R2465 R1808 R2508 R4664 R3106 R3215 R8218 R7201 1992 R8113 R6472 R3182 R7162 R7508 R1601 R9573 R9626 R7699 1993 R8861 R2203 R6133 R2251 R8416 R4784 R8956 R5299 R1682 1994 R4742 R3785 R5924 R4848 R8504 R7789 R8883 R8830 R1944 1995 R2466 R8796 R6057 R5036 R8759 R5173 R4530 R6949 R2763 1996 R4914 R8303 R7567 R5780 R1955 R6782 R1822 R4566 R1235 1997 R9511 R8559 R6068 R5236 R1721 R9370 R6392 R2828 R5281 1998 R6450 R5890 R1173 R9145 R2706 R7441 R3801 R5256 R5970 1999 R8790 R7838 R2375 R6831 R9748 R3743 R1278 R7737 R9727 2000 R5017 R6843 R4967 R2563 R3675 R5591 R8447 R5708 R8230 2001 R8300 R4017 R9634 R6841 R3179 R4382 R5674 R5488 R9771 2002 R8983 R5210 R2118 R6529 R9627 R3402 R4372 R1074 R5048 2003 R9782 R5039 R7198 R8763 R7947 R6383 R3895 R6338 R4631 2004 R4717 R6689 R1092 R1444 R3332 R1913 R6364 R7087 R6412 2005 R1138 R1205 R2052 R6349 R8167 R7080 R5530 R2249 R4891 2006 R2430 R2289 R8617 R7155 R3172 R2301 R4012 R4727 R4204 2007 R3342 R4284 R1635 R3270 R7620 R3101 R2834 R9931 R9181 2008 R9259 R3744 R3917 R9914 R2649 R9245 R4129 R5935 R4249 2009 R2617 R1893 R9988 R7490 R9833 R3816 R4477 R2949 R2241 The security obtain the code and issuing city from the person. They want to verify the date. The excel values when entered are as shown: Security Values Code City R9573 Poona R1444 Mysore I know that excel itself can be used to R5210 generate the solution using the offset R8416 function. However, in this case, we want R1601 to write a user defined function using excels worksheet functions to obtain the value. Access Yr Bangalore Bombay Goa The steps involved here are: 1. From the city, we can get the column number 2. Using this we know the column where the code must exist. 3. Using the code, we find the row number. 74 4. Using that we find the year. 5. If this leads to an error, we know the code does not exists and we make the result as “FALSE”. The worksheet function we use is match as it gives the correct occurrence position. The function would go like this: Public Function reverse (code As Variant, city$, table As Range) Dim head, rqcol As Range Dim ro%, co%, rnum%, cnum%, test% ro = table.Rows.Count co = table.Columns.Count test = 0 Set head = Range (table.Cells (1, 1), table.Cells (1, co)) On Error GoTo LB1 'Finding the column number cnum = Application.WorksheetFunction.Match (city, head, 0) 'Creating a one dimensional column range reference object Set rqcol = Range (table.Cells (1, cnum), table.Cells (ro, cnum)) 'Finding the row number in this range rnum = Application.WorksheetFunction.Match (code, rqcol, 0) 'Obtaining the Year reverse = table.Cells (rnum, 1).Value test = 1 LB1: 'If there is an error If test = 0 Then reverse = "FALSE" End Function When in excel, we call the function from the list of UDF‟s (U ser Defined Functions), the following screen appears: 75 The rsulting data on usage of the UDF is as shown: Security Values Access Yr Code City R9573 Poona 1992 R1444 Mysore 2004 R5210 Bangalore 2002 R8416 Bombay 1993 R1601 Goa 1992 R3331 Bombay R3342 Chennai 2007 R3917 Hyderabad 2008 FALSE The idea of the case study is to illustrate the use of worksheet functions that can be called into VBA‟s service. Using the FORMULA Property for the Range The syntax is very simple. To find the sum for example, you use Range(“C4”).Formula = “=sum(A1:C3)”  In this case, in cell C4, the formula will be writtern. This can be used for all cases. Consider a vlookup example given below: Sub lokup() Dim tb As Range, tg As Range 'Setting up the values Set tb = Range("B1:D13") Set tg = Range("G4") 'Using the Formula Property tg.Formula = "=vlookup(G2," & _  tb.Address & ",$G$3,0)" End Sub In the above example, the cell G4 will have the necessary lookup formula. Notice the lookup table range tb cannot be given as it is in the formula property. Everything in the formula property must 76 be a text. As such, tb.address is provided and the texts are concatenated to get the desired result. Yet another property is FORMULAR1C1. Using this property, relative referencing can be obtainable. For Example: Range(“D4”).FormulaR1C1=”=R[0]C[ -2] + R[0]C[-1]”  Here the formula in Cell D4 will be equal to the sum of the values in the cells to the left of D4. When executed, the formula that will be writtern in the cell D4 is “=B4 + C4”  Arrays in VBA Arrays contain a sequence of variables. Each one is called an element of  the array and is identified by an index number. Dim can be used to declare an array without giving it any values. Example 1 Dim cities (1 to 50) As String This creates a one-dimensional array of variables that can contain 50 strings. The variables are cities (1), cities (2), cities (3),..... cities (50). A typical statement in a procedure might then be Choice = cities (1) Example 2 Dim htge (1 to 80, 1 to 80) As Single This creates a two-dimensional array of Single data type. Example 3 Also, the function called Array can create an array from a list of  variables as in Children = Array ("Raju", "Leela", "Kripa") When the Array function is used, the variables are of type Variant. This in effect means that the variable Children should be dimensioned as a variant. 77 Typically the first variable of a dimensioned array is array(0). This is by default in cases where we dimension arrays. But this is not always the case. When you use functions to generate arrays such as the Array function above, then the first array would in most likelihood be Array(1). In order to avoid such confusions, it is recommended to have all arrays to typically start with array(1). To ensure this is done, we need to declare the following statement on the top of the module: Option Base 1 This statement ensures that all arrays by default will start from array (1). Using LBound and UBound LBound and UBound are functions of VBA that are used to determine the boundaries of the array. While LBound shows the Lower Boundary, UBound shows the upper limit of the array. Consider an array: City (3 to 9) UBound (City) is 9 LBound (City) is 3 Using ReDim and ReDim Preserve for Dynamic Arrays The arrays discussed earlier were static i.e. the size of the array was fixed while dimensioning it. The size is not determined when the program is running or at runtime. If that is the case then , the array is considered to be dynamic. In a dynamic array the array variable is first initialized without the numbers but with the brackets. During runtime, the ReDim keyword is used to fix the size of the array. If the size needs to be increased further, using ReDim will yield the desired result but only at the cost of the array losing all its existing data. For preserving the data one needs to use the ReDim Preserve keyword to accomplish this task. Consider the example below: Sub RBarr1 ( ) Dim i%, j%, k% „The Original dimensioning has no limits in a dynamic array as below Dim arr ( ) As Integer j = InputBox ("How many values?", "No. of") 78 „The array is re-dimensioned at run time i.e. when the macro runs. ReDim arr(1 To j) „Obtaining Values for the array For i = 1 To j : arr(i) = InputBox("Value", "Val") : Next i k = InputBox ("How many more values:", "Values") „The array is re-dimensioned by preserving the existing values „If Preserve is not used the original values get deleted. ReDim Preserve arr (1 To j + k) For i = 1 To l: arr (j + i) = InputBox ("Value") : Next i „Lbound – Lower boundary of the array – In this case 1 „Ubound – Upper Boundary – In This case j+1 MsgBox arr (LBound (arr)): MsgBox arr (UBound (arr)) End Sub While working with a range of cell values may be difficult, processing arrays becomes quite easier. But then how do we convert range values as arrays? Assigning Range Values Directly To an Array and Vice Versa In Visual Basic, it is possible to assign the values in a range directly to an array variable. The trick is that variable must be a Variant variable. The following illustrates how to do this: Sub WriteToAnArray () Dim myArray As Variant, n As Integer, m As Integer 'Assign range values to the variant variable MyArray = WorkSheets (1).Range ("a1:c5") 'Determine the array size display in a message „Since the range is 2-dimensional, the array becomes 2-dimensional „Array size for a 2-dimensional array has to be given as below n = UBound (MyArray, 1) m = UBound (MyArray, 2) MsgBox n & " " & m 'Display some values in the range MsgBox MyArray (1, 1) MsgBox MyArray (5, 3) End Sub In reverse process, it is also possible to directly assign an array to a range. But remember that in both ways, the array in question must be a 2-dimensional array. This is because excel always assumes that a range is equivalent to a 2-Dimensional array. Consider the example below: Option Base 1 Sub arraytorange () Dim arr (5, 5) As Integer 79 Dim i%, j%, k% Dim rg As Range k=0 For i = 1 To 5 For j = 1 To 5 k=k+1 arr (i, j) = k „The array arr gets values from 1 to 25 Next j Next i Set rg = Range ("D1:H5") „rg has the same dimension 5 by 5 rg = arr „We are assigning an array to a range directly End Sub If the above example is run, the range D1:H5 will get values of the array arr viz. values 1 to 25. Dynamic Arrays using Variants Any variable that has been initialized as a varian t can be converted into an array while the macro runs. However, the initialization must be in th e form of a parametric array. This can be explained by the use of an example. Suppose N is a variant variable. I then provide a statement as shown: N = Array (“Hello”,”How”,”Are”,”You”) N now becomes an array holding 4 variants. The type of array initialization is termed as a parametric initialization. Consider the example below: Option Base 1 Sub vart () Dim Ready As Variant Dim i% Ready = Array (4, 5, 6, 7) For i = 1 To UBound (Ready) ActiveCell.Offset (i - 1, 0).Value = Ready (i) Next i End Sub Since the option Base is 1, the UBound of thi s parametrically generated array Ready is 4. These will be pasted on t he excel sheet starting from the active cell. 80 Using the Split Function to convert a string to arrays The split function splits a string into several pieces based on a given separator/delimiter. The split string is fed back as a parameter array which can be caught by a variant. The syntax is: = split (“Text”,”Delimiter”, Type -Optional) It is easier to explain using an example. Consider a macro that inputs a text variable, splits it into an array by way of a delimiter(say comma(,) in this case. The macro is as writtern below: Sub splitter () Dim txt$, dl$ Dim vr As Variant Dim i%, lb%, ub% 'Obtaining text and delimiter txt = InputBox("Give string to be split", "Input String") dl = InputBox("Provide the delimiter", "Delimiter") 'Converting variant into an array vr = Split (txt, dl) 'split results in a parameter array lb = LBound (vr): ub = UBound (vr) For i = lb To ub ActiveCell.Offset (i, 0).Value = vr(i) Next i End Sub Suppose the text given in the input box is as shown: When the delimiter is asked, we type “,”. The text is then split and written in the active sheet starting from the active cell. The variant variable vr becomes an array that contains the split values of the text. 81 Using the Join Function to convert a 1 dimensional array to a string A one dimensional array is grouped with any specified delimiter using the join function. This the opposite of split function. Consider a simple example below: Sub joiner () Dim ABC$ Dim var1 As Variant var1 = Array ("Ram", "Ravi", "Bhaarath", "Gopal", "Sundar", "Veera") ABC = Join (var1, ",") MsgBox ABC End Sub All the members of the array var1 are joined by the comma(,) delimiter as specified in the join function used in the example above. The result would be a message box as shown: Using Excels Features through VBA Methods of the Range Object  The Sort Method The sort method can be invoked from the range object using the Range.sort option. The parameters for invoking the method are as described below: Range Variable.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3) 82 Parameters Name Requirement Data Type Description Key1 Optional Variant Specifies the first sorting field, preferable to give as range name Order1 Optional XlSortOrder Determines the sort order for the field specified as Key1. This can be xlAscending (or 1) and xlDescending (or 2) Key2 Optional Variant Second sort field Type Optional Variant Specified which elements are to be sorted. Order2 Optional XlSortOrder Determines the sort order for Key2. Key3 Optional Variant Third sort field. Order3 Optional XlSortOrder Determines the sort order for Key3. Header Optional XlYesNoGuess Specifies whether the first row contains a header. xlNo is the default value; xlYes needs to be given if there is a header. xlGuess can be given if we are not sure and we want Excel to attempt to guess this. OrderCustom Optional Variant Specifies a one-based integer offset into the list of custom sort orders. MatchCase Optional Variant Set to True to perform a case-sensitive sort, False to perform non-case sensitive sort. Orientation Optional XlSortOrientation Specifies if the sort should be row-wise or column-wise. Row-wise is default. SortMethod Optional XlSortMethod Specifies the sort method. DataOption1 Optional XlSortDataOption Specifies how to sort text in Key1. DataOption2 Optional XlSortDataOption Specifies how to sort text in Key2. DataOption3 Optional XlSortDataOption Specifies how to sort text in Key3. Consider a simple example of a list as shown below which needs to be sorted either ascending or descending in the field specified: 83 This list contains 3 fields. The user can choose any one field and sort the entire table either ascending or descending based on that field. The code for this is as shown: Sub sorter () Dim rn1, k1, hd As Range Dim vl, typ As Integer typ = 1 Set rn1 = Application.InputBox ("Select Range", "List", Type: =8) Set hd = Range (rn1.Cells (1, 1), rn1.Cells (1, rn1.Columns.Count)) Set k1 = Application.InputBox ("Select single field", "Sort", Type: =8) 'Checkup - is the cell selected single and a heading or not If k1.Cells.Count > 1 Or Union (hd, k1).Address <> hd.Address Then Exit Sub End If  'Option to sort ascending or descending vl = MsgBox ("To sort Ascending press YES. Else, NO", vbYesNo, "Sort") If vl <> vbYes Then typ = 2 rn1.Sort k1, typ, Header: = xlYes End Sub The line rn1.sort is the only line new where k1 is the desired field heading cell and typ is 1 or 2 for sorting ascending or descending respectively. The header being present, the header parameter has to be given. All other parameters are optional. When the program executes, it first asks for the range. This we enter as shown below: 84 It then asks for the field heading. Let us choose say the designation field cell as shown: It then asks us to press Yes/NO to sort ascending/descending: If we click Yes, the data is sorted in ascending order as shown below: The sorting example can be altered to sort based on 2 or 3 fields. 85 The AutoFilter Method The syntax for this is as shown below: Range.AutoFilter (Field, Criteria1, Operator, Criteria2, VisibleDropDown) Parameters Name Required Data Type Description Field  Optional Variant The field number. First field is 1, second is 2 etc. from the left. Optional Variant The criteria in quotes. Use "=" to find blank fields, Use "<>" to find nonblank fields. If this argument is omitted, the criteria is All. If Operator  is xlTop10Items, Criteria1  specifies the number of  items. If criteria is 5, then the top 5 items will be displayed. Optional XlAnd, xlOr, XlTop10Items etc are XlAutoFilterOperator some of the different operators to choose from for usage. Criteria1  Operator  Criteria2  VisibleDropDown  Optional Optional Variant The second criteria (a string). Used with Criteria1  and Operator  to construct compound criteria. Variant True to display the AutoFilter dropdown arrow for the filtered field. False to hide the AutoFilter dropdown arrow for the filtered field. True by default. Consider the same example used in the sort method: 86 We will filter this range based on the designation and Locality. Designation can be “Faculty” or “Trainer”. The locality must be “R.A.Puram”. The code for this is as shown below: Sub autfltr () Dim rn1 As Range Set rn1 = Application.InputBox ("Select Range", "List", Type:=8) rn1.AutoFilter 2, "Faculty", xlOr, "Trainer", True rn1.AutoFilter 3, "R.A.Puram", , , True „The statement below makes the drop down to not be shown for Field 1 „Drop down will be there for others. This feature is not possible in Excel „It is only possible in VBA rn1.AutoFilter 1, VisibleDropDown: = False End Sub When we run the application, this will be the result: The above example can be altered to filter based on other criteria. Note: By setting only the visible drop down option, we can have filtering only for selected fields if need be. This can prevent users from filtering unwanted fields or those fields which should not be filtered. 87 The Text to Column Method This is by large a very useful method for automation. The data is usually separated for the delimiter option which is most often used. Let us however illustrate both options. The Delimited Option The syntax for the text to columns method runs as shown: Range Object.TextToColumns (Destination Range, Data Type, Delimiters) Parameters Name Required Data Type Description A Range that specifies where Excel will place the results in its top left corner. Destination Optional Variant Data Type Optional xlDelimited The format of text to be split. xlFixedWidth Optional Specifies whether to use single, double or no quotes as the text qualifier. ConsecutiveDelimiter Optional Variant True to have Microsoft Excel consider consecutive delimiters as one delimiter. The default value is False. Tab Optional Variant True to have tab character as a delimiter. The default value is False. Semicolon Optional Variant True to have semicolon as a delimiter. The default value is False. Comma Optional Variant True to have comma as a delimiter. The default value is False. Space Optional Variant True to have space as a delimiter. The default value is False. Other Optional Variant True to have any other character as delimiter. Default is False. Other Char Optional Variant (Required if Other is True). The other delimiter is specified. Text Qualifier 88 Decimal Separator Optional Variant Decimal separator to use for numbers. The default setting is the system setting. Thousands Separator Optional Variant Thousand separator to use for numbers. The default setting is the system setting. TrailingMinusNumbers Optional Variant Numbers that begin with minus sign. Consider data to be in a single column separated by a delimiter as shown: We now write the code to separate the text using text to columns option: Sub textocoldelim () Dim rn1 As Range Dim dlm As String Set rn1 = Application. InputBox ("Select Range", "List", Type: =8) dlm = InputBox ("Type the delimiter", "Separator") „Applying the text to column method Rn1.TextToColumns Destination: =rn1.Range ("a1").Offset (0, 1), _  Data Type:=xlDelimited, Consecutive Delimiter: =True, _  Other: =True, Other Char: =dlm End Sub The destination is given as the adjacent column cell. This option i s not possible in Excel. The delimiter is asked for in the inputbox. The result will be as shown below: 89 The Fixed Width Option Consider that the data is as shown: The individual values need to be separated based on the fi eld width. This is done as shown below: Sub ttcflwth () Dim rn1 As Range Set rn1 = Application.InputBox ("Select Range", "List", Type:=8) rn1.TextToColumns Destination: =rn1.Range ("a1").Offset (0, 1), _  DataType :=xlFixedWidth End Sub This macro will automatically separate the data into 3 columns as shown: 90 To be more specific such as assigning the width, the data type for each column, the syntax can be more extensively used as shown: The syntax is very similar to the Delimiter option and is as shown below as an example for which the explanation is given: RangeObject.TextToColumns Destination: =Range ("B4"), _  Data Type: =xlFixedWidth, _  Field Info: =Array (Array (0, 1), Array (20, 1), Array (51, 1), Array (77, 3)) Explanation: Field Info: This should be given as an array of array values. Each field or each column must be an array within the overall array. Let us take the first field: Array (0, 1) – Here 0 represents the starting field Number and 1 represents the General Data type Let us take the second field: Array (20, 1) – Here 20 represents the starting field Number and 1 represents the General Data type Let us take the fourth field: Array (77, 3) – Here 77 represents the starting field Number and 3 represents the DMY Data type. This means that the original data is a date represented in the dd-mm-yy format. Text to columns automatically changes it to a date in the specified format in your computer say “mm -dd-yy”). 91