VBA : Supprimer des lignes en fonction d’une valeur contenue dans un champ (2).

L’un des lecteurs (Jean-Noël) de ce modeste blog (qu’il en soit remercié) me demande une précision au sujet de l’article « Supprimer des lignes en fonction d’une valeur contenue dans un champ ». En effet, il souhaiterai  pouvoir effacer les lignes non pas en fonction d’une correspondance exacte de la valeur du champ mais simplement si la valeur se trouve dans l’expression globale de ce champ. J’ai donc modifié quelque peu mon code…

- 1 –  Considérons ici la liste (A1 :E24), je souhaite supprimer toutes les lignes pour lesquels l’éditeur contient le mot Microsoft.

- 2 – Voici à quoi pourrait ressembler le code, à vous de l’adapter ! J’ai également récupérer le nombre de lignes effacées

Sub DelEditeur3()
Dim i As Integer
Dim lig As Integer
Dim Editeur As String
lig = 0
Editeur = InputBox("Veuillez entrer l'editeur à supprimer ?", "Welcome", "Microsoft")
'La valeur saisie est transmise à la variable Editeur
With ThisWorkbook.Sheets("Feuil1")
  For i = .Range("C" & .Rows.Count).End(xlUp).Row To 2 Step -1
If InStr(.Range("C" & i).Value, Editeur) <> 0 Then
'la fonction InStr(chaîne1, chaîne2) permet de vérifier la présence de chaîne2 dans
‘chaîne1'et retourne l'occurrence de la première position de la comparaison donc pas 0
‘si chaîne2 est présente dans chaîne1
Rows(i).Delete
lig = lig + 1
'Comptons les lignes effacées
End If
Next i
  MsgBox "J'ai effacer " & lig & " ligne(s) contenant l'expression : "  _
& Editeur, vbOKOnly + vbInformation, "INFORMATION"
'Affichons le nombre de lignes effacées
End With
End Sub

- 3 – Et voici le résultat après exécution de la procédure DelEditeur3()…

Partager |

VBA : Créer une macro multi feuilles

Voici un point particulier sur lequel il faut noter une sérieuse différence avec les versions antérieures d’Excel. Effectivement pour être multi-feuilles une macro doit être enregistrée dans le fichier PERSONNAL.XLSB (cf article précédent). Voici comment procéder sur un exemple très simple.

 
- 1 – Ouvrir une nouvelle feuille de calcul dans Excel et passez la commande Développeur / Enregistrer une macro

• Attribuer un nom de la macro
  
• Choisissez éventuellement une Touche de raccourci pour son Exécution clavier
  
• Choisissez impérativement l’option Classeur de macros personnelles dans la liste Enregistrer la macro dans :
  
• Cliquez sur Ok
  

-2 – Procéder à l’enregistrement de votre macro (Dans l’exemple j’ai enregistré un code pour imprimer automatiquement une feuille de calcul en 3 exemplaires). N’oubliez pas de cliquez sur le bouton Arrêter l’enregistrement lorsque vous avez terminé.
Automatiquement le code enregistré ira se copier dans une feuille de module de code du projet PERSONNAL.XLSB

Si vous ne possédez pas l’onglet développeur : Cliquez sur le Bouton Office / Options Excel / Standard et cochez l’option Afficher l’onglet développeur dans le ruban

- 3 – Pour voir le code de votre macro faites ALT + F11 ou Développeur / Visual Basic. Le code se trouve bien dans une feuille de module de code du projet PERSONAL.XLSB.

 Sub Impression_3_EX()
' Impression_3_EX Macro
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
End Sub

- 4 –Nous allons ajouter un bouton à la barre d’outils Accès rapide pour une exécution plus pratique de la macro.

• Faites clic / droit sur la barre d’outils Accès rapide
  
• Choisissez l’option Personnaliser la barre d’outils Accès rapide

 

• Dans la liste des catégories de commande, choisissez Macros
  
• Sélectionnez la macro dans la liste et cliquez sur le bouton Ajouter >>, la macro apparaît dans la liste de droite
  
• Cliquez sur le bouton Modifier… pour sélectionner un autre symbole

 

• Ciquez sur le bouton Ok puis à nouveau sur Ok

L’exécution sera désormais possible sur n’importe quel classeur en utilisant le symbole ajouté sur la barre d’outils Accès rapide

-5 – A la fermeture du classeur n’omettez pas de confirmer l’enregistrement de la modification du fichier XLSB

Partager |

VBA : Créer une fonction

