Trong bài này, chúng ta sẽ tìm hiểu cách tạo các hàm do người dùng định nghĩa trong Microsoft Excel bằng cách sử dụng VBA.

_ Hàm do người dùng xác định: – Microsoft Excel đã có rất nhiều hàm, tuy nhiên mỗi người có những yêu cầu, tình huống khác nhau, chúng ta có thể tạo một hàm riêng theo yêu cầu gọi là Hàm do người dùng xác định. Chúng ta có thể sử dụng hàm User Defined giống như các hàm khác trong Excel._

Dưới đây là các chủ đề mà chúng tôi sẽ tạo hàm do người dùng xác định: 1). Làm thế nào để đếm số từ trong Ô hoặc phạm vi?

2). Làm cách nào để trích xuất một từ từ một Câu hoặc Ô trong Excel?

3). Làm thế nào để tạo công thức cho ISO?

4). Làm thế nào để biết Trang tính và tên Sổ làm việc bằng VBA?

5). Làm thế nào để trích xuất từ ​​đầu tiên và cuối cùng từ một ô trong Excel?

Làm cách nào để tạo hàm do người dùng xác định để đếm số từ trong Ô hoặc Phạm vi? Chúng tôi có dữ liệu trong trang tính 1 trong đó chúng tôi có một số địa chỉ vì vậy chúng tôi muốn đếm các từ trong một ô hoặc một phạm vi bằng cách tạo hàm do người dùng xác định thông qua VBA trong Excel.

image 1

Để thực hiện chức năng do người dùng xác định, vui lòng làm theo các bước dưới đây: – * Mở VBA Page và nhấn phím Alt + F11.

  • Chèn một mô-đun.

Viết đoạn mã được đề cập bên dưới:

Function WORDSCOUNT(rRange As Range) As Long

Dim rCell As Range

Dim Count As Long

For Each rCellInrRange

lCount = lCount + Len(Trim(rCell)) - Len(Replace(Trim(rCell), " ", "")) + 1

Next rCell

WORDSCOUNT = lCount

End Function

Giải thích mã: – Để tạo hàm do người dùng định nghĩa, chúng ta bắt đầu mã để đặt tên hàm và định nghĩa các biến. Chúng tôi đã sử dụng “Đối với mỗi vòng lặp” trong mã để đếm các từ trong phạm vi.

Làm thế nào để sử dụng hàm này trong Excel?

Để sử dụng chức năng này, hãy làm theo các bước sau: – * Đi tới Trang tính Excel.

  • Để đếm các từ cho một ô, hãy nhập công thức vào ô D7.

  • = WORDSCOUNT (C7), ô C7 là ô mà chúng ta muốn tính các từ.

  • Hàm sẽ trả về 6, có nghĩa là ô C7 chứa 6 từ.

  • Để thực hiện phép tính tương tự cho các ô còn lại, hãy sao chép cùng một công thức và dán vào phạm vi.

image 2

  • Để đếm các từ trong phạm vi, hãy sử dụng công thức là = WORDSCOUNT (C7: C16), và nhấn Enter.

  • Hàm sẽ trả về số lượng từ.

image 3

Lưu ý: – UDF này sẽ hữu ích để đếm các từ trong một phạm vi hoặc trong một ô.

Bây giờ chúng ta sẽ viết mã để đếm từ bằng cách sử dụng dấu phân cách đã chỉ định (,). Thực hiện theo các bước sau: –

Function SEPARATECOUNTWORDS(rRange As Range, Optional separator As Variant) As Long

Dim rCell As Range

Dim Count As Long

If IsMissing(separator) Then

separator = ","

End If

For Each rCellInrRange

lCount = lCount + Len(Trim(rCell)) - Len(Replace(Trim(rCell), separator, ""))

Next rCell

SEPARATECOUNTWORDS = lCount

End Function

Để sử dụng chức năng này, hãy làm theo các bước sau: –

  • Chuyển đến Trang tính Excel.

  • Để đếm các dấu phân cách cụ thể trong từ, chúng tôi sẽ sử dụng hàm đã xác định này.

  • = SEPARATECOUNTWORDS (C7) và nhấn Enter.

  • Hàm sẽ trả về số lượng các dấu phân cách cụ thể.

image 4

Làm cách nào để trích xuất một từ từ một câu hoặc ô trong Microsoft Excel bằng VBA? Chúng tôi có dữ liệu trong sheet1. Trong đó chúng tôi có một số địa chỉ vì vậy chúng tôi muốn trích xuất các từ từ một câu hoặc ô hoặc một phạm vi bằng cách tạo hàm do người dùng xác định thông qua VBA trong Excel.

image 5

Để tạo chức năng do người dùng xác định, vui lòng làm theo các bước sau: –

  • Mở VBA Page và nhấn phím Alt + F11.

  • Chèn một mô-đun.

