Các thao tác xử lý chuỗi string

Trong chương này , bạn sẽ tìm thấy các chức năng quan trọng nhất để thao tác với chuỗi string trong Excel VBA

Đặt một nút lệnh trên bảng tính của bạn và thêm các dòng mã dưới đây. Để thực hiện các dòng code, nhấp vào nút lệnh trên sheet.

Các thao tác xử lý chuỗi stringJoint Strings

Chúng tôi sử dụng toán tử & để nối hai chuỗi với nhau Code: Dim text1 As String, text2 As String
text1 = "Hi"
text2 = "Tim"

MsgBox text1 & " " & text2

Kết quả:

Lưu ý : để chèn một khoảng trống, sử dụng " " Left Để trích xuất các ký tự bên trái của một chuỗi , sử dụng hàm LEFT. Code:

Dim text As String
text = "example text"

MsgBox Left(text, 4)

Kết quả:

RIGHT

Để trích xuất các ký tự bên phải nhất từ một chuỗi, sử dụng RIGHT. Chúng tôi cũng có thể trực tiếp chèn văn bản trong một hàm.

Code:

MsgBox Right("example text", 2)

Kết quả:

MID

Để trích xuất một chuỗi con , bắt đầu ở giữa của một chuỗi , sử dụng MID.

Code:

MsgBox Mid("example text", 9, 2)

Các thao tác xử lý chuỗi string

Kết quả:

Lưu ý : bắt đầu tại vị trí 9 ( t ) với chiều dài 2. Bạn có thể bỏ qua đối số thứ ba nếu bạn muốn trích xuất một chuỗi con bắt đầu ở giữa của một chuỗi cho đến cuối

LEN: độ dài chuỗi

Để có được độ dài của một chuỗi , sử dụng Len .

Code:

MsgBox Len("example text")

Kết quả:

Lưu ý : khoảng trống (vị trí 8 ) cũng được bao gồm trong chuỗi Instr Để tìm vị trí của một chuỗi con trong một chuỗi , sử dụng Instr . Code:

MsgBox Instr("example text", "am")

Kết quả:

Lưu ý : . chuỗi đang tìm thấy ở vị trí 3

Read more

Ngày và Giờ

0

Read more

Bắt sự kiện - Event

Sự kiện là những hành động của người sử dụng mà Excel VBA catch để thực thi mã. Sự kiện (Event): mở một Workbook Code thêm vào các sự kiện mở Workbook sẽ được thực hiện bằng Excel VBA khi bạn mở bảng tính . 1 . Mở VBA editor 2 . Double click vào Workbook trong Project Explorer.

3 . Chọn Workbook từ danh sách thả xuống bên trái . Chọn Open từ danh sách thả xuống ngay .

4 . Thêm dòng mã sau vào sự kiện Workbook mở:

MsgBox "Good Morning"

5 . Lưu, đóng và mở lại tập tin Excel .

Kết quả:

Worksheet Change Event

Code thêm vào sự kiện Change Worksheet sẽ được thực hiện bằng Excel VBA khi bạn thay đổi một tế bào trên một bảng tính . 1 . Mở VBA editor

2 . Double click vào một sheet ( ví dụ Sheet1 ) trong Project Explorer .

3 . Chọn Worksheet từ danh sách thả xuống bên trái . Chọn Thay đổi từ danh sách thả xuống ngay .

Thêm các dòng mã sau vào sự kiện Change Worksheet : 4 . Các tổ chức sự kiện Change Worksheet lắng nghe tất cả những thay đổi trên Sheet1 . Chúng tôi muốn Excel VBA để làm một cái gì đó nếu có điều gì thay đổi trong ô B2 . Để đạt được điều này , thêm những dòng mã sau :

If Target.Address = "$B$2" Then

End If

5 . Chúng tôi muốn Excel VBA để hiển thị một MsgBox nếu người dùng nhập vào một giá trị lớn hơn 80. Để đạt được điều này , thêm dòng mã sau giữa Nếu và End If.

