excel-vba
Trucs et astuces Excel VBA

Remarques

Utiliser les feuilles xlVeryHidden

Les feuilles de calcul dans Excel ont trois options pour la propriété Visible . Ces options sont représentées par des constantes dans l'énumération xlSheetVisibility et sont les suivantes:

1. xlVisible ou xlSheetVisible : -1 (valeur par défaut pour les nouvelles feuilles)
2. xlHidden ou xlSheetHidden : 0
3. xlVeryHidden xlSheetVeryHidden : 2

Les feuilles visibles représentent la visibilité par défaut des feuilles. Ils sont visibles dans la barre des onglets et peuvent être librement sélectionnés et visualisés. Les feuilles masquées sont masquées dans la barre des onglets et ne peuvent donc pas être sélectionnées. Cependant, les feuilles masquées peuvent être masquées depuis la fenêtre Excel en cliquant avec le bouton droit de la souris sur les onglets de la feuille et en sélectionnant "Afficher".

Les feuilles très cachées, par contre, ne sont accessibles que via Visual Basic Editor. Cela en fait un outil extrêmement utile pour stocker des données sur des instances d'Excel et stocker des données qui doivent être masquées pour les utilisateurs finaux. Les feuilles peuvent être consultées par référence nommée dans le code VBA, ce qui permet une utilisation facile des données stockées.

Pour modifier manuellement la propriété .Visible d'une feuille de calcul en xlSheetVeryHidden, ouvrez la fenêtre Propriétés de VBE ( F4 ), sélectionnez la feuille de calcul à modifier et utilisez la liste déroulante de la treizième ligne pour effectuer votre sélection.

Pour modifier la propriété .Visible d'une feuille de calcul en xlSheetVeryHidden¹ dans le code, accédez de la même manière à la propriété .Visible et affectez une nouvelle valeur.

with Sheet3
    .Visible = xlSheetVeryHidden
end with

¹ _{xlVeryHidden et xlSheetVeryHidden renvoient une valeur numérique de 2 (elles sont interchangeables).}

Feuille de calcul .Name, .Index ou .CodeName

Nous savons que la «meilleure pratique» dicte qu'un objet de plage doit avoir sa feuille de travail parente explicitement référencée. Une feuille de calcul peut être désignée par sa propriété .Name, sa propriété .Index numérique ou sa propriété .CodeName, mais un utilisateur peut réorganiser la file d'attente de la feuille de calcul en faisant simplement glisser un onglet de nom ou en double-cliquant sur le même onglet et taper dans un classeur non protégé.

Considérons une feuille de travail standard trois. Vous avez renommé les trois feuilles de calcul lundi, mardi et mercredi dans cet ordre et codé les sous-procédures VBA qui font référence à celles-ci. Considérez maintenant qu'un utilisateur arrive et décide que le lundi appartient à la fin de la file d'attente de la feuille de calcul, tandis qu'un autre intervient et décide que les noms des feuilles de calcul ont l'air mieux en français. Vous avez maintenant un classeur avec une file d'attente d'onglets de nom de feuille de calcul qui ressemble à ce qui suit.

Si vous aviez utilisé l'une des méthodes de référence de feuille de calcul suivantes, votre code serait désormais rompu.

'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

L'ordre d'origine et le nom de la feuille de calcul d'origine ont été compromis. Toutefois, si vous aviez utilisé la propriété .CodeName de la feuille de calcul, votre sous-procédure serait toujours opérationnelle

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

L'image suivante montre la fenêtre Projet VBA ([Ctrl] + R) qui répertorie les feuilles de calcul par .CodeName puis par .Name (entre parenthèses). L'ordre dans lequel ils sont affichés ne change pas; l'ordinal .Index est pris par l'ordre dans lequel ils sont affichés dans la file d'attente de l'onglet Nom dans la fenêtre de la feuille de calcul.

Bien qu'il soit rare de renommer un nom de code, ce n'est pas impossible. Ouvrez simplement la fenêtre Propriétés de VBE ([F4]).

La feuille de calcul .CodeName est dans la première ligne. La feuille de calcul .Name est dans le dixième. Les deux sont modifiables.

Utilisation de chaînes avec des délimiteurs à la place des tableaux dynamiques

L'utilisation de tableaux dynamiques dans VBA peut s'avérer très fastidieuse et prendre du temps sur des ensembles de données très volumineux. Lorsque vous stockez des types de données simples dans un tableau dynamique (chaînes, nombres, booléens, etc.), vous pouvez éviter les ReDim Preserve requises pour les tableaux dynamiques dans VBA en utilisant la fonction Split() avec certaines procédures de chaîne intelligentes. Par exemple, nous allons examiner une boucle qui ajoute une série de valeurs d'une plage à une chaîne basée sur certaines conditions, puis utilise cette chaîne pour renseigner les valeurs d'un 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 chaîne Delimiter elle-même peut être définie sur n'importe quelle valeur, mais il est prudent de choisir une valeur qui ne se produira pas naturellement dans l'ensemble. Disons, par exemple, que vous traitiez une colonne de dates. Dans ce cas, utiliser . , - , ou / serait imprudent en tant que délimiteurs, car les dates pourraient être formatées pour utiliser l’une quelconque de celles-ci, générant plus de points de données que prévu.

Remarque: L' utilisation de cette méthode (à savoir la longueur maximale des chaînes) est limitée, elle doit donc être utilisée avec précaution dans le cas de jeux de données très volumineux. Ce n'est pas nécessairement la méthode la plus rapide ou la plus efficace pour créer des tableaux dynamiques dans VBA, mais c'est une alternative viable.

Evénement Double Click pour les formes Excel

Par défaut, les formes dans Excel n'ont pas de moyen spécifique pour gérer les clics simples et doubles, contenant uniquement la propriété "OnAction" pour vous permettre de gérer les clics. Cependant, il peut arriver que votre code vous oblige à agir différemment (ou exclusivement) en double-cliquant. Le sous-programme suivant peut être ajouté à votre projet VBA et, lorsqu'il est défini comme routine OnAction pour votre forme, vous permet d'agir sur les clics doubles.

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

Cette routine fera que la forme ignorera fonctionnellement le premier clic, exécutant uniquement le code souhaité sur le deuxième clic dans la période spécifiée.

Boîte de dialogue Ouvrir un fichier - Fichiers multiples

Ce sous-programme est un exemple rapide sur la manière de permettre à un utilisateur de sélectionner plusieurs fichiers, puis de faire quelque chose avec ces chemins, par exemple obtenir les noms de fichiers et les envoyer à la console 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