Forum
Quản trị Office Quản trị Word

Hướng dẫn chi tiết về lập trình VBA trên Excel ( Excel Macros )

Advertisement

VBA – Tổng quan

VBA là viết tắt của V asual B asic cho A pplications một ngôn ngữ lập trình hướng sự kiện từ Microsoft mà bây giờ chủ yếu được sử dụng với các ứng dụng văn phòng của Microsoft như MSExcel, MS-Word và MS-Access.
Nó giúp các techies xây dựng các ứng dụng và giải pháp tùy chỉnh để nâng cao khả năng của các ứng dụng đó. Lợi thế của cơ sở này là bạn CẦN KHÔNG có cài đặt trực quan cơ bản trên PC của chúng tôi, tuy nhiên, việc cài đặt Office sẽ giúp hoàn toàn trong việc đạt được mục đích.
Bạn có thể sử dụng VBA trong tất cả các phiên bản văn phòng, ngay từ MS-Office 97 đến MS-Office 2013 và cũng với bất kỳ phiên bản mới nhất nào có sẵn. Trong số VBA, Excel VBA là phổ biến nhất. Lợi thế của việc sử dụng VBA là bạn có thể xây dựng các công cụ rất mạnh mẽ trong MS Excel bằng cách sử dụng lập trình tuyến tính.


Ứng dụng VBA

Bạn có thể tự hỏi tại sao sử dụng VBA trong Excel vì bản thân MS-Excel cung cấp vô số các hàm sẵn có. MS-Excel chỉ cung cấp các chức năng cơ bản sẵn có mà có thể không đủ để thực hiện các phép tính phức tạp. Trong hoàn cảnh như vậy, VBA trở thành giải pháp rõ ràng nhất.
Ví dụ, rất khó để tính toán trả nợ hàng tháng của khoản vay bằng cách sử dụng các công thức dựng sẵn của Excel. Thay vào đó, nó rất dễ dàng để lập trình một VBA cho một phép tính như vậy.

Truy cập VBA Editor

Trong cửa sổ Excel, nhấn “ALT + F11”. Một cửa sổ VBA mở ra như được hiển thị trong ảnh chụp màn hình sau đây.

Ra quyết định trong VBScript

VBA – Excel Macros

Trong chương này, bạn sẽ học cách viết một macro đơn giản theo từng bước.
Bước 1 – Trước tiên, bật menu ‘Nhà phát triển’ trong Excel 20XX. Để làm như vậy, hãy nhấp vào Tệp → Tùy chọn.
Bước 2 – Nhấp vào tab ‘Customize the Ribbon’ và kiểm tra ‘Developer’. Nhấp vào ‘OK’.

Nhà phát triển trong VBScript

Bước 3 – Dải ‘Developer’ xuất hiện trên thanh menu.

Nhà phát triển trong VBScript

Bước 4 – Nhấp vào nút ‘Visual Basic’ để mở Trình soạn thảo VBA.

Nhà phát triển trong VBScript

Bước 5 – Bắt đầu kịch bản bằng cách thêm một nút. Nhấp vào Chèn → Chọn nút.

Nhà phát triển trong VBScript

Bước 6 – Thực hiện nhấp chuột phải và chọn ‘thuộc tính’.

Nhà phát triển trong VBScript

Bước 7 – Chỉnh sửa tên và chú thích như được hiển thị trong ảnh chụp màn hình sau.

Nhà phát triển trong VBScript

Bước 8 – Bây giờ bấm đúp vào nút và phác thảo tiểu thủ tục sẽ được hiển thị như được hiển thị trong ảnh chụp màn hình sau đây.

Nhà phát triển trong VBScript

Bước 9 – Bắt đầu viết mã bằng cách thêm một tin nhắn.
Private Sub say_helloworld_Click()
MsgBox "Hi"
End Sub
Bước 10 – Nhấn nút để thực hiện thủ tục con. Đầu ra của tiểu thủ tục được hiển thị trong ảnh chụp màn hình sau đây.

Nhà phát triển trong VBScript

Lưu ý – Trong các chương tiếp theo, chúng tôi sẽ trình bày bằng cách sử dụng một nút đơn giản, như được giải thích từ bước 1 đến 10. Do đó, điều quan trọng là phải hiểu rõ chương này.

VBA – Điều khoản Excel

Trong chương này, bạn sẽ tự làm quen với thuật ngữ VBA excel thường được sử dụng. Các thuật ngữ này sẽ được sử dụng trong các mô-đun tiếp theo, do đó hiểu được mỗi một trong số đó là quan trọng.

Mô-đun

Mô-đun là khu vực nơi mã được viết. Đây là một Workbook mới, do đó không có bất kỳ Modules nào.

Mô-đun trong VBScript

Để chèn một Mô-đun, hãy điều hướng đến Chèn → Mô-đun. Khi mô-đun được chèn ‘module1’ được tạo.
Trong các mô-đun, chúng ta có thể viết mã VBA và mã được viết trong một thủ tục. Thủ tục / thủ tục phụ là một loạt các câu lệnh VBA hướng dẫn phải làm gì.

Mô-đun trong VBScript

Thủ tục

Các thủ tục là một nhóm các câu lệnh được thi hành như một tổng thể, nó chỉ thị cho Excel cách thực hiện một nhiệm vụ cụ thể. Nhiệm vụ được thực hiện có thể là một nhiệm vụ rất đơn giản hoặc rất phức tạp. Tuy nhiên, thực hành tốt là phá vỡ các thủ tục phức tạp thành các quy trình nhỏ hơn.
Hai loại thủ tục chính là Sub và Function.

Mô-đun trong VBScript

Chức năng

Một hàm là một nhóm mã có thể tái sử dụng, có thể được gọi là bất kỳ đâu trong chương trình của bạn. Điều này giúp loại bỏ sự cần thiết phải viết cùng một mã lặp đi lặp lại. Điều này giúp các lập trình viên chia một chương trình lớn thành một số hàm nhỏ và dễ quản lý.
Ngoài các chức năng sẵn có, VBA cũng cho phép viết các hàm do người dùng định nghĩa và các câu lệnh được viết giữa Function và End Function .

Tiểu thủ tục

Các thủ tục con hoạt động tương tự như các hàm. Trong khi các thủ tục con KHÔNG trả về một giá trị, các hàm có thể hoặc không thể trả về một giá trị. Thủ tục phụ có thể được gọi mà không cần từ khóa cuộc gọi. Các thủ tục con luôn được đính kèm trong các câu lệnh Sub và End Sub .

VBA – Nhận xét Macro

Nhận xét được sử dụng để ghi lại logic chương trình và thông tin người dùng mà các lập trình viên khác có thể làm việc liên tục trên cùng một mã trong tương lai.
Nó bao gồm các thông tin như được phát triển bởi, được sửa đổi bởi, và cũng có thể bao gồm logic kết hợp. Nhận xét bị bỏ qua bởi thông dịch viên trong khi thực thi.
Các chú thích trong VBA được biểu thị bằng hai phương thức.
  • Bất kỳ câu lệnh nào bắt đầu bằng một Trích dẫn Đơn (‘) được coi là nhận xét. Sau đây là một ví dụ.
' This Script is invoked after successful login 
'
Written by : TutorialsPoint
' Return Value : True / False
  • Bất kỳ câu lệnh nào bắt đầu bằng từ khóa “REM”. Sau đây là một ví dụ.
REM This Script is written to Validate the Entered Input 
REM
Modified by : Tutorials point/user2

VBA – Hộp thư

Hàm MsgBox hiển thị một hộp thông báo và chờ người dùng nhấp vào một nút và sau đó một hành động được thực hiện dựa trên nút được người dùng nhấp vào.

Cú pháp

MsgBox(prompt[,buttons][,title][,helpfile,context])

Mô tả về Thông Số

  • Nhắc – Thông số bắt buộc. Chuỗi được hiển thị dưới dạng tin nhắn trong hộp thoại. Độ dài tối đa của lời nhắc là khoảng 1024 ký tự. Nếu thông điệp mở rộng đến nhiều hơn một dòng, thì các dòng có thể được phân tách bằng ký tự trả về (Chr (13)) hoặc ký tự linefeed (Chr (10)) giữa mỗi dòng.
  • Nút – Tham số tùy chọn. Biểu thức số chỉ định loại nút để hiển thị, kiểu biểu tượng để sử dụng, danh tính của nút mặc định và phương thức của hộp tin nhắn. Nếu để trống, giá trị mặc định cho các nút là 0.
  • Tiêu đề – Thông số tùy chọn. Một biểu thức String được hiển thị trong thanh tiêu đề của hộp thoại. Nếu tiêu đề được để trống, tên ứng dụng sẽ được đặt trong thanh tiêu đề.
  • Helpfile – Tham số tùy chọn. Biểu thức String xác định tệp trợ giúp để sử dụng để cung cấp trợ giúp theo ngữ cảnh cho hộp thoại.
  • Bối cảnh – Tham số tùy chọn. Biểu thức số nhận dạng số bối cảnh Trợ giúp được tác giả Trợ giúp gán cho chủ đề Trợ giúp thích hợp. Nếu ngữ cảnh được cung cấp, cũng phải cung cấp helpfile.
