4. Các ví dụ về Hàm tự định nghĩa

Tổng hợp: phamnhukhang, ttphong2007 (GPE)

Mục lục ví dụ:

1. Hàm trả về tên sheet hiện hành

2. Hàm trả về tên workbook hiện hành

3. Hàm trả về đường dẫn đến workbook hiện hành

4. Hàm trả về tên đầy đủ của workbook

5. Hàm trả về User hiện tại của Windows hoặc Excel

6. Hàm trả về công thức trong một ô (cell) xác định

7. Hàm kiểm tra xem một ô có chứa công thức hay không

8. Hàm kiểm tra xem một ô (cell) trong bảng tính có đang áp dụng tính năng Data Validation hay không

9. Hàm kiểm tra xem một ô (cell) có chứa chú thích (comment) hay không

10. Hàm tính tổng các ô tô nền theo màu xác định

11. Hàm tính tổng các ô chứa Font chữ theo màu xác định

12. Hàm xoá bỏ các số 0 đứng trước giá trị số khỏi một ô xác định

13. Hàm xoá bỏ các ký tự chữ khỏi một ô xác định

14. Hàm xoá bỏ tất cả ký tự số khỏi một ô xác định

15. Hàm xác định vị trí của ký tự số đầu tiên trong chuỗi cho trước trong ô

16. Hàm tính tổng tất cả chữ số của con số cho trước trong ô (cell)

17. Hàm tính tổng tất cả chữ số của các con số trong vùng (range)
18. Hàm kiểm tra một worksheet có rỗng hay không

19. Hàm kiểm tra một worksheet có đang được bảo vệ (protected) hay không

20. Hạm tạo AutoText của riêng bạn

1. Hàm trả về tên sheet hiện hành

1. Nhấn <Alt+F11> để mở cửa sồ VBE (Visual Basic Editor)
2. Vào Insert, chọn Module.
3. Nhập vào đoạn mã sau:

Function TabName() 
   TabName = ActiveSheet.Name 
End Function 

4. Đóng cửa sổ VBA Editor bằng phím tắt <Alt+Q>, và tại ô A1 nhập vào hàm sau: =TabName().
5. Nhấn <Enter> và xem kết quả

Top

2. Hàm trả về tên workbook hiện hành

Excel có sẵn hàm =CELL("Filename") trả về tên workbook kèm theo đường dẫn. Cách dễ hơn để lấy tên workbook là viết hàm tự tạo.

1. Nhấn <Alt+F11> vào VBE.
2. Vào Insert, chọn Module.
3. Nhập đoạn mã sau:

Function WkbName() 
   WkbName = ActiveWorkbook.Name 
End Function 

4. Đóng cửa sổ VBE bằng <Alt+Q>, tại ô A3 nhập vào hàm: =WkbName().
5. Nhấn <Enter> và xem kết quả.

Top

3. Hàm trả về đường dẫn đến workbook hiện hành

1. Nhấn <Alt+F11> vào VBE.
2. Vào Insert, chọn Module.
3. Nhập đoạn mã sau:

Function WkbPath() 
   WkbPath = ActiveWorkbook.Path 
End Function 

4. Đóng cửa sổ VBE và tại ô A4 nhập vào hàm sau: =WkbPath().
5. Nhấn <Enter> và xem kết quả.

Top

4. Hàm trả về tên đầy đủ của workbook

Chúng ta đã viết hàm trả về tên workbook và đường dẫn đến workbook. Để lấy tên workbook đầy đủ (đường dẫn và tên) thì chúng ta dùng hàm sau.

1. Nhấn <Alt+F11> vào VBE.
2. Vào Insert, chọn Module.
3. Nhập đoạn mã sau:

Function WkbFull() 
   WkbFull = ActiveWorkbook.FullName 
End Function 

4. Đóng cửa sổ VBE và tại ô A5 nhập vào công thức: =WkbFull().
5. Nhấn <Enter> và xem kết quả.

Top

5. Hàm trả về User hiện tại của Windows hoặc Excel

Để lấy tên User Windows hiện hành:

1. Nhấn <Alt+F11> vào VBE.
2. Vào Insert, chọn Module.
3. Nhập đoạn mã sau:

Function User() 
   User = Environ("Username") 
End Function 

4. Đóng cửa sổ VBE và chọn ô A6 nhập vào hàm sau: =User().
5. Nhấn <Enter> và xem kết quả.

Để lấy tên User Excel hiện hành:

1. Nhấn <Alt+F11> vào VBE.
2. Vào Insert, chọn Module.
3. Nhập đoạn mã sau:

4. Đóng cửa sổ VBE và chọn ô A7 nhập vào hàm sau: =ExcelUser().
5. Nhấn <Enter> và xem kết quả.

Top

6. Hàm trả về công thức trong một ô (cell) xác định

Ứng dụng rất tốt cho trường hợp chúng ta muốn hiển thị công thức dạng Text bên cạnh ô tính toán

1. Nhấn <Alt+F11> vào VBE.
2. Vào Insert, chọn Module.
3. Nhập đoạn mã sau:

