VBA 속도 개선 (배열, 화면업데이트 중지 등 반영 후)

VBA
작성자
코인으로대동단결
작성일
2022-10-25 14:45
조회
117
엑셀버전 : 엑셀2010 이전

운영체제 : 윈도우10

질문 요약 : 인터넷에 돌아다니는 여러 정보들을 이용해서 속도를 많이 개선시켰으나.. 더 빨라질 수 있을 것 같은데 어떤 부분이 문제로 작용하는지를 모르겠습니다.

VBA는 i = 1 to 1000 으로 i를 바꿔가면서 나오는 결과값들을 배열함수 Temp에 저장하고 마지막에 워크시트에 붙이는 VBA입니다.

근데.. 속도가 영 느려서.. 워크시트들에 있는 수식들이 문제인지.. 아니면 VBA로 어떻게 더 빠르게 할 수 있는 방법이 있는지 문의드립니다. (배열함수 부분을 어떻게 바꾸면 더 빨라질 것 같은데 떠오르지가 않습니다..)

 

(Application.Screenupdating, EnableEvents. Calcuation 들도 다 반영을 했는데.. 배열함수도 쓰고.. 흡..)

아래는 VBA 본문입니다! (엑셀파일은 4메가 정도인데 크기가 크다고 올라가지 않네요 .. )

Sub Cal_TVOG_sto()

Dim i As Integer

Dim BEL As Range
Dim result_sto As Range
Dim indata As Range
Dim outdata As Range
Dim MP As Range
Dim SNno As Range
Dim Temp() As Variant

Set BEL = Range("BEL")
Set indata = Range("indata")
Set outdata = Range("outdata")
Set result_sto = Range("result_sto")
Set MP = Range("MP")
Set SNno = Range("SNno")

result_sto.Resize(1000, MP).ClearContents

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Sheets("result_sto").Range("B4") = Time()

For i = 1 To MP 'MP

ReDim Preserve Temp(1 To 1000, 1 To i)

outdata = Application.Transpose(indata)

For j = 1 To 1000 'SN

SNno.Value = j
Application.Calculate
Temp(j, i) = BEL
Application.StatusBar = "MP " & i & " of " & MP & " and SN " & j & " of " & 1000
Application.SendKeys "{numlock}"

Next j

Set indata = indata.Offset(1, 0)

Next i

result_sto.Resize(UBound(Temp, 1), UBound(Temp, 2)) = Temp
Sheets("result_sto").Range("C4") = Time()

Erase Temp
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True

ActiveWorkbook.Save

End Sub

스크랩
공유
전체 6

  • 2022-10-25 15:23

    @코인으로대동단결 님 몇가지 부분적으로 답변드리면

    ReDim Preserve Temp(1 To 1000, 1 To i)  <- Preserve는 속도가 느리기 때문에 누적하는 배열이 아니라면 Redim 사용권장

    SNno.Value = j
    Application.Calculate
    Temp(j, i) = BEL
    Application.StatusBar = "MP " & i & " of " & MP & " and SN " & j & " of " & 1000
    Application.SendKeys "{numlock}"

    작성하신 코드는 pc에서 일어나는 작업을 물리적으로 자동화하는 방식이기 때문에 느릴 수 밖에 없습니다..

    이미 배열을 잘 다루시는 듯 하니 Application.Calculate, Application.SendKeys "{numlock}" 이런 물리동작을 줄이고 언어 안에서 자동화를 구현하면 속도가 빨라질겁니다. 아마도 시트안에서 계산된 결과를 받아와야해서 calculate 를 하셨을텐데.. calculate를 하면 앞에 설정한

    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True

    이 설정은 큰 의미가 없게됩니다.


    • 2022-10-25 17:26

      @고양이냐옹 님 답변 감사드립니다!
      추가로 궁금한 것들이 있는데..
      1) preserve를 쓴건 처음부터 redim으로 크게 잡아버리면 메모리를 많이 잡아먹어 느려진다는 소리를 들어서 바꾼거였는데 다시 redim으로 해볼게요 !!
      2) 물리동작을 줄이고 언어 안에서 자동화를 구현하라는 말씀은.. 시트에서 Application.Calculate를 통해 계산한것을 가져오는 식이 아닌, VBA에 계산식을 넣으라는 말씀이신거죠 ??!! 시트에서 계산식이 꽤나 복잡한데 .. 어떻게 넣어야할지 고민해봐야겠네요 !!
      3) 아 numlock을 넣은건 화면보호기가 켜지면 vba가 멈추더라구요 ...? 그래서 화면보호기가 안켜지게끔 넣은거였습니다.. ㅋㅋ

      감사합니다!


      • 2022-10-28 16:55

        @코인으로대동단결 님 preserve는 예를 들어 레코드 100행짜리 데이터가 있고, 100행 레코드를 유지한 상태로 데이터를 누적관리할 때 사용하면 좋습니다.

        근데 누적관리할 경우에는 array 보다 vba에는 collection 이나 dictionary 를 사용하는게 더 효과적입니다


  • 2022-10-25 15:26

    @코인으로대동단결 님  

    For i = 1 To MP 'MP
    
    ReDim Preserve Temp(1 To 1000, 1 To i)
    
    ==>
    
    ReDim Temp(1 To 1000, 1 To MP)
    For i = 1 To MP 'MP
    
    로 해보세요
    
    
    

     


    • 2022-10-25 17:27

      @마법의손 님 넵 Preserve 쓰지 않는 방식으로 적용해보겠습니다! 감사합니다 !


      • 2022-10-25 23:25

        @코인으로대동단결 님 두줄의 순서를 바꿔보세요


