Advanced Excel VBA Crash Course

Introduction-1

Why learn Macros?

Preview

Activate "Developer" tab

Preview

Saving a Macro-enabled WorkBook (XLSM)

Macro Security Settings

Developer tab - Essential Buttons

Head & Tail of a Macros (Sub Procedure & User Defined Function)

"Click Here" button to run a Macro

Downloadable Working files

Practice Files

INTRODUCTION 2

Macros - Record & Run

Preview

Macros - Viewing the code

Macro-free vs. Macro-enabled Workbook

Visual Basic Editor (VBE)

Personal Macro WB

Ways to Run a Macro

GOOGLE

Using Google to locate useful VBA codes – 1

Using VBE and Macro Recorder to customize Macro code

Using Google to locate useful VBA codes – 2

OBJECTS, PROPERTIES, METHODS

Objects, Properties, Values, Methods and Parameters 1

Objects, Properties, Values, Methods and Parameters 2

WORKSPACE

Code Color & Activating Edit Toolbar w. Comments

Edit Toolbar - Indent, Bookmark

Code writing shortcuts incl. Intellisense

Testing the Code

Immediate Window 01

RANGE & CELLS OBJECT

Range Selection - Method 1

Range Selection - Method 2 & 3

Advantage of Range Selection - Method 2 & 3

Range Selection (Rows & Columns) - Method 4

Assigning values to selected range of cells

Exercise 1 - Assigning values to selected range of cells

Exercise 2- Assigning values to selected range of cells

Multiple Range Selection - Method 5

Multiple Range Selection - Method 6

Range Selection Ways – Others

ABSOLUTE vs RELATIVE REFERENCE

Offset – Basics

Using Relative Reference in Macros

RANGE NAVIGATION

Navigating between a range of cells - Part 1

Navigating between a range of cells - Part 2

Navigating between a range of cells - Part 3

Exercise - Finding Last Row No.

Exercise - Finding Last Column No.

Exercise - Finding Next Row No.

Difference - DIM vs. SET

LOOPS

"For...Next"

"If...Else...End If" nested within "For...Next"

"Do…While"

"Do…Until"

"For Each...Next"

"If...End If" nested within "For Each...Next"

Exercise - "Do While" and Loop Counter

"With…End With" - Example 1

"With…End With" - Example 2

"Select…Case" – Demonstration

"Select…Case" – Explanation

OPTIMIZING CODE EFFICIENCY

Speed up the code running – 1

Speed up the code running – 2

Speed up the code running – 3

EVENTS

Introduction

Events - Workbook Open and Worksheet Activate

Exercise 1 - Tracking Workbook Opens

Exercise 2 - Tracking Workbook Save

Event - Worksheet Before Double Click

Event - Worksheet Change

Event - Worksheet Calculate

Pivot Table - Refresh - Work Sheet Activate

Introduction to Msg box in excel vba

Form Controls vs ActiveX Controls

MouseMove Event w. Appln Statusbar

USER FORMS

Introduction

User Form – Basics

Exercise - Part 1 of 4

Exercise - Part 2 of 4

Exercise - Part 3 of 4

Exercise - Part 4 of 4

Option Button – 1

Option Button – 2

Multi Page – Introduction

USER DEFINED FUNCTIONS

UDF Intro 01

UDF Example 1 - ScoreResult with IF END IF

UDF Example 2 – CellComments

UDF Samples - Series A

UDF Samples - Series B

MIS PROJECTS

Split Workbook

Protect - Unprotect Sheets

Index of Hyperlinks to all Sheets

Extract all Comments of a WorkBook to a new WorkSheet

Project- Automate,Comment,Insertion and Extraction

Count Cells by Color

Combine WorkSheets

Index of Hyperlinks to all Sheets

Advanced Excel VBA Crash Course MIS PROJECTS

By: Yoda Learning

5 minutes

Share the link to this page

Copied

Facebook

Twitter

Add the class to your calendar

Add to Google Calendar

Add to Apple Calendar

Add to Yahoo Calendar

Add to Outlook Calendar

Completed

You need to have access to the item to view this lesson.

One-time Fee

$69.99

List Price: $99.99

You save: $30

€61.12

List Price: €87.32

You save: €26.20

£52.83

List Price: £75.47

You save: £22.64

CA$95.88

List Price: CA$136.98

You save: CA$41.09

A$99.63

List Price: A$142.34

You save: A$42.70

S$89.63

List Price: S$128.05

You save: S$38.42

HK$548.08

List Price: HK$783.01

You save: HK$234.92

