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




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





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.




top