VBA/Excel

Excel macro dev process

1. Record Macro 2. Perform action manually 3. Stop Macro 4. Read macro code

Why is VBA unlike other programming languages

Don't need to know how to write all types of codes • Excel can "record" actions performed by the user and give the code (macro) that, once upon run, would perform the same actions • Challenge then becomes deciphering the codes, to adapt to similar but different scenarios

Considerations for VBA analytics

Identifying opportunities for Excel automation • Define purpose of task • Data sources • Data integrity / quality • Audit trail

What is VBA?

VBA = Visual Basic of Application (Client side Script language ) a subet of Visual Basic which has a macro application is a scripting language of visual basic and used to automate window application ( this is used to automate informaton form microsoft word to microsoft excel

to show distributions

column bar chart or line chart

Macro uses for analytics

extraction, transformation, loading of data

to show composition of individual parts and how they make a whole

pie charts or stacked bar charts

to compare many category values

use column bar charts or line charts

to understand the relationship between value sets

use line charts