Excel Macro Basics

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