Chuyên mục xử lý, gỡ rối code VBA

ndu96081631 · 24/9/13

Topic Những câu hỏi về code, xin giải thích các code... đã quá dài nên mình đóng nó lại và mở topic khác
Tất cả những bài viết liên quan đến việc nhờ giải thích, xử lý và gỡ rối code VBA, các bạn vui lòng đăng tại đây!
Cảm ơn

AnhThu-1976 · 4/5/18

Em có dựa code của thầy Ndu, để viết ra code dưới, nhưng thấy có nhiều ElseIf quá, không biết có cách nào rút gọn được không?

PHP:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rngCopy As Object, rngPaste As Object
    If Not Intersect(Target, Range("F1:J1")) Is Nothing Then
        Set rngCopy = Range("F1:J1")
    ElseIf Not Intersect(Target, Range("F2:J2")) Is Nothing Then
        Set rngCopy = Range("F2:J2")
    ElseIf Not Intersect(Target, Range("F3:J3")) Is Nothing Then
        Set rngCopy = Range("F3:J3")
    ElseIf Not Intersect(Target, Range("F4:J4")) Is Nothing Then
        Set rngCopy = Range("F4:J4")

    ElseIf Not Intersect(Target, Range("F5:J5")) Is Nothing Then
        Set rngCopy = Range("F5:J5")
    ElseIf Not Intersect(Target, Range("F6:J6")) Is Nothing Then
        Set rngCopy = Range("F6:J6")
    ElseIf Not Intersect(Target, Range("F7:J7")) Is Nothing Then
        Set rngCopy = Range("F7:J7")
    ElseIf Not Intersect(Target, Range("F8:J8")) Is Nothing Then
        Set rngCopy = Range("F8:J8")
    End If
    If TypeName(rngCopy) = "Range" Then
        On Error Resume Next
        Set rngPaste = Application.InputBox("Chon vùng Paste", Type:=8)
        On Error GoTo 0
        If TypeName(rngPaste) = "Range" Then rngPaste.Resize(rngCopy.Rows.Count, rngCopy.Columns.Count).Value = rngCopy.Value
    End If
End Sub

Ba Tê · 4/5/18

AnhThu-1976 đã viết:

Em có dựa code của thầy Ndu, để viết ra code dưới, nhưng thấy có nhiều ElseIf quá, không biết có cách nào rút gọn được không?

PHP:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rngCopy As Object, rngPaste As Object
    If Not Intersect(Target, Range("F1:J1")) Is Nothing Then
        Set rngCopy = Range("F1:J1")
    ElseIf Not Intersect(Target, Range("F2:J2")) Is Nothing Then
        Set rngCopy = Range("F2:J2")
    ElseIf Not Intersect(Target, Range("F3:J3")) Is Nothing Then
        Set rngCopy = Range("F3:J3")
    ElseIf Not Intersect(Target, Range("F4:J4")) Is Nothing Then
        Set rngCopy = Range("F4:J4")

    ElseIf Not Intersect(Target, Range("F5:J5")) Is Nothing Then
        Set rngCopy = Range("F5:J5")
    ElseIf Not Intersect(Target, Range("F6:J6")) Is Nothing Then
        Set rngCopy = Range("F6:J6")
    ElseIf Not Intersect(Target, Range("F7:J7")) Is Nothing Then
        Set rngCopy = Range("F7:J7")
    ElseIf Not Intersect(Target, Range("F8:J8")) Is Nothing Then
        Set rngCopy = Range("F8:J8")
    End If
    If TypeName(rngCopy) = "Range" Then
        On Error Resume Next
        Set rngPaste = Application.InputBox("Chon vùng Paste", Type:=8)
        On Error GoTo 0
        If TypeName(rngPaste) = "Range" Then rngPaste.Resize(rngCopy.Rows.Count, rngCopy.Columns.Count).Value = rngCopy.Value
    End If
End Sub

Không biết ý bạn muốn làm là gì thì làm sao rút gọn?

HieuCD · 4/5/18

AnhThu-1976 đã viết:

Em có dựa code của thầy Ndu, để viết ra code dưới, nhưng thấy có nhiều ElseIf quá, không biết có cách nào rút gọn được không?

