[excel] Excel VBA에서 Select를 사용하지 않는 방법

Program / 글쓴이

.SelectExcel VBA에서 이해할 수있는 오해에 대해 많이 들었지만 사용 하지 않는 방법에 대해서는 확실하지 않습니다. Select함수 대신 변수를 사용할 수 있으면 코드를 더 재사용 할 수 있다는 것을 알게되었습니다 . 그러나를 사용하지 않으면 (예 : ActiveCell등) 물건을 참조하는 방법을 잘 모르겠습니다 Select.

내가 발견 한 범위에서이 문서 와 선택 사용하지 않는 이점에이 예제 하지만 아무것도 찾을 수 없습니다 어떻게 ?

답변

선택을 피하는 방법의 몇 가지 예

사용 Dim‘D 변수

Dim rng as Range

Set변수를 필요한 범위로 단일 셀 범위를 참조하는 방법에는 여러 가지가 있습니다

Set rng = Range("A1")
Set rng = Cells(1,1)
Set rng = Range("NamedRange")

또는 다중 셀 범위

Set rng = Range("A1:B10")
Set rng = Range("A1", "B10")
Set rng = Range(Cells(1,1), Cells(10,2))
Set rng = Range("AnotherNamedRange")
Set rng = Range("A1").Resize(10,2)

당신은 할 수 있습니다 받는 바로 가기를 사용하는 Evaluate방법,하지만이 덜 효율적이며 일반적으로 생산 코드에 피해야한다.

Set rng = [A1]
Set rng = [A1:B10]

위의 모든 예는 활성 시트의 셀을 나타 냅니다. 활성 시트로만 작업하고 싶지 않다면 Worksheet변수 를 흐리게 처리하는 것이 좋습니다

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Set rng = ws.Cells(1,1)
With ws
    Set rng = .Range(.Cells(1,1), .Cells(2,10))
End With

당신이 경우 않는 작업하려는 ActiveSheet, 명확하게 그것을 명시하는 것이 가장 좋습니다. 그러나 일부 Worksheet방법은 활성 시트를 변경하므로 주의 하십시오.

Set rng = ActiveSheet.Range("A1")

다시 이것은 활성 통합 문서를 나타냅니다 . ActiveWorkbook또는 로만 작업하고 싶지 않으면 변수 ThisWorkbook를 흐리게 처리하는 것이 좋습니다 Workbook.

Dim wb As Workbook
Set wb = Application.Workbooks("Book1")
Set rng = wb.Worksheets("Sheet1").Range("A1")

당신이 경우 않는 작업하려는 ActiveWorkbook, 명확하게 그것을 명시하는 것이 가장 좋습니다. 그러나 많은 WorkBook방법이 활성화 된 책을 변경하므로 주의 하십시오.

Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("A1")

ThisWorkbook객체를 사용 하여 실행 코드가 포함 된 책을 참조 할 수도 있습니다 . 

Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1")

일반적인 (나쁜) 코드는 책을 열고 데이터를 얻은 다음 다시 닫는 것입니다.

이것은 나쁘다 :

Sub foo()
    Dim v as Variant
    Workbooks("Book1.xlsx").Sheets(1).Range("A1").Clear
    Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
    v = ActiveWorkbook.Sheets(1).Range("A1").Value
    Workbooks("SomeAlreadyOpenBook.xlsx").Activate
    ActiveWorkbook.Sheets("SomeSheet").Range("A1").Value = v
    Workbooks(2).Activate
    ActiveWorkbook.Close()
End Sub

그리고 더 좋을 것입니다 :

Sub foo()
    Dim v as Variant
    Dim wb1 as Workbook
    Dim  wb2 as Workbook
    Set wb1 = Workbooks("SomeAlreadyOpenBook.xlsx")
    Set wb2 = Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
    v = wb2.Sheets("SomeSheet").Range("A1").Value
    wb1.Sheets("SomeOtherSheet").Range("A1").Value = v
    wb2.Close()
End Sub

범위 변수로 범위를 SubS 및 FunctionS에 전달하십시오.

Sub ClearRange(r as Range)
    r.ClearContents
    '....
End Sub

Sub MyMacro()
    Dim rng as Range
    Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:B10")
    ClearRange rng
End Sub

또한 메소드 (예 : Find및 Copy)를 변수에 적용해야 합니다.

Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10")
Set rng2 = ThisWorkbook.Worksheets("SomeSheet").Range("B1:B10")
rng1.Copy rng2

