excel-vba
VBA 모범 사례

비고

항상 "Option Explicit"을 사용하십시오.

VBA 편집기 창에서 도구 메뉴에서 "옵션"을 선택하십시오.

그런 다음 "편집기"탭에서 "변수 선언 필요"가 선택되어 있는지 확인하십시오.

이 옵션을 선택하면 Option Explicit 이 모든 VBA 모듈의 맨 위에 자동으로 배치됩니다.

작은 메모 : 이것은 지금까지 열지 않은 모듈, 클래스 모듈 등에 해당됩니다. 따라서 "Require Variable Declaration"옵션을 활성화하기 전에 이미 Sheet1 코드를 살펴 본다면 Option Explicit 가 추가되지 않습니다!

Option Explicit 은 Dim 문과 같이 모든 변수를 사용하기 전에 정의해야합니다. Option Explicit 사용하지 않으면 인식 할 수없는 단어가 VBA 컴파일러에 의해 Variant 유형의 새 변수로 간주되어 인쇄 오류와 관련된 매우 어려운 버그가 발생합니다. Option Explicit 사용하면 알 수없는 단어가 있으면 컴파일 오류가 발생하여 문제가되는 행을 나타냅니다.

예 :

다음 코드를 실행하면 :

Sub Test()
  my_variable = 12
  MsgBox "My Variable is : " & myvariable
End Sub

다음 메시지가 표시됩니다.

당신은 서면으로 실수를 한 myvariable 대신 my_variable , 다음 메시지 상자가 비어있는 변수를 표시합니다. Option Explicit 을 사용하는 경우 문제를 나타내는 컴파일 오류 메시지가 표시되므로이 오류를 사용할 수 없습니다.

이제 올바른 선언을 추가하면

Sub Test()
  Dim my_variable As Integer
  my_variable = 12
  MsgBox "My Variable is : " & myvariable
End Sub

myvariable 의 오류를 정확하게 나타내는 오류 메시지가 표시 myvariable .

Option Explicit 및 배열에 대한 참고 사항 ( 동적 배열 선언 ) :

ReDim 문을 사용하여 프로 시저 내에서 암시 적으로 배열을 선언 할 수 있습니다.

• ReDim 문을 사용할 때 배열의 이름을 잘못 입력하지 않도록주의하십시오.

• Option Explicit 문이 모듈에 포함되어 있더라도 새 배열이 만들어집니다

  Dim arr() as Long

  ReDim ar() 'creates new array "ar" - "ReDim ar()" acts like "Dim ar()"

범위가 아닌 배열 작업

오피스 블로그 - Excel VBA 성능 코딩 우수 사례

가능한 한 Range 의 사용을 피함으로써 최상의 성능을 얻는 경우가 많습니다. 이 예제에서는 전체 Range 객체를 배열로 읽어 Range 배열의 각 숫자를 제곱 한 다음 다시 배열을 Range 로 반환합니다. Range 는 두 번만 액세스하지만 루프는 읽기 / 쓰기를 위해 20 번 액세스합니다.

Option Explicit
Sub WorkWithArrayExample()
    
Dim DataRange As Variant
Dim Irow As Long
Dim Icol As Integer
DataRange = ActiveSheet.Range("A1:A10").Value ' read all the values at once from the Excel grid, put into an array

For Irow = LBound(DataRange,1) To UBound(DataRange, 1) ' Get the number of rows.
  For Icol = LBound(DataRange,2) To UBound(DataRange, 2) ' Get the number of columns.
    DataRange(Irow, Icol) = DataRange(Irow, Icol) * DataRange(Irow, Icol) ' cell.value^2
  Next Icol
Next Irow
ActiveSheet.Range("A1:A10").Value = DataRange ' writes all the results back to the range at once
    
End Sub

Timed 예제에 대한 추가 정보와 정보는 Charles Williams의 효율적인 VBA UDF 작성 (1 부) 및 시리즈의 다른 기사에서 찾을 수 있습니다.

가능한 경우 VB 상수를 사용하십시오.

If MsgBox("Click OK") = vbOK Then

대신에 사용할 수 있습니다.

If MsgBox("Click OK") = 1 Then

가독성을 높이기 위해

개체 브라우저 를 사용하여 사용 가능한 VB 상수를 찾으십시오. 보기 → VB 편집기에서 개체 브라우저 또는 F2 를 누릅니다 .

