excel-vba
Suggerimenti e trucchi Excel VBA

Osservazioni

Usando i fogli xlVeryHidden

I fogli di lavoro in Excel hanno tre opzioni per la proprietà Visible . Queste opzioni sono rappresentate da costanti nell'enumerazione xlSheetVisibility e sono le seguenti:

1. xlVisible o xlSheetVisible : -1 (il valore predefinito per i nuovi fogli)
2. xlHidden o xlSheetHidden valore: 0
3. xlVeryHidden xlSheetVeryHidden valore: 2

I fogli visibili rappresentano la visibilità predefinita per i fogli. Sono visibili nella barra delle schede dei fogli e possono essere liberamente selezionati e visualizzati. I fogli nascosti sono nascosti dalla barra delle schede dei fogli e non sono quindi selezionabili. Tuttavia, i fogli nascosti possono essere nascosti dalla finestra di Excel facendo clic con il tasto destro sulle schede del foglio e selezionando "Scopri"

I fogli Very Hidden, d'altra parte, sono accessibili solo tramite Visual Basic Editor. Questo li rende uno strumento incredibilmente utile per l'archiviazione dei dati tra le istanze di Excel e per l'archiviazione dei dati che devono essere nascosti agli utenti finali. È possibile accedere ai fogli mediante un riferimento denominato all'interno del codice VBA, consentendo un facile utilizzo dei dati memorizzati.

Per modificare manualmente la proprietà .Visible di un foglio di lavoro su xlSheetVeryHidden, aprire la finestra Proprietà di VBE ( F4 ), selezionare il foglio di lavoro che si desidera modificare e utilizzare il menu a discesa nella tredicesima riga per effettuare la selezione.

Per modificare la proprietà .Visible di un foglio di lavoro su xlSheetVeryHidden¹ nel codice, accedere in modo analogo alla proprietà .Visible e assegnare un nuovo valore.

with Sheet3
    .Visible = xlSheetVeryHidden
end with

¹ _{Sia xlVeryHidden che xlSheetVeryHidden restituiscono un valore numerico di 2 (sono intercambiabili).}

Foglio di lavoro. Nome, .Index o .CodeName

Sappiamo che "best practice" impone che a un oggetto range debba essere esplicitamente fatto riferimento il foglio di lavoro principale. Un foglio di lavoro può essere riferito alla sua proprietà .Name, alla proprietà numerica .Index o alla sua proprietà .CodeName ma un utente può riordinare la coda del foglio semplicemente trascinando una scheda nome o rinominare il foglio con un doppio clic sulla stessa scheda e alcuni digitando una cartella di lavoro non protetta.

Considera un foglio di lavoro standard tre. In questo ordine sono stati rinominati i tre fogli di lavoro lunedì, martedì e mercoledì e le sottosezioni VBA codificate che fanno riferimento a questi. Ora considera che un utente arriva e decide che il lunedì appartiene alla fine della coda del foglio di lavoro, poi ne arriva un altro e decide che i nomi del foglio di lavoro sembrano migliori in francese. Ora hai una cartella di lavoro con una coda della scheda del nome del foglio di lavoro che assomiglia a quanto segue.

Se avessi usato uno dei seguenti metodi di riferimento per il foglio di lavoro, il tuo codice sarebbe stato rotto.

'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

Sia l'ordine originale che il nome del foglio di lavoro originale sono stati compromessi. Tuttavia, se si fosse utilizzata la proprietà .CodeName del foglio di lavoro, la sottoprocedura sarebbe ancora operativa

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

L'immagine seguente mostra la finestra del progetto VBA ([Ctrl] + R) che elenca i fogli di lavoro di .CodeName e poi di .Name (tra parentesi). L'ordine in cui sono visualizzati non cambia; l'ordinale. L'indice viene preso dall'ordine in cui vengono visualizzati nella coda della scheda nome nella finestra del foglio di lavoro.

Mentre è raro rinominare un .CodeName, non è impossibile. Basta aprire la finestra delle proprietà di VBE ([F4]).

Il foglio di lavoro .CodeName si trova nella prima riga. Il foglio di lavoro. Nome è nel decimo. Entrambi sono modificabili.

Utilizzo di stringhe con delimitatori al posto di matrici dinamiche

L'utilizzo di matrici dinamiche in VBA può essere piuttosto complesso e richiedere tempo per set di dati molto grandi. Quando si memorizzano tipi di dati semplici in una matrice dinamica (stringhe, numeri, booleani ecc.), È possibile evitare le istruzioni ReDim Preserve richieste per gli array dinamici in VBA utilizzando la funzione Split() con alcune procedure di stringa intelligenti. Ad esempio, esamineremo un ciclo che aggiunge una serie di valori da un intervallo a una stringa in base a determinate condizioni, quindi utilizza quella stringa per popolare i valori di un controllo 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

La stringa Delimiter stessa può essere impostata su qualsiasi valore, ma è prudente scegliere un valore che non si verificherà naturalmente all'interno dell'insieme. Supponi, ad esempio, di elaborare una colonna di date. In tal caso, usando . , - , o / sarebbe imprudente come delimitatore, in quanto le date potrebbero essere formattate per utilizzare uno qualsiasi di questi, generando più punti di dati di quanto previsto.

Nota: Esistono limitazioni all'utilizzo di questo metodo (ovvero la lunghezza massima delle stringhe), quindi dovrebbe essere usato con cautela in caso di set di dati molto grandi. Questo non è necessariamente il metodo più veloce o più efficace per la creazione di array dinamici in VBA, ma è un'alternativa valida.

Evento doppio clic per forme Excel

Per impostazione predefinita, le forme in Excel non hanno un modo specifico per gestire clic singoli o doppi, contenente solo la proprietà "OnAction" per consentire all'utente di gestire i clic. Tuttavia, potrebbero esserci casi in cui il codice richiede all'utente di agire in modo diverso (o esclusivo) con un doppio clic. La seguente subroutine può essere aggiunta al tuo progetto VBA e, se impostata come routine OnAction per la tua forma, ti consente di agire con doppio clic.

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

Questa routine farà in modo che la forma ignori funzionalmente il primo clic, eseguendo solo il codice desiderato al secondo clic entro l'intervallo di tempo specificato.

Finestra di dialogo Apri file - Più file

Questa subroutine è un rapido esempio su come consentire a un utente di selezionare più file e quindi fare qualcosa con quei percorsi di file, come ad esempio ottenere i nomi dei file e inviarli alla console tramite 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