1 - An Introduction to VBA
Learn the most important lines of code from an analyst’s perspective. Learn how to automate repetitive tasks with the use of VBA. Move away for the macro recorder with some simple lines of code to automate your spreadsheets.
• Referring to sheets to avoid errors in a change of sheet name (few programmers follow these simple rules).
• Trapping a changing range (dynamic range), copy this moving range to a new location (paste values, paste formatting, paste everything).
• Using SpecialCells to isolate and manipulate like information (delete, highlight or copy this data).
• Using find and replace with VBA to locate and change data.
2 - Recording Excel VBA Macros
The macro recorder is a great tool to help you automate routine tasks without needing to learn visual basic for applications (the language sitting behind Excel). This webinar will take participants through some of the most common financial tasks and how the macro recorder can be used to take the manual process out of producing reports.
• Generating commonly using formatting, number formatting and cutting and pasting techniques to save time.
• Removing unwanted rows or columns in a single batch process.
• Adding the tasks to a toolbar, the quick access toolbar or a macro button.
• Creating the personal macro workbook and using this workbook to store commonly used Excel macros.
3 - Filtering, Sorting and Consolidation Using VBA
This webinar will simplify typical VBA code for the financial analyst. All coding will be achieved in as little lines as possible.
• Filter to isolate data, filter by colour, filter on multiple conditions.
• Sorting data efficiently, linking the range to a chart so it cascades and presents well.
• Using the advanced filter to create child sheets from a master data set.
• Consolidate a workbook, bring a group of templates into a consolidation sheet and have calculations summarise the data. Automate the process so it picks any new information.
4 - Looping and Working with VBA Change Events
This webinar takes users through some more advanced techniques in the VBA language. Looping, creating If statements and change events to manage information.
• Looping constructs – how to loop through a range of cells efficiently, how to perform the same macro a specified number of times. Covering the FOR loop, the DO While Loop, the DO until loop.
• How to loop through sheets and manipulate and cleanse your data to save time.
• Change events to trigger a macro when a cell or group of cells change.
• Creating IF statements in VBA to trigger an event.