If Target.Value > 80 Then MsgBox "Goal Completed"

6 . Trên Sheet1 , nhập một số lượng lớn hơn 80 vào ô B2 . Kết quả: \r

Read more

Khai báo mảng - Array

Mảng (array)là một nhóm các biến. Trong Excel VBA, bạn có thể tham khảo một biến cụ thể (phần tử) của một mảng bằng cách sử dụng tên mảng và chỉ số .
Mảng một chiều
Để tạo một mảng một chiều , thực hiện các bước sau đây . Đặt một Khai báo mảng - Arraynút lệnh trên bảng tính của bạn và thêm các dòng mã sau :
Dim Films(1 To 5) As String

Films(1) = "Lord of the Rings"
Films(2) = "Speed"
Films(3) = "Star Wars"
Films(4) = "The Godfather"
Films(5) = "Pulp Fiction"

MsgBox Films(4)
Kết quả khi bạn nhấp chuột vào nút lệnh trên bảng :

Giải thích : các dòng mã đầu tiên khai báo một mảng String với tên Films. Các mảng bao gồm năm phần tử. Tiếp theo , chúng ta khởi tạo mỗi phần tử của mảng. Cuối cùng , chúng tôi hiển thị các yếu tố thứ tư, sử dụng MsgBox .
Mảng hai chiều
Để tạo một mảng hai chiều , thực hiện các bước sau đây . Lần này chúng ta sẽ đọc từ sheet .
 Đặt một nút lệnh trên bảng tính của bạn và thêm các dòng mã sau :
Dim Films(1 To 5, 1 To 2) As String
Dim i As Integer, j As Integer

For i = 1 To 5
For j = 1 To 2
Films(i, j) = Cells(i, j).Value
Next j
Next i

MsgBox Films(4, 2)
Kết quả khi bạn nhấp chuột vào nút lệnh trên bảng :

Giải thích : các dòng mã đầu tiên khai báo một mảng String với tên biến Films. Các mảng có hai chiều . Nó bao gồm 5 dòng và 2 cột .
Mẹo: hàng đi đầu tiên , sau đó cột . Hai biến khác của loại Integer được sử dụng cho các hai vòng lặp để khởi tạo mỗi phần tử của mảng. Cuối cùng , chúng tôi hiển thị các phần tử tại các giao điểm của dòng 4 và cột 2 .

Read more

Function và Sub

Sự khác biệt giữa một hàm FUNCTION và SUBtrong Excel VBA là Function có thể trả về một giá trị , trong khi Sub thì không. Funtion và Sub trở nên rất hữu ích khi chương trình ngày một phức tạp

Function

Nếu bạn muốn Excel VBA để thực hiện một nhiệm vụ mà trả về kết quả, bạn có thể sử dụng Function. Đặt Function vào một module ( Trong Visual Basic Editor, nhấn Insert, Module) . Ví dụ , Function với tên Area.

Function và Sub

Function Area(x As Double, y As Double) As Double

Area = x * y

End FunctionFunction và Sub

Giải thích : Hàm này có hai đối số (kiểu Double) và một kiểu trả về (sau as kiểu Double) . Bạn có thể sử dụng tên của Function (Area) trong mã của bạn để chỉ ra kết quả như mong muốn (ở đây x * y).

Bạn có thể sử dụng chức năng này từ các nơi khác trong mã của bạn đơn giản bằng cách sử dụng tên của Function và đưa ra một giá trị của các tham số .

Đặt một Function và Subnút bấm trên bảng tính của bạn và thêm các dòng mã sau :

Dim z As Double

z = Area(3, 5) + 2

MsgBox z

Giải thích : Hàm trả về một giá trị , do đó bạn có để 'lấy' giá trị này trong mã của bạn . Bạn có thể sử dụng một biến khác (z) cho việc này . Tiếp theo, bạn có thể thêm các giá trị khác để biến này (nếu bạn muốn) . Cuối cùng , hiển thị giá trị sử dụng một MsgBox .

