excel-vba
Excel VBA Wskazówki i porady

Uwagi

Korzystanie z xlVeryHidden Sheets

Arkusze w programie Excel mają trzy opcje dla właściwości Visible . Te opcje są reprezentowane przez stałe w wyliczeniu xlSheetVisibility i są następujące:

1. xlVisible lub xlSheetVisible wartość: -1 (domyślnie dla nowych arkuszy)
2. xlHidden lub xlSheetHidden wartość: 0
3. xlVeryHidden xlSheetVeryHidden wartość: 2

Widoczne arkusze reprezentują domyślną widoczność arkuszy. Są one widoczne na pasku kart arkusza i można je dowolnie wybierać i przeglądać. Ukryte arkusze są ukryte na pasku kart arkusza i dlatego nie można ich wybrać. Ukryte arkusze można jednak ukryć w oknie programu Excel, klikając prawym przyciskiem myszy zakładki arkuszy i wybierając „Pokaż”

Z drugiej strony bardzo ukryte arkusze są dostępne tylko za pomocą Edytora Visual Basic. Dzięki temu są one niezwykle przydatnym narzędziem do przechowywania danych między instancjami programu Excel, a także do przechowywania danych, które powinny być ukryte przed użytkownikami końcowymi. Dostęp do arkuszy można uzyskać poprzez nazwane odniesienie w kodzie VBA, umożliwiając łatwe wykorzystanie przechowywanych danych.

Aby ręcznie zmienić właściwość .Visible arkusza roboczego na xlSheetVeryHidden, otwórz okno Właściwości VBE ( F4 ), wybierz arkusz, który chcesz zmienić, i użyj menu rozwijanego w trzynastym wierszu, aby dokonać wyboru.

Aby zmienić właściwość .Visible arkusza roboczego na xlSheetVeryHidden¹ w kodzie, podobnie uzyskaj dostęp do właściwości .Visible i przypisz nową wartość.

with Sheet3
    .Visible = xlSheetVeryHidden
end with

¹ _{Zarówno xlVeryHidden, jak i xlSheetVeryHidden zwracają wartość liczbową 2 (są one wymienne).}

Arkusz .Name, .Index lub .CodeName

Wiemy, że „najlepsza praktyka” nakazuje jawne odwołanie do obiektu nadrzędnego. Do arkusza można odwoływać się za pomocą jego właściwości .Name, numerycznej .Index lub jego właściwości .CodeName, ale użytkownik może zmienić kolejność kolejki arkuszy, po prostu przeciągając kartę nazwy lub zmieniając nazwę arkusza roboczego, klikając dwukrotnie tę samą kartę i niektóre wpisując niechroniony skoroszyt.

Rozważ standardowy trzy arkusze. Zmieniłeś nazwy trzech arkuszy w poniedziałek, wtorek i środę w tej kolejności i zakodowałeś procedury VBA, które się do nich odnoszą. Teraz zastanów się, że jeden użytkownik przychodzi i decyduje, że poniedziałek należy do końca kolejki arkuszy, a następnie przychodzi inny i decyduje, że nazwy arkuszy wyglądają lepiej po francusku. Masz teraz skoroszyt z kolejką kart nazw arkuszy, która wygląda mniej więcej tak:

Jeśli używałeś jednej z następujących metod odwoływania się do arkusza roboczego, kod zostałby teraz uszkodzony.

'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

Zarówno oryginalne zamówienie, jak i nazwa oryginalnego arkusza roboczego zostały naruszone. Jeśli jednak użyto właściwości .CodeName arkusza roboczego, podprocedura nadal działałaby

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

Poniższy obraz pokazuje okno Projektu VBA ([Ctrl] + R), które zawiera listę arkuszy według .CodeName, a następnie .Name (w nawiasach). Kolejność ich wyświetlania się nie zmienia; numer porządkowy .Index jest brany według kolejności, w jakiej są wyświetlane w kolejce kart nazw w oknie arkusza roboczego.

Chociaż zmiana nazwy .CodeName jest rzadkością, nie jest to niemożliwe. Wystarczy otworzyć okno Właściwości VBE ([F4]).

Arkusz roboczy .CodeName znajduje się w pierwszym rzędzie. Nazwa arkusza .Name jest w dziesiątej. Oba są edytowalne.

Używanie ciągów z ogranicznikami zamiast tablic dynamicznych

Korzystanie z tablic dynamicznych w VBA może być dość niewygodne i czasochłonne w przypadku bardzo dużych zestawów danych. Przechowując proste typy danych w tablicy dynamicznej (ciągi, liczby, booleany itp.), Można uniknąć instrukcji ReDim Preserve wymaganych od tablic dynamicznych w VBA, używając funkcji Split() z pewnymi sprytnymi procedurami łańcuchowymi. Na przykład przyjrzymy się pętli, która dodaje szereg wartości z zakresu do ciągu w oparciu o niektóre warunki, a następnie używa tego ciągu do wypełnienia wartości 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

Sam łańcuch Delimiter można ustawić na dowolną wartość, ale rozsądnie jest wybrać wartość, która naturalnie nie pojawi się w zestawie. Powiedzmy, na przykład, że przetwarzałeś kolumnę dat. W takim przypadku za pomocą . , - lub / byłyby nierozsądne jako ograniczniki, ponieważ daty można sformatować tak, aby używały dowolnego z nich, generując więcej punktów danych, niż się spodziewałeś.

Uwaga: Istnieją ograniczenia w stosowaniu tej metody (mianowicie maksymalna długość ciągów znaków), dlatego należy zachować ostrożność w przypadku bardzo dużych zestawów danych. Niekoniecznie jest to najszybsza lub najskuteczniejsza metoda tworzenia dynamicznych tablic w VBA, ale jest realną alternatywą.

Zdarzenie podwójnego kliknięcia dla kształtów Excela

Domyślnie Kształty w programie Excel nie mają określonego sposobu obsługi pojedynczych i podwójnych kliknięć, zawierającego tylko właściwość „OnAction”, która umożliwia obsługę kliknięć. Mogą jednak wystąpić sytuacje, w których Twój kod wymaga działania innego (lub wyłącznie) przy podwójnym kliknięciu. Poniższy podprogram można dodać do projektu VBA, a gdy zostanie ustawiony jako procedura OnAction dla kształtu, umożliwia działanie przy podwójnym kliknięciu.

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

Ta procedura spowoduje, że kształt będzie funkcjonalnie ignorował pierwsze kliknięcie, uruchamiając tylko pożądany kod przy drugim kliknięciu w określonym przedziale czasu.

Otwórz okno dialogowe plików - wiele plików

Ten podprogram jest szybkim przykładem tego, jak pozwolić użytkownikowi wybrać wiele plików, a następnie zrobić coś z tymi ścieżkami plików, na przykład uzyskać nazwy plików i wysłać je do konsoli za pośrednictwem 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