VBA : Les ActiveX de contrôle des dates

Dans cette article je souhaite expliquer comment utiliser les contrôles ActiveX "Microsoft Monthview "et "Microsoft DTPicker", puisqu'ils ne sont plus fournis en natif dans les versions Excel 2013 et 2016. Dans une première partie nous développerons la récupération et l'installation de ces contrôles, puis leurs utilisations pour contrôler efficacement une saisie de date dans une feuille de calcul ou dans un formulaire VBA.

Installation des contrôles

 

En premier lieu, mettons en place les contrôles "Microsoft Monthview" et "Microsoft DTPicker", le fichier MSCOMCT2.OCX contient les contrôles en question, ainsi que les contrôles UpDown, ScrollBar et Animation. Il faut commencer par télécharger ce fichier, car il n'existe probablement pas sur votre ordinateur (si vous utilisez Excel 2013 ou 2016) vous pouvez le télécharger à l'adresse suivante : http://www.ocxme.com/ocx/files/mscomct2_ocx.html
Vous devez ensuite copier le fichier dans le dossier c:\Windows\system32 si vous avez une version 32 bits de Windows, ou dans le dossier c:\windows\sysWOW64\ en cas de version 64 bits.
Il faut à présent Exécuter le fichier OCX, pour cela activer l'invite de commande en mode Administrateur :  Utiliser la commande CMD dans votre commande Démarrer / Exécuter ou dans la zone de recherche selon votre version de Windows. Attention de bien valider la saisie à l'aide des touches SHIFT+ CTRL + Entrée du clavier pour bien basculer l'invite de commande en mode Administrateur.

La ligne de commande à saisir, sera alors :

Pour windows 32 bits : %systemroot%\System32\regsvr32 c:\windows\System32\MSCOMCT2.OCX
Pour windows 64 bits : %systemroot%\SysWoW64\regsvr32 c:\windows\SysWOW64\MSCOMCT2.OCX

Utilisation des contrôles dans Excel : Saisir une date dans une cellule

 

• Accéder à l'onglet Développeur du Ruban
• Utilisez la commande Insérer / contrôles ActiveX / Autres contrôles
• Sélectionnez le contrôle "Microsoft Date and Time Picker control 6.0 (SP6)"

 

• Dessinez l'objet en glissant + Positionnement et redimensionnement

Attention la commande Mode Création du ruban Développeur est alors active, pour faire le test de fonctionnement, vous devrez d'abord la désactiver.

• Quelques réglages de bases possibles grâce au menu contextuel de l'Objet

• Pour affectez le choix utilisateur à une cellule, utilisez la commande Propriétés du  Menu contextuel, 

• Renseignez la propriété LinKedCell

 

Utilisation des contrôles en VBA : Saisir une date dans un formulaire

 

Nous allons en premier lieu ajouter les contrôles dans la boite à Outils :

• Créez un formulaire VBA

• Affichez la boîte à outils

• Faites clic droit pour obtenir le menu contextuel de la boîte

• Sélectionner alors la commande Contrôle Supplémentaires…

• Dans la liste des contrôles Choisissez "Microsoft MonthView control 6.0 (SP6)" et "Microsoft Date and Time control 6.0 (SP6)"

 

• Cliquez sur OK
• Dessiner les 2 objets MonthView1 et DTPicker1 sur votre formulaire grâce aux nouveaux contrôles apparaissant dans la boîte à outils.

Ici le code VBA nous montre comment affecter le choix de l'utilisateur dans un contrôle ou l'autre, à une cellule de la feuille de calcul.

Private Sub CommandButton1_Click()
     Range("a10").Value = DTPicker1.Value
     Range("a11").Value = MonthView1.Value
     UserForm1.Hide
End Sub


Merci de votre attention…

Partager |

VBA : Modifier la casse à l'aide du clic droit

La casse est un terme utilisé dans les domaines de la typographie et de l'informatique pour   distinguer une alternative entre les lettres écrites en capitales (MAJUSCULE) et les lettres écrites en minuscule. En l’absence d'utilisation des fonctions de traitement de chaînes de caractères :

1. =MAJUSCULE( chaîne de caractères )
2. =MINUSCULE( chaîne de caractères )
3. =NOMPROPRE( chaîne de caractères) - 1^er lettre de chaque mot de la chaîne de caractères en majuscule

