{"text_format":"3","workbook":"xlsx","n_sheets":5,"sheets":["Excel VBA - Introduction","VBE - VBA Code Editor","Record & Run Macros","Starting with Excel VBA","Working with Objects"],"not_rendered":{"images":7,"macros":1},"read":"whole","date_system":"1900"}

{"sheet":"Excel VBA - Introduction","index":0,"n_rows":57,"n_cols":8,"rows_kept":57}
["Excel VBA - Introduction"]
[]
[]
[null,1,null,"Programming Languages & Visual Basic"]
[]
[{"$nulls":3},"A",null,"Computers do not understand the human language, like English. The CPU understands the binary machine code ie. the 0 and 1 in binary system which is called the machine language"]
[]
[{"$nulls":3},"B",null,"Program: A program is the instructions given in the form of a set of codes (called source code), in a particular programming language, to make the computer perform an action."]
[]
[{"$nulls":3},"C",null,"Translators:  Compiler and Interpreter"]
[]
[{"$nulls":5},"a",null,"A compiler converts and executes the whole program at a time."]
[]
[{"$nulls":5},"b",null,"An Interpreter translates and executes code each line individually at a time."]
[]
[{"$nulls":5},"c",null,"A compiler translated program is executed faster as compared to a program being executed by an interpreter. A compiler reports an error after analyzing the entire program while an "]
[{"$nulls":7},"interpreter finds an error simultaneously, at the exact place where the error occurs, and stops further execution of the program till the error is rectified."]
[]
[null,2,null,"Visual Basic is GUI-based"]
[]
[{"$nulls":3},"A",null,"A graphical user interface (GUI) is a user interface with which a user can interact with the computer using images (GUI elements include windows, pull-down menus, buttons, scroll bars, ...)"]
[{"$nulls":5}," rather than pure text."]
[]
[null,3,null,"Visual Basic for Applications (VBA)"]
[]
[{"$nulls":3},"A",null,"VBA stands for Visual Basic for Applications and reference to Excel VBA indicates that the Application is Excel. It is a language which Excel understands and is used to give instructions to Excel to "]
[{"$nulls":5},"program and automate tasks."]
[]
[{"$nulls":3},"B",null,"VBA is also used in other Microsoft Office applications like Microsoft Word, Microsoft Access, PowerPoint, ... and with many third-party applications."]
[]
[{"$nulls":3},"C",null,"VBA is an event-driven programming language in which a program or code is executed on the external occurrence of an event. Events are actions performed, or occurences, which trigger a "]
[{"$nulls":5},"VBA code, viz. these could be a user action like mouse click or key press, or a message from another program."]
[]
[null,4,null,"Extending Knowledge"]
[]
[{"$nulls":3},"A",null,"Exploring VBA programming Language"]
[{"$nulls":3},"B",null,"Learning VBA Structures"]
[{"$nulls":3},"C",null,"Functions v/s Subroutines"]
[{"$nulls":3},"D",null,"Manipulation of Data"]
[{"$nulls":3},"E",null,"Interacting with user"]
[{"$nulls":3},"F",null,"Handling errors"]
[]
[null,5,null,"Advantages & Disadvantages"]
[]
[{"$nulls":3},"A",null,"Advantages"]
[{"$nulls":5},1,null,"Excel always executes the task in exactly the same way. (In most cases, consistency is a good thing.)"]
[{"$nulls":5},2,null,"Excel performs the task much faster than you can do it manually."]
[{"$nulls":5},3,null,"If you’re a good macro programmer, Excel always performs the task without errors"]
[{"$nulls":5},4,null,"If you set things up properly, someone who doesn’t know anything about Excel can perform the task."]
[{"$nulls":5},5,null,"Automating for long, time-consuming task and repeated tasks."]
[]
[{"$nulls":3},"B",null,"Disadvantages"]
[{"$nulls":5},1,null,"You have to know how to write programs in VBA"]
[{"$nulls":5},2,null,"Other people who need to use your VBA programs must have their own copies of Excel"]
[{"$nulls":5},3,null,"Sometimes, things go wrong. In other words, you can’t blindly assume that your VBA program will always work correctly under all circumstances."]
[{"$nulls":5},4,null,"Microsoft is continually upgrading Excel. Even though Microsoft puts great effort into compatibility between versions, you may discover that VBA code you’ve written doesn’t work properly "]
[{"$nulls":7},"with older versions or with a future version of Excel."]