검색 할 클래스 입력

사용 가능한 회원보기

설명 변수 이름 사용

코드의 설명적인 이름과 구조가 주석을 필요 없게 만듭니다.

Dim ductWidth  As Double
Dim ductHeight As Double
Dim ductArea   As Double

ductArea = ductWidth * ductHeight

~보다 낫다

Dim a, w, h

a = w * h

셀, 범위, 워크 시트 또는 통합 문서이든 관계없이 한 위치에서 다른 위치로 데이터를 복사 할 때 특히 유용합니다. 다음과 같은 이름을 사용하여 자신을 도와주십시오.

Dim myWB As Workbook
Dim srcWS As Worksheet
Dim destWS As Worksheet
Dim srcData As Range
Dim destData As Range

Set myWB = ActiveWorkbook
Set srcWS = myWB.Sheets("Sheet1")
Set destWS = myWB.Sheets("Sheet2")
Set srcData = srcWS.Range("A1:A10")
Set destData = destWS.Range("B11:B20")
destData = srcData

한 줄에 여러 변수를 선언하면 모든 변수에 대해 다음과 같이 유형을 지정해야합니다.

Dim ductWidth As Double, ductHeight As Double, ductArea As Double

다음은 마지막 변수 만 선언하고 첫 번째 변수는 Variant 남습니다.

Dim ductWidth, ductHeight, ductArea As Double

오류 처리

올바른 오류 처리 기능을 사용하면 최종 사용자가 VBA 런타임 오류를 볼 수 없으며 개발자가 오류를 쉽게 진단하고 수정할 수 있습니다.

VBA에서 세 가지 주요 오류 처리 방법이 있습니다. 두 가지 중 하나는 코드에서 특별히 요구하지 않는 한 분산 된 프로그램에 대해서는 피해야합니다.

On Error GoTo 0 'Avoid using

또는

On Error Resume Next 'Avoid using

사용 선호 :

On Error GoTo <line> 'Prefer using

오류 이동 0

코드에서 오류 처리가 설정되지 않은 경우 On Error GoTo 0 이 기본 오류 처리기입니다. 이 모드에서 런타임 오류는 일반적인 VBA 오류 메시지를 표시하여 코드를 끝내거나 debug 모드로 들어가 소스를 식별 할 수 있습니다. 코드를 작성하는 동안이 방법은 가장 간단하고 유용하지만 최종 사용자에게 배포되는 코드는 피해야합니다. 최종 사용자가 이해하기 어려운 방법이기 때문에이 방법이 항상 바람직하지 않습니다.

오류시 다음 재개시

On Error Resume Next 을 클릭하면 VBA는 오류 처리기가 변경 될 때까지 오류 호출 이후의 모든 행에 대해 런타임에 발생하는 오류를 무시합니다. 아주 특정한 경우에이 줄이 유용 할 수 있지만이 경우는 피해야합니다. 예를 들어, Excel 매크로에서 별도의 프로그램을 시작할 때 프로그램이 이미 열려 있는지 여부를 알 수없는 경우 On Error Resume Next 전화가 유용 할 수 있습니다.

'In this example, we open an instance of Powerpoint using the On Error Resume Next call
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide

'Open PPT if not running, otherwise select active instance
On Error Resume Next
Set PPApp = GetObject(, "PowerPoint.Application")
On Error GoTo ErrHandler
If PPApp Is Nothing Then
    'Open PowerPoint
    Set PPApp = CreateObject("PowerPoint.Application")
    PPApp.Visible = True
End If

On Error Resume Next 호출을 사용하지 않았고 Powerpoint 응용 프로그램이 열려 있지 않으면 GetObject 메서드가 오류를 발생시킵니다. 따라서 응용 프로그램의 두 인스턴스를 작성하지 않으려면 On Error Resume Next 가 필요했습니다.

참고 : 빨리 당신이 더 이상 필요로 즉시 오류 처리기를 재설정하는 것이 좋습니다 On Error Resume Next 전화를

오류 발생시 <line>

이 오류 처리 방법은 다른 사용자에게 배포 된 모든 코드에 권장됩니다. 이를 통해 프로그래머는 VBA가 지정된 행에 코드를 전송하여 오류를 처리하는 방법을 정확하게 제어 할 수 있습니다. 태그는 숫자 문자열을 포함한 모든 문자열로 채울 수 있으며 콜론이 뒤에 오는 해당 문자열에 코드를 보냅니다. 여러 오류 처리 블록은 On Error GoTo <line> 의 여러 호출을 사용하여 사용할 수 있습니다. 아래의 서브 루틴은 On Error GoTo <line> 호출의 구문을 보여줍니다.

