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

"With…End With" - Example 2

Advanced Excel VBA Crash Course LOOPS

By: Yoda Learning

2 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

€59.35

List Price: €84.79

You save: €25.44

£51.25

List Price: £73.22

You save: £21.97

CA$95.12

List Price: CA$135.90

You save: CA$40.77

A$106.44

List Price: A$152.06

You save: A$45.62

S$89.09

List Price: S$127.29

You save: S$38.19

HK$549.41

List Price: HK$784.91

You save: HK$235.49

CHF 55.48

List Price: CHF 79.26

You save: CHF 23.78

NOK kr704.86

List Price: NOK kr1,006.99

You save: NOK kr302.12

DKK kr442.85

List Price: DKK kr632.67

You save: DKK kr189.82

NZ$115.26

List Price: NZ$164.67

You save: NZ$49.40

د.إ257.07

List Price: د.إ367.26

You save: د.إ110.19

৳8,536.76

List Price: ৳12,195.90

You save: ৳3,659.13

₹5,970

List Price: ₹8,528.94

You save: ₹2,558.93

RM295.56

List Price: RM422.25

You save: RM126.69

₦106,988.81

List Price: ₦152,847.71

You save: ₦45,858.90

₨19,910.50

List Price: ₨28,444.79

You save: ₨8,534.29

฿2,267.58

List Price: ฿3,239.55

You save: ฿971.96

₺2,791.47

List Price: ₺3,987.98

You save: ₺1,196.51

B$380.23

List Price: B$543.21

You save: B$162.98

R1,226.16

List Price: R1,751.73

You save: R525.57

Лв116

List Price: Лв165.73

You save: Лв49.72

₩95,271.59

List Price: ₩136,108.10

You save: ₩40,836.51

₪235.24

List Price: ₪336.07

You save: ₪100.83

₱3,940.78

List Price: ₱5,629.93

You save: ₱1,689.15

¥10,068.55

List Price: ¥14,384.26

You save: ¥4,315.71

MX$1,312.90

List Price: MX$1,875.65

You save: MX$562.75

QR254.48

List Price: QR363.56

You save: QR109.08

P926.33

List Price: P1,323.39

You save: P397.05

KSh9,046.20

List Price: KSh12,923.70

You save: KSh3,877.50

E£3,453.96

List Price: E£4,934.44

You save: E£1,480.48

ብር9,629

List Price: ብር13,756.30

You save: ብር4,127.30

Kz63,827.73

List Price: Kz91,186.39

You save: Kz27,358.65

CLP$64,828.93

List Price: CLP$92,616.73

You save: CLP$27,787.80

CN¥501.17

List Price: CN¥715.99

You save: CN¥214.82

RD$4,147.97

List Price: RD$5,925.92

You save: RD$1,777.95

DA9,051.73

List Price: DA12,931.59

You save: DA3,879.86

FJ$156.47

List Price: FJ$223.54

You save: FJ$67.07

Q538.10

List Price: Q768.75

You save: Q230.64

GY$14,640.92

List Price: GY$20,916.50

You save: GY$6,275.58

ISK kr8,463.19

List Price: ISK kr12,090.79

You save: ISK kr3,627.60

DH627.45

List Price: DH896.39

You save: DH268.94

L1,178.68

List Price: L1,683.90

You save: L505.22

ден3,652.19

List Price: ден5,217.64

You save: ден1,565.44

MOP$565.84

List Price: MOP$808.38

You save: MOP$242.54

N$1,233.07

List Price: N$1,761.61

You save: N$528.53

C$2,568.36

List Price: C$3,669.24

You save: C$1,100.88

रु9,567.91

List Price: रु13,669.04

You save: रु4,101.12

S/248.59

List Price: S/355.14

You save: S/106.55

K288.79

List Price: K412.57

You save: K123.78

SAR262.48

List Price: SAR374.99

You save: SAR112.50

ZK1,677.10

List Price: ZK2,395.96

You save: ZK718.86

L300.33

List Price: L429.06

You save: L128.73

Kč1,463.27

List Price: Kč2,090.48

You save: Kč627.20

Ft23,699.61

List Price: Ft33,858.04

You save: Ft10,158.42

SEK kr667.81

List Price: SEK kr954.05

You save: SEK kr286.24

ARS$84,407.94

List Price: ARS$120,587.94

You save: ARS$36,180

Bs483.55

List Price: Bs690.81

You save: Bs207.26

COP$281,115.69

List Price: COP$401,611.06

You save: COP$120,495.37

₡35,263.43

List Price: ₡50,378.49

You save: ₡15,115.06

L1,824.26

List Price: L2,606.20

You save: L781.93

₲558,200.87

List Price: ₲797,463.99

You save: ₲239,263.12

$U2,761.75

List Price: $U3,945.53

You save: $U1,183.77

zł252.59

List Price: zł360.85

You save: zł108.26

Already have an account? Log In

Advanced Excel VBA Crash Course

"With…End With" - Example 2

Sign Up

Sign Up

Share