VBA سالهاست که بخشی از مجموعه Microsoft Office بوده است. در حالی که عملکرد و قدرت کامل یک برنامه VB کامل را ندارد، VBA به کاربران ^(VBA)آفیس^(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 بارگذاری می کند.

یک آرایه می تواند هر نوع داده ای را به عنوان یک متغیر معمولی نگهداری کند، مانند رشته ها، بولی، اعداد صحیح، شناورها و غیره.

عدد داخل پرانتز نیز می تواند متغیر باشد. برنامه نویسان معمولاً از یک حلقه^(Loop) For برای شمارش تمام بخش های یک آرایه و بارگذاری سلول های داده در صفحه گسترده در آرایه استفاده می کنند. در ادامه این مقاله خواهید دید که چگونه این کار را انجام دهید.

نحوه برنامه نویسی یک آرایه VBA در اکسل

بیایید نگاهی به یک برنامه ساده بیندازیم که در آن ممکن است بخواهید اطلاعات را از یک صفحه گسترده در یک آرایه چند بعدی بارگذاری کنید.

به عنوان مثال، بیایید به صفحه‌گسترده فروش محصول نگاه کنیم، جایی که می‌خواهید نام، کالا و کل فروش نماینده فروش را از صفحه‌گسترده خارج کنید.

توجه داشته باشید که در VBA هنگامی که به سطرها یا ستون‌ها ارجاع می‌دهید، سطرها و ستون‌ها^{(rows and columns)} را از بالا سمت چپ از 1 می‌شمارید. بنابراین ستون rep 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 اول باید به جای 1 از 2 شروع شود. این بدان معناست که هنگام بارگیری مقدار سلول باید 1 را برای مقدار ردیف آرایه کم کنید. به آرایه با استفاده از سلول های^(Cells) (i، j).Value.

کجا اسکریپت آرایه VBA خود را وارد کنید^{(Your VBA Array Script)}

برای قرار دادن برنامه یک اسکریپت VBA در ^(VBA)اکسل^(Excel) ، باید از ویرایشگر VBA استفاده کنید. ^(VBA)با انتخاب منوی Developer و انتخاب ^(Developer)View Code در قسمت Controls روبان می توانید به این مورد دسترسی داشته باشید .

اگر برنامه‌نویس^(Developer) را در منو نمی‌بینید ، باید آن را اضافه کنید. برای انجام این کار، File and Options را انتخاب کنید تا پنجره Excel Options باز شود.

دستورات انتخاب را از منوی کشویی به همه دستورات^{(All Commands)} تغییر دهید . از منوی سمت چپ Developer را انتخاب کنید و دکمه Add را انتخاب کنید تا به صفحه سمت راست منتقل شود. چک باکس را برای فعال کردن آن انتخاب کنید و برای^(OK) اتمام، تأیید را انتخاب کنید.

هنگامی که پنجره کد ویرایشگر^{(Code Editor)} باز می شود، مطمئن شوید که برگه ای که داده های شما در آن است در قسمت سمت چپ انتخاب شده است. کاربرگ^(Worksheet) را در منوی سمت چپ انتخاب کنید و در سمت راست Activate را انتخاب کنید . این یک زیربرنامه جدید به نام Worksheet_Activate () ایجاد می کند.

این تابع هر زمان که فایل صفحه گسترده باز شود اجرا می شود. کد را در قسمت اسکریپت داخل این زیر روال قرار دهید.

این اسکریپت در 12 ردیف کار می کند و نام تکرار را از ستون 3، مورد از ستون 4، و فروش کل را از ستون 7 بارگیری می کند.

هنگامی که هر دو حلقه For به پایان رسید، آرایه دو بعدی arrMyArray حاوی تمام داده هایی است که از برگه اصلی مشخص کرده اید.

دستکاری آرایه ها در Excel VBA

فرض کنید می‌خواهید 5 درصد مالیات بر فروش را برای همه قیمت‌های فروش نهایی اعمال کنید و سپس همه داده‌ها را در یک برگه جدید بنویسید.

می توانید این کار را با اضافه کردن یک حلقه For دیگر بعد از حلقه اول، با دستور نوشتن نتایج در یک صفحه جدید انجام دهید.

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)} همه کاره هستند . 

مثال بالا یک کاربرد بسیار ساده برای یک آرایه است. می‌توانید آرایه‌های بسیار بزرگ‌تری ایجاد کنید و مرتب‌سازی، میانگین‌گیری یا بسیاری از عملکردهای دیگر را در مقابل داده‌هایی که در آنها ذخیره می‌کنید انجام دهید.

اگر می‌خواهید خلاقیت واقعی داشته باشید، حتی می‌توانید دو آرایه حاوی طیف وسیعی از سلول‌ها^{(containing a range of cells)} از دو صفحه مختلف ایجاد کنید و محاسباتی را بین عناصر هر آرایه انجام دهید.

برنامه ها فقط با تخیل شما محدود می شوند.

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

VBA has been part of the Microsoft Offіce suite for many years. Whіle іt doesn’t have the full functionality and power of a full VB application, VBA providеs Office users with the flexibility to integrate Office products and automate thе 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

• نحوه حذف خطوط خالی در اکسل

• نحوه استفاده از ویژگی Speak Cells اکسل

• نحوه درج کاربرگ اکسل در Word Doc

• نحوه استفاده از تجزیه و تحلیل What-If اکسل

• نحوه تعمیر یک ردیف در اکسل

• اتصال اکسل به MySQL

• مرتب سازی داده های تک ستونی و چند ستونی در صفحات گسترده اکسل

• چگونه یک فرمول/گزاره IF در اکسل بنویسیم

• هنگام کپی کردن فرمول در اکسل، منابع سلول را حفظ کنید

• 3 روش برای تقسیم سلول در اکسل

• نحوه ادغام سلول ها، ستون ها و ردیف ها در اکسل

• نحوه استفاده از مراجع مطلق در اکسل

• نحوه جابجایی ستون ها در اکسل

• 2 روش برای استفاده از تابع Transpose اکسل

• 4 روش برای تبدیل Excel به Google Sheets

• نحوه استفاده از تابع PMT در اکسل

• نحوه ایجاد یک Pivot Table ساده در اکسل

• نحوه استفاده از دستورات If و Nested If در اکسل

• املای سریع اعداد در Word و Excel

• نحوه استفاده از Flash Fill In Excel