In a working environment where the automation and efficiency are key; proficiency in tools such as Visual Basic for Applications (VBA) in Excel has become essential. The course Visual Basic. Macros It introduces you to VBA programming, a highly sought-after and valued skill across a wide range of sectors. Through this online course, you’ll delve into the creation and management of macros, the Excel object model and the specific programming of this platform. You’ll learn how to develop bespoke applications that streamline repetitive tasks, and how to debug and manage errors effectively. Choosing us means opting for flexible learning tailored to your current trends in the job market, where the ability to use Excel can open up new career opportunities and boost your competitiveness.
Visual Basic. Macros
Digital Skills
40 hours
Introduction
Objectives
-
Learn how to create macros for automate tasks efficiently handle repetitive tasks in Excel.
-
Identify and use the object model Excel for working with data and spreadsheets.
-
To study the environment of VBA programming in Excel to develop bespoke solutions.
-
Developing writing skills working VBA code and organised in Excel.
Table of Contents
TEACHING UNIT 1. MACROS
1. Definition. Recording and running a macro using the Macro Recorder
2. Macro and add-in security
3. Relative or absolute macro references
4. Generic or work-specific macros
5. Remove macros
6. Sub Procedures vs VBA Functions
7. Different ways of carrying out sub-procedures
TEACHING UNIT 2. EXCEL OBJECT MODEL: DEFINITION AND APPLICATIONS
1. Simplified data model
2. Complete data model. Key elements of the data model
3. Object hierarchy in Excel: Cell, Range, Worksheet, Workbook, Window, Application
TEACHING UNIT 3. EXCEL PROGRAMMING ENVIRONMENT: VISUAL BASIC FOR APPLICATIONS (VBA)
1. The Visual Basic Editor
2. User interface (properties, project and debugging windows)
3. Project organisation.
4. Projects and Modules
5. Code window
6. The object examiner
7. Using the Immediate Window in VBA
8. Using the ‘Locales’ window in VBA
9. The concept of a breakpoint and its usefulness
TEACHING UNIT 4. PROGRAMMING WITH VBA (I)
1. The concepts of object, property, method, event and collection
2. Properties of objects
3. Object methods
4. The concept of an event. Applying events to the Workbook 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. Fill ranges with values, by rows and columns, starting from an initial value
10. Remove decimals
11. Add the same number to the values in a range
12. Multiply the values in a range by the same number
13. Highlight higher values
14. Working with sheets of paper and books
15. Automatic sorting of worksheets using VBA
16. Print sheet names
17. Inserting multiple rows and columns
18. Delete blank pages
19. Insert a specific number of pages
20. Different ways of finding the last row in a range
21. Insert blank rows into a sorted table, separating the data
22. Remove empty rows from a table
TEACHING UNIT 5. VBA INSTRUCTIONS
1. Basic input and output instructions (INPUTBOX and MSGBOX)
2. Data types
3. Formula vs. FormulaLocal vs. Formula R1C1
4. Excel and VBA formats
5. Loops in VBA
TEACHING UNIT 6. PROGRAMMING WITH VBA (II)
1. Comments and continuation lines in programmes
2. Declaration of variables and constants
3. The purpose of the OPTION EXPLICIT clause
4. Scope of variables
5. Scope of procedures and functions
6. Operators
7. Create a user-defined function
8. The difference between a procedure and a function
9. Calls to procedures and functions
10. Create links to Excel.docx files
11. Create separate files for each Excel worksheet
12. Copying and pasting several non-adjacent cells and ranges simultaneously
13. Working with Collections
TEACHING UNIT 7. DEBUGGING AND ERROR HANDLING
1. Using the debugging tools. Using the debug window
2. Types of errors in VBA. Troubleshooting errors
3. Error handling during execution. VBA error statement
TEACHING UNIT 8. PRACTICAL CREATION OF VBA APPLICATIONS IN WORKBOOKS AND WORKSHEETS
1. Troubleshooting VBA code errors in PivotTables, from Excel 2010 onwards
2. Connecting Excel to databases via ADO (ActiveX Data Objects)
3. Creating ADO connections and record sets
4. Inserting SQL statements into VBA to run queries
5. Inserting Excel variables into SQL expressions
6. Connecting an Access database to Excel via ADO
7. Explanation of the formula for distributing data across sheets
8. Manipulating data in Excel tables using VBA
9. Copying ranges from closed workbooks using ADO
10. Importing data from a text file using ADO
11. Browse the files in a folder and all its subfolders
12. Analysis of various functions in VBA
13. Macro for pasting Excel cells into Word
14. Printing via VBA. Printing with narrow margins
15. Using .XLAM files
16. Save a collection of macros to an xlam file as an add-in
17. Removing add-ins in Excel
18. Create separate files for each Excel worksheet
19. Add a macro to a context menu (right-click)
20. Run a macro at a specific time, or at intervals
21. Select one or more files using a dialogue box, via GetOpenFilename
22. Methods for increasing VBA speed