Chapter 1:
Excel Basics:
● Cell styles
● Data Validation
● Protecting Worksheets
○ Review → Protect sheet
● Adding notes
Chapter 2:
● Subroutine (sub), a macro:
○ Programmers → subroutine/procedure
○ Spreadsheet users → macro
● Program → all subs in an application
● Sub → performs one specific task in the overall program
Excel object model:
● Objects → Properties (describe an object)→ methods → argument for
methods/events
Excel objects:
● Object→ Range
● Properties → value, formula, alignment
● Methods → clear contents, copy
➔ Event handler: code that runs whenever event fires
To change the color of the active cell:
● ActiveCell.Interior.Color = 255 (VbRed)
,VBE Basics:
● IDE for programming with VBA on excel
● How to open it ?
○ Press Visual Basic OR Alt + F11
● Extensions:
○ Spreadsheets → xlsx
○ Macro → xlsm
● Features:
○ View TAB
○ Code Window
○ Project Explorer Window
■ Project (workbook)
● List of spreadsheets
■ Modules Folder
● List of modules
■ Forms
● List of user forms
○ Properties Window
■ Content depend on the selected object
○ Immediate Window
■ One-line VBA commands
○ Object Browser
● More Features:
○ Auto complete sub → end sub
○ Colors —> blue for keyword, red for syntax error, green for comments
○ Case INSENSITIVE
, Chapter 3:
● Macros:
○ Macros = procedure = subs
■ VBA codes that perform a set of actions
○ Loops & control logic → cannot be recorded
Recording Macros:
○ Developer Tab → record macros
○ Taskbar → left hand corner
● Toolbar:
● Saving → can be added to Quick access toolbar (QAT)
Drawbacks:
○ Recorded code is difficult to read
○ duplicate /create sheet → shows error
Chapter 5:
● Subroutines:
○ VBA set of code that performs a specific task
■ Sub → macro
○ Program contains multiple sub
○ Located → in module
■ VBE → insert → module
■ Sub name cannot contain space (camel case)
■ First sub → main sub
Excel Basics:
● Cell styles
● Data Validation
● Protecting Worksheets
○ Review → Protect sheet
● Adding notes
Chapter 2:
● Subroutine (sub), a macro:
○ Programmers → subroutine/procedure
○ Spreadsheet users → macro
● Program → all subs in an application
● Sub → performs one specific task in the overall program
Excel object model:
● Objects → Properties (describe an object)→ methods → argument for
methods/events
Excel objects:
● Object→ Range
● Properties → value, formula, alignment
● Methods → clear contents, copy
➔ Event handler: code that runs whenever event fires
To change the color of the active cell:
● ActiveCell.Interior.Color = 255 (VbRed)
,VBE Basics:
● IDE for programming with VBA on excel
● How to open it ?
○ Press Visual Basic OR Alt + F11
● Extensions:
○ Spreadsheets → xlsx
○ Macro → xlsm
● Features:
○ View TAB
○ Code Window
○ Project Explorer Window
■ Project (workbook)
● List of spreadsheets
■ Modules Folder
● List of modules
■ Forms
● List of user forms
○ Properties Window
■ Content depend on the selected object
○ Immediate Window
■ One-line VBA commands
○ Object Browser
● More Features:
○ Auto complete sub → end sub
○ Colors —> blue for keyword, red for syntax error, green for comments
○ Case INSENSITIVE
, Chapter 3:
● Macros:
○ Macros = procedure = subs
■ VBA codes that perform a set of actions
○ Loops & control logic → cannot be recorded
Recording Macros:
○ Developer Tab → record macros
○ Taskbar → left hand corner
● Toolbar:
● Saving → can be added to Quick access toolbar (QAT)
Drawbacks:
○ Recorded code is difficult to read
○ duplicate /create sheet → shows error
Chapter 5:
● Subroutines:
○ VBA set of code that performs a specific task
■ Sub → macro
○ Program contains multiple sub
○ Located → in module
■ VBE → insert → module
■ Sub name cannot contain space (camel case)
■ First sub → main sub