참고 : Exit Sub 라인은 첫 번째 오류 처리기 위에 배치되고 오류가 호출 되지 않고 코드가 자연스럽게 블록으로 진행되지 않도록 모든 후속 오류 처리기 앞에 배치해야합니다. 따라서 코드 블록의 끝 부분에 오류 처리기를 배치하는 것이 기능 및 가독성을위한 최선의 방법입니다.

Sub YourMethodName()
    On Error GoTo errorHandler
    ' Insert code here
    On Error GoTo secondErrorHandler

    Exit Sub 'The exit sub line is essential, as the code will otherwise
             'continue running into the error handling block, likely causing an error

errorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
        VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
    Exit Sub

secondErrorHandler:
    If Err.Number = 424 Then 'Object not found error (purely for illustration)
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        Exit Sub
    Else
        MsgBox "Error " & Err.Number & ": " & Err.Desctription
        Application.ScreenUpdating = True
        Application.EnableEvents = True   
        Exit Sub
    End If      
    Exit Sub

End Sub

오류 처리 코드로 메소드를 종료 한 경우 다음을 정리하십시오.

• 부분적으로 완료된 부분은 모두 실행 취소하십시오.
• 파일 닫기
• 화면 초기화 재설정
• 계산 모드 재설정
• 이벤트 재설정
• 마우스 포인터 재설정
• End Sub 다음에 지속되는 객체 인스턴스에서 언로드 메서드 호출
• 상태 표시 줄 재설정

당신의 일을 문서화하십시오

특히 동적 워크로드를 코딩하는 경우 나중에 사용하기 위해 작업 내용을 문서화하는 것이 좋습니다. 좋은 설명은 왜 코드가 무엇을하고 있는지를 설명해야하며 코드가하는 것이 아닙니다.

Function Bonus(EmployeeTitle as String) as Double
    If EmployeeTitle = "Sales" Then
        Bonus = 0    'Sales representatives receive commission instead of a bonus
    Else
        Bonus = .10
    End If
End Function

코드가 자신이하는 일을 설명하기 위해 주석이 필요하다는 것을 모호하게 생각한다면 주석을 통해 설명하는 대신 코드를 더 명확하게 다시 작성하십시오. 예를 들어, 대신 :

Sub CopySalesNumbers
    Dim IncludeWeekends as Boolean
    
    'Boolean values can be evaluated as an integer, -1 for True, 0 for False.
    'This is used here to adjust the range from 5 to 7 rows if including weekends.
    Range("A1:A" & 5 - (IncludeWeekends * 2)).Copy
    Range("B1").PasteSpecial
End Sub

다음과 같이 쉽게 따르도록 코드를 명확히하십시오.

Sub CopySalesNumbers
    Dim IncludeWeekends as Boolean
    Dim DaysinWeek as Integer
    
    If IncludeWeekends Then
        DaysinWeek = 7
    Else
        DaysinWeek = 5
    End If
    Range("A1:A" & DaysinWeek).Copy
    Range("B1").PasteSpecial
End Sub 

매크로 실행 중 속성 끄기

조기 최적화 를 피하는 것은 모든 프로그래밍 언어에서 모범 사례입니다 . 그러나 테스트 결과 코드가 너무 느리게 실행되는 것으로 밝혀지면 응용 프로그램이 실행되는 동안 응용 프로그램의 일부 속성을 해제하여 속도를 향상시킬 수 있습니다. 다음 코드를 표준 모듈에 추가하십시오.

Public Sub SpeedUp( _
    SpeedUpOn As Boolean, _
    Optional xlCalc as XlCalculation = xlCalculationAutomatic _
)
    With Application
        If SpeedUpOn Then
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
            .EnableEvents = False
            .DisplayStatusBar = False 'in case you are not showing any messages
            ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting
        Else
            .ScreenUpdating = True
            .Calculation = xlCalc
            .EnableEvents = True
            .DisplayStatusBar = True
            ActiveSheet.DisplayPageBreaks = True
        End If
    End With
End Sub