Function FormT(rng As Range) 
   FormT = " " & rng.Formula 
End Function 

4. Đóng cửa sổ VBE và tại ô B5 nhập vào hàm: =FormT(A5) để xem công thức dùng trong ô A5
5. Nhấn <Enter> và xem kết quả.

Top

7. Hàm kiểm tra xem một ô có chứa công thức hay không

1. Nhấn <Alt+F11> vào VBE.
2. Vào Insert, chọn Module.
3. Nhập đoạn mã sau:

Function FormYes(rng As Range) 
   FormYes = rng.HasFormula 
End Function 

4. Đóng cửa sổ VBE và tại ô B1 nhập vào hàm: =FormYes(A1).
5. Chép xuống đến ô B5 và xem kết quả, nếu ô chứa công thức thì kết quả trả về True ngược lại thì trả về False.

Top

8. Hàm kiểm tra xem một ô (cell) trong bảng tính có đang áp dụng tính năng Data Validation hay không

1. Nhấn <Alt+F11> vào VBE.
2. Vào Insert, chọn Module.
3. Nhập đoạn mã sau:

Function Valid(rng As Range) 
   Dim intV As Integer 
   On Error GoTo errorM 
   intV = rng.Validation.Type 
   Valid = True 
   Exit Function 
errorM: 
   Valid = False 
End Function 

4. Đóng cửa sổ VBE và tại ô C1 nhập vào hàm: =Valid(A1) để kiểm tra ô A1
5. Nhấn <Enter> và xem kết quả.

Top

9. Hàm kiểm tra xem một ô (cell) có chứa chú thích (comment) hay không

1. Nhấn <Alt+F11> vào VBE.
2. Vào Insert, chọn Module.
3. Nhập đoạn mã sau:

Function ComT(rng As Range) 
   On Error GoTo errorM 
   If Len(rng.Comment.Text) > 0 Then ComT = True 
   Exit Function 
errorM: 
   ComT = False 
End Function 

4. Đóng cửa sổ VBE, chọn vùng C1:C5 và nhập vào hàm: =ComT(A1).
5. Nhấn <Ctrl+Enter> và xem kết quả.

Top

10. Hàm tính tổng các ô tô nền theo màu xác định

1. Nhấn <Alt+F11> vào VBE.
2. Vào Insert, chọn Module.
3. Nhập đoạn mã sau:

Function SumColor(Area As Range, Ci As Integer) 
   Dim sng As Single, rng As Range 
   For Each rng In Area 
      If rng.Interior.ColorIndex = Ci Then sng = 
      sng+rng.Value 
   Next rng 
   SumColor = sng 
End Function 

4. đóng cửa sổ VBE và tại ô C6 nhập vào hàm: =SumColor(A1:A5,3).
5. Nhấn <Enter> và xem kết quả.

Ghi chú: Màu nền (1=black, 2=white, 3=red, 4=green,5=blue, ...).

Top

11. Hàm tính tổng các ô chứa Font chữ theo màu xác định

1. Nhấn <Alt+F11> vào VBE.
2. Vào Insert, chọn Module.
3. Nhập đoạn mã sau:

Function SumColorF(Area As Range, Ci As Integer) 
   Dim sng As Single, rng As Range 
   For Each rng In Area 
      If rng.Font.ColorIndex = Ci Then sng = 
      sng+rng.Value 
   Next rng 
   SumColorF = sng 
End Function 

4. Đóng cửa sổ VBE, tại ô A6 nhập vào hàm: =SumColorF(A1:A5,5).
5. Nhấn <Enter> và xem kết quả.

Ghi chú: Màu nền (1=black, 2=white, 3=red, 4=green,5=blue, ...)

Top

12. Hàm xoá bỏ các số 0 đứng trước giá trị số khỏi một ô xác định

1. Nhấn <Alt+F11> vào VBE.
2. Vào Insert, chọn Module.
3. Nhập đoạn mã sau:

Function KillZeros(rng As Range) 
   Dim intS As Integer 
   intS = rng 
   While intS - Int(intS) > 0 
   intS = intS * 10 
   Wend 
   KillZeros = intS 
End Function 

4. Đóng cửa sổ VBE và chọn vùng B1:B5 nhập vào hàm: =KillZeros(A1).
6. Nhấn <Ctrl+Enter> và xem kết quả.

Top

13. Hàm xoá bỏ các ký tự chữ khỏi một ô xác định

1. Nhấn <Alt+F11> vào VBE.
2. Vào Insert, chọn Module.
3. Nhập đoạn mã sau:

Function LetterOut(rng As Range) 
   Dim i As Integer 
   For i = 1 To Len(rng) 
      Select Case Asc (Mid(rng.Value, i, 1)) 
      Case 0 To 64, 123 To 197 
         LetterOut = LetterOut & Mid(rng.Value, i, 1) 
      End Select 
   Next i 
End Function 

