VBA - SQL

VBA PROGRAMMING 1. MỘT SỐ KHÁI NIỆM CƠ BẢN Macro là gì ?

 ều l ần.  ần. Để  tự động hóa các công Khi làm việc với Excel, có nh ững công việc phải lặp đi lặp l ại nhi ề việc như vậy, chúng ta có th ể sử dụng Macro và VBA trong Excel.

Đối tượng, phương thứ c và thuộc tính? VBA là ngôn ng ữ l ập trình hướng đối tượng. Điều đó có nghĩa là các thành phầ n trong Excel có  ển và can thi ệp t ới thông qua các phương thức và th ể coi là các đối tượng để chúng ta điều khi ể thuộc tính của chúng. Phương thức g ắn li ề  ền với các đối tượng. Phương thứ c thực hiện một hay nhi ều  ều hành động và không trả v ề các giá trị. Một s ố ví dụ của phương thức như phương thức select, copy, paste… M ỗi

đối tượng trong Excel đều có các đặc điểm riêng và được gọi là thuộc tính. Thuộc tính  ống như n hư phương p hương thứ th ức, thuộc tính trả v ề m ột giá trị được thi ế   ế t l ập s ẵn ho ặc do ta khai không gi ống báo. Đối tượng đang làm việ c (chứa con trỏ chuột) thì ta gọi là đối tượng Active (đang kích hoạ t) Một s ố đối  ố đối tượng cơ bả n trong VBA 

Application : Là ứng dụng Excel



Workbook : Là một file Excel



WorkSheet : Là Là bảng tính, chứa các ô, đối tượng đồ họa trong Sheet,…



Sheet



Range

: Là một ô hay kh ối ô



Cell

: Là một ô đơn lẻ



Character

: Có th ể là WorkSheet, Chart, Dialog,….

: Là ký t ự đơn lẻ trong ô

ủa phương thứ c và thuộc tính: Cú pháp chung c ủa 

Object.Method



Object.Property=Value

Sub và Function?

 ểm tra Hàm và thủ tục là hai ứng dụng chính của VBA. Để phân biệt hàm với thủ tục, chúng ta ki ể trình . Nế u từ khóa có tên Sub thì đó là thủ từ khóa có tên Function ho ặc Sub ở đầu m ỗi chương trình. tục, còn tên Function là hàm tự lập. Thủ t ục có ph ạm vi ảnh

hưởng l ớn, có th ể thay đổi thuộc tính cũng như thực hi ện các phương  ều đối tượng. Hàm t ự l ập thì chỉ thực hi ện giá trị tính toán t ại ô chứa tên hàm, là thức cho nhi ều k ế t quả của việc tính toán đố i với các tham s ố của hàm.

Page 41

VBA PROGRAMMING 2. MACRO Trong quá trình th ực hiện Macro có hai khái ni ệm: 

 

 ếu tương đối Tham chi ếu

(Use Relative Conference). Macro trong trườ ng hợp này có th ể được áp dụng đối với b ấ t kỳ ô nào, mảng nào trong Excel.  ế u tuyệt đối (mặc định) – chỉ áp dụng đối với vị trí của những ô ghi Macro. Tham chi ế  Khi tạo macro, n ế u chúng ta ch ọn lưu ở Personal Macro Workbook. Macro sẽ khởi động cùng với file Excel m ỗi khi nó khởi động.

R1C1 là gì? R là row, C là column. Ch ỉ s ố sau R, C n ếu

đặt trong móc [] là tham chi ếu  ếu tương đối. Ngượ c l ại n ế u không đặt trong móc [] là tham chi ế   ế u tuyệt đối. Tham chi ếu  ếu tương đố i sẽ tính từ vị trí được chọn. Tham chi ế   ế u tuyệt đối chỉ ra vị trí được chọn. Ví dụ: R1C1 là ô ở dòng 1, c ột 1. Còn R[1]C[1] là tăng cộ t hiện tại thêm 1 đơn vị, tăng row hiệ n tại thêm

1 đơn vị. N ếu đang ở ô R2C2 thì R[1]C[1] tham chi ếu  ếu đế n ô R[3]C[3]. MsgBox 

MsgBox “textline1” & value1 & vbNewLine & “textline2” & value2…..

MsgBox Function MsgBox Function là một hàm đầy đủ hơn hàm MsgBox đơn giả n. Ví dụ: Private Sub Sub CommandButton1_Click  CommandButton1_Click() Dim answer Dim  answer As Integer answer = MsgBox("Are you sure you want to empty the sheet?", vbYesNo + vbQuestion, "Empty Sheet") If answer If  answer = vbYes Then Cells.ClearContents Else 'do nothing End If End Sub

 ển thị được gán với 1 bi ế   ế n answer. N ế u bi ế   ế n nhận được có giá trị Trong ví dụ trên, MsgBox sẽ hi ể vbYes thì xóa toàn b ộ Sheet. Ngược lại thì không làm gì.

Cú pháp t  ổ ng ng quát c ủa MsgBox Function: MsgBox (promt [, buttons] [,title] ) Trong đó: promt: Nội dung lời nhắc của thông báo buttons: Nút điều khiển như Yes, No, Cancel,... title: Tiêu đề hộp thông báo buttons gồm nhiều button, các buttons ta sử dụng phép + ví dụ như vbYesNo + vbQuestion

Page 42

VBA PROGRAMMING Một số buttons hay sử dụng - vbOKOnly : (vbOK) - vbOKCancel : (vbOK, vbCancel) - vbYesNo : (vbYes, vbNo) - vbYesNoCancel : (vbYes, vbNo, vbCancel) - vbRetryCancel : (vbRetry, vbCancel) - vbCritical, vbQuestion, vbInformation, vbExclamation

