View RSS Feed

อรวีร์

การหลีกเลี่ยงใช้คำสั่งวนรอบ

Rate this Entry
6 Comments
by
อรวีร์
, 23 May 2010 at 23:33 (2683 Views)
ก่อนจะเขียนเทคนิกการใช้คำสั่งวนรอบตอนที่ 3 (ยังคิดไม่ออกเลยว่าจะเขียนอะไรเลยค่ะ )
คิดขึ้นได้ว่าน่าจะเขียนถึงเรื่องตรงข้าม คือ การหลีกเลี่ยงการใช้คำสั่งวนรอบ

การเขียน VBA ใช้คำสั่งวนรอบใน Excel ส่วนใหญ่ที่อรวีร์เห็นจะเป็นเรื่องการเขียนลักษณะนี้
For R = FirstRow To LastRow
. . . . . . .
Next
เพื่อวนทำงานทุกๆบรรทัดที่มีข้อมูล เช่น . . .
โจทย์ : สมมุติมีข้อมูลลักษณะฐานข้อมูลเก็บเป็นบรรทัดๆ โดยเริ่มจากบรรทัดที่ 2 Column A, B, C ไล่บรรทัดลงเรื่อยๆถึงบรรทัดสุดท้าย(ไม่แน่นอน)
ให้ตรวจสอบทุกบรรทัดว่าบรรทัดใดค่าใน Column C น้อยกว่า 0 ให้ลบค่าใน Column B ทิ้ง
การเขียน Code สำหรับงานนี้แบบนี้ปกติจะเขียนได้แบบนี้ค่ะ
Code:
LastRow = [A65535].End(xlUp).Row
For R = 2 To LastRow
    If Cells(R, 3).Value < 0 Then
        Cells(R, 2).ClearContents
    End If
Next
การทำงานของโปรแกรมก็จะวนทำทีละบรรทัด ซึ่งถ้าจำนวนข้อมูลมากเป็นหมื่นบรรทัด โปรแกรมจะทำงานช้า
------------------------------------------------------
การเขียนโปรแกรมใน Excel เราคงเคยได้ยินว่าไม่ควรเขียน Function ใช้งานเองถ้ามันมีอยู่แล้วใน Excel (หรือสามารถประยุกค์หลายๆ Function ใน Excel) เพราะ Function ที่เราเขียนด้วย VBA จะทำงานช้ากว่า
เช่นเดียวกับการวนรอบทีละบรรทัด จะไม่ควรใช้ For . . . Next ถ้าสามารถใช้ให้ Excel ทำทีเดียวทุกบรรทัด

เรามาลองทำขั้นตอนทำงานดังนี้
1. ใส่สูตรที่ D2 = If( C2<0 , NA() ) จะพบว่าบรรทัดใดที่ค่าใน Column C น้อยกว่า 0 จะได้ผลลัพธ์เป็น #N/A
2. Copy จาก D2 ลงไปจน D บรรทัดสุดท้าย
3. เลือกบริเวณ D2 ถึง D บรรทัดสุดท้าย
4. จากเมนู Edit -> Goto -> Special -> เลือก Formula -> ให้เครื่องหมายถูกเฉพาะ Errors -> กด OK
จะพบว่า Excel จะเลือกเฉพาะ Column D ที่ค่าใน C น้อยกว่า 0

ถ้าเราบันทึก Macro ตามขั้นตอนข้างบนและปรับปรุง Code ให้กระชับขึ้นบ้างแล้วจะได้ดังนี้
LastRow = [A65535].End(xlUp).Row
Range("D2:D" & LastRow).FormulaR1C1 = "=IF(RC[-1]<0,NA())"
Range("D2:D" & LastRow).SpecialCells(xlCellTypeFormulas, 16).Select

ถ้าแก้ Code บรรทัดสุดท้ายเป็น
Range("D2:D" & LastRow).SpecialCells(xlCellTypeFormulas, 16).Offset(0, -2).Select
แล้ว Run จะพบว่าเป็นการเลือกเฉพาะ Column B ที่ค่าใน C น้อยกว่า 0

ถ้าแก้ Code บรรทัดสุดท้ายเป็น
Range("D2:D" & LastRow).SpecialCells(xlCellTypeFormulas, 16).Offset(0, -2).ClearContents
แล้ว Run จะพบว่า Excel จะลบค่าใน Column B ที่ค่าใน C น้อยกว่า 0

