excel-vba
Excel VBA 팁과 트릭

비고

xlVeryHidden 시트 사용

엑셀의 워크 시트에는 Visible 속성에 대한 세 가지 옵션이 있습니다. 이러한 옵션은 xlSheetVisibility 열거 형의 상수로 표시되며 다음과 같습니다.

1. xlVisible 또는 xlSheetVisible 값 : -1 (새 시트의 기본값)
2. xlHidden 또는 xlSheetHidden 값 : 0
3. xlVeryHidden xlSheetVeryHidden xlVeryHidden 값 : 2

보이는 시트는 시트의 기본 가시성을 나타냅니다. 시트 탭 막대에 표시되며 자유롭게 선택하고 볼 수 있습니다. 숨겨진 시트는 시트 탭 막대에서 숨겨 지므로 선택할 수 없습니다. 그러나 숨겨진 시트는 시트 탭을 마우스 오른쪽 버튼으로 클릭하고 '숨기기 해제'를 선택하여 Excel 창에서 숨길 수 있습니다.

반면에 매우 숨겨진 시트는 Visual Basic Editor를 통해서만 액세스 할 수 있습니다. 따라서 최종 사용자로부터 숨겨져 있어야하는 데이터를 저장할뿐만 아니라 Excel의 여러 인스턴스에서 데이터를 저장하는 데 매우 유용한 도구가됩니다. VBA 코드 내에서 명명 된 참조로 시트에 액세스 할 수 있으므로 저장된 데이터를 쉽게 사용할 수 있습니다.

워크 시트의 .Visible 속성을 xlSheetVeryHidden으로 수동 변경하려면 VBE의 속성 창 ( F4 )을 열고 변경할 워크 시트를 선택한 다음 13 번째 줄의 드롭 다운을 사용하여 선택합니다.

코드에서 워크 시트의 .Visible 속성을 xlSheetVeryHidden¹로 변경하려면 마찬가지로 .Visible 속성에 액세스하고 새 값을 할당합니다.

with Sheet3
    .Visible = xlSheetVeryHidden
end with

¹ _{xlVeryHidden 과 xlSheetVeryHidden은 모두 숫자 값 2를 반환합니다 (서로 바꿔 사용할 수 있음).}

워크 시트 .Name, .Index 또는 .CodeName

'우수 사례'는 범위 객체가 명시 적으로 참조 된 상위 워크 시트를 가져야한다고 명시합니다. 워크 시트는 .Name 속성, 숫자 .Index 속성 또는 .CodeName 속성을 통해 참조 할 수 있지만 사용자는 이름 탭을 드래그하거나 워크 시트의 이름을 더블 클릭하거나 이름을 바꿀 수 있습니다. 보호되지 않은 통합 문서에 입력합니다.

표준 세 가지 워크 시트를 고려하십시오. 월요일, 화요일, 수요일의 순서로 3 개의 워크 시트의 이름을 변경하고이를 참조하는 VBA 서브 프로 시저를 코딩했습니다. 이제는 한 명의 사용자가 워크 시트 대기열 끝에 있고 월요일이 워크 시트 대기열에 속한 다음 다른 워크 시트가 프랑스어로보기 좋다고 결정한 것으로 간주합니다. 이제 다음과 같은 워크 시트 이름 탭 대기열이있는 통합 문서가 있습니다.

다음 워크 시트 참조 방법 중 하나를 사용했다면 코드가 손상됩니다.

'reference worksheet by .Name
with worksheets("Monday")
    'operation code here; for example:
    .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)) = 1
end with

'reference worksheet by ordinal .Index
with worksheets(1)
    'operation code here; for example:
    .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)) = 1
end with

원래 주문과 원래 워크 시트 이름이 모두 손상되었습니다. 그러나 워크 시트의 .CodeName 속성을 사용한 경우 하위 프로 시저가 여전히 작동 할 수 있습니다.

with Sheet1
    'operation code here; for example:
    .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)) = 1
end with

다음 이미지는 VBA 프로젝트 창 ([Ctrl] + R)을 보여줍니다.이 창은 .CodeName, .Name (괄호 안에) 순으로 워크 시트를 나열합니다. 표시되는 순서는 변경되지 않습니다. 서수 .Index는 워크 시트 창에있는 이름 탭 대기열에 표시된 순서대로 사용됩니다.

.CodeName의 이름을 바꾸는 일은 드문 일이지만 불가능하지 않습니다. VBE의 Properties 창 ([F4])을 열면됩니다.

워크 시트. 코드 이름은 첫 번째 행에 있습니다. 워크 시트의 .Name은 열 번째에 있습니다. 둘 다 편집 할 수 있습니다.

동적 배열 대신 구분 기호와 함께 문자열 사용