InputBox Function

Hàm InputBox được sử dụng để nh ắc người dùng nhập vào một giá trị. Ví dụ: Dim myValue As Variant 'Biến Variant có thể là bất kỳ kiểu giá trị nào MyValue = InputBox ("Cho tôi một số đầu vào")

Cú pháp: InputBox(promt [,title] [,default] ) - default là giá trị mặc định được nhập

3. ĐỐI TƯỢNG WORKBOOK VÀ WORKSHEET Ví dụ: 'Using the worksheet name. Worksheets("Sales").Range("A1").Value = "Hello" 'Using the index number (1 is the first worksheet starting from the left). Worksheets(1).Range("A1").Value = "Hello" 'Using the CodeName. Sheet1.Range("A1").Value = "Hello"

Close and Open Workbooks("close-open-workbooks.xls").Close Workbooks(1).Close ActiveWorkbook.Close 'Đóng workbook đang active Workbooks.Close ' Đóng tất cả workbooks

'Mở workbook Workbooks.Open ("sales.xls") ' Hoặc Dim MyFile As String MyFile = Application.GetOpenFilename()

4. ĐỐI TƯỢNG RANGE Gán hoặc l ấ y giá trị cho 1 ô Range("B3").Value = 2 ' Range("A1:A4").Value = 5 ' Range("A1:A2,B3:C4").Value = 10 ' Range("Prices").Value = 15 ' Cells(3, 2).Value = 2 Range(Cells(1, 1), Cells(4, 4)).Value

Ô B3 Mảng: A1-->A4 Mảng A1-->A2 & B3-->B4 Ô hoặc mảng, có range name là Prices 'Ô ở  hàng 3, cột 2 = 5 ' Mảng từ A1-->D4

Page 43

VBA PROGRAMMING Khai báo một đối tượng range Dim example As Range Set example = Range("A1:C4") example.Value = 8

Phương thức Select – lựa chọn. Worksheets(3).Activate Worksheets(3).Range("B7").Select

Lưu ý: Trước khi sử dụng Sheets hay Workbooks nào chúng ta c ần phải Activate nó. Thuộc tính rows() và columns() n ằm trong đối tượng range.

Phương thức Copy, Paste, Clear Range("A1:A2").Select Selection.Copy Range("C3").Select ActiveSheet.Paste Range("A1").ClearContents 'Hoặc Range("A1").Value = ""

Thuộc tính count dùng để đế m s ố  ô, cột, dòng c ủa 1 range Thuộc tính CurrentRegion : Thuộc tính này trả v ề phạm vi (một range) bao quanh một range nào

đó. Thuộc tính Resize : Thay đổi kích thướ c của một range thành một kích thước khác. Range("A1:C4").Resize(3, 2).Select 'Thay đổi kích thước từ 4x3 thành 3x2

Lựa chọn Entire Row và Column EntireRow 'Hàng chứa ô đang activate (activeCell) EntireColumn 'Cột chứa ô đang activate ActiveCell.EntireColumn.Cells(5).Value = 2

Thuộc tính Offset(m,n):  Dịch chuy ển range, cell xu ống dưới m hàng, và qua ph ải n cột. Từ Active Cell đế n ô cu ố i cùng trong ph ạm vi: ActiveCell.End(xlDown) ActiveCell.End(xlUp) ActiveCell.End(xlToRight) ActiveCell.End(xlToLeft)

'Ô 'Ô 'Ô 'Ô

cuối cùng trong cột của range đầu tiên của cột của range cuối cùng của hàng của range đầu tiên của hàng của range

Chúng ta có thể sử dụng ô này và ô ActiveCell để lựa chọn range: Range(ActiveCell, ActiveCell.End(xlDown)).Select

Phương thức Union (Phép h ợp) và Intersect (Phép giao) Union(Range("B2:C7"), Range("C6:F8")).Select Intersect(Range("B2:C7"), Range("C6:F8")).Select

Page 44

VBA PROGRAMMING Đối tượng Selection: Sau khi một đối tượng được chọn b ằng phương thức Select, hoặc một phạm vi đang được bôi đen, có dấ u trỏ chu ột thì đối tượng đó đượ c gọi là đối tượ ng Selection. Chúng ta có th ể sử dụng các phương thức, thuộc tính đối với đối tượng này.

5. KHAI BÁO VÀ SỬ DỤNG BIẾ N 5.1. Khai báo biế n Dim x Dim x Dim x Dim x

As As As As

Integer String Double Boolean

'Kiểu 'Kiểu 'Kiểu 'Kiểu

nguyên chuỗi số thực True or False

Từ khóa: Option Explicit

Nên đặt từ khóa này ở đầu chương trình. Từ khóa này được sử dụng yêu c ầu các bi ế n trong VBA phải được khai báo trước khi sử dụng. N ế u không nó sẽ báo l ỗi. Bi ế n h ằng Khi bi ế n là giá trị không thay đổi thì chúng ta nên thi ế t l ập ki ểu h ằng s ố cho bi ế n. Bi ế n h ằng khi

thay đổi sẽ nhận được thông báo l ỗi. Const Name As DataType = Value

Bi ến đối tượng Bi ến đối tượ ng là các bi ến như range,….Để gán giá trị cho bi ến đối tượng ta sử dụng từ khóa set: Set VarName = Object

5.2. Phạm vi hoạt động của bi ế n  ế n g ồm 3 mứ c. Trong VBA phạm vi hoạt động c ủa bi  

Mức 1: Phạm vi hoạt động trong 1 Sub hay Proccedure.