Office 블로그 에 대한 추가 정보 - Excel VBA 성능 코딩 우수 사례

그리고 매크로의 시작과 끝에서 호출하십시오.

Public Sub SomeMacro
    'store the initial "calculation" state
    Dim xlCalc As XlCalculation
    xlCalc = Application.Calculation

    SpeedUp True

    'code here ...

    'by giving the second argument the initial "calculation" state is restored
    'otherwise it is set to 'xlCalculationAutomatic'
    SpeedUp False, xlCalc
End Sub

이러한 것들이 일반적인 Public Sub 프로 시저의 "향상된 기능"으로 대부분 간주 될 수 있지만 Application.EnableEvents = False 이벤트 처리를 비활성화하면 하나 이상의 워크 시트에서 값을 변경하는 Worksheet_Change 및 Workbook_SheetChange 개인 이벤트 매크로의 필수 항목으로 간주해야합니다. 이벤트 트리거를 사용하지 않으면 값이 변경되면 이벤트 매크로가 재귀 적으로 실행되어 "고정"통합 문서가 될 수 있습니다. '안전 종료'오류 처리기를 통해 이벤트 매크로를 종료하기 전에 이벤트를 다시 켜야합니다.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        
        'code that may change a value on the worksheet goes here
        
    End If
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

주의 사항 : 이 설정을 사용하지 않도록 설정하면 런타임이 향상되므로 응용 프로그램 디버깅이 훨씬 어려워 질 수 있습니다. 코드가 제대로 작동 하지 않으면 문제를 파악할 때까지 SpeedUp True 호출을 주석으로 처리하십시오.

xlCalculationManual 에서 통합 문서를 계산하지 xlCalculationManual 때문에 워크 시트의 셀에 쓰고 워크 시트 함수에서 계산 된 결과를 다시 읽는 경우 특히 중요합니다. SpeedUp 하지 않고이 문제를 해결하려면 Application.Calculate 를 포함시켜 특정 지점에서 계산을 실행하는 것이 좋습니다.

참고 : 이들은 Application 자체의 속성이므로 매크로가 종료되기 전에 Application 이 다시 활성화되어 있는지 확인해야합니다. 따라서 오류 처리기를 사용하고 여러 끝점 (즉 End 또는 Unload Me )을 피하는 것이 특히 중요합니다.

오류 처리 :

Public Sub SomeMacro()
    'store the initial "calculation" state
    Dim xlCalc As XlCalculation
    xlCalc = Application.Calculation

    On Error GoTo Handler
    SpeedUp True
    
    'code here ...
    i = 1 / 0
CleanExit:
    SpeedUp False, xlCalc
    Exit Sub
Handler:
    'handle error
    Resume CleanExit
End Sub

Excel에서 ActiveCell 또는 ActiveSheet를 사용하지 마십시오.

어떤 이유로 든 코드가 잘못된 위치에서 실행되면 ActiveCell 또는 ActiveSheet 사용하면 실수의 근원이 될 수 있습니다.

ActiveCell.Value = "Hello" 
'will place "Hello" in the cell that is currently selected
Cells(1, 1).Value = "Hello" 
'will always place "Hello" in A1 of the currently selected sheet

ActiveSheet.Cells(1, 1).Value = "Hello" 
'will place "Hello" in A1 of the currently selected sheet
Sheets("MySheetName").Cells(1, 1).Value = "Hello" 
'will always place "Hello" in A1 of the sheet named "MySheetName"

• Active* 를 사용하면 사용자가 지루하고 다른 워크 시트를 클릭하거나 다른 통합 문서를 열면 장기 실행 매크로에서 문제가 발생할 수 있습니다.
• 코드가 열리거나 다른 통합 문서를 만드는 경우 문제가 발생할 수 있습니다.
• 코드에서 Sheets("MyOtherSheet").Select 사용하면 문제가 발생할 수 있습니다 Sheets("MyOtherSheet").Select 하고 시트를 읽거나 쓰기 전에 시트를 잊었습니다.

워크 시트를 사용하지 마십시오.

모든 작업이 단일 워크 시트로 진행되는 경우에도 코드에서 워크 시트를 명시 적으로 지정하는 것이 좋습니다. 이 습관은 나중에 코드를 확장하거나 Sub 또는 Function 의 일부 또는 전부를 다른 곳에서 재사용하는 것이 훨씬 더 쉽습니다. 많은 개발자는 코드에서 워크 시트에 대해 동일한 지역 변수 이름을 사용하는 습관을 수립하여 해당 코드의 재사용을 훨씬 수월하게 만듭니다.

