VBA COURSE OUTLINE
Section1: Basic of Macros in Excel
LESSON 1 : VISUAL BASIC EDITOR (VBE) IN EXCEL
OperationsandmanipulationswithVBE,VisualBasic(writingprocedure/Functions,Modules,
UserForms), Projects window/Code Window/Property window, Toolbars/Obje
ct Browser(Library Functions)
LESSON 2 : THE PROJECT WINDOW IN THE VISUAL BASIC EDITOR OF EXCEL
DetailedExplanationofproject Window, where you see all the opened Workbooks;
where youadd modules and user forms.
LESSON 3 : THE PROPERTIES WINDOW IN THE VISUAL BASIC EDITOR OF EXCEL
The propertiesof the Workbooks; the names of the modules and the properties
of all thecomponentsof theuserform.
LESSON 4 : THE CODE WINDOW IN THE VISUAL BASIC EDITOR OF EXCEL
Write and test all your code, see the dropdown lists in code Window.
LESSON 5 : DEVELOPING MACROS IN THE VBE
Startwithcreating new VBA procedures from scratch. Learn the loops, Functions,
variablesandotherfeatures.
LESSON 6 : TESTING MACROS IN THE VBE
UsetheF5/F8/F9 keys and see what you procedure does in Excel step by step.
Back up,correct andre-test.
LESSON 7 : EXCEL MACROS RECORDER
Use the r ec or d er to dev el o p ma cro s r a pi dl y, i f you want to do more the Macro r ec or d er is the best
t ea ch er and will be gr eat a s si st a n t even when you b ec o m e an ex per t at p r o g r a m m i n g in
VBA. I t wr i te s the code for you. In this ch a pter you will l ear n about and t ry the Macro r e c or
d e r . You will run the macro that you have r e c or d e d .
LESSON 8 : MODIFYING MACROS IN THE VBE
Modifytherecordedandexistingmanuallycreatedmacros..
LESSON 9 : VBA SECURITY AND PROTECTION.
Disable Macros/Enablemacros, password protection for a VBA code, Excel file. Pr
otect yourcode, your sheetsand your computer.
LESSON 10 : VBA EVENTS
For a p r o c e d ur e to start i t must be t r i gg er ed, cl i c ki n g on a butt on can cl ose a wor k book, al so
the val ue of cell can be c h a n g e d ..
Section 2: Programming Macros in Excel
LESSON 11 : CODE IN VBA FOR EXCEL
Coding tips andtricks, Howto select the cell how to enter the value into the cell
, how tomodify, how to change the text background etc. basic
LESSON 12 : WORKING WITH THE APPLICATION, WORKBOOKS, WORKSHEETS
Application eventsusing VBE like, Open (), workbook_changed (), Quit () List of f
unction tooperatetheexcel workbook, sheet(cut/copy/paste), Chart
LESSON 13 : PLAYING AROUND THE WORKSHEET
Differentfunctiontooperatetheworksheet,calculationandmore.
LESSON 14 : COMMUNICATING WITH USERS USING MESSAGE AND INPUT BOXES
Interactingwiththeuserwithminimaleffortusingdialogboxes.
LESSON 15 : WORKING WITH EXCEL DATABASE AND FILTERS
Retrievethedatabaseinformation (Access Database) into worksheet or on the use
r forms inexcel, sort andfilter the data.
LESSON 16 : DEALING WITH ERRORS
Howtodealwitherr or sin VBAcod e,h owto adde rr orhandlert oha ndletheru nti meer ror .
LESSON 17 : WORKING WITH VARIABLES.
Hardco ding/Dy nami ccod ing ,VARIANTty peva ri abl e
LESSON 18 : WORKING WITH STATEMENTS / LOOPS / FUNCTIONS.
Theya ret heKI LL,theIF_THEN_ELSE _END,SE LE CT_CASE,DO_LOOP,FOR_ NEXT,or
c reat in gE xcelf unc tions, usingexis tingVBAf unc tions( e. gLcas e,Ucase,Now, Range etc .)
LESSON 19 : WORKING WITH EXTERNAL DATA AND SQL.
When you c on n ect to outsi de so ur c e of data ( l ar ge d at a b a s e , text f i les, other excel w o r k b o o k s,
Ac ce ss etc.) the co m p uter is usi ng SQL s tr u ct u r e Qu er y l a n g u a g e) a s p e ci al i z e d l an g ua g e to
wor k with data. You can use Ex c el ’ s f u n ct i o n al i t i e s to co nn ect and e xtr a ct data but you
can also use di r ectl y the SQL l a n g u a g e. I t is much fa ster .
LESSON 20 : WORKING WITH WINDOWS AND OTHER MICROSOFT PROGRAMS.
With VBAforExcel you can develop VBA procedures (macros) to work within in Exc
el whilecallingotherMicrosoft programe4slikeAccess,Notepad, Word,Projectande
ven Windows.
Section 3: User Forms and Controls in Excel
LESSON 21 : FORMS (USER FORMS) IN VBA FOR EXCEL.
DevelopUser Forms; Manage and Set the Control Properties, access the control
with VBAcode.
LESSON 22 : PROPERTIES AND VBA CODE FOR COMMAND BUTTONS.
The“CLICK”controlwheremostofthecoderesidesinauserform.
LESSON 23 : PROPERTIES AND VBA CODE FOR LABELS & TEXT BOXES.
Theuserjustseesitandcannotsubmitanyinformationwithit.
LESSON 24 : PROPERTIES AND VBA CODE FOR COMBO BOXES
TheBIGcontrol,Itisadrop-downlist.
LESSON 25 : PROPERTIES AND VBA CODE FOR LIST BOXES.
Whenmultiplechoicesareanecessity.
LESSON 26: PROPERTIES & VBA CODE FOR CHECK BOXES, OPTION BUTTONS & FRAMES.
The“True/False”controlstobeusedasagroupwithaframe.
LESSON 27: PROPERTIES AND VBA CODE FOR SPIN BUTTONS.
Theyaresexier than text boxes and sometimes make a difference in the clients’ o
pinion ofyour talent.
LESSON 28: EXCEL VBA CONTROLS’ PROPERTIES AND VBA CODE.
Theyarethecontrolstobuildcatalogsandshoppingcarts.
VBA DO'S AND DON'TS - BEST VBA RESOURCES - CASE STUDY
