Visual Basic. Macros

TEACHING UNIT 1. MACROS 1. Definition. Recording and running a macro using the Macro Recorder 2. Macro and add-in security 3. Relative and absolute references in macros 4. Generic and workbook-specific macros 5. Deleting macros 6. Sub Procedures vs. VBA Functions 7. Different Ways to Run Sub Procedures LESSON UNIT 2. EXCEL OBJECT MODEL: DEFINITION AND APPLICATIONS 1. Simplified Data Model 2. Complete Data Model. Main Elements of the Data Model 3. Object Hierarchy in Excel: Cell, Range, Worksheet, Workbook, Window, Application TEACHING UNIT 3. PROGRAMMING ENVIRONMENT IN EXCEL: VISUAL BASIC FOR APPLICATIONS (VBA) 1. The Visual Basic Editor 2. User Interface (Properties, Project, and Debug windows) 3. Project Organization 4. Projects and Modules 5. Code Window 6. The Object Inspector 7. Using the Immediate Window in VBA 8. Using the Locals Window in VBA 9. The Concept of Breakpoints and Their Use TEACHING UNIT 4. PROGRAMMING WITH VBA (I) 1. Concepts of Object, Property, Method, Event, and Collection 2. Object Properties 3. Object Methods 4. The Concept of Events. Applying Events to Workbooks and Worksheets 5. Working with Ranges or Cells 6. Using the OFFSET Function 7. Working with Ranges of Values 8. Filling a Range with Random Values 9. Filling Ranges with Values, by Rows and Columns, Starting from an Initial Value 10. Removing Decimal Places 11. Adding the Same Number to the Values in a Range 12. Multiplying the Values in a Range by the Same Number 13. Highlighting the Highest Values 14. Working with Worksheets and Workbooks 15. Automatically Sorting Worksheets Using VBA 16. Printing Worksheet Names 17. Inserting Multiple Rows and Columns 18. Deleting Empty Worksheets 19. Inserting a Specific Number of Pages 20. Different ways to find the last row in a range 21. Inserting blank rows into a sorted table, separating data 22. Deleting blank rows in a table TEACHING UNIT 5. INSTRUCTIONS IN VBA 1. Basic input-output instructions (INPUTBOX and MSGBOX) 2. Data types 3. Formula vs. Local Formula vs. R1C1 Formula 4. Excel and VBA formats 5. Loops in VBA TEACHING UNIT 6. PROGRAMMING WITH VBA (II) 1. Comments and line breaks in programs 2. Declaring variables and constants 3. The OPTION EXPLICIT clause 4. Scope of Variables 5. Scope of Procedures and Functions 6. Operators 7. Creating a User-Defined Function 8. Difference Between a Procedure and a Function 9. Calling Procedures and Functions 10. Creating Links to Excel.docx Worksheets 11. Creating separate files for each Excel worksheet 12. Simultaneously copying and pasting multiple non-adjacent cells and ranges 13. Working with Collections TEACHING UNIT 7. DEBUGGING AND ERROR HANDLING 1. Using debugging tools. Using the Debug window 2. Types of errors in VBA. Locating errors 3. Error handling during execution. VBA error handling TEACHING UNIT 8. PRACTICAL CREATION OF VBA APPLICATIONS IN WORKBOOKS AND WORKSHEETS 1. Troubleshooting VBA code errors in pivot tables, starting with Excel 2010 2. Connecting Excel to databases via ADO (ActiveX Data Objects) 3. Creating ADO connections and record sets 4. Inserting SQL statements within VBA to execute queries 5. Inserting Excel variables within SQL expressions 6. Connecting an Access database to Excel via ADO 7. Explanation of an expression to distribute data across worksheets 8. Manipulating data from Excel tables using VBA 9. Copying ranges from closed workbooks using ADO 10. Importing data from a text file using ADO 11. Iterating through data in a folder and all its subfolders 12. Analysis of various VBA utilities 13. Macro to paste Excel cells into Word 14. Printing via VBA. Printing with narrow margins 15. Using .XLAM files 16. Saving a collection of macros in an .XLAM file as an add-in 17. Removing add-ins in Excel 18. Creating separate files for each Excel worksheet 19. Adding a macro to a context menu (right-click) 20. Running a macro at a specific time or at intervals 21. Selecting one or more files using a dialog box via GetOpenFilename 22. Methods for increasing VBA speed