แบงปนMicrosoft Excel 2010 for Macro and VBA Programming
โดย: สมเกยรต ชวยมาก
1
เน�อหาหลกสตร
• วนท� 1 : การใช Macro & พ�นฐาน VBA• วนท� 2 : การเขยนโปรแกรม VBA • วนท� 3 : การพฒนาโปรแกรมสาเรจรปบน Excel
2
ทาความรจกกบ Macro
• Macro คอ...........................................................
• Macro คอ...........................................................
• ประโยชนของ Macro คอ........................................
• Macro เหมาะกบงานท�...........................................
• พ�นฐาน/ความเขาใจกอนไปถงข �น Macro
3
การเตรยมพรอมกอนเร�มตนทา Macro
4
การเตรยมพรอมกอนเร�มตนทา Macro
• บนทกไฟลเปน Macro File• Tab เคร�องมอ Developer• การกาหนด Macro Security• เกบ Macro ไวท�ไหน
5
บนทกไฟลเปน Macro File
• File -> Save as
• หรอ File -> Excel Option
6
Tab เคร�องมอ Developer
7
การกาหนด Macro Security
8
เกบ Macro ไวท�ไหน
9
สรางคาส�ง Macro ทาไดก�วธ
10
การบนทกคาส�งดวยเคร�องมอ
Record Macro
11
ชนดของ Macro
• Absolute • Relative
12
แนวคดเร�องการบนทก Macro
• …….• …….• บนทก• …….• นาไปใชงาน
13
ข �นตอนบนทก Macro
1. กดป ม2. ทางานท�ตองการ3. กดป ม
14
ทดสอบการ Run คาส�ง Macro
15
ทดสอบการ Delete คาส�ง Macro
16
การลบ Macro ท�อยใน Personal.XLSX
17
วธการ Run Macro
18
วธการ Run Macro
• วธท� 1: Developer -> Macros• วธท� 2: Shortcut key• วธท� 3: Button• วธท� 4: Object บนชต• วธท� 5: Quick Access Toolbar• วธท� 6: Tab Ribbon
19
วธท� 1: Developer -> Macros
20
วธท� 2: Shortcut key
21
วธท� 3: Button
22
วธท� 4: Object บนชต
23
วธท� 5: Quick Access Toolbar
24
วธท� 6: Tab Ribbon
25
เทคนคการทางานกบงานท�มข �นยาวมากๆ
• Macro1• Macro2• Macro3• Macro…n
26
เรยนรหลกการเขยนโปรแกรม
• เรยนร OOP– Object/Property/Method/Event
• เรยนร Constant/Variable• เรยนร Bug/Debug• LAB
27
เร�มตนกอนการเขยน VBA
• หนาตาง VBA• การต �งภาษาเพ�อใชในการเขยนโปรแกรม• เคร�องมอ Comment/UnComment• เขยนโปรแกรมท�ไหนไดบาง
28
ความสมพนธระหวาง Macro กบ VBA
29
การต �งภาษาเพ�อใชในการเขยนโปรแกรม
30
เคร�องมอ Comment/UnComment
31
เขยนโปรแกรมท�ไหนไดบาง
32
เร�มเขยน VBA
33
เร�มเขยน VBA
• เรยนรเร�อง Procedure• เรยนรเร�อง OOP• เรยนรเร�อง Constant/Variable• เรยนรเร�อง Statement Control• เรยนรเร�อง Bug/Debug• เรยนรเร�อง User Interface
34
เรยนรเร�อง Procedure
• Sub Procedure
• Function Procedure
35
เรยนรเร�อง OOP
36
Object Oriented Programming (Class -> Instant -> Object)Object -> PropertyObject -> MethodObject -> Event
Excel Object • Application• Workbooks• Worksheets• Sheets• Range• Cells• Offset
37
� ActiveWindows
� ActiveWorkbook
� ActiveWorksheet
� ActiveSheet
� Selection
� Activecell
� Activecell.Offset(1,0)
การอางอง Object
38
ตวอยางทF 3Application.Activeworkbook.ActiveSheet.ActiveCell.Value=10Activecell.Value=10ActiveCell.Offset(1,0).Value=10
ตวอยางทF 1Application.Workbooks(“Book1”).Sheets(“Sheet1”).Range(“A1”).Value=10Range(“A1”).Value=10
ตวอยางทF 2Application.Workbooks(1).Sheets(1).Cells(1,1).Value=10Cells(1,1).Value=10
เทคนคการเขยนโปรแกรมแบบ OOP• เร�มตนพมพ Object กอนแลวตามหลงดวย .
Property
Method
39 39
Object.Property=Var
Var=Object.Property
No Argument > Object.Method
Argument > Object.Method Argument
Required > Object.Method A1, A2
Optional > Object.Method A1, [A2]
ทดสอบ Object: Application
• Application.Quite• Application.Caption=“MyApp“• Application.ActiveWindow.Caption=1• Application.DisplayFormulaBar=False• Application.DisplayStatusBar=False• Application.DisplayFullScreen=True
40
ทดสอบ Object: Workbooks
• คาส�ง WorkBooks ท�ใช บอย– Workbooks.Count– Workbooks.Add– WorkBooks(2).Close– WorkBooks.Open– WorkBooks(1).Activate
41
• WorkBooks.Add• WorkBooks.Open
“C:\Book1.xls”• WorkBooks(“MyBook”).Sav
e• WorkBooks(1).Save• ActiveWorkBook.Save• ActiveWorkBook.Close
ทดสอบ Object: Worksheets• Sheets.Add• Sheets.Add after:=Sheets(“Sheet2”)• Sheets.Add after:=Sheets(1)• Sheets.Add after:=Sheets(Sheets.Count)• Sheets.Add Count:=3, Before:=Sheets(2)• ActiveSheets.Delete• Sheets(Array(1,3,5)).Delete• Sheets(2).Copy After:=Sheets(3)• Sheets(2).Copy After:=WorkBooks(2).Sheets(3)
• Sheets(2).Visible=False
42 42
ทดสอบ Object: Range/Cells
43
� A1-Style
� Range(“A1”).Select
� Range(“A1”).Value=10
� Range(“C1”).Formula=B1/2
� R1C1-Style
� Cells(1,1).Select
� Cells(1,1).Value=10
� Cells(1,3).FormulaR1C1
� =R1C2/2
ทดสอบ Object: Event
44
แนะนาคาส�ง InputBox กบ MsgBox
• Inputbox– inputbox “กรณาปอนช�อ”– Var=inputbox (“กรณาปอนช�อ”)
• Msgbox– Msgbox “ยนดตอนรบ”– Var=msgbox (“คณตองการจบคาส�งใชหรอไม”)
45
ทดสอบคาส�ง InputBox
46
ทดสอบคาส�ง MsgBox
47
48
ConstantVariableData Type
49
เร�องของคาคงท� (Constant)Ex1. คาคงทFของ VB (Visual Basic Constant) -คาคงทFใน MsgBox ไดแก vbCritical, vbYes, vbNo-คาคงทFส Color Constant ไดแก vbRed, vbGreen, vbBlue, vbMagenta-คาคงทFส Color Index ไดแก 1-56เชน range(“a1”).font.color=vbRedเชน range(“a1”).font.color=RGB(255, 255, 255)
เชน range(“a1”).font.colorindex=3
Ex.2 คาคงทFของ Excel (Excel Constant)Selection.End(xlDown).SelectSelection.End(xlToRight).SelectSelection.End(xlUp).SelectSelection.End(xlToLeft).Select
Ex.3 คาคงทFกาหนดเอง (Define Constant)Const ช�อคาคงท� [As DataType] = คาท�กาหนด
เชน Const A = 10 เชน Const B = “VBA”
เชน Const C = #1/1/2010#
50
ชนดของขอมลมาตรฐาน (Standard Data Type)
ประเภท ชนดขอมล ขนาดหนวยความจา
String 0-2 พนลาน ตวอกษร 1 Byte/Charecter
Byte เลขจานวนเตม 0- 255 1 Byte
Integer เลขจานวนเตม +-32,767 2 Byte
Long เลขจานวนเตม +- 2 พนลาน 4 Byte
Single ตวเลขทศนยม 4 Byte
Double ตวเลขทศนยม 8 Byte
Currency เงนตราทศนยม 4 ตาแหนง 8 Byte
Object วตถตางๆ 4 Byte
Variant เกบไดทกประเภท 16 Byte
Boolean True, False 2 Byte
User Defined ผใชกาหนดเอง ผใชกาหนดเอง
51
เร�องของตวแปร (Variable)
ตวอยางทF 3Dim dteBirthDate As DatedteBirthDate =inputbox (“กรณาปอนวนเดอนปเกด”)Range(“A1”).value= dteBirthDate
ตวอยางทF 1Var=inputbox (“กรณาปอนขอมล”)Range(“A1”).value=Var
ตวอยางทF 2Dim bytAge As BytebytAge=inputbox (“กรณาปอนอายของคณ”)Range(“A1”).value=A
Dim VarVar=inputbox (“กรณาปอนขอมล”)Range(“A1”).value=A
Dim Var As VarientVar=inputbox (“กรณาปอนขอมล”)Range(“A1”).value=Var
รปแบบการประกาศตวแปรDim ชFอตวแปร [As] [DataType]รปแบบการประกาศตวแปรDim ชFอตวแปร [As] [DataType]
52
เร�องของตวแปร (Variable)ตวอยางทF 5Dim A as Byte, B as Byte
ตวอยางทF 4Dim A as ByteDim B as Byte
ตวอยางทF 7Dim strName as StringDim strName as String *30Dim bytAge as ByteDim intNum as IntegerDim dblInterestRate as DoubleDim curLoan as CurrencyDim dteStartDate as DateDim blnToggle as BooleanDim VarDim Var as VarientConst B as Integer = 100
ตวอยางทF 6Dim A, B, C As Byte
ตวอยางทF 8Dim wbk as WorkBookDim wst as WorkSheetDim rng as RangeDim slt as Selection
53
การเปล �ยนชนดของขอมล (Conversion Function)
ฟงกช Fน แปลงเปน
Cbool (ตวแปร) BooleanCInt (ตวแปร) IntegerCLng (ตวแปร) LongCbyte (ตวแปร) ByteCStr (ตวแปร) StringCcur (ตวแปร) CurrencyCdec (ตวแปร) DecimalCSng (ตวแปร) SingleCdate (ตวแปร) DateCDbl (ตวแปร) DoubleCvar (ตวแปร) Variant
Sub TestConversion()Dim A As StringDim B As VariantA = InputBox(“…”)MsgBox TypeName(A)B = CInt(A)MsgBox TypeName(B)End Sub
ตวแปร ArrayArrayArrayArrayArray ขนาด คาอธบาย
Dim a()
Dim a(2)
Dim a(2,3)
Dim a(2,3,4)
54
Sub TestArray1()Dim a(3) as Integera(1) = 20a(2) = 30a(3) = 40MsgBox a(3)
End Sub
Scope ของตวแปร
55
Project
Module1 Module2
Sub Sub1 Dim AA=1C=1End Sub
Sub Sub2 C=2D=2End Sub
Sub Sub3 D=3
End Sub
Sub Sub4 D=4
End Sub
Dim C
Option Explicit
Public D
การรบ-สงคาตวแปร (Main/Sub)
56
รบ-สง ภายใน SubSub Test1()Dim A as ByteA=10msgbox A
End Sub
รบ-สง ภายนอก Sub (1)Sub Test1()Dim A as ByteA=10call Test2( )
End Sub
Sub Test2(A As String)msgbox A
End Sub
รบ-สง ภายนอก Sub (2)Sub Test1()Dim A As ByteDim B As ByteA = 10B = 20Call Test2( )
End Sub
Sub Test2(A As Byte, B As Byte)MsgBox A + B
End Sub
การรบ-สงคาตวแปร (ByVal/ByRef)
57
ByValSub Test1()Dim A as ByteA=10call Test2( )msgbox A
End Sub
Sub Test2(ByVal A As Byte)A=A+10msgbox A
End Sub
ByRefSub Test1()Dim A as ByteA=10call Test2( )msgbox A
End Sub
Sub Test2(ByRef A As Byte)A=A+10msgbox A
End Sub
58
Error (Bug/Debug)
59
Error Type
Error Type
Syntax
Run TimeLogic
60
คาส�ง Debug Error
Sub A()OnError Resume Next
คาส Fงเดม ..............
End Sub
Sub A()OnError Goto Err_Handler
คาส Fงเดม ..............
Err_Handler
End Sub
เรยนรผช วยในการเขยนโปรแกรมและการใชงาน User Interface
• เรยนร Statement Control– Condition– Loop
• เรยนร User Interface– SheetForm– UserForm
61
62
Statement Control
IF Select Case For Next Do Loop
For Each With
63
IF 1) IF เง Fอนไข Then จรง
2) IF เง Fอนไข Then จรง
Else เทจ
End IF
64
IF (ตอ)3) IF เง Fอนไข 1 Then
จรง 1 Else IF เง Fอนไข 2
Then จรง 2 Else นอกเหนอจากน {น End IF
65
SelectSelect Case ชFอตวแปร/วตถ
Case Is เง Fอนไข 1 จรง 1Case Is เง Fอนไข 2 จรง 2Case Elseนอกเหนอจากน {น
End Select
Select CaseSelect CaseSelect CaseSelect Case AAAA
Case IsCase IsCase IsCase Is >=>=>=>=20202020
B=B=B=B=2222
Case IsCase IsCase IsCase Is >=>=>=>=10101010
B=B=B=B=1111
Case ElseCase ElseCase ElseCase Else
B=B=B=B=0000
End SelectEnd SelectEnd SelectEnd Select
66
Select (ตอ)Select CaseSelect CaseSelect CaseSelect Case AAAA
Case IsCase IsCase IsCase Is 1 1 1 1 To To To To 10101010
B=B=B=B=1111
Case IsCase IsCase IsCase Is 11 11 11 11 To To To To 20202020
B=B=B=B=2222
End SelectEnd SelectEnd SelectEnd Select
Select CaseSelect CaseSelect CaseSelect Case ช�อตวแปร/วตถ
Case IsCase IsCase IsCase Is SN To ENSN To ENSN To ENSN To EN
จรง 1
Case IsCase IsCase IsCase Is SN To ENSN To ENSN To ENSN To EN
จรง 2
End SelectEnd SelectEnd SelectEnd Select
67
Do UntilDo Until เง�อนไข คาส�ง...Loop
Dim ADo Until A = 10
A = A + 1Loop
Do คาส�ง...
Loop Until เง�อนไข
Dim ADo
A = A + 1Loop Until A = 10
68
Do WhileDo While เง�อนไข คาส�ง...Loop
Dim ADo While A <> 10
A = A + 1Loop
Do คาส�ง...
Loop While เง�อนไข
Dim ADo
A = A + 1Loop While A <> 10
69
For NextFor ตวแปร1 = Start To End
คาส Fง...Next ตวแปร1
70
For EachFor Each เดFยว In ชด
คาส Fง/เงFอนไขNext
71
With…End With
With ชFอตวแปร/วตถคาส Fง 1คาส Fง 2คาส Fง 3..........
End With
ตวอยางรปแบบ
User Interface
72
User Interface
• . . . . .... ..... .... ..... .
• . . . . .... ..... .... ..... .
• SheetForm• USERFORM
73
SheetForm
74
UserForm (………………………..)
75
ทดสอบการใชงาน User Form
76
Top Related