Actuellement, votre logiciel Excel ne permet pas de modifier la casse des mots contenus dans les cellules d'une feuille de calcul (ni commande, ni touche de raccourci clavier). Aussi je vous propose ce code VBA permettant la bascule entre les trois positions. La difficulté ici ne réside pas dans la modification de la casse d'un contenu sélectionné mais plutôt dans le choix de l'affectation de ce code en vue de son exécution. Afin de rendre son utilisation la plus aisée possible, j'ai choisi ici d'utiliser le menu contextuel, c'est à dire de reprogrammer le menu surgissant du bouton droit de la  souris. Pour que cette application puisse être utilisable quelque soit le classeur que vous manipulez, vous avez le choix d'incorporer votre code soit dans un classeur de macros personnelles, soit dans un classeur de macros complémentaires.

 

 

 

 

 

 

 

Première étape : Modifier le menu contextuel des cellules.

 

Les procédures sont évidement stockées dans le module ThisWorkbook du projet VBA, j'ai ici choisi d'utiliser mon classeur (.xlsb) de macros personnelles.

Sub resetmenu()
          Application.CommandBars("cell").Reset
 End Sub
'**********************************************************
Private Sub Workbook_Open()
resetmenu
'appel la macro qui effacera la commande du menu contextuel
'crée lors de la session précédente

    Dim Cpop1 As CommandBarPopup
    Dim Cbut As CommandBarButton
    Dim MaBarre As CommandBar
    Set MaBarre = Application.CommandBars("cell")
    Set Cpop1 = MaBarre.Controls.Add(msoControlPopup)
    'on ajoute le popup qui va comporté tout les boutons
    With Cpop1
        .Caption = "Modifier la casse"    'label du sous-menu
    End With
    'on ajoute un bouton au sous-menu contextuel
    Set Cbut = Cpop1.Controls.Add(Type:=msoControlButton)
    With Cbut
        .FaceId = 403    ' bouton avec icône (référence à une table)
        .Caption = "MAJUSCLE"    ' label du bouton
        .OnAction = "majuscule"    'appel de la macro associée
    End With
    'on répéte l'opération pour les deux autres boutons
    Set Cbut = Cpop1.Controls.Add(Type:=msoControlButton)
    With Cbut
        .FaceId = 404
        .Caption = "minuscule"
        .OnAction = "minuscule"
    End With
    Set Cbut = Cpop1.Controls.Add(Type:=msoControlButton)
    With Cbut
        .FaceId = 306
        .Caption = "Nom Propre"
        .OnAction = "nompropre"
    End With
End Sub

Etape 2 : Écrire les trois macros pour la gestion de la casse

Les procédures sont stockées dans une feuille de module de code du projet VBA, du même classeur (.xlsb) de macros personnelles.

Dim Ch As Range
Sub majuscule()
  For Each Ch In Selection
  'Pour chaque cellule Ch de ma sélection Selection
  'Selection permet de renvoyer l'objet sélectionné dans la fenêtre active
    If Not Ch.HasFormula Then
    'la propriété HasFormula de l'objet range permet de tester qu'aucune
    ' cellules de la plage ne contiennent de formule, dans ce cas la conversion est ignorée
      Ch.Value = UCase(Ch.Value) 'Upper Case = MAJUSCULE
    End If
  Next Ch
End Sub
'*************************************************************
Sub minuscule()
  For Each Ch In Selection
    If Not Ch.HasFormula Then
      Ch.Value = LCase(Ch.Value) 'Lower Case = minuscule
    End If
  Next Ch
End Sub

'*************************************************************
 Sub nompropre()
  For Each Ch In Selection
    If Not Ch.HasFormula Then
      Ch.Value = Application.Proper(Ch.Value)
      'si une fonction Excel n'a pas d'équivalent en VBA
      'utiliser le la fonction Excel (anglais) comme une méthode de l'objet application
    End If
  Next Ch
End Sub

Merci de votre attention...

Partager |

VBA : En 2016, Quelles nouveautés ?

De retour après quelques mois d'absences, permettez-moi d'abord chères lecteurs et chères lectrices de vous présenter mes meilleurs vœux pour l'année 2016. Pour ma part je ferais simplement le vœu que ce blog puisse continuer à vous être utile tout au cours de cette nouvelle année.