Viết đoạn mã được đề cập bên dưới *: –

Function GETWORD(Text As Variant, N As Integer, Optional Delimiter As Variant) As String

If IsMissing(Delimiter) Then

Delimiter = " "

End If

GETWORD = Split(Text, Delimiter)(N - 1)

End Function

Giải thích mã: – Trong đoạn mã trên, chúng ta đã đề cập đến tên hàm với các biến. Và sau đó chúng tôi đã xác định các tiêu chí để trích xuất từ ​​từ câu hoặc ô.

Bây giờ chúng ta sẽ học cách sử dụng công thức này. Thực hiện theo các bước sau: – * Đi tới Trang tính Excel.

  • Sử dụng công thức này trong ô D7.

  • = GETWORD (C7,2) và nhấn Enter.

  • Hàm sẽ trả về từ thứ hai từ ô vì trong công thức chúng ta đã đề cập đến từ 2 ^ nd ^ number. Nếu bạn muốn truy xuất từ ​​được đặt ở vị trí 3 ^ rd ^, bạn cần thay đổi số từ 2 thành 3 trong công thức.

image 6

Làm thế nào để tạo công thức số Tuần ISO trong Microsoft Excel bằng VBA? Chúng ta sẽ tìm hiểu cách tạo công thức số tuần ISO trong Excel với UDF này. Chức năng này chúng tôi sẽ sử dụng để xác định rằng ngày được đề cập thuộc về số tuần nào trong năm.

Chúng tôi có một danh sách ngày trong trang tính và trong cột thứ hai, chúng tôi muốn truy xuất số tuần.

image 7

Để tạo UDF cho yêu cầu này, hãy làm theo các bước sau: – * Mở Trang VBA và nhấn phím Alt + F11.

  • Chèn một mô-đun.

  • Viết đoạn mã được đề cập bên dưới: –

Function ISOWEEKNUMBER(Indate As Date) As Long

Dim Dt As Date

Dt = DateSerial(Year(Indate - Weekday(Indate - 1) + 4), 1, 3)

ISOWEEKNUMBER = Int((Indate - Dt + Weekday(Dt) + 5) / 7)

End Function

Giải thích mã: -: – Trong đoạn mã trên, chúng ta đã đề cập đến tên hàm với các biến. Và sau đó chúng tôi đã đặt giá trị ngày và sau đó chúng tôi đã xác định tiêu chí của hàm “ISOWEENUMBER”.

Làm cách nào chúng ta có thể sử dụng hàm này trong tệp Excel của mình?

  • Chuyển đến Trang tính Excel.

  • Nhập công thức vào ô D7.

  • = ISOWEEKNUMBER (C7) và nhấn Enter.

  • Hàm sẽ trả về tuần cho ngày đã nhập trong ô.

Bây giờ để truy xuất số tuần cho mỗi ngày, hãy sao chép cùng một công thức trong phạm vi.

image 8

Bây giờ chúng ta sẽ học cách trả lại các tiêu chuẩn ISO bắt đầu của năm trong Excel- Thứ Hai đầu tiên của năm.

Về cơ bản, hàm này sẽ kiểm tra xem ngày 1 ^ st ^ thứ hai của năm sẽ rơi vào ngày nào và sau đó nó sẽ bắt đầu tính số tuần kể từ ngày đó. Hãy xem cách chúng tôi có thể tạo UDF cho yêu cầu này.

image 9

Thực hiện theo các bước dưới đây: – * Mở VBA Page và nhấn phím Alt + F11.

  • Chèn một mô-đun.

Viết đoạn mã được đề cập bên dưới *: –

Function ISOSTYR(Year As Integer) As Date

Dim WD As Integer

Dim NY As Date

NY = DateSerial(Year, 1, 1)

WD = (NY - 2) Mod 7

If WD < 4 Then

ISOSTYR = NY - WD

Else

ISOSTYR = NY - WD + 7

End If

End Function

Giải thích mã: – Trong đoạn mã trên, chúng ta đã đề cập đến tên hàm với các biến. Và, sau đó chúng tôi đã thiết lập tiêu chí cho các biến và sau đó chúng tôi đã xác định đầu vào công thức.

Bạn chỉ cần cung cấp năm 2001 ở định dạng này và công thức sẽ cung cấp cho bạn 1 ^ st ^ Thứ Hai trong năm.

Bây giờ chúng ta sẽ học cách sử dụng UDF trong tệp Excel. Thực hiện theo các bước sau: – * Đi tới Trang tính Excel.

  • Nhập công thức vào ô D7.

  • = ISOSTYR (C7) và nhấn Enter.

  • Hàm sẽ trả về ngày của thứ Hai 1 ^ st ^ của tuần đầu tiên của Năm Mới.

  • Để trả về ngày thứ Hai 1 ^ st ^ của tuần đầu tiên của Năm Mới, hãy sao chép cùng một công thức và dán vào phạm vi.

