VBAเป็นส่วนหนึ่งของชุดโปรแกรม Microsoft Office^{(Microsoft Office)}มาหลายปีแล้ว แม้ว่าจะไม่มีฟังก์ชันการทำงานเต็มรูปแบบและพลังของแอปพลิเคชัน VB เต็มรูปแบบ แต่VBA ก็ ให้ความยืดหยุ่นแก่ผู้ใช้Office ในการผสานรวมผลิตภัณฑ์ ^(Office)Officeและทำให้งานของคุณทำงานโดยอัตโนมัติ

หนึ่งในเครื่องมือที่ทรงพลังที่สุดที่มีอยู่ในVBAคือความสามารถในการโหลดข้อมูลทั้งหมดลงในตัวแปรเดียวที่เรียกว่าอาร์เรย์ การโหลดข้อมูลด้วยวิธีนี้ทำให้คุณสามารถจัดการหรือคำนวณช่วงข้อมูลนั้นได้หลากหลายวิธี

ดังนั้นอาร์เรย์VBA คืออะไร? ^(VBA)ในบทความนี้ เราจะตอบคำถามนั้น และแสดงวิธีใช้ในสคริปต์ VBA ของคุณ^{(your own VBA script)}เอง

อาร์เรย์ VBA คืออะไร?

การใช้อาร์เรย์VBA ใน ^(VBA)Excelทำได้ง่ายมาก แต่การทำความเข้าใจแนวคิดของอาร์เรย์อาจซับซ้อนเล็กน้อยหากคุณไม่เคยใช้มาก่อน

ลองนึกถึง^(Think)อาร์เรย์เหมือนกล่องที่มีส่วนต่างๆ อยู่ข้างใน อาร์เรย์หนึ่งมิติคือกล่องที่มีส่วนหนึ่งบรรทัด อาร์เรย์สองมิติคือกล่องที่มีส่วนสองบรรทัด

คุณสามารถใส่ข้อมูลลงในแต่ละส่วนของ "กล่อง" นี้ในลำดับใดก็ได้ที่คุณต้องการ

ที่จุดเริ่มต้นของ สคริปต์ VBAคุณต้องกำหนด "กล่อง" นี้โดยกำหนดอาร์เรย์VBA ของคุณ ^(VBA)ดังนั้น ในการสร้างอาร์เรย์ที่สามารถเก็บข้อมูลหนึ่งชุด (อาร์เรย์หนึ่งมิติ) คุณจะต้องเขียนบรรทัดต่อไปนี้

Dim arrMyArray(1 To 6) As String

ภายหลังในโปรแกรมของคุณ คุณสามารถใส่ข้อมูลลงในส่วนใดๆ ของอาร์เรย์นี้โดยอ้างอิงส่วนในวงเล็บ

arrMyArray(1) = "Ryan Dube"

คุณสามารถสร้างอาร์เรย์สองมิติโดยใช้บรรทัดต่อไปนี้

Dim arrMyArray(1 To 6,1 to 2) As Integer

ตัวเลขตัวแรกแสดงถึงแถว และตัวที่สองคือคอลัมน์ ดังนั้นอาร์เรย์ด้านบนจึงสามารถเก็บช่วงที่มี 6 แถวและ 2 คอลัมน์ได้

คุณสามารถโหลดองค์ประกอบใด ๆ ของอาร์เรย์นี้ด้วยข้อมูลดังนี้

arrMyArray(1,2) = 3

สิ่งนี้จะโหลด 3 ลงในเซลล์ B1

อาร์เรย์สามารถเก็บข้อมูลประเภทใดก็ได้เป็นตัวแปรปกติ เช่น สตริง บูลีน จำนวนเต็ม โฟลต และอื่นๆ

ตัวเลขในวงเล็บสามารถเป็นตัวแปรได้เช่นกัน โปรแกรมเมอร์มักใช้ For Loopเพื่อนับผ่านทุกส่วนของอาร์เรย์ และโหลดเซลล์ข้อมูลในสเปรดชีตลงในอาร์เรย์ คุณจะเห็นวิธีการดำเนินการนี้ในบทความนี้

วิธีการตั้งโปรแกรมอาร์เรย์ VBA ใน Excel

มาดูโปรแกรมง่ายๆ ที่คุณอาจต้องการโหลดข้อมูลจากสเปรดชีตลงในอาร์เรย์หลายมิติ

