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: Information Functions in Excel

In the following lesson I go through the Experience Level 1 Information Functions.  These functions are used to obtain information about formatting & referencing about specific cell or range references.  In this lesson I review the following functions:

ISBLANK – ISERROR – ISTEXT – ISNONTEXT – ISNUMBER

as well as the following info_types of the CELL() function:

“address” – “row” – “col” – “contents” – “width” – “filename”

“color” – “prefix” – “protect” – “type” – “format”

The CELL(“format”) info_type has many code results, so I’ve summarized them here:

Make sure to download the latest Master Workbook to follow along with the video lesson.

Video Lesson: Basic Excel Formatting Techniques

Today’s video lesson goes through some of the basic formatting techniques in Excel 2010:

– How to Use the Auto-Fill Drag Handle / Fill Series Options

– How to Use the Format Painter

– Freeze / Unfreeze Panes

– Sheet Formatting (Coloring Tabs / Renaming)

– Print Area Setup / Print Options

Download the latest Master Workbook to follow along with the video!  The sheet titled “8 – Autofill” in the Master Workbook is what is used in the lesson example:

Now that you’ve got the basic formatting techniques down, why not learn about proper Cell Referencing.

Video Lesson: Advanced Pivot Tables – Round 1

Pivot Tables are extremely useful in Excel.  This Advanced Excel video lesson is a follow-up to the Introduction to Pivot Tables and covers the first round of advanced topics.

The lesson goes over some additional Pivot Table options and features including:

– Drill-down functionality which creates a summary sheet of any Pivot Table amount selected.

– Pivot Table Options including show/hide totals, error/empty cell display options, and refresh when opening workbook (and more).

– Value calculation options including sum/count/average, % of grand total, and % of parent row / parent column total.

– Formatting / display options including row/column label layout, sorting labels, and other formatting.

As always, download the latest version of the Master Workbook to follow along at home.

Feel like you’re starting to master the Pivot Table?  Move on to Advanced Pivot Tables – Round 2 to continue learning more!

Video Lesson: Formula Auditing & Evaluation

Formula Auditing and Formula Evaluation are powerful components in Excel, and today’s video lesson goes over the following uses of the Formula Auditing features:

Show Formulas:  How to change view to show formulas instead of cell results.

Formula Evaluation:  See how Excel views the formula as you walk your way through the calculations.

Trace Precedents / Trace Dependents:  Helps finding out where your data is coming from and/or going to.

Watch Window:  Allows for user to set certain cells into a window that can be viewed as you switched sheets.

Make sure to download the most recent Master Workbook to follow along.

Excel Video Lesson: Data Validation

This Excel video lesson walks you through how to set up Data Validation rules.  The main purpose of Data Validation is to set up rules so that only certain types of information can be entered into cells (which helps to reduce manual entry error).

Make sure to download the most recent Master Workbook to follow along.

Let me know if you have any questions!

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