Mức 2: Phạm vi hoạt động trong 1 Module



Mức 3: Phạm vi hoạt động trong nhi ều Module

Khai báo trong Sub or Function: 

Khi một bi ến

được khai báo b ằng từ khóa Dim trong Sub ho ặc Function thì phạm vi hoạt động của bi ến đó nằ m trong Sub ho ặc Function được khai báo. Chúng ta không th ể gọi bi ế n này trong Sub hoặc Function khác. Giá tr ị của bi ế n sẽ bi ế n m ấ t khi k ế t thúc Procedure hoặc Function.



Bi ế n Static khai báo m ột bi ế n bên trong một thủ tục. Và chỉ có phạm vi hoạt động bên trong thủ tục đó. Giá trị của bi ế n không m ấ t khi k ế t thúc Procedure.

Bi   ế n dùng chung - Khai báo trên đầ u module: 

Khai báo b ằng Dim hoặc Private ==> Phạm vi hoạt

động của bi ế n là trong toàn bộ module

chứa nó. Giá trị của bi ế n không m ấ t khi k ế t thúc Sub or Function 

Khai báo b ằng Public ==> Phạm vi hoạt

động của bi ế n là t ấ t cả  các module trong một

workbook.

Page 45

VBA PROGRAMMING Public và Private đố i v ới  Sub: 

Khi một Sub có từ  khóa Public hoặc không có t ừ  khóa nào. T ấ t cả  các module trong



workbook đều có th ể gọi Sub này. Khi một Sub có từ khóa Private. Phạm vi hoạt động của Sub này là trong module ch ứa nó.

6. C ẤU TRÚC ĐIỀU KHIỂN IF - THEN 1.1. C ấ u trúc IF Dạng đơn giản: If <điều kiện 1> Then ' => IF condition is validated, THEN 'khối lệnh 1 'Nếu điều kiện 1 đúng thực hiện khối lệnh 1 Else ' => OTHERWISE 'Khối lệnh 2 'Nếu điều kiện 1 sai thực hiện khối lệnh 2 End If Dạng t   ổ ng quát: If <điều kiện 1> Then ' => IF condition is validated, THEN 'Khối lệnh 1 'Nếu điều kiện 1 đúng thực hiện khối lệnh 1 ElseIf <điều kiện 2>  'Khối lệnh 2 'Nếu điều kiện 1 sai thực hiện khối lệnh 2 ElseIf <điều kiện 3>  'Khối lệnh 3 'Nếu điều kiện 1&2 sai thực hiện khối lệnh 3 ElseIf <điều kiện 4>  'Khối lệnh 4 ……………… Else ' => OTHERWISE 'Khối lệnh cuối 'Nếu tất cả các điều kiện trên sai thực hiện khối lệnh cuối End If

1.2. C ấ u trúc Select Case Select Case   Case      Case < Giá trị kiểm tra 2>     Case      …… Case Else    End Select

1.3. Logic: And, Or  <điều kiện 1> And <điều kiện 1>   <điều kiện 1> Or <điều kiện 1>  Not  <điều kiện> 

Page 46

VBA PROGRAMMING 7. VÒNG L ẶP 7.1. Vòng lặp DO…LOOP Do    If <  Điều kiện thoát vòng lặp> Then Exit Do

Loop 'Tiếp tục lặp

7.2. Vòng lặp DO WHILE….LOOP Do While <  Điều kiện lặp>    

'Thực hiện khối lệnh khi điều kiện lặp đúng 'Hành động lặp lại cho đến khi điều kiện lặp sai 'Thì kết thúc vòng lặp 'Điều kiện lặp được kiểm tra ở đầu vòng lặp Loop 'Tiếp tục lặp

7.3. Vòng Lặp DO…LOOP WHILE Do   

'Tương tự vòng lặp Do While…Loop 'Tuy nhiên điều kiện lặp được kiểm tra ở cuối vòng lặp Loop While <  Điều kiện lặp> 

7.4. Vòng lặp DO UNTIL…LOOP VÀ DO…LOOP UNTIL DO UNTIL…LOOP VÀ DO…LOOP UNTIL phương thức ho ạt động gi ống như DO WHILE…LOOP VÀ DO…LOOP UNTIL. Điểm khác duy nh ất là UNTIL(cho đế n khi) thực hiện vòng lặp khi điều kiện lặp sai. Thoát khỏi vòng lặp khi điều kiện lặp đúng.

7.5. Vòng lặp FOR…NEXT For  = <  [Step ]  Điểm đầu> To <  Điểm cuối>     Next [  ] 'Bướ c nhảy có thể là giá trị dươ ng hoặc âm 'Nếu không có hay bỏ Step thì VBA sẽ mặc định bước nhảy là 1

7.6. Vòng lặp FOR EACH…NEXT For Each  In      Next  

' Phải khai báo kiểu dữ liệu của Phần_tử trước khi sử dụng. ' Ví dụ In Range thì Phần_tử cũng phải kiểu Range.

7.7. Lệnh thoát EXIT  Exit Do: Thoát kh ỏi vòng lặp Do  Exit For: Thoát kh ỏi vòng lặp For  Exit Sub: Thoát khỏi thủ tục Sub  Exit Function: Thoát kh ỏi Function

Page 47

VBA PROGRAMMING 8. MACRO ERRORS Một s ố  thủ thuật xử lý l ỗi: 

Step Into: F8



Break Point



Run & Reset

9. STRING Một s ố  hàm xử lý chu ỗi: 

N ối chu ỗi: &



Left(str,number)



Right(str,number)



Mid(str, start_number,number)



Len(str)



Instr(str, find_text): Hàm tìm ki ế m