Các nút thông số có thể thực hiện bất kỳ các giá trị sau –
  • 0 vbOKOnly – Chỉ hiển thị nút OK.
  • 1 vbOKCancel – Hiển thị các nút OK và Cancel.
  • 2 vbAbortRetryIgnore – Hiển thị các nút Abort, Retry và Ignore.
  • 3 vbYesNoCancel – Hiển thị các nút Yes, No và Cancel.
  • 4 vbYesNo – Hiển thị các nút Có và Không.
  • 5 vbRetryCancel – Hiển thị các nút Retry và Cancel.
  • 16 vbCritical – Hiển thị biểu tượng Thông báo quan trọng.
  • 32 vbQuestion – Hiển thị biểu tượng Query Query.
  • 48 vbExclamation – Hiển thị biểu tượng Message Warning.
  • 64 vbInformation – Hiển thị biểu tượng Message Information.
  • 0 vbDefaultButton1 – Nút đầu tiên là mặc định.
  • 256 vbDefaultButton2 – Nút thứ hai là mặc định.
  • 512 vbDefaultButton3 – Nút thứ ba là mặc định.
  • 768 vbDefaultButton4 – Nút thứ tư là mặc định.
  • 0 vbỨng dụngPhương thức ứng dụng đa phương thức – Ứng dụng hiện tại sẽ không hoạt động cho đến khi người dùng trả lời hộp thư.
  • 4096 vbSystemModal System modal – Tất cả các ứng dụng sẽ không hoạt động cho đến khi người dùng trả lời vào hộp tin nhắn.
Các giá trị trên được phân chia hợp lý thành bốn nhóm: Nhóm đầu tiên (0 đến 5) cho biết các nút được hiển thị trong hộp tin nhắn. Nhóm thứ hai (16, 32, 48, 64) mô tả kiểu biểu tượng được hiển thị, nhóm thứ ba (0, 256, 512, 768) cho biết nút nào phải là mặc định và nhóm thứ tư (0, 4096) ) xác định phương thức của hộp tin nhắn.

Giá trị trả về

Hàm MsgBox có thể trả về một trong các giá trị sau có thể được sử dụng để xác định nút mà người dùng đã nhấp vào trong hộp thông báo.
  • 1 – vbOK – OK đã được nhấp
  • 2 – vbCancel – Huỷ đã được nhấp
  • 3 – vbAbort – Đã hủy nhấp chuột
  • 4 – vbRetry – Thử lại đã được nhấp
  • 5 – vbIgnore – Bỏ qua đã được nhấp
  • 6 – vbYes – Có được nhấp
  • 7 – vbNo – Không được nhấp

Thí dụ

Function MessageBox_Demo() 
'Message Box with just prompt message
MsgBox("Welcome")

'
Message Box with title, yes no and cancel Butttons
int a = MsgBox("Do you like blue color?",3,"Choose options")
' Assume that you press No Button
msgbox ("The Value of a is " & a)
End Function

Đầu ra

Bước 1 – Chức năng trên có thể được thực hiện bằng cách nhấp vào nút “Chạy” trên Cửa sổ VBA hoặc bằng cách gọi hàm từ Bảng tính Excel như minh họa trong ảnh chụp màn hình sau đây.

Hộp thư trong VBA

Bước 2 – Một hộp Thông báo đơn giản được hiển thị với thông báo “Chào mừng” và nút “OK”

Hộp thư trong VBA

Bước 3 – Sau khi nhấp vào OK, một hộp thoại khác sẽ hiển thị cùng với thông báo cùng với các nút “có, không và hủy”.

Hộp thư trong VBA

Bước 4 – Sau khi nhấp vào nút ‘Không’, giá trị của nút đó (7) được lưu dưới dạng số nguyên và được hiển thị dưới dạng hộp thông báo cho người dùng như được hiển thị trong ảnh chụp màn hình sau. Sử dụng giá trị này, có thể hiểu được nút nào người dùng đã nhấp vào.

Hộp thư trong VBA

VBA – Hộp nhập liệu

Hàm InputBox nhắc người dùng nhập các giá trị. Sau khi nhập các giá trị, nếu người dùng nhấp vào nút OK hoặc nhấn ENTER trên bàn phím, hàm InputBox sẽ trả về văn bản trong hộp văn bản. Nếu người dùng nhấp vào nút Hủy, hàm sẽ trả về một chuỗi rỗng (“”).

Cú pháp

InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile,context])

Mô tả về Thông Số

  • Nhắc – Thông số bắt buộc. Chuỗi được hiển thị dưới dạng tin nhắn trong hộp thoại. Độ dài tối đa của lời nhắc là khoảng 1024 ký tự. Nếu thông điệp mở rộng đến nhiều hơn một dòng, thì các dòng có thể được phân tách bằng ký tự trả về (Chr (13)) hoặc ký tự linefeed (Chr (10)) giữa mỗi dòng.
  • Tiêu đề – Thông số tùy chọn. Một biểu thức String được hiển thị trong thanh tiêu đề của hộp thoại. Nếu tiêu đề được để trống, tên ứng dụng sẽ được đặt trong thanh tiêu đề.
  • Mặc định – Thông số tùy chọn. Một văn bản mặc định trong hộp văn bản mà người dùng muốn được hiển thị.
  • XPos – Thông số tùy chọn. Vị trí của trục X thể hiện khoảng cách nhanh từ phía bên trái của màn hình theo chiều ngang. Nếu để trống, hộp nhập sẽ được căn giữa theo chiều ngang.
  • YPos – Thông số tùy chọn. Vị trí của trục Y biểu diễn khoảng cách nhanh từ phía bên trái của màn hình theo chiều dọc. Nếu để trống, hộp nhập sẽ được căn giữa theo chiều dọc.
  • Helpfile – Một tham số tùy chọn. Biểu thức String xác định tệp trợ giúp được sử dụng để cung cấp Trợ giúp theo ngữ cảnh cho hộp thoại.
  • ngữ cảnh – Tham số tùy chọn. Biểu thức số nhận dạng số bối cảnh Trợ giúp được tác giả Trợ giúp gán cho chủ đề Trợ giúp thích hợp. Nếu ngữ cảnh được cung cấp, cũng phải cung cấp helpfile.

Thí dụ

Chúng ta hãy tính toán diện tích của một hình chữ nhật bằng cách lấy các giá trị từ người dùng trong thời gian chạy với sự trợ giúp của hai hộp đầu vào (một cho chiều dài và một cho chiều rộng).
Function findArea() 
Dim Length As Double
Dim Width As Double

Length = InputBox("Enter Length ", "Enter a Number")
Width = InputBox("Enter Width", "Enter a Number")
findArea
= Length * Width
End Function

Đầu ra

Bước 1 – Để thực hiện tương tự, hãy gọi bằng cách sử dụng tên hàm và nhấn Enter như được hiển thị trong ảnh chụp màn hình sau.

Bản trình diễn hộp nhập

Bước 2 – Khi thực hiện, hộp đầu vào đầu tiên (chiều dài) được hiển thị. Nhập một giá trị vào hộp nhập liệu.

Bản trình diễn hộp nhập

Bước 3 – Sau khi nhập giá trị đầu tiên, hộp nhập thứ hai (chiều rộng) được hiển thị.

Bản trình diễn hộp nhập

Bước 4 – Sau khi nhập số thứ hai, nhấp vào nút OK. Khu vực được hiển thị như được hiển thị trong ảnh chụp màn hình sau.

Bản trình diễn hộp nhập

VBA – Biến

Biến là một vị trí bộ nhớ được đặt tên được sử dụng để giữ một giá trị có thể được thay đổi trong khi thực thi tập lệnh. Sau đây là các quy tắc cơ bản để đặt tên biến.
  • Bạn phải sử dụng chữ cái làm ký tự đầu tiên.
  • Bạn không thể sử dụng dấu cách, dấu chấm (.), Dấu chấm than (!) Hoặc ký tự @, &, $, # trong tên.
  • Tên không được vượt quá 255 ký tự.
  • Bạn không thể sử dụng từ khóa dành riêng cho Visual Basic làm tên biến.
Cú pháp
Trong VBA, bạn cần khai báo các biến trước khi sử dụng chúng.
Dim <<variable_name>> As <<variable_type>>

Loai du lieu

Có rất nhiều loại dữ liệu VBA, có thể được chia thành hai loại chính, cụ thể là các kiểu dữ liệu số và không phải số.

Các kiểu dữ liệu số

