excel-vba
Excel VBA Tips en trucs

Opmerkingen

XlVeryHidden Sheets gebruiken

Werkbladen in Excel hebben drie opties voor de eigenschap Visible . Deze opties worden voorgesteld door constanten in de xlSheetVisibility opsomming en zijn als volgt:

1. xlVisible of xlSheetVisible waarde: -1 (de standaardwaarde voor nieuwe bladen)
2. xlHidden of xlSheetHidden waarde: 0
3. xlVeryHidden xlSheetVeryHidden waarde: 2

Zichtbare bladen vertegenwoordigen de standaardzichtbaarheid voor bladen. Ze zijn zichtbaar in de bladtabbalk en kunnen vrij worden geselecteerd en bekeken. Verborgen bladen zijn verborgen op de bladtabbalk en kunnen dus niet worden geselecteerd. Verborgen bladen kunnen echter worden verborgen vanuit het Excel-venster door met de rechtermuisknop op de bladtabs te klikken en "Onthouden" te selecteren

Zeer verborgen bladen daarentegen zijn alleen toegankelijk via de Visual Basic Editor. Dit maakt ze een ongelooflijk handig hulpmiddel voor het opslaan van gegevens in verschillende instanties van Excel, evenals het opslaan van gegevens die voor eindgebruikers moeten worden verborgen. De bladen zijn toegankelijk via de genoemde referentie binnen de VBA-code, waardoor de opgeslagen gegevens eenvoudig kunnen worden gebruikt.

Als u de .Visible-eigenschap van een werkblad handmatig wilt wijzigen in xlSheetVeryHidden, opent u het venster Eigenschappen van de VBE ( F4 ), selecteert u het werkblad dat u wilt wijzigen en gebruikt u de vervolgkeuzelijst in de dertiende rij om uw selectie te maken.

Als u de .Visible-eigenschap van een werkblad wilt wijzigen in xlSheetVeryHidden¹ in code, opent u op dezelfde manier de .Visible-eigenschap en wijst u een nieuwe waarde toe.

with Sheet3
    .Visible = xlSheetVeryHidden
end with

¹ _{Zowel xlVeryHidden als xlSheetVeryHidden retourneren een numerieke waarde van 2 (ze zijn uitwisselbaar).}

Werkblad .Name, .Index of .CodeName

We weten dat 'best practice' voorschrijft dat naar een bovenliggend object moet worden verwezen naar het bovenliggende werkblad. Een werkblad kan worden aangeduid met zijn .Name-eigenschap, numerieke .Index-eigenschap of zijn .CodeName-eigenschap, maar een gebruiker kan de werkbladwachtrij opnieuw ordenen door eenvoudig een naamtabblad te slepen of het werkblad te hernoemen met een dubbelklik op hetzelfde tabblad en enkele typen in een niet-beveiligde werkmap.

Overweeg een standaard drie werkblad. U hebt de drie werkbladen maandag, dinsdag en woensdag in die volgorde hernoemd en VBA-subprocedures gecodeerd die hiernaar verwijzen. Bedenk nu dat een gebruiker langskomt en besluit dat maandag aan het einde van de wachtrij van het werkblad hoort, dan komt een andere langs en besluit dat de werkbladnamen er beter uitzien in het Frans. U hebt nu een werkmap met een wachtrij voor een werkbladnaamtabblad dat er ongeveer als volgt uitziet.

Als u een van de volgende werkbladverwijzingsmethoden had gebruikt, zou uw code nu worden verbroken.

'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

Zowel de oorspronkelijke volgorde als de originele werkbladnaam is aangetast. Als u echter de eigenschap .CodeName van het werkblad had gebruikt, zou uw subprocedure nog steeds operationeel zijn

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

De volgende afbeelding toont het VBA-projectvenster ([Ctrl] + R) waarin de werkbladen worden vermeld op .CodeName en vervolgens op .Name (tussen haakjes). De volgorde waarin ze worden weergegeven, verandert niet; de rangtelwoord .Index wordt genomen in de volgorde waarin ze worden weergegeven in de wachtrij van het tabblad met namen in het werkbladvenster.

Hoewel het ongewoon is om een .CodeName te hernoemen, is het niet onmogelijk. Open eenvoudig het venster Eigenschappen van de VBE ([F4]).

Het werkblad .CodeName bevindt zich in de eerste rij. De .Name van het werkblad staat in de tiende. Beide zijn bewerkbaar.

Tekenreeksen gebruiken met scheidingstekens in plaats van dynamische arrays

Het gebruik van Dynamic Arrays in VBA kan behoorlijk onhandig en tijdrovend zijn bij zeer grote datasets. Bij het opslaan van eenvoudige datatypes in een dynamische array (Strings, Numbers, Booleans etc.) kan men de ReDim Preserve instructies die vereist zijn voor dynamische arrays in VBA vermijden door de Split() -functie te gebruiken met enkele slimme stringprocedures. We zullen bijvoorbeeld kijken naar een lus die een reeks waarden uit een bereik toevoegt aan een string op basis van enkele voorwaarden, en vervolgens die string gebruiken om de waarden van een ListBox te vullen.

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

De Delimiter zelf kan op elke waarde worden ingesteld, maar het is verstandig om een waarde te kiezen die niet vanzelf in de set voorkomt. Stel dat u bijvoorbeeld een kolom met datums aan het verwerken was. In dat geval met . , - , of / zou onverstandig zijn als scheidingstekens, omdat de datums kunnen worden opgemaakt om een van deze te gebruiken, waardoor meer gegevenspunten worden gegenereerd dan u had verwacht.

Opmerking: er zijn beperkingen aan het gebruik van deze methode (namelijk de maximale lengte van tekenreeksen), dus het moet met voorzichtigheid worden gebruikt in het geval van zeer grote datasets. Dit is niet noodzakelijk de snelste of meest effectieve methode voor het maken van dynamische arrays in VBA, maar het is een uitvoerbaar alternatief.

Dubbelklik op Evenement voor Excel-vormen

Vormen in Excel hebben standaard geen specifieke manier om enkele versus dubbele klikken te verwerken, met alleen de eigenschap "OnAction" waarmee u klikken kunt verwerken. Er kunnen echter gevallen zijn waarin uw code vereist dat u anders (of exclusief) handelt bij een dubbelklik. De volgende subroutine kan worden toegevoegd aan uw VBA-project en, wanneer ingesteld als de OnAction routine voor uw vorm, kunt u reageren op dubbele klikken.

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

Deze routine zorgt ervoor dat de vorm de eerste klik functioneel negeert en alleen de gewenste code uitvoert bij de tweede klik binnen de opgegeven periode.

Open het dialoogvenster Bestand - Meerdere bestanden

Deze subroutine is een snel voorbeeld om een gebruiker meerdere bestanden te laten selecteren en vervolgens iets met die bestandspaden te doen, zoals de bestandsnamen ophalen en via debug.print naar de console sturen.

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