Kết quả khi bạn nhấp chuột vào nút bấm trên bảng :

Sub Nếu bạn muốn Excel VBA để thực hiện một số hành động, bạn có thể sử dụng một Sub. Đặt một Sub vào một module ( Trong Visual Basic Editor, nhấn Insert, Module) . Ví dụ , Sub với tên Area.

Function và Sub

Sub Area(x As Double, y As Double)

MsgBox x * y

End Sub

Function và Sub

Function và SubGiải thích : sub này có hai đối số (kiểu Double) . Nó không trả lại giá trị ! Bạn có thể tham khảo phụ này ( gọi sub ) từ các nơi khác trong mã của bạn bằng cách đơn giản bằng cách sử dụng tên của tiểu và đưa ra một giá trị của các tham số .

Đặt một Function và Subnút lệnh trên bảng tính của bạn và thêm dòng mã sau :

Area 3, 5

Kết quả khi bạn nhấp chuột vào nút lệnh trên bảng :

Bạn có thể thấy sự khác biệt giữa Function và Sub? Function trả lại những giá trị 15. Chúng tôi bổ sung các giá trị 2 cho kết quả này và hiển thị các kết quả cuối cùng . Khi chúng tôi gọi Sub chúng tôi không có kiểm soát nhiều hơn kết quả ( 15 ) bởi vì một SUB không thể trả về một giá trị!

Read more

Application Object

Excel là một trong những dạng đối tượng. Chúng tôi gọi đó là các đối tượng Ứng dụng. Các đối tượng ứng dụng cho phép truy cập đến rất nhiều lựa chọn liên quan đến Excel .
WorksheetFunction
Bạn có thể sử dụng thuộc tính WorksheetFunction trong Excel VBA để truy cập các chức năng Excel . 1 . Ví dụ , đặt một Application Objectnút lệnh trên bảng tính của bạn và thêm dòng mã sau :

Range("A3").Value = Application.WorksheetFunction.Average(Range("A1:A2"))

Khi bạn nhấp chuột vào nút CommandButton1 trên bảng tính, Excel VBA tính trung bình cộng của các giá trị trong ô A1 và ô A2 và đặt kết quả vào ô A3 .
 Lưu ý : thay vì Application.WorksheetFunction.Average , chỉ đơn giản là sử dụng WorksheetFunction.Average . Nếu bạn nhìn vào thanh công thức, bạn có thể thấy rằng các công thức đó không được đưa vào ô A3 . Để chèn các công thức đó vào ô A3 , sử dụng dòng mã sau :

Range("A3").Value = "=AVERAGE(A1:A2)"

ScreenUpdating
Đôi khi bạn có thể tìm thấy hữu ích khi tắt chức năng cập nhật màn hình tự động (để tránh nhấp nháy) trong khi thực hiện mã. Kết quả là , mã của bạn sẽ chạy nhanh hơn .
1 . Ví dụ , đặt một Application Objectnút lệnh trên bảng tính của bạn và thêm các dòng mã sau :

Dim i As Integer

For i = 1 To 10000
Range("A1").Value = i
Next i

Khi bạn nhấp chuột vào nút lệnh trên bảng tính, Excel VBA hiển thị mỗi giá trị là một phần nghìn giây và điều này có thể mất thời gian .
 2 . Để tăng tốc độ quá trình, cập nhật các mã như sau .

Dim i As Integer

Application.ScreenUpdating = False

For i = 1 To 10000
Range("A1").Value = i
Next i

Application.ScreenUpdating = True

Kết quả là , mã của bạn sẽ chạy nhanh hơn và bạn sẽ chỉ nhìn thấy kết quả cuối cùng ( 10000 ) .

DisplayAlerts
Bạn có thể hướng dẫn Excel VBA không hiển thị cảnh báo trong khi thực thi mã .
1 . Ví dụ , đặt một Application Objectnút lệnh trên bảng tính của bạn và thêm dòng mã sau :