Bảng sau hiển thị các kiểu dữ liệu số và phạm vi giá trị được phép.
Kiểu Phạm vi giá trị
Byte 0 đến 255
Số nguyên -32,768 đến 32,767
Dài -2,147,483,648 đến 2.147.483.648
Độc thân
-3.402823E + 38 đến -1.401298E-45 cho các giá trị âm
1,401298E-45 đến 3,402823E + 38 cho giá trị dương.
Gấp đôi
-1.79769313486232e + 308 đến -4.94065645841247E-324 cho các giá trị âm
4.94065645841247E-324 đến 1.79769313486232e + 308 cho các giá trị dương.
Tiền tệ -922,337,203,685,477,5808 đến 922,337,203,685,477,5807
Thập phân
+/- 79,228,162,514,264,337,593,543,950,335 nếu không sử dụng số thập phân
+/- 7.9228162514264337593543950335 (28 chữ số thập phân).

Các kiểu dữ liệu không phải dạng số

Bảng sau hiển thị các loại dữ liệu không phải số và phạm vi giá trị được phép.
Kiểu Phạm vi giá trị
Chuỗi (độ dài cố định) 1 đến 65.400 ký tự
Chuỗi (độ dài biến đổi) 0 đến 2 tỷ ký tự
Ngày Ngày 1 tháng 1 đến ngày 31 tháng 12 năm 9999
Boolean Đúng hay sai
Vật Bất kỳ đối tượng nhúng nào
Biến thể (số) Bất kỳ giá trị nào lớn gấp đôi
Biến thể (văn bản) Giống như chuỗi có độ dài thay đổi
Thí dụ
Chúng ta hãy tạo một nút và đặt tên là ‘Variables_demo’ để chứng minh việc sử dụng các biến.

vba_02.jpg

Private Sub say_helloworld_Click()
Dim password As String
password
= "Admin#1"

Dim num As Integer
num
= 1234

Dim BirthDay As Date
BirthDay = DateValue("30 / 10 / 2020")

MsgBox "Passowrd is " & password & Chr(10) & "Value of num is " &
num
& Chr(10) & "Value of Birthday is " & BirthDay
End Sub
Đầu ra
Khi thực thi kịch bản, đầu ra sẽ được hiển thị trong ảnh chụp màn hình sau đây.

vba_03.jpg

VBA – Hằng số

Hằng số là một vị trí bộ nhớ được đặt tên được sử dụng để giữ một giá trị KHÔNG THỂ thay đổi trong khi thực thi tập lệnh. Nếu người dùng cố gắng thay đổi giá trị Hằng số, việc thực thi tập lệnh kết thúc bằng một lỗi. Các hằng số được khai báo giống như cách các biến được khai báo.
Sau đây là các quy tắc để đặt tên hằng số.
  • Bạn phải sử dụng chữ cái làm ký tự đầu tiên.
  • Bạn không thể sử dụng dấu cách, dấu chấm (.), Dấu chấm than (!) Hoặc ký tự @, &, $, # trong tên.
  • Tên không được vượt quá 255 ký tự.
  • Bạn không thể sử dụng từ khóa dành riêng cho Visual Basic làm tên biến.

Cú pháp

Trong VBA, chúng ta cần gán một giá trị cho các hằng số được khai báo. Một lỗi được ném, nếu chúng ta cố gắng thay đổi giá trị của hằng số.
Const <<constant_name>> As <<constant_type>> = <<constant_value>>

Thí dụ

Hãy để chúng tôi tạo ra một nút “Constant_demo” để chứng minh làm thế nào để làm việc với các hằng số.
Private Sub Constant_demo_Click() 
Const MyInteger As Integer = 42
Const myDate As Date = #2/2/2020#
Const myDay As String = "Sunday"

MsgBox "Integer is " & MyInteger & Chr(10) & "myDate is "
& myDate & Chr(10) & "myDay is " & myDay
End Sub

Đầu ra

Khi thực thi kịch bản, đầu ra sẽ được hiển thị như được hiển thị trong ảnh chụp màn hình sau đây.

vba_05.jpg

VBA – Nhà khai thác

Một toán tử có thể được định nghĩa bằng cách sử dụng một biểu thức đơn giản – 4 + 5 bằng 9. Ở đây, 4 và 5 được gọi là toán hạng và + được gọi là toán tử . VBA hỗ trợ các loại toán tử sau:
  • Toán tử số học
  • Toán tử so sánh
  • Các toán tử logic (hoặc quan hệ)
  • Nhà điều hành ghép nối

Các nhà khai thác Arithmatic

Các toán tử số học sau được VBA hỗ trợ.
Giả sử biến A giữ 5 và biến B giữ 10, sau đó –
Nhà điều hành Sự miêu tả Thí dụ
+ Thêm hai toán hạng A + B sẽ cho 15
– – Trừ toán hạng thứ hai từ lần đầu tiên A – B sẽ cho -5
* Nhân cả hai toán hạng A * B sẽ cho 50
/ Chia tử số cho mẫu số B / A sẽ cho 2
% Toán tử mô-đun và phần còn lại sau khi phân chia số nguyên B% A sẽ cho 0
^ Toán tử lũy thừa B ^ A sẽ cho 100000

Toán tử so sánh

Có các toán tử so sánh sau được VBA hỗ trợ.
Giả sử biến A giữ 10 và biến B giữ 20, sau đó –
Nhà điều hành Sự miêu tả Thí dụ
= Kiểm tra xem giá trị của hai toán hạng có bằng nhau hay không. Nếu có, thì điều kiện là đúng. (A = B) là sai.
<> Kiểm tra xem giá trị của hai toán hạng có bằng nhau hay không. Nếu các giá trị không bằng nhau, thì điều kiện là đúng. (A <> B) là True.
> Kiểm tra xem giá trị của toán hạng bên trái có lớn hơn giá trị của toán hạng bên phải hay không. Nếu có, thì điều kiện là đúng. (A> B) là Sai.
< Kiểm tra nếu giá trị của toán hạng bên trái nhỏ hơn giá trị của toán hạng bên phải. Nếu có, thì điều kiện là đúng. (A <B) là True.
> = Kiểm tra nếu giá trị của toán hạng bên trái lớn hơn hoặc bằng giá trị của toán hạng bên phải. Nếu có, thì điều kiện là đúng. (A> = B) là Sai.
<= Kiểm tra nếu giá trị của toán hạng bên trái nhỏ hơn hoặc bằng giá trị của toán hạng bên phải. Nếu có, thì điều kiện là đúng. (A <= B) là True.

Các nhà khai thác hợp lý

Các toán tử logic sau đây được VBA hỗ trợ.
Giả sử biến A giữ 10 và biến B giữ 0, sau đó –
Nhà điều hành Sự miêu tả Thí dụ
Được gọi là toán tử logic và được gọi. Nếu cả hai điều kiện là True, thì Biểu thức là đúng. a <> 0 AND b <> 0 là Sai.
HOẶC LÀ Được gọi là logic hoặc toán tử. Nếu bất kỳ điều kiện nào trong hai điều kiện là True thì điều kiện là đúng. a <> 0 HOẶC b <> 0 là đúng.
KHÔNG PHẢI Được gọi là Operical NOT Operator. Được sử dụng để đảo ngược trạng thái logic của toán hạng của nó. Nếu điều kiện là đúng, thì toán tử logic NOT sẽ sai. NOT (a <> 0 OR b <> 0) là sai.
XOR Được gọi là Loại trừ hợp lý. Nó là sự kết hợp của NOT và OR Operator. Nếu một, và chỉ một, của các biểu thức được đánh giá là True, kết quả là True. (a <> 0 XOR b <> 0) là đúng.

Các nhà điều hành ghép nối

Sau các toán tử ghép nối được VBA hỗ trợ.
Giả sử biến A giữ 5 và biến B giữ 10 rồi –
Nhà điều hành Sự miêu tả Thí dụ
+ Thêm hai Giá trị dưới dạng Biến. Giá trị là số A + B sẽ cho 15
& Kết hợp hai giá trị A & B sẽ cung cấp cho 510
Giả sử biến A = “Microsoft” và biến B = “VBScript”, sau đó –
Nhà điều hành Sự miêu tả Thí dụ
+ Kết hợp hai giá trị A + B sẽ cung cấp cho MicrosoftVBScript
& Kết hợp hai giá trị A & B sẽ cung cấp cho MicrosoftVBScript
Lưu ý – Các toán tử ghép nối có thể được sử dụng cho cả số và chuỗi. Đầu ra phụ thuộc vào ngữ cảnh, nếu các biến giữ giá trị số hoặc giá trị chuỗi.

VBA – Quyết định

Việc ra quyết định cho phép các lập trình viên kiểm soát luồng thực thi của một tập lệnh hoặc một trong các phần của nó. Việc thực hiện được điều chỉnh bởi một hoặc nhiều câu lệnh có điều kiện.
Sau đây là hình thức chung của một cấu trúc ra quyết định điển hình được tìm thấy trong hầu hết các ngôn ngữ lập trình.