CHF 55.24

List Price: CHF 78.92

You save: CHF 23.67

NOK kr680.65

List Price: NOK kr972.40

You save: NOK kr291.74

DKK kr456.73

List Price: DKK kr652.50

You save: DKK kr195.77

NZ$120.20

List Price: NZ$171.72

You save: NZ$51.52

د.إ257.03

List Price: د.إ367.21

You save: د.إ110.17

৳8,602.80

List Price: ৳12,290.24

You save: ৳3,687.44

₹6,468.47

List Price: ₹9,241.07

You save: ₹2,772.59

RM275.09

List Price: RM393.01

You save: RM117.91

₦97,104.82

List Price: ₦138,727.12

You save: ₦41,622.30

₨19,575.56

List Price: ₨27,966.28

You save: ₨8,390.72

฿2,276.59

List Price: ฿3,252.42

You save: ฿975.82

₺3,092.92

List Price: ₺4,418.64

You save: ₺1,325.72

B$375.65

List Price: B$536.66

You save: B$161.01

R1,181.33

List Price: R1,687.69

You save: R506.36

Лв119.54

List Price: Лв170.79

You save: Лв51.24

₩104,708.49

List Price: ₩149,589.97

You save: ₩44,881.48

₪219.52

List Price: ₪313.61

You save: ₪94.09

₱4,185.33

List Price: ₱5,979.30

You save: ₱1,793.97

¥11,151.01

List Price: ¥15,930.69

You save: ¥4,779.68

MX$1,248.74

List Price: MX$1,784

You save: MX$535.25

QR254.85

List Price: QR364.08

You save: QR109.23

P955.34

List Price: P1,364.83

You save: P409.49

KSh9,048.30

List Price: KSh12,926.70

You save: KSh3,878.40

E£3,669.34

List Price: E£5,242.14

You save: E£1,572.80

ብር10,943.40

List Price: ብር15,634.10

You save: ብር4,690.70

Kz63,840.04

List Price: Kz91,203.97

You save: Kz27,363.93

CLP$64,439.09

List Price: CLP$92,059.79

You save: CLP$27,620.70

CN¥482.66

List Price: CN¥689.55

You save: CN¥206.88

RD$4,307.22

List Price: RD$6,153.44

You save: RD$1,846.21

DA9,273.75

List Price: DA13,248.79

You save: DA3,975.03

FJ$155.39

List Price: FJ$222

You save: FJ$66.60

Q537.63

List Price: Q768.08

You save: Q230.44

GY$14,667.49

List Price: GY$20,954.45

You save: GY$6,286.96

ISK kr8,815.24

List Price: ISK kr12,593.74

You save: ISK kr3,778.50

DH660.30

List Price: DH943.33

You save: DH283.02

L1,225.75

List Price: L1,751.14

You save: L525.39

ден3,769.07

List Price: ден5,384.61

You save: ден1,615.54

MOP$565.16

List Price: MOP$807.41

You save: MOP$242.24

N$1,177.50

List Price: N$1,682.21

You save: N$504.71

C$2,579.69

List Price: C$3,685.43

You save: C$1,105.73

रु10,354.15

List Price: रु14,792.28

You save: रु4,438.12

S/241.76

List Price: S/345.39

You save: S/103.62

K306.56

List Price: K437.97

You save: K131.40

SAR262.64

List Price: SAR375.21

You save: SAR112.57

ZK1,364.60

List Price: ZK1,949.51

You save: ZK584.91

L311.38

List Price: L444.85

You save: L133.47

Kč1,493.84

List Price: Kč2,134.15

You save: Kč640.30

Ft23,940.09

List Price: Ft34,201.59

You save: Ft10,261.50

SEK kr658.56

List Price: SEK kr940.84

You save: SEK kr282.28

ARS$97,863.52

List Price: ARS$139,811.02

You save: ARS$41,947.50

Bs484.45

List Price: Bs692.10

You save: Bs207.65

COP$258,111.38

List Price: COP$368,746.35

You save: COP$110,634.96

₡32,985.81

List Price: ₡47,124.61

You save: ₡14,138.79

L1,855.78

List Price: L2,651.23

You save: L795.45

₲452,300.17

List Price: ₲646,170.79

You save: ₲193,870.62

$U2,826.59

List Price: $U4,038.16

You save: $U1,211.57

zł260.99

List Price: zł372.86

You save: zł111.86

Already have an account? Log In

Advanced Excel VBA Crash Course

Index of Hyperlinks to all Sheets

Sign Up

Sign Up

Share