속도개선 구문 사용시 자동계산 문제

VBA
작성자
나는나
작성일
2021-04-20 18:58
조회
77

안녕하세요.

Application.ScreenUpdating = false
 
Application.Calculation = xlCalculationManual
 
Application.EnableEvents = false
 
'명령문...
 
Application.ScreenUpdating = True
 
Application.Calculation = xlCalculationAutomatic
 
Application.EnableEvents = True

위와 같은 속도개선 구문을 사용할 때 주의사항이 있는지요.

시트에 함수가 입력되어 있고, 그 함수가 참조하는 값이 갱신되었을 때 새로 계산해서 나오는 결과를 또 다른 셀에서 참조한다고 가정하면, Application.Calculation = xlCalculationManual 이 수동계산 명령이 문제 없을지요?

프러시저 앞 부분에 Application.Calculation = xlCalculationManual을 넣었을 때와 넣지 않았을 때 결과값이 다르게 나오지는 않나요?

 

 

회원등급 : 잎새등급
포인트 : 168 EP
총질문 : 6 개 (마감율 : 50%)
채택답변 : 0 개
전체 8

  • 2021-04-20 21:25
    Application.Calculation = xlCalculationAutomatic

    으로 명령문 종료 전 모든 계산이 수행되기 때문에 결과는 동일하게 계산됩니다. Application.ScreenUpdating 만 제거하고 한단계씩 실험하면 금방 확인할 수 있습니다.


    • 2021-04-20 21:40

      제가 질문을 충분히 자세히 하지 않은 건가 하는 의문이 남습니다.

      프러시져의 코드가 아주 깁니다. 그 안에 무수히 많은 계산식이 단계마다 있습니다. 이럴 때 자동계산이 그때그때 이뤄지지 않고 맨 마지막 코드에서 Application.Calculation = xlCalculationAutomatic이 실행될 때 한 꺼번에 계산된다는 게 가능한가요?

      계산1이 계산2에 영향을 주고 또 다른 코드가 진행되고 계산2의 결과값이 또 다른 계산3에 참조로 쓰이는 복잡한 계산단계에서도, 맨 마지막에 자동계산을 하면 한꺼번에 그 앞의 계산이 되는 건지 궁금합니다.


      • 2021-04-20 21:47

        수식으로 =SUM(A1:A3)이 입력되어 있으면 수식은 A1셀을 우선 참조합니다.

        그리고 A1셀에 =AVERAGE(B1:C3)이 있으면 다시 B1셀을 우선 참조합니다. 이런 식으로 수식이 각 단계별로 계산되기 때문에 한 번에 모두 계산되는거죠.

        엑셀 설정에서 계산방식을 수동으로 바꾸면 Manaul 계산과 동일한 상태가 되고, 그 상태에서 키보드 F9키를 누르면 시트가 다시 계산되는데 한번 시험해보세요.


        • 2021-04-21 00:02

          시험해봤는데 수동계산으로 할 경우 잘 못 된 값이 나옵니다.

          result = [A1] + [A2]
          If result > 1000 Then
          result = result * 10
          MsgBox result
          Else
          result = result * 100
          MsgBox result
          End If

          예를 들어 이렇게 간단한 if문이 있다고 가정했을 때, result의 값에 따라서 그 다음 조건값이 달라지고 다른 명령문이 실행됩니다. A1과 A2 셀에 숫자가 들어가 있다면(고정된 값이라면), 자동계산이든 수동계산이든 정상적인 계산이 됩니다.

          하지만 A1과 A2에 함수가 들어있다면(가변적인 값이라면), 함수가 업데이트 되지 않아 엉뚱한 결과값이 나옵니다. 함수가 사용된 프러시저에서는 함수 다음에 Calculate 명령을 추가해서 함수를 계산한 다음 그 이후 코드가 진행돼야 나중에 정확한 결과를 얻을 수 있습니다. 따라서 Application.Calculation = xlCalculationManual은 제한적으로 사용하는 것이 맞는 것 같습니다.


          • 2021-04-21 13:50

            명령문이 이렇게 작성되면 계산이 잘못될수도 있겠네요...

            명령문 인수를 시트의 계산된 값에서 받아오는 형태로 작성해본적은 없어 생각해보지 못했는데, 이렇게 코드가 작성되야 하는 상황이 따로 있을까요?

            시트에 아주 복잡한 산식이 많이 걸려있어 매크로로 작성하기 번거로운 상황을 제외하면 인수를 이렇게 받아오는 경우는 매우 드물 것 같은데, 궁금합니다.


  • 2021-04-21 11:42

    저 역시 Application.calculation 옵션에 따른 계산값 차이가 나는 것은 지금것 한번도 보지못했습니다.

    아무 문제 없이 잘 돌아가는 것 같습니다.


    • 2021-04-21 13:06

      속도개선 구문 사용에 있어서 일반화의 오류에 빠지지 않도록 조심해야 한다는 겁니다. 코드 시작과 끝에 Application.calculation 명령을 집어넣었을 때 문제가 생기는 경우가 있습니다. 제 파일에는 필터링과 vlookup함수를 많이 사용해야 하는 코드가 작성되어 있고 데이터 양이 매우매우 많아서 매크로 작동 시 속도문제가 발생합니다. 그래서 엑셀 리본메뉴에서 '수동계산모드'를 선택해서 사용할 때가 많습니다. 각각의 매크로 코드에도 Application.calculation을 집어넣어서 혹시라도 자동계산 상태였으면 수동계산 모드로 바꾸지만, 프러시저 진행 중간에 함수의 결과값이 업데이트 되어야 하는 경우에는 Calculate(엑셀에서 F9)를 한 줄씩 삽입합니다. 계산이 필요한 지점에서 갱신하지 않고 그 다음 코드로 진행하면 함수가 들어있는 참조의 값이 이전에 있던 값으로 남아있어서 나중에 결과값이 달라집니다.

      함수를 하나 만들고 수동계산모드에서 그 함수가 참조하는 셀의 값을 변경하면 함수의 결과값이 갱신되지 않고 이전 값 그대로 있고, F9를 누르든 자동계산모드로 변경을 해야 새로 갱신이 되는 걸 볼 수 있습니다. 이 증상이 매크로에서도 그대로 일어나기 때문에 Calculate(엑셀에서 F9)를 필요한 부분에 넣어줘야 정상적인 결과값이 나옵니다.

      속도개선 구문 중 Application.calculation은 작성한 프러시저의 코드에 따라서 결과값이 달라질 수 있고 그런 경우 Calculate 명령과 함께 사용해야 안전합니다.


  • 2021-04-23 00:04

    엑셀은 셀 값을 참조하는 셀의 목록을 자동 구성 (=셀 계산 종속성 트리)해서 처리합니다.
    이것을 dirty 라고 알고 있습니다.

    데이터가 많은 경우,
    제 경험도, 간헐적 오류가 발생하는 부분이 있었습니다. (아주 오래전 경험입니다.)

    사용자 정의함수에
    셀 계산 종속성 트리 셀의 값을 인자로 넘길 때 입니다.
    이때는 "나는나" 님이 이야기 하는 방식을 간소화 해서
    - range.dirty 로 재계산 영역을 한정하는 방법
    예) Application.Calculation = xlCalculationManual
    'do something
    Application.Range("A1:C2300").Dirty

    - 이것도 속도향상에 문제가 있어서,
    중간과정에서 임시 시트에 값복사하여 계산하는 방법을 사용했었습니다.