Ra quyết định trong VBA

VBA cung cấp các loại tuyên bố ra quyết định sau đây. Nhấp vào các liên kết sau để kiểm tra chi tiết của chúng.
S.No. Tuyên bố & mô tả
1 nếu câu lệnh

Một khi tuyên bố bao gồm một biểu thức Boolean theo sau là một hoặc nhiều câu lệnh.
2 câu lệnh if..else

Một câu lệnh if else bao gồm một biểu thức Boolean theo sau bởi một hoặc nhiều câu lệnh. Nếu điều kiện là True, các câu lệnh trong If statements được thực hiện. Nếu điều kiện là sai, phần khác của tập lệnh được thực thi.
3 nếu … elseif..câu lệnh

Một khi tuyên bố tiếp theo một hoặc nhiều ElseIf báo cáo, mà bao gồm các biểu thức Boolean và sau đó tiếp theo là một tùy chọn else statement , mà thực hiện khi có đủ các điều kiện trở thành sai.
4 lồng nhau nếu câu lệnh

Một nếu hoặc elseif tuyên bố bên khác nếu hoặc elseif statement (s).
5 báo cáo chuyển đổi

Một câu lệnh switch cho phép một biến được kiểm tra tính bình đẳng dựa vào một danh sách các giá trị.

VBA – Vòng lặp

Có thể có một tình huống khi bạn cần thực hiện một khối mã nhiều lần. Nói chung, các câu lệnh được thực thi tuần tự: Câu lệnh đầu tiên trong một hàm được thực hiện trước tiên, tiếp theo là câu lệnh thứ hai và tiếp tục như vậy.
Ngôn ngữ lập trình cung cấp các cấu trúc điều khiển khác nhau cho phép các đường dẫn thực thi phức tạp hơn.
Một câu lệnh vòng lặp cho phép chúng ta thực hiện một câu lệnh hoặc một nhóm các câu lệnh nhiều lần. Sau đây là hình thức chung của một câu lệnh vòng lặp trong VBA.

Kiến trúc vòng lặp

VBA cung cấp các loại vòng lặp sau đây để xử lý các yêu cầu lặp. Nhấp vào các liên kết sau để kiểm tra chi tiết của chúng.
S.No. Loại vòng lặp & Mô tả
1 cho vòng lặp

Thực hiện một chuỗi các câu lệnh nhiều lần và viết tắt mã quản lý biến vòng lặp.
2 cho vòng lặp ..each

Điều này được thực hiện nếu có ít nhất một phần tử trong nhóm và được nhắc lại cho mỗi phần tử trong một nhóm.
3 while..wend loop

Điều này kiểm tra điều kiện trước khi thực hiện vòng lặp.
4 do..while loops

Các câu lệnh do..While sẽ được thực thi miễn là điều kiện là True (tức là) Vòng lặp nên được lặp lại cho đến khi điều kiện là Sai.
5 các vòng lặp do..until

Các câu lệnh do..Until sẽ được thực hiện miễn là điều kiện là False (tức là,) Lặp lại lặp lại cho đến khi điều kiện là True.

Câu lệnh điều khiển vòng lặp

Các câu lệnh điều khiển vòng lặp thay đổi thực thi từ chuỗi bình thường của nó. Khi thực thi rời khỏi một phạm vi, tất cả các câu lệnh còn lại trong vòng lặp KHÔNG được thực thi.
VBA hỗ trợ các câu lệnh điều khiển sau. Nhấp vào các liên kết sau để kiểm tra chi tiết của chúng.
S.No. Bảng điều khiển & mô tả
1 Thoát Đối với câu lệnh

Chấm dứt câu lệnh Vòng lặp for và chuyển lệnh thực thi đến câu lệnh ngay sau vòng lặp
2 Thoát lệnh

Chấm dứt câu lệnh Do While và chuyển lệnh thực hiện đến câu lệnh ngay sau vòng lặp

VBA – Chuỗi

Chuỗi là một chuỗi ký tự, có thể bao gồm chữ cái, số, ký tự đặc biệt hoặc tất cả các ký tự. Một biến được gọi là một chuỗi nếu nó được đặt trong dấu ngoặc kép “”.

Cú pháp

variablename = "string"

Ví dụ

str1 = "string"   ' Only Alphabets
str2 = "132.45" '
Only Numbers
str3
= "!@#$;*" ' Only Special Characters
Str4 = "Asc23@#" '
Has all the above

Hàm chuỗi

Có các hàm chuỗi VBA được xác định trước, giúp các nhà phát triển làm việc với các chuỗi rất hiệu quả. Sau đây là các phương thức String được hỗ trợ trong VBA. Vui lòng nhấp vào từng phương pháp để biết chi tiết.
S.No. Tên chức năng & Mô tả
1 InStr

Trả về lần xuất hiện đầu tiên của chuỗi con được chỉ định. Tìm kiếm xảy ra từ trái sang phải.
2 InstrRev

Trả về lần xuất hiện đầu tiên của chuỗi con được chỉ định. Tìm kiếm diễn ra từ phải sang trái.
3 Lcase

Trả về trường hợp thấp hơn của chuỗi được chỉ định.
4 Ucase

Trả về trường hợp trên của chuỗi được chỉ định.
5 Trái

Trả về một số ký tự cụ thể từ phía bên trái của chuỗi.
6 Đúng

Trả về một số ký tự cụ thể từ phía bên phải của chuỗi.
7 Giữa

Trả về một số ký tự cụ thể từ một chuỗi dựa trên các tham số được chỉ định.
số 8 Ltrim

Trả về một chuỗi sau khi xóa các dấu cách ở bên trái của chuỗi được chỉ định.
9 Rtrim

Trả về một chuỗi sau khi xóa các dấu cách ở bên phải của chuỗi được chỉ định.
10 Cắt

Trả về giá trị chuỗi sau khi xóa cả dấu cách hàng đầu và dấu cách trống.
11 Len

Trả về độ dài của chuỗi đã cho.
12 Thay thế

Trả về một chuỗi sau khi thay thế một chuỗi bằng một chuỗi khác.
13 Không gian

Điền một chuỗi với số không gian được chỉ định.
14 StrComp

Trả về giá trị số nguyên sau khi so sánh hai chuỗi được chỉ định.
15 Chuỗi

Trả về một chuỗi có ký tự được chỉ định cho số lần được chỉ định.
16 StrReverse

Trả về một chuỗi sau khi đảo ngược chuỗi ký tự của chuỗi đã cho.

VBA – Chức năng ngày giờ

Các hàm ngày và thời gian VBScript giúp các nhà phát triển chuyển đổi ngày và giờ từ định dạng này sang định dạng khác hoặc để thể hiện giá trị ngày tháng hoặc thời gian theo định dạng phù hợp với một điều kiện cụ thể.

Chức năng ngày tháng

S.No. Mô tả chức năng
1 Ngày

Một hàm, trả về ngày hệ thống hiện tại.
2 CDate

Một hàm, mà chuyển đổi một đầu vào đã cho đến nay.
3 DateAdd

Một hàm, trả về một ngày mà khoảng thời gian đã chỉ định đã được thêm vào.
4 DateDiff

Một hàm, trả về sự khác biệt giữa hai khoảng thời gian.
5 DatePart

Hàm, trả về một phần được chỉ định của giá trị ngày đầu vào đã cho.
6 DateSerial

Một hàm, trả về một ngày hợp lệ cho năm, tháng và ngày đã cho.
7 FormatDateTime

Một hàm, định dạng ngày dựa trên các tham số được cung cấp.
số 8 IsDate

Một hàm, trả về một giá trị Boolean cho dù tham số được cung cấp có phải là một ngày hay không.
9 ngày

Hàm, trả về một số nguyên từ 1 đến 31 đại diện cho ngày của ngày được chỉ định.
10 tháng

Hàm, trả về một số nguyên từ 1 đến 12 đại diện cho tháng của ngày được chỉ định.
11 Năm

Hàm trả về một số nguyên đại diện cho năm của ngày được chỉ định.
12 MonthName

Hàm trả về tên của tháng cụ thể cho ngày được chỉ định.
13 WeekDay

Một hàm, trả về một số nguyên (1 đến 7) đại diện cho ngày trong tuần cho ngày được chỉ định.
14 WeekDayName

Hàm trả về tên ngày trong tuần được chỉ định.

Hàm thời gian

S.No. Mô tả chức năng
1 Hiện nay

Một hàm, trả về ngày giờ hiện tại của hệ thống.
2 Giờ

Một hàm, trả về một số nguyên từ 0 đến 23 đại diện cho phần giờ của thời gian đã cho.
3 Phút