Je commencerai donc dans ce premier article par une webographie succincte, me permettant ainsi de vous présenter quelques nouveautés qui pourraient s'avérer utiles pour vos développements et autres codages….  

 

 

 

Nouvelle version d'Office : 

 

 Office 2016 est disponible depuis la fin du mois de septembre, et se décline actuellement en deux gammes de produits :

• Sur Office 365 (Famille 5 postes 99 € par an, Personnel 1 poste 69 € par an),
• En version 2016 (Famille et étudiant 1 poste 149 € par an, Professionnel 539 €)

Mon propos n'est pas ici de vous présenter l'ensemble des nouveautés de cette version 2016, le journal du net à réaliser un excellent diaporama à ce sujet. Il est clair dans les deux cas que les prix ont très sensiblement augmentés. Par contre pour les développeurs VBA vous pouvez noter les points suivants :

• Accès à une API Javascript complète sous Windows et Mac
• De nouveaux objets requêtes et WorkbookQuery pour accélérer et faciliter l'interrogation des bases de données.
• Des objets comme ModelFormatboolean ou ModelFormatCurrency permettront de fixer un format déclaratif pour des booléens ou des nombres
• Enfin sachez que de nombreuses nouveautés concernent en fait les versions Mac, notamment la personnalisation du ruban via XML

Nouvelle version de MZ-Tools :

Au mois de Mai, j'avais rédigé un article détaillé pour vous présenter l'intérêt à utiliser ce logiciel. Une nouvelle version (8.0) est disponible pour les développeurs Visual Basic, VBA, Visual C# et VB.NET. MZ-Tools 8.0 for VBA, est compatible avec la nouvelle mouture d'Office (2016) et avec les versions 64bits d'office ce qui n'était pas le cas jusqu'à présent, de plus il est possible de l'installer en l'absence de droit d'administration. Télécharger cette nouvelle version sur le site de MZ-Tools.





Merci de votre attention

Partager |

VBA : L’objet Fichier système

Comment manipuler les fichiers positionnés sur votre disque dur, à l’aide du langage VBA. L’utilisation de l’objet "FileSystemObject" va nous permettre de régler cette question. Dans cet exemple nous allons pouvoir :

• Supprimer un fichier
• Renommer un fichier
• Déplacer un fichier
• Ouvrir un fichier

Évidement pour pouvoir réaliser ces actions il faudra au préalable trouver sur le disque le fichier qui nous intéresse. Donc rechercher un fichier sera notre première action.

Nous saisissons dans la zone de texte "TextBox1" du formulaire "UserForm2" le nom d’un fichier Excel (sans l’extension), puis nous cliquons sur le bouton "Find". Le système parcours ici tous les sous-dossiers d’un dossier "Blog" d’un disque "E:".

Si le fichier est localisé ; son Path (chemin d’accès) va s'afficher dans la zone de label "Label1" située en dessous, l'utilisateur choisira alors l'action qu'il désire accomplir.

Il sera nécessaire de créer 2 objets, un pour l'accès au système de fichiers Windows (scripting.FileSystemObject) et un autre objet de type dossier pour récupérer notre dossier racine par la méthode GetFolder. Vous noterez également l'emploi de la fonction Dir() de la classe FileSystem qui permet la récupération du nom du fichier dans une chaîne de type Path ou une chaîne vide en cas d'absence. Le reste du code ne semble pas poser de difficultés.

'-----------------------------------------------------------------------

Option Explicit

Public fichier As String

Public fichier2 As String

Public Flder As Object

Public comp As String

 Const boite = vbOKOnly + vbInformation

 Const titre = "www.olivier - picot.fr"

 '-----------------------------------------------------------------------

Private Sub Find_Click()

Dim fso As Object

Dim Dossier As Object

Dim LeDossier As String

Dim chemin As String

comp = TextBox1.Value

LeDossier = "E:\Blog"

Set fso = CreateObject("scripting.FileSystemObject")

Set Dossier = fso.getfolder(LeDossier)

'Examen de chaque sous dossier du dossier E:\Blog

For Each Flder In Dossier.subfolders

    fichier = Dir(Flder.Path & Application.PathSeparator & comp & ".xlsx")

    If fichier <> "" Then 'donc si on l'a trouvé

        chemin = Flder.Path & Application.PathSeparator

        Exit For

    End If

Next Flder