셀 범위를 반복하는 경우 범위 값을 변형 배열에 먼저 복사하고 반복하는 것이 좋습니다 (빠른).

Dim dat As Variant
Dim rng As Range
Dim i As Long

Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10000")
dat = rng.Value  ' dat is now array (1 to 10000, 1 to 1)
for i = LBound(dat, 1) to UBound(dat, 1)
    dat(i,1) = dat(i,1) * 10 'or whatever operation you need to perform
next
rng.Value = dat ' put new values back on sheet

이것은 가능한 것에 대한 작은 맛보기입니다.

답변

.Select/ .Activate/ Selection/ Activecell/ Activesheet/ Activeworkbook등을 피해야하는 두 가지 주요 이유

  1. 코드 속도가 느려집니다.
  2. 일반적으로 런타임 오류의 주요 원인입니다.

우리는 어떻게 그것을 피합니까?

1) 관련 객체로 직접 작업

이 코드를 고려

Sheets("Sheet1").Activate
Range("A1").Select
Selection.Value = "Blah"
Selection.NumberFormat = "@"

이 코드는 다음과 같이 작성할 수도 있습니다.

With Sheets("Sheet1").Range("A1")
    .Value = "Blah"
    .NumberFormat = "@"
End With

2) 필요한 경우 변수를 선언하십시오. 위와 동일한 코드를 작성할 수 있습니다

Dim ws as worksheet

Set ws = Sheets("Sheet1")

With ws.Range("A1")
    .Value = "Blah"
    .NumberFormat = "@"
End With

답변

위에서 언급 한 모든 훌륭한 답변에 한 가지 작은 강조점을 추가하겠습니다.

아마도 Select 사용을 피하기 위해 할 수있는 가장 큰 일은 VBA 코드에서 가능한 한 의미있는 변수 이름과 결합 된 명명 된 범위를 사용하는 것 입니다. 이 점은 위에서 언급했지만 조금씩 빛났다. 그러나 특별한주의가 필요합니다.

더 많은 것을 생각할 수는 있지만 명명 된 범위를 자유롭게 사용하는 몇 가지 추가 이유가 있습니다.

명명 된 범위를 사용하면 코드를보다 쉽게 ​​읽고 이해할 수 있습니다.

예:

Dim Months As Range
Dim MonthlySales As Range

Set Months = Range("Months")
'e.g, "Months" might be a named range referring to A1:A12

Set MonthlySales = Range("MonthlySales")
'e.g, "Monthly Sales" might be a named range referring to B1:B12

Dim Month As Range
For Each Month in Months
    Debug.Print MonthlySales(Month.Row)
Next Month

명명 된 범위 Months와 MonthlySales포함 된 내용 및 절차를 수행하는 것은 매우 분명합니다 .

이것이 왜 중요한가? 그것은 쉽게 부분적으로 있기 때문에 다른 사람들이 그것을 이해하지만하기 위해 혹시 보거나 때문에, 당신은 여전히 명명 된 범위와 좋은 변수 이름을 사용해야 코드를 사용하는 유일한 사람 경우에도 당신이 잊고 당신이 그것으로 할 무엇을 의미하는지 년 후, 그리고 당신은 낭비 당신의 코드가 무엇을하고 있는지 알아내는 30 분.

명명 된 범위는 스프레드 시트의 구성이 변경 될 때 매크로가 깨지지 않도록합니다.

위의 예제가 다음과 같이 작성된 경우를 고려하십시오.

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Range("A1:A12")
Set rng2 = Range("B1:B12")

Dim rng3 As Range
For Each rng3 in rng1 
    Debug.Print rng2(rng3.Row)
Next rng3

이 코드는 처음에는 잘 작동합니다. 즉, 귀하 또는 미래의 사용자가 “gee wiz, 결정할 때까지 열에 새 열을 추가 할 것입니다 A!”또는 몇 개월과 판매 열을 추가하거나 각 열에 헤더를 추가하십시오. 이제 코드가 깨졌습니다. 그리고 끔찍한 변수 이름을 사용했기 때문에 필요한 것보다 수정하는 방법을 알아내는 데 더 많은 시간이 걸립니다.

명명 된 범위를 사용하여 시작한 경우 Months및 Sales열을 원하는대로 이동할 수 있으며 코드는 계속 정상적으로 작동합니다.

답변

나는 다른 사람들이 모두 긴 것을 주었으므로 짧은 대답을 할 것입니다.