Hàm, trả về một số nguyên nằm trong khoảng từ 0 đến 59, biểu thị phần phút của thời gian đã cho.
4 Thứ hai

Hàm, trả về một số nguyên nằm trong khoảng từ 0 đến 59, biểu thị phần giây của thời gian đã cho.
5 Thời gian

Một hàm, trả về thời gian hệ thống hiện tại.
6 Bộ hẹn giờ

Hàm, trả về số giây và mili giây từ 12:00 sáng.
7 TimeSerial

Một hàm, trả về thời gian cho đầu vào cụ thể của giờ, phút và giây.
số 8 Giá trị thời gian

Một hàm, chuyển đổi chuỗi đầu vào thành định dạng thời gian.

VBA – Mảng

Chúng tôi biết rất rõ rằng một biến là một vùng chứa để lưu trữ một giá trị. Đôi khi, các nhà phát triển ở một vị trí để giữ nhiều hơn một giá trị trong một biến duy nhất tại một thời điểm. Khi một loạt các giá trị được lưu trữ trong một biến duy nhất, thì nó được gọi là một biến mảng .

Tuyên bố mảng

Các mảng được khai báo giống như một biến đã được khai báo ngoại trừ việc khai báo một biến mảng sử dụng dấu ngoặc đơn. Trong ví dụ sau, kích thước của mảng được đề cập trong dấu ngoặc đơn.
'Method 1 : Using Dim
Dim arr1() '
Without Size

'Method 2 : Mentioning the Size
Dim arr2(5) '
Declared with size of 5

'Method 3 : using 'Array' Parameter
Dim arr3
arr3 = Array("apple","Orange","Grapes")
  • Mặc dù, kích thước mảng được chỉ định là 5, nhưng nó có thể chứa 6 giá trị khi chỉ mục mảng bắt đầu từ ZERO.
  • Chỉ số mảng không được âm.
  • Mảng VBScript có thể lưu trữ bất kỳ loại biến nào trong một mảng. Do đó, một mảng có thể lưu trữ một số nguyên, chuỗi hoặc ký tự trong một biến mảng đơn lẻ.

Gán giá trị cho một mảng

Các giá trị được gán cho mảng bằng cách chỉ định một giá trị chỉ mục mảng đối với mỗi giá trị được gán. Nó có thể là một chuỗi.

Thí dụ

Thêm một nút và thêm chức năng sau đây.
Private Sub Constant_demo_Click()
Dim arr(5)
arr
(0) = "1" 'Number as String
arr(1) = "VBScript" '
String
arr
(2) = 100 'Number
arr(3) = 2.45 '
Decimal Number
arr
(4) = #10/07/2013# 'Date
arr
(5) = #12.45 PM# 'Time

msgbox
("Value stored in Array index 0 : " & arr(0))
msgbox
("Value stored in Array index 1 : " & arr(1))
msgbox
("Value stored in Array index 2 : " & arr(2))
msgbox
("Value stored in Array index 3 : " & arr(3))
msgbox
("Value stored in Array index 4 : " & arr(4))
msgbox
("Value stored in Array index 5 : " & arr(5))
End Sub
Khi bạn thực thi hàm trên, nó tạo ra kết quả sau.
Value stored in Array index 0 : 1
Value stored in Array index 1 : VBScript
Value stored in Array index 2 : 100
Value stored in Array index 3 : 2.45
Value stored in Array index 4 : 7/10/2013
Value stored in Array index 5 : 12:45:00 PM

Mảng đa chiều

Tuy nhiên, mảng không chỉ giới hạn ở một thứ nguyên duy nhất, chúng có thể có tối đa 60 thứ nguyên. Mảng hai chiều là các mảng được sử dụng phổ biến nhất.

Thí dụ

Trong ví dụ sau, mảng đa chiều được khai báo với 3 hàng và 4 cột.
Private Sub Constant_demo_Click()
Dim arr(2,3) as Variant ' Which has 3 rows and 4 columns
arr(0,0) = "Apple"
arr(0,1) = "Orange"
arr(0,2) = "Grapes"
arr(0,3) = "pineapple"
arr(1,0) = "cucumber"
arr(1,1) = "beans"
arr(1,2) = "carrot"
arr(1,3) = "tomato"
arr(2,0) = "potato"
arr(2,1) = "sandwitch"
arr(2,2) = "coffee"
arr(2,3) = "nuts"

msgbox("Value in Array index 0,1 : " & arr(0,1))
msgbox("Value in Array index 2,2 : " & arr(2,2))
End Sub
Khi bạn thực thi hàm trên, nó tạo ra kết quả sau.
Value stored in Array index : 0 , 1 : Orange
Value stored in Array index : 2 , 2 : coffee

Tuyên bố ReDim

Câu lệnh ReDim được sử dụng để khai báo các biến mảng động và phân bổ hoặc phân bổ lại không gian lưu trữ.

Cú pháp

ReDim [Preserve] varname(subscripts) [, varname(subscripts)]

Mô tả về Thông Số

  • Preserve – Một tham số tùy chọn được sử dụng để bảo toàn dữ liệu trong một mảng hiện có khi bạn thay đổi kích thước của kích thước cuối cùng.
  • Varname – Một tham số được yêu cầu, biểu thị tên của biến, sẽ theo các quy ước đặt tên biến chuẩn.
  • Subscripts – Một tham số bắt buộc, cho biết kích thước của mảng.

Thí dụ

Trong ví dụ sau, một mảng đã được định nghĩa lại và sau đó các giá trị được lưu giữ khi kích thước hiện tại của mảng được thay đổi.
Lưu ý – Khi thay đổi kích thước một mảng nhỏ hơn ban đầu, dữ liệu trong các thành phần bị loại bỏ sẽ bị mất.
Private Sub Constant_demo_Click()
Dim a() as variant
i
= 0
redim a
(5)
a
(0) = "XYZ"
a
(1) = 41.25
a
(2) = 22

REDIM PRESERVE a
(7)
For i = 3 to 7
a
(i) = i
Next

'to Fetch the output
For i = 0 to ubound(a)
Msgbox a(i)
Next
End Sub
Khi bạn thực thi hàm trên, nó tạo ra kết quả sau.
XYZ
41.25
22
3
4
5
6
7

Phương pháp mảng

Có nhiều chức năng sẵn có bên trong VBScript giúp các nhà phát triển xử lý các mảng hiệu quả. Tất cả các phương thức được sử dụng kết hợp với các mảng được liệt kê dưới đây. Vui lòng nhấp vào tên phương thức để biết chi tiết.
S.No. Mô tả chức năng
1 LBound

Một hàm, trả về một số nguyên tương ứng với chỉ số nhỏ nhất của các mảng đã cho.
2 UBound

Một hàm, trả về một số nguyên tương ứng với chỉ số lớn nhất của các mảng đã cho.
3 Tách

Hàm trả về một mảng chứa số giá trị được chỉ định. Tách dựa trên dấu phân cách.
4 Tham gia

Một hàm, trả về một chuỗi chứa một số lượng các giá trị được chỉ định trong một mảng. Đây là một hàm đối lập chính xác của Phương thức tách.
5 Bộ lọc

Một hàm, trả về một mảng dựa trên số không chứa một tập hợp con của một mảng chuỗi dựa trên một tiêu chí lọc cụ thể.
6 IsArray

Một hàm, trả về một giá trị boolean cho biết biến đầu vào có phải là một mảng hay không.
7 Tẩy xóa

Một hàm, phục hồi bộ nhớ được cấp phát cho các biến mảng.

VBA – Các hàm do người dùng định nghĩa

Một hàm là một nhóm mã có thể tái sử dụng có thể được gọi ở bất kỳ đâu trong chương trình của bạn. Điều này giúp loại bỏ sự cần thiết phải viết cùng một mã lặp đi lặp lại. Điều này cho phép các lập trình viên chia một chương trình lớn thành một số hàm nhỏ và dễ quản lý.
Ngoài các chức năng sẵn có, VBA cũng cho phép viết các hàm do người dùng định nghĩa. Trong chương này, bạn sẽ học cách viết các hàm của riêng bạn trong VBA.

Định nghĩa chức năng

Một hàm VBA có thể có một câu lệnh trả về tùy chọn. Điều này là bắt buộc nếu bạn muốn trả về một giá trị từ một hàm.
Ví dụ, bạn có thể vượt qua hai số trong một hàm và sau đó bạn có thể mong đợi từ hàm để trả về phép nhân của chúng trong chương trình gọi điện của bạn.
Lưu ý – Một hàm có thể trả về nhiều giá trị được phân tách bằng dấu phẩy làm mảng được gán cho chính tên hàm.
Trước khi chúng ta sử dụng một hàm, chúng ta cần xác định hàm cụ thể đó. Cách phổ biến nhất để định nghĩa một hàm trong VBA là sử dụng từ khóa Function , theo sau là một tên hàm duy nhất và nó có thể hoặc không mang một danh sách các tham số và một câu lệnh với từ khóa End Function , cho biết kết thúc của hàm. Sau đây là cú pháp cơ bản.

