Everyday, Practical Programming with Excel VBA

Create Amazing Programs that Make Excel Work For YOU
4.5 (1 rating) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
34 students enrolled
$19
$20
5% off
Take This Course
  • Lectures 134
  • Length 4 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 4/2016 English

Course Description

You purchase a computer. You keep your software up to date.  Is it about time the computer did something for You? You can have the computer complete hours of work with 0 errors. You can really impress in your job by programming solutions.

In the first two weeks of my last job I attended a meeting about a very important project. This meeting covered the 160-hour first phase of a project. I completed that first phase in 16 hours (two days) using VBA programming. This course covers the techniques I used to create programming like that. In fact, the last example is exactly the problem solved by that programming. This course moves quickly from coverage of the building blocks of programming to documenting and then creating solutions.

Have you ever taken a programming class and then you don't know how to use what you've learned? I frequently work with technical people who took college programming courses that won't volunteer to create a programming solution. They have the knowledge, but have not found real-world programs to work on to increase their confidence. Do you want to see what real, practical programs look like, learn how they work and learn how to think through the analysis, design and development that takes you from beginning to end? This course uses really great examples so whether you have taken one or more programming classes or have no programming experience or training at all you can be prepared to create useful programs that can impress, speed through work and expand your career.

Real-World programming does not start with coding, but with analysis of the task. This course begins at that step. When you have analyzed the problem and understand the building blocks of the programming language, then you can begin coding effectively.

This course includes four hours of video and uses nine, real-world examples to illustrate the creation of programming solutions in Excel® VBA to requests frequently encountered in an IT environment. 

This course includes the following examples: 

Automating the inserting of rows, creation of formulas and application of formatting to make a report, summing up lines of data within groups

Reordering Mailing Labels from One Column to One Column Per Type of Address Information

Comparing two worksheets and Reporting the Differences

Dividing a File into Smaller Files

Opening a File and Finding Matching Lines to Write to a Different File

Doing a lookup of Purchase Detail in One Worksheet, Reducing Inventory Counts in Another worksheet and Presenting the Results as an Invoice in Another Worksheet

Using Active Data Objects to Gather Table Schema from SQL Server® to Programmatically  Create Stored Procedures.

The analysis of a problem consists of asking the right questions and documenting the decision points. This course uses a Free, Flowcharting Program to document the proposed programming solution. This is helpful for confirming programming requirements, determining program flow and identifying initial variables and constants.

What are the requirements?

  • Students will need to have a version of Microsoft® Excel® installed. The videos capture the use of version 2010 on Windows 7 Professional, but there are only small differences between several of the Excel® versions. Many of the programs I initially created using Excel 2003 or earlier versions of Excel. During the course, students will need to download and install Free Flow-charting software called YeD and also download and install the Free software Microsoft® SQL Server® Express with Tools.

What am I going to get from this course?

  • Analyze Problems and Program Solutions in Microsoft® Excel® VBA.

What is the target audience?

  • This course is intended for students that have some Microsoft® Excel® experience and want to learn how to create a programming solution in Microsoft® Excel® VBA.

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Introduction
01:39

I created spreadsheet reporting for an engineering consulting firm. Years later I demonstrated one of the macros to a friend who worked at a bank. Open the spreadsheet, view macros, select macro, Run. 

Her jaw dropped. 

It can be very impressive to someone who has had to manually do spreadsheet work that is time consuming, tedious and requires complete accuracy to see a program complete a process in about 1 second, done perfectly.

This lecture demonstrates that program.

03:25

Not everyone in the IT field gets there through a specific college degree program, followed by an internship, followed by a hiring. There was a time when IT was staffed by people who just demonstrated they had the skills.  You bought yourself a home PC, understood the operating system and a few programs? You're our new Computer Guy! 

I went from sales to software sales to computer sales to computer consulting to my first corporate IT job. I was hired because a company was moving from mostly mainframe terminals to PC's and I had demonstrated knowledge of PC's. 

I was able to move up within the IT field in large part because I kept educating myself outside of the job and in doing consulting on the side.

This lecture discusses my background, the roadmap of my career. 

01:10

This lecture will explain a little bit about the software this course will utilize, discuss and rely upon in the programming examples in addition to Microsoft® Excel®.

Section 2: Setting Things Up
01:50

You need to protect yourself so your enthusiastic volunteering to program a solution for your job does not backfire on you. Reconsider your programming methodology. Put use of a Sandbox right near the top and keep that as a top priority.

Consider the attached pdf graphic demonstrating the safe haven a sandbox can be.

00:26

Jeanine creates a sandbox and uses that for automation, getting rewarded for her extra effort and streamlining processes.

00:24

You want to benefit from creating a programming solution and not receive trouble, instead. Watch Walter go from the excitement of creating a programming solution to the discouragement and fear of getting in trouble for losing files.

