Cách tối ưu code thêm dòng

PhanTuHuong · 5/9/09

Tôi có viết đoạn code lập trình sự kiện. Yêu cầu từ ô B24 trở xuống theo hàng dọc, cứ có nội dung là tự động xuống 1 dòng, dòng đó mang đầy đủ nội dung trên. Nghe thì đơn giản nhưng thực hiện khá lằng nhằng (dễ bị lỗi và treo máy), tôi đã làm nhưng chưa được ưng ý vì code khá lằng nhằng.

Nhờ mọi người tư vấn, tối ưu code:

Mã:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim BaseCell As Range, BaseRow As Integer
    
    On Error GoTo Thoat
    
    Set BaseCell = Range("B24:B20000")
    
    If Not Intersect(BaseCell, Target) Is Nothing And IsEmpty(Target) = False And IsEmpty(Target.Offset(0, -1)) = False Then
        Rows(Target.Row).Copy
        Rows(Target.Row + 1).Select
        Selection.Insert Shift:=xlDown
        Target.Offset(1, 0).ClearContents
        Application.CutCopyMode = False
        Target.Offset(1, 0).Select
        Set Target = Range("A1")
        Set BaseCell = Nothing
        Exit Sub
    End If
Thoat:
Set BaseCell = Nothing
End Sub

ndu96081631 · 5/9/09

Tại sao không dùng List nhỉ? Cần gì viết code!

ptm0412 · 5/9/09

Tối ưu thuật toán hay tối ưu code thì không dám bàn, nhưng có vài dòng code có vẻ thừa như:
- Rows(Target.Row + 1).Select: có thể insert copy cell bằng cách khác
- Target.Offset(1, 0).Select: nếu nhập liệu xong enter, là đã select cell kế dưới rồi nên dư, còn nếu người dùng quen sử dụng tab thay cho enter để nhập liệu tiếp theo dòng, thì mất công select lại lên trên. Theo tôi làm thế này gọn hơn 1 tí ti.

PHP:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim BaseCell As Range, BaseRow As Integer
    On Error GoTo Thoat
    Set BaseCell = Range("B14:B200")
    If Not Intersect(BaseCell, Target) Is Nothing And IsEmpty(Target) = False And _ 
    IsEmpty(Target.Offset(0, -1)) = False Then
        Rows(Target.Row).Copy
        Target.Offset(1, 0).EntireRow.Insert Shift:=xlDown
        Target.Offset(1, 0).ClearContents
        Application.CutCopyMode = False
        Set Target = Range("A1")
        Set BaseCell = Nothing
        Exit Sub
    End If
Thoat:
Set BaseCell = Nothing
End Sub

Ghi chú:
1. Nếu dòng 25 là dòng cộng và có công thức Sum, thì tôi luôn để cách dữ liệu 1 dòng trống, khi insert row thì đứng tại dòng trống đó mà insert thì công thức Sum tự cập nhật. Nếu không có dòng trống này, Sum bị thiếu, càng insert càng thiếu.
2. Do copy nguyên dòng và insert copy cells, nên số thứ tự cũng tự copy xuống theo, các cột có công thức cũng vậy, dù người dùng không có ý định nhập liệu vào dòng mới insert đó (vì hết rồi chẳng hạn)
3. Nếu cần insert dòng với đầy đủ công thức như thế, tôi sẽ dùng chức năng List của Excel 2003 hoặc Table của Excel 2007 còn hay hơn, vì ngoại trừ việc thêm dòng, nó còn có thể cancel việc thêm dòng nếu không nhập liệu vào dòng mới thêm.

File kèm theo có 2 sheet, 1 sheet xài code và 1 sheet xài List

PhanTuHuong · 5/9/09

ndu96081631 đã viết:
Tại sao không dùng List nhỉ? Cần gì viết code!

Do thói quen lập trình, mà món List em lại không thạo. Bác cho ví dụ được không?

ndu96081631 · 5/9/09

PhanTuHuong đã viết:
Do thói quen lập trình, mà món List em lại không thạo. Bác cho ví dụ được không?