image 10

Làm cách nào để biết tên Bảng tính và Sổ làm việc bằng cách sử dụng VBA trong Microsoft Excel? Thực hiện theo các bước và mã dưới đây: – * Mở VBA Page và nhấn phím Alt + F11.

  • Chèn một mô-đun.

Viết đoạn mã được đề cập bên dưới *: –

Function Worksheetname()

Worksheetname = Range("A1").Parent.Name

End Function

Giải thích mã: – Trong đoạn mã trên, chúng ta đã đề cập đến tên hàm và sau đó chúng ta đã xác định cách biết tên trang tính.

Để sử dụng công thức này, bạn chỉ cần nhập công thức vào bất kỳ ô nào theo cách này: – = Worksheetname (). Hàm sẽ trả về tên trang tính.

image 11

Để tạo chức năng cho tên Workbook, hãy làm theo các bước và mã dưới đây: – * Mở VBA Page, nhấn phím Alt + F11.

  • Chèn một mô-đun.

  • Viết đoạn mã được đề cập bên dưới: –

Function Workbookname()

Workbookname = ThisWorkbook.Name

End Function

Giải thích mã: -: – Trong đoạn mã trên, chúng ta đã đề cập đến tên hàm và sau đó chúng ta đã xác định cách làm thế nào để biết tên sổ làm việc.

Để sử dụng công thức này, bạn chỉ cần nhập công thức vào bất kỳ ô nào theo cách này: – = Workbookname (). Hàm sẽ trả về tên trang tính.

image 12

Làm thế nào để trích xuất từ ​​đầu tiên và cuối cùng từ một ô bằng cách sử dụng VBA trong Microsoft Excel? Chúng tôi có dữ liệu trong sheet1 trong đó chúng tôi có một số địa chỉ vì vậy chúng tôi muốn trích xuất từ ​​cuối cùng và từ đầu tiên từ một câu hoặc ô hoặc một phạm vi bằng cách tạo hàm do người dùng xác định thông qua VBA trong Excel.

image 13

Đầu tiên, chúng ta sẽ viết hàm để trích xuất từ ​​đầu tiên. Vui lòng làm theo các bước sau: – * Mở Trang VBA, nhấn phím Alt + F11.

Chèn một mô-đun Viết đoạn mã được đề cập bên dưới *: –

Function GETFW(Text As String, Optional Separator As Variant)

Dim FW As String

If IsMissing(Separator) Then

Separator = " "

End If

FW = Left(Text, InStr(1, Text, Separator, vbTextCompare))

GETFW = Replace(FW, Separator, "")

End Function

Giải thích mã: – Trong đoạn mã trên, chúng ta đã đề cập đến tên hàm với các biến. Và sau đó chúng tôi đã xác định các tiêu chí để trích xuất từ ​​từ câu hoặc ô.

Bây giờ chúng ta sẽ học cách sử dụng công thức này. Thực hiện theo các bước sau: –

  • Chuyển đến Trang tính Excel.

  • Sử dụng công thức này trong ô D9.

  • = GETFW (C9) và nhấn Enter.

  • Hàm sẽ trả về từ đầu tiên từ dữ liệu. Bây giờ, để truy xuất từ ​​đầu tiên cho tất cả các ô, hãy sao chép cùng một công thức trong phạm vi.

image 14

  • Bây giờ chúng ta sẽ viết mã để trích xuất từ ​​cuối cùng từ ô.

Làm theo đoạn mã được đề cập dưới đây: – * Mở Trang VBA, nhấn phím Alt + F11.

  • Chèn một mô-đun.

Viết đoạn mã được đề cập bên dưới *: –

Function GETLW(Text As String, Optional Separator As Variant)

Dim LW As String

If IsMissing(Separator) Then

Separator = " "

End If

LW = StrReverse(Text)

LW = Left(lastword, InStr(1, LW, Separator, vbTextCompare))

GETLW = StrReverse(Replace(LW, Separator, ""))

End Function

Bây giờ chúng ta sẽ học cách sử dụng công thức này. Thực hiện theo các bước sau: – * Đi tới Trang tính Excel.

  • Sử dụng công thức này trong ô D9.

  • = GETLW (C9) Nhấn Enter.

  • Hàm sẽ trả về từ cuối cùng từ dữ liệu. Bây giờ, để truy xuất từ ​​cuối cùng cho tất cả các ô, hãy sao chép cùng một công thức trong phạm vi.

image 15

Đây là những hàm chúng ta có thể xác định thông qua VBA và sau đó có thể sử dụng nó làm công thức của Excel. Ngoài ra, chúng ta có thể tạo thêm nhiều chức năng do người dùng xác định.

Hãy tiếp tục học hỏi với chúng tôi, chúng tôi sẽ đưa ra những công thức khó hơn.

Excel