Arrays

1. Arrays Lecturer: KanjanaThongsanit

2. Example • การใช้ Array ใช้ในการเก็บข้อมูลที่ต้องการในรูปแบบมิติต่าง เช่น

3. ถ้าเรามีข้อมูลอยู่จำนวนหนึ่งดังนี้ สมมุติว่าที่ คอลัมม์ A ชื่อคน คอลัมม์B คือ เงินเดือนที่ได้รับ • เราต้องการหาค่าเฉลี่ยของเงินเดือนที่สูงกว่า 50,000 บาท และเก็บข้อมูลว่าใครบ้างที่มีเงินเดือนดังกล่าว และได้รับเงินเท่าไร

4. จะทำอย่างไร? จึงมีความจำเป็นต้องกำหนดตัวแปรที่เป็น Array เพื่อมา รับค่าข้อมูลของคนที่มีเงินเดือน เกิน 50,000

5. การประกาศตัวแปร Array • การประกาศรูปแบบนี้เป็นการระบุพื้นที่ ของ Array ที่จะเก็บข้อมูล ผู้เขียนโปรแกรมต้องรู้ว่าจำนวนข้อมูลที่ต้องการเก็บมีกี่ข้อมูล • Dim employee(100)As String, salary(100) As Currency • ใน VBA จะระบุข้อมูลโดยที่เริ่มจาก Employee(0),Employee(1), Employee(2), …, Employee(100) • หรือที่เรียกว่า 0-based indexing

6. การประกาศตัวแปร Array • แต่ถ้าผู้ใช้ไม่คุ้นกับการเริ่มต้นด้วย 0 สามารถกำหนด โดยใช้คำสั่ง Option Base1 • โดยวางที่ด้านบนของ Code โดยอยู่ด้านล่างของ Option Explicit • จะทำให้ กลายเป็น 1-based indexing • ทำให้ข้อมูลเริ่มต้นที่ Employee(1),Employee(2), Employee(3), …, Employee(100)

7. หรือผู้ใช้อาจกำหนดในรูปแบบหรือผู้ใช้อาจกำหนดในรูปแบบ • Dim employee(1 To 100)As String, salary(1 To 100) As Currency • ซึ่งสามารถใช้ในขณะที่ กำหนด Base 0 ได้

8. Dynamic indexing and Redim • บางที่เราไม่ทราบขนาดของ Arrayดังนั้นเพื่อไม่ให้เปลืองพื้นที่จากการระบุขนาดของ Array ที่มีขนาดใหญ่เกินไป • เราสามารถกำหนดให้ขนาดของ Array มีการเปลี่ยนแปลงขนาดได้ วิธีการคือ 1. เริ่มต้น ประกาศ Array เป็นวงเล็บว่าง Dim student() as String

9. Dynamic indexing and Redim 2. การกำหนดขนาด Array หลังจากที่ user ระบุจำนวนข้อมูล เช่น nStudent= InputBox(“How many students are in your Department”) Redim student(1 To nStudent) ดังนั้นขนาดของ Array จึงขึ้นกับว่าผู้ใช้ ใส่ จำนวนนักเรียนเท่าไร ใน VBA เราสามารถใช้ Redimกี่ครั้งก็ได้ แต่การใช้ Redimถ้า ขนาดของ Array ที่ประกาศใหม่ มีขนาดเล็กกว่าเดิม ข้อมูลที่มีเดิมก็จะถูกลบได้

10. Check 1 Sub test_redim() Dim student() As String Dim nstudent As Integer Dim x As Integer nstudent = InputBox("how many student") ‘ใช่ReDimกำหนด array size ของ student() For x = 0 To nstudent ‘ รับค่าชื่อของ student แต่ละคนเพื่อป้อนค่าเข้าสู่ตัวแปร student Next ‘ใส่For Loop เพื่อแสดงชื่อของ student เพื่อป้อนค่าเข้าสู่ตัวแปร student End Sub

