BUSINESS ANALYSIS AND REPORT AUTOMATION WITH MICROSOFT EXCEL VBA/MACRO

Live-Online Training: N220,000
Classroom Training: N300,000
3 - 4 participants: 5% discount
5 or more participants: 10% discount

 (Available also for Customised Training by Duration, Venue & Fee)

(PARTICIPANTS SHOULD BRING THEIR LAPTOPS)

Course Contents

DAY ONE

Introduction to Excel VBA/Macro

·            Recording Excel macros

·            Assigning a keyboard shortcut

·            Inserting a Macro button

·            Getting to the VBA Editor

·            Making practically useful Macros

·            The limitation of recording Macros

·            Overcoming that limitation with just a few edit to the recorded macro

 DAY TWO

Most Important VBA Codes

(LastRow code, On Error code, ScreenUpdating code …)

·            Code to import a text file into Excel

-         Using a browser method to allow you select the text file

-         Hard linking the location and name of the file to be imported

·            If … Then … End If block

·            For … Next loop

-         Regular For loop

-         For each loop

·            While … Wend loop

·            Do … loop

-         Regular Do loop

-         Do While loop

-         Do Until loop

·               Automating analysis and recurrent reports

·               Simple practical uses

 DAY THREE

Userforms to Make More Interactive Macros

·            Inserting a Userform

·            Designing a Userform

·            Linking Userform controls to Excel cells

·            Dynamically controlling Userform controls

·            Simple practical uses

 DAY FOUR

Working with APIs and Third Party Programs

·            Pulling data from the web directly from the Macro

-         An example with stock analysis to pull daily stock price data

·            Launching a third party software to load your output file

-         An example with launching Google Earth to show operations sites already in Excel

General Excel Macro Tips