Excel Macros Part 2

This lesson explains how to use the VBA editor to create and edit Macros.

 

Related lessons:         http://bus91l.altervista.org/Assignments/Macros.htm

http://bus91l.altervista.org/Assignments/Macros3.htm

 

Covered in Macros 1

Understand how to Record Macros

Use ALT F11 to open VBA Editor

Use the VBA Editor tools to edit macros.

Know that red text in the VBA code indicates an error in that instruction.

Understand how to protect a worksheet.

Understand how to save the file as a Macros enabled file type.

 

New Stuff

VBA Select Dynamic Ranges   1:21

VBA Select Fixed Named Ranges    1:36

VBA Copying/Pasting Ranges    1:11

VBA Creating a Popup Menu #1     2:12

VBA Creating a Popup Menu #2     9:20

 

Learning Objectives – Summary of Key Points

Understand how to work with dynamic named ranges.

Understand how to use a file path in a function or formula.

Understand how to save the file as a Macros enabled file type.

Understand how to create a For-Next loop in VBA

Understand how to create a Message Box in VBA.

 

Video: VBA Basics Tutorial         Time = 5:22

Brush up on the basics. It uses Excel 2007 but everything will still work in later versions of Excel.

The video shows how to use the “Use Relative References” icon in the developer tab, something that I have not seen in other videos.

https://www.youtube.com/watch?v=t1vdt0FddsE

 

Video:  Watch this video to learn about For-Next Loops. 

Creating and Using Loops In VBA      Time = 11:33

You will be using some of the tools in this video in the Used Cars database assignment.

 

Video:  Watch this video to learn about message boxes and the Case command.

Running macros from a validation list  Time  =  9:11

It uses the Case method to pick from a validation list and display messages.  You will be using some of the tools in this video in the Used Car assignment.

Task: Create a macro to display an interactive message box.

 

This is the end of the lesson.

 

 Instructions for the Check for Understanding (CFU) form.

A CFU form is turned in for every assignment.

Starting with the line for your name, copy and paste the CFU form into a Word document.

Fill in your name, date, section number

Circle (or indicate by highlighting) a Y or N response for each statement. I do not subtract points for No answers.

Below the list of Y/N statements, insert ONE screenshot of any part of your work. I just need to see something to prove that you did the assignment. I don’t need to see all of the assignment.  Resize the screenshot, if necessary, so both the form and graphic can fit on one page.

A copy of the CFU is due to be turned in during the next class session

Name _______________________________      Date___________       Section _______

Check for Understanding – Excel Macros 2

Y/N I can create a For-Next loop in VBA

Y/N I can create a Message Box in VBA,