10. ARRAY 11.1. Mảng c ố định Mảng c ố định là mảng không thay đổi được s ố ph ần t ử của mảng. Mảng động là mảng có th ể thay đổi được s ố ph ần tử của mảng. Khai báo: Option Base 1 Cụm từ này ý mu ốn nói ph ần tử đầu tiên của mảng là 1 chứ không phải là 0. Dim Mangrong() As String Dim MyFriend(1 to 30) As String Dim NoiSuy(1 to 20, 1 to 30) As Single Dim Array(“Michael”, “David”) trong đó, kiểu dữ liệu là Variant UBound(mảng, 1) UBound(mảng, 2) Lbound(mảng, 1) và Lbound(mảng, 2) MyFriend(1) NoiSuy(i,j)

' Mảng MyFriend một chiều ' Mảng NoiSuy 2 chiều ' Hàm Array tạo mảng chứa các biến bên 'Trả về chỉ số trên của hàng của mảng 'Trả về chỉ số trên của cột của mảng

' Phần tử thứ nhất của mảng ' Phần tử hàng i, cột j của mảng

11.2. Mảng động Trong quá trình t ạo m ảng, có nhi ều trườ ng h ợp c ần thay đổi s ố lượng ph ần t ử trong mảng đó. Để làm việc đó ta có thể sử dụng từ khóa ReDim hoặc ReDim Preserve để thay đổi lại kích thước của m ảng. Từ khóa ReDim khởi t ạo l ại giá trị cho mảng, toàn bộ ph ần t ử trong mảng hiện hành bi ế n m ấ t. Dim Arr(4) As String ReDim Arr(6) As String ReDim Preserve Arr(6) As String

Page 48

VBA PROGRAMMING Đối với những dòng dữ liệu ng ắn chúng ta có th ể d ồn chúng thành một dòng b ằng cách thêm từ khóa “:” ở giữa chúng. Arr(1) = "Tháng 1" Arr(2) = "Tháng 2" Arr(1) = "Tháng 1" : Arr(2) = "Tháng 2"

11. BIẾN NGƯỜI DÙNG Chúng ta có th ể sử dụng Type để khai báo bi ến đối

tượng cho riêng mình. Ví dụ bi ến đối tượng của chúng ta là ThongTin. Trong đó ThongTin có các thuộ c tính là Ten, DiaChi, Tuoi. Chúng ta khai báo như sau: Type ThongTin Ten As String DiaChi As String Tuoi As Integer End Type

Ví dụ: 'Creation of a variable type Type guests last_name As String first_name As String End Type Sub variables() 'Declaration Dim p1 As guests 'Assigning values to p1 p1.last_name = "Smith" p1.first_name = "John" 'Example of use MsgBox p1.last_name & " " & p1.first_name End Sub

12.WITH…END WITH With…End With giúp chúng ta có thể  giảm việc lặp đi lặp lại những cú pháp gi ống nhau. Ví dụ: Sub properties() ActiveCell.Borders.Weight = 3 ActiveCell.Font.Bold = True ActiveCell.Font.Size = 18 ActiveCell.Font.Italic = True ActiveCell.Font.Name = "Arial" End Sub

Page 49

VBA PROGRAMMING Khi sử dụng With…End With chúng ta có chương trình tương đương chương trình trên: Sub properties() 'Beginning of instructions using command: WITH With ActiveCell .Borders.Weight = 3 .Font.Bold = True .Font.Size = 18 .Font.Italic = True .Font.Name = "Arial" 'End of instructions using command: END WITH End With End Sub

Một ví dụ khác: Sub properties() With ActiveCell .Borders.Weight = 3 With .Font .Bold = True .Size = 18 .Italic = True .Name = "Arial" End With End With End Sub

13.SUB AND FUNCTION Function Func_Name(x As Double, y As Double,.....) As Double ............ ............ Func_Name=....

End Function Sub Func_Name(x As Double, y As Double,....) End Sub

Function sẽ tr ả v ề m ột giá trị khi chúng ta g ọi hàm, trong khi đó Sub chỉ  th ực hi ện m ột lo ạt các

hành động khi chúng ta g ọi Sub và không tr ả v ề giá trị. ByRef và ByVal ByRef là giá trị mặc định của VBA vì vậy chúng ta chỉ c ần quan

tâm đế n ByVal là đủ. Khi khai báo bi ế n, thực hiện tính toán trong m ột s ố hàm hoặc thủ tục giá trị của bi ế n có th ể thay đổi. Tuy nhiên n ế u chúng ta không mu ốn thay đổi các giá trị này, chúng ta có th ể sử dụng ByVal trong Function hay Procedure. Khi đó VBA sẽ  tạo ra một bản sao của bi ến để  thực hiện tính toán. Còn giá trị g ốc thì v ẫn giữ nguyên. Ví dụ như: Func_Name(Byval x As Double...)

Page 50

VBA PROGRAMMING 14. ActiveX CONTROLS VÀ USERFORM Sử dụng các hàm c ủa Excel trong VBA Gọi hàm trong Excel trong VBA: worksheetFunction.Vlookup

14.1. Form controls  Input range: Nơi chứa giá tr ị truy ền vào các đối tượ ng trong form controls  Cell link: Nơi nhận giá tr ị truy ền từ các đối tượng trong form controls  3-D shading: Tạo định dạng 3-D cho các đối tượ ng 14.2. ActiveX Controls TextBox 

TB_Name.Value

ListBox & Combo Box 

LinkedCell: Nơi nhận giá trị truy ền từ ListBox



ListFillRange: Mảng chứa giá trị truy ền vào ListBox