Thầy quét chọn nguyên vùng dử liệu, Click phải chuột, chọn Create List là xong
Ah, quên, hình như thầy dùng Excel 2007? Cái này hơi khác: Quét chọn vùng dử liệu, vào tab Insert, chọn Table
Sau khi đã tạo List hoặc Table xong, thầy gõ gì đó vào dòng kế cuối là lập tức nó sẽ chèn thêm 1 dòng cho thầy ---> Các công thức ở dòng trên cũng tự động copy xuống dòng dưới
Cái này rất thuận tiện cho việc nhập liệu có dòng tổng cuối trang ---> Chức năng sẳn có, khỏi cần viết gì cả ---> Khỏe re!

PhanTuHuong · 5/9/09

Em dùng Office 2007, thực hiện như anh ko được.

violetdylan · 5/9/09

PhanTuHuong đã viết:
Em dùng Office 2007, thực hiện như anh ko được.

Dùng Office2003 thực hiện theo hướng dẫn rất đơn giản Hướng à.

ndu96081631 đã viết:
Thầy quét chọn nguyên vùng dử liệu, Click phải chuột, chọn Create List là xong
Ah, quên, hình như thầy dùng Excel 2007? Cái này hơi khác: Quét chọn vùng dử liệu, vào tab Insert, chọn Table
Sau khi đã tạo List hoặc Table xong, thầy gõ gì đó vào dòng kế cuối là lập tức nó sẽ chèn thêm 1 dòng cho thầy ---> Các công thức ở dòng trên cũng tự động copy xuống dòng dưới
Cái này rất thuận tiện cho việc nhập liệu có dòng tổng cuối trang ---> Chức năng sẳn có, khỏi cần viết gì cả ---> Khỏe re!

Nhưng có môt vài vấn đề:

1/ Xuất hiện từ column1, column2.... (có bao nhiêu cột là thêm bấy nhiêu chữ column) phá hỏng định dạng các cột.
2/ Ở cột Số TT, mình để công thức [=IF(B14<>"",A14+1,"")] nó copy xuống dưới nhưng báo lỗi #VALUE!
3/ Có dòng kẻ ngang nhưng khi nhảy dòng xong lại không có

Bạn hướng dẫn giúp mình với nhé

Xin cảm ơn bạn nhiều

ndu96081631 · 5/9/09

PhanTuHuong đã viết:
Em dùng Office 2007, thực hiện như anh ko được.

Tôi nghĩ Excel 2003 làm được thì lý nào 2007 lại không làm được
Tiếc là tôi không có Excel 2007 để thí nghiệm, tuy nhiên thầy có thể xem cái này:
http://www.jkp-ads.com/articles/Excel2007tables.asp
Trong đó có 1 đoạn:

4. Automatic expansion of table

If you type anything next to a table, Excel assumes you want to expand the table and automatically increases the table size to include your new entry. Of course you can undo this expansion too, or switch off this behavior entirely

violetdylan đã viết:
1/ Xuất hiện từ column1, column2.... (có bao nhiêu cột là thêm bấy nhiêu chữ column) phá hỏng định dạng các cột.

Hỏng có à nha! Nếu bạn dử liệu đã có tiêu đề rồi thì sau khi tạo Table làm gì có mấy chử Column ấy chứ ---> E rằng bạn đã thao tác sai

violetdylan đã viết:
2/ Ở cột Số TT, mình để công thức [=IF(B14<>"",A14+1,"")] nó copy xuống dưới nhưng báo lỗi #VALUE!
3/ Có dòng kẻ ngang nhưng khi nhảy dòng xong lại không có

Về công thức thì tôi không biết... Còn vụ Format, trong Excel 2007 có rất nhiều kiểu Format có sằn, còn đẹp hơn rất nhiều so với bạn tự làm
Nghiên cứu xem ---> Tôi không có Excel 2007 nên không thể hướng dẩn cụ thể được, chỉ biết rằng nó còn mạnh hơn Excel 2003 rất nhiều (cái này chắc phải nhờ BNTT hướng dẩn chi tiết quá)
Ngày trước, khi còn xài Excel 2000, để tạo dòng tổng cuối trang người ta phải viết code chứ bây giờ thì không cần ---> Excel 2007 mà không dùng đến Table thì quả là điều đáng tiếc

