Mastering Excel Vba For The Real World

Embrace the Magic of Excel VBA Automation

What you'll learn

How to improve your productivity by automating repetitive tasks in a single click.

How to Debug a code to understand what a code does, fix possible issues and make sure your code does exactly what you need it to do.

How use Excel VBA Dictionary

How to use ArrayList in Excel VBA with examples.

How to programmatically create Pivot Tables and Pivot Charts in Excel VBA.

How to use loops in Excel VBA (While Loop, do While Loop, For Loop and For Each Loop).

How to make decisions using the If statement (else, ElseIf, nested if statements), and case statements (Case else, nested case statements).

How to use Excel VBA user Defined Functions to avoid writing too much repetitive code.

How to control Word from Excel (Automate MS Word from Excel using Excel VBA)

How to use the font object.

How to use dynamic code.

How to work with csv files.

How to work with multiple workbooks

Requirements

Basic Excel Knowledge

Basic Excel VBA Knowledge

Desire to learn the Magic of Excel VBA Automation

Description

By taking this course on Udemy, you will gain knowledge on the following: How to: 1. Create dynamic code in your automations. 2. Become more efficient and get more time to focus on other assignments as you will be able to execute complex tasks in a single click. 3. Create Pivot Tables programmatically. 4. Create Pivot Charts programmatically. 5. Use methods such as: Replace, Sort, Remove Duplicates, AutoFilter and Find in Excel VBA to dynamically find columns names, rather than fixed column positions in a data set. 6. Easily Automate Microsoft Word from Excel and Integrate your Excel data into your Word Automation. 7. Use Excel VBA Dictionaries 8. Use ArrayLists to improve the efficiency of your codes. 9. Use If statements, Nested If statements, Else and ElseIf Statement in Excel VBA. 10. Use case statements and nested case statements.11. Use Excel VBA loops, including: While Loop, Do While Loop, For Next Loop and For Each Loop. 12. Use advanced decision making logics along with Excel VBA Loops to create advanced and flexible codes in Excel VBA. 13. Master the debugging process of an Excel VBA application to understand what your code does and how it does it.The List above contains just some of the many benefits and advantages that Excel VBA brings to the table.

Overview

Section 1: Introduction

Lecture 1 Introduction

Section 2: Getting Familiar with the VBA Editor

Lecture 2 Adding the Developer Tab

Lecture 3 Main Parts of the VBA Editor

Lecture 4 Adding the Edit Toolbar

Section 3: Using the Macro Recorder Functionality

Lecture 5 Macros, Macro Security

Lecture 6 Recording Macros Part I

Lecture 7 Recording Macros Part II

Lecture 8 Editing Macros

Lecture 9 Programming in VBA vs Recording Macros

Section 4: Building solid ground in Excel VBA

Lecture 10 Excel VBA Object Model

Lecture 11 Workbooks, Worksheets and Sheets Collections

Lecture 12 Workbooks Coding Lesson

Lecture 13 Sheets, Worksheets Coding Lesson

Lecture 14 Range Object Coding Lesson

Lecture 15 Columns, Rows and Cells Properties Coding Lesson

Section 5: Learning about VBA Operators

Lecture 16 Arithmetic Operators

Lecture 17 Comparison Operators

Lecture 18 Logical Operators

Lecture 19 Concatenation Operators

Section 6: Data Types in VBA and Variables

Lecture 20 Excel VBA Data Types

Lecture 21 Variables Coding Lesson

Lecture 22 Excel VBA Constants

Lecture 23 Option Explicit in VBA

Section 7: Font Object

Lecture 24 Font Object Coding Lesson

Section 8: With End With Statement

Lecture 25 With End With Statement

Section 9: Set Statement

Lecture 26 Set Statement

Section 10: Excel VBA Built-In Functions

Lecture 27 Format Function

Lecture 28 String Functions Part I

Lecture 29 String Functions Part II

Lecture 30 Date and Time functions Part I

Lecture 31 Date and Time functions Part II

Lecture 32 Excel VBA Round Function

Section 11: WorksheetFunction Object

Lecture 33 WorksheetFunction

Section 12: User Defined Functions

Lecture 34 User Defined Functions

Lecture 35 The Call Statement - Calling Functions and/or Sub-Procedures

Section 13: Protecting your Macros

Lecture 36 Protecting your Macros

Section 14: Excel VBA Key Methods

Lecture 37 Range.Replace Method

Lecture 38 Range.Sort Method

Lecture 39 Range.Remove Duplicates Method

Lecture 40 Range.AutoFilter Method

Lecture 41 Range.Find Method

Section 15: Conditional Statements for Decision Making

Lecture 42 If Statement

Lecture 43 Nested If Statements

Lecture 44 Select Case Statement

Lecture 45 Nested Select Case Statements

Section 16: Excel VBA Loops

Lecture 46 Do While Loop

Lecture 47 Do Until Loop

Lecture 48 For Next Loop Part I

Lecture 49 For Next Loop Part II

Lecture 50 For Each Loop

Section 17: Automating Pivot Tables and Pivot Charts dynamically in VBA

Lecture 51 Automate Pivot Tables in VBA

Lecture 52 Automate Pivot Charts

Section 18: ArrayLists in Excel VBA

Lecture 53 VBA ArrayList Part I

Lecture 54 VBA ArrayList Part II

Lecture 55 VBA ArrayList Part III

Section 19: Excel VBA Dictionaries

Lecture 56 VBA Dictionaries Part I

Lecture 57 VBA Dictionaries Part II

Lecture 58 VBA Dictionaries Part III

Section 20: Automating Microsoft Word from Excel

Lecture 59 Microsoft Word Automation from Excel Part I

Lecture 60 Microsoft Word Automation from Excel Part II

Section 21: Working with csv Files in VBA

Lecture 61 Extract Data from another Workbook and save file as csv File

Lecture 62 Reading from csv File and convert it to a different Excel Extension

Section 22: Final HR File

Lecture 63 Final File

Section 23: Error Handling and Err Object in VBA

Lecture 64 Error Handling

Lecture 65 Err Object and Error Handling

Business Analysts,Reporting Analysts,Data Analyst,Any Excel user who wants to start automating complex and repetitive tasks