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.
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.
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.
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.
Jeanine creates a sandbox and uses that for automation, getting rewarded for her extra effort and streamlining processes.
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.
Variables are foundational to programming and I point out something that you might not have known about variables.
This lecture demonstrates setting up variables to hold values in a program and gives some suggestions about how to setup variables.
Consider Lee and his bus route as a metaphor for nested loops. When you use nested loops, you are creating an intermediate-level program.
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..
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.
This lecture breaks down the analysis part of the methodology.
This is an explanation of inputs and outputs and how those are considered when creating a programming solution.
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.
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.
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.
This lecture provides data as an example of determining the metadata about data input.
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.
This lecture walks you through a lab, opening a macro and the responding to security prompts.
This lecture covers setting a location for trusted documents and the recommended security setting.
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.
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
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.
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.
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.
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.
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.
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.
This lecture demonstrates relating the inner loop to the way we will move through the data by using a YeD diagram.
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.
This lecture discusses the outer loop.The code for this main loop is shown.
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.
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.
This lecture covers analysis for the rearrange program. There is an attached lab for you to begin creating a YeD diagram.
This lecture shows the answer to the prior lab and another lab is attached wherein you will create decision points in the YeD diagram.
This lecture shows the answer to lab 11. Lab 12 is attached.
This lecture shows the answer to lab12 and lab 13 is attached.
This lecture shows the answer to lab 13 and lab 14 is attached.
This lecture begins to show the answer to Lab 14.
This lecture continues to cover the answer to lab14 and explains how to connect boxes in YeD. Lab 15 is attached.
This lecture discusses the completed YeD diagram for this program. The completed YeD diagram is attached.
This lecture discusses the setup code. Behind the scenes Excel® uses long whenever you define a variable as integer.
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.
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.
This lecture reviews the answer to Lab 17.
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.
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.
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?
This lecture has the final YeD diagram attached and covers the make up of the YeD diagram for this program.
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.
Lab 21 is attached and the code is covered that happens after the last subtotal group has been reached in the code.
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.
This lecture discusses breaking out code into a different subroutine without using public variables but passing values along to another subroutine.
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.
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.
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.
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.
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.
Processing the array containing filenames. Function IsNumeric is introduced and used. Lower and upper bounds of arrays are explained.
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.