ตัวอย่างเช่น ให้ดูสเปรดชีตการขายผลิตภัณฑ์ ซึ่งคุณต้องการดึงชื่อตัวแทนขาย รายการ และยอดขายรวมออกจากสเปรดชีต

โปรดทราบว่าในVBAเมื่อคุณอ้างอิงแถวหรือคอลัมน์ คุณจะนับแถวและคอลัมน์ที่^{(rows and columns)}เริ่มต้นจากด้านซ้ายบนที่ 1 ดังนั้นคอลัมน์ตัวแทนคือ 3 คอลัมน์รายการคือ 4 และคอลัมน์ทั้งหมดคือ 7

ในการโหลดสามคอลัมน์นี้จากทั้ง 11 แถว คุณจะต้องเขียนสคริปต์ต่อไปนี้

Dim arrMyArray(1 To 11, 1 To 3) As String
Dim i As Integer, j As Integer

For i = 2 To 12
   For j = 1 To 3
      arrMyArray(i-1, j) = Cells(i, j).Value
   Next j
Next i

คุณจะสังเกตเห็นว่าในการข้ามแถวส่วนหัว หมายเลขแถวใน For look แรกต้องเริ่มต้นที่ 2 แทนที่จะเป็น 1 ซึ่งหมายความว่าคุณต้องลบ 1 สำหรับค่าแถวอาร์เรย์เมื่อคุณโหลดค่าเซลล์ ลงในอาร์เรย์โดยใช้Cells (i, j).Value

ตำแหน่งที่จะแทรกสคริปต์ VBA Array ของคุณ^{(Your VBA Array Script)}

ในการวางโปรแกรมสคริปต์VBA ใน ^(VBA)Excelคุณต้องใช้ตัวแก้ไขVBA คุณสามารถเข้าถึงได้โดยเลือก เมนู นักพัฒนา^(Developer)และเลือกดูโค้ด^{(View Code)}ใน ส่วน การควบคุม^(Controls)ของริบบิ้น

หากคุณไม่เห็นผู้พัฒนา^(Developer)ในเมนู คุณจะต้องเพิ่มเข้าไป ในการดำเนินการนี้ ให้เลือกไฟล์^(File)และตัวเลือก^(Options)เพื่อเปิดหน้าต่างตัวเลือกของ Excel

เปลี่ยนคำสั่ง select จาก dropdown เป็นAll Commands ^{(All Commands)}เลือก ผู้ พัฒนา^(Developer)จากเมนูด้านซ้ายและเลือก ปุ่ม เพิ่ม^(Add)เพื่อย้ายไปยังบานหน้าต่างด้านขวา เลือกช่องทำเครื่องหมายเพื่อเปิดใช้งานและเลือกตกลง^(OK)เพื่อเสร็จสิ้น

เมื่อ หน้าต่าง ตัวแก้ไขโค้ด^{(Code Editor)}เปิดขึ้น ตรวจสอบให้แน่ใจว่าได้เลือกแผ่นงานข้อมูลของคุณในบานหน้าต่างด้านซ้าย เลือกเวิ^(Worksheet) ร์กชี ตในรายการดรอปดาวน์ด้านซ้ายและเปิดใช้งาน^(Activate)ทางด้านขวา สิ่งนี้จะสร้างรูทีนย่อยใหม่ชื่อWorksheet_Activate ()

ฟังก์ชันนี้จะทำงานทุกครั้งที่เปิดไฟล์สเปรดชีต วางโค้ดลงในบานหน้าต่างสคริปต์ภายในรูทีนย่อยนี้

สคริปต์นี้จะทำงานใน 12 แถว และจะโหลดชื่อตัวแทนจากคอลัมน์ 3 รายการจากคอลัมน์ 4 และยอดขายรวมจากคอลัมน์ 7

เมื่อ For loops ทั้งคู่เสร็จสิ้น อาร์เรย์สองมิติ arrMyArray จะมีข้อมูลทั้งหมดที่คุณระบุจากชีตต้นฉบับ

การจัดการอาร์เรย์ใน Excel VBA

สมมติว่าคุณต้องการใช้ภาษีขาย 5% กับราคาขายสุดท้ายทั้งหมด แล้วเขียนข้อมูลทั้งหมดลงในแผ่นงานใหม่

