반환 배열에 VBA 기능을 만들기

Posted on: 2020年11月12日 2020年11月12日
Tags: custom-functions-in-vba

제목에서 알 수 있듯이 Excel에서 배열을 반환하는 사용자 정의 함수를 만드는 방법을 배웁니다. 우리는 이미`link : / vba-user-defined-functions [VBA에서 사용자 정의 함수 생성]`방법을 배웠습니다. 따라서 시간을 낭비하지 않고 자습서를 시작하겠습니다.

배열 함수 란 무엇입니까?

배열 함수는 사용될 때 배열을 반환하는 함수입니다. 이 함수는 CTRL + SHIFT + ENTER 키 조합과 함께 사용되며 이것이 배열 함수 또는 수식을 CSE 함수 및 수식으로 호출하는 것을 선호하는 이유입니다.

엑셀 배열 함수는 종종 다중 셀 배열 수식입니다. 한 가지 예는`link : / mathematical-functions-excel-transpose-function [TRANSPOSE function]`입니다.

VBA에서 UDF 배열 함수 만들기

따라서 시나리오는 ThreeEven () 함수를 사용하여 처음 3 개의 짝수를 반환하려는 것입니다.

코드는 다음과 같습니다.

Function ThreeEven() As Integer()

'define array

Dim numbers(2) As Integer

'Assign values to array

numbers(0) = 0

numbers(1) = 2

numbers(2) = 4

'return values

ThreeEven = numbers

End Function

워크 시트에서이 함수를 사용하겠습니다.

You can see that, we first select three cells (horizontally, for vertical we have to use two-dimensional array. We have it covered below.). Then we start writing our formula. Then we hit CTRL+SHIFT+ENTER. This fills the selected cells with the array values.

참고 :

실제로 얼마나 많은 세포가 필요한지 알 수 없습니다. 이 경우 항상 예상 배열 길이보다 많은 셀을 선택하십시오. 이 함수는 셀을 배열로 채우고 추가 셀은 # N / A 오류를 표시합니다.
기본적으로이 배열 함수는 값을 수평 배열로 반환합니다.

세로 셀을 선택하려고하면 모든 셀에 배열의 첫 번째 값만 표시됩니다.

어떻게 작동합니까?

배열 함수를 만들려면이 구문을 따라야합니다.

Function functionName(variables) As returnType()

dim resultArray(length) as dataType

'Assign values to array here

functionName =resultArray

End Function

함수 선언은 위에 정의 된 것과 같아야합니다. 이것은 그것이 배열 함수임을 선언했습니다.

워크 시트에서 사용하는 동안 Ctrl + Shift + Enter 키 조합을 사용해야합니다. 그렇지 않으면 배열의 첫 번째 값만 반환합니다.

수직 배열을 반환하는 VBA 배열 함수

UDF 배열 함수가 수직으로 작동하도록하려면 많은 작업을 수행 할 필요가 없습니다. 배열을 2 차원 배열로 선언하기 만하면됩니다. 그런 다음 첫 번째 차원에서 값을 추가하고 다른 차원은 비워 둡니다. 이것이 당신이하는 방법입니다 :

Function ThreeEven() As Integer()

'define array

Dim numbers(2,0) As Integer

'Assign values to array

numbers(0,0) = 0

numbers(1,0) = 2

numbers(2,0) = 4

'return values

ThreeEven = numbers

End Function

이것이 워크 시트에서 사용하는 방법입니다.

===

UDF Array Function with Arguments in Excel* In the above examples, we simply printed sum static values on the sheet.

함수가 범위 인수를 받아들이고 일부 작업을 수행하고 결과 배열을 반환하기를 원한다고 가정 해 보겠습니다.

예

범위의 모든 값에 “-done”추가 이제이 작업을 쉽게 수행 할 수 있지만 사용자 정의 VBA 배열 함수를 사용하여 문제를 해결하는 방법을 보여 드리려고합니다.

그래서 여기에서는 범위를 인수로 취하고 범위의 모든 값에 “-done”을 추가하는 배열 함수를 원합니다. 이것은`link : / tips-concatenate-formula-in-microsoft-excel [concatenation function]`을 사용하여 쉽게 수행 할 수 있지만 여기서는 배열 함수를 사용합니다.

Function CONCATDone(rng As Range) As Variant()

Dim resulArr() As Variant

'Create a collection

Dim col As New Collection

'Adding values to collection

On Error Resume Next

For Each v In rng

col.Add v

Next

On Error GoTo 0

'completing operation on each value and adding them to Array

ReDim resulArr(col.Count - 1, 0)

For i = 0 To col.Count - 1

resulArr(i, 0) = col(i + 1) & "-done"

Next

CONCATDone = resulArr

End Function

설명 :

위의 함수는 범위를 인수로 받아들이고 범위의 각 값에 “-done”을 추가합니다.

여기에서 VBA 컬렉션을 사용하여 배열의 값을 보유한 다음 각 값에 대해 작업을 수행하고 2 차원 배열에 다시 추가 한 것을 볼 수 있습니다.

네, 이것이 배열을 반환 할 수있는 사용자 지정 VBA 배열 함수를 만드는 방법입니다. 충분히 설명이 되었기를 바랍니다. 이 기사와 관련하여 질문이 있으시면 아래 댓글 섹션에 입력하십시오.

작업 파일을 다운로드하려면 아래 링크를 클릭하십시오.

`link : /wp-content-uploads-2019-12-Create-VBA-Function-to-Return-Array-1.xls [__ 배열을 반환하는 VBA 함수 생성]