Cú pháp

Thêm một nút và thêm chức năng sau đây.
Function Functionname(parameter-list)
statement 1
statement 2
statement 3
.......
statement n
End Function

Thí dụ

Thêm hàm sau trả về vùng. Lưu ý rằng một giá trị / giá trị có thể được trả về với tên hàm chính nó.
Function findArea(Length As Double, Optional Width As Variant)
If IsMissing(Width) Then
findArea
= Length * Length
Else
findArea
= Length * Width
End If
End Function

Gọi một hàm

Để gọi một hàm, gọi hàm bằng cách sử dụng tên hàm như được hiển thị trong ảnh chụp màn hình sau đây.

Ra quyết định trong VBA

Đầu ra của khu vực như được hiển thị bên dưới sẽ được hiển thị cho người dùng.

Ra quyết định trong VBA

VBA – Thủ tục phụ

Thủ tục phụ tương tự như chức năng, tuy nhiên có một vài sự khác biệt.
  • Các thủ tục con KHÔNG trả về một giá trị trong khi các hàm có thể hoặc không thể trả về một giá trị.
  • Thủ tục con có thể được gọi mà không cần từ khóa cuộc gọi.
  • Các thủ tục con luôn được đính kèm trong các câu lệnh Sub và End Sub.

Thí dụ

Sub Area(x As Double, y As Double)
MsgBox x * y
End Sub

Thủ tục gọi điện

Để gọi Thủ tục ở đâu đó trong tập lệnh, bạn có thể thực hiện cuộc gọi từ một hàm. Chúng tôi sẽ không thể sử dụng cùng cách với chức năng như thủ tục con S W KHÔNG trả lại giá trị.
Function findArea(Length As Double, Width As Variant)
area
Length, Width ' To Calculate Area 'area' sub proc is called
End Function
Bây giờ bạn sẽ có thể gọi hàm này nhưng không thể gọi thủ tục con như được hiển thị trong ảnh chụp màn hình sau đây.

sub_procedure trong VBA

Khu vực này được tính toán và chỉ hiển thị trong hộp Thông báo.

calculate_area_sub_2 trong VBA

Ô kết quả hiển thị ZERO vì giá trị vùng KHÔNG trả về từ hàm. Trong ngắn hạn, bạn không thể thực hiện một cuộc gọi trực tiếp đến một thủ tục phụ từ bảng tính excel.

calculate_area_sub_3 trong VBA

VBA – Sự kiện

VBA, một chương trình hướng sự kiện có thể được kích hoạt khi bạn thay đổi một ô hoặc phạm vi giá trị ô theo cách thủ công. Thay đổi sự kiện có thể làm mọi thứ dễ dàng hơn nhưng bạn có thể nhanh chóng kết thúc một trang có định dạng đầy đủ. Có hai loại sự kiện.
  • Sự kiện bảng tính
  • Sự kiện Workbook

Sự kiện bảng tính

Bảng tính Sự kiện được kích hoạt khi có thay đổi trong bảng tính. Nó được tạo ra bằng cách nhấp chuột phải vào tab trang tính và chọn ‘mã xem’ và sau đó dán mã.
Người dùng có thể chọn mỗi một trong các bảng tính đó và chọn “WorkSheet” từ danh sách thả xuống để nhận danh sách tất cả các sự kiện Bảng tính được hỗ trợ.

Bản trình diễn hộp nhập

Sau đây là các sự kiện bảng tính được hỗ trợ có thể được thêm bởi người dùng.
Private Sub Worksheet_Activate() 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Private Sub Worksheet_Calculate()
Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_Deactivate()
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Thí dụ

Hãy để chúng tôi nói, chúng tôi chỉ cần hiển thị một tin nhắn trước khi nhấp đúp.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
MsgBox "Before Double Click"
End Sub

Đầu ra

Khi nhấp đúp vào bất kỳ ô nào, hộp thông báo sẽ hiển thị cho người dùng như được hiển thị trong ảnh chụp màn hình sau đây.

Bản trình diễn hộp nhập

Sự kiện Workbook

Sự kiện sổ làm việc được kích hoạt khi có thay đổi trong sổ làm việc trên toàn bộ. Chúng tôi có thể thêm mã cho các sự kiện bảng tính bằng cách chọn ‘ThisWorkbook’ và chọn ‘workbook’ từ menu thả xuống như được hiển thị trong ảnh chụp màn hình sau đây. Ngay lập tức Workbook_open sub thủ tục được hiển thị cho người dùng như được thấy trong ảnh chụp màn hình sau đây.

Bản trình diễn hộp nhập

Sau đây là các sự kiện Workbook được hỗ trợ có thể được thêm bởi người dùng.
Private Sub Workbook_AddinUninstall() 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Private Sub Workbook_Deactivate()
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Private Sub Workbook_Open()
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Private Sub Workbook_WindowResize(ByVal Wn As Window)

Thí dụ

Hãy để chúng tôi nói, chúng tôi chỉ cần hiển thị một thông báo cho người dùng rằng một trang tính mới được tạo thành công, bất cứ khi nào một trang tính mới được tạo.
Private Sub Workbook_NewSheet(ByVal Sh As Object)
MsgBox "New Sheet Created Successfully"
End Sub

Đầu ra

Sau khi tạo một bảng excel mới, một thông báo sẽ được hiển thị cho người dùng như được hiển thị trong ảnh chụp màn hình sau đây.

Bản trình diễn hộp nhập

VBA – Xử lý lỗi

Có ba loại lỗi trong lập trình: (a) Lỗi cú pháp, (b) Lỗi thời gian chạy và (c) Lỗi hợp lý.

Lỗi cú pháp

