The MS Excel Visual Basic for Applications (Macros) course aims to impart knowledge that will enable attendees to become proficient in using the different programmatic features of MS Excel as required by industry. The other Equinox Academy modules that need to have been covered prior to subscribing for this training path, or of which prior knowledge is necessary, are:
- Microsoft Excel for Beginners, which sets the ground for customising the Excel work environment;
- The Intermediate Microsoft Excel module, which covers functionality, add-ins and further customisation; and
- The MS Excel Advanced Techniques module, which goes into how the functionalities discovered in the Intermediate module can be leveraged to provide solutions to complex problems, especially in relation to automation and object linking and embedding.
The MS Excel Macros and Visual Basic for Applications course has been designed on the basis of surveys undertaken throughout Maltese industry by Equinox Advisory Ltd, and the course effectively prepares the user to be able to take on tasks that require specialised execution of repetitive routines that are not available as standard functions in MS Excel but which can be programmed either as a macro or a function with a view to automating the same routines.
The course is composed of the following modules and requires the ownership of a MS Excel 2013 or 2016 license:
- Module 1: Introduction to VBA for Excel.
- Module 2: Working with VBA.
- Module 3: Manipulating VBA Objects and Working with Excel VBA Functions.
- Module 4: Controlling Program Flows, Execution and Events.
- Module 5: Creating Effective Code.
- Module 6: Interacting with users through Forms, Running and Storing VBA Code.
The target group for the MS Excel Macros and Visual Basic for Applications are people who are already proficient in the use of MS Excel and who use it regularly. Candidates for this course are generally individuals who might benefit or be interested in pursuing further studies in extending their knowledge of the MS Excel automation tools and who would like to be able to unlock the power of MS Excel when it comes to interacting with data repositories in a way that yields the desired computation, ordering, structuring or querying results in the shortest possible time and in the most efficient method. This course is rated as a very advanced course and as such is not suitable for individuals who have not yet been introduced to the MS Excel VBA Environment, the MS Excel formula structures, and more generally the topics that are covered in modules 1 to 3 of our other course in MS Excel for Data Analysis and Statistics (please refer to our website and the brochure for the MS Excel for Data Analysis and Statistics) if in doubt.
The high-level objectives of this course include:
- A clear understanding of how to record and run macros;
- Understanding VBA code in terms of syntax, operators and operator precedence, data types, and functions;
- Interacting with and adapting existing VBA code using the Visual Basic Editor (VBE);
- Writing VBA Code manually from scratch using the VBE to boost productivity and reduce time on routine activities;
- The effective use of automation techniques to reduce human error in repetitive tasks where off-the-shelf solutions are not available;
- Understanding the difference between Modules, Functions, Variables, and Constants;
- Performing different loops, controls and error handling in Excel macros;
- Using events to trigger specific VBA code;
- Mastering the ability to troubleshoot VBA code effectively.
Duration: 4 hours
- Excel 2013/2016 Security Settings and Macro Files
- The Developer Ribbon
- Recording and Running a Simple Macro
- Editing an Existing Macro in the Visual Basic Editor
- The New Macro Enabled File Type
- Quick Tour of the Excel Visual Basic Editor
Duration: 4 hours
- What is a Module?
- Creating a Sub Procedure Manually
- How to Call a Sub Procedure
- Creating a Function Procedure
- How to Call a Function
- Using Comments within your VBA Code
- Declaring Variables and Data Types
- The Scope of a Variable
- The Static Declaration and Variable Expiry
- Constants Instead of Variables
- An Intrinsic Constant
Duration: 4 hours
- Referencing Cells Using the Range Object
- The CELLS Property
- The OFFSET Property
- The VALUE of Ranges
- The TEXT Object of the RANGE
- A Number of Read Only RANGE Properties
- The FONT Property and Colours
- Formatting Numeric Values
- Add Formulas to Cells through VBA Code
- Common METHODS of the RANGE Object
- What is a Function?
- Built-in VBA Date Functions
- Text Manipulation with VBA Functions
- Determining File Sizes Using FILELEN
- Using Worksheet Excel Functions in VBA Code
- User Defined Functions in Excel
Duration: 4 hours
- Using GOTO and Labels
- Controlling the Program Flow with IF THEN ELSE
- Multiple Criteria plus Nested IFs
- Adding ELSEIF to Speed Up Execution
- SELECT CASE as an Alternative to IF
- The FOR NEXT Loop
- FOR NEXT with a VBA Collection
- DO WHILE Loop
- How to Create an Event
- WorkBook Events – Open
- Workbook Events – Closing and Saving
- Workbook Triggers – Activate and Deactivate
- Worksheet Triggers – Activate and Deactivate
- Other Useful Worksheet Triggers
- Using Application Events
Duration: 4 hours
- Error Trapping the Easy Way
- Make the VBA ignore errors
- Interact with the user when an error occurs
- Making use of the ERR Object
- Debugging Techniques
- Using Breakpoints to Help Debug
- Setup and use a Watch
- Speeding up the VBA Tips
Duration: 4 hours
- Using MSGBOX to Interact with Users
- Use INPUTBOX for Accepting Values from Users
- Selecting a Range with the INPUT Box
- An Introduction to UserForms
- Creating a UserForm
- Using Command Buttons in Excel
- Adding Option Buttons in a Frame
- Adding the Code that Makes the Form Work
- Calling your UserForm and Validating Content
- UserForm Controls – The CheckBox
- UserForm Controls – ListBox And/Or Combo Box
- Images on UserForms
- Labels and Text Boxes
- Multiple Tabs on a UserForm
- Allowing the User to Highlight a Range
- Scrollbar and Spin Controls
- Controlling Tab Order and Aligning Controls
- Trigger a Procedure from the Quick Access Toolbar
- Customizing the Ribbons
- Adding Buttons to Sheets
- Adding a Keyboard Shortcut Trigger
- Where to Store your VBA Code
- Adding Code to the PERSONAL File
- Producing Excel-Based Models On the Basis Of Statistics Estimated Relationships
And Building In Interval Ranges
Bernard Mallia is Equinox Academy’s trainer of choice for all advanced MS Excel courses, including VBA. He has been providing bespoke Excel courses for the past 14 years and his experience covers a number of diverse but interlinked areas such as software programming, econometric estimation, data modelling and BI strategy using Excel.
Bernard keeps himself up-to-date on the latest advances in Excel and helps customers use these new tools & features to increase their productivity and efficiency. His hands-on experience, which includes applications rolled out at industrial scale, puts him in a unique position train and assist others in their adoption of these new tools.
Bernard has been providing Excel training for the past 14 years ranging from intermediate to expert level. His vision is to help clients empower themselves and their businesses through the best use of the latest cutting edge IT Technologies with special emphasis on Excel, VBA, PowerPivot, PowerQuery, DAX, SQL and various other MS Excel add-ons.
Bernard holds an M.Sc in Information Systems, an M.Sc in Economics, a B.Com. Hons. Degree with a specialisation in economics and public policy and 3 diplomas. He is also certified as an ECDL expert and a MOS (Microsoft Office Specialist) master.
Bernard has provided, and continues to provide, MS Excel training to various sectors ranging from finance, gaming, manufacturing, educators, pharmaceuticals, telecoms, tourism and the general public. His methodology is to use a workshop environment with a hands-on approach and also providing practical guidance on how to apply various excel tools to work related problems. This methodology enables the participant to learn practically how to improve Excel use in the day to day work.
Bernard also has in-depth expertise in using Excel for Data Analysis and as a Business Intelligence tool.
In his professional life, Bernard is an experienced consultant and adviser in the fields of economics, project management, ICT and Information Systems. He has a polymathic background and was awarded his first degree in Commerce with a specialisation in Public Policy and Economics, as well as an honours degree in Public and Private Sector Management from the University of Malta. He pursued post- graduate studies at the University of Edinburgh where he read for a M.Sc. in Economics, and at the European University where he read for a M.Sc. in Information Systems. He has also been the recipient of diplomas in Computerised Bookkeeping (IAB), Project Management (CIC), and Advertising and Public Relations (CIC). Bernard is a certified MS Office advanced user and a SQL Server 2005 Microsoft Certified Professional. Bernard delivers courses in:
- MS Excel
- Financial Planning & Budgeting
- Risk Management
- Project Management
- Regulatory Policy and Institutions
- Introduction to Telecoms
The price for all six modules is of Euro 1,085 which is inclusive of the following:
- Course Notes
- Examination Fee
- Parking where available
One may also wish to register for individual
modules at the cost of Euro 260 per module.
Equinox Academy is licensed as a Higher Education License Institution (License number 2015-005) by the National Commission for Further and Higher Education.
Sessions for this course will be held in English as follows:
- Module 1 – 3rd May, 16:00 – 20:00
- Module 2 – 10th May, 16:00 – 20:00
- Module 3 – 15th May, 16:00 – 20:00
- Module 4 – 17th May, 16:00 – 20:00
- Module 5 – 22nd May, 16:00 – 20:00
- Module 6 – 24th May, 16:00 – 20:00