Take 24 seconds and experience the ups and downs, the highs and lows of Walter's day in the office. He came up with a great programming solution but execution was not perfect and should have figured out a way to create and test his soluton first in a sandbox.

There is also a pdf attached as a reminder of Walter's not so perfect day.

Section 3: Programming Foundation
01:33

Variables are foundational to programming and I point out something that you might not have known about variables.

01:24

This lecture demonstrates setting up variables to hold values in a program and gives some suggestions about how to setup variables.

02:02

Consider Lee and his bus route as a metaphor for nested loops. When you use nested loops, you are creating an intermediate-level program.

01:31

If you don't yet have an idea about the layout of a program this lecture covers how an intermediate-level program would be organized..

Section 4: Methodology for Creating a Programming Solution
01:48

I know of people taking a programming class and then not knowing where to begin when they find a need to create their first programs on their own. Instead of allowing students to figure out a methodology over time, this lecture steps in and provides one to get students started creating programming solutions.

01:33

This lecture breaks down the analysis part of the methodology.

00:43

This is an explanation of inputs and outputs and how those are considered when creating a programming solution.

02:51

This lecture explains the technique we use to determine the last cell to be processed in the input and defines the outer loop of many programs.

03:08

Once you learn how to create a programming solution, some problems can look like they need a programming solution that really could be solved using other techniques.

An example is given that can trip people up, getting them to think they need a programming solution when something much more straightforward can solve the problem.

02:08

Even though the lab is done in the video, the pdf with the questions is attached as a downloadable file. Some people are new to programming and identifying the way a program can understand inputs is new. This is a practice lab done for you to prepare you for thinking about data in this new way.

04:53

This lecture provides data as an example of determining the metadata about data input.

Section 5: Programming Environment
03:20

Lab 2, a pdf, is attached for you to work through this example of creating a macro and then viewing, critiquing and running the created code. Demonstrates running a macro in one workbook that is stored in a different workbook.

00:44

This lecture walks you through a lab, opening a macro and the responding to security prompts.

01:28

This lecture covers setting a location for trusted documents and the recommended security setting.

01:17

Lab 5 enables your installed version of Excel® to use the tab that has buttons for Macros.

Lab 6 is the creation of a button.

Test1.xlsm is attached here containing a macro but you would have created this file also if you did Lab 2 which goes with Lecture 13.

01:49

Download an Excel® file containing a macro that you can step through with F8 or run to the end with F5 function key.

There is a pdf attached explaining some of the things that can be done to test a macro.

This lecture demonstrates a complete and intermediate-level, though
simple program. It is still intermediate level, however, as it loops
through data.

The concept of hard coding is defined. There is also a demonstration of
viewing current values of variables as a program is running. It is explained and demonstrated how to step through a program line by line.

04:15

There is a 3-page pdf attached for Lab 7. The immediate window is introduced along with printing (showing) values in the immediate window and even executing a for-next loop in the immediate window, and setting up a variable watch.

Section 6: Downloading and Installing Free Programs
05:47

This lecture discusses operating system type and downloading the free, flow-charting software called YeD from yworks.com/YeD.Installation of the program is walked through. Suggestions are made for how to install the program and then the program is briefly demonstrated.

One lab and two other pdf files are attached.


05:05

This lecture covers downloading and installing SQL Server® Express with Tools. This program is used in the last section for programming with ADODB. We install this with both forms of authentication so we can use code for either method of connecting to SQL Server® from Excel® VBA in the last program in this course.

Section 7: Rearrange Labels Program
02:22

This lecture introduces mailing labels data, the methodology for creating a programming solution and the desired result of the mailing labels rearranged in a way that the labels can be imported to a different program.

01:17

We break down our desired result into an initial solution, counting the maximum number of rows to make it easier to create the solution we really want which is to rearrange the mailing labels. We will keep comparing a count to the max rows variable.

01:25

This lecture demonstrates relating the inner loop to the way we will move through the data by using a YeD diagram.

03:17

This lecture reviews the setup, inner loop and outer loop with YeD diagrams. The completed YeD diagram is attached. This lecture also shows the code for the program in the setup section.

00:58

This lecture discusses the outer loop.The code for this main loop is shown.

01:28

This lecture shows the completed code for this initial program solution. Msgbox is introduced, it is a way to output to the person running the code. 

04:40

This initial program is attached to this lecture. Running the program is demonstrated. There is a lab attached for you to run this program, also.

02:21

This lecture covers analysis for the rearrange program. There is an attached lab for you to begin creating a YeD diagram.

00:39

This lecture shows the answer to the prior lab and another lab is attached wherein you will create decision points in the YeD diagram. 

00:55

This lecture shows the answer to lab 11. Lab 12 is attached.

00:47

