ความรู้พื้นฐานเกี่ยวกับ macro ใน excel 1 contents...
TRANSCRIPT
I
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS PART 1
ความรู้พื้นฐานเกี่ยวกับ Macro ใน Excel ____ 1ควำมหมำยของ Macro และ VBA.. .......................2
เปดใช้งำนมำโคร (Macro) และกำรเขยีนโปรแกรมด้วย VBA ................................................................2zสร้ำงมำโคร (Macro) ใน Excel 2007 ................... 2
zสร้ำงมำโคร (Macro) ใน Excel 2010 ................... 2
zเขยีนโปรแกรมด้วย VBA ใน Excel 2010 ............ 3
zเขยีนโปรแกรมด้วย VBA ใน Excel 2007 ............ 3
zเขยีนโปรแกรมด้วย VBA ใน Excel 2003 ............ 3
เปดใช้เครื่องมอืนกัพฒันำ (Developer Tab) ..........4zเปดใช้แทบ็นกัพฒันำ (Developer Tab) ใน Excel
2007 ..................................................................... 4
zเปดใช้แทบ็นกัพฒันำ (Developer Tab) ใน Excel 2010 ..................................................................... 5
เปรยีบเทยีบกำรท�ำงำนโดยใช้ Macro และกำรเขยีน VBA ..............................................................6
ก�ำหนด Macro Security.. .......................................7zตั้งค่ำใน Excel 2003 ............................................ 7
zตั้งค่ำใน Excel 2007 ............................................ 8
zตั้งค่ำใน Excel 2010 ............................................ 9
ก�ำหนดค่ำเมื่อเปดไฟล์ที่มโีปรแกรม VBA ฝงอยู่ ...9zส�ำหรบัผู้ใช้งำน Excel 2007 ................................ 9
zส�ำหรบัผู้ใช้งำน Excel 2010................................10
บนัทกึไฟล์ที่มมีำโคร (Macro) ..............................10zส�ำหรบัผู้ใช้งำน Excel 2007 .............................. 10
zส�ำหรบัผู้ใช้งำน Excel 2010 .............................. 11
สร้ำงมำโครเบื้องต้น (Record New Macro) .........12
เปลี่ยนชื่อและลบ Macro .....................................14
แก้ไขมำโครที่สร้ำงไว้แล้ว.....................................15
สั่งรนัมำโคร (Run Macro) และกำรก�ำหนดมำโครไว้ใช้งำน ...............................................................15zก�ำหนดให้รนัมำโครจำกหน้ำต่ำง Visual Basic
Editor (VBE) ....................................................... 15
zก�ำหนดให้รนัมำโครโดยคลกิปุ่ม Run ในหน้ำต่ำง Macro ................................................................. 16
zก�ำหนดให้รนัมำโครจำกหน้ำแทบ็ View ............ 16
II
zก�ำหนดให้รนัมำโครจำก Shortcut key .............. 17
ก�ำหนดมำโครให้กบัออบเจก็ต์ต่ำงๆ (Shapes, Picture, Control Button) .......................................17zก�ำหนดมำโครให้กบั Shapes ............................. 17
zก�ำหนดมำโครให้กบั Picture .............................. 19
zก�ำหนดมำโครให้กบั Control Button ................. 20
zก�ำหนดมำโครไว้ที่ Quick Access Toolbar......... 21
ข้อผดิพลำดในมำโคร (Macro Errors) ..................31
Debug Macros (ตรวจแก้จดุบกพร่องในมำโคร)...32
เขยีนโปรแกรมเพื่อก�ำหนด Object, Properties & Method (วตัถ,ุ คณุสมบตั ิและวธิกีำร) ................34zExcel VBA Property ........................................... 34
zExcel VBA Method ............................................ 35
zApplication Object ............................................. 37
Workbooks & Worksheets ..................................39
ก�ำหนด Properties และ Method ของ Workbooks และ Worksheets ..................................................41zตวัอย่ำงกำรเพิ่ม Worksheets ........................... 42
ก�ำหนด Module (โมดูล) และ Procedure
(โพรซเีจอร์) ...........................................................43
ตวัแปร (Variable) ..................................................43zตวัแปรค่ำคงที่ (Constant) .................................. 44
ชนดิข้อมูล (Data Type) ........................................44
PART 3การใช้งานปุมควบคุมแบบตางๆ (Controls) __ 47สร้ำงกล่องข้อควำม (TextBox) .............................48zส�ำหรบัผู้ใช้งำน Excel 2003 .............................. 48
zส�ำหรบัผู้ใช้งำน Excel 2007 และ Excel 2010...48
zสร้ำง TextBox ลงบนแผ่นงำน ........................... 48
zเตมิข้อควำมใน TextBox ด้วยกำรเขยีนโค้ด VBA (Visual Basic of Applications) ............................ 51
zลบข้อควำมออกจำก TextBox ........................... 54
สร้ำงกล่องรำยกำร (ListBox) ...............................55zส�ำหรบัผู้ใช้งำน Excel 2003 .............................. 55
zส�ำหรบัผู้ใช้งำน Excel 2007 และ Excel 2010...55
zสร้ำง ListBox ลงบนแผ่นงำน ............................ 55
สร้ำงกล่องข้อควำมแบบมตีวัเลอืก (ComboBox)...60zส�ำหรบัผู้ใช้งำน Excel 2003 .............................. 60
zส�ำหรบัผู้ใช้งำน Excel 2007 และ Excel 2010...60
zสร้ำง ComboBox ลงบนแผ่นงำน ...................... 61
สร้ำงกล่องท�ำเครื่องหมำย P (CheckBox) ........65zส�ำหรบัผู้ใช้งำน Excel 2003 .............................. 65
zส�ำหรบัผู้ใช้งำน Excel 2007 และ Excel 2010...66
PART 2ความรู้พื้นฐานเกี่ยวกับ VBA ____________ 23Visual Basic Editor (VBE) .....................................24zส�ำหรบัผู้ใช้งำน Excel 2007 และ Excel 2010...24
zส�ำหรบัผู้ใช้งำน Excel 2003 .............................. 25
องค์ประกอบที่ส�ำคญัในหน้ำต่ำงกำรเขยีนโปรแกรม VBA ......................................................25zเมนูบำร์ (Menu Bar) .......................................... 25
zทูลบำร์ (Toolbar) ................................................ 26
zProject Explorer ................................................. 26
zProperties Window ............................................ 26
zCode Window .................................................... 27
zMacro Comments .............................................. 27
ก�ำหนดให้แสดงผลภำษำไทยในหน้ำต่ำงเขยีนโปรแกรม ..............................................................27
MsgBox (กล่องข้อควำม) .....................................28zแสดงกล่องข้อควำมแบบง่ำยๆ .......................... 28
zแสดงข้อควำมตวัเลขที่ผู้ใช้เลอืก ....................... 29
zกำรเพิ่มแถวของข้อควำมในกำรแสดงในกล่องข้อควำม ............................................................. 30
III
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS zสร้ำง CheckBox ลงบนแผ่นงำน ...............................66
zวธิอ้ีำงถงึช่องท�ำเครื่องหมำย P(CheckBox) โดยกำรเขยีนโค้ด VBA ............................................................. 67
สร้ำงปุ่มตวัเลอืก (Option Button) ........................69zส�ำหรบัผู้ใช้งำน Excel 2003 .............................. 69
zส�ำหรบัผู้ใช้งำน Excel 2007 และ Excel 2010...70
zสร้ำง Option Button ลงบนแผ่นงำน ................. 70
zวธิอี้ำงถงึ Option Button (ปุ่มตวัเลอืก) โดยกำรเขยีนโค้ด VBA ................................................... 71
สร้ำงปุ่มค�ำสั่ง (Command Button) ......................75zส�ำหรบัผู้ใช้งำน Excel 2003 .............................. 75
zส�ำหรบัผู้ใช้งำน Excel 2007 และ Excel 2010... 76
zสร้ำง Command Button ลงบนแผ่นงำน ........... 76
zตวัอย่ำงกำรใช้งำน ............................................ 77
สร้ำงปุ่มกำรเพิ่ม/ลดค่ำ (Spin Button) .................80zส�ำหรบัผู้ใช้งำน Excel 2003 .............................. 80
zส�ำหรบัผู้ใช้งำน Excel 2007 และ Excel 2010... 80
zสร้ำง Spin Button ลงบนแผ่นงำน .................... 81
สร้ำงแบบฟอร์มส�ำหรบัผู้ใช้งำน (UserForm) .......83zส�ำหรบัผู้ใช้งำน Excel 2003 .............................. 83
zส�ำหรบัผู้ใช้งำน Excel 2007 และ Excel 2010... 84
zสร้ำง UserForm ลงบนแผ่นงำน ........................ 84
zแสดง UserForm ................................................ 88
zก�ำหนดค่ำเริ่มต้นให้กบั UserForm .................... 89
zก�ำหนดมำโครให้กบัปุ่ม Spin Button ................ 91
zก�ำหนดมำโครให้กบัปุ่ม Cancel ......................... 92
zก�ำหนดมำโครให้กบัปุ่ม Clear Form ................. 93
zก�ำหนดมำโครให้กบัปุ่ม OK ............................... 94
PART 4 Workshops สร้างงานด้วย Macro _______ 97Workshop 1 : สร้ำงมำโครให้กบักรำฟหรอื
แผนภูม ิ(Create Macro Dynamic Chart) .............98zสร้ำงกรำฟแบบไดนำมกิจำกตำรำงข้อมูล ........ 98
zสร้ำงมำโครเอำไว้ใช้งำน .................................. 102
zสร้ำงปุ่มส�ำหรบัใช้ในกำรควบคมุ .................... 111
zก�ำหนดมำโครลงในปุ่ม .................................... 114
IV
Workshop 2 : Import Data น�ำเข้ำข้อมูลจำก
แหล่งอื่น .............................................................118
Workshop 3 : กรองข้อมูลสนิค้ำหรอืยอดขำย
5 ล�ำดบั ..............................................................126
Workshop 4 : หำผลรวมย่อยของสนิค้ำอย่ำงรวดเรว็ ................................................................135zสร้ำงมำโคร SubTotal_Product ........................ 135
zสร้ำงมำโคร ClearSubTotal_Product ................ 138
zสร้ำงมำโคร SubTotal_Salesman ..................... 141
zสร้ำงมำโคร ClearSubTotal_Salesman ............. 143
zก�ำหนดมำโครลงในปุ่มต่ำงๆ ........................... 145
Workshop 5 : หำผลรวมของสนิค้ำแบบไดนำมกิ โดยที่ข้อมูลอยู่ในแผ่นงำนเดยีวกนัและต่ำง
แผ่นงำนกนั ........................................................149
Workshop 6 : ลอ็คเซลล์บำงเซลล์ไม่ให้มกีำรแก้ไข และก�ำหนดให้กรอกข้อมูลได้ในบำงช่อง ...........159
Workshop 7 : ปลดลอ็คเซลล์หรอื Worksheet ให้ท�ำงำนได้ตำมปกต ิ.............................................162
Workshop 8 : สร้ำงตำรำงเปรยีบเทยีบยอดเงนิกู้และกำรช�ำระเงนิงวด .........................................165zสร้ำงมำโคร Compare ...................................... 165
zสร้ำงมำโคร ClearCompare .............................. 169
zก�ำหนดมำโครลงในปุ่ม .................................... 170
Workshop 9 : หำข้อมูลที่ซ�้ำหรอืไม่ซ�้ำกนั โดยข้อมูลนั้นอยู่ต่ำงคอลมัน์กนั ...............................174zสร้ำงมำโคร Compare2Lists ............................ 174
zสร้ำงมำโคร ClearCompare2Lists .................... 179
zก�ำหนดมำโครลงในปุ่ม .................................... 180
Workshop 10 : หำข้อมูลที่ซ�้ำกนัอย่ำงรวดเรว็ น�ำค่ำที่ได้ไปวำงไว้ในอกีคอลมัน์หนึ่ง .....................183zสร้ำงมำโคร Duplicate1 .................................... 183
zสร้ำงมำโคร ClearDuplicate1 ........................... 189
zก�ำหนดมำโครลงในปุ่ม .................................... 191
Workshop 11 : ลบข้อมูลที่ซ�้ำกนัออกอย่ำง รวดเรว็ ................................................................193
Workshop 12 : สร้ำงมำโครกรำฟในกำรแสดงรำยงำนผลกำรด�ำเนนิงำน .................................196
zสร้ำงมำโคร FormatChart ................................. 196
zก�ำหนดมำโครลงในปุ่ม .................................... 200
Workshop 13 : สร้ำงตวักรองข้อมูลแบบหลำยเงื่อนไข ...............................................................203zสร้ำงมำโคร MultipleFilter ................................ 205
zสร้ำงมำโคร Clear_MultipleFilter ...................... 206
zก�ำหนดมำโครลงในปุ่ม .................................... 207
Workshop 14 : แสดงผลกำรประเมนิและเกรด
ตำมเกณฑ์ที่ต้องกำร ..........................................211zสร้ำงมำโคร Cal_Test ....................................... 212
zก�ำหนดมำโครลงในปุ่ม .................................... 214
zสร้ำงมำโคร Clear ............................................ 216
zก�ำหนดมำโครลงในปุ่ม .................................... 217
zสรปุท้ำยบท...................................................... 220
PART 5 Workshops สร้างงานด้วย VBA ________ 221Workshop 1 : กำรน�ำเข้ำแผ่นงำน (Import
Worksheets) .......................................................222
Workshop 2 : กำรเปรยีบเทยีบช่วงเซลล์
(Compare Ranges) .............................................224
Workshop 3 : กรอกข้อมูลลงในกล่องข้อควำม (Input Boxes) ......................................................227
Workshop 4 : กำรสลบัค่ำ (Swap Values) ........230
Workshop 5 : แสดงแถบสถำนะ (Status Bar)...232
Workshop 6 : ก�ำหนดจดุพกั (Breakpoint) ........234
V
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS
CONT
ENTS Workshop 7 : ลบเซลล์ว่ำงออกจำกคอลมัน์
(Delete Blank Cells) ...........................................237
Workshop 8 : ลบรำยกำรที่ซ�้ำกนั (Remove
Duplicate) แบบหนึ่งคอลมัน์ ..............................239
Workshop 9 : ลบรำยกำรที่ซ�้ำกนั (Remove
Duplicate) แบบหลำยคอลมัน์ ............................242
Workshop 10 : แสดงรูปภำพตำมชื่อหรอืรหสั (Show Image in Comments) .............................244
Workshop 11 : สั่งพมิพ์รูปภำพที่อยู่ในกล่อง
ข้อคดิเหน็ (Print Image Comments) .................249
Workshop 12 : เคลยีร์ข้อมูลและภำพประกอบ
ในเอกสำร (Clear Content and Object) .............252
Workshop 13 : น�ำข้อมูลพร้อมภำพประกอบไปเกบ็ไว้ในแผ่นงำนอื่น (Move object to another sheet) ..................................................................255
Workshop 14 : สร้ำงแบบฟอร์มให้ผู้ใช้งำน
ส�ำหรบักรอกข้อมูล (Interactive Userform) ........259zกำรสร้ำงแบบฟอร์ม ......................................... 259
zก�ำหนดให้แสดงแบบฟอร์มขึ้นมำ .................... 265
Workshop 15 : สร้ำง UserForm ขั้นสูง
(Advanced Userform) .........................................269zเขยีนโค้ด VBA เพื่อให้แสดงข้อมูลตำม
ต้องกำร ............................................................ 279
Workshop 16 : แสดงรูปภำพประกอบใน UserForm (Show Picture in UserForm) ............284
Workshop 17 : เขยีนฟงก์ชนัขึ้นมำใช้งำนเอง (แปลงตวัเลขจ�ำนวนเงนิเปนตวัอกัษรภำษำองักฤษ) ..............................................................290zสรปุท้ำยบท...................................................... 292