Ads 468x60px

Labels

Jun 2, 2012

Segment 1: Recording an Excel Macro


This post is the first segment of the three-segment series: An Introduction to Excel Macros and VBA.

Copy Data

The following data set includes defect data for an electronics assembly inspection station. To begin the lesson, copy the following data set and paste it into a new Excel Workbook in cell A1 of Sheet1.

Defect Type
Defect Quantity
Backwards Part
26
Billboard
9
Excess Solder
23
Insufficient Solder
30
Missing Part
23
Solder Bridge
12
Tombstone
13
Unsoldered Pin
8

Record Pareto Macro

1.     Select “Record Macro…” under Macros in the View tab and save the macro as “RecordPareto” stored in “This Workbook”



2.      Highlight the data set (including headers)
3.      Sort in descending order by the second column (Sort is located in the Data tab)
4.      Select Clustered Column Chart from Insert tab (Under “2-D Column”)


5.      Delete the legend
6.      Rename the chart "New Chart Title"
7.      Insert an X-axis title (Layout tab under "Axis Titles")
8.      Rename title "X-Axis Title"
9.      Insert an Y-axis title (Layout tab under "Axis Titles")
10.  Rename title "Y-Axis Title"
11.  Select an area in the chart outside the title (to unselect the Y-Axis Title)

View Recorded Macro

Open Microsoft Visual Basic for Applications by selecting Alt + F11. Navigate to Module1 in the VBA Project window on the left (Click the plus next to VBAProject (Book1), then the plus next to Modules). Open Module1 by double clicking on it.


Read through the code to familiarize yourself with VBA code. Copy the code to a word processor and label each line of code that performs one of the above steps with the appropriate step number. Hint: a single event may produce multiple lines of code. When this happens, label only the first line of code produced by the event.

Continue to Segment 2: Modifying an Excel Macro > > >

0 comments:

Post a Comment