{"sheet":"VBE - VBA Code Editor","index":1,"n_rows":74,"n_cols":12,"rows_kept":74}
["VBE - VBA Code Editor"]
[]
[]
[null,1,null,"Visual Basic Editor in Excel (VBE)"]
[]
[{"$nulls":3},"A",null,"The Visual Basic Editor (VBE) in Excel is an environment used to write, edit and debug VBA code, and is contained in the Microsoft Excel workbook."]
[]
[{"$nulls":3},"B",null,"Launch the Visual Basic Editor"]
[{"$nulls":11},"Excel 2007"]
[{"$nulls":5},"a",null,"Alternative 1:"]
[]
[{"$nulls":7},"On the Developer tab, in the Code group, click Visual Basic to display VBE."]
[]
[{"$nulls":5},"b",null,"Alternative 2:"]
[]
[{"$nulls":7},"On the Developer tab, in the Controls group, click View Code to display VBE."]
[]
[{"$nulls":5},"c",null,"Alternative 3:"]
[]
[{"$nulls":7},"Right Click the active worksheet tab at the bottom, then click on View Code to display VBE."]
[]
[{"$nulls":5},"d",null,"Alternative 4:"]
[]
[{"$nulls":7},"Press Alt+F11 keys combination to switch back and forth between workbook environment and VBE."]
[]
[{"$nulls":5},"e",null,"Alternative 5:"]
[]
[{"$nulls":7},"To edit a Macro in VBE: On the Developer tab, in the Code group, click Macros, which opens the Macro dialog box. Select a Macro Name and click on Edit."]
[]
[{"$nulls":3},"C",null,"Project Explorer"]
[]
[{"$nulls":5},"To display the Project Explorer window, you can use the shortcut keys CTRL +R in VBE"]
[]
[{"$nulls":3},"D",null,"Visual Basic Editor components"]
{"$rows_empty":27}
[{"$nulls":5},"Note:",null,"To get the list of Command Bars & Controls available in VBE click on the Button"]
[]
[]
[]
[]
[]
[null,2,null,"Subroutines vs Functions"]
[]
[{"$nulls":3},"A",null,"Sub procedures / Subroutines"]
[{"$nulls":7},"it’s a chunk of computer code that performs some action on or with objects"]
[]
[{"$nulls":3},"B",null,"Function Procedure"]
[{"$nulls":7},"A Function procedure returns a single value. You can call it from another VBA procedure or even use it as a function in a worksheet formula."]

{"sheet":"Record & Run Macros","index":2,"n_rows":60,"n_cols":15,"rows_kept":60}
["Record & Run Macros"]
[]
[]
[null,1,null,"Recording a Macro"]
[]
[{"$nulls":3},"A.",null,"An easy way to learn VBA is to record your own macros with the Excel Macro Recorder tool, and then read, run and edit the code of the macro which you have recorded in Visual Basic Editor."]
[]
[{"$nulls":3},"B.",null,"A macro is the instructions given in the form of a set of codes to make the computer perform an action"]
[]
[{"$nulls":3},"C",null,"In the beginning when not very conversant with vba, you can record a macro while performing a specific task, and then going to the VBE you can view the required set of codes."]
[]
[]
[null,2,null,"Start Recording a Macro"]
[{"$nulls":10},"Excel 2007"]
[]
[{"$nulls":3},"a",null,"Alternative 1:"]
[]
[{"$nulls":7},"Click View tab on the ribbon, click Macros in the Macros group, and then click Record Macro which will open the dialog box of Record Macro"]
[]
[{"$nulls":3},"b",null,"Alternative 2:"]
[]
[{"$nulls":7},"Open the Record Macro dialog box is to click the Developer tab on the ribbon, and click Record Macro in the Code group."]
[]
[{"$nulls":3},"c",null,"Alternative 3:"]
[]
[{"$nulls":7},"Also can be opened trough the Record Macro dialog box by clicking on the icon appearing on the Status Bar which appears at the bottom Left of the Microsoft Office Excel 2007 window"]
{"$rows_empty":13}
[null,3,null,"Naming a Macro"]
[]
[{"$nulls":3},"A macro name must start with a letter as the first character while subsequent characters can be letters, numbers or an underscore (which is a good way of separating words). Spaces and most special characters are not allowed. "]
[{"$nulls":3},"It can have a maximum of 255 characters. A macro name cannot use keywords / reserved words such as If, And, Or, Loop, Do, Len, Close, Date, ElseIf, Else, Select, ... that VBA uses as part of its programming language. "]
[{"$nulls":3},"It will help to assign a name which is reflective of the action you wish to perform with the macro."]
[]
[null,4,null,"Store a Macro"]
[]
[{"$nulls":3},"This Workbook (macro is stored in your current workbook), New Workbook (this creates a new workbook, default name Book1, Book2, and so on, where the macro gets stored), or Personal Macro Workbook. "]
[]
[null,5,null,"Things to Remember"]
[{"$nulls":11},"Sample Work File 1",null,null,"Sample Work File 2"]
[{"$nulls":3},"A",null,"Recording Mode:",null,"Absolute Way of Recording Macro"]
[]
[{"$nulls":7},"Relative Way of Recording Macro"]
[]
[{"$nulls":3},"B",null,"Assign a Keyboard Shortcut"]
[]
[{"$nulls":3},"C",null,"Store a Macro"]
[]
[{"$nulls":3},"D",null,"Add a Description"]