Une fonction est une procédure VBA particulière qui se caractérise par le fait qu’elle retourne une valeur à la feuille de calcul. Ainsi il sera possible d’enrichir la collection des fonctions d’Excel en créant des outils utilisables dans n’importe quel classeur.

 
- 1 –Ouvrir une nouvelle feuille de calcul dans Excel et entrez les valeurs suivantes dans les cellules A1 à C3. (Les valeurs en rouge sont obtenues par les fonctions VBA décrites ci-dessous).

- 2 – Accédez ensuite à votre éditeur Visual Basic ALT + F11 ou Développeur / Visual Basic. Vous devrez saisir le code des fonctions dans une feuille de module de code du projet personal.xlsb.

 - 3 -Voici à quoi pourrait ressembler le code des deux fonctions, à vous de l’adapter éventuellement !

Function PerimetreCercle(rayon)
'rayon est la valeur transmise de la feuille de calcul à la fonction PerimetreCercle()
' Calculons le périmètre du cercle
  PerimetreCercle = 2 * Application.Pi() * rayon
'Application.Pi() retourne la valeur 3.1415927....'
PerimetreCercle est la valeur retourné par la fonction
End Function

Function SurfaceCercle(rayon)
' Calculons la surface du cercle
SurfaceCercle = Application.Pi() * rayon ^ 2
'le symbole ^ élève à la puissance
End Function

- 4 – Retournez à votre feuille de calcul, vous devez à présent utiliser vos nouvelles fonctions pour effectuer vos calculs en B2 et B3. Pour nous faciliter la tâche il suffit de savoir que ces fonctions apparaissent maintenant dans l’assistant de fonctions.

• Mettez votre curseur en B2.
• Passez la commande Formules / Insérer une fonction et choisissons la catégorie « Personnalisées », la liste des fonctions de personal.xlsb apparaît.
• Sélectionnez maintenant la fonction de nôtre choix
• Cliquez sur Ok
• Sélectionnez la cellule B1 pour obtenir la valeur du rayon
• Cliquez sur Ok
• Recommencez pour le calcul de la cellule B3

Partager |

VBA : Contrôle de validité dans un formulaire

Cette semaine voyons comment programmer simplement divers contrôles de validité sur les objets Textbox d’un formulaire (saisie obligatoire, type de données, longueur de chaîne…). Différentes fonctions de type String nous serons d’un grand secours…

-1 - Réalisons d’abord le formulaire VB suivant :

 
-2 – Puis affectons la macro OpenForm() à  un bouton de commande « Annuler » de la feuille de calcul pour ouvrir le formulaire. Le code a été saisi dans une feuille de module de code (Module 1)

Sub OpenForm()
'Rendre toutes les zones vide
FormTest.TextBox1.Text = ""
FormTest.erreur1.Caption = ""
FormTest.TextBox2.Text = ""
FormTest.erreur2.Caption = ""
FormTest.TextBox3.Text = ""
FormTest.erreur3.Caption = ""
FormTest.TextBox4.Text = ""
FormTest.erreur4.Caption = ""
FormTest.TextBox5.Text = ""
FormTest.TextBox6.Text = ""
FormTest.erreur6.Caption = ""
'avant l'ouverture du formulaire
  FormTest.Show
End Sub

-3 – Programmons maintenant par sécurité le  bouton de commande SORTIR du formulaire

Private Sub Sortir_Click()
'Fermons le formulaire en cas de click sur le bouton Annuler
FormTest.Hide
End Sub

- 4 – Premier contrôle très simple pour savoir si l’utilisateur a bien renseigné ce textbox. 

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'L'événement Exit se déclenche lorsque le focus sort de la zone de texte
If TextBox1.Text = "" Then
erreur1.Caption = "Vous n'avez pas rempli ce champ"
'Un simple message pour attirer l'attention de l'utilisateur
Else
erreur1.Caption = ""
End If
End Sub

-5 – Convertissons maintenant une saisie en MAJUSCULE

Private Sub TextBox2_Change()
'L'événement Change se déclenche lorsque la valeur de la zone de texte est modifiée
TextBox2.Text = UCase(TextBox2.Text)
'ou LCase() pour mettre en minuscule
erreur2.Caption = "Conversion réussie"
End Sub

- 6 – Vérifions la longueur d’une chaîne, ici 5 caractères

 Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Len(TextBox3.Text) = 5 Then
'Len() retourne la longueur d'une chaîne de texte
erreur3.Caption = "Cette zone contient bien 5 caractères"
Else
erreur3.Caption = "Cette zone ne contient pas 5 caractères mais " _
  & Len(TextBox3.Text) & "caractère(s)"
