Advanced Excel VBA

Chapter 1: Fundamentals of Programming 1st Stage

1. What is VBA?

2. Why VBA?

3. How VBA is Helpful?

4. Recording a macro?

5. Running a macro?

6. Editing a macro?

 Knowing the VBA Editor:

a) Code Window

b) Properties Window

c) Object Browser

d) Project Explorer

e) Knowing the Toolbar & Buttons

7. How to Debug/Debugging Tools:

a) Code stepping

b) Immediate window

c) Breakpoints

d) Block commenting & un-commenting

8. Putting Objects to work:

a) Excel object Model

b) Objects

c) Collections

d) Methods

e) Properties

f) Events

g) Fundamental of programming 1st Stage:

i) Why we use Variables

ii) Variables

iii) Data type

iv) Object Variables

v) Life Time of Variables

vi) Logical operators

Chapter 2: Fundamental of programming 2nd Stage

Looping:

1. For Loop…Next

2. For Each…Next

3. Do loop

4. Do While

5. Do Until

6. With….End Statements

 Conditional Branching:

1. If…Then…Else

2. Select…Case

3. What is Procedures

4. Creating Procedures

5. What is Function

6. Built in Function

7. User Defined Function

8. Practice Session

9. Doubt Clear Session

Chapter 3: Working with Excel Objects

Workbook:

1. Opening a new Workbook

2. Opening a Workbook from a particular Folder

3. Opening a Workbook using Windows get Open Dialog

4. Save & Save as method

5. Closing a Workbook

6. Deleting a File

7. Quitting an Application

8. Screen Updating

9. Protecting a Workbook

Worksheet:

1. Activate/Select a Worksheet

2. Delete

3. Protection

4. Add Worksheet/s

5. Delete Worksheet/s

Range & Cells:

a) Active Cell

b) Offset

c) Range

d) Selection

e) Clear Data

f) Auto filter

g) Advanced Filter

h) Sorting

i) Working with Build in Functions

j) Working with Formulae

k) Various other Methods& Properties

i) Practice Session

ii) Doubt Clear Session

Chapter 4: Working with Excel Objects. Continued
& Developing Front End in Excel

1. Pivots & Charts:

a) Create Pivots

b) Add/Modify Field to Pivot

c) Refresh Pivot

d) Auto Refresh All Pivots in a Workbook

e) Create Chart

2. Introduction to Forms

a) Form Properties

b) Form Control

c) Creation of a Data Entry Forms

3. Connecting Excel with other Office Applications:

i) Word

ii) Access

iii) Outlook

4. Error Handling in VBA

5. Practice Session