Code: LB/CB_Name.AddItem “Value”



Code: LB/CB_Name.RemoveItem



Code: LB/CB_Name.Clear



Code: LB/CB_Name.List(i)/.ListIndex – Chỉ s ố của mục đang được selection



Code: LB/CB_Name.ListCount



List Box và Combo Box b ắt đầu b ằng chỉ s ố 0.

Check Box 

CheckBox_Name.Value = True or False

Option Buttons 

OptionButton_Name.Value = True or False



Group Name:

Spin Button 

SpinButton_Name.Value



SpinButton_Name.Max



SpinButton_Name.Min



SpinButton_Name.SmallChange

14.3. Userform Tạo một Userform 

Insert --> Userform



Sử dụng các Toolbox controls để thi ế t k ế  giao diện

  



Đặt name cho các đối tượng Đặt Caption: Nh ững gì hi ển thị ra màn hình Vi ế t code liên k ết các đối tượng và bảng tính Show Modal: Vừa làm việc trên Userform, vừa làm việc trên sheet

Page 51

VBA PROGRAMMING Set giá trị cho các đối tượng trong Userform (Initialize) 

Userform --> Sub Initialize()



Textbox.Value=””



ListBox.Clear



ListBox.AddItem “Value”



ComboBox.Clear



ComboBox.AddItem “Value”



CheckBox.Value = True or False



OptionButtons = True



Object_đặt_trỏ _chuột.SetFocus

Ẩn – Hiện – Hủy Userform  Hiện Userform: Userform_name.show (Gọi l ần đầu)  Gọi Userform: Call Userform_name_initialize() (Gọi lại)  Ẩn Userform: Userform.hide  Đóng Userform: Unl oad Me RefEdit – Hộp thoại get a range từ sheet



Userform_name.RefEdit_name.Text (cài đặt ở initialize)



RefEdit_Name.Value

Page 52

VBA PROGRAMMING Multiple List Box Selections

       

ListBox_name.MultiSelect = 1, 2, 3,…. ListBox_name.ListCount: Số lượng phần tử trong ListBox ListBox_name.Selection(i) = True/False: Phần tử đang được lựa chọn hay không ListBox_name.List(i): Giá trị của phần tử thứ i ListBox_name.Value: Giá trị của phần tử đang được chọn ListBox_name.AddItem “Value”: Thêm phần tử ListBox_name.RemoveItem : Xóa phần tử (i-1) ListBox_name.ListIndex: Phần tử đang được selection có index là

Multiple Combo Box Selections

   

Tạo 1 mảng & code: ComboBox_Name.List = Mảng ComboBox_Name.Value : Giá trị được chọn trong cột đầu tiên. ComboBox_Name.Column(n): Giá trị trong cộ t (n+1) Khởi tạo giá trị CB1 trong initialize, code giá trị CB2 trong CB1_change

Page 53

VBA PROGRAMMING Dependent Combo Boxes

Multiple Pages



Image_name.Picture = LoadPicture(“Path”)

Page 54

VBA PROGRAMMING

15. B ẪY LỖI TRONG EXCEL Sub procedure_name() 'Khai báo Dim 'Thiết lập xử lý lỗi TryAgain: On Error GoTo BadEntry ' Code here Exit Sub

'Thông báo lỗi nếu xảy ra lỗi BadEntry: Ans = Msg... 'Thử lại chọn Yes If Ans = vbYes Then Resume TryAgain End Sub

16.LÀM VIỆC VỚI M ẢNG 16.1. Khai báo mảng tĩnh Dim arr_name(n) Dim arr_name(1 to n) Dim arr_samples(m, n) Option Base 1

' Mảng 1 chiều, bắt đầu từ 0 ' Mảng 1 chiều, bắt đầu từ 1 ' Khai báo mảng 2 chiều

' Bắt đầu index = 1

16.2. Khai báo kiểu biến của mảng Dim arr_name(Lower to Upper) As Arr_type ' Lower là cận dưới ' Upper là cận trên

16.3. Kích thước mảng LBound(ArrName, 1|2) UBound(ArrName, 1|2)

'L là cận dưới 'U là cận trên '1 là hàng, 2 là cột

16.4. Mảng động

Chúng ta sử dụng mảng động khi không biết kích thước cụ thể của mảng. Đầu tiên chúng ta khai báo mảng trước, sau đó khi đã biết được kích thước của mảng rồi chúng ta sẽ khai báo nó sau: ' Khai báo mảng động Dim Arr1(), Arr2() 'Khai báo khi biết kích thước Redim Arr1(n), Arr2(1 to n, 1 to m) ' Khi dùng Redim thì các phần tử trong mảng sẽ bị xóa ' Nếu không muốn xóa thì dùng Preserve để khai báo Redim preserve Arr1(n)

' Nếu sử dụng Preserve thì không thể sử dụng Redim để thay đổi số phần tử của mảng

Page 55

VBA PROGRAMMING 16.5. Chú ý khi làm việc với mảng

Khi khai báo mảng động trong VBA thì mặc định là mảng hai chiều. Khi gán giá trị của mảng bởi Range thì mặc định là mảng hai chiều.

Khi khai báo mảng một chiều trong VBA. Mặc định mảng này có cấu tạo theo chiều ngang. Nên khi chuyển mảng này qua Excel, nếu chuyển theo chiều ngang thì được kết quả như ý muốn, ngược lại nếu chuyển kết quả theo chiều dọc thì tất cả các ô đều có cùng giá trị và đó là giá trị đầu tiên trong mảng. 16.6. Truyền giá trị từ worksheet vào VBA Module