4. Đóng cửa sổ VBE, chọn vùng B1:B5 và nhập vào hàm: =LetterOut(A1).
5. Nhấn <Ctrl+Enter>.

Top

14. Hàm xoá bỏ tất cả ký tự số khỏi một ô xác định

1. Nhấn <Alt+F11> vào VBE.
2. Vào Insert, chọn Module.
3. Nhập đoạn mã sau:

Function NumberOut(rng As Range) 
   Dim i As Integer 
   For i = 1 To Len(rng) 
      Select Case Asc (Mid(rng.Value, i, 1)) 
      Case 0 To 64, 123 To 197 
      Case Else 
         NumberOut = NumberOut & Mid(rng.Value, i, 1) 
      End Select 
   Next i 
End Function 

4. Đóng cửa sổ VBE, chọn vùng B1:B5 và nhập vào hàm: =NumberOut(A1).
5. Nhấn <Ctrl+Enter>.

Top

15. Hàm xác định vị trí của ký tự số đầu tiên trong chuỗi cho trước trong ô

1. Nhập các chuỗi vào vùng A1:A5.
2. Nhấn <Alt+F11> vào VBE.
3. Vào Insert, chọn Module.
4. Nhập đoạn mã sau:

Function FirstNum(rng As Range) 
   Dim i As Integer 
   For i = 1 To Len(rng.Value) 
      Select Case Mid(rng.Value, i, 1) 
      Case 0 To 9 
         FirstNum = i 
         Exit Function 
      End Select 
   Next i 
End Function 

5. Đóng cửa sổ VBE, chọn vùng B1:B5 và nhập vào hàm: =FirstNum(A1).
6. Nhấn <Ctrl+Enter>.

Top

16. Hàm tính tổng tất cả chữ số của con số cho trước trong ô (cell)

1. Nhấn <Alt+F11> vào VBE.
2. Vào Insert, chọn Module.
3. Nhập đoạn mã sau:

Function Qs(rng As Range) 
   Dim i As Integer 
   For i = 1 To Len(rng.Value) 
      Qs = Qs+Cint (Mid(rng.Value, i, 1)) 
   Next i 
End Function 

4. Đóng cửa sổ VBE, chọn vùng B1:B5 và nhập vào hàm: =Qs(A1).
6. Nhấn <Ctrl+Enter>.

Top

17. Hàm tính tổng tất cả chữ số của các con số trong vùng (range)

1. Nhấn <Alt+F11> vào VBE.
2. Vào Insert, chọn Module.
3. Nhập đoạn mã sau:

Function QsE(Area As Range) 
   Dim i As Integer 
   Dim rng As Range 
   For Each rng In Area 
      For i = 1 To Len(rng.Value) 
         QsE = QsE+CInt (Mid(rng.Value, i, 1)) 
      Next i 
   Next rng 
End Function 

4. Đóng cửa sổ VBE, chọn ô B1 và nhập vào hàm: =QsE(A1:A5).
5. Nhấn <Enter> và xem kết quả.

Top

18. Hàm kiểm tra một worksheet có rỗng hay không

1. Nhấn <Alt+F11> vào VBE.
2. Vào Insert, chọn Module.
3. Nhập đoạn mã sau:

Function ShEmpty(s As String) As Boolean 
   If Application.CountA (Sheets(s).UsedRange) = 0 
   Then 
   ShEmpty = True 
Else 
   ShEmpty = False 
End If 
End Function 

4. Đóng cửa sổ VBE, chọn ô A1 trong Sheet1 nào đó và nhập vào hàm:=ShEmpty("Sheet2") , (với Sheet2 là sheet cần kiểm tra).
6. Nhấn <Enter> và xem kết quả.

Top

19. Hàm kiểm tra một worksheet có đang được bảo vệ (protected) hay không

1. Nhấn <Alt+F11> vào VBE.
2. Vào Insert, chọn Module.
3. Nhập đoạn mã sau:

Function ShProt(s As String) As Boolean 
   On Error GoTo errorM 
   If Sheets(s).ProtectContents = True Then 
      ShProt = True 
   End If 
   Exit Function 
errorM: 
   ShProt = False 
End Function 

4. Đóng cửa sổ VBE, chọn ô A1 trong Sheet1 và nhập vào hàm: =shProt("Sheet2") , (với Sheet2 là sheet cần kiểm tra).
6. Nhấn <Enter> và xem kết quả.

Top
 

20. Hạm tạo AutoText của riêng bạn

1. Nhấn <Alt+F11> vào VBE.
2. Vào Insert, chọn Module.
3. Nhập đoạn mã sau:

Function AuTxt(rng As Range) As String 
   Select Case rng.Value 
   Case 1 
      AuTxt = "fire" 
   Case 2 
      AuTxt = "water" 
   Case 3 
      AuTxt = "heaven" 
   Case Else 
      AuTxt = "invalid text" 
   End Select 
End Function 

4. Đóng cửa sổ VBE, chọn vùng B1:B4 và nhập vào hàm: =AuTxt(A1).
5. Nhấn <Ctrl+Enter>.

Top