VBA Excel Function and Sub Excel VBA

By Lê Quốc Thái

21/06/2019

1

833

Đánh giá lequocthai.com:

Voted: 0 Votes: 0

Your page rank:

Các bài viết có liên quan:
MỘT SỐ VÍ DỤ VỀ CÁC CÔNG THỨC SỬ DỤNG HÀM COUNTIF
VBA EXCEL HỘP THOẠI THÔNG BÁO
CÁC CÔNG THỨC EXCEL ĐỂ TÍNH TỔNG SUM, SUMIF
VBA EXCEL VÍ DỤ VỀ SCRIPTING DICTIONARY

1. Cấu trúc

[Public | Private | Static] Function / Sub

Line(s) code

End Function / Sub

Function/ End Function: Bắt đầu/ kết thúc một hàm.

Sub/ End Sub: Bắt đầu/ kết thúc một thủ tục.

Các từ khóa Public, Private, Static để xác định phạm vi (tầm vực) của Function/ Sub.

- Public: phạm vi toàn cục, ở đâu trong môi trường VBA Project cũng có thể thấy / dùng được.

Ví dụ:

'Module1:
Public Sub Vidu()
MsgBox "Day la mot public_sub."
End Sub

'Module2:
Sub CallPublicSub()
    Call Vidu
    'Hoac:'
    Module1.Vidu
End Sub

- Private: phạm vi nội bộ, các Function/ Sub khác trong cùng môi trường VBA Project mới thấy/ dùng được.

Ví dụ:

'Module1:
Private Sub Vidu()
MsgBox "Day la mot private_sub."
End Sub

'Module2:
Sub CallPrivateSub1()
    Call Vidu
    'Hoac:'
    'Module1.Vidu'
    'Ket qua: Xay ra loi - "Sub or Function not defined" '
End Sub

Tuy nhiên, có cách để gọi một Function/ Sub từ một Module khác được khai báo Private.

Tiếp ví dụ trên:

'Module2:
Sub CallPrivateSub2()
'Cach goi toi mot Private Sub'
Run "Module1.Vidu"
End Sub

- Static: Phạm vi toàn cục, ở đâu trong môi trường VBA Project cũng có thể thấy / dùng được.
Đặc biệt, các biến được khai báo trong Static Sub/ Function được lưu giá trị cho tới khi thoát ứng dụng.
Ví dụ:

'Module1:
Static Sub Vidu()
    Dim a As Long
    a = a + 10
    MsgBox a
    'Chay sub nay 3 lan, bien a van duoc luu gia tri sau cac lan chay sub:'
    'Lan 1: a=10'
    'Lan 2: a=20'
    'Lan 3: a=30'
End Sub

'Module2:
Sub CallStaticSub()
    Call Vidu
    'Chay sub nay sau khi chay sub Module1.Vidu'
    'Hoac:'
    Module1.Vidu
End Sub

2. Function

Cấu trúc:

[Public | Private | Static] Function([ByVal / ByRef | Arguments As type of variables]) [As type of variables]

Line(s) code

End Function

Function/ End Function: Bắt đầu/ kết thúc một hàm.

Public | Private | Static: Không bắt buộc. Các từ khóa để xác định phạm vi (tầm vực) của Function.

ByVal / ByRef: Không bắt buộc. Kiểu truyền đối số cho hàm. Mặc định là ByRef (kiểu tham chiếu).

Arguments As type of variables: Không bắt buộc. Các đối số của hàm, và kiểu dữ liệu khai báo tương ứng với đối số.

[As type of variables]: Không bắt buộc. Kiểu dữ liệu của kết quả trả về của hàm. Không khai báo thì kết quả của hàm có kiểu dữ liệu được gán vào.

Ví dụ:

'//Hàm tách ho tên

Function TachHoTen(ByVal chuoi As String) As Variant
    'Loai khoang trang dâu và cuôi chuôi:'
    chuoi = Trim(chuoi)
    Dim arrTmp As Variant, mangKQ()
    'Cat chuoi hoten bang dâu phân cách: khoang trang'
    arrTmp = Split(chuoi, " ")  'Ket qua tra ve là môt mang 1 chiêu gôm các chu phân cach boi khoang trang'
    ReDim mangKQ(1 To 3)
    mangKQ(1) = arrTmp(LBound(arrTmp))  'Ho'
    mangKQ(3) = arrTmp(UBound(arrTmp))  'Ten'
    mangKQ(2) = Mid(chuoi, Len(mangKQ(1)) + 2, Len(chuoi) - Len(mangKQ(1) & mangKQ(3)) - 2) 'Dem'
    mangKQ(2) = Trim(mangKQ(2)) 'Loai khoang trang nêu có.'
    TachHoTen = mangKQ          'Gán kêt qua cho Function'
End Function

Công thức trên bảng tính:

=INDEX(TachHoTen($A5),1) ‘Tra ve ho

=INDEX(TachHoTen($A5),2) ‘Tra ve ten dem

=INDEX(TachHoTen($A5),3) ‘Tra ve ten

Ví dụ:

'//Tính thê tích khôi hôp

Function TheTichHop(ByVal x As Double, ByVal y As Double, ByVal z As Double) As Double
    TheTichHop = x * y * z
End Function

Công thức trên bảng tính:

=TheTichHop(10,20,34)

=TheTichHop(B9, B10, B11)

3. Sub

sub function lequocthai.com — Sub function lequocthai.com

Cấu trúc:

[Public | Private | Static] Sub([ByVal / ByRef | Arguments As type of variables])

Line(s) code

End Sub

Sub/ End Sub: Bắt đầu/ kết thúc một Sub.