Có hai cách để truyền giá trị từ worksheet vào VBA Module. Cách thứ nhất là sử dụng vòng lặp để lấy giá trị của mỗi ô và lưu giá trị vào các thành phần của một mảng. Cách thứ hai là gán một mảng bởi worksheet range. Khi đó mảng trở thành mảng hai chiều.  Và chỉ số của mảng bắt đầu bằng 1 cho dù có khai báo Option Base là gì đi nữa. Tuy nhiên mảng này chỉ trỏ đến mảng trên worksheet chứ không phải là gán giá trị trực tiếp. Nhưng ta có thể sử dụng các chỉ số như đối với mảng. 16.7. Truyền giá trị từ một mảng VBA một chiều qua Worksheet

Như đã nói ở trên, mảng một chiều trong VBA có cấu tạo theo chiều ngang. Nên khi chuyển qua Worksheet ta cần chú ý. Chuyển mảng một chiều ra một hàng trên worksheet: Sub HocMang() Dim Mang(10) Dim i As Integer Dim j As Integer For i = 1 To 10 Mang(i) = Range("A" & i) Next i Range("C4:L4") = Mang End Sub

Chuyển mảng một chiều ra một cột trên worksheet: Thử thay đoạn code trên: Range("C4:L4") kết quả. Tất cả các ô đều cùng giá trị.

= Mang thành Range("C1:C10") = Mang và xem

Sub HocMang() Dim Mang(10) Dim i As Integer Dim j As Integer For i = 1 To 10 Mang(i) = Range("A" & i) Next i Range("C1:C10") = Mang End Sub

Page 56

VBA PROGRAMMING Cách thứ nhất: Sử dụng vòng lặp. Option Explicit Option Base 1 Sub HocMang() Dim Mang(10) Dim i As Integer Dim j As Integer For i = 1 To 10 Mang(i) = Range("A" & i) Next i For j = 1 To 10 Range("B" & j) = Mang(j) Next j End Sub

Cách thứ hai: Sử dụng mảng hai chiều Cách thứ ba: Sử dụng Resize

Đối với cách này, ta tạo một biến có kiểu Range sau đó sẽ gán biến đó bởi ô đầu tiên trong range đầu ra trong worksheet. Sau đó sử dụng thuộc tính Resize để mở rộng mảng này. Sub HocMang() Dim Mang(10) Dim i As Integer Dim j As Integer Dim Out As Range Set Out = Range("B1") For i = 1 To 10 Mang(i) = Range("A" & i) Next i Out.Resize(UBound(Mang), 1) = Application.Transpose(Mang) End Sub

Cách thứ tư: Sử dụng Transpose Sub HocMang() Dim Mang(10) Dim i As Integer Dim j As Integer For i = 1 To 10 Mang(i) = Range("A" & i) Next i Range("C1:C10") = Application.Transpose(Mang) End Sub

Page 57

VBA PROGRAMMING 16.8. Truyền giá trị từ một mảng VBA hai chiều qua Worksheet

Cách 1: Gán trực tiếp Sub TestArray() Dim Mang1(10, 10) Dim i As Integer For i = 1 To 10 Mang1(i, 1) = Range("A" & i).Value Mang1(i, 2) = Range("B" & i).Value Next i Range("C1:D10").Value = Mang1 End Sub

Cách 2: Sử dụng hàm transpose như trên Sub TestArray() Dim Mang1(10, 10) Dim Out As Range Set Out = Range("C1") Dim i As Integer For i = 1 To 10 Mang1(i, 1) = Range("A" & i).Value Mang1(i, 2) = Range("B" & i).Value Next i Out.Resize(UBound(Mang1, 1), UBound(Mang1, 2)).Value = Mang1 End Sub

16.9. Hàm tự định nghĩa Chúng ta nói m ột ít v ề

hàm người dùng vì ph ần này có liên quan đế n v ấn đề mảng chúng ta

đang nói ở trên. Hàm (function) có th ể hi ểu đơn giản như sau: Hàm là một

chương trình máy tính (Computer Program) để  tính toán và trả v ề một giá trị cho (các) ô mà bạn nhập vào. Khi vi ế t một hàm người dùng chúng ta nên xác đị nh ki ểu bi ế n truy ền cho hàm. Ví dụ: Function MoIW(Formula As String, Decimals As Integer)

N ế u tham s ố truy ền không đúng kiể u, một thông báo l ỗi #Value!  sẽ được trả v ề.

Tương tự ở trên chúng ta cũng nên xác đị nh ki ểu trả v ề của hàm người dùng. Function MoIW(Formula As String, Decimals As Integer) As Double

Để trả v ề một giá trị l ỗi từ một hàm thông thường chúng ta vi ết như sau: If(TimThayLoi) Then TenHam=”Thong bao loi”: Exit Function

Nhưng đây không phải là cách t ốt nh ất để  xử lý (handle) l ỗi. Chúng ta dùng CVErr(Giá tr ị l ỗi) để trả v ề giá trị l ỗi worksheet của Excel mà Excel có th ể xử lý. If(TimThayLoi) Then TenHam=CVErr(xlErrNA): Exit Function

Page 58

VBA PROGRAMMING Đối với một s ố hàm, một s ố tham s ố không b ắt buộc truy ền vào cho hàm. Đối với trường h ợp này chúng ta s ẽ dùng từ khóa Optional để khai báo. Function MoIW(Formula As String, Optional Decimals As Integer) As Double

Để ki ểm tra người dùng có nh ập vào bi ế n này hay không chúng ta có th ể dùng IsMissing để ki ểm tra. 16.10. Làm việc với mảng trong hàm

