BEST VBA COURSE

Overview: Best VBA Course

1. Introduction to VBA/Macros in Excel
• Uses of learning Macros
• What is Macro?
• What is VBA?
• Difference Between VB/VBA/VBS
• How to enable Developer Tab
• How to save the Macro file
• How to enable Macros
• How to open VBA Editor and what is the shortcut key?

2. Types of Windows in VBA Editor
• Project Explorer Window
• Properties Window
• Code Window
Sheet Code window
Module code window
Userform code window
Class code window
• Immediate window
• Object browser

3. Project Explorer Window
• What is Project Explorer window
• How to open it?

4. Properties Window
• What is Property window?
• How to Open it?
• How to open object properties
• What is the difference between Macro XlSheetVisible, XlSheetHidden and XlSheetVeryHidden?

5. What is Code Window?
• How to create Sheet code window?
• How to create Module code window?
• How to create userform code window?
• How to create Class code window?

6. What is Module?
7. What is the difference between Sheet code and module code?
8. What is Object?
• Object Model
• Properties
• Methods
• Events
• Syntax for Method with arguments
• Syntax for Method without arguments
• Syntax for Property
• Realtime examples on Methods and Properties
• How to open Object Browser

9. Immediate Window
• What is Immediate Window
• How to use it?

10. What is Procedure?
• Sub Procedure
• Function Procedure

11. Sub Procedure
• Synonyms for Procedure
• How to start and end a Macro
• How to create a simple Macro

12. Macro naming conventions
13. Different ways to run a macro
• Using Play button
• Using Default Shortcut key
• Run macro from Developer Tab
• Assigning our own shortcut key to Macro
• Assign Macro to other objects (Images, Shapes, Buttons etc.)
• Assign Macro to Quick access toolbar
• Create New Group/New Tab to assign the Macro

14. What is Record Macro?
• Advantages
• Disadvantages

15. Different Type of Errors
• Compile Errors
• Run Time Errors
• Logical Errors

16. Reference Styles
• Range
• Worksheet
• Workbook

17. Difference ways to working with Cells
• ActiveCell
• Selection
• Range method
• Cells method
• Square Bracket Method []
• Offset

18. Different Methods & Properties on Cells
• Assign Values to Cells
• Cut/Copy/Paste/PasteSpecial
• Font Properties
• Cell Background color
• Borders
• Text Alignments
• WrapText/Merge/Unmerge Cells
• Indent Level
• Insert/Delete Cells/Rows/Columns
• Row Height/Column Width
• Hide/unhide rows/columns
• Number Formatting
• Clear Options

19. Active Window Properties
• Hide/Unhide Gridlines
• Hide/Unhide Headers
• Hide/Unhide Formulas
Activity: 1 Task using Cell Reference

20. What is with block?
21. How to give comments?
22. VBA Color codes
• Red
• Blue
• Black
• Green

23. Different ways to working with sheets
• Using Sheet Name
• ActiveSheet
• Using Sheet Position/Index
• Using Sheet ID

24. Different Methods & Properties of Sheets
• Create a Sheet
• Move/Copy Sheet
• Sheet Tab Color
• Hide/Unhide Sheet
• Activate Sheet
• Protect/Unprotect Sheet
• Delete Sheet
• How to disable display alerts
Activity: 1 Task using Sheet Reference

25. Different ways to working with workbooks
• ActiveWorkbook
• Thisworkbook
• Using Workbook Name
• ActiveWorkbook vs Thisworkbook

26. Different Methods & Properties of Workbooks
• Create a Workbook
• Open Existing Workbook
• Save File
• Close File
Activity: 1 Task using workbook Reference

27. Get and Set Properties
Activity: 1 Task using get & set properties

28. Variables
• Data Types in VBA (Byte, Integer, Long, Single, Double, Currency, Boolean, String, Object, Variant)
• Variable Declaration
• Option Explicit
• Scope of Variables
• Advantages & Disadvantages of Variant

29. Different Types of Loops in VBA
• For Loop
• Do While
• Do Until
• For Each

30. For Loop
• For Loop Syntax
• For loop examples using Range
• For Loop examples using cells
• Nested for Loop
• What is Step (Step 1, 2, 3… & Step -1, -2, -3…)
Activity: 10 Interview based Task using For Loop

31. If Condition
• IF and then in single line
• IF and then in multiple lines
• If-Else condition
• Nested if

32. Select – Case
Activity: 3 Interview based Tasks using If & Select

33. Conditional Loops (using For and IF)
34. What is Exit for?
Activity: 5 Interview Tasks using Conditional Loop

35. How to find Number of Rows/Columns in Data set(Last Row/Last Column)
Activity: 1 Interview based Tasks using LastRow/LastColumn

36. How to Remove Duplicate Values
37. How to apply Filters
38. How to sort data
39. What is Screen Updating?
Activity: 5 Interview based Tasks
Project1: Data segregation using If condition
Project2: Data Segregation using Filters

40. Set Option
• Using Range
• Using Worksheet
• Using Workbooks
• Using Combinations

41. Do-While Loop
• Syntax
• Examples

42. Do-Until Loop
• Syntax
• Examples

43. Differences between Do-While and do Until
44. Differences between Do-while & For Loop
45. For Each Loop
• Syntax
• For Each using Range
• For Each using Sheet
• How to Refresh Pivot Tables using For Each Loop
Project3: Data Consolidation using For-Each Loop

46. Functions
• Excel Functions
• VBA Functions
• User Defined Functions (UDF)

47. Excel Functions
• How to use Excel functions in VBA
• What is Formula Property

48. Run Time Errors
• What is Run Time Error
• How to Handle Run Time Errors using
On Error Resume Next
On Error Goto Label
On Error Goto 0
On Error Goto -1
• What is the list of Errors inVBA?
• How to display user defined error message
Project4: A Project Using Error Handling

49. VBA Functions
• What is VBA Function?
• How to use VBA functions?

50. User defined Functions
• Function to calculate Discount amount
• How to run user defined functions from Excel
• How to call User defined functions from Sub Procedure
• Function to reverse the string
• SpellNumber function
Project5: Create a Function to calculate Income Tax
Project6: Create a Function extract Numbers/Characters

51. What is the difference between Sub Procedure/Function Procedure?
52. How to call one Sub procedure to another Sub Procedure
53. What is ByVal and ByRef?
54. Different Types of Boxes
• MsgBox
• InputBox

55. Arrays
• What is Array?
• Static Array
• Dynamic Array
• Option Base
Project7: A Project using Arrays

56. File Dialog
• File Picker
• Folder Picker
• File Open
• File Save As
Project8: A Project using File Dialogs

57. Controls
• What are the different controls?
• What is the difference between Form Controls and Active- X Controls?

58. Events
• What is Event?
• Different Types of Events
• Workbook Open Event
• Sheet Change
• Sheet Activate
• Workbook Close

59. UserForms
• Create a Userform
• How to Call One userform to another userform
• Store data from Userform to Excel
• Hide/Unhide Excel Application
• Userform Initialize Event
• How to close Userform
Project9: A Project using Userforms and Events
Project10: A Main Project using all concepts