셀안 수식입력 VS 매크로 구현. 누가 속도가 더 빠를까요?

Question

안녕하세요.

아래 첫째와 둘째중 누가 속도가 더 빠를까요?

첫째: C1 셀 안에 = A1 + B1 라는 (매크로없이) 즉석 수식을 입력했습니다.

둘째: C1 셀 안에 =plus(A1,B1) 이라는 매크로 수식을 입력했습니다. 이 매크로는 아래

Function plus(num1, num2)
    Range("C1").Value = num1 + num2
End Function

질문 : 위 2개중 누가 속도가 더 빠를까요? 위는 최간단 더하기로 질문을 하기위한 하나의 예일 뿐입니다. 실제로는 엄청나게 복잡한 수식으로 한번 계산할때 20초 정도 걸려요. 그러니 무쟈게 복잡한 수식입니다. 그러니 0.001초라도 더 빠르게 하고 싶어요. 제가 여쭤보는건 이 값을 구하기 위하여 [ 셀안에 수식입력 VS 매크로로 구현 ] 방법중 누가 속도가 더빠르냐? 에 대한 것을 여쭙니다.

예상답변중: Timer 함수를 써서 측정해보라? 라고 말씀하시면, 저는 [ 셀안 새로운 수식값 업데이트 등 변수가 많아... 이 방법은 제대로 측정안됩니다 ] 라고 말씀드리고 싶습니다. 어쟀든 이걸 떠나 제가 알고 싶은건 저 위 2개중 누가 속도가 더 빠르냐인 것입니다.

수미수 · Accepted Answer

대용량 데이터에서 VBA가 빠른 것은 2019이전 버전입니다.
2024이상의 동적 배열은 가변크기 배열입니다. 이 동적 배열은 함수를 한번 만 호출합니다. 기존에 수식 복사 방식이라면 데이터의 크기에 (셀당 함수 호출)따라 함수 호출 횟수가 비례해서 오버헤드가 발생하지만 동적 배열은 함수 호출을 한번만 하고 내부적으로 함수를 평가하고 결과를 가변 크기 배열로 만들어 한번에 분산시키는 방식이므로 속도가 비약적으로 빨라졌습니다.
같은 VLOOKUP 함수로 10만행을 수식 복사 방식과 동적 배열로 시험해보면 속도 측정하지 않고도 월등히 빨라진걸 눈으로 확인할수 있습니다.
그리고 위와 같은 + 연산은 함수가 아니고 넘파이에 브로드캐스팅과 같이 벡터화 연산을 하므로 VBA로 작성한 함수와는 비교 안될 정도로 빠릅니다.
저수준 언어인 C++로 작성된 네이티브 함수는 VBA와 같은 인터프리터 언어보다 컴파일 방식에서 월등히 빠릅니다.
일반적으로 엑셀의 연산은 연산자의 연산은 벡터화, 복잡한 함수 사용과 데이터의 크기가 크면 멀티 스레딩 연산을 사용하고 이는 엑셀의 계산 엔진이 판단하고 둘 다 동시에 사용하거나 거의 벡터화 연산을 주로 사용합니다.
일반적으로는 동적배열을 사용한 함수가 가장 빠르고 막대한 크기에 연산에서 가장 빠른건 DAX입니다.

마법의손 · Answer

데이터량을 늘리거나 계산량을 늘려서 직접 확인하지 않는 이유가 있나요? 아래 수메리안님이 맞다고 생각하며 동의합니다..^^

수메리안 · Answer

안녕하세요~
1. 단순 계산의 경우에서는,
엑셀 셀 수식(=A1+B1)은 C++로 구현된 네이티브 계산 엔진에서 실행되며, 멀티스레드까지 활용하기 때문에 VBA보다 훨씬 빠릅니다.
반면 VBA의 사용자 정의 함수(UDF)는 인터프리터 방식이라 본질적으로 느리고, 특히 Range("C1").Value = …처럼 함수 내부에서 셀을 직접 수정하는 것은 엑셀 재계산 엔진과 충돌하여 속도와 안정성을 크게 떨어뜨립니다.
게다가 수천~수만 번 반복 호출되면 네이티브 수식 대비 수십 배까지 느려질 수 있어, 이는 잘못된 패턴이라 할 수 있습니다.
2. 그러나 대용량 데이터의 경우에서는
행 수가 많아지면 단순 수식만으로는 재계산 오버헤드가 커질 수 있습니다.
이때는 VBA 프로시저를 활용해 배열 기반으로 처리하는 방식이 훨씬 효율적입니다. 즉, 셀 접근을 최소화하여 범위를 한 번에 읽고 → 메모리에서 연산을 모두 수행한 뒤 → 결과를 한 번에 기록하면 됩니다.
여기에 화면 업데이트, 이벤트, 자동계산을 일시적으로 꺼두면 속도 이점은 기하급수적으로 커집니다. 실제로 수식으로는 20초 걸리던 계산도, 배열 기반 VBA 프로시저로 전환하면 몇 초 내에 끝낼 수 있습니다.
단순 계산이고 행이 적은 경우라면 셀 수식이 최적이지만, 복잡한 로직에 행이 많은 경우라면, VBA 배열 프로시저가 압도적으로 유리하다고 볼 수 있습니다~