Range được truyền vào hàm và có thể được sử dụng như một mảng: Nếu một đối số Range được truyền vào hàm, Range có thể được xử lý như mảng trong VBA không cần khai báo. Truyền đối số vào hàm: Xét hàm Sum: SUM(“A1:A10”, “B3:C5”)

Trong hàm trên, các đối số vào có thể là không xác định. Chúng ta sẽ nói về khai báo các đối số như vậy. Để khai báo đối số không xác định ta sử dụng: ParamArray Var_name()

Trong một hàm chỉ có một đối số được khai báo như trên và phải là đối số được khai báo cuối cùng trong hàm. Var_name() là một mảng các biến. Mảng này luôn luôn bắt đầu bằng chỉ số 0 dù có khai báo Option Base là 0 hay 1 đi nữa. Ví dụ: Function ArrayMaker(ParamArray rng()) For J = 0 To UBound(rng) YSize = rng(J).Columns.Count For K = 1 To YSize statements Next K Next J

Hàm trả về một mảng các giá trị: Function thunghiem() Dim Arr(3) Arr(1) = "MyLove" Arr(2) = "Chan nhi" Arr(3) = "Buon qua" thunghiem = Arr End Function Function thunghiem() thunghiem = Array("MySlope", "My Intercept", "MyRSq") End Function

.

Page 59

BASIC SQL 1. MAINUPULATION 1.1. Ki ểu dữ liệu trong SQL  Integer: Số nguyên  Text: Kí tự  Date: YYYY – MM – DD  Real: Số thực 1.2. Tạo bảng CREATE TABLE  table_name( column_1 data_type, column_2 data_type, column_3 data_type, column_4 data_type, );

1.3. Thêm dữ liệu vào bảng INSERT INTO table_name(colum1, colum2,..)  VALUES (value1, value2,...);

1.4. Update dữ liệu UPDATE  table_name SET column = value  WHERE conditional;

1.5. Thêm cột vào bảng  ALTER TABLE  table_name ADD COLUMN column_name data_type;

1.6. Xóa hàng  table_name WHERE DELETE FROM  column_conditional;

2. SELECTING COLUMNS 2.1. SELECT FROM SELECT  Columns_names_1 As Output_Name, Columns_names_2 As Output_Name,...   table_names As tb_name FROM  LIMIT  n;

-- Dấu ; báo hiệu kết thúc một truy vấn -- Đôi khi có thể sử dụng từ khóa GO -- Để chọn toàn bộ sử dụng SELECT * -- LIMIT n là giới hạn n dòng kết quả đầu ra

2.2. DISTINCT SELECT DISTINCT FROM  ;

-- DISTNICT trả về các kết quả không trùng lặp

Page 60

BASIC SQL 2.3. COUNT SELECT DISTINCT FROM  ;

-- DISTNICT trả về các kết quả không trùng lặp

2.4. COUNT DISTINCT SELECT DISTINCT FROM  ;

-- DISTNICT trả về các kết quả không trùng lặp

3. FILTERING ROWS 3.1. Câu lệnh t ổng quát SELECT FROM   WHERE conditional GO

3.2. Các toán tử so sánh = : equal  <> : Not equal  < : less than >  : greater than  <= : less than or equal to >= : greater than or equal to

3.3. AND, OR, BETWEEN - AND  AND, OR  , BETWEEN - AND