예를 들어, 다음 코드는 모호합니다. - 개발자가 다른 워크 시트를 활성화하거나 변경하지 않는 한 :

Option Explicit
Sub ShowTheTime()
    '--- displays the current time and date in cell A1 on the worksheet
    Cells(1, 1).Value = Now()  ' don't refer to Cells without a sheet reference!
End Sub

Sheet1 이 활성화되어 있으면 Sheet1 A1 셀에 현재 날짜와 시간이 채워집니다. 그러나 사용자가 어떤 이유로 든 워크 시트를 변경하면 워크 시트가 현재 활성화되어있는 코드가 코드에 업데이트됩니다. 대상 워크 시트가 모호합니다.

가장 좋은 방법은 코드에서 참조하는 워크 시트를 항상 식별하는 것입니다.

Option Explicit
Sub ShowTheTime()
    '--- displays the current time and date in cell A1 on the worksheet
    Dim myWB As Workbook
    Set myWB = ThisWorkbook
    Dim timestampSH As Worksheet
    Set timestampSH = myWB.Sheets("Sheet1")
    timestampSH.Cells(1, 1).Value = Now()
End Sub

위의 코드는 통합 문서와 워크 시트를 모두 식별하는 데 명확합니다. 잔인한 것처럼 보일 수도 있지만, 목표 참조에 관한 좋은 습관을 창출하면 미래의 문제에서 벗어날 수 있습니다.

SELECT 또는 ACTIVATE 사용하지 않기

코드에서 Select 또는 Activate 를 사용하는 것은 매우 드물지만 일부 Excel 메서드는 워크 시트 나 통합 문서를 활성화해야 예상대로 작동합니다.

VBA를 배우기 시작하면 매크로 레코더를 사용하여 작업을 기록한 다음 코드를 살펴 보는 것이 좋습니다. 예를 들어, Sheet2의 D3 셀에 값을 입력하기 위해 수행 한 작업을 기록했으며 매크로 코드는 다음과 같습니다.

Option Explicit
Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Sheet2").Select
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "3.1415"   '(see **note below)
    Range("D4").Select
End Sub

하지만 매크로 기록자는 각각의 (사용자) 작업에 대한 코드 줄을 생성한다는 것을 기억하십시오. Sheets("Sheet2").Select Sheet2)를 선택하려면 워크 시트 탭을 클릭하고 선택 ( Sheets("Sheet2").Select ))을 클릭하고 값 ( Range("D3").Select 입력하기 전에 D3 셀을 클릭하고 Range("D3").Select )을 Range("D3").Select 하고 Enter 키를 사용하여 현재 선택된 셀 아래의 셀을 선택 : Range("D4").Select ).

여러 가지 문제가 있습니다. 여기를 .Select 하십시오 :

• 워크 시트가 항상 지정되지는 않습니다. 이것은 녹음하는 동안 워크 시트를 전환하지 않으면 코드가 다른 활성 워크 시트에 대해 다른 결과를 산출 함을 의미합니다.
• .Select() 가 느립니다. Application.ScreenUpdating 이 False 로 설정되어 있더라도 처리해야하는 불필요한 작업입니다.
• .Select() 는 다루기 힘듭니다. Application.ScreenUpdating 이 True 되면 Excel은 실제로 작업중인 셀, 워크 시트 및 양식을 선택합니다. 이것은 눈에 스트레스를주고보기에는 정말로 불쾌합니다.
• .Select() 는 리스너를 트리거합니다. 이것은 조금 더 진보 된 것이지만, 작업하지 않으면 Worksheet_SelectionChange() 와 같은 함수가 트리거됩니다.

VBA로 코딩 할 때, 모든 "타이핑"동작 (예 : Select 문)은 더 이상 필요하지 않습니다. 셀에 값을 넣으려면 코드가 단일 명령문으로 축소 될 수 있습니다.

'--- GOOD
ActiveWorkbook.Sheets("Sheet2").Range("D3").Value = 3.1415

'--- BETTER
Dim myWB      As Workbook
Dim myWS      As Worksheet
Dim myCell    As Range

Set myWB = ThisWorkbook             '*** see NOTE2
Set myWS = myWB.Sheets("Sheet2")
Set myCell = myWS.Range("D3")

