VBA PO Generator: Homework Solution Part 1

The following is a guest video lesson provided by Chris H. (reddit: Kristofmic).

In this video series we’ll follow along with solutions to the advanced homework for the VBA Purchase Order Generator Walkthrough.

In Part 1 of the homework solution, we develop enhancements to the purchase order worksheet enabling users to input as many entries into the purchase order as they would like, as well as dynamically selecting and removing items that have been added to the purchase order.

You will learn how to do the following with Visual Basic (VBA) in Excel:
  • Inserting and deleting rows & columns using the .EntireRow / .EntireColumn and .insert / .delete functions
  • Expanding a selection of cells using the .resize() function
  • Moving to various cells in the worksheet using the .offset() function
  • Copying and pasting values and formatting using the .copy and .pastespecial functions
  • Additional Usage of For…Next loops

Here is the updated Purchase Order Generator file to follow along with the lesson.

Video Lesson: Absolute & Relative Referencing

Absolute and Relative Cell Referencing

This video tutorial focuses on Absolute vs. Relative cell referencing within Excel.  You likely know it as the dollar sign ($) used in functions/formulas (for example, C5 vs $C$5).

There is also a quick demonstration of the video using a VLOOKUP formula.  The VLOOKUP formula is explained in more detail in these two lessons here and here, but the example in this video gives a basic understanding of how it works in Excel.

Update 5-22: I’ve noticed some users (especially those with Macs) have had difficulty viewing this video.  Since it is one of the older videos, I will be re-recording it and adding information so that it is comparable to more recent videos.

If you’re following the Lesson Guide, next up is Math & Statistical Functions (e.g. SUM, AVERAGE)

Excel Training Lesson Plan Posted

I just updated the site with a proposed lesson plan to give everyone an idea of the topics that I plan on covering throughout the course.

I may jump around between topics depending on what makes more sense and what people are interested in, but I will update the Lesson Plan page to reflect any new posts.

Welcome to Excel Exposure!

I’ve created this site as part of the University of Reddit course (this information is posted here as well). The goal is to expose the world to the amazing functionality of Microsoft Excel. I hope to make this site an easy reference for Excel users of all skill levels so that they can refine their skills, become more efficient, and get the most out of this amazing piece of software.

Excel Exposure Training Course – General Overview

This course will be a complete lesson / teaching tool related to Microsoft Excel. It will have sections and information applicable to all experience levels. It will be updated regularly with additional lessons, tips, and demonstrations of how you can get the most out of Excel.

This course will be taught using Microsoft Excel 2010, but most of the functionality can be found in Excel 2007/2003 (although it may be in a different location). I will not be focusing on the Mac version of the product as I do not have it.

Prerequisites

General knowledge of Microsoft Excel is recommended, but there will be sections of the site for complete beginners.

These will likely be updated last because the main purpose is to improve the knowledge-base of those already familiar with Excel. I will try to make sure it is clear what the experience level should be for each lesson.

Syllabus

The course will be hosted on this website.  I will be posting a new video lesson from the Lesson Plan weekly on Sunday nights (as well as quick tips and other informative links sporadically throughout the course).

The general outline of the syllabus will evolve as the lessons are created/posted.  Always check the Lesson Plans for updates to the course schedule.

Teacher qualifications

Masters of Business Administration (MBA) with concentration in Accounting

Bachelor’s Degree in Accounting

Over 8 years of experience pushing Excel to the limit and improving my abilities.

Questions?

Feel free to reach out to me with any questions, comments or feedback:  ben@excelexposure.com