{"sheet":"Starting with Excel VBA","index":3,"n_rows":134,"n_cols":25,"rows_kept":134}
["Starting with Excel VBA"]
[]
[]
[null,1,null,"VBA Codes, VBA Objects, Properties , Methods & Events:"]
[]
[{"$nulls":3},"A",null,"A set of VBA codes are called macros or procedures"]
[]
[{"$nulls":3},"B",null,"Excel VBA Objects, Properties & Methods:"]
[]
[{"$nulls":5},"a.",null,"An Object is a thing which contains data and has properties and methods"]
[]
[{"$nulls":5},"b.",null,"Properties are the characteristics or attributes that describe the object (like name, color, size) or define an object's behaviour (viz. if visible or enabled). An object's data or information can be"]
[{"$nulls":7},"accessed with properties (viz. Value property, Name property)."]
[]
[{"$nulls":5},"c.",null,"A Method is an action performed by an object. Calling a Method will execute a vba code which will cause the object to perform an action"]
[]
[{"$nulls":5},"d.",null,"A Collection Object in vba refers to a group of related items, as a single object. Many objects are present both in single form as well as in multiples. For example: (i) Workbook & Workbooks - all open "]
[{"$nulls":7},"Workbook objects in Excel are referred to as the Workbooks collection; (ii) Worksheet & Worksheets - A Worksheets Collection object refers to all Worksheets contained in a workbook."]
[]
[{"$nulls":5},"Example:",null,"If you are to say someone to kick the object (\"Ball\") then how would you say it?"]
[]
[{"$nulls":5},"English Language",null,"KICK   THE    BALL",{"$nulls":7},"English Language",null,"PICKUP  THE  RED  BALL  OUT  OF  ALL  THE  BALLS  LYING  IN  THE  GROUND"]
[]
[{"$nulls":5},"VBA Language",null,"BALL           KICK",{"$nulls":7},"VBA Language",null,"BALLS ( \" RED \" )       SELECT"]
[]
[]
[{"$nulls":5},"English Language",null,"KICK   THE    BALL   TOWARDS   LEFT",{"$nulls":7},"English Language",null,"BLOW  UP  THE  AIR  PREASSURE  OF  THE  FOOTBALL  TO  20"]
[]
[{"$nulls":5},"VBA Language",null,"BALL           KICK     DIRECTION:=  LEFT",{"$nulls":7},"VBA Language",null,"BALL        AIRPREASSURE    =  20"]
[]
[]
[{"$nulls":5},"English Language",null,"KICK   THE    BALL   TOWARDS   LEFT    VERY   HARD"]
[{"$nulls":15},"Object",null,"CAR",{"$nulls":4},"Object",null,"Workbooks"]
[{"$nulls":5},"VBA Language",null,"BALL           KICK     DIRECTION:=  LEFT     FORCE:=   VERY HARD"]
[{"$nulls":15},"Properties",{"$nulls":6},"Properties"]
[{"$nulls":15},"Name",null,"Audi",{"$nulls":4},"Name",null,"Book1"]
[{"$nulls":15},"Color",null,"Black, White, Blue"]
[{"$nulls":15},"Type",null,"Cedan, HitchBack",{"$nulls":4},"Methods"]
[{"$nulls":15},"Engine",null,"Petrol",{"$nulls":6},"Open"]
[{"$nulls":24},"Close"]
[{"$nulls":15},"Methods",{"$nulls":8},"Add"]
[{"$nulls":17},"Start"]
[{"$nulls":17},"Off",{"$nulls":4},"Events"]
[{"$nulls":17},"Park",{"$nulls":6},"Before Open"]
[{"$nulls":24},"Before Close"]
[{"$nulls":15},"Events"]
[{"$nulls":17},"After Start of Engine"]
[{"$nulls":17},"Before Shutting down the Engine"]
[]
[]
[null,2,null,"Modules in Excel VBE:"]
[]
[{"$nulls":5},"Object Modules in Excel are: ThisWorkbook module, Sheet modules (worksheets and chart sheets), UserForm modules and Class modules."]
[]
[{"$nulls":5},"Standard Code Modules: These are also referred to as Code Modules or Modules, and there can be any number of these Modules (Module1, Module2, …) in a VBA project, wherein each Module can be used"]
[{"$nulls":5}," for covering a certain aspect of the project. To insert a Module click Insert on the VBE Menu Bar and then select Module"]
[]
[]
[null,3,null,"VBA Procedures:"]
[]
[{"$nulls":5},"A Procedure is usually of two types, a sub-procedure (viz. sub-routine) or a function."]
[]
[]
[null,4,null,"Creating a sub-procedure:"]
[]
[{"$nulls":5},"A sub-procedure declaration statement starts with the keyword Sub, followed by a name and then followed by a set of parentheses. A sub-procedure ends with an End Sub statement which can be typed but"]
[{"$nulls":5}," it also appears automatically on going to the next line after the Sub declaration statement is typed."]
[]
[]
[null,5,null,"Line Continuation within VBA code:"]
[]
[{"$nulls":5},"Many a times while writing code in VBA, a single line of code might get very long and in the code window you will need to scroll to the right side to read it in full. To split a single line into multiple lines in VBA, "]
[{"$nulls":5},"you can use the Line Continuation Character which is the combination of a space followed by an underscore"]
[]
[{"$nulls":5},"Using this as a line break, will enable you to continue to the next line and so on, and this will treat all these continued lines as a single line in your VBA code."]
[]
[{"$nulls":3},"Example: ",null,"The first code uses the Line Continuation Character to break a single line into 2 lines, whereas the second code is the same in a single line."]
[]
[{"$nulls":5},"APPLICATION       WORKBOOKS ( \" BOOK1 \")       WORKSHEETS ( \" SHEET1 \") _"]
[{"$nulls":5},"    RANGE ( \" A1 \" )  ACTIVATE"]
[]
[{"$nulls":5},"APPLICATION       WORKBOOKS ( \" BOOK1 \")       WORKSHEETS ( \" SHEET1 \")     RANGE ( \" A1 \" )  ACTIVATE"]
[]
[]
[null,6,null,"Comment Text within VBA code:"]
[]
[{"$nulls":5},"Within a Procedure, while writing code you can simultaneously provide comments to explain the purpose and what the code is doing. To differentiate your comments with the code, you will identify the comments by "]
[{"$nulls":5},"preceding them with either a single apostrophe character (') or with Rem followed by a space. Comment text will not be considered and will be ignored by VBA so that a syntax error is not returned. It is strongly "]
[{"$nulls":5},"recommended to use comment text in your code which will be of immense help to another user in understanding, or if you might want to edit at a later date."]
{"$rows_empty":16}
[null,7,null,"Indenting your VBA code:"]
[]
[{"$nulls":5},"Your VBA Procedure may consist of multiple lines of code with a series of statements. As your code gets lengthier and more complex, formatting the code with indentation will help make it easier to read, and it will "]
[{"$nulls":5},"also make debugging simpler. Indenting means moving code to the right side. Developers typically use indenting for code within the beginning and end lines of loops such as If...Else...End If, For...Next, etc. wherein the"]
[{"$nulls":5}," series of statements between a For..Next loop are indented to distinguish them belonging to the particular loop. Indenting is particulalry useful in nested code so that each block of code is visually separated, and the "]
[{"$nulls":5},"beginning and end lines of each block are exactly lined up. Indenting is usually done by pressing the Tab key once or multiple times as required, before typing your statement."]
{"$rows_empty":17}
[null,8,null,"Using Variables in VBA:"]
[]
[{"$nulls":5},"A variable is a named storage location used to store temporary values or information for use in execution of the code. In your vba program, a variable stores data and its content is used or changed later while executing the code. "]
[{"$nulls":5},"By declaring a variable for use in your code, you tell the Visual Basic compiler the variable's data type (type of value it represents viz. integer, decimal, text, boolean, etc.) and other information such as its scope/level "]
[{"$nulls":5},"(what code can access it - variables can be Procedure Level, Module Level or can have a Public scope). Variables must be explicitly declared using the Dim, Private, Public, ReDim, or Static statements. When you declare variables "]
[{"$nulls":5},"by using a Dim statement (Dim is short for dimension): for declaring a variable to hold an Integer value, use \"Dim rowNumber As Integer\"; for declaring a variable to hold text values, use \"Dim strEmployeeName As String\"; and so on."]

{"sheet":"Working with Objects","index":4,"n_rows":8,"n_cols":6,"rows_kept":8}
["Working with Objects"]
[]
[]
[null,1,null,"The Excel Object Model hierarchy"]
[]
[{"$nulls":5},"The Object Model of the Application (Excel) refers to and contains its programming objects which are related to each other in a hierarchy. The entire Excel application is represented by the Application Object which is at the top of the "]
[{"$nulls":5},"Excel object hierarchy and moving down you can access the objects from Application to Workbook to Worksheet to Range (Cells) and further on, by connecting the objects with a period (dot). Excel "]
[{"$nulls":5},"objects are accessed through 'parent' objects"]