Apply Excel VBA In Industry

In this lecture, I will present a business case where you as the VBA developer can save up to 97% of your time by automating tasks in Excel.

STOP working for Excel, make it work for you!

Organizing data into tables and creating borderlines, colors, etc is NOT value-added work. Invest your time ANALYZING what the data is trying to tell you.

Let's go over what exactly is VBA in Excel by introducing a beginner example of how code in the background can be written to execute or perform actions on the front end for the user to see.

In this lecture, I present how to add the "Developer" tab into Excel. This is where you will be visiting a lot throughout the duration of this course in order to write code.

The steps to do so is the following:

1. Click on File

2. Click on Options

3. Click on Customize Ribbon

4. Click on the Checkbox that says "Developer"...Voala! ….. You're Done!

In this lecture, you are going to code in VBA a line that gives inserts the sentence "I'm going to Master This".

In this lecture, we will go over how to copy and paste cells in Excel with VBA Code in a couple of different methods.

In this lecture, we'll go over how to add, subtract, multiply and divide with VBA code. Fun! :)

In this lecture, we will explore a key component of Excel VBA, the MsgBox; pronounced (message-box). It's used to display information to the user.

In this lecture, we will use the input box feature of Excel VBA to ask the user for their name, Year of Birth, and their favorite sports team!

In this lecture we will go over the concept of variables and how to use them through the "Dim" feature in VBA, short for Dimension. We will do so through a brief exercise.

Final lecture of Part I - Excel VBA basics: congrats on the progress you've made so far! - Here's a virtual High-5 ^_^

In this lecture we're going to combine all the concepts we've learned in part 1 in one Macro - to prep you for the assignment coming up ahead.

The "For" loop is perhaps one of the most used lines in VBA, and we'll go through an exercise of adding values in two columns using the "For" statement.

In this lecture we'll use the "IF" statement, along with the "For" statement from the previous lecture, to either add or multiply values in two columns.

In this lecture, I want to start applying real-life examples.

We're going to loop through an accounts receivable ledger assign a customer class based on the number of days their bill is overdue. This will assist in maximizing the dollar value returned by prioritizing which outstanding bills to tackle first.

Let's Go!

You're the trust-worthy industrial engineer responsible to make more for less and drive down costs. You're tasked with creating an Excel Macro that calculates the following for every SKU (Stock Keeping unit):

1. Cycle time for each SKU made

2. Number of Units Made

3. Average Cycle Time for each SKU type

4. The longest cycle time for each SKU type.

Let's go!

There's a production challenge currently going on due to an unexpected increase in demand. Pressure is on!

Every day, we're going to put together a production report showing output of the production lines.

We need to display production numbers for every day of the month, highlight the days where the target was met or exceeded Green, and the days when the target wasn't met with red. (We don't want a lot of those!)

Let's do it!

We're 3 months out from the end of our fiscal year. With large amounts of money owed to us in accounts receivable we'd like to collect. You've been given a directive to develop a strategy to maximize on the amount of cash collected. You two advantages by your side: 1. You have A LOT of historical data; 2. You have a VBA program to help you.....Let's see what the strategy will look like.

You're captain crunch, saving every dollar is a must! Below are some inefficiencies in the current process:

1. Are we getting the best deal? Rumor has it that we might be getting the same SKU for a cheaper price at another vendor. Let's go Deal hunting!

With a lot of SKUs and a high volume of orders - VBA is a must to save the most amount of money possible!

You're the HR manager for the city, and at the close of every month you need to report the total headcount by department to the financial controller to consolidate fiscal costs.

You used to do this manually, that was before you joined this class.

Now we're gonna up your game a little, do this with VBA!

Payroll!

No one wants less money on their cheque!

And using VBA we're going to make sure of that. Let's dig in!

Solution Video to Assignment 6 / Lecture 21. Enjoy!

We conclude with a presentation talking about when to use VBA, but also when NOT to use VBA.

In this lecture, we'll look at the 'record a macro' feature in excel together. Let's do it!

NESTED Loops, Arrays, SQL data automation, Pivot Tables, and more complex industry problems, and that is just the beginning!