ActiveWorkbook.Close

Khi bạn nhấp chuột vào nút lệnh trên bảng tính, Excel VBA đóng tập tin Excel của bạn và yêu cầu bạn lưu các thay đổi bạn đã thực hiện .

 2 . Để hướng dẫn Excel VBA không hiển thị cảnh báo này trong khi thực thi mã , cập nhật các mã như sau .

Application.DisplayAlerts = False

Application.DisplayAlerts = False

ActiveWorkbook.Close

Application.DisplayAlerts = True

Kết quả là , Excel VBA đóng tập tin Excel của bạn , mà không yêu cầu bạn lưu các thay đổi bạn đã thực hiện . Bất kỳ thay đổi chưa nhớ sẽ bị mất.
Tính toán
Theo mặc định , tính toán được thiết lập để tự động. Kết quả là , Excel sẽ tính toán lại bảng tính tự động mỗi khi một giá trị ảnh hưởng đến một công thức thay đổi . Nếu bảng tính của bạn có chứa nhiều công thức phức tạp, bạn có thể tăng tốc độ macro của bạn bằng cách thiết lập tính toán thủ công.
1 . Ví dụ , đặt một Application Objectnút lệnh trên bảng tính của bạn và thêm dòng mã sau :

Application.Calculation = xlCalculationManual

Khi bạn nhấp chuột vào nút lệnh trên bảng tính, Excel VBA lập tính toán thủ công.

2 . Bạn có thể xác minh điều này bằng cách nhấp vào File, Options, công thức . 3 . Bây giờ khi bạn thay đổi giá trị của ô A1 , giá trị của ô B1 là không tính toán lại. Bạn có thể tự tính toán lại bảng tính, bằng cách nhấn F9 . 4. Trong hầu hết các trường hợp, bạn sẽ thiết lập tính năng tự động trở lại vào cuối của mã của bạn . Đơn giản chỉ cần thêm dòng mã sau đây để đạt được điều này .

Application.Calculation = xlCalculationAutomatic
Excel là một trong những dạng đối tượng. Chúng tôi gọi đó là các đối tượng Ứng dụng. Các đối tượng ứng dụng cho phép truy cập đến rất nhiều lựa chọn liên quan đến Excel .
WorksheetFunction
Bạn có thể sử dụng thuộc tính WorksheetFunction trong Excel VBA để truy cập các chức năng Excel . 1 . Ví dụ , đặt một Application Objectnút lệnh trên bảng tính của bạn và thêm dòng mã sau :

Range("A3").Value = Application.WorksheetFunction.Average(Range("A1:A2"))

Application Object

Khi bạn nhấp chuột vào nút CommandButton1 trên bảng tính, Excel VBA tính trung bình cộng của các giá trị trong ô A1 và ô A2 và đặt kết quả vào ô A3 .
 Lưu ý : thay vì Application.WorksheetFunction.Average , chỉ đơn giản là sử dụng WorksheetFunction.Average . Nếu bạn nhìn vào thanh công thức, bạn có thể thấy rằng các công thức đó không được đưa vào ô A3 . Để chèn các công thức đó vào ô A3 , sử dụng dòng mã sau :

Range("A3").Value = "=AVERAGE(A1:A2)"

ScreenUpdating
Đôi khi bạn có thể tìm thấy hữu ích khi tắt chức năng cập nhật màn hình tự động (để tránh nhấp nháy) trong khi thực hiện mã. Kết quả là , mã của bạn sẽ chạy nhanh hơn .
1 . Ví dụ , đặt một Application Objectnút lệnh trên bảng tính của bạn và thêm các dòng mã sau :

Dim i As Integer

For i = 1 To 10000
Range("A1").Value = i
Next i

Khi bạn nhấp chuột vào nút lệnh trên bảng tính, Excel VBA hiển thị mỗi giá trị là một phần nghìn giây và điều này có thể mất thời gian . 2 . Để tăng tốc độ quá trình, cập nhật các mã như sau .

