VBA 는 수년 동안 Microsoft Office 제품군 의 일부였습니다 . 완전한 VB 응용 프로그램의 모든 기능과 성능을 가지고 있지는 않지만 VBA 는 Office 사용자에게 (Office)Office 제품을 통합하고 해당 제품에서 수행하는 작업을 자동화할 수 있는 유연성을 제공 합니다.
VBA 에서 사용할 수 있는 가장 강력한 도구 중 하나 는 전체 데이터 범위를 배열이라는 단일 변수에 로드하는 기능입니다. 이러한 방식으로 데이터를 로드하면 다양한 방식으로 해당 데이터 범위를 조작하거나 계산을 수행할 수 있습니다.
그렇다면 VBA 배열이란 무엇입니까? 이 기사에서 우리는 그 질문에 답하고 당신 자신의 VBA 스크립트(your own VBA script) 에서 그것을 사용하는 방법을 보여줄 것 입니다.
VBA 배열이란 무엇입니까?
Excel 에서 (Excel)VBA 배열을 사용하는 것은 매우 간단하지만 배열의 개념을 한 번도 사용해 본 적이 없다면 배열의 개념을 이해하는 것이 약간 복잡할 수 있습니다.
(Think)내부에 섹션이 있는 상자와 같은 배열을 생각하십시오 . 1차원 배열은 한 줄의 섹션이 있는 상자입니다. 2차원 배열은 두 줄의 섹션이 있는 상자입니다.
이 "상자"의 각 섹션에 원하는 순서대로 데이터를 넣을 수 있습니다.
VBA 스크립트 시작 부분에서 VBA 배열 을 정의하여 이 "상자"를 정의해야 합니다 . 따라서 한 세트의 데이터(1차원 배열)를 저장할 수 있는 배열을 만들려면 다음 줄을 작성합니다.
Dim arrMyArray(1 To 6) As String
나중에 프로그램에서 괄호 안의 섹션을 참조하여 이 배열의 모든 섹션에 데이터를 배치할 수 있습니다.
arrMyArray(1) = "Ryan Dube"
다음 줄을 사용하여 2차원 배열을 만들 수 있습니다.
Dim arrMyArray(1 To 6,1 to 2) As Integer
첫 번째 숫자는 행을 나타내고 두 번째 숫자는 열을 나타냅니다. 따라서 위의 배열은 6개의 행과 2개의 열이 있는 범위를 보유할 수 있습니다.
다음과 같이 데이터가 있는 이 배열의 모든 요소를 로드할 수 있습니다.
arrMyArray(1,2) = 3
이렇게 하면 셀 B1에 3이 로드됩니다.
배열은 문자열, 부울, 정수, 부동 소수점 등과 같은 일반 변수로 모든 유형의 데이터를 보유할 수 있습니다.
괄호 안의 숫자도 변수가 될 수 있습니다. 프로그래머는 일반적으로 For 루프(Loop) 를 사용하여 배열의 모든 섹션을 계산하고 스프레드시트의 데이터 셀을 배열로 로드합니다. 이 문서의 뒷부분에서 이 작업을 수행하는 방법을 볼 수 있습니다.
Excel에서 VBA 배열을 프로그래밍하는 방법
스프레드시트에서 다차원 배열로 정보를 로드할 수 있는 간단한 프로그램을 살펴보겠습니다.
예를 들어 제품 판매 스프레드시트에서 판매 담당자의 이름, 품목 및 총 판매액을 가져오려는 경우를 살펴보겠습니다.
VBA 에서 행이나 열을 참조할 때 행과 열(rows and columns) 은 왼쪽 상단에서 1부터 계산합니다. 따라서 rep 열은 3, item 열은 4, total 열은 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 스크립트를 Excel 에 배치하려면 VBA 편집기 를 사용해야 합니다 . 개발자(Developer) 메뉴를 선택 하고 리본 의 컨트롤 섹션에서 (Controls)코드 보기(View Code) 를 선택하여 액세스할 수 있습니다 .
메뉴에 개발자(Developer) 가 표시되지 않으면 추가해야 합니다. 이렇게 하려면 파일(File) 및 옵션(Options) 을 선택 하여 Excel 옵션 창을 엽니다.
선택 명령을 드롭다운에서 모든 명령(All Commands) 으로 변경합니다 . 왼쪽 메뉴에서 개발자(Developer) 를 선택 하고 추가(Add) 버튼을 선택하여 오른쪽 창으로 이동합니다. 확인란을 선택하여 활성화하고 확인(OK) 을 선택하여 완료합니다.
코드 편집기(Code Editor) 창이 열리면 왼쪽 창에서 데이터가 있는 시트가 선택되어 있는지 확인합니다. 왼쪽 드롭다운에서 워크시트(Worksheet) 를 선택 하고 오른쪽에서 활성화(Activate) 를 선택합니다. 그러면 Worksheet_Activate(Worksheet_Activate) () 라는 새 서브루틴이 생성됩니다 .
이 기능은 스프레드시트 파일이 열릴 때마다 실행됩니다. 이 서브루틴 내부의 스크립트 창에 코드를 붙여넣습니다.
이 스크립트는 12개 행에서 작동하며 3열에서 담당자 이름, 4열에서 항목, 7열에서 총 판매액을 로드합니다.
두 For 루프가 모두 끝나면 2차원 배열 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%를 곱한 합계가 포함됩니다.
결과 시트는 다음과 같습니다.
보시다시피 Excel의 VBA (Excel)배열(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 Office suite for many years. While it doesn’t have the full functionаlity and power of a full VB application, VBA provides Office users with the flexibility to integrаte Officе prodυcts and automate 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.