매크로를 기록하고 재사용 할 때마다 .select 및 .activate가 표시됩니다. 셀이나 시트를 선택하면 활성화됩니다. 그 시점부터는 Range.Value활성 셀과 시트를 사용하는 것처럼 규정되지 않은 참조를 사용할 때마다 . 코드의 위치를 ​​보지 않거나 사용자가 통합 문서를 클릭하는 경우에도 문제가 될 수 있습니다.

따라서 셀을 직접 참조하여 이러한 문제를 제거 할 수 있습니다. 간다 :

'create and set a range
Dim Rng As Excel.Range
Set Rng = Workbooks("Book1").Worksheets("Sheet1").Range("A1")
'OR
Set Rng = Workbooks(1).Worksheets(1).Cells(1, 1)

아니면 당신은 할 수

'Just deal with the cell directly rather than creating a range
'I want to put the string "Hello" in Range A1 of sheet 1
Workbooks("Book1").Worksheets("Sheet1").Range("A1").value = "Hello"
'OR
Workbooks(1).Worksheets(1).Cells(1, 1).value = "Hello"

이러한 방법에는 여러 가지 조합이 있지만 나 같은 초조 한 사람들에게는 가능한 한 빨리 표현되는 일반적인 아이디어가 될 것입니다.

답변

“… Select 함수 대신 변수를 사용할 수 있으면 코드를 더 재사용 할 수 있다는 것을 알게되었습니다.”

.Select직접 셀 참조보다 더 나은 선택이 될 수있는 격리 된 소수의 상황 이상을 생각할 수는 없지만 , 방어 해야 할 Selection이유는 .Select피해야 할 이유와 같은 이유로 버려서는 안된다는 점입니다.

단축 키 조합에 할당 된 짧고 시간을 절약하는 매크로 서브 루틴을 두 키를 탭하면 많은 시간을 절약 할 수 있습니다. 워크 시트 전체의 데이터 형식을 준수하지 않는 포켓 데이터를 처리 할 때 작업 코드에서 작동하도록 셀 그룹을 선택할 수 있다는 것은 놀라운 일입니다. 셀 그룹을 선택하고 서식 변경을 적용 할 수있는 것과 같은 방식으로 셀 그룹을 선택하여 특수 매크로 코드를 실행하면 시간을 크게 절약 할 수 있습니다.

선택 기반 하위 프레임 워크의 예 :

Public Sub Run_on_Selected()
    Dim rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each rng In rSEL
        Debug.Print rng.Address(0, 0)
        'cell-by-cell operational code here
    Next rng
    Set rSEL = Nothing
End Sub

Public Sub Run_on_Selected_Visible()
    'this is better for selected ranges on filtered data or containing hidden rows/columns
    Dim rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each rng In rSEL.SpecialCells(xlCellTypeVisible)
        Debug.Print rng.Address(0, 0)
        'cell-by-cell operational code here
    Next rng
    Set rSEL = Nothing
End Sub

Public Sub Run_on_Discontiguous_Area()
    'this is better for selected ranges of discontiguous areas
    Dim ara As Range, rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each ara In rSEL.Areas
        Debug.Print ara.Address(0, 0)
        'cell group operational code here
        For Each rng In ara.Areas
            Debug.Print rng.Address(0, 0)
            'cell-by-cell operational code here
        Next rng
    Next ara
    Set rSEL = Nothing
End Sub

처리 할 실제 코드는 한 줄에서 여러 모듈에 이르기까지 모든 것이 될 수 있습니다. 이 방법을 사용하여 외부 통합 문서의 파일 이름을 포함하는 거친 셀 선택에서 장기 실행 루틴을 시작했습니다.

간단히 말해서 and와 Selection밀접한 관련이 있기 때문에 버리지 마십시오 . 워크 시트 속성으로서 다른 많은 목적이 있습니다..SelectActiveCell

(예, 나는이 질문에 관한 .Select것이 Selection아니라 VBA 코더가 추측 할 수있는 오해를 제거하고 싶었습니다.)

답변

다음에서는 Select 접근법 (OP가 피하고 싶은 접근법)과 Range 접근법을 비교하고 있습니다 (이것은 질문에 대한 답변입니다). 따라서 첫 번째 선택이 표시 될 때 읽기를 중단하지 마십시오.

실제로 당신이하려는 일에 달려 있습니다. 어쨌든 간단한 예가 유용 할 수 있습니다. 활성 셀의 값을 “foo”로 설정한다고 가정 해 봅시다. ActiveCell을 사용하면 다음과 같이 작성할 수 있습니다.

