Chuyển Worksheet_Change(...) thành Function(...)

[boyxin]

Chào các bác! dạo này hình như em càng ngày càng ngu thêm hay sao ý

Với cùng 1 nhiệm vụ, dùng sự kiện Worksheet_Change(...) thì cho kết quả ngon lành, thế mà chuyển nhiệm vụ đó dưới dạng hàm tự tạo Function(...) thì toàn báo lỗi,không ra kết quả.

Option Explicit
Dim Tmp As Variant, i As Byte, iM As Byte, iL As Range
Function LastWeek(iT As Byte, iMon As String, iLop As String) As Long
    If iMon <> "" Then
        With Sheet1
            iM = .[a1:bo1].Find(iMon, , xlValues, 1, 1).Column
            Set iL = .Cells(2, iM).Resize(, 4).Find(Mid(iLop, 1, 1) * 1)
            Tmp = iL.Offset(iT).Value
        End With
    Else
        Tmp = 0
    End If
    LastWeek = Tmp
End Function

Em đã sửa được Hàm cho kết quả thì sự kiện Worksheet_Change(...) lại im re không hoạt động. không rõ lý do vì sao?


Mong các bác giúp đỡ (Nội dung cụ thể trong file đính kèm)

 

[Nguyễn Duy Tuân]

Trong code của anh có đoạn kiểm tra

If Cells(i, "B") <> "" Then

Nhưng biến i chưa được gán giá trị, lúc khởi đầu nó có gí trị là 0, vì thế tại Cells(i, "B") gây lỗi.
Điều kiện các đối số RowIndex, ColumnIndex > 0

 

[sealand]

Boyxin chỉ cần viết thế này là được:

Dim Tmp As Variant, i As Byte, iM As Byte, iL As Range
Function LastWeek1(iT As Byte, iMon As String, iLop As String)
        With Sheet1
            iM = .[a1:bo1].Find(iMon, , xlValues, 1, 1).Column
            Set iL = .Cells(2, iM).Resize(, 4).Find(Mid(iLop, 1, 1) * 1)
            Tmp = iL.Offset(iT).Value
        End With
    LastWeek1 = Tmp
End Function

Nhưng trong trường hợp này dùng hàm Match hay, nhanh, gọn hơn

 

[boyxin]

Trong code của anh có đoạn kiểm tra


Nhưng biến i chưa được gán giá trị, lúc khởi đầu nó có gí trị là 0, vì thế tại Cells(i, "B") gây lỗi.
Điều kiện các đối số RowIndex, ColumnIndex > 0

Cảm ơn Tuân nhé.

Đã sửa lại rồi nhưng vấn đề bi giò là Function cho kết quả thì đến lượt Worksheet_Change(...) lại im re không hoạt động.
không rõ lý do vì sao?

Boyxin chỉ cần viết thế này là được:

Dim Tmp As Variant, i As Byte, iM As Byte, iL As Range
Function LastWeek1(iT As Byte, iMon As String, iLop As String)
        With Sheet1
            iM = .[a1:bo1].Find(iMon, , xlValues, 1, 1).Column
            Set iL = .Cells(2, iM).Resize(, 4).Find(Mid(iLop, 1, 1) * 1)
            Tmp = iL.Offset(iT).Value
        End With
    LastWeek1 = Tmp
End Function

Nhưng trong trường hợp này dùng hàm Match hay, nhanh, gọn hơn

Cảm ơn sealand nhé

Mình nghĩ: Cần có điều kiện để khi tìm iM không phát sinh lỗi hoặc kết quả sai (do dữ liệu: iMon chỉ là "" hoặc <>"") nên mới thêm điều kiện iMon <> ""

 

[SA_DQ]

Mình sửa như thế này thì có thể xài tạm, nhưng tiềm ẩn lỗi khôn lường

Option Explicit
Dim Tmp As Variant, i As Byte, iM As Byte, iL As Range
Function LastWeek(Tuan As Byte, Mon As String, Lop As String) As Long
 On Error GoTo Loi

1 If Cells(i, "B") <> "" Then
   With Sheet1
3      iM = .[a1:bo1].Find(Mon, , xlValues, 1, 1).Column
      Set iL = .Cells(2, iM).Resize(, 4).Find(Mid(Lop, 1, 1) * 1)
5      Tmp = iL.Offset(Tuan).Value
   End With
7 Else
   Exit Function
   Tmp = 0
9 End If
 LastWeek = Tmp
Err_:       Exit Function
Loi:
   Select Case Err
   Case 1004
      Resume Next
   Case Else
      MsgBox Error, , Erl
      Resume Err_
   End Select
End Function

(*) Lỗi ngay dòng lệnh 1; Đó là lỗi 1004
Khi ô ngang hàng cột 'B' ="" thì báo Err = 3

(*) Mà bạn thử đưa Cells(i,"B") vô làm tham số của hàm xem sao?

 

[sealand]

Mình nghĩ: Cần có điều kiện để khi tìm iM không phát sinh lỗi hoặc kết quả sai (do dữ liệu: iMon chỉ là "" hoặc <>"") nên mới thêm điều kiện iMon <> ""

Nhưng với iMon<>"" nhưng lại sai chính tả thì lỗi không tìm thấy vẫn lọt lưới.
Theo mình nên rào như sau:

Dim iM As Byte, iL As Range
Function LastWeek1(iT As Byte, iMon As String, iLop As String)
    On Error goto Slyloi
        With Sheet1
            iM = .[a1:bo1].Find(iMon, , xlValues, 1, 1).Column
            Set iL = .Cells(2, iM).Resize(, 4).Find(Mid(iLop, 1, 1) * 1)
            LastWeek1 = iL.Offset(iT).Value
        End With
  Exit Function