violetdylan · 5/9/09

Bạn kiểm tra file đính kèm và hướng dẫn giúp mình với nhé

Xin cảm ơn

ptm0412 · 5/9/09

Cái vụ Column1, column2 xuất hiện là do hoặc không tô chọn cả tiêu đề, hoặc không đánh dấu vào ô chọn "My table have header" khi create list (hoặc insert table),

Excel 2007 cũng insert dòng như List của 2003, lúc nãy nhầm. Xem file kèm theo và thử gõ dòng liền dưới của table, cột không có công thức.

TrungChinhs · 5/9/09

Thầy thử xem "củ khoai" này có được không ?

ptm0412 · 5/9/09

1. List 2003 và Table 2007 có 1 số điểm chung:
- Có dòng Total row, ẩn hiện tùy ý. Có thể chọn công thức total row là sum, Max, Min, Average, ...
- Có thể chèn dòng và copy công thức dòng trên xuống
- Có thể thêm cột vào bên phải bằng cách đơn giản là gõ tiêu đề vào cột kế bên.
- Có 1 hình tam giác nhỏ ở góc dưới bên phải của List - Table để resize
- ....

2. Một số điểm khác nhau:
- Khi hiện dòng Total Row, List 2003 vẫn có thể chèn 1 dòng data & copy công thứcbằng cách gõ dữ liệu vào dòng có dấu * ở trong List, Table thì không. Table chỉ có thể gõ dữ liệu chèn dòng copy công thức khi ẩn total row.
- Table 2007 có thể copy định dạng xuống dòng mới chèn, list 2003 thì có cell copy có cell không.
- ...

