excel-vba
Trucs et astuces Excel VBA
Recherche…
Remarques
Cette rubrique comprend une grande variété de trucs et astuces utiles découverts par les utilisateurs SO grâce à leur expérience en matière de codage. Ce sont souvent des exemples de moyens permettant de contourner les frustrations ou les façons d’utiliser Excel de manière plus "intelligente".
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:
-
xlVisible
ouxlSheetVisible
:-1
(valeur par défaut pour les nouvelles feuilles) -
xlHidden
ouxlSheetHidden
:0
-
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