Slyloi:
LastWeek1=0
End Function

 

[boyxin]

Option Explicit
Dim Tmp As Variant, i As Byte, iM As Byte, iL As Range
Function LastWeek(Tuan As Byte, Mon As String, Lop As String) As Long
 On Error GoTo Loi

1 If Cells(i, "B") <> "" Then
   With Sheet1
3      iM = .[a1:bo1].Find(Mon, , xlValues, 1, 1).Column
      Set iL = .Cells(2, iM).Resize(, 4).Find(Mid(Lop, 1, 1) * 1)
5      Tmp = iL.Offset(Tuan).Value
   End With
7 Else
   Exit Function
   Tmp = 0
9 End If
 LastWeek = Tmp
Err_:       Exit Function
Loi:
   Select Case Err
   Case 1004
      Resume Next
   Case Else
      MsgBox Error, , Erl
      Resume Err_
   End Select
End Function

(*) Lỗi ngay dòng lệnh 1; Đó là lỗi 1004
Khi ô ngang hàng cột 'B' ="" thì báo Err = 3

(*) Mà bạn thử đưa Cells(i,"B") vô làm tham số của hàm xem sao?

Dạ, cái đó lúc đầu em nhầm, em đã sửa If Cells(i,"B") <> "" Then thành If iMon <> "" Then
rồi thay hàm của bác hoặc thay hàm của Seland hoặc như sau:

Option Explicit
Dim Tmp As Variant, i As Byte, iM As Byte, iL As Range
Function LastWeek(iT As Byte, iMon As String, iLop As String) As Long
    If iMon <> "" Then
        With Sheet1
            iM = .[a1:bo1].Find(iMon, , xlValues, 1, 1).Column
            Set iL = .Cells(2, iM).Resize(, 4).Find(Mid(iLop, 1, 1) * 1)
            Tmp = iL.Offset(iT).Value
        End With
    Else
        Tmp = 0
    End If
    LastWeek = Tmp
End Function

Thì Hàm đã cho kết quả nhưng vấn đề nảy sinh là sự kiện Worksheet_Change(...) lại im re không hoạt động. Không rõ lý do vì sao?, liệu hai thứ này có thể ở chung trên cùng 1 file được không? Muốn cho chúng ở chung voiứ nhau trên cùng 1 file thì cần xử lý tiếp như thế nào để không xinh lỗi (Trong dữ liệu: iMon và iLop đồng thời chỉ có thể là các giá trị ="" hoặc =Text)

 

[boyxin]

Nhưng với iMon<>"" nhưng lại sai chính tả thì lỗi không tìm thấy vẫn lọt lưới.
Theo mình nên rào như sau:

Dim iM As Byte, iL As Range
Function LastWeek1(iT As Byte, iMon As String, iLop As String)
    On Error goto Slyloi
        With Sheet1
            iM = .[a1:bo1].Find(iMon, , xlValues, 1, 1).Column
            Set iL = .Cells(2, iM).Resize(, 4).Find(Mid(iLop, 1, 1) * 1)
            LastWeek1 = iL.Offset(iT).Value
        End With
  Exit Function
Slyloi:
LastWeek1=0
End Function

Một cách rào, bẫy lỗi hay, nhưng ở đây dữ liệu mình biết chắc chắn không xảy ra trường hợp đó vì iMon được lấy ra từ vùng đang dùng để dò tìm mà

 

[sealand]

Đúng là mình cunbgx chưa tìm ra nguyên do. Nếu gõ vào thì nó chấp nhận, nhưng chọn bằng Vali. thì sinh chuyện.

 

[sealand]

Mình tham gia thêm hàm dùng Match, Boyxin tham khảo nha:

Dim iM As Byte, iL As Byte, Wf As WorksheetFunction
Function LastWeek1(iT As Byte, iMon As String, iLop As String)
Set Wf = WorksheetFunction
 On Error GoTo Slyloi
   iM = Wf.Match(iMon, Sheet1.[a1:bo1], 0)
   iL = Wf.Match(Mid(iLop, 1, 1) * 1, Sheet1.Cells(2, iM).Resize(, 3), 0)
   LastWeek1 = Sheet1.Cells(iT + 2, iM + iL).Value
Exit Function
Slyloi:
LastWeek1 = 0
End Function

 

[Nguyễn Duy Tuân]

Bây giờ bác đưa ra code bác đã sửa đi. Em sẽ xem lỗi và đưa la lý giải cho bác ngay.

 

[boyxin]

Bây giờ bác đưa ra code bác đã sửa đi. Em sẽ xem lỗi và đưa la lý giải cho bác ngay.

Tập tin đính kèm

• 
  Help010910.rar (14.3 KB)

Vấn đề bi giờ là:

Hàm đã cho kết quả đúng rồi nhưng phần sự kiện WorkSheet_Change(..) lại có vấn đề:

• Khi chọn Validation tại C1 thì giá trị hàm thay đổi theo (ở cột F) nhưng phần kết quả của sự kiện WorkSheet_Change(..) ở cột E lại im re không nhúc nhích.
• Khi nhập số vào C1 thì cả sự kiện WorkSheet_Change(..) và hàm hoạt động cho kết quả bình thường
• Khi xoá hết phần công thức tại cột F thì sự kiện WorkSheet_Change(..) lại hoạt động bình thường

Có cách nào khắc phục để hàm và sự kiện chung sống với nhau tronmg cùng File được không?