This lecture shows the answer to lab12 and lab 13 is attached.

00:11

This lecture shows the answer to lab 13 and lab 14 is attached.

00:41

This lecture begins to show the answer to Lab 14.

00:35

This lecture continues to cover the answer to lab14 and explains how to connect boxes in YeD. Lab 15 is attached.

00:50

This lecture discusses the completed YeD diagram for this program. The completed YeD diagram is attached.

01:14

This lecture discusses the setup code. Behind the scenes Excel® uses long whenever you define a variable as integer.

04:12

This lecture demonstrates and discusses the outer loop. Lab 16 is attached. Code to cut and paste the contents of a cell is introduced. In the second half of the lecture, a table is displayed showing how second address line is handled differently from the line with city, state and zip. The code also uses the comparison operator for "not equal to".

The code contains commented lines that don't run but provide information about the code.

Section 8: Schemler Windows Report Program
00:59

Schemler Windows program is introduced in this lecture. We discuss analysis as part of our methodology. Lab 17 is attached for you to consider the decision points to use in the YeD diagram for this program.

00:35

This lecture reviews the answer to Lab 17.

01:58

YeD is supposed to be pronounced like Why Ed. I pronounced it as Yed (which would rhyme with "said") at around 80 seconds in this lecture.Sorting is discussed in this lecture. Lab 18 is attached. Consider the steps needed in the setup to handle groups and sorting.

02:48

The answer to lab18 is covered. Lab 19 is attached. It is explained that you will get an error if you try to define a variable that already exists. With our diagram we move from a decision point to a loop.

02:11

This lecture covers the answers to lab19 and Lab20 is attached. What do we do when there is a change in the window type? How do we handle summing up the groups?

00:57

This lecture has the final YeD diagram attached and covers the make up of the YeD diagram for this program.

04:33

This lecture discusses the code in the setup section.  Also covered is what a generic outer loop is made up of. The second half of the lecture goes through the code that handles processing when the criteria is met. In this program the meeting of criteria means that the window type has changed. Every time there is a change we are capturing the ending row of a group and the beginning row of the next group.

01:19

Lab 21 is attached and the code is covered that happens after the last subtotal group has been reached in the code.

06:54

This lecture discusses breaking out code into a different subroutine to be called from our program (a subroutine in Excel® VBA is a program). The line extension character is mentioned and public variables are used. Scope is also mentioned along with public variables.

01:52

This lecture discusses breaking out code into a different subroutine without using public variables but passing values along to another subroutine.

Section 9: Functions, File Commands
03:24

This lecture introduces functions and covers code that uses save as to change the name of a workbook. The file types of xls and xlsm are discussed. Code that turns off application alerts is used. A function is called using msgbox.

01:28

Other ways in code to rename a file and copy a file are covered. Also, there is code to open a workbook and check to see if a file exists.

04:12

This lecture discusses the use of Excel® functions, including isna and text functions instr, mid, left and right. Using absolute cell references is demonstrated so a formula can be copied down while referring to the same range. This lecture uses the immediate window to run Excel® functions.


Section 10: Rename files in a folder
04:11

In this lecture we create a sandbox to program against.  A zip file containing all the files is attached if you do not want to create all the files manually. The way files sort is discussed. The desired result for the program is explained.

04:02

This lecture explains the code, having two loops within an outer loop. Arrays are used. Len function (length) is used. We use DIR to loop through the files in a folder. GetAttr is used to get file attributes and bitwise operator And is used to pull a particular attribute from the results. Binary and decimal systems are discussed. System variable vbDirectory is used. It has a value of 16.

02:25

Processing the array containing filenames. Function IsNumeric is introduced and used. Lower and upper bounds of arrays are explained.

Section 11: Break up a file into smaller files
01:18

This lecture introduces the concept of file I/O (Input/Output).

01:44

This lecture begins with an analysis of the problem of wanting to break up an Excel® file that is too large for Excel® to open. Excel® used to be limited to 65,000+ rows but later versions can handle more rows. We still may be presented with a file containing more rows than even the latest versions of Excel® can open. We introduce the program that will break up one large file into smaller files of whatever number of rows we designate. In this program we choose 65000.

There is a lab attached to begin the YeD diagram for this program.

00:51

Lab2 answer is revealed. Lab3 is attached. Consider the tasks needed before the outer loop.

01:08

The answer to lab 3 is covered. The pdf file contains a lab for extending the YeD diagram with tasks needed when lineNumber is greater than 65000.

00:45

The answer to lab4 is covered. The way tasks are described can be helpful for seeing where a task can be reused on a loop.The completed YeD diagram is attached both as a diagram file and a pdf view of that file.

01:27

Error handling is an important part of programming of any complexity. It makes a program more robust. 

04:54