If fichier = "" Then

    MsgBox "le fichier n'existe pas", vbInformation + vbOKOnly, "ERREUR"

Else

    fichier = chemin & comp & ".xlsx"

    UserForm2.Label1.Caption = fichier

End If

End Sub

 '-----------------------------------------------------------------------

Private Sub Ouvrir_Click()

Workbooks.Open fichier

MsgBox "Fichier ouvert", boite, titre

End Sub

 '-----------------------------------------------------------------------

Private Sub Renommer_Click()

fichier2 = Flder.Path & Application.PathSeparator & "copie_" & comp & ".xlsx"

    Name fichier As fichier2

    MsgBox "Fichier rennomé", boite, titre

End Sub

 '-----------------------------------------------------------------------

Private Sub Supprimer_Click()

 Kill fichier

 MsgBox "Fichier supprimé", boite, titre

End Sub

 '-----------------------------------------------------------------------

Private Sub Deplace_Click()

Name fichier As "E:\SNCF\" & comp & ".xlsx"

MsgBox "Fichier déplacé", boite, titre

End Sub

 '-----------------------------------------------------------------------

Private Sub sortir_Click()

 UserForm2.Hide

End Sub

Vous pourrez très facilement adapter cet exemple,

Merci de votre attention

Partager |

VBA : Connaissez-vous MZ-Tools ?

Comme l’on constaté tous les développeurs VBA, les possibilités de gestion du code offertes par l’éditeur (Visual Basic Editor) de Microsoft sont extrêmement réduites.  Les fonctionnalités de l’éditeur peuvent être grandement améliorés par l’implémentation de logiciels divers et variés. Je vous présente ici l’excellent logiciel espagnol, MZ-Tools 3.0 for VBA. Ce logiciel est compatible avec les dernières versions d’Excel 2010 et 2013 (32-bit). Il fonctionne sous toutes les versions de Windows, y compris Windows 8. Précisons qu’il s’agit d’un logiciel totalement en FreeWare, aussi en cas d’utilisation professionnelle une donation est toujours possible.

Vous pourrez le télécharger à l’adresse http://www.mztools.com/v3/download.aspx

Voici les principales fonctionnalités amélioratrices offertes par le logiciel, de quoi se poser sérieusement deux questions :

1. Comment on faisait avant ?
2.  Microsoft ne pourrait ’il pas repenser sérieusement son éditeur VBE ?

• Amélioration de la fonction Rechercher / Remplacer qui peut être transversale à plusieurs projets
• Gestion du suivi des appels de Procédures
• Création de modèles de code pour stocker des extraits de code que vous insérez fréquemment dans votre code source. Pour insérer un modèle de code, vous pouvez le sélectionner dans une liste ou lui affecter un raccourci
• Amélioration de la commande Insertion / Procédure…
• Assistant pour la saisie des instructions de type Select Case…End Select avec appel par raccourci
• Numérotation de Ligne : Vous pouvez ajouter ou supprimer des numéros de ligne à une procédure, module, … simplement à l’aide du menu contextuel.
• Création possible de 9 « presse papiers » privés pour conserver et réutiliser des extraits de code.

• Un assistant de création de la boîte de dialogue MsgBox évitant une saisie fastidieuse des paramètres.

• Outils de modification de la casse des caractères dans le code, MAJ / min
• Assistant de gestion de l’ordre des tabulations sur les objets de formulaires

• Copier /  coller des contrôles de formulaires avec leur Code intégré.
• Renommer des contrôles entraine automatiquement le renommage de l’objet correspondant dans le code d’où un gain de temps considérable et une sérieuse diminution du nombre d’erreurs.
• Demande automatique de saisie du nom et de la légende d’un contrôle lorsque vous l’ajouter dans votre formulaire.
• Outils de détection dans le code source des variables et constantes inutilisées, pour amélioration de la performance.
• Effacement automatique de la fenêtre d’exécution
• Fermez toutes les fenêtres ouvertes dans l’éditeur sauf éventuellement la fenêtre active.

Cette liste est loin d’être exhaustive, je me suis concentré sur les éléments me paraissant particulièrement intéressant pour un gain de productivité, il y a aussi une gestion possible des tâches d’un projet, des retours statistiques sur vos codes ... et bien d’autres fonctionnalités à découvrir.

Merci de votre attention

Partager |