11. Multiple Dimensions การประกาศตัวแปร เช่น Dim student_subject(1 To 100,1 To 5) As string ดังนั้นในข้อมูลนี้จะมีพื้นที่สำหรับเก็บข้อมูลจำนวน 500 ข้อมูล ตัวอย่างของ Arrays ใน VBA มีตารางข้อมูลของ product code Example File name : Unit Prices.xlsm

12. ข้อจำกัดของการกำหนด ตัวแปร เมื่อเราประกาศตัวแปรอยู่ภายใน sub หมายความว่าเราประกาศตัวแปร อยู่ในระดับ local หมายความว่าจะให้ตัวแปรนี้ได้ภายใน sub นั้น เท่านั้น ตัวอย่างเช่น Sub Test1() Dim weight As double weight = 50 End Sub Sub Test2() MsgBox “The weight ” & weight End Sub ถ้าเรา runTest1 แล้ว run Test 2 จะพบว่าจะไม่ปรากฏค่าใดๆ

13. การประกาศตัวแปร 1. ประกาศ Dim weight As double ที่ด้าน บนของ Module หมายถึง ตัวแปรนี้สามารถใช้ภายใน module นี้ใน sub ไหนก็ได้ 2. ประกาศ Public weight As double หมายถึง ตัวแปรนี้สามารถเรียกใช้ ได้ใน module อื่น ๆ ใน project เดียวกันได้

14. การกำหนด Code ให้เป็นกลุ่ม ๆ • เนื่องจากการเขียน sub ที่มีความยาวมากๆ จะเกิดปัญหาคือ • อ่านยาก • ถ้าพบจุดบกพร่องของโปรแกรมจะแก้ไขได้ยาก • ยากในการจะนำ code นั้นๆ มาใช้อีกที สำหรับการเขียนโปรแกรมอื่นๆ

15. ตัวอย่าง การกำหนด Code ให้เป็นกลุ่ม ๆ Sub Main() Call Task 1 Call Task 2 End Sub Sub Task1() Call Task3() End Sub Sub Task2() ‘ Lines of code End Sub Sub Task3() ‘ Lines of code End Sub

16. ในการเขียน Code อาจไม่ใช้คำสั่ง Call แต่อาจใช้ Task1 เลย ก็ได้ แต่ข้อดีของการ ใช้ call คือทำให้ แยกชัดระหว่างตัวแปรกับชื่อ sub ที่ถูกเรียก ตัวอย่าง Sub NearestNeighbor() Call GetProblemSize Call Initialize Call PerformHeuristic Call DisplayResults End Sub

17. การเขียนโปรแกรมสามารถทำได้หลายรูปแบบการเขียนโปรแกรมสามารถทำได้หลายรูปแบบ • ประกาศตัวแปรที่ ด้านบน 2. การส่งผ่านค่า Arguments

18. Check 2 ประกาศตัวแปรที่ ด้านบน Dim lastName As String, firstName As String Sub main() Dim I As Integer For I = 1 To 10 lastName = Range(“nm").Cells(I, 2) firstName = Range(“nm").Cells(I, 1) Call DisplayName Next End Sub Sub DisplayName() Dim customerName As String customerName = firstName & " " & lastName MsgBox "The customer's full name is " & customerName End Sub สร้างข้อมูลใน excel และตั้งชื่อว่า nm

19. Check 3การส่งผ่านค่า Arguments Sub Main() Dim I As Integer, firstName As String, lastName As String For I = 1 To 10 lastName = Range(“nm").Cells(I, 2) firstName = Range(“nm").Cells(I, 1) Call DisplayName(lastName, firstName) Next End Sub Sub DisplayName(iNameAs String, fName As String) Dim customerName As String customerName =iName& " " & fName MsgBox "The customer's full name is " & customerName End Sub

20. จะเห็นได้ว่าการส่งผ่าน Argument ที่สร้าง ใน sub ไม่จำเป็นจะต้องตั้งชื่อเหมือนกัน แต่สิ่งที่ ต้องการ คือ 1.จำนวนตัวแปรหลัง Argument ต้องเท่ากัน 2.ชนิดตัวแปรหลัง Argument ต้องเท่ากัน 3.เรียงลำดับตัวแปรหลัง Argument ต้องเหมือนกัน