condtional AND conditional  conditional) (conditional OR  Columns BETWEEN...  AND... -- BETWEEN AND trả về kết quả bao gồm cả điểm đầu và cuối -- Mệnh đề OR nên để trong ngoặc

3.4. IN

Toán tử trả về kết quả nếu giá trị tồn tại trong một list nào đó . Ví dụ: SELECT  title, language  films FROM   WHERE language IN ('English', 'Spanish', 'French') GO

3.5. NULL and IS NULL NULL là giá trị missing hoặc unknow values . NULL có thể được sử dụng trong mệnh đề WHERE

như IS NULL và IS NOT NULL.

Page 61

BASIC SQL 3.6. LIKE and NOT LIKE

Toán tử LIKE và NOT LIKE được sử dụng trong mệnh đề WHERE đối với các giá trị text mà không biết chính xác text đó và thay vào đó là sử dụng các toán tử thay thế text như % hay _ . % được sử dụng để thay thế cho nhiều ký tự. _ được sử dụng để thay thế cho một ký tự. Ví dụ: select  name  people from   where name like '_r%'

4. AGGREGATE FUNCTIONS 4.1. Cú pháp t ổng quát SELECT FUNCTION(colums) FROM TABLE  WHERE conditional

-- Các hàm như AVG, MAX, MIN, SUM, ROUND(column, n) – làm tròn số

4.2. Tính toán với Select SELECT (statement) As output_name

-- Chú ý: -- 4/3 cho kết quả là 1 -- 4.0/3.0 cho kết quả là 1.333

5. SORTING, GROUPING AND JOIN 5.1. ORDER BY SELECT FROM   WHERE ORDER BY colums DESC,...

-- DESC giảm -- Mặc định là tăng

5.2. GROUP BY SELECT -- Bước 5: Trả về kết quả FROM  -- Bước 1:  WHERE -- Bước 2: Kiểm tra mệnh đề where GROUP BY -- Bước 3: Gom nhóm HAVING  func(colums) conditional -- Bước 4: Kiểm tra mệnh đề having

5.3. JOIN SELECT  table_1 AS tb1 JOIN table_2 AS tb2 FROM  ON tb1.FK = tb2.PK  WHERE

Page 62

BASIC SQL 6. MULTIPLE TABLES 6.1. Tạo khóa CREATE TABLE  table_name( column_1 data_type, ..., column_n data type PRIMARY KEY);

-- Tạo bảng với khóa chính CREATE TABLE  table_name( column_1 data_type, ...,

column_n data_type, CONSTRAINT  PK_table_name PRIMARY KEY column_name); GO -- Tạo khóa phụ  ALTER TABLE  table_name  ADD CONSTRAINT  FK_table1_table2 FOREIGN KEY () REFERENCES  references_table_name () GO

6.2. Truy v ấ n SELECT  tb1.column, tb2.column,…  table_1 AS tb1 JOIN table_2 AS tb2 FROM  ON tb1.FK = tb2.PK  WHERE

Ngoài JOIN còn có LEFT JOIN và RIGHT JOIN. LEFT JOIN sẽ lấy toàn bộ bảng bên trái và những giá trị nào trong bảng bên phải không thỏa mãn sẽ để NULL. Tương tự với RIGHT JOIN.

7. TRUY V Ấ N LỒNG 7.1. Truy v ấ n l ồng bên trong mệnh đề where -- Ví dụ SELECT *  flights FROM   WHERE origin in ( SELECT  code  airports FROM   WHERE elevation < 2000);

-- Truy vấn lồng là các truy vấn lồng nhau -- Truy vấn con có thể nằm ở nhiều vị trí khác nhau -- Phổ biến nhất là nằm trong mệnh đề WHERE

Truy vấn lồng gồm hai loại là truy vấn lồng phân cấp và truy vấn lồng tương quan. Đối với truy vấn lồng phân cấp, thì truy vấn con và truy vấn cha độc lập với nhau. Ví dụ trên là một ví dụ về truy vấn lồng phân cấp. Truy vấn lồng tương quan là truy vấn mà truy vấn con và truy vấn cha có liên quan đến nhau. Truy vấn con sử dụng thuộc tính bên trong truy vấn cha.

Page 63

BASIC SQL Chú ý toán tử NOT IN không lấy giá trị NULL. 7.2. Truy v ấ n l ồng bên trong m ệnh đề from

Ví dụ: SELECT  a.dep_month, a.dep_day_of_week,  AVG(a.flight_count) AS average_flights FROM ( SELECT  dep_month, dep_day_of_week, dep_date, COUNT(*) AS flight_count  flights FROM  GROUP BY 1,2,3 ) AS a GROUP BY 1,2 ORDER BY 1,2;

7.3. Truy v ấ n l ồng tương quan Truy v ấ n  lồng tương quan sẽ  lặp qua từng dòng trong truy v ấ n cha. Ứng với từng dòng đó nó sẽ lặp lại truy v ấ n con và trả v ề các dòng nào th ỏa mãn điều kiện. Ví dụ: SELECT  id  flights AS f FROM   WHERE distance < ( SELECT AVG(distance)  flights FROM   WHERE carrier = f.carrier);

8. PHÉP HỢP, GIAO VÀ PHÉP TRỪ 8.1. UNION – PHÉP HỢ P, BỎ QUA NHỮNG DÒNG GI ỐNG NHAU  table1 SELECT  column_name(s) FROM  UNION  table2; SELECT  column_name(s) FROM  -- Ví dụ:   legacy_products SELECT  brand FROM  UNION   new_products; SELECT  brand FROM 

8.2. UNION ALL – PHÉP HỢ P, LẤY H Ế T  table1 SELECT  column_name(s) FROM  UNION ALL  table2; SELECT  column_name(s) FROM  -- Ví dụ SELECT  id, avg(a.sale_price) FROM (   order_items SELECT  id, sale_price FROM  UNION ALL   order_items_historic) AS a SELECT  id, sale_price FROM  GROUP BY 1;

Page 64

BASIC SQL 8.3. INTERSECT – PHÉP GIAO L ấ y các ph ần tử trùng nhau ở hai bảng

8.4. EXCEPT – PHÉP TRỪ L ấ y các ph ần tử n ằm trong bảng thứ nh ấ t không n ằm trong bảng thứ hai

9. CONDITIONAL AGGREGATE 9.1. SELECT - CASE -- Ví dụ SELECT CASE  WHEN elevation < 500 THEN 'Low'  WHEN elevation BETWEEN 500 AND 1999 THEN 'Medium'  WHEN elevation >= 2000 THEN 'High' ELSE 'Unknown' END AS elevation_tier , COUNT(*)  airports FROM  GROUP BY 1;

-- Ví dụ 2, CASE WHEN trong function SELECT  state_name, COUNT(CASE WHEN elevation >= 2000 THEN 1 ELSE NULL END) as

count_high_elevation_aiports  airports FROM  GROUP BY state;

9.2. COMBINING AGGREGATE SELECT  origin, 100.0*(sum  (CASE WHEN carrier = 'UN' THEN distance ELSE 0 END)/sum  (distance)) as  percentage_flight_distance_from_united  flights FROM  GROUP BY origin;

10.DATE, TIME AND STRING FUNCTION 10.1.DATE and TIME Function DATETIME(time1, '+3 hours', '40 minutes', '2 days');

-- Time sau time 1 3h40p 2day

10.2.Number Function SELECT (number1 + number2); SELECT CAST(number1 AS REAL) / number3; -- Đổi kiểu SELECT ROUND(number, precision);  MAX(n1,n2,n3,...)  MIN(n1,n2,n3,...)

-- Giá trị lớn nhất từng hàng of các biến -- Giá trị nhỏ nhất từng hàng of các biến

10.3.String Function -- Nối chuỗi - ||, thay thế chuỗi- REPLACE(string, old, new) SELECT  id, first_name || ' ' || last_name As full_name, REPLACE(ingredients,'_',' ') as item_ingredients  bakeries; from 

Page 65