Dim i As Integer

Application.ScreenUpdating = False
For i = 1 To 10000
Range("A1").Value = i
Next i
Application.ScreenUpdating = True

Kết quả là , mã của bạn sẽ chạy nhanh hơn và bạn sẽ chỉ nhìn thấy kết quả cuối cùng ( 10000 ) .

DisplayAlerts
Bạn có thể hướng dẫn Excel VBA không hiển thị cảnh báo trong khi thực thi mã .
1 . Ví dụ , đặt một Application Objectnút lệnh trên bảng tính của bạn và thêm dòng mã sau :

ActiveWorkbook.Close

Khi bạn nhấp chuột vào nút lệnh trên bảng tính, Excel VBA đóng tập tin Excel của bạn và yêu cầu bạn lưu các thay đổi bạn đã thực hiện .

 2 . Để hướng dẫn Excel VBA không hiển thị cảnh báo này trong khi thực thi mã , cập nhật các mã như sau .

Application.DisplayAlerts = False

Application.DisplayAlerts = False

ActiveWorkbook.Close

Application.DisplayAlerts = True

Kết quả là , Excel VBA đóng tập tin Excel của bạn , mà không yêu cầu bạn lưu các thay đổi bạn đã thực hiện . Bất kỳ thay đổi chưa nhớ sẽ bị mất.
Tính toán
Theo mặc định , tính toán được thiết lập để tự động. Kết quả là , Excel sẽ tính toán lại bảng tính tự động mỗi khi một giá trị ảnh hưởng đến một công thức thay đổi . Nếu bảng tính của bạn có chứa nhiều công thức phức tạp, bạn có thể tăng tốc độ macro của bạn bằng cách thiết lập tính toán thủ công.
1 . Ví dụ , đặt một Application Objectnút lệnh trên bảng tính của bạn và thêm dòng mã sau :

Application.Calculation = xlCalculationManual

Khi bạn nhấp chuột vào nút lệnh trên bảng tính, Excel VBA lập tính toán thủ công.

2 . Bạn có thể xác minh điều này bằng cách nhấp vào File, Options, công thức . 3 . Bây giờ khi bạn thay đổi giá trị của ô A1 , giá trị của ô B1 là không tính toán lại.
 Bạn có thể tự tính toán lại bảng tính, bằng cách nhấn F9 .
 4. Trong hầu hết các trường hợp, bạn sẽ thiết lập tính năng tự động trở lại vào cuối của mã của bạn . Đơn giản chỉ cần thêm dòng mã sau đây để đạt được điều này .

Application.Calculation = xlCalculationAutomatic

Read more

ActiveX Controls

Bài này tìm hiểu làm thế nào để tạo điều khiển < strong> ActiveX < / strong > như nút bấm, hộp văn bản , hộp danh sách , vv Để tạo một điều khiển ActiveX trong < strong> Excel VBA < / strong > , thực hiện các bước sau đây . 1 . Trên Developer Tab, nhấn Insert 2 . Ví dụ , trong nhóm các điều khiển ActiveX , vào Command Button để chèn một nút điều khiển lệnh.

3 . Kéo một nút Command trên bảng tính của bạn .

4 . Nhấp chuột phải vào nút bấm (ở chế độ Design Mode) .

5 . Nhấn vào View Code .

Lưu ý : bạn có thể thay đổi các chú thích và tên của một điều khiển bằng cách click chuột phải vào điều khiển (ở chế độ Design Mode) và sau đó nhấp vào Properties. Thay đổi tiêu đề của nút bấm thành Apply ' . Còn bây giờ , chúng ta đặt tên CommandButton1 cho nút bấm.

Visual Basic Editor xuất hiện.

6 . Thêm dòng mã được hiển thị dưới đây giữa Private Sub CommandButton1_Click ( ) và End Sub . 7 . Chọn vùng B2: B4 và nhấp vào nút bấm (không ở chế độ Design Mode) Kết quả:

Read more