전체 2,955
번호 카테고리 제목 작성자 작성일 추천 조회
2943 문서서식
New 사용자 지정 정렬 첨부파일
wbyu**** | 20:49 | 추천 0 | 조회 6
wbyu**** 20:49 0 6
2942 함수/공식
New 동적범위 적용 질문
두부 | 18:33 | 추천 0 | 조회 10
두부 18:33 0 10
2941 함수/공식
New 가로방향으로 수집되는 값을 세로방향으로 나열하는 법 첨부파일 (1)
녹차라면 | 14:29 | 추천 0 | 조회 26
녹차라면 14:29 0 26
2940 대시보드
New 간트차트 상 병합된 셀의 필터링 &진척도 표시 관련 첨부파일
asuka | 08:46 | 추천 0 | 조회 21
asuka 08:46 0 21
2939
New 안녕하세요 ~ VBA 카카오톡 관련문의 입니다 (1)
호기윤 | 02:00 | 추천 0 | 조회 25
호기윤 02:00 0 25
2938 함수/공식
New 교차판매 (cross-selling)분석을 위해 어떻게 가공해야하는지 모르겠습니다 ㅠ
동엽 | 2021.05.13 | 추천 0 | 조회 34
동엽 2021.05.13 0 34
2937 함수/공식
New 엑셀 함수질문있습니다. 첨부파일 (1)
BackGom | 2021.05.13 | 추천 1 | 조회 39
BackGom 2021.05.13 1 39
2936 함수/공식
New 전체직원에서 중복되지 않은 값을 다른곳에 추출 ! 도와주세요 ㅠ 첨부파일 (2)
E | 2021.05.13 | 추천 0 | 조회 48
E 2021.05.13 0 48
2935 문서서식
New 데이터 정규화 도와주세요.... 첨부파일
닉뿡뿡 | 2021.05.13 | 추천 1 | 조회 48
닉뿡뿡 2021.05.13 1 48
2934 함수/공식
New 안녕하세요 이 함수식의 해석좀 부탁드려도 될까요 (2) 답변완료
Hynix | 2021.05.13 | 추천 0 | 조회 51
Hynix 2021.05.13 0 51
2933 문서서식
New 셀병합 세로로 병합된 범위 문제 (1)
엑셀고수왕소취 | 2021.05.13 | 추천 0 | 조회 37
엑셀고수왕소취 2021.05.13 0 37
2932 피벗테이블
New 피벗테이블 행 필드 순서변경 질문드립니다. 첨부파일 (1)
월마리아 | 2021.05.12 | 추천 0 | 조회 26
월마리아 2021.05.12 0 26
2931 VBA
New VBA 문제 관련 (VBA를 사용하여 사진 옮기기) 첨부파일 (7) 답변완료
철모대장 | 2021.05.12 | 추천 0 | 조회 57
철모대장 2021.05.12 0 57
2930 VBA
New 이건 VBA로 해결해야 할 것 같습니다. 첨부파일 (3) 답변완료
. | 2021.05.12 | 추천 1 | 조회 76
. 2021.05.12 1 76
2929 VBA
New 행방향 필터 문의드립니다 ( 가로필터) 첨부파일 (2)
김희주 | 2021.05.12 | 추천 0 | 조회 64
김희주 2021.05.12 0 64
2928 문서서식
New 도와주세요! 매크로 말고 서식 설정으로 숫자와 한글 같이 나오게 할 수 있나요? (2)
회계직원 | 2021.05.12 | 추천 0 | 조회 39
회계직원 2021.05.12 0 39
2927 차트/그래프
New 엑셀 간트차트 자동화 양식 (진행상황, 업무흐름 표시) 문의 (2) 답변완료
비엘 | 2021.05.12 | 추천 1 | 조회 68
비엘 2021.05.12 1 68
2926 함수/공식
New 날짜 일수별 사용현황 합계 분석이 가능할까요 (3)
쭈니준이 | 2021.05.12 | 추천 0 | 조회 47
쭈니준이 2021.05.12 0 47
2925 함수/공식
엑셀 수량 집계 함수 질문합니다. (1)
상길 | 2021.05.11 | 추천 1 | 조회 49
상길 2021.05.11 1 49
2924 함수/공식
다량의 데이터에서 특정값을 포함하는 행 추출방법 첨부파일 (1)
고래 | 2021.05.11 | 추천 0 | 조회 64
고래 2021.05.11 0 64
2923 함수/공식
엑셀 A라는 파일에 엑셀 1월, 2월,3월 각 파일을 가지고 A라는 파일에 매달 업데이트 하는 방법 도와주세요! (5)
jyk0077@hanmail.net | 2021.05.11 | 추천 2 | 조회 77
jyk0077@hanmail.net 2021.05.11 2 77
2922 VBA
이게 가능할까요 ? (2)
12394 | 2021.05.11 | 추천 0 | 조회 80
12394 2021.05.11 0 80
2921 VBA
패턴을 찾아 색칠해 주는 방법 문의 첨부파일 (4)
눈사람 | 2021.05.11 | 추천 0 | 조회 51
눈사람 2021.05.11 0 51
2920 파워쿼리/피벗
ximage 함수조정등으로 이미지링크외 기타 웹페이지캡쳐 다운
오재석 Diego | 2021.05.11 | 추천 1 | 조회 58
오재석 Diego 2021.05.11 1 58
2919 함수/공식
엑셀 다중조건을 만족하고 중복값 제거 후 고유값 개수 카운트 공식 문의 첨부파일 (1)
우와토넛 | 2021.05.10 | 추천 0 | 조회 64
우와토넛 2021.05.10 0 64
2918 VBA
자동 셀병합 함수 오류 (1)
꼬구마 | 2021.05.10 | 추천 0 | 조회 38
꼬구마 2021.05.10 0 38
2917 파워쿼리/피벗
엑셀로 카카오톡 대화내용을 읽어들여 올 수 있나요? (2) 답변완료
만법귀일 | 2021.05.10 | 추천 0 | 조회 100
만법귀일 2021.05.10 0 100
2916 함수/공식
추세 출력 공식이 알고 싶습니다. (3) 답변완료
index | 2021.05.10 | 추천 0 | 조회 54
index 2021.05.10 0 54
2915 함수/공식
이미지파일을 엑셀 셀에 불러오는 함수 알려주세요 첨부파일 (1) 답변완료
왕장 | 2021.05.09 | 추천 0 | 조회 66
왕장 2021.05.09 0 66
2914 기능/도구
맥북 엑셀 관련 (1)
naver_5f055a605ee7d | 2021.05.09 | 추천 1 | 조회 38
naver_5f055a605ee7d 2021.05.09 1 38