PHP:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rngCopy As Object, rngPaste As Object
    If Not Intersect(Target, Range("F1:J1")) Is Nothing Then
        Set rngCopy = Range("F1:J1")
    ElseIf Not Intersect(Target, Range("F2:J2")) Is Nothing Then
        Set rngCopy = Range("F2:J2")
    ElseIf Not Intersect(Target, Range("F3:J3")) Is Nothing Then
        Set rngCopy = Range("F3:J3")
    ElseIf Not Intersect(Target, Range("F4:J4")) Is Nothing Then
        Set rngCopy = Range("F4:J4")

    ElseIf Not Intersect(Target, Range("F5:J5")) Is Nothing Then
        Set rngCopy = Range("F5:J5")
    ElseIf Not Intersect(Target, Range("F6:J6")) Is Nothing Then
        Set rngCopy = Range("F6:J6")
    ElseIf Not Intersect(Target, Range("F7:J7")) Is Nothing Then
        Set rngCopy = Range("F7:J7")
    ElseIf Not Intersect(Target, Range("F8:J8")) Is Nothing Then
        Set rngCopy = Range("F8:J8")
    End If
    If TypeName(rngCopy) = "Range" Then
        On Error Resume Next
        Set rngPaste = Application.InputBox("Chon vùng Paste", Type:=8)
        On Error GoTo 0
        If TypeName(rngPaste) = "Range" Then rngPaste.Resize(rngCopy.Rows.Count, rngCopy.Columns.Count).Value = rngCopy.Value
    End If
End Sub

Thử code

Mã:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rngCopy As Object, rngPaste As Object
    If Target.Column >= 6 And Target.Column <= 10 and Target.Row <= 8 Then
        Set rngCopy = Range("F" & Target.Row).Resize(, 5)
        On Error Resume Next
        Set rngPaste = Application.InputBox("Chon vùng Paste", Type:=8)
        On Error GoTo 0
        If TypeName(rngPaste) = "Range" Then rngPaste.Resize(rngCopy.Rows.Count, rngCopy.Columns.Count).Value = rngCopy.Value
    End If
End Sub

Hoặc

Mã:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rngCopy As Object, rngPaste As Object
    If Not Intersect(Target, Range("F1:J8")) Is Nothing Then
        Set rngCopy = Range("F" & Target.Row).Resize(, 5)
        On Error Resume Next
        Set rngPaste = Application.InputBox("Chon vùng Paste", Type:=8)
        On Error GoTo 0
        If TypeName(rngPaste) = "Range" Then rngPaste.Resize(rngCopy.Rows.Count, rngCopy.Columns.Count).Value = rngCopy.Value     
    End If
End Sub

VetMini · 4/5/18

For each rgAddr in Array("F1:J1", "F2:J2", ...)
If Not Intersect(target, Range(rgAddr)) Is Nothing Then
Set rgCopy = Range(rgAddr)
Exit For
End If
Next rgAddr

Cái lỗi chính của code bạn không ở chỗ If Else nhiều, mà là ở chỗ lặp đi lặp lại mấy cái tên range nhiều quá. Trong nghề lập trình, cái này là lỗi code khó kiểm soát.

Hâu hết các bậc trưởng thượng ở diễn đàn này chỉ chú ý vào tốc độ code và số dòng code chứ không hề khuyến khích người mới học căn bản. Vòng lặp for nằm trong phần căn bản nhất của lập trình. Nhìn vào một cái gì lặp đi lặp lại thì phải nghĩ đến vòng lặp. Ở đây, cái vòng lặp của tôi khong chỉ cốt thâu gọn code, nó còn có mục đích chỉ kể đến cái string range mỗi cái đúng 1 lần -> chỉ có một chỗ để kiểm soát.

truongvu317 · 4/5/18

vova2209 đã viết:
Code mới rút gọn lại hơn hay như nào chị tư vấn cho em với ạ

Chém gió chut! Hôm qua ai nói chuyện "xoẹt xoẹt" ấy, giờ anh "Vẹt Nhỏ" đã đổi giới tính rồi hay sao.

truongvu317 · 4/5/18