คุณสามารถทำได้โดยเพิ่ม For loop อื่นต่อจากอันแรก ด้วยคำสั่งให้เขียนผลลัพธ์ไปยังชีตใหม่

For k = 2 To 12
   Sheets("Sheet2").Cells(k, 1).Value = arrMyArray(k - 1, 1)
   Sheets("Sheet2").Cells(k, 2).Value = arrMyArray(k - 1, 2)
   Sheets("Sheet2").Cells(k, 3).Value = arrMyArray(k - 1, 3)
   Sheets("Sheet2").Cells(k, 4).Value = arrMyArray(k - 1, 3) * 0.05
Next k

การดำเนินการนี้จะ "ยกเลิกการโหลด" อาร์เรย์ทั้งหมดลงในSheet2โดยมีแถวเพิ่มเติมที่มียอดรวมคูณด้วย 5% สำหรับจำนวนภาษี

แผ่นงานผลลัพธ์จะมีลักษณะดังนี้

อย่างที่คุณเห็นอาร์เรย์VBA ใน ^(VBA)Excelมีประโยชน์อย่างยิ่งและใช้งานได้หลากหลายเหมือนกับเคล็ดลับอื่นๆ^{(any other Excel trick)}ของ  Excel

ตัวอย่างข้างต้นเป็นการใช้อาร์เรย์ที่ง่ายมาก คุณสามารถสร้างอาร์เรย์ที่มีขนาดใหญ่กว่ามากและทำการเรียงลำดับ หาค่าเฉลี่ย หรือฟังก์ชันอื่นๆ มากมายกับข้อมูลที่คุณจัดเก็บไว้ในอาร์เรย์ได้

หากคุณต้องการสร้างสรรค์ผลงานอย่างแท้จริง คุณสามารถสร้างอาร์เรย์สองอาร์เรย์ที่มีช่วงของเซลล์^{(containing a range of cells)}จากแผ่นงานที่แตกต่างกันสองแผ่น และทำการคำนวณระหว่างองค์ประกอบของแต่ละอาร์เรย์

แอปพลิเคชั่นถูก จำกัด ด้วยจินตนาการของคุณเท่านั้น

What Is a VBA Array in Excel and How to Program One

VBA has been рart of the Microsoft Office suitе for many years. While it doesn’t have the full functionality аnd power of a full VB application, VBA proνides Office users with the flexibіlity to integratе Office products and automаte the work you do in them.

One of the most powerful tools available in VBA is the ability to load an entire range of data into a single variable called an array. By loading data in this way, you can manipulate or perform calculations on that range of data in a variety of ways.

So what is a VBA array? In this article we’ll answer that question, and show you how to use one in your own VBA script.

What Is a VBA Array?

Using a VBA array in Excel is very simple, but understanding the concept of arrays can be a little bit complex if you’ve never used one.

Think of an array like a box with sections inside it. A one-dimensional array is a box with one line of sections. A two-dimensional array is a box with two lines of sections.

You can put data into each section of this “box” in any order you like.

At the beginning of your VBA script, you need to define this “box” by defining your VBA array. So to create an array that can hold one set of data (a one-dimension array), you would write the following line.

Dim arrMyArray(1 To 6) As String

Later in your program, you can place data into any section of this array by referencing the section inside of parentheses.

arrMyArray(1) = "Ryan Dube"

You can create a two-dimensional array using the following line.

Dim arrMyArray(1 To 6,1 to 2) As Integer

The first number represents the row, and the second is the column. So the above array could hold a range with 6 rows and 2 columns.

You can load any element of this array with data as follows.

arrMyArray(1,2) = 3

This will load a 3 into the cell B1.

An array can hold any type of data as a regular variable, such as strings, boolean, integers, floats and more.

The number inside the parenthesis can also be a variable. Programmers commonly use a For Loop to count through all sections of an array and load data cells in the spreadsheet into the array. You’ll see how to do this later in this article.

How to Program a VBA Array in Excel

Let’s take a look at a simple program where you may want to load information from a spreadsheet into a multidimensional array.

As an example, let’s look at a product sales spreadsheet, where you want to pull the sales rep’s name, item, and total sale out of the spreadsheet.