myCell.Value = 3.1415

(위의 BETTER 예제는 중간 변수를 사용하여 셀 참조의 다른 부분을 구분하는 것을 보여줍니다 .GOOD 예제는 항상 정상적으로 작동하지만 더 긴 코드 모듈에서는 매우 복잡 할 수 있으며 참조 중 하나가 잘못 입력되면 디버그하기가 더 어려울 수 있습니다. )

** 참고 : 매크로 레코더는 입력하려는 데이터 유형에 대해 많은 가정을합니다.이 경우 문자열 값을 수식으로 입력하여 값을 만듭니다. 코드는이 작업을 수행 할 필요가 없으며 위의 그림과 같이 숫자 값을 셀에 직접 할당 할 수 있습니다.

** NOTE2 : 권장되는 방법은 ActiveWorkbook 대신 로컬 통합 문서 변수를 ThisWorkbook 것입니다 (명시 적으로 필요하지 않은 경우). 그 이유는 매크로가 일반적으로 VBA 코드가 생성 한 통합 문서에서 리소스를 필요로하거나 사용할 것이고 다른 통합 문서와 작업하도록 코드를 명시 적으로 지정하지 않으면 해당 통합 문서 외부에서 보지 않을 것입니다. Excel에서 여러 통합 문서를 열면 ActiveWorkbook 은 VBA 편집기에서 볼 수있는 통합 문서와 다른 포커스 가있는 통합 문서 입니다. 그래서 당신이 실제로 다른 것을 참조 할 때 하나의 워크 북에서 실행하고 있다고 생각합니다. ThisWorkbook 코드가 실행되는 것을 포함하는 통합을 의미한다.

모든 통합 문서 및 시트에 대한 참조를 항상 정의하고 설정하십시오.

각각 여러 개의 시트가있는 여러 개의 열린 통합 문서로 작업 할 때 모든 통합 문서 및 시트에 대한 참조를 정의하고 설정하는 것이 가장 안전합니다.

ActiveWorkbook 또는 ActiveSheet 는 사용자가 변경할 수 있으므로 의존하지 마십시오 .

다음 코드 예제에서는 " Data.xlsx "통합 문서의 " Raw_Data "시트에서 " Results.xlsx "통합 문서의 " Refined_Data "시트로 범위를 복사하는 방법을 보여줍니다.

이 절차에서는 Select 메서드를 사용하지 않고 복사하고 붙여 넣는 방법도 보여줍니다.

Option Explicit

Sub CopyRanges_BetweenShts()

    
    Dim wbSrc                           As Workbook
    Dim wbDest                          As Workbook
    Dim shtCopy                         As Worksheet
    Dim shtPaste                        As Worksheet
    
    ' set reference to all workbooks by name, don't rely on ActiveWorkbook
    Set wbSrc = Workbooks("Data.xlsx")
    Set wbDest = Workbooks("Results.xlsx")
    
    ' set reference to all sheets by name, don't rely on ActiveSheet
    Set shtCopy = wbSrc.Sheet1 '// "Raw_Data" sheet
    Set shtPaste = wbDest.Sheet2 '// "Refined_Data") sheet
    
    ' copy range from "Data" workbook to "Results" workbook without using Select
    shtCopy.Range("A1:C10").Copy _
    Destination:=shtPaste.Range("A1")

End Sub

WorksheetFunction 개체가 UDF에 상응하는 것보다 빠르게 실행됩니다.

VBA는 런타임에 컴파일되므로 성능에 큰 부정적인 영향을 미치며 기본 제공되는 모든 기능이 더 빨라지고이를 사용하려고합니다.

예를 들어 SUM과 COUNTIF 함수를 비교하고 있지만 WorkSheetFunctions로 해결할 수있는 것이 있으면 if를 사용할 수 있습니다.

그 (것)들을위한 첫번째 시도는 범위를 통해서 반복하고 세포 (범위를 사용하는)에 의하여 세포 그것을 가공하기위한 것입니다 :

Sub UseRange()
    Dim rng as Range
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    Total = 0
    CountLessThan01 = 0
    For Each rng in Sheets(1).Range("A1:A100")
        Total = Total + rng.Value2
        If rng.Value < 0.1 Then
            CountLessThan01 = CountLessThan01 + 1
        End If
    Next rng
    Debug.Print Total & ", " & CountLessThan01