This lecture discusses the setup code. Freefile command is used to give the number to use for the next file to be opened. The command to open a file and to open a file to output are both used. The command to read in a line from a file is used. Writing out a line to a file is also used. Print command has no double quotes around a line but write puts double quotes around a line written to a file. 

Mod function is discussed. Mod gives the remainder when you divide one integer by another.

We use a do-while loop with EOF (end of file), so the code runs until the end of the input file is reached.

01:25

After watching the lecture, download the attached pdf for an assignment to run the program. We can add error handling with 5 lines of code. We close out the files. A boolean variable is used (true or false). The Excel® file containing the program to break a file into smaller files is attached. Lab 5 to run the program is also attached.

02:10

Lab 6 to run code to take apart a file, only writing out matching lines, is attached to this lecture. The takeApart.xlsm Excel® file is also attached. 


02:24

Place the file resultsNoCarriageReturns.txt in c:\ExcelVBA\takeApart\save. Input function is used which can read any number of characters.

Section 12: Windows Registry, API, Excel References, FileSystemObject
01:22

The registry can be a better place to hold some information than a file but is more permanent than just holding a value in memory. We create a function but learn how to call it in the next lecture. Be VERY careful with the registry or you might be unable to start your computer.

02:21

We call a function to write to and read from the registry. We use the immediate window and the registry to verify we have written to the registry. Be VERY careful with the registry or you might be unable to start your computer.

01:28

The attached xlsm file contains the VBA discussed in this and the prior two lectures.

04:21

This lab also asks you to add the reference for ADO (Active Data Objects) used in the final program. This lecture discusses early binding and late binding and intellisense.

00:51

This lecture is an introduction to API calls, an advanced topic.

02:36

The program demonstrated in this lecture is attached to this lecture. Sample files to use for the program are also attached in a zip file. User-defined variable types are used. 64bit operating system has a different variable type, longlong which can contain a very large integer number.

01:38

This lecture continues analysis of the use of the API call.

04:20

The Excel file for this and the sample files are attached to the Lecture "API Function Call Example, User-Defined Types". FileSystemObject is used to check for the existence of a file and delete a file if it does exist. DIR is used to loop through a folder. And is used within an if statement to check for more than one thing. FileSystemObject is also used to write the entire contents of a variable to a file. This is a more object-oriented way to deal with files.

Lab 8 is attached.

03:06

This lecture covers a subroutine, StitchFilesInUNCDirectory, included in the same Excel file downloaded in lecture, "API Function Call Example, User-Defined Types".

02:11

This lecture will use the api call to look up a file name and open that workbook selected using the API call to bring up a file selection dialog box.

A button is created in the workbook to allow us to run the code.

Section 13: Compare Two Worksheets
01:10

This lecture introduces the program we will be covering in this section. Lecture 88 has the 3 Excel Files and the Lab for this program.

01:22

We need to verify the comparison will be helpful. Adding a row to one of the worksheets will make all the cells different.

01:03

This lecture discusses the analysis of how to create a program like this. Lab 9 is attached to have you create the decision points.

00:58

This lecture reviews the answer for lab 9 and lab 10 is attached.

00:50

This lecture reviews the answer for lab 10. 

01:01

Lab 11 is attached.

01:13

The answer to lab 11 is covered in this lecture. Lab 12 is attached.

00:49

Lab 13 is attached. Lab 12 answer is reviewed in this lecture.

01:03

The answer to the Lab 13 is covered in this lecture.  The final YeD diagram is attached.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Jay Murphy, Professional Trainer, Excel Consultant, Database Admin

I attended the University of Wisconsin-Madison and took programming and accounting courses while changing majors from English to Information Systems to International Relations. I taught courses such as Microsoft® Excel® to the advanced level and programmed reports for a civil engineering consulting company. I developed in Microsoft® Access an invoicing, contact management, label-making and marketing program which I packaged with the Developer's Toolkit. I was the computer and network support person for a law firm where I created a Linux firewall/fileserver. I took over a helpdesk database, creating charting and writing all the vba code to make it a complete helpdesk application. Because of my database development and Linux/Unix experience, I was given the opportunity to be trained to be a Database Administrator. I've been working as a Database Administrator for over 10 years at very large corporations in financial services and healthcare companies. As a Database Administrator, I have programmed in TSQL, made several advanced SSIS packages and programmed nested loops in Powershell. During this time I also have created for myself over 100 web pages that present and manage data connected to a secure, remotely-hosted Microsoft® SQL Server® database.

When I had just started a job as a Database Administrator, I was introduced to the 160-hour first phase of a project to create nearly 200 stored procedures with column names numbering up to 250 from tables in SQL Server®. Using the techniques I teach in the Excel® VBA course, I completed that phase of the project in under 2 days or less than 16 hours.

Ready to start learning?
Take This Course