VBA에서 동적 배열을 사용하면 매우 큰 데이터 세트에 비해 상당히 복잡하고 시간이 오래 걸릴 수 있습니다. 간단한 데이터 형식을 동적 배열 (Strings, Numbers, Booleans 등)에 저장할 때 Split() 함수를 사용하여 VBA에서 동적 배열에 필요한 ReDim Preserve 문을 피할 수 있습니다. 예를 들어 범위에서 일련의 값을 조건에 따라 문자열에 추가 한 다음 해당 문자열을 사용하여 ListBox의 값을 채우는 루프를 살펴 보겠습니다.

Private Sub UserForm_Initialize()

Dim Count As Long, DataString As String, Delimiter As String

For Count = 1 To ActiveSheet.UsedRows.Count
    If ActiveSheet.Range("A" & Count).Value <> "Your Condition" Then
        RowString = RowString & Delimiter & ActiveSheet.Range("A" & Count).Value
        Delimiter = "><" 'By setting the delimiter here in the loop, you prevent an extra occurance of the delimiter within the string
    End If
Next Count

ListBox1.List = Split(DataString, Delimiter)

End Sub

Delimiter 문자열 자체는 임의의 값으로 설정할 수 있지만 자연스럽게 집합 내에서 발생하지 않는 값을 선택하는 것이 좋습니다. 예를 들어 날짜 열을 처리한다고 가정 해 보겠습니다. 이 경우,를 사용하십시오 . , - 또는 / 는 구분 기호로 현명하지 않을 수 있습니다. 날짜 중 하나를 사용하여 형식을 지정할 수 있으므로 예상보다 많은 데이터 포인트가 생성됩니다.

참고 : 이 방법을 사용할 때 한계가 있습니다 (즉, 문자열의 최대 길이). 매우 큰 데이터 세트의 경우에는주의해서 사용해야합니다. 이것은 반드시 VBA에서 동적 배열을 만드는 가장 빠르고 가장 효과적인 방법은 아니지만 가능한 대안입니다.

Excel 셰이프의 두 번 클릭 이벤트

기본적으로 Excel의 셰이프에는 클릭 수를 처리 할 수있는 "OnAction"속성 만 포함 된 단일 또는 이중 클릭을 처리하는 특정 방법이 없습니다. 그러나 코드에서 두 번 클릭하여 다르게 (또는 독점적으로) 행동해야하는 경우가있을 수 있습니다. 다음 서브 루틴을 VBA 프로젝트에 추가 할 수 있으며 셰이프의 OnAction 루틴으로 설정하면 두 번 클릭하여 작업 할 수 있습니다.

Public Const DOUBLECLICK_WAIT as Double = 0.25 'Modify to adjust click delay
Public LastClickObj As String, LastClickTime As Date

Sub ShapeDoubleClick()
    
    If LastClickObj = "" Then
        LastClickObj = Application.Caller
        LastClickTime = CDbl(Timer)
    Else
        If CDbl(Timer) - LastClickTime > DOUBLECLICK_WAIT Then
            LastClickObj = Application.Caller
            LastClickTime = CDbl(Timer)
        Else
            If LastClickObj = Application.Caller Then
                'Your desired Double Click code here
                LastClickObj = ""
            Else
                LastClickObj = Application.Caller
                LastClickTime = CDbl(Timer)
            End If
        End If
    End If
    
End Sub

이 루틴은 모양이 첫 번째 클릭을 기능적으로 무시하게하고 지정된 시간 범위 내의 두 번째 클릭에서 원하는 코드 만 실행합니다.

파일 열기 대화 상자 - 여러 파일

이 서브 루틴은 사용자가 여러 파일을 선택하고 그 파일 경로를 사용하여 파일 이름을 얻은 다음 debug.print를 통해 콘솔로 보내는 것과 같은 작업을 수행하는 방법에 대한 간단한 예입니다.

Option Explicit

Sub OpenMultipleFiles()
    Dim fd As FileDialog
    Dim fileChosen As Integer
    Dim i As Integer
    Dim basename As String
    Dim fso As Variant
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    basename = fso.getBaseName(ActiveWorkbook.Name)
    fd.InitialFileName = ActiveWorkbook.Path ' Set Default Location to the Active Workbook Path
    fd.InitialView = msoFileDialogViewList
    fd.AllowMultiSelect = True
    
    fileChosen = fd.Show
    If fileChosen = -1 Then
        'open each of the files chosen
        For i = 1 To fd.SelectedItems.Count
            Debug.Print (fd.SelectedItems(i))
            Dim fileName As String
            ' do something with the files.
            fileName = fso.getFileName(fd.SelectedItems(i))
            Debug.Print (fileName)
        Next i
    End If

End Sub