End Sub

한 가지 개선 사항은 범위 값을 배열에 저장하고 다음을 처리하는 것입니다.

Sub UseArray()
    Dim DataToSummarize As Variant
    Dim i As Long
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    DataToSummarize = Sheets(1).Range("A1:A100").Value2 'faster than .Value
    Total = 0
    CountLessThan01 = 0
    For i = 1 To 100
        Total = Total + DataToSummarize(i, 1)
        If DataToSummarize(i, 1) < 0.1 Then
            CountLessThan01 = CountLessThan01 + 1
        End If
    Next i
    Debug.Print Total & ", " & CountLessThan01
End Sub

그러나 어떤 루프를 작성하는 대신 간단한 수식을 실행하는 데 매우 편리한 Application.Worksheetfunction 을 사용할 수 있습니다.

Sub UseWorksheetFunction()
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    With Application.WorksheetFunction
        Total = .Sum(Sheets(1).Range("A1:A100"))
        CountLessThan01 = .CountIf(Sheets(1).Range("A1:A100"), "<0.1")
    End With
    
    Debug.Print Total & ", " & CountLessThan01
End Sub

또는 더 복잡한 계산을 위해 Application.Evaluate 를 사용할 수도 있습니다.

Sub UseEvaluate()
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    With Application
        Total = .Evaluate("SUM(" & Sheet1.Range("A1:A100").Address( _
            external:=True) & ")")
        CountLessThan01 = .Evaluate("COUNTIF('Sheet1'!A1:A100,""<0.1"")")
    End With
    
    Debug.Print Total & ", " & CountLessThan01
End Sub

그리고 마지막으로 Sub 25,000 회 이상을 실행하는 경우 여기에 평균 (5 회) 밀리 초 단위의 시간이 있습니다 (물론 각 PC마다 다를 수 있지만 서로 비교해 보면 유사하게 작동합니다).

1. UseWorksheetFunction : 2156ms
2. 사용 배열 : 2219 ms (+ 3 %)
3. UseEvaluate : 4693 ms (+ 118 %)
4. 사용 범위 : 6530ms (+ 203 %)

속성 또는 메소드의 이름을 변수로 다시 사용하지 마십시오.

프로퍼티 또는 메소드의 예약 된 이름을 자신의 프로 시저 및 변수의 이름으로 다시 사용하는 것이 일반적으로 '우수 사례'로 간주되지 않습니다.

나쁜 형식 - 다음은 (엄격하게 말하면) 합법적 인 작업 코드이지만 행 , 열 및 주소 속성뿐 아니라 Find 메서드를 다시 사용하면 이름 모호성과 관련된 문제 / 충돌이 발생할 수 있으며 일반적으로 일반적으로 혼란 스럽습니다.

Option Explicit

Sub find()
    Dim row As Long, column As Long
    Dim find As String, address As Range
    
    find = "something"
    
    With ThisWorkbook.Worksheets("Sheet1").Cells
        Set address = .SpecialCells(xlCellTypeLastCell)
        row = .find(what:=find, after:=address).row        '< note .row not capitalized
        column = .find(what:=find, after:=address).column  '< note .column not capitalized
        
        Debug.Print "The first 'something' is in " & .Cells(row, column).address(0, 0)
    End With
End Sub

Good Form - 모든 예약어가 원본과 비슷하지만 고유 한 근사치로 바뀌면 잠재적 인 명명 충돌이 발생하지 않습니다.

Option Explicit

Sub myFind()
    Dim rw As Long, col As Long
    Dim wht As String, lastCell As Range
    
    wht = "something"
    
    With ThisWorkbook.Worksheets("Sheet1").Cells
        Set lastCell = .SpecialCells(xlCellTypeLastCell)
        rw = .Find(What:=wht, After:=lastCell).Row         '◄ note .Find and .Row
        col = .Find(What:=wht, After:=lastCell).Column     '◄ .Find and .Column
        
        Debug.Print "The first 'something' is in " & .Cells(rw, col).Address(0, 0)
    End With
End Sub

의도적으로 표준 방법이나 속성을 자신의 사양으로 다시 작성하려는 경우가있을 수 있지만 이러한 상황은 거의없고 그 사이에 있습니다. 대부분의 경우, 소유 구조의 예약 된 이름을 다시 사용하지 마십시오.