Public | Private | Static: Không bắt buộc. Các từ khóa để xác định phạm vi (tầm vực) của Sub.

ByVal / ByRef | Arguments As type of variables: Không bắt buộc. Các đối số của Sub. ByVal / ByRef là các kiểu truyền đối số.

Ví dụ:

Sub TachHoVaTen() 'Tách ho và tên'
    Dim chuoi, Tmp
    chuoi = Trim(Sheet1.Range("A5").Value)
    Tmp = Split(chuoi, " ")  'Ket qua tra ve là môt mang 1 chiêu gôm các chu phân cach boi khoang trang'
    Sheet1.Range("F5").Value = Tmp(LBound(Tmp))
    Sheet1.Range("H5").Value = Tmp(UBound(Tmp))
    Sheet1.Range("G5").Value = Mid(chuoi, Len(Tmp(LBound(Tmp))) + 2, Len(chuoi) - Len(Tmp(LBound(Tmp)) & Tmp(UBound(Tmp))) - 2)
End Sub

‘—————-‘

Sub TheTich()   'Tính thê tích khôi hôp'
    Dim x As Double, y As Double, z As Double
    x = Sheet1.Range("B9").Value
    y = Sheet1.Range("B10").Value
    z = Sheet1.Range("B11").Value
    MsgBox "Thê tích khôi hôp: " & x * y * z & " dvtt."
End Sub

‘—————-‘

Sub ScreenAndCal_ON()
    Dim i As Long, T As Double
    'Lây gôc thoi gian chay code:'
    T = Timer

    ‘Bât tang tôc cho code’
    SpeedCode True ‘Goi môt thu tuc con’
    ‘Vòng lap gan sô thu tu: 1 – 100 000’
    For i = 1 To 100000
        Sheet1.Range(“E” & i).Value = i
    Next i

‘Tat tang tôc cho code’
SpeedCode False ‘Goi môt thu tuc con’

'Thoi gian hoàn thành:' MsgBox Round(Timer - T, 2) & " giây" 'T=2.62 giây' End Sub

‘————–‘

Public Sub SpeedCode(ByVal OnOff As Boolean) 'Dây là môt thu tuc con'
With Application
    If OnOff = True Then        'Nêu chon là True'
        .ScreenUpdating = False     'Vô hiêu hóa câp nhât màn hình'
        .Calculation = xlCalculationManual      'Tính toán thu công'
    Else   'Nêu chon là False'
        .ScreenUpdating = True      'Câp nhât màn hình'
        .Calculation = xlCalculationAutomatic   'Tu dông tính toán'
    End If
End With
End Sub

4. ByVal / ByRef

Là 2 kiểu truyền đối số cho Function/ Sub.

ByVal: Truyền đối số kiểu truyền giá trị.

ByRef: Truyền đối số kiểu tham chiếu

Mặc định, trong VBA truyền đối số kiểu tham chiếu ByRef.

Ví dụ:

Sub Test_ByValRef()
    Dim x As Long, y As Long
    x = 50
    y = 50
    MsgBox "By_Ref = " & By_Ref(x) & vbNewLine & "x = " & x      'By Ref_1 = 500 | x = 500
    MsgBox "By_Val = " & By_Val(y) & vbNewLine & "y = " & y      'By Val_2 = 500 | y = 50
End Sub

‘————-‘

FFunction By_Ref(ByRef n As Long) As Long
    n = 10 * n
    By_Ref = n
End Function

‘————-‘

Function By_Val(ByVal n As Long) As Long
    n = 10 * n
    By_Val = n
End Function

Chú thích:

– Với Function By_Ref():

Truyền đối số bằng tham chiếu (ByRef), tức là ta đang tham chiếu tới giá trị gốc. Giá trị của n (giá trị ban đầu) được thay đổi trong hàm. (Ví dụ trên: Sau khi truyền đối số thì x = 500).

– Với Function By_Val():

Truyền đối số bằng giá trị, tức là ta chỉ lấy giá trị của đối số (copy value), chứ không làm thay đổi giá trị ban đầu của đối số. Giá trị của n (giá trị ban đầu) vẫn giữ nguyên không đổi. (Ví dụ trên: Sau khi truyền đối số thì y =50).

5. Ví dụ

Hàm trả về dòng cuối dùng có dữ liệu tương ứng với cell tham chiếu.

'//Function tra vê dòng cuôi cùng có du liêu'
Function LastRow(ByVal ws As Worksheet, ByVal sCol As String) As Long
    If ws.FilterMode = True Then ws.ShowAllData     'Nêu dang filter thi clear'
    ws.Cells.EntireRow.Hidden = False               'Unhide toan bô rows'
    LastRow = ws.Cells(ws.Rows.Count, sCol).End(xlUp).Row 'Tra vê dòng cuôi cùng có du liêu'
End Function

Hàm rút ngắn họ và tên với trường hợp có từ 2 tên đệm trở lên.

'//Function rut ngan ho và tên (nêu có 2 tên dêm)'
Function ShortName(ByVal sName As String) As String '//Công thuc o bang tinh Excel =ShortName("ho và tên")'
    Dim arrTmp, i As Long
    sName = WorksheetFunction.Trim(sName)
    arrTmp = Split(sName, " ")
    If UBound(arrTmp) > 2 Then
        For i = LBound(arrTmp) + 1 To UBound(arrTmp) - 1
            arrTmp(i) = Left(arrTmp(i), 1) & "."
        Next i
        ShortName = Join(arrTmp, " ")
    Else
        ShortName = sName
    End If
End Function

Tải file ví dụ: Private Sub | Public Sub | Static Sub | ByVal-ByRef | Ví dụ