Lỗi cú pháp, còn được gọi là lỗi phân tích cú pháp, xảy ra tại thời gian diễn giải cho VBScript. Ví dụ, dòng sau gây ra lỗi cú pháp vì nó thiếu dấu đóng ngoặc.
Function ErrorHanlding_Demo()
dim x
,y
x
= "Tutorialspoint"
y
= Ucase(x
End Function

Lỗi thời gian chạy

Lỗi thời gian chạy, còn được gọi là ngoại lệ, xảy ra trong quá trình thực thi, sau khi diễn giải.
Ví dụ, dòng sau gây ra một lỗi thời gian chạy vì ở đây cú pháp là chính xác nhưng trong thời gian chạy nó đang cố gắng gọi fnmultiply, đó là một chức năng không tồn tại.
Function ErrorHanlding_Demo1()
Dim x,y
x
= 10
y
= 20
z
= fnadd(x,y)
a
= fnmultiply(x,y)
End Function

Function fnadd(x,y)
fnadd
= x + y
End Function

Lỗi hợp lý

Lỗi logic có thể là loại lỗi khó nhất để theo dõi. Các lỗi này không phải là kết quả của cú pháp hoặc lỗi thời gian chạy. Thay vào đó, chúng xảy ra khi bạn mắc sai lầm trong logic điều khiển kịch bản của bạn và bạn không nhận được kết quả mong đợi.
Bạn không thể bắt được những lỗi đó, bởi vì nó phụ thuộc vào yêu cầu nghiệp vụ của bạn loại logic bạn muốn đưa vào chương trình của mình.
Ví dụ: chia một số bằng 0 hoặc một tập lệnh được viết đi vào vòng lặp vô hạn.

Err Object

Giả sử nếu chúng ta có lỗi thời gian chạy, thì việc thực hiện dừng lại bằng cách hiển thị thông báo lỗi. Là một nhà phát triển, nếu chúng ta muốn nắm bắt lỗi, thì đối tượng Lỗi được sử dụng.

Thí dụ

Trong ví dụ sau, Err.Number đưa ra số lỗi và Err.Description đưa ra mô tả lỗi.
Err.Raise 6   ' Raise an overflow error.
MsgBox "Error # " & CStr(Err.Number) & " " & Err.Description
Err.Clear '
Clear the error.

Xử lý lỗi

VBA cho phép một thói quen xử lý lỗi và cũng có thể được sử dụng để vô hiệu hóa một thói quen xử lý lỗi. Nếu không có câu lệnh On Error, bất kỳ lỗi thời gian chạy nào xảy ra đều gây tử vong: thông báo lỗi được hiển thị và việc thực thi dừng đột ngột.
On Error { GoTo [ line | 0 | -1 ] | Resume Next }
S.No. Từ khóa & Mô tả
1
Line GoTo
Bật quy trình xử lý lỗi bắt đầu tại dòng được chỉ định trong đối số dòng được yêu cầu. Dòng được chỉ định phải nằm trong cùng một quy trình với câu lệnh On Error hoặc lỗi biên dịch sẽ xảy ra.
2
GoTo 0
Tắt trình xử lý lỗi đã bật trong quy trình hiện tại và đặt lại thành Không có gì.
3
GoTo -1
Tắt ngoại lệ đã bật trong quy trình hiện tại và đặt lại thành Không có gì.
4
Tiếp tục Tiếp tục
Chỉ định rằng khi một lỗi thời gian chạy xảy ra, điều khiển đi đến câu lệnh ngay lập tức sau câu lệnh nơi xảy ra lỗi và việc thực hiện tiếp tục từ điểm đó.

Thí dụ

Public Sub OnErrorDemo()
On Error GoTo ErrorHandler ' Enable error-handling routine.
Dim x, y, z As Integer
x = 50
y = 0
z = x / y '
Divide by ZERO Error Raises

ErrorHandler: ' Error-handling routine.
Select Case Err.Number '
Evaluate error number.
Case 10 ' Divide by zero error
MsgBox ("You attempted to divide by zero!")
Case Else
MsgBox "UNKNOWN ERROR - Error# " & Err.Number & " : " & Err.Description
End Select
Resume Next
End Sub

VBA – Đối tượng Excel

Khi lập trình bằng VBA, có một vài đối tượng quan trọng mà người dùng sẽ xử lý.
  • Đối tượng ứng dụng
  • Đối tượng Workbook
  • Các đối tượng bảng tính
  • Đối tượng dải ô

Đối tượng ứng dụng

Đối tượng Application bao gồm các mục sau:
  • Cài đặt và tùy chọn trên toàn ứng dụng.
  • Các phương thức trả về các đối tượng cấp cao nhất, chẳng hạn như ActiveCell, ActiveSheet, v.v.

Thí dụ

'Example 1 :
Set xlapp = CreateObject("Excel.Sheet")
xlapp.Application.Workbooks.Open "C:test.xls"

'
Example 2 :
Application.Windows("test.xls").Activate

'Example 3:
Application.ActiveCell.Font.Bold = True

Đối tượng Workbook

Đối tượng Sổ làm việc là một thành viên của bộ sưu tập Sổ làm việc và chứa tất cả các đối tượng Sổ làm việc hiện đang mở trong Microsoft Excel.

Thí dụ

'Ex 1 : To close Workbooks
Workbooks.Close

'
Ex 2 : To Add an Empty Work Book
Workbooks.Add

'Ex 3: To Open a Workbook
Workbooks.Open FileName:="Test.xls", ReadOnly:=True

'
Ex : 4 - To Activate WorkBooks
Workbooks("Test.xls").Worksheets("Sheet1").Activate

Các đối tượng bảng tính

Đối tượng Worksheet là một thành viên của bộ sưu tập Worksheets và chứa tất cả các đối tượng Worksheet trong một bảng tính.

Thí dụ

'Ex 1 : To make it Invisible
Worksheets(1).Visible = False

'
Ex 2 : To protect an WorkSheet
Worksheets("Sheet1").Protect password:=strPassword, scenarios:=True

Đối tượng dải ô

Đối tượng dải ô đại diện cho một ô, một hàng, một cột hoặc một vùng ô có chứa một hoặc nhiều khối ô liên tục.
'Ex 1 : To Put a value in the cell A5
Worksheets("Sheet1").Range("A5").Value = "5235"

'
Ex 2 : To put a value in range of Cells
Worksheets("Sheet1").Range("A1:A4").Value = 5

VBA – Tập tin văn bản

Bạn cũng có thể đọc tệp Excel và ghi nội dung của ô vào một tệp văn bản bằng VBA. VBA cho phép người dùng làm việc với các tệp văn bản bằng hai phương thức –
  • Đối tượng hệ thống tệp
  • sử dụng lệnh viết

Đối tượng hệ thống tệp (FSO)

Như tên cho thấy, FSO giúp các nhà phát triển làm việc với các ổ đĩa, thư mục và tệp. Trong phần này, chúng ta sẽ thảo luận cách sử dụng FSO.
S.No. Loại đối tượng & mô tả
1
Lái xe
Drive là một đối tượng. Chứa các phương thức và thuộc tính cho phép bạn thu thập thông tin về một ổ đĩa được gắn vào hệ thống.
2
Ổ đĩa
Ổ đĩa là một Bộ sưu tập. Nó cung cấp một danh sách các ổ đĩa gắn liền với hệ thống, hoặc về thể chất hoặc hợp lý.
3
Tập tin
Tệp là một đối tượng. Nó chứa các phương thức và thuộc tính cho phép các nhà phát triển tạo, xóa hoặc di chuyển một tệp.
4
Các tập tin
Tệp là một Bộ sưu tập. Nó cung cấp một danh sách tất cả các tập tin chứa trong một thư mục.
5
Thư mục
Thư mục là một đối tượng. Nó cung cấp các phương thức và thuộc tính cho phép các nhà phát triển tạo, xóa hoặc di chuyển các thư mục.
6
Thư mục
Thư mục là một Bộ sưu tập. Nó cung cấp một danh sách tất cả các thư mục trong một thư mục.
7
TextStream
TextStream là một đối tượng. Nó cho phép các nhà phát triển đọc và viết các tệp văn bản.

Lái xe

Drive là một đối tượng, cung cấp quyền truy cập vào các thuộc tính của một ổ đĩa hoặc chia sẻ mạng cụ thể. Các thuộc tính sau được hỗ trợ bởi đối tượng Drive –
  • AvailableSpace
  • Ký tự ổ đĩa
  • Loại ổ
  • Hệ thống tập tin
  • Không gian trông
  • Sẵn sàng
  • Con đường
  • Thư mục gốc
  • Số sê-ri
  • ShareName
  • TotalSize
  • VolumeName

Thí dụ

Bước 1 – Trước khi tiếp tục viết kịch bản bằng FSO, chúng ta nên kích hoạt Microsoft Scripting Runtime. Để thực hiện tương tự, hãy điều hướng đến Công cụ → Tham chiếu như được hiển thị trong ảnh chụp màn hình sau đây.

Excel FSO trong VBScript

Bước 2 – Thêm “Microsoft Scripting RunTime” và nhấn OK.

Excel FSO trong VBScript

Bước 3 – Thêm dữ liệu mà bạn muốn viết trong một tệp văn bản và thêm một nút lệnh.

Excel FSO trong VBScript

Bước 4 – Bây giờ là lúc để Script.
Private Sub fn_write_to_text_Click()
Dim FilePath As String
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long

Dim fso As FileSystemObject
Set fso = New FileSystemObject
Dim stream As TextStream

LastCol = ActiveSheet.UsedRange.Columns.Count
LastRow = ActiveSheet.UsedRange.Rows.Count

' Create a TextStream.
Set stream = fso.OpenTextFile("D:TrySupport.log", ForWriting, True)

CellData = ""

For i = 1 To LastRow
For j = 1 To LastCol
CellData = Trim(ActiveCell(i, j).Value)
stream.WriteLine "The Value at location (" & i & "," & j & ")" & CellData
Next j
Next i

stream.Close
MsgBox ("Job Done")
End Sub

Đầu ra

Khi thực thi tập lệnh, đảm bảo rằng bạn đặt con trỏ vào ô đầu tiên của trang tính. Tệp Support.log được tạo như được hiển thị trong ảnh chụp màn hình sau trong “D: Try”.

Excel FSO trong VBScript

Nội dung của tệp được hiển thị trong ảnh chụp màn hình sau.

Excel FSO trong VBScript

Viết lệnh

Không giống như FSO, chúng tôi KHÔNG cần thêm bất kỳ tham chiếu nào, tuy nhiên, chúng tôi KHÔNG thể làm việc với các ổ đĩa, tệp và thư mục. Chúng tôi sẽ chỉ có thể thêm luồng vào tệp văn bản.

Thí dụ

Private Sub fn_write_to_text_Click()
Dim FilePath As String
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long

LastCol = ActiveSheet.UsedRange.Columns.Count
LastRow = ActiveSheet.UsedRange.Rows.Count

FilePath = "D:Trywrite.txt"
Open FilePath For Output As #2

CellData = ""
For i = 1 To LastRow
For j = 1 To LastCol
CellData = "The Value at location (" & i & "," & j & ")" & Trim(ActiveCell(i, j).Value)
Write #2, CellData
Next j
Next i

Close #2
MsgBox ("Job Done")
End Sub

Đầu ra

Khi thực thi kịch bản, tệp “write.txt” được tạo ở vị trí “D: Try” như được hiển thị trong ảnh chụp màn hình sau đây.

Excel FSO trong VBScript

Nội dung của tệp được hiển thị trong ảnh chụp màn hình sau.

Excel FSO trong VBScript

VBA – Biểu đồ lập trình

Sử dụng VBA, bạn có thể tạo biểu đồ dựa trên các tiêu chí nhất định. Chúng ta hãy xem xét nó bằng cách sử dụng một ví dụ.
Bước 1 – Nhập dữ liệu mà biểu đồ phải được tạo ra.

Đồ thị trong VBA

Bước 2 – Tạo 3 nút – một để tạo biểu đồ thanh, một nút khác để tạo biểu đồ hình tròn và một nút khác để tạo biểu đồ cột.

Đồ thị trong VBA

Bước 3 – Xây dựng một Macro để tạo ra từng loại biểu đồ này.
' Procedure to Generate Pie Chart
Private Sub fn_generate_pie_graph_Click()
Dim cht As ChartObject
For Each cht In Worksheets(1).ChartObjects
cht.Chart.Type = xlPie
Next cht
End Sub

'
Procedure to Generate Bar Graph
Private Sub fn_Generate_Bar_Graph_Click()
Dim cht As ChartObject
For Each cht In Worksheets(1).ChartObjects
cht
.Chart.Type = xlBar
Next cht
End Sub

' Procedure to Generate Column Graph
Private Sub fn_generate_column_graph_Click()
Dim cht As ChartObject
For Each cht In Worksheets(1).ChartObjects
cht.Chart.Type = xlColumn
Next cht
End Sub
Bước 4 – Khi nhấp vào nút tương ứng, biểu đồ được tạo. Trong đầu ra sau, bấm vào nút tạo biểu đồ tròn.

Đồ thị trong VBA

VBA – Biểu mẫu người dùng

Một Form là một hộp thoại tùy chỉnh được xây dựng mà làm cho một mục dữ liệu người dùng kiểm soát hơn và dễ dàng hơn để sử dụng cho người dùng. Trong chương này, bạn sẽ học cách thiết kế một biểu mẫu đơn giản và thêm dữ liệu vào excel.
Bước 1 – Điều hướng đến VBA Window bằng cách nhấn Alt + F11 và điều hướng đến “Insert” Menu và chọn “User Form”. Khi chọn, biểu mẫu người dùng được hiển thị như được hiển thị trong ảnh chụp màn hình sau đây.

Ra quyết định trong VBScript

Bước 2 – Thiết kế các biểu mẫu bằng cách sử dụng các điều khiển đã cho.

Ra quyết định trong VBScript

Bước 3 – Sau khi thêm mỗi điều khiển, các điều khiển phải được đặt tên. Chú thích tương ứng với những gì xuất hiện trên biểu mẫu và tên tương ứng với tên logic sẽ xuất hiện khi bạn viết mã VBA cho phần tử đó.

Ra quyết định trong VBScript

Bước 4 – Sau đây là các tên đối với từng điều khiển được thêm vào.
Điều khiển Tên hợp lý Chú thích
Từ frmempform Biểu mẫu nhân viên
Hộp nhãn ID nhân viên nhận thức Mã hiệu công nhân
Hộp nhãn đầu tiên tên đầu tiên Tên đầu tiên
Hộp nhãn cuối cùng họ Họ
dob Hộp Nhãn dob Ngày sinh
Mailid Label Box tài khoản mail ID email
Hộp nhãn hộ chiếu Người nắm hộ chiếu Người nắm hộ chiếu
Hộp văn bản ID Emp txtempid KHÔNG áp dụng
Hộp văn bản tên đầu tiên txtfirstname KHÔNG áp dụng
Họ và tên txtlastname KHÔNG áp dụng
Hộp văn bản ID email txtemailid KHÔNG áp dụng
Hộp ngày kết hợp cmbdate KHÔNG áp dụng
Tháng Combo Box cmbmonth KHÔNG áp dụng
Hộp kết hợp năm cmbyear KHÔNG áp dụng
Nút radio có radioyes Vâng
Không có nút radio radiono Không
Nút gửi btnsubmit Gửi đi
Nút hủy btncancel Hủy bỏ
Bước 5 – Thêm mã cho sự kiện tải biểu mẫu bằng cách thực hiện nhấp chuột phải vào biểu mẫu và chọn ‘Xem mã’.

Ra quyết định trong VBScript

Bước 6 – Chọn ‘Userform’ từ các đối tượng thả xuống và chọn phương thức ‘Khởi tạo’ như được hiển thị trong ảnh chụp màn hình sau đây.

Ra quyết định trong VBScript

Bước 7 – Sau khi tải biểu mẫu, đảm bảo rằng các hộp văn bản được xóa, hộp thả xuống được lấp đầy và nút Radio được đặt lại.
Private Sub UserForm_Initialize()
'Empty Emp ID Text box and Set the Cursor
txtempid.Value = ""
txtempid.SetFocus

'
Empty all other text box fields
txtfirstname
.Value = ""
txtlastname
.Value = ""
txtemailid
.Value = ""

'Clear All Date of Birth Related Fields
cmbdate.Clear
cmbmonth.Clear
cmbyear.Clear

'
Fill Date Drop Down box - Takes 1 to 31
With cmbdate
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
.AddItem "7"
.AddItem "8"
.AddItem "9"
.AddItem "10"
.AddItem "11"
.AddItem "12"
.AddItem "13"
.AddItem "14"
.AddItem "15"
.AddItem "16"
.AddItem "17"
.AddItem "18"
.AddItem "19"
.AddItem "20"
.AddItem "21"
.AddItem "22"
.AddItem "23"
.AddItem "24"
.AddItem "25"
.AddItem "26"
.AddItem "27"
.AddItem "28"
.AddItem "29"
.AddItem "30"
.AddItem "31"
End With

'Fill Month Drop Down box - Takes Jan to Dec
With cmbmonth
.AddItem "JAN"
.AddItem "FEB"
.AddItem "MAR"
.AddItem "APR"
.AddItem "MAY"
.AddItem "JUN"
.AddItem "JUL"
.AddItem "AUG"
.AddItem "SEP"
.AddItem "OCT"
.AddItem "NOV"
.AddItem "DEC"
End With

'
Fill Year Drop Down box - Takes 1980 to 2014
With cmbyear
.AddItem "1980"
.AddItem "1981"
.AddItem "1982"
.AddItem "1983"
.AddItem "1984"
.AddItem "1985"
.AddItem "1986"
.AddItem "1987"
.AddItem "1988"
.AddItem "1989"
.AddItem "1990"
.AddItem "1991"
.AddItem "1992"
.AddItem "1993"
.AddItem "1994"
.AddItem "1995"
.AddItem "1996"
.AddItem "1997"
.AddItem "1998"
.AddItem "1999"
.AddItem "2000"
.AddItem "2001"
.AddItem "2002"
.AddItem "2003"
.AddItem "2004"
.AddItem "2005"
.AddItem "2006"
.AddItem "2007"
.AddItem "2008"
.AddItem "2009"
.AddItem "2010"
.AddItem "2011"
.AddItem "2012"
.AddItem "2013"
.AddItem "2014"
End With

'Reset Radio Button. Set it to False when form loads.
radioyes.Value = False
radiono.Value = False

End Sub
Bước 8 – Bây giờ thêm mã vào nút Gửi. Khi nhấp vào nút gửi, người dùng sẽ có thể thêm các giá trị vào trang tính.
Private Sub btnsubmit_Click()
Dim emptyRow As Long

'Make Sheet1 active
Sheet1.Activate

'
Determine emptyRow
emptyRow
= WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
Cells(emptyRow, 1).Value = txtempid.Value
Cells(emptyRow, 2).Value = txtfirstname.Value
Cells(emptyRow, 3).Value = txtlastname.Value
Cells(emptyRow, 4).Value = cmbdate.Value & "/" & cmbmonth.Value & "/" & cmbyear.Value
Cells(emptyRow, 5).Value = txtemailid.Value

If radioyes.Value = True Then
Cells(emptyRow, 6).Value = "Yes"
Else
Cells(emptyRow, 6).Value = "No"
End If
End Sub
Bước 9 – Thêm một phương thức để đóng biểu mẫu khi người dùng nhấp vào nút Hủy.
Private Sub btncancel_Click()
Unload Me
End Sub
Bước 10 – Thực hiện biểu mẫu bằng cách nhấp vào nút “Chạy”. Nhập các giá trị vào biểu mẫu và nhấp vào nút ‘Gửi’. Tự động các giá trị sẽ chảy vào trang tính như được hiển thị trong ảnh chụp màn hình sau đây.

Ra quyết định trong VBScript

Facebook Comments

DienDan.Edu.Vn

DienDan.Edu.Vn Cám ơn bạn đã quan tâm và rất vui vì bài viết đã đem lại thông tin hữu ích cho bạn.
DienDan.Edu.Vn! là một website với tiêu chí chia sẻ thông tin,... Bạn có thể nhận xét, bổ sung hay yêu cầu hướng dẫn liên quan đến bài viết. Vậy nên đề nghị các bạn cũng không quảng cáo trong comment này ngoại trừ trong chính phần tên của bạn.
Cám ơn.

Đăng bình luận

(+84) (85) 39.67890