excel-vba
Excel VBA tips och tricks

Anmärkningar

Använda xlVeryHidden Sheets

Kalkylblad i Excel har tre alternativ för den Visible egenskapen. Dessa alternativ representeras av konstanter i xlSheetVisibility uppräkningen och är enligt följande:

1. xlVisible eller xlSheetVisible värde: -1 (standard för nya ark)
2. xlHidden eller xlSheetHidden värde: 0
3. xlVeryHidden xlSheetVeryHidden värde: 2

Synliga ark representerar standardsynligheten för ark. De syns i arkflikfältet och kan fritt väljas och visas. Dolda ark döljs från arkflikfältet och kan därför inte väljas. Dolda ark kan emellertid tas bort från excel-fönstret genom att högerklicka på arkflikarna och välja "Unhide"

Mycket dolda ark, å andra sidan, är bara tillgängliga via Visual Basic Editor. Detta gör dem till ett oerhört användbart verktyg för att lagra data över Excel-exemplar och lagra data som bör döljas för slutanvändare. Bladen kan nås med namnet referens inom VBA-kod, vilket gör det enkelt att använda den lagrade informationen.

Om du vill ändra ett kalkylblads. Synliga egenskap till xlSheetVeryHidden öppnar du VBE: s Egenskaper ( F4 ), väljer kalkylbladet du vill ändra och använder rullgardinsmenyn i den trettonde raden för att göra ditt val.

Om du vill ändra ett kalkylblads .Visible-egenskap till xlSheetVeryHidden¹ i kod, öppnar du på samma sätt den .Visible-egenskapen och tilldelar ett nytt värde.

with Sheet3
    .Visible = xlSheetVeryHidden
end with

¹ _{Både xlVeryHidden och xlSheetVeryHidden returnerar ett numeriskt värde på 2 (de är utbytbara).}

Kalkylblad. Namn, .Index eller. Kodnamn

Vi vet att "bästa praxis" dikterar att ett intervallobjekt ska ha sitt överordnade kalkylblad uttryckligen refererat. Ett kalkylblad kan hänvisas till med dess .Name-egenskap, numeriska .Index-egenskap eller dess .CodeName-egenskap, men en användare kan ordna kalkylbladet genom att helt enkelt dra en namnflik eller byta namn på kalkylbladet med ett dubbelklick på samma flik och några att skriva i en oskyddad arbetsbok.

Tänk på ett vanligt tre kalkylblad. Du har bytt namn på de tre kalkylerna måndag, tisdag och onsdag i den ordningen och kodade VBA-underrutiner som hänvisar till dessa. Tänk nu på att en användare kommer med och bestämmer att måndag hör till slutet av kalkylarken och sedan kommer en annan med och beslutar att kalkylens namn ser bättre ut på franska. Nu har du en arbetsbok med en kön på kalkylnamnet som ser ut som följande.

Om du hade använt någon av följande referensmetoder för kalkylbladet skulle din kod nu brytas.

'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

Både den ursprungliga ordningen och det ursprungliga kalkylbladets namn har äventyrats. Men om du hade använt egenskapen .CodeName-egenskapen för kalkylbladet, skulle underproceduren fortfarande vara i drift

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

Följande bild visar VBA-projektfönstret ([Ctrl] + R) som visar kalkylarken med .Kodnamn sedan av. Namn (inom parentes). Ordningen de visas ändras inte; ordinalen. Inställningen tas i den ordning de visas i kön för namnflik i kalkylfönstret.

Även om det är ovanligt att byta namn på ett .Kodnamn, är det inte omöjligt. Öppna helt enkelt VBE: s Egenskaper-fönster ([F4]).

Kalkylarket. Kodnamn finns i den första raden. Kalkylbladets .namn är i det tionde. Båda är redigerbara.

Använda strängar med avgränsare i stället för dynamiska matriser

Att använda Dynamic Arrays i VBA kan vara ganska klumpigt och tidsintensivt över mycket stora datamängder. När man lagrar enkla datatyper i en dynamisk grupp (Strängar, siffror, booleaner etc.) kan man undvika ReDim Preserve uttalanden som krävs för dynamiska arrayer i VBA genom att använda funktionen Split() med några smarta strängprocedurer. Till exempel kommer vi att titta på en slinga som lägger till en serie värden från ett intervall till en sträng baserat på vissa villkor och använder sedan den strängen för att fylla värdena i en 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

Själva Delimiter strängen kan ställas in på valfritt värde, men det är klokt att välja ett värde som inte naturligt kommer att uppstå inom uppsättningen. Säg till exempel att du bearbetade en kolumn med datum. I så fall använder du . , - , eller / skulle vara oklokt som avgränsare, eftersom datumen skulle kunna formateras för att använda något av dessa, vilket genererar fler datapunkter än du antog.

Obs! Det finns begränsningar för att använda den här metoden (nämligen den maximala längden på strängar), så den bör användas med försiktighet i fall av mycket stora datasätt. Detta är inte nödvändigtvis den snabbaste eller mest effektiva metoden för att skapa dynamiska matriser i VBA, men det är ett genomförbart alternativ.

Dubbelklicka-händelse för Excel-former

Som standard har Shapes i Excel inte ett specifikt sätt att hantera enstaka kontra dubbelklick, som bara innehåller egenskapen "OnAction" så att du kan hantera klick. Det kan dock finnas fall där din kod kräver att du agerar annorlunda (eller exklusivt) med ett dubbelklick. Följande subrutin kan läggas till i ditt VBA-projekt och, när det ställs in som OnAction rutin för din form, låter dig agera på dubbelklick.

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

Denna rutin får formen att ignorera det första klicket, bara köra önskad kod på det andra klicket inom det angivna tidsintervallet.

Öppna fildialog - Flera filer

Den här subrutinen är ett snabbt exempel på hur man tillåter en användare att välja flera filer och sedan göra något med de filvägarna, till exempel få filnamn och skicka den till konsolen via 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