vova2209 đã viết:
[QUOTE="HieuCD

[QUOTE="Befait[/QUOTE]
Cảm ơn các anh đã giúp đỡ.. nhìn chạy chạy từng dòng trông đẹp mắt thật ^^!

Mã:

Sub TieuDeRowBangKL()
    Dim i As Long
    Dim aCodeName, ws As Worksheet, sName
        aCodeName = Array("Sheet24", "Sheet25", "Sheet26", "Sheet39", "Sheet40", "Sheet111") 'Liêt kê codeName cua các sheets

    On Error Resume Next
    For Each sName In aCodeName
        Set ws = SheetCodeName(sName)
        For i = 1 To 11
        ws.Select
            ws.Range("B" & i).RowHeight = Sheet23.Range("B" & i).RowHeight
        Next i
    Next sName
End Sub

Đây là code cũ của em trông gà thật ^^!

Mã:

Sub SetupRowBangKL2()
Dim i As Long
    For i = 1 To 11
        Sheet24.Range("B" & i).RowHeight = Sheet23.Range("B" & i).RowHeight
        Sheet25.Range("B" & i).RowHeight = Sheet23.Range("B" & i).RowHeight
        Sheet26.Range("B" & i).RowHeight = Sheet23.Range("B" & i).RowHeight
        Sheet39.Range("B" & i).RowHeight = Sheet23.Range("B" & i).RowHeight
        Sheet40.Range("B" & i).RowHeight = Sheet23.Range("B" & i).RowHeight
    Next i
End Sub

[/QUOTE]
Tui cảm nhận hai code này chả khác nhau về mặt ý tưởng, thậm chí code đầu tiên còn cực yếu là khác. Cuối cùng mà nói bạn vẫn phải tìm cách lấy tham chiếu tới từng sheet để thao tác, code của bạn chính ra lại trong sáng, dài hơn vài dòng không là cái gì cả. Nếu đã dùng namecode để truy cập thì thà rằng viết là array(sheet1,sheet2....) cho nó nhanh, đỡ phải mất công dò tìm tham chiếu, có luôn mà dùng. Với cách viết này ta còn được vba hỗ trợ gợi ý khi viết code, ví dụ khi gõ là shee thì vba sẽ liệt kê ra tất cả những cái liên quan, dùng chuột chọn là xong, không thể nhầm lẫn khi viết là sheet1.... Mà nếu có thay đổi namecode, vba sẽ báo lỗi ngay trước khi cho chạy.

vova2209 · 4/5/18

truongvu317 đã viết:
[QUOTE="Befait

Cảm ơn các anh đã giúp đỡ.. nhìn chạy chạy từng dòng trông đẹp mắt thật ^^!

Mã:

Sub TieuDeRowBangKL()
    Dim i As Long
    Dim aCodeName, ws As Worksheet, sName
        aCodeName = Array("Sheet24", "Sheet25", "Sheet26", "Sheet39", "Sheet40", "Sheet111") 'Liêt kê codeName cua các sheets

    On Error Resume Next
    For Each sName In aCodeName
        Set ws = SheetCodeName(sName)
        For i = 1 To 11
        ws.Select
            ws.Range("B" & i).RowHeight = Sheet23.Range("B" & i).RowHeight
        Next i
    Next sName
End Sub

Đây là code cũ của em trông gà thật ^^!

Mã:

Sub SetupRowBangKL2()
Dim i As Long
    For i = 1 To 11
        Sheet24.Range("B" & i).RowHeight = Sheet23.Range("B" & i).RowHeight
        Sheet25.Range("B" & i).RowHeight = Sheet23.Range("B" & i).RowHeight
        Sheet26.Range("B" & i).RowHeight = Sheet23.Range("B" & i).RowHeight
        Sheet39.Range("B" & i).RowHeight = Sheet23.Range("B" & i).RowHeight
        Sheet40.Range("B" & i).RowHeight = Sheet23.Range("B" & i).RowHeight
    Next i
End Sub

[/QUOTE]
Tui cảm nhận hai code này chả khác nhau về mặt ý tưởng, thậm chí code đầu tiên còn cực yếu là khác. Cuối cùng mà nói bạn vẫn phải tìm cách lấy tham chiếu tới từng sheet để thao tác, code của bạn chính ra lại trong sáng, dài hơn vài dòng không là cái gì cả. Nếu đã dùng namecode để truy cập thì thà rằng viết là array(sheet1,sheet2....) cho nó nhanh, đỡ phải mất công dò tìm tham chiếu, có luôn mà dùng. Với cách viết này ta còn được vba hỗ trợ gợi ý khi viết code, ví dụ khi gõ là shee thì vba sẽ liệt kê ra tất cả những cái liên quan, dùng chuột chọn là xong, không thể nhầm lẫn khi viết là sheet1.... Mà nếu có thay đổi namecode, vba sẽ báo lỗi ngay trước khi cho chạy.[/QUOTE]
Mình muốn dùng codeName để khi đổi tên ko ảnh hưởng tới code. Và cũng thử cách khác xem tốc độ code chạy có tốt hơn không?
làm thế nào cho dòng này ngắn lại nhỉ bạn: Sheet23.Range("B" & i).RowHeight .
Dim ws23 as WorkSheets
Set ws23 = Sheet23.Range("B" & i).RowHeight lại bị báo lỗi

VetMini · 4/5/18

truongvu317 đã viết:
...
Tui cảm nhận hai code này chả khác nhau về mặt ý tưởng, thậm chí code đầu tiên còn cực yếu là khác. ....

Thì chính người hỏi muốn cho nó chớp cho đẹp mắt mờ.

vova2209 đã viết:
...
Dim ws23 as WorkSheets
Set ws23 = Sheet23.Range("B" & i).RowHeight lại bị báo lỗi

Cái phần màu xanh nó là thuộc tính của range, phần màu đỏ là sheet.
Code loạn xà ngầu, Đem râu ông nọ cắm cằm bà kia may là nó báo lỗi. Chứ nó lẳng lặng sửa sai dữ liệu thì ở đấy mà khóc.[/QUOTE]

truongvu317 · 4/5/18

VetMini đã viết:
Thì chính người hỏi muốn cho nó chớp cho đẹp mắt mờ.

Cái phần màu xanh nó là thuộc tính của range, phần màu đỏ là sheet.
Code loạn xà ngầu, Đem râu ông nọ cắm cằm bà kia may là nó báo lỗi. Chứ nó lẳng lặng sửa sai dữ liệu thì ở đấy mà khóc.

Nói chung là cứ đi copy, chả hiểu ngọn nguồn gì thì cũng mệt lắm.

VetMini · 4/5/18

truongvu317 đã viết:
...
Tui cảm nhận hai code này chả khác nhau về mặt ý tưởng {1}, thậm chí code đầu tiên còn cực yếu {2} là khác. Cuối cùng mà nói bạn vẫn phải tìm cách lấy tham chiếu tới từng sheet để thao tác, code của bạn chính ra lại trong sáng {3}, dài hơn vài dòng không là cái gì cả. Nếu đã dùng namecode để truy cập thì thà rằng viết là array(sheet1,sheet2....) cho nó nhanh {4} đỡ phải mất công dò tìm tham chiếu, có luôn mà dùng. Với cách viết này ta còn được vba hỗ trợ gợi ý khi viết code, ví dụ khi gõ là shee thì vba sẽ liệt kê ra tất cả những cái liên quan {5}, dùng chuột chọn là xong, không thể nhầm lẫn khi viết là sheet1.... Mà nếu có thay đổi namecode, vba sẽ báo lỗi ngay trước khi cho chạy.

{1} Theo kỹ thuật lập trình thì rất khác nhau về mặt ý tưởng. Một code dùng vòng lặp sẽ nhấn mạnh ở điểm rằng các Range của mỗi sheet sẽ giống nhau. Code kia nêu từng sheet ra, cho nên phải đọc hết từng Range mới biết chúng giống nhau.
(thật sự cũng có cách chọn dòng của nhóm sheets và sửa luôn một lúc. Nhưng người hỏi code muốn nó chạy đẹp mắt cho nên tôi không buồn bàn tới)

{2} yếu hay mạnh là chuyện chủ quan, tôi khong nói tới.

{3} xem luận lý ở {1}, tùy theo chủ ý và trường phái code mà bên nào sẽ trong sáng hơn. Theo luật thống kê thì nếu số sheets khoảng 2-3 thì đúng là trong sáng hơn, 4-5 sheets thì tương đương, 6+ sheets thì sự trong sáng nghiêng về bên vòng lặp.

{4} dùng luôn array(sheet1, sheet2, ...) đúng là cách gọn nhất. Tuy nhiên, đó là cách gọi trực tiếp đối tượng sheet.
Ở đay, code trên chỉ là biểu diễn trường hợp dùng string array mà vẫn đề cập được sheet qua codename. Dùng string array uyển chuyển hơn, ví dụ đầu module có một string cho biết tên của các sheets liên quan.

{5} Điều này không hẳn quan trọng. Có nhiều cách để VBA liệt kê phương thức và thuộc tính của đối tượng.

truongvu317 · 4/5/18

VetMini đã viết:
Điều này không hẳn quan trọng. Có nhiều cách để VBA liệt kê phương thức và thuộc tính của đối tượng.

Giả sử có sheet1, nếu viết là "sheetl" sẽ không có bất cứ thông báo nào cho tới khi code chạy bị lỗi, còn nếu cố ý viết là SheetL thì lúc dịch sẽ báo lỗi ngay, em muốn đề cập tới vấn đề đó.

VetMini · 4/5/18

truongvu317 đã viết:
Giả sử có sheet1, nếu viết là "sheetl" sẽ không có bất cứ thông báo nào cho tới khi code chạy bị lỗi, còn nếu cố ý viết là SheetL thì lúc dịch sẽ báo lỗi ngay, em muốn đề cập tới vấn đề đó.

Dựa vào điều này để tránh lỗi code thì tôi bó tay, không dám bàn cãi tiếp.

vova2209 · 4/5/18

VetMini đã viết:
Thì chính người hỏi muốn cho nó chớp cho đẹp mắt mờ.

Cái phần màu xanh nó là thuộc tính của range, phần màu đỏ là sheet.
Code loạn xà ngầu, Đem râu ông nọ cắm cằm bà kia may là nó báo lỗi. Chứ nó lẳng lặng sửa sai dữ liệu thì ở đấy mà khóc.

[/QUOTE]
Thực ra toàn nhờ anh chị viết code cho để ứng dụng làm cho nhanh, vừa nhờ vừa tìm hiểu nên còn bỡ ngỡ em sẽ cố gắng nhiều. Cảm ơn a chị GPE đã giúp nhiều

mdk18 · 6/5/18

Xin chào mọi người..mình gặp 1 số vấn đề về mở 1 file excel 2016 thì bị báo lỗi như hình dưới..ko biết có ae nào khắc phục được giúp mình ko ạ..xin cảm ơn trước. Máy mình win 10 32bit

keke355992 · 6/5/18

Chào các bạn. Mình xin hỏi chút về vấn đề sau: Mình có nhiều Sheet trong Workbook., Ví dụ: Sheets("A"); Sheets("B"); Sheets("C").... Vậy dùng câu lệnh nào có thể lấy đc tên Sheet mình vừa DeActivate. Ví dụ: đang ở sheets("A"), mà bấm chọn Sheet bất kỳ thì có 1 Msgbox hiện "A" ....
( ActivateSheet.Name - nhưng nó chỉ lấy sheet hiện hành, chứ kp là lấy tên Sheet vừa đi qua xong )

befaint · 6/5/18

keke355992 đã viết:
Chào các bạn. Mình xin hỏi chút về vấn đề sau: Mình có nhiều Sheet trong Workbook., Ví dụ: Sheets("A"); Sheets("B"); Sheets("C").... Vậy dùng câu lệnh nào có thể lấy đc tên Sheet mình vừa DeActivate. Ví dụ: đang ở sheets("A"), mà bấm chọn Sheet bất kỳ thì có 1 Msgbox hiện "A" ....
( ActivateSheet.Name - nhưng nó chỉ lấy sheet hiện hành, chứ kp là lấy tên Sheet vừa đi qua xong )

keke355992 · 6/5/18

Cám ơn befaint, mình k nghĩ ra là dùng sự kiện WorkBook. Cứ loay hoay WorkSheet

♫ђöล♥ßล†♥†µ♫ · 6/5/18

keke355992 đã viết:
Chào các bạn. Mình xin hỏi chút về vấn đề sau: Mình có nhiều Sheet trong Workbook., Ví dụ: Sheets("A"); Sheets("B"); Sheets("C").... Vậy dùng câu lệnh nào có thể lấy đc tên Sheet mình vừa DeActivate. Ví dụ: đang ở sheets("A"), mà bấm chọn Sheet bất kỳ thì có 1 Msgbox hiện "A" ....
( ActivateSheet.Name - nhưng nó chỉ lấy sheet hiện hành, chứ kp là lấy tên Sheet vừa đi qua xong )

Như thế này cũng được anh ạ

Mã:

Option Explicit
Public Str As String
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Str = Sh.Name
    MsgBox Str
End Sub

befaint · 6/5/18

♫ђöล♥ßล†♥†µ♫ đã viết:
Như thế này cũng được anh ạ

Mã:

Option Explicit Public Str As String Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) Str = Sh.Name MsgBox Str End Sub

Thế viết luồn vầy. =))

PHP:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    MsgBox Sh.Name
End Sub

vova2209 · 11/5/18

Cho em hỏi! Em đang đứng tại sheet22 gọi code nó chạy ở sheet21 Code dưới đã đúng chưa ạ. Em xin cảm ơn
With Sheets("Sheet21")
code
...
End With

Chuyên mục xử lý, gỡ rối code VBA

Huyền thoại GPE

Thành viên tích cực

Cạo Rồi Khỏi Gội

Chuyên gia GPE

Ăn cùng góc phố

Thành viên tích cực

Thành viên tích cực

Thành viên tích cực

Ăn cùng góc phố

Thành viên tích cực

Ăn cùng góc phố

Thành viên tích cực

Ăn cùng góc phố

Thành viên tích cực

Thành viên mới

File đính kèm

Thành viên thường trực

|||||||||||||

File đính kèm

Thành viên thường trực

Thành viên tiêu biểu

|||||||||||||

Thành viên tích cực

Facebook

Group