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.

VBA Walkthrough #2: Message Encoder & Decoder

This VBA macro is hopefully a bit more interesting than the last, and shows how I created a message encryptor/decryptor in Excel using VBA/macros.

The lesson covers these specific areas of VBA usage:

– For…Next loop (including nested loops)

– If…ElseIf…Then Statement

– Excel functions: CHAR() / LEN() / VLOOKUP()

– VBA functions: Mod / Mid / Asc / Chr

Download the Message Encryptor & Decryptor here to follow along with the video lesson.

VBA Walkthrough #1: Purchase Order Generator

VBA Macro Walkthrough

One of the best ways of learning VBA is to take a look at completely functioning macros & code to see how others approach a problem.  In this video lesson, I walk you through a Purchase Order Generator that I created using Excel and VBA.  Throughout the video, the following topics are discussed:

– How to create unique userforms which allow users to input information in a separate window.

– Usage of VBA userform toolbox items such as Label, ListBox, TextBox, and Buttons.

– Coding related to specific events (when an item in a list is chosen, or a button is clicked).

– Declaring variables, and using worksheet functions in VBA.

– Populating Purchase Order sheet with relevant product information based on user’s selections.

Download the Purchase Order Generator workbook to follow along.

Also, this is the first video lesson with HOMEWORK!!  Please feel free to complete the assignments in the worksheet and send to ben@excelexposure.com for feedback.

 

See the Guest Video Lesson of the Homework Solution here!

Video Lesson: Introduction to Macros & VBA

Using macros in Excel requires knowledge of Visual Basic for Applications (VBA).  In this video lesson you will get an introduction to how macros work within Excel and how to build them.  The video lesson includes the following topics:

– Setting up Excel workbooks to use Macros & VBA (filetype, menu and security settings).

– How to record a macro and edit recorded VBA code.

– How to write custom macro including InputBox and MsgBox functions.

– How to assign buttons and shortcuts to macros.

Make sure to download the Visual Basic Examples workbook here to follow along.