21. Function Subroutines • สิ่งที่ Function ไม่เหมือนกับ Sub ทั่วไปคือ Function สามารถ ส่งค่าได้ เหมือนกับ การที่เราเรียก function ใน excel เราใส่ค่า input ลงไป function จะส่งค่าที่เป็นคำตอบให้เรา

22. Function Subroutines Function Larger(number1 As Single, number2 As Single) As Single If number1 >= number2 Then Larger = number1 Else Larger = number2 End If End Function Sub CallingSub() Dim firstNumber As Single, secondNumber As Single firstNumber = 3 secondNumber = 5 MsgBox "The larger of the two number is " & Larger(firstNumber, secondNumber) End Sub

23. ตัวอย่าง Function • ตัวอย่าง Function อยู่ใน file : Functions Function MySum(values As Variant) As Single Dim v As Variant, total As Single For Each v In values total = total + v Next MySum = total End Function

24. การกำหนดเหตุการณ์เกิดขึ้นเมื่อ workbook ถูกเปิด ตัวอย่าง ใน File Merge list เราสามารถ Set ได้ว่าทุกครั้งที่ เปิด workbook ให้เปิดที่ sheet “Explanation” ตลอด วิธีการ Set คือเราสามารถกำหนด ได้ที่ ThisWorkbookใน VBE

25. การกำหนดเหตุการณ์เกิดขึ้นเมื่อ workbook ถูกเปิด

26. Assignment :Traveling Salesman Problem • Given a collection of cities and the cost of travel between each pair of them, the traveling salesman problem, or TSP for short, is to find the cheapest way of visiting all of the cities and returning to your starting point.

27. 2 1 4 3 5 2 1 5 4 3 ข้อมูลระหว่างเมืองแต่ละเมือง

28. 1.ให้สร้างข้อมูลของระยะทาง โดย random ค่าเพื่อให้ได้ output ตามตาราง ออกแบบ Code เพื่อให้ได้ output ตามตาราง For irow = 1 To nCities - 1 For icol = _____ To nCities .Cells(irow, icol) = Int(Rnd *100) + 1 Next Next

29. 2. เพื่อแสดงว่า ระยะทางไปกลับเท่ากัน,Copy ลงตำแหน่ง inverse For iRow = 2 To nCities For iCol = 1 To iRow – 1 .Cells(iRow, iCol) = ____________   Next Next

30. Nearest Neighbor Nearest Neighbor เป็น Heuristic ที่มีแนวคิดง่าย ๆ คือ • จาก node เริ่มต้นจะค้นหา node s ที่มีระยะที่ใกล้เคียงที่สุด และจะเลือกเส้นทางที่ไปหา nodes นั้นๆ • Node s จะถูกเลือก starting node ต่อไป แล้วเลือกหา node ที่ใกล้ที่สุดโดยไม่รวม node ที่ถูกเลือกไปแล้ว

31. Nearest Neighbor สมมุติเริ่มที่ City 1 เมืองที่ใกล้ที่สุดคือ City 3 จาก City 3 เมืองที่ใกล้ที่สุด(ที่ไม่รวม City 1)คือ City 4 จาก City 4 เมืองที่ใกล้ที่สุด(ที่ไม่รวม City 1,3)คือ City 5 จาก City 5 เมืองที่ใกล้ที่สุด(ที่ไม่รวม City 1,3,4)คือ City 2

32. 2 1 5 4 3 เริ่มต้นของการ สร้าง code ต้องออกแบบลำดับการคิด

33. For each step of route For each city Determine the minimum distance of the node which is not the current node and visited node Next Keep the solution Set the node was visited Calculate the total distance Set to the current node = the minimum node Next total distance = total distance + distance between the stating node and the last node write the solution to worksheet massageBox Report the total distance

34. Determine the minimum distance Set minimum_distance = bignumber For eachicity If (icityis not the current node) and (is not the visited node) If the distance value of the current node toicity < minimum_distance minimum_distance = the distance value minimum_node = icity End If End If Next

35. Code อื่น ๆ • Route(step)= minimum_node • wasVisited(minimum_node) = True • totalDistance = totalDistance + minimum_distance • current node = minimum_node