Everyday, Practical Programming with Excel VBA
3.7 (3 ratings)
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.
48 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Everyday, Practical Programming with Excel VBA to your Wishlist.

Add to Wishlist

Everyday, Practical Programming with Excel VBA

Create Amazing Programs that Make Excel Work For YOU
3.7 (3 ratings)
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.
48 students enrolled
Created by Jay Murphy
Last updated 11/2016
English
Current price: $10 Original price: $20 Discount: 50% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 4 hours on-demand video
  • 81 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Analyze Problems and Program Solutions in Microsoft® Excel® VBA.
View Curriculum
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.
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.

Who 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.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
134 Lectures
04:12:20
+
Introduction
3 Lectures 06:14

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.

Preview 01:39

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. 

My Background
03:25

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®.

Preview 01:10
+
Setting Things Up
3 Lectures 02:40

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.

Create a Sandbox, then Start Coding
01:50

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

Preview 00:26

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.

Preview 00:24
+
Programming Foundation
4 Lectures 06:30

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

What are Variables?
01:33

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

How are Variables Used?
01:24

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

Preview 02:02

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..

The general format of a VBA program
01:31
+
Methodology for Creating a Programming Solution
7 Lectures 17:04

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.

Methodology
01:48

This lecture breaks down the analysis part of the methodology.

Analyze a Problem To Be Solved With a Programming Solution
01:33

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

Determine Inputs and Outputs
00:43

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.

Methodology Last Row Last Column
02:51

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.

Does It Make Sense To Create a Program At All?
03: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.

Metadata (Data about your Data)
02:08

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

Analysis Using Schemler Windows Data
04:53
+
Programming Environment
6 Lectures 12:53

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.

Create First Macro with Macro Recorder
03:20

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

Open a Macro and Security
00:44

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

Trust Settings
01:28

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.

Create a Macro Button
01:17

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.

Schemler Windows Row by Row
01:49

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.

More Row by Row, Immediate Window and Watches
04:15
+
Downloading and Installing Free Programs
2 Lectures 10:52

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.


Download and Install YeD with Overview
05:47

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.

Download and Install SQL Server® 2014 Tools and Database Engine
05:05
+
Rearrange Labels Program
17 Lectures 27:52

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.

Mailing Labels Rearrange Analysis
02:22

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.

Mailing Labels Max Rows Analysis
01:17

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

Inner Loop for Mailing Labels Max Rows
01:25

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.

Mailing Labels Max Rows Looking at Inner Loop For Setup
03:17

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

Mailing Labels Max Rows OuterLoop
00:58

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. 

Mailing Labels Max Rows Processing and Final Code
01:28

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.

Run Mailing Labels Max Rows
04:40

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

Build YeD Setup for Mailing Labels Rearrange
02:21

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

Lab 10 Answer and Lab 11 PDF Attachment
00:39

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

Lab 11 Answer Decision Points and Lab 12 PDF Attachment
00:55

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

Lab 12 Answer and Lab 13 PDF Attachment
00:47

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

Lab 13 Answer and Lab 14 PDF Attachment
00:11

This lecture begins to show the answer to Lab 14.

Lab 14 Answer
00:41

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

Lab 14 Answer Continued and Lab 15 PDF Attachment
00:35

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

Lab 15 Final Answer and PDF with Final YeD for this program
00:50

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

Mailing Labels Rearrange Code Setup
01:14

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.

Mailing Labels Processing and Lab 16 PDF Attachment
04:12
+
Schemler Windows Report Program
10 Lectures 24:06

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.

Analysis and Lab 17 PDF
00:59

This lecture reviews the answer to Lab 17.

YeD Decision Points Review Lab 17
00:35

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.

YeD Setup, Lab 18 PDF Attached
01:58

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.

Review Lab 18, Lab 19 PDF
02:48

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?

Review Lab 19 Processing Inner Loop YeD, Lab 20 PDF
02:11

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

Final YeD and Lab 20 YeD After Loop Cleanup
00:57

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.

Coding Setup and Inner Loop
04:33

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

Code After Outer Loop, Lab 21 PDF Attached
01:19

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.

Preview 06:54

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

Passing Values to Called Subroutines
01:52
+
Functions, File Commands
3 Lectures 09:04

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.

Functions
03:24

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.

File Commands
01:28

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.


Internal Functions (Text Functions)
04:12
+
Rename files in a folder
3 Lectures 10:38

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.

Sandbox For Rename Files in a Folder
04:11

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.

Loop thru files
04:02

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

Loop thru Array of Filenames, Process
02:25
5 More Sections
About the Instructor
Jay Murphy
3.7 Average rating
3 Reviews
48 Students
1 Course
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.