3. Một số điểm khác biệt hẳn của Table:
- Table 2007 có nhiều style định dạng.
- Tham chiếu vào trong table trong cùng dòng sẽ có dạng như sau: =Table5[[#This Row];[stien]]. Không biết để làm gì, vì không có [previous row] hoặc [next row]
- Table có thể dùng như 1 Database table trong SQL
- ...

Chắc phải chờ BNTT thật!

PhanTuHuong · 5/9/09

Trung Chinh đã viết:
Thầy thử xem "củ khoai" này có được không ?

Code của bác rất ngắn gọn, nhưng luôn có 1 dòng phía dưới bị ẩn

TrungChinhs · 5/9/09

PhanTuHuong đã viết:
Code của bác rất ngắn gọn, nhưng luôn có 1 dòng phía dưới bị ẩn

Vì vậy mới gọi là "củ khoai" chẹp...chẹp... Dòng ẩn ở phía dưới nhằm mục đích giữ định dạng dòng kẻ và giữ phạm vi vùng Sum khi Insert row và xét thấy không ảnh hưởng gì tới bảng tính (nếu dùng List thì dòng cuối cũng luôn có *).

Tôi đã sửa lại theo đúng yêu cầu của bài (không có dòng ẩn) tuy nhiên code phải dài hơn 1 tý...

ptm0412 · 6/9/09

Code của TrungChinh tuy ngắn nhưng lại thừa:

- Đằng nào cũng copy và paste, mà sao chỉ paste format và vẫn phải có những câu lệnh gõ công thức cho từng ô? Nếu 8 ô (nằm trong 8 cột) có công thức thì phải làm 8 dòng sao? Tại sao không copy và Paste All?

- Thêm 1 dòng lệnh tạo công thức Sum, nếu 3, 4 ô Sum nằm trên những cột cách nhau thì phải làm 3, 4 dòng lệnh? Trong khi chỉ cần có 1 dòng trắng xen giữa dòng tổng và dòng dữ liệu cuối cùng, thì Excel tự cập nhật Sum range?

Xem lại thì thấy Code của Hướng tốt hơn nếu chèn trước 1 dòng trắng vào trên dòng tổng. Dòng này cũng có thể dấu đi như file kèm theo.

Code đó dài hơn vì có những dòng khai báo biến, dòng bẫy lỗi, dòng xóa bộ nhớ, bài bản hơn.

TrungChinhs · 6/9/09

@ ptm0412 sáng nay em đã sửa lại code khắc phục một số nhược điểm trên rồi bác ạ ! Tuy nhiên tính bài bản thì vẫn chưa. Bác xem lại và góp ý thêm cho em nhé.
Cảm ơn bác nhiều !
Trung Chính

ptm0412 · 6/9/09

Bạn xem lại bài tôi viết bên trên: "ngắn nhưng thừa"
File mới của bạn sau khi sửa cũng vẫn vậy. Nếu có 8 cột chứa công thức bạn vẫn phải viết 8 dòng lệnh gán công thức; nếu có 4 cột sum bạn sẽ phải dùng 4 câu lệnh gán công thức sum. Mà vẫn phải dùng 3 câu lệnh định dạng border

Tất cả những cái đó chỉ cần dùng 1 câu copy và paste All. Bạn chưa xem file có code của Hướng, hay là bạn chưa thử?

Bỏ các vụ bẫy lỗi, khai báo, xóa bộ nhớ, chỉ có 2 dòng lệnh thực thi chính:

PHP:

Target.EntireRow.Copy
        Target.Offset(1, 0).EntireRow.Insert Shift:=xlDown

Vừa paste All vừa insert tương tự như lệnh insert copy cell trong menu chuột phải.

PhanTuHuong · 8/9/09

ndu96081631 đã viết:
Thầy quét chọn nguyên vùng dử liệu, Click phải chuột, chọn Create List là xong
Ah, quên, hình như thầy dùng Excel 2007? Cái này hơi khác: Quét chọn vùng dử liệu, vào tab Insert, chọn Table
Sau khi đã tạo List hoặc Table xong, thầy gõ gì đó vào dòng kế cuối là lập tức nó sẽ chèn thêm 1 dòng cho thầy ---> Các công thức ở dòng trên cũng tự động copy xuống dòng dưới
Cái này rất thuận tiện cho việc nhập liệu có dòng tổng cuối trang ---> Chức năng sẳn có, khỏi cần viết gì cả ---> Khỏe re!

Trong Excel 2003, dùng Create List thì được. Nhưng trong 2007, lại là Create Table, nhìn chung trông rất giống nhau (khác mỗi 2 chữ trên) nhưng chức năng thêm dòng lại không thực hiện được.

ndu96081631 · 9/9/09

PhanTuHuong đã viết:
Trong Excel 2003, dùng Create List thì được. Nhưng trong 2007, lại là Create Table, nhìn chung trông rất giống nhau (khác mỗi 2 chữ trên) nhưng chức năng thêm dòng lại không thực hiện được.

Thầy nói sao ấy chứ... Bây giờ tôi đang thí nghiệm trên Excel 2007 đây, thêm dòng bình thường

rollover79 · 9/9/09

ndu96081631 đã viết:
Thầy nói sao ấy chứ... Bây giờ tôi đang thí nghiệm trên Excel 2007 đây, thêm dòng bình thường

NDU thử thêm dòng Total Row vào xem sẽ biết sự khác nhau giữa List và Table.

Cách tối ưu code thêm dòng

VBA & VB.NET for Excel & AutoCad

Huyền thoại GPE

Bad Excel Member

File đính kèm

VBA & VB.NET for Excel & AutoCad

Huyền thoại GPE

VBA & VB.NET for Excel & AutoCad

Ốc và Sóc

Huyền thoại GPE

Ốc và Sóc

File đính kèm

Bad Excel Member

File đính kèm

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

File đính kèm

Bad Excel Member

VBA & VB.NET for Excel & AutoCad

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

File đính kèm

Bad Excel Member

File đính kèm

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

Bad Excel Member

VBA & VB.NET for Excel & AutoCad

Huyền thoại GPE

File đính kèm

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

Facebook

Group