A Gentle Introduction To Advanced Excel Techniques and VBA

In this Lecture we will

• Review some basic Excel skills by developing  a fictitious budget for Chris Kent

  • Excel 2016 saves as .xlsx whereas Excel 97-2003 saves as .xls

  • autofill

  • row and column sums

  • replication

  • user created formulas

  • formatting

  • conditional formatting (format only cells that contain... )

  • if conditions

  • min,max

  • count, countif,sumif

  • average, round

  • non-adjacent calculations

  • range

  • relative and absolute addressing

  • sorting

• Provide you with practice exercises to refresh your skills

In this Lecture we will

• Continue to review and extend our Excel skills by creating a compound interest table for a fictitious initial investment and interest rate (int2Final.xlsx)

• Look at how to use the PMT, FV, and PV functions used in amortization payment calculations

• Provide you with a practice exercise and notes to refresh your skills (Car Loan Problem)

In this Lecture we will

• Learn how to properly document a spreadsheet (moving from int2Plain.xlsx -> int2Enhanced.xlsx)

  • Title section

  • Description

  • Table of Contents

    • Initializations (input)

    • Calculations (process)

    • Summary (output)

• Illustrate a number of ways to improve the appearance of a spreadsheet

  • AutoFormat ... NOT "Format as a Table" ...must be added to the Ribbon Toolbar

    • right click toolbar

    • choose customize ribbon

    • choose commands not in ribbon... then choose AutoFormat (must add it to a new group)

  • custom formatting

  • merging cells

• Learn how to freeze rows and columns

  • View Tab ... Freeze Panes

• Learn how to remove gridlines

  • In the Page Layout and View Tabs

• Learn how to add comments

In this Lecture we will

• Learn how to create a  workbook consisting of several worksheets (MultipleSheetsSkeleton.xlsx -> MultipleSheetsFinal.xlsx)

  • name, add, delete, move, copy

• Apply formatting across sheets (grouped sheets)

  • when you enter info in one cell it is entered on all parallel cells in the grouped sheets

• Learn two ways to reference data from another sheet

  • SheetName!A7

  • Named ranges .. select range or single cell ... then insert name ... define

    • name each sheets profits DinnerProfit, BeveragesProfit,DessertsProfit and store the total on a new sheet

• Learn how to create a Main Menu that contains hyperlinks to each of the worksheets

• Provide you with a practice exercise which implements a practical application of multiple worksheets

  • note use of conditional formatting ... data bar

In this Lecture we will

• Review some basic charting techniques... in this lecture we focus on bar charts and pie charts

• Create a bar chart comparing fast food prices (fastfood.xlsx -> fastfoodChart.xlsx)

  • first we will block the data and then choose a clustered column chart ... switching row/col

  • we will edit the chart

    • modify the title, axes,legend, labels

    • change the style of the chart

  • add descriptive text and arrows

  • demo recalculation of values and chart

• Create a pie chart indicating a breakdown of school supply inventory for a store (supplies.xlsx -> suppliesPieChart.xlsx)

  • demonstrate the concept of exploding a slice of the pie chart

• Provide you with an opportunity to practice creating several charts

In this Lecture you will