End If
End Sub

- 7 – Vérifions que la saisie est bien de type texte et qu’il ne s’agit pas d’un nombre ou d’une date.

 Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If IsNumeric(TextBox4.Text) Or IsDate(TextBox4.Text) Then
'isNumeric() et isDate() renvoie VRAI si la zone contient un nombre ou une date
erreur4.Caption = "Ceci n'est pas un texte"
Else
erreur4.Caption = "Ceci est  un texte"
End If
End Sub

- 8 – Associons dans la même procédure les 4 contrôles précédents

Private Sub TextBox5_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox5.Text = "" Or Len(TextBox5.Text) <> 5 _
Or IsNumeric(TextBox5.Text) Or IsDate(TextBox5.Text) Then
       MsgBox "Veuillez remplir cette zone avec" _
 & Chr(10) & "un texte de 5 caractères"
'Chr() simule un touche clavier ici le retour ligne
Else
TextBox5.Text = UCase(TextBox5)
MsgBox "Ceci est  un texte de 5 caractères" _
& Chr(10) & "converti en majuscule"
End If
End Sub

 
- 9 – Dernier exemple, vérifions si une chaîne 1 est comprise dans une chaîne 2, ici nous voulons savoir si la valeur 1 ou 2 se trouve en première  position de la saisie

Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If InStr(1, TextBox6.Text, 1) Or InStr(1, TextBox6.Text, 2) Then
'InStr(départ, chaîne, comparaison) permet de savoir si comparaison est présent
' dans chaîne à partir de la position départ
erreur6.Caption = "La saisie commence par le chiffre 1 ou 2"
Else
erreur6.Caption = "La saisie ne commence pas par le chiffre 1 ou 2"
End If
End Sub

Partager |

VBA : Automatiser le passage de données d’Excel vers Word

Comment peut-on prendre des données sur une feuille de calcul Excel (tableau ou graphique) et automatiser leur exportation dans un document Word ? Cela est possible en combinant astucieusement VBA Word et VBA Excel.

 
- 1 – Sur une nouvelle feuille de calcul, créez un tableau de données, (ici A1:D8) puis à partir de ce tableau créez le graphique de votre choix.

- 2 – Accédez ensuite à votre éditeur Visual Basic ALT + F11 ou Développeur / Visual Basic. Pour pouvoir manipuler les objets ou les collections d’objets de Word, nous devons nous assurer que la bibliothèque qui les contient est active dans votre éditeur Visual Basic. Passez la commande F2 ou Affichage / Explorateur d’objets et vérifier sa présence dans la liste des bibliothèques d’objets.

 
- 3 – Si la bibliothèque Word est absente de la liste, passez la commande Outils / Références… et cochez la référence Microsoft Word 12.0 Object Library pour activer la bibliothèque des objets Word. Puis validez en cliquant sur Ok.

 
- 4 – Voici à quoi pourrait ressembler le code, à vous de l’adapter !

Sub Passage_Excel_Word()
Dim appWord As New Word.Application
Dim docWord As New Word.Document

' Il faut créer un nouveau document Word dans l'application Word
With appWord
 .Visible = True
Set docWord = .Documents.Add
  .Activate
End With

'Dans Word on ajoute une ligne de titre avec une mise en forme
With appWord.Selection
  .TypeText Text:="Chiffre d'affaire 2003"
 .HomeKey Unit:=wdLine
 .EndKey Unit:=wdLine, Extend:=wdExtend
.ParagraphFormat.Alignment = wdAlignParagraphCenter
  .Font.Size = 18
  With .Font
.Name = "Arial"
.Size = 16
.Bold = True
  End With

'Copier le tableau Excel dans le presse papier
  Range("a1:b8").Copy

' Coller le tableau dans Word avec liaison
.EndKey Unit:=wdLine
  .TypeParagraph
  .PasteSpecial Link:=True, DataType:=wdPasteOLEObject, _
Placement:=wdInLine, DisplayAsIcon:=False

'Copier le graphique Excel dans le presse papier
  ActiveSheet.ChartObjects(1).Activate
  ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy

'Coller le graphique dans Word
   .TypeParagraph
   .Paste
End With

'Enregistrer le document Word
With docWord
.SaveAs  ThisWorkbook.Path & "\ca_2003.doc",  Allowsubstitutions:=True

'Dans Word Aperçu avant impression du résultat
   .PrintPreview

'Réinitialiser l'objet
Set appWord = Nothing
End With
End Sub

- 5 – après exécution du code vous devriez avoir le document suivant en Aperçu avant impression dans Word.

Partager |