Sub Macro1()
    ActiveCell.Value = "foo"
End Sub

활성 셀이 아닌 셀 (예 : “B2”)에이 셀을 사용하려면 먼저 다음과 같이 선택해야합니다.

Sub Macro2()
    Range("B2").Select
    Macro1
End Sub

범위를 사용하면 원하는 셀의 값을 원하는대로 설정하는 데 사용할 수있는보다 일반적인 매크로를 작성할 수 있습니다.

Sub SetValue(cellAddress As String, aVal As Variant)
    Range(cellAddress).Value = aVal
End Sub

그런 다음 Macro2를 다음과 같이 다시 작성할 수 있습니다.

Sub Macro2()
    SetCellValue "B2", "foo"
End Sub

그리고 Macro1은 다음과 같습니다.

Sub Macro1()
    SetValue ActiveCell.Address, "foo"
End Sub

이것이 조금 정리하는 데 도움이되기를 바랍니다.

답변

회피 Select하고 Activate당신을 조금 더 나은 VBA 개발자로 만드는 움직임입니다. 일반적으로, Select그리고 Activate매크로 기록 할 때, 따라서, 사용되는 Parent시트 또는 범위가 항상 활성 상태로 간주된다.

이것은 당신이 피할 수있는 방법입니다 Select및 Activate다음과 같은 경우 :

새 워크 시트 추가 및 셀 복사 :

From (매크로 레코더로 생성 된 코드) :

Sub Makro2()
    Range("B2").Select
    Sheets.Add After:=ActiveSheet
    Sheets("Tabelle1").Select
    Sheets("Tabelle1").Name = "NewName"
    ActiveCell.FormulaR1C1 = "12"
    Range("B2").Select
    Selection.Copy
    Range("B3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

에:

Sub TestMe()
    Dim ws As Worksheet
    Set ws = Worksheets.Add
    With ws
        .Name = "NewName"
        .Range("B2") = 12
        .Range("B2").Copy Destination:=.Range("B3")
    End With
End Sub

워크 시트간에 범위를 복사하려는 경우 :

에서:

Sheets("Source").Select
Columns("A:D").Select
Selection.Copy
Sheets("Target").Select
Columns("A:D").Select
ActiveSheet.Paste

에:

Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").Range("a1")

멋진 명명 된 범위 사용

당신은 그들에 액세스 할 수있는 []다른 방법에 비해, 정말 아름다운이다. 자신을 확인하십시오 :

Dim Months As Range
Dim MonthlySales As Range

Set Months = Range("Months")
Set MonthlySales = Range("MonthlySales")

Set Months =[Months]
Set MonthlySales = [MonthlySales]

위의 예는 다음과 같습니다.

Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").[A1]

값을 복사하지 않고 가져옵니다.

일반적으로 의향이 있다면 select무엇인가를 복사하고있는 것입니다. 값에만 관심이있는 경우 선택을 피하는 것이 좋습니다.

Range("B1:B6").Value = Range("A1:A6").Value

항상 워크 시트를 참조하십시오

이것은 아마도 가장 흔한 실수 일 것입니다 VBA. 범위를 복사 할 때마다 때때로 워크 시트가 참조되지 않으므로 VBA는 잘못된 시트를 ActiveWorksheet로 간주합니다.

'This will work only if the 2. Worksheet is selected!
Public Sub TestMe()
    Dim rng As Range
    Set rng = Worksheets(2).Range(Cells(1, 1), Cells(2, 2)).Copy
End Sub

'This works always!
Public Sub TestMe2()
    Dim rng As Range
    With Worksheets(2)
        .Range(.Cells(1, 1), .Cells(2, 2)).Copy
    End With
End Sub

나는 절대로 .Select또는 .Activate아무것도 사용하지 않을 수 있습니까 ?

  • 사용을 정당화 할 수있는 시기 .Activate와 .Select시각적 이유로 특정 워크 시트를 선택해야하는 경우를 보여주는 좋은 예입니다 . 예를 들어, 파일을 닫을 때 어떤 ActiveSheet를 무시했는지에 관계없이 Excel에서 항상 표지 워크 시트를 먼저 선택한 상태로 Excel을 열어야합니다.

따라서 아래 코드와 같은 것이 좋습니다.

Private Sub Workbook_Open()
    Worksheets("Cover").Activate
End Sub