• Learn to create a Combination chart and discuss why you would use it (timely.xlsx -> timelyComboChart.xlsx)

  • start off with a column type chart ... then

  • change graph type to combo

    • line for wages and column for number employed (# employed on sec axis)

    • show data labels on line chart

• Learn to create a chart using non-adjacent data (babytracker.xlsx -> babytrackerChart.xlsx)

  • put graph on another sheet ... now we need to select data range from "Baby Tracker" sheet (Select Data)

  • Move to Tracker sheet ... ONLY pick days and total

    • after picking days in data range use COMMA and then pick total data range

• Provide you with an opportunity to practice some of the skills learned in this lecture  by creating a Climograph for Havana Cuba.

In this Lecture we will

• Learn why you would want to use Object Linking and Embedding

  • You can use Object Linking and Embedding (OLE) to include content from other programs, such as Word or Excel.     

• Learn that  OLE is supported by many different programs, and OLE is used to make content that is created in one program available in another program. For example, you can insert an Office Word document in an Office Excel workbook. To see what types of content that you can insert, click Object in the Text group on the Insert tab. Only programs that are installed on your computer and that support OLE objects appear in the Object type box.

• Learn that There are basically three types of OLE you can perform: (OLEDemo.xlsx)

  • Embed an object in a worksheet/document … Created from a file

    o   Changes made to object are not updated in source

  • Insert a link to a file … so you now have an embedded object connected to original source

    o   Changes made to source are updated in worksheet/document

  • Create a new object from inside Excel/Word

    o   Here you are creating an entirely new object based on another program without leaving your workbook/doc. For example, if you want to add a more detailed explanation to your chart or table you can create an embedded document such as Word in Excel.

• Provide you with a number of exercises to practice OLE

In this Lecture we will

• Introduce the concept of "What-if Analysis"

  • What-If Analysis allows you to determine the optimal values for a given situation

  • For example, if you know that you can only spend a maximum of $32000 this year on new computers, you could adjust the monthly budget amount so you could spend the total amount by the end of the year and yet still remain within your departments monthly budgetary constraints.

• Introduce the technique of goal seek (On Data Ribbon... What If Analysis)

  • A method of performing what-if analysis in which the result is known, but the value of a dependent variable is unknown.

  • You basically arrive at a desired result by changing the value of another cell... you find a desired value for a cell that contains a formula

    • Process is ... Set Cell ... To Value ... By Changing Cell

  • perform a simple goal seek example using int2.xlsx -> int2GoalSeek.xlsx (Compound Interest Spreadsheet) ... setting balance to 40000 by changing principle cell

    
    

• Introduce the Solver Tool

  • A method of performing what-if analysis in which the result is known, but more than a single variable variable is unknown. In addition there may be additional constraints upon the final result.

  • Solver is similar to Goal Seek except that the features and flexibility it offers is much greater.

  • Solver can modify many more cells in attempting to find the desired solution.

  • You must add Solver to Excel (not on Data Ribbon by default)

    • File/Options/Addins/Solver Addin

  • Go through a series of scenarios

    • solver1Problem.xlsx -> solver1Solution.xlsx ... basic solution changing unit cells only

    • solver2Problem.xlsx -> solver2Solution.xlsx ... integer constraint (click add button to access constraint screen)

      • make sure to uncheck "Make Unconstrained Variables Non-Negative"

      • make sure to uncheck "Ignore Integer Constraints " in Options

    • solver3Problem.xlsx -> solver3Solution.xlsx ... non-neg constraint

    • solver4Problem.xlsx  -> solver4Solution.xlsx ... minimum value constraint

• Provide you with an exercise to practice using the Solver

In this Lecture we will

• Demonstrate how to use the "Subtotal" command from the Data Tab to summarize grouped data (subtotal.xlsx -> subtotalFinal.xlsx)

  • must sort first by key column before performing Subtotal

  • so if subtotaling by Salesperson, must sort by Salesperson first

  • if subtotaling by Month , then you must sort by Months first

• Introduce the concept of Pivot Tables (pivotintro.xlsx)

  • A rearrangeable table that allows you to analyze complex data in a variety of ways.

  • A pivot table cross tabulates data in columns and rows with the option to filter and sort the display as well as to expand on its detail

  • ... So if you must perform analyses on complex data, a PivotTable can make the whole process a lot easier. For example,if you had a database containing  lots of information, such as sales data by salesperson,product and month you can summarize it in a PivotTable.

    • With the table, you could display the totals by product for each month or rearrange the table to display totals by salesperson

    • The flexibility of the PivotTable is its greatest asset.

  • A pivot table has four elements , the filters, the columns, the rows and the values

• Create a simple pivot table for sales data (pivotdata.xlsx -> pivotdataFinal.xlsx)

  • highlight entire data including titles

  • go to Insert Tab and choose Pivot Table

  • place Pivot Table on existing worksheet just below data

  • experiment with various scenarios

    • choose only Salesperson and Amount ... then add Product

    • change Value to count .... then add month

  • Now fully utilize Field area

    • move Product to Filters

    • move Salesperson to Columns

    • move Month to Rows

    • set Value to Sum of Amount

• Provide you with an exercise to practice using  Pivot Tables

In this Lecture we will

• Work through the solution to the Pivot Table exercise from the last lecture

• Demonstrate how to create a simple Pivot Table Chart (pivotdata.xlsx -> pivotdataChart.xlsx)

  • highlight data ... then go to insert tab and choose PivotChart

  • choose all fields ... then

    • move Product to Filters

    • move Salesperson to Columns

    • move Month to Rows

    • set Value to Sum of Amount

• Provide you with a simple exercise to practice creating a Pivot Chart

In this Lecture we will

• Learn that a conditional statement takes the general form of: If a certain condition is met then X, else Y.  Sometimes these are called IF/THEN/ELSE statements.

• The basic format in Excel is: =IF(LOGICAL TEST, VALUE IF TRUE, VALUE IF FALSE)

• Take a look at a number of different ways to us the IF statement in a simple application

  • Simple  condition ... single if  (if1.xlsx)

  • Multiple conditions ... nested if (if2.xlsx)

  • Complex condition ... using AND OR commands (if3.xlsx)

    • IF (AND(cond,cond),exp,exp)

    • IF (OR(cond,cond),exp,exp)

• Look at creating more complicated Conditional Formatting expressions.

  • conditional.xlsx -> conditionalFinal.xlsx

  • choose "Use formula to determine which cells to format"

    • = AND($b4>=90,$c4>=90) ... fill background in green

    • note use of absolute addressing

• Learn how to use the CHOOSE command

  • The Excel CHOOSE function returns a value from a list using a given position or index.

  • For example, CHOOSE(2,"red","blue","green") returns "blue", since blue is the 2nd value listed after the index number. The values provided to CHOOSE can include references. 

  • Revisit the if2.xlsx demo and redo it using CHOOSE ... (choose.xlsx)

• Provide you with a number of  simple exercises to practice using the IF and CHOOSE functions

In this Lecture we will

• Discuss the solutions to  Problems 2 and 3 from the exercises from the previous Lecture

• Challenge you with two problems which implement Conditional Statements

  • The River Flow Problem

  • The Party Planner Application

In this Lecture we will

• Learn how to use the VLOOKUP function

  • VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. The "V" stands for "vertical". Lookup values must appear in the first column of the table, with lookup columns to the right. 

  • The VLOOKUP function takes the form: VLOOKUP(<value>, <range>,<column>)

    • Where <value> is a number

    • <range> is the cell range where the VLOOKUP table is stored

    • VLOOKUP finds the largest number in the first column of <range> which is less than or equal to <value> and then returns the value stored in the same row in <column> of the VLOOKUP table.

    • The value of <column> is usually 2 to indicate that the second column in the VLOOKUP table stores the value to be returned.

    • Values in the first column of a VLOOKUP table must be in ascending order

• Work through a series of examples which implement the VLOOKUP function

  • Intro examples vlookup1.xlsx and vlookup2.xlsx

    • note the use of named ranges

    • need to go back and check names  ranges ? ... goto to Formula Tab ... Name Manager

    • vlookup2.xlsx acts as a pre-cursor to our Budget Tracker spreadsheet we develop near the end of the course.

  • Sample Applications

    • Tax Table (vlookup3.xlsx)

    • Student Grades (vlookup4.xlsx)

• Provide you with a number of simple exercises to practice using the VLOOKUP Function

In this Lecture we will

• Work through the solutions to the previous Lecture exercises

• Provide you with two more advanced challenge problems which incorporate  Conditional statements and  the VLOOKUP function

In this Lecture we will

• Learn how to use the INDEX function

  • returns a value at the intersection of row and column in a block of data (array/table/chart)

  • it takes the form  INDEX ( range of cells, row,col)

  • work through a simple introductory example (index1.xlsx)

• Demonstrate how  to implement INDEX in an application that determines prices of items from an inventory of data (index2.xlsx)

  • notice the use of 3 tables

    • one table consisting of rows and columns (where are actual prices are stored)

    • two helper vertical tables which will be used to obtain the necesssary row and column pointers

    • note: the vertical lookup tables MUST be in sorted order ... note error before making correction

• Demonstrate how to implement the INDEX MATCH Function (indexmatch.xlsx)

  • The INDEX MATCH MATCH formula is the combination of two functions in Excel: INDEX and MATCH.

    =INDEX() returns the value of a cell in a table based on the column and row number.

    =MATCH() returns the position of a cell in a row or column.

    Combined, the two formulas can look up and return the value of a cell in a table based on vertical and horizontal criteria.  For short, this is referred to as just the Index Match function.

• Challenge you to modify the BicycleShop.xlsx solution from the previous lecture to implement the INDEX function BicycleShopIndexSolution.xlsx.

In this Lecture we will

• Wrap up the Conditional Calculations and Table Lookups Section by challenging you with the Vertical Blinds Application Problem

  • reviews the use of the IF function

  • reviews the use of the  CHOOSE function

  • reviews the use of the VLOOKUP function

  • reviews the use of the INDEX function

In this Lecture we will

• Discuss the fact that when you start using Excel you’ll probably do all your work manually because you haven’t found the powerful automation built right into Excel. 

• Introduce you to Macros, quickly show you the Macro recorder and explain the difference between Macros and VBA. 

• Learn that a Macro is a collection of commands that you can run with a single click or keyboard combination. They can automate almost anything you can do in Excel and even do things you might not have known were possible. 

  • In essence, a macro is a computer program that gives automated instructions to the computer. When you create a macro, you are recording your mouse clicks and keystrokes. After you create a macro, you can edit it to make minor changes to the way it works.   The original macros were a way to use a few characters to represent a lot of instructions. They were called macros because the output was much bigger than the input. (Greek makros = “long, large”) 

  • The use of Macros in Excel saves time and can expand the capabilities of Excel. Suppose that every month, you create a report for your accounting manager. You want to format the names of the customers with overdue accounts in red, and also apply bold formatting. You can create and then run a macro that quickly applies these formatting changes to the cells you select.   

  • You have probably seen macro warnings when opening Microsoft Office documents, and so macros might make you think of scary things such as viruses. Well you should know that most macros are not only harmless but are actually tremendous timesavers. And macros are easier to create and use than you might expect. 

  • RecordedMacroDemo (ctrl+f  ... formatcells)

• Microsoft VBA is the programming language that Excel Macros are recorded in. VBA stands for “Visual Basic for Applications”. With VBA you can create visual basic macros for Excel. 

  • If you can record Macros with the Macro recorder, why would you want to use VBA? The truth is that while the Macro recorder is simple to use, it doesn’t create nice code and it can only record straightforward tasks. 

  • VBA allows you to add brains to your macros and turn them into intelligent programs, for example you can ask the user to type in their name and store that in your program. VBA allows you to take advantage of features that can’t be accessed through the standard user interface.  

  • Excel was the first major Microsoft Office application to use VBA, but VBA is not tied directly to Excel. It works just as well with other VBA-enabled applications, such as Word, Access and PowerPoint. So anyone who knows how to write Excel macros in VBA can transfer that knowledge to other types of Visual Basic programming. 

  • VBAdemo (user created form)

In this Lecture we will

• Learn the easiest way to create a macro by using the macro recorder. This is like turning on a tape recorder (I'm dating myself LOL) that will register everything you do until you turn it off. The recorded operation can then by played back by using a keyboard shortcut consisting of the Ctrl key plus a single letter that you choose.

• Learn that the DEVELOPER tab where the Record Macro and Other VBA Tools are,  is hidden by default

  • Goto the FILE tab ... then OPTIONS ... CUSTOMIZE RIBBON .. MAIN .... DEVELOPER  ... to activate

  • Set the Macro Security to "Disable with Notification"

• Create a simple recorded macro to change the format (bold titles , fill background) of a group of data (macrorecordintro.xlsm)

  • call it format with shortcut CTRL + f

  • run it using the shortcut key and the formal name

  • assign the macro to a Form Control Button (in Developer Tab choose INSERT) and run it... don't use ActiveX version

  • Note: macro enabled workbooks are save as .xlsm

• View and modify the macro code (VBA) using the editor (choose MACRO button ... then edit)

  • highlight sub procedure use

  • modify code

In this Lecture we will

• Learn that when you use the Macro Recorder, VBA creates a block of macro code that starts with the Sub keyword followed by the name of the macro and a pair of parentheses. The block of code is terminated by the End Sub keyword. This block of code is referred to in VBA as a Sub Procedure

• Learn that so far we have only dealt with VBA Sub Procedures. Sub Procedures carry out actions such as formatting spreadsheet cells or saving workbooks. VBA also supports one other type of procedure called a Function procedure. Function procedures start with the Function keyword and end with the End Function keywords.

• Learn that the difference between a Sub Procedure and and Function Procedure is that Function procedures calculate a result or a return value.  When you run a Function procedure, it produces a numeric value or some text that you can use in a worksheet. You can use Function procedures in your worksheet formulas in the same way that you can use the SUM function or the IF function to calculate a result in a worksheet formula.

• Learn that by using VBA you can write your own worksheet functions that you can use in your cell calculations. By writing your own functions you can make many complex calculations much easier for yourself and others in your organization. For example say you wanted to convert degrees Centigrade to degrees Fahrenheit. You could write your own function to do that. (tempconversion.xlsm)

  • There is no way that you can record a user-defined function. It must be written from scratch

  • Activate the workbook that will contain the function and from the Developer Tab choose Visual Basic (Alt+F11) to open the Editor window

  • Use Insert/Module to create a new VBA module

  • Type in the function

    • Public Function CToF(DegreesC)

                 CToF = DegreesC * 9 / 5 + 32

      End Function

  • Test out the function a list of Celsius temperatures and calculate the corresponding Fahrenheit temperatures

• Challenge you to create a function that converts kilograms to pounds

In this Lecture we will

• Look at some more advanced applications of User Defined Functions

  • We will create a Commission Function that involves some conditional statements to determine the corresponding rate (Commission.xlsm)

  • Public Function ComRate(sales)

    If sales >= 10000 Then

        ComRate = 0.1

    Else

        ComRate = 0.07

    End If

    End Function

• Look at the concept of Event procedures and how to implement them in your workbook (interestvba.xlsm)

  • Choose ThisWorkbook ... the event WorkBook_Open()

  • add the VBA command MsgBox "Welcome"

• Provide you with an exercise containing  problems which will allow you to practice creating User Defined Functions in Excel using VBA

In this Lecture we will

• Learn that Visual Basic for Applications is an object-oriented programming language. The basic concept of object-oriented programming is that a software application (Excel in this case) consists of various individual objects, each of which has its own set of features and uses. 

• Learn that An Excel application contains workbooks, worksheets, range of cells and each of these object has its own set of features, which are called properties, and its own set of uses, called methods. 

  • A worksheet cell is an object, and among its describable features (its properties) are its address, height, and formatted fill color. A workbook is also a object, and among its usable features (its methods) are its capabilities to be opened, closed, and have a chart or pivot table added to it. 

• Learn that to point to a specific object in VBA, you can traverse the object model.
  For example, to get to cell A1 on Sheet 1, you can enter this code:

  Application.Workbooks("cost.xlsx").Worksheets("Sheet1").Range("A1").Select

  In most cases, the object model hierarchy is understood, so you don’t have to type every level. Entering this code also gets you to cell A1 because Excel infers that you mean the active workbook, and the active sheet:

  Range("A1").Select

• Learn that if you want to refer to a worksheet in the Worksheets collection, you can refer to it by its position in the collection, as an index number starting with 1, or by its name, as quoted text. 
  Worksheets(1).Select
  Worksheets("MySheet").Select

• Learn that you refer to the property of an object by referring to the object and then to the property. For instance, you can change the value of a specific cell in your worksheet by changing its value property. 
  Worksheets("Sheet1").Range("A1").value=5

• Learn how to access VBA Reference Help with the VBA editor

• Learn how to access the Object Browser within the VBA editor (View/Object or F2) 

• Use the Immediate Window (View Menu) to illustrate the Excel Object Model (Qtrsales.xlsx)

  • ?Application.name

  • ?Application.Workbooks(1).name

  • ?Application.Workbooks("Qtrsales.xlsx").Worksheets.count

  • ?Application.Workbooks(1).Worksheets(1).name

  • ?Application.Workbooks(1).Worksheets(1).range("A1").value

  • Application.Workbooks(1).Worksheets(1).range("B10").value=20

  • Worksheets(1).range("2:2").select    .... selects row 2

  • Worksheets(1).range("A1:A8,E1:E8").select

  • Application.quit

• Provide you with an exercise to practice using the Excel Object Model

In this Lecture we will

• Discuss how to declare the use of regular variables in a VBA program

  • Dim title As String

    Dim rownum As Integer

    title = "Paradise Electronics"

    rownum = 1
    

• Discuss how writing VBA procedures for Excel essentially involves manipulating objects. Excel itself is an object (called Application) and it contains more than 100 additional objects. 

• Discuss how an object variable is a variable that serves as a substitute for the actual object.

  • To create an object variable, use the Set keyword. The statements below create an object variable for the Range object:
    

    Dim Dependents as Range
    Set Dependents = Workbooks("Taxbook").Worksheets("Sheet1") .Range("A5")

    
    After this assignment is made, you can use the Dependents variable as if it were the actual object. This includes modifying its properties and using its methods. For example, you can change the value stored in cell A5 with the following statement:
    

    Dependents.Value = 3

• Implement a Sub Procedure ... not a Function Procedure to modify the format of a Spreadsheet that tracks sales by employees for an Electronics Store (sales.xlsx -> salesvba.xlsm)

  • Public Sub FormatWorksheet()

    'declare regular variables and store values

    Dim title As String

    Dim rownum As Integer

    title = "Paradise Electronics"

    rownum = 1

    
    

    'declare object variable and assign address

    'other data types include Workbook and Range

    Dim shtFirstQ As Worksheet

    Set shtFirstQ = Application.Workbooks("salesvba.xlsm").Worksheets(1)

    
    

    'insert two rows above row 1

    shtFirstQ.Rows(rownum).Insert

    shtFirstQ.Rows(rownum).Insert

    
    

    shtFirstQ.Range("A1").Value = title

    
    

    shtFirstQ.Range("b13").Formula = "=sum(b4:b12)"

    shtFirstQ.Range("c13").Formula = "=sum(c4:c12)"

    shtFirstQ.Range("d13").Formula = "=sum(d4:d12)"

    
    

    'print preview worksheet

    shtFirstQ.PrintPreview

    End Sub

  • Once this procedure is created it is automatically available also via the Macro Icon in the Developer Tab

    • We insert a Form Control Button so we can quickly run the macro right on the worksheet

      • right click the button and click on Assign Macro

    • You can also insert a Shapes and assign a  macro to it

• Provide you with an exercise to practice implementing Object variables in VBA code

  • Note the insertion of a Form Control Button which connects to a macro in InventorySolution.xlsm

In this Lecture we will

• Extend our knowledge of Variables by focusing on inputting and using String Variables

  • declarations and assignment statements

  • option explicit .... placed in the General Declarations section (forces proper declarations)

  • using an Inputbox

  • using the val command (to convert a string to number)

  • the concept of string concatentation

• Implement the concepts above using two example spreadsheets

  • commission.xlsx -> commissionvba.xlsm (we do together)

  • note the use of the Workbook_Open event to store the VBA code

    • code executes soon as Workbook is opened

    • Private Sub Workbook_Open()

      ' declare variables and assign address to object variable

      Dim name As String

      Dim rate As String

      Dim shtJan As Worksheet

      
      

      Set shtJan = Application.Workbooks("commissionvba.xlsm").Worksheets("January")

      
      

      name = InputBox("Enter your name", "Name")

      rate = InputBox("Enter the rate", "Rate")

      
      

      'convert rate to number and divide by 100

      shtJan.Range("B1").Value = Val(rate) / 100
      

      shtJan.Range("A19").Value = "Prepared by " & name

      
      

      End Sub

  • indexget1.xlsm (demo)

    • This spreadsheet is a redo of index2.xlsx from Lecture 18

    • This spreadsheet now incorporates user input via the InputBox command

    • .... but no error checking yet with "if" statements to check for erroneous part numbers and sizes

In this Lecture we will

• Put into practice what we learned in the previous lecture on using string variables and the InputBox command in VBA

• Work through the first Exercise problem together (Associates.xlsx -> AssociatesSolution.xlsm)

• Give you an opportunity to try several other questions for practice ... we will discuss the solutions after you have attempted them

• Revisit the "commisionvba.xlsm" from the previous lesson and modify it to use a Call statement (commissionvbaCall.xlsm)

  • Add a new module then create a macro procedure named GetNameAndRate.

  • Move the code from the Workbook's Open event to the GetNameAndRate procedure

  • In the Open event  enter a Call statement that calls the GetNameAndRate procedure

  • Save and exit Excel... then reopen the Workbook to invoke its Open event.

  • The reason we did this is ...

    • Assume that the user wants to change the name and rate. Rather than having to close and then open the workbook to invoke its Open event, we can simply run the GetNameAndRate macro.

In this Lecture we will

• Challenge you to incorporate user prompts into the "party.xlsx" spreadsheet from Lecture 15

  • You will need to code a workbook Open event procedure that begins by welcoming the user to the "Party Planner" and then prompts the user to enter 8 items of information, from  total number of guests  to choice for music.

• The solution is called "partymac.xlsm" ... give it a run to see how it works... but try not to look at the underlying VBA code before you give it a good attempt.

In this Lecture we will

• Learn how to implement a ComboBox  control within an Excel spreadsheet as another way to accept user input

  • Click Insert from the Developers Tab

  • Choose ComboBox from the ActiveX controls

  • Place the control somewhere on the spreadsheet

  • Click on Properties ... change the name of the control to CboCities

  • Now you need to fill the control with data

    • create a list of cities somewhere on the Worksheet

    • name the list ... cities

  • Go back to Properties ... under ListFillPage ... enter cities

  • Exit Design Mode and Protect the worksheet (Review Tab)

  • Lastly we are going to code the CboBox to display the city we have chosen

    • Unprotect Worksheet and go back into Design Mode

    • Double click to enter the VBA code window
      Private Sub CboCity_Change()

      MsgBox "You chose " & CboCity.Text

      End Sub

    • Exit the Design mode and test out the CboBox

• Compare the ComboBox control to the ListBox control ... basically same implementation

  • Change Column Head to true in properties

• Demonstrate a practical application of the ListBox control (pricelist.xlsx -> pricelistvba.xlsm)

  • we choose a model number from a ListBox  and use it to extract the price corresponding from a lookup table.

  • note the use of the DblClick Event

  • note the numeric declarations of Double (decimals) and Currency

  • note how we  unprotect the worksheet to allow use to use the InputBox and then  protect the worksheet before we finish.

  • note the use of the VLOOKUP command in VBA
    
    'search for model number and return price

    'false implies you must find an exact match
    

    curPrice = Application.WorksheetFunction.VLookup(LstModel.Text, Range("pricelist"), 2, False)

• Provide you with an exercise to practice implementing the ListBox control and the InputBox

In this Lecture we will

• Start off by working through the solutions to the previous lecture Exercise on the implementation of the ListBox control

• Look at a practical application (parts ordering system)  of the some of the worksheet controls we have covered over the past several lectures (indexget2.xlsm)

  • Order Total Price

    • user enters a part number via a listbox

    • user enters a part size and quantity via inputboxes

    • VBA code simply places user input into specific cells on the Entry sheet to be used by formulas hard coded into adjacent cells

    • using the VLOOKUP and INDEX functions the spreadsheet determines the total cost of the order

  • Change Part Prices

    • user enters a part number via a combox

    • user enters  a part size and new price via  inputboxes

    • VBA code places all user input into a scratch pad area on the Lookup sheet

      • The scratch pad area is where the row and column locations of the price to be changed are determined

        • ... with the help of some new lookup tables which match up sizes and partnumbers with the exact location of the relevant data in the actual PriceTable (which need the column letter... for the size  and the row number...for the part number)

      • VBA code is then used to perform  string concatenation to form the exact  cell location and update the price table
        
        TableLocation = "Lookups!" & shtLookups.Range("d18").Value & shtLookups.Range("d19").Value

        shtLookups.Range(TableLocation).Value = newprice

In the Lecture we will

• Enhance the Parts Ordering System from the previous lecture by incorporating the concepts of Selection and Repetition to error check user input

• The VBA If statement is used to allow your code to make choices when it is running.You will often want to make choices based on the data your macros inputs.
  
  Dim Msg As String 

  If Time < 0.5 Then   
       Msg = “Morning” 
  ElseIf Time >= 0.5 And Time < 0.75 Then   
       Msg = “Afternoon” 
  Else   
       Msg = “Evening” 
  End If 
  MsgBox “Good “ & Msg

• Loops are one of the most basic and powerful programming tools in VBA, and used across most programming languages. Loops are used to repeat a block of code as many times as required, until a given condition remains true or a specific point (or value) is reached, after which the the next section of code is executed. A loop enables you to write a few simple lines of code and achieve a far more significant output, just by repetition. 

  • There are two types of syntax in the Do Until Loop.

    Do
    [Code block to Execute]

    [if  condition then Exit Do]
    Loop

    
    

    Do
    [Code block to Execute]
    Loop [Until condition]

• Work through SelectionRepetitionPrimer.xlsm

• Work through indexbr.xlsm

  • focus here is on error checking the user input using a combination of If statements and do loops

In this Lecture we will

• Challenge you to update the partymac.xlsm application from Lecture 28

  • When the workbook is initially opened the "Welcome" Message should appear but not the rest of the input prompts

  • Place a button on the worksheet which when pressed activates the actual VBA code to accept user input for the Party Planner

  • Include error checking during user input using the techniques from the Selection Repetition Lecture.

• Call your solution partycheck.xlsm

In this Lecture we will

• Challenge you to update the Vertical Blinds Application (blinds.xlsx) from Lecture 19 ... call it (blindsmac.xlsm)

  • Use ComboBoxes to input the width and height of the blinds

  • Use a ListBox to input the style number desired

    • no more guessing the exact width, heights and styles of blinds on hand... a lot more user friendly

  • Use a Horizontal Scroll Bar to indicate the number of sets required
    
    Private Sub HsbSets_Change()

    Dim shtEntry As Worksheet

    Set shtEntry = Application.Workbooks("blindsmac.xlsm").Worksheets("entry")

    shtEntry.Range("b8").Value = HsbSets.Value

    End Sub

    
    

    Private Sub HsbSets_Scroll()

    Dim shtEntry As Worksheet

    Set shtEntry = Application.Workbooks("blindsmac.xlsm").Worksheets("entry")

    shtEntry.Range("b8").Value = HsbSets.Value

    End Sub

    
    

• Challenge you to update blindsmac.xlsm to support the ablility to update the raw prices in the prices table... call it (blindsmacupdate.xlsm)

  
  

In this Lecture we will

• Learn how to update the look and feel of our spreadsheets by adding a main menu (foodcosts.xlsx -> foodcostsMenuUpdate.xlsm)

  • remove the grid lines (Page Layout-Gridlines-View)

  • add a fill color or background image

  • add buttons which allow us to access the other worksheets in the workbook

    • Remember to use ActiveX controls... then double click to enter VBA code

    • Application.Workbooks("foodcostsMenuUpdate.xlsm").Worksheets("Dinners").Activate

  • add hyperlinks/buttons allowing us to return back to the main menu

• Demonstrate how the Party Planner Spreadsheet we have worked on  throughout the Lectures can be updated to a Menu Driven Workbook (partybtn.xlsm)

  • Remember to edit any buttons or controls you must unprotect the worksheet (Review Tab)  and be in Design Mode (Developer Tab)

  • Data Entry Button

    • Private Sub CmdDataEntry_Click()

           Application.Workbooks("partybtn.xlsm").Worksheets("party").Activate

      End Sub

    • Can also be accessed using ...Application.Workbooks(1).Worksheets(2).Activate

    • Once the party worksheet is activated we use a procedure "call"
      Private Sub Worksheet_Activate()

           Call PartyPlanner

      End Sub

  • Print a Worksheet
    Private Sub ImgPrint_Click()

         Dim shtparty As Worksheet

         Set shtparty = Application.Workbooks("partybtn.xlsm").Worksheets("party")

         shtparty.PrintPreview

    End Sub

  • Save a Worksheet
    Private Sub CmdSave_Click()

         Dim shtMain As Workbook

         Set shtMain = Application.Workbooks("partybtn.xlsm")

         shtMain.SaveAs "partybtn.xlsm"

    End Sub

  • Exit a Workbook

    Private Sub CmdExit_Click()

         Application.Quit

    End Sub

• Offer you the challenge to update the "Professors" problem from Lecture 27 so that it implements a  menu driven workbook. (Professor.xlsx -> ProfessorSolution.xlsm)

  • have buttons which provide access to each of the worksheets and return buttons on each of those worksheets

  • have a button which activates the key macro GetTotalPoints

  • have a button which saves the workbook

  • have  buttons on each worksheet that print each worksheet

    
    

In this Lecture we will

• Learn how to create and implement a User Form consisting of a number of form controls (text boxes, combo boxes, radio buttons  and command buttons) (Test.xlsm ... DataEntry.frm)

  • First we create two worksheets ... Main/Data

    • On the Main sheet add to two ActiveX Buttons "Enter Data" and "View Data" ... they will be coded in VBA soon

    • On the Data sheet enter the labels Name, Sex, City and one cell over Define the cells with the exact same names

  • Next enter the Visual Basic Editor

    • Choose UserForm from the insert menu ... name the form DataEntry in properties

      • add Label for name and associated Textbox

      • add Label for city and associated Combo Box

      • add Frame and Radio Buttons for sex

      • add OK and Cancel Buttons

      • save newly created UserForm by choosing Export File (will save .frm )

  • Now we start coding

    • Lets start by coding the 2 buttons on the Main sheet (double click on them to enter VBA )

      • Enter Data
        Private Sub BtnEntry_Click()

        DataEntry.Show

        End Sub

      • View Data

        Application.Workbooks("Test.xlsm").Worksheets("Data").Activate   OR
        Application.Workbooks(1).Worksheets(2).Activate
        ... add a Back to Main Hyperlink

    • Staying in the VBA editor

      • double click on ThisWorkbook  and code the Open procedure
        Private Sub Workbook_Open()

        Application.Workbooks(1).Worksheets(1).Activate

        End Sub

    • Finally we focus on the UserForm

      • Double click anywhere on the form but not on a control ... we want the Initialize event ... here we will automatically fill the ComboBox  with the city names
        Private Sub UserForm_Initialize()

        CboCity.AddItem "Toronto"

        CboCity.AddItem "New York"

        CboCity.AddItem "Paris"

        End Sub

      • Next we code the Cancel Button ... we want to hide the UserForm and return back to the Main sheet
        Private Sub BtnCancel_Click()

        DataEntry.Hide

        Application.Workbooks(1).Worksheets(1).Activate

        End Sub

      • ... and lastly the OK Button... this is where most of the logic occurs. This is where we take all the inputted information and transfer it to our Data sheet... for further processing
        
        Private Sub BtnOK_Click()

        
        Dim shtData, shtMain As Worksheet

        Set shtData = Application.Workbooks(1).Worksheets(2)

        Set shtMain = Application.Workbooks(1).Worksheets(1)

        
        

        shtData.Range("name").Value = TxtName.Text

        shtData.Range("city").Value = CboCity.Text

        
        

        If RdoMale.Value = True Then

            shtData.Range("sex") = "Male"

        Else

            shtData.Range("sex") = "Female"

        End If

        
        

        DataEntry.Hide

        shtMain.Activate

        
        

        End Sub

In this Lecture we will

• Review and extend our knowledge of Excel User Forms (dialogintro.xlsm ... DataEntry.frm)

  • Multiple textboxes

  • Multiple Radiobuttons in a Frame control

  • Listbox .... introduces the repetition structure For...Next
    Private Sub UserForm_Initialize()

    For i = 1 To 31

        LstDate.AddItem i

    Next i

    End Sub

In this Lecture we

• Revisit the Parts Ordering Application from Lectures 30 and 31 for the last time

• Implement the  UserForm concepts we have covered in the previous lectures to further enhance the Part Order Application to make it as user friendly as possible  (indexdlg.xlsm ... IndexData.frm ... IndexUpdate.frm)

In this Lecture we

• Offer you the challenge of creating the "Budget Tracker" application

• We will provide you with the full solution ... try it all you want ... but don't look at the code... give it a good try.