전체 8,512
번호 카테고리 제목 작성자 작성일 추천 조회
알림
「🎉 올해의 책 투표」 '진짜쓰는 실무엑셀' 이 후보에 선정되었습니다! (👉깜짝 이벤트) (51)
오빠두엑셀 | 2022.11.07 | 추천 23 | 조회 7954
오빠두엑셀 2022.11.07 23 7954
공지사항 함수/공식
⭐ [더 나은 커뮤니티 문화를 위한 Q&A 글 작성 규칙] ⭐ (105)
오빠두엑셀 | 2021.10.28 | 추천 135 | 조회 8511
오빠두엑셀 2021.10.28 135 8511
44967 함수/공식
New 중복값 제가하는 방법 첨부파일
tndka**** | 01:29 | 추천 0 | 조회 15
tndka**** 01:29 - 15
44964 기능/도구
New 엑셀에서 새 인스턴스(instance)로 실행되는 이유가 궁금합니다. 첨부파일
월마리아 | 01:16 | 추천 0 | 조회 16
월마리아 01:16 - 16
44963 VBA
New 일정간격마다 모두재계산&파일 열자마자 매크로 실행
KangU | 2022.11.28 | 추천 0 | 조회 19
KangU 2022.11.28 - 19
44962 함수/공식
New 누적 합계 구하기 함수 관련 엑셀파일
짱구 | 2022.11.28 | 추천 0 | 조회 18
짱구 2022.11.28 - 18
44959 함수/공식
New 안녕하세요! 엑셀 뉴비, 고수분들께 도움을 구하고자 합니다 ㅠㅠ 엑셀파일첨부파일 (3)
U_ho | 2022.11.28 | 추천 0 | 조회 22
U_ho 2022.11.28 - 22
44956 함수/공식
New 고수님들 도와주세요 (1)
LUKE. | 2022.11.28 | 추천 0 | 조회 19
LUKE. 2022.11.28 - 19
44955 함수/공식
New 따로 수정이나 새로고침 하지않아도 실시간으로 계산값이 변경되는 날짜계산기 (2) 답변완료
KangU | 2022.11.28 | 추천 0 | 조회 28
KangU 2022.11.28 - 28
44953 함수/공식
New 엑셀 날짜 함수 사용시 날짜 표시 안되는 이슈 첨부파일 (2)
junee562 | 2022.11.28 | 추천 0 | 조회 28
junee562 2022.11.28 - 28
44950 함수/공식
New vlookup을 활용한 여러 조건 시 결과값을 불러오고 싶습니다. 엑셀파일 (1)
DBOX | 2022.11.28 | 추천 0 | 조회 32
DBOX 2022.11.28 - 32
44946 함수/공식
New 정산금액을 공문에 참조하려고 하는데 n/a가 뜹니다. 엑셀파일 (3)
야근쟁이 | 2022.11.28 | 추천 0 | 조회 25
야근쟁이 2022.11.28 - 25
44941 함수/공식
New COUNT 함수 관련 2가지 조건의 수를 구하고 싶습니다. 첨부파일 (2) 답변완료
2039290jf1 | 2022.11.28 | 추천 0 | 조회 27
2039290jf1 2022.11.28 - 27
44940 VBA
New 엑셀 매크로 AutoFill 관련 문의 (1)
원더우먼 | 2022.11.28 | 추천 0 | 조회 34
원더우먼 2022.11.28 - 34
44938 기능/도구
New 온라인그림 선택이 안되요. 첨부파일 (1)
굿걸♡ | 2022.11.28 | 추천 0 | 조회 22
굿걸♡ 2022.11.28 - 22
44937 함수/공식
New 다중조건 if 관련 문의 (2) 답변완료
지도현 | 2022.11.28 | 추천 0 | 조회 39
지도현 2022.11.28 - 39
44935 함수/공식
New 엑셀 2007,2010 오프라인 도움말파일 구해요
나야 | 2022.11.28 | 추천 0 | 조회 19
나야 2022.11.28 - 19
44932 VBA
New VBA FIND를 통해서 일치하는 값 찾을때까지 검색 후 만약에 없으면 해당셀에 찾는값 없음 넣기 (1)
고바마 | 2022.11.28 | 추천 0 | 조회 35
고바마 2022.11.28 - 35
44930 VBA
New vba를 이용하여 수식 결과값 추출 시트를 만들고 싶습니다. 첨부파일 (1)
코모 | 2022.11.28 | 추천 0 | 조회 44
코모 2022.11.28 - 44
44929 함수/공식
New [관심]!!경우의수 적용하여, 값 판단법 좀 알려주세요..ㅜ 엑셀파일첨부파일 (5)
ak스타 | 2022.11.28 | 추천 0 | 조회 28
ak스타 2022.11.28 - 28
44927 함수/공식
New 다중 sumifs 기타 값 계산 엑셀파일첨부파일 (3) 답변완료
선크 | 2022.11.28 | 추천 0 | 조회 34
선크 2022.11.28 - 34
44915 VBA
New 괄호안의 숫자 합계 구하기 VBA 또는 수식 엑셀파일첨부파일 (4) 답변완료
007 | 2022.11.27 | 추천 0 | 조회 62
007 2022.11.27 - 62
44913 함수/공식
New 함수 결과값이 이상합니다 첨부파일 (1)
엄기용 | 2022.11.27 | 추천 0 | 조회 31
엄기용 2022.11.27 - 31
44907 함수/공식
New 시간만 구하는 방법.. 첨부파일 (4) 답변완료
candle0723 | 2022.11.27 | 추천 0 | 조회 39
candle0723 2022.11.27 - 39
44904 문서서식
New 표시형식 문의 드립니다 (1)
거꾸로문 | 2022.11.26 | 추천 0 | 조회 30
거꾸로문 2022.11.26 - 30
44901 VBA
New 안녕하세요. vba로 단위 변환 질문드립니다.
Jes | 2022.11.26 | 추천 0 | 조회 38
Jes 2022.11.26 - 38
44899 차트/그래프
New 슬라이서 서식을 변경 한 후, 다른 엑셀 파일에서도 사용할 수 있는 방법이 있을까요?
twou**** | 2022.11.26 | 추천 0 | 조회 27
twou**** 2022.11.26 - 27
44896 VBA
New 여러 문자들 중 참조 문자에 있는 값 찾기 vba 엑셀파일첨부파일 (4)
007 | 2022.11.26 | 추천 0 | 조회 61
007 2022.11.26 - 61
44892 기능/도구
New 필터랑 행 숨기기 된 상태에서 자동채우기 안 되나요?
오리파 | 2022.11.25 | 추천 0 | 조회 25
오리파 2022.11.25 - 25
44887 함수/공식
New 다중조건을 만족하는 여러개의 값 구하는 함수식 문의드립니다. (1)
함수시르미 | 2022.11.25 | 추천 0 | 조회 53
함수시르미 2022.11.25 - 53
44886 VBA
New 엑세스 및 엑셀 연동 VBA INSERT구문 질문 엑셀파일
H.B | 2022.11.25 | 추천 0 | 조회 35
H.B 2022.11.25 - 35
44885 함수/공식
New 엑셀로 전투 시뮬레이션을 만들 수 있을까요? (4)
네버 | 2022.11.25 | 추천 0 | 조회 61
네버 2022.11.25 - 61