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