Note that in VBA when you reference rows or columns, you count rows and columns starting from the top left at 1. So the rep column is 3, the item column is 4, and the total column is 7.

To load these three columns over all 11 rows, you’ll need to write the following script.

Dim arrMyArray(1 To 11, 1 To 3) As String
Dim i As Integer, j As Integer

For i = 2 To 12
   For j = 1 To 3
      arrMyArray(i-1, j) = Cells(i, j).Value
   Next j
Next i

You’ll notice that in order to skip the header row, the row number in the first For look needs to start at 2 rather than 1. This means you need to subtract 1 for the array row value when you’re loading the cell value into the array using Cells(i, j).Value.

Where to Insert Your VBA Array Script

To place program a VBA script in Excel, you need to use the VBA editor. You can access this by selecting the Developer menu and selecting View Code in the Controls section of the ribbon.

If you don’t see the Developer in the menu, you’ll need to add it. To do this, select File and Options to open the Excel Options window.

Change the choose commands from dropdown to All Commands. Select Developer from the left menu and select the Add button to move it to the pane on the right. Select the checkbox to enable it and select OK to finish.

When the Code Editor window opens, make sure the sheet your data is in is selected in the left pane. Select Worksheet in the left dropdown and Activate in the right. This will create a new subroutine called Worksheet_Activate().

This function will run whenever the spreadsheet file is opened. Paste the code into the script pane inside this subroutine.

This script will work through the 12 rows and will load the rep name from column 3, the item from column 4, and the total sale from column 7.

Once both For loops are finished, the two dimensional array arrMyArray contains all of the data you specified from the original sheet.

Manipulating Arrays in Excel VBA

Let’s say you want to apply a 5% sales tax to all of the final sale prices, and then write all of the data to a new sheet.

You can do this by adding another For loop after the first, with a command to write the results to a new sheet.

For k = 2 To 12
   Sheets("Sheet2").Cells(k, 1).Value = arrMyArray(k - 1, 1)
   Sheets("Sheet2").Cells(k, 2).Value = arrMyArray(k - 1, 2)
   Sheets("Sheet2").Cells(k, 3).Value = arrMyArray(k - 1, 3)
   Sheets("Sheet2").Cells(k, 4).Value = arrMyArray(k - 1, 3) * 0.05
Next k

This will “unload” the entire array into Sheet2, with an additional row containing the total multiplied by 5% for the tax amount.

The resulting sheet will look like this.

As you can see, VBA arrays in Excel are extremely useful and as versatile as any other Excel trick. 

The example above is a very simple use for an array. You can create much larger arrays and perform sorting, averaging, or many other functions against the data you store in them.

If you want to get real creative, you could even create two arrays containing a range of cells from two different sheets, and perform calculations between elements of each array.

The applications are limited only by your imagination.

Related posts

• วิธีการลบบรรทัดว่างใน Excel

• วิธีใช้ฟีเจอร์ Speak Cells ของ Excel

• วิธีแทรกแผ่นงาน Excel ลงใน Word Doc

• วิธีใช้การวิเคราะห์แบบ What-If ของ Excel

• วิธีแก้ไขแถวใน Excel

• วิธีสร้างมาโครหรือสคริปต์ VBA ใน Excel

• คู่มือ VBA ขั้นสูงสำหรับ MS Excel

• วิธีผสานเซลล์ คอลัมน์ และแถวใน Excel

• จัดกึ่งกลางข้อมูลเวิร์กชีตของคุณใน Excel สำหรับการพิมพ์

• วิธีใช้ฟังก์ชัน PMT ใน Excel

• ทำไมคุณควรใช้ช่วงที่มีชื่อใน Excel

• วิธีเปิด Word และ Excel ในเซฟโหมด

• 5 วิธีในการแปลงข้อความเป็นตัวเลขใน Excel

• วิธีจัดเรียงตามวันที่ใน Excel

• 3 วิธีในการแบ่งเซลล์ใน Excel

• วิธีการเขียนสูตร IF/คำสั่งใน Excel

• วิธีการสร้างแผนภูมิวงกลมใน Excel

• 4 วิธีในการใช้เครื่องหมายถูกใน Excel

• สะกดตัวเลขอย่างรวดเร็วใน Word และ Excel

• วิธีแก้ไขข้อผิดพลาด #N/A ในสูตร Excel เช่น VLOOKUP