Submit "การหลีกเลี่ยงใช้คำสั่งวนรอบ" to Facebook Submit "การหลีกเลี่ยงใช้คำสั่งวนรอบ" to Twitter Submit "การหลีกเลี่ยงใช้คำสั่งวนรอบ" to Digg Submit "การหลีกเลี่ยงใช้คำสั่งวนรอบ" to del.icio.us Submit "การหลีกเลี่ยงใช้คำสั่งวนรอบ" to StumbleUpon Submit "การหลีกเลี่ยงใช้คำสั่งวนรอบ" to Google

Categories
‎ Excel VBA and Add-Ins

Comments

  1. อรวีร์'s Avatar
    จากโจทย์ข้างบน Code ที่ปรับปรุงสุดท้ายจะได้ดังนี้ค่ะ
    Code:
    LastRow = [A65535].End(xlUp).Row
With Range("D2:D" & LastRow)
    .FormulaR1C1 = "=IF(RC[-1]<0,NA())"
    .SpecialCells(xlCellTypeFormulas, 16).Offset(0, -2).ClearContents
    .ClearContents ' เพื่อลบค่าชั่วคราวใน Column D ทิ้ง
End With
    อรวีร์จะยกตัวอย่างโจทย์ที่ใช้ลักษณะนี้ไปเรื่อยๆ สักหลายตัวอย่างต่อไปนะคะ เพื่อความเข้าใจดีขึ้น
  2. อรวีร์'s Avatar
    โจทย์ : ลบบรรทัดที่ค่าใน Column A เป็นช่องว่าง
    Code:
    LastRow = [A65535].End(xlUp).Row
With Range("D2:D" & LastRow)
    .FormulaR1C1 = "=IF(ISBLANK(RC1),NA())"
    .SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
    .ClearContents
End With
  3. อรวีร์'s Avatar
    ลบทุกบรรทัดที่สูตรใน Column C ได้ผล Error
    Code:
    LastRow = [A65535].End(xlUp).Row
Range("C2:C" & LastRow).SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
    ลบทุกบรรทัดที่ค่าใน Column C เป็น Error
    Code:
    LastRow = [A65535].End(xlUp).Row
Range("C2:C" & LastRow).SpecialCells(xlCellTypeConstants, 16).EntireRow.Delete
  4. อรวีร์'s Avatar
    บรรทัดที่ค่าใน Column B ยาวกว่า 5 ตัวอักษร ให้ใส่พื้นสีเหลืองที่ Column A ถึง C
    Code:
    LastRow = [A65535].End(xlUp).Row
With Range("D2:D" & LastRow)
    .FormulaR1C1 = "=If( LEN(RC2)>5 , NA() )"
    Application.Intersect( .SpecialCells(xlCellTypeFormulas, 16).EntireRow , Columns("A:C") ).Interior.ColorIndex = 6
    .ClearContents
End With
  5. อรวีร์'s Avatar
    ตรวจสอบค่าใน Column B ที่มีค่ายาวกว่า 5 ตัวอักษร ให้ลบข้อมูลในบรรทัดนั้น
    Code:
    LastRow = [A65535].End(xlUp).Row
With Range("D2:D" & LastRow)
    .FormulaR1C1 = "=If( LEN(RC2)>5 , NA() )"
    .SpecialCells(xlCellTypeFormulas, 16).EntireRow.Clearcontents
    .ClearContents
End With
    ถ้าลบข้อมูลหลังบรรทัดนั้น
    Code:
    LastRow = [A65535].End(xlUp).Row
With Range("D2:D" & LastRow)
    .FormulaR1C1 = "=If( LEN(RC2)>5 , NA() )"
    .SpecialCells(xlCellTypeFormulas, 16).EntireRow.Offset(1, 0).Clearcontents
    .ClearContents
End With
  6. อรวีร์'s Avatar
    ย้ายบรรทัด 3,4, 6,7, 9,10, 12,13 ไปไว้ใน Sheets ที่สอง(เริ่มที่บรรทัดที่ 2)และลบบรรทัดจากต้นทางทิ้ง
    Code:
    LastRow = [A65535].End(xlUp).Row
With Range("D2:D" & LastRow)
    .FormulaR1C1 = "=IF(MOD(ROW(),3)<>2,NA())"
    ' เก็บทุกบรรทัดตามเงื่อนไขไว้ที่ตัวแปร Source
    Set Source = .SpecialCells(xlCellTypeFormulas, 16).EntireRow
    Source.Copy Sheets(2).Range("A2")
    Source.Delete ' ลบบรรทัดทิ้งหลัง Copy ไปแล้ว
    .ClearContents
End With
' ลบ Column D ที่ Copy เกินไปทิ้ง
Sheets(2).Columns(4).ClearContents