Doodle.com

http://www.doodle.com/ : Comment trouver une date, pour une réunion de travail, un déjeuner d'affaires, une conférence téléphonique, un événement familial, une sortie cinéma ou tout événement de groupe ? Doodle.com vous permet une planification sans douleurs que vous soyez particulier ou entreprise. 
De plus vous pouvez connecter vos calendriers (Lotus notes, Google calendar,....) et les afficher sur vos iPhone et vos pages iGoogle...

Partager |

VBA : Compter les couleurs

Un de mes amis me demande, comment on pourrait cumuler le contenu de plusieurs cellules en fonction d'une couleur de fond appliquée à ces cellules ? Voici deux petites procédures VBA qui permettrons de résoudre facilement ce problème.

- 1 – Cette première procédure cumule simplement dans les cellules C1 à c5 les valeurs contenues dans des cellules de même couleur entre A1 et A10.

 Sub Cumul_couleur()
'déclaration des variables
Dim CouleurFond As Long
Dim LigneCumul As Long
Dim CouleurCumul As Long
Dim ligne As Long
'remise à zéro de cumuls précédent sans effacement de couleur
Range("c1:c5").ClearContents
For LigneCumul = 1 To 5
    Cells(LigneCumul, 3).Select
'récupération de la 1er couleur de cumul
    CouleurCumul = Selection.Interior.ColorIndex
    For ligne = 1 To 10
        Cells(ligne, 1).Select
        CouleurFond = Selection.Interior.ColorIndex
'récupération de la 1er couleur de fond
        If CouleurFond = CouleurCumul Then
'après comparaison des 2 couleurs on cumul
            Cells(LigneCumul, 3).Value = Cells(LigneCumul, 3).Value _
            + Cells(ligne, 1).Value
        End If
    Next ligne
Next LigneCumul
'Et on recommence
End Sub

- 2 – Dans ce second exemple nous appliquons une méthode identique mais à l'ensemble d'un tableau en comptant en plus le nombre de valeur par couleur.

Sub Cumul_Comptage_couleur()
'déclaration des variables
Dim CouleurFond As Long
Dim LigneCumul As Long
Dim CouleurCumul As Long
Dim ligne As Long
Dim col As Long
'remise à zéro de cumuls précédent sans effacement de couleur
Range("f1:f5").ClearContents
Range("h1:h5").ClearContents
For LigneCumul = 1 To 5
    Cells(LigneCumul, 6).Select
'récupération de la 1er couleur de cumul
    CouleurCumul = Selection.Interior.ColorIndex
    For ligne = 1 To 10
        For col = 1 To 4
            Cells(ligne, col).Select
            CouleurFond = Selection.Interior.ColorIndex
'récupération de la 1er couleur de fond
            If CouleurFond = CouleurCumul Then
'après comparaison des 2 couleurs on cumul
               Cells(LigneCumul, 6).Value = Cells(LigneCumul, 6).Value _
               + Cells(ligne, col).Value
' et maintenant on compte
               Cells(LigneCumul, 8).Value = Cells(LigneCumul, 8).Value + 1
              End If
        Next col
    Next ligne
Next LigneCumul
'Et on recommence
End Sub

Ces deux exemples seront aisément adaptables à vos tableaux…

 

Partager |

Petite et grande valeur

Comment déterminer la plus grande et la plus petite valeur d'une plage de cellules contenant des données numériques ? Facile me direz-vous il suffit d'utiliser les fonctions = max() et = min(), ces deux fonctions permettant effectivement de déterminer les maximas et le minimas de votre plage.
Mais comment faire si l'on veut connaître non pas la première grande valeur ou la première petite valeur mais la Kiéme valeur grande ou petite d'une plage ? Permettez moi de vous présentez la fonction :

=GRANDE.VALEUR( plage ; K )

Ou

=PETITE.VALEUR( plage ; K )

- 1 – Regardons la syntaxe de la fonction grande valeur par exemple les cellules A1:B10 contiennent les données et la fonction =max(A1:B10) saisie en E1 nous affiche la plus grande valeur de la plage (ici 1 9120,00). Utilisons maintenant la fonction :
=GRANDE.VALEUR (A1:B10;2) qui retournera la 2ème grande valeur de la plage dans la cellule A3.

- 2 – Recommençons avec la fonction petite valeur, la fonction =min(A1:B10) saisie en E3 nous affiche la plus petite valeur de la plage (ici -265,00). Utilisons maintenant la fonction :
=PETITE.VALEUR (A1:B10;2) qui retournera la 2ème petite valeur de la plage dans la cellule E4.

Partager |

Statistiques sur les filtres automatiques

Comment appliquer des fonctions statistiques aux résultats affichés par un filtre automatique ? Effectivement les fonctions classiques (SOMME(), MOYENNE(), MAX(), MIN()…) ne conviennent pas dans un tel cas puisqu'elles retournent un résultat sur l'ensemble des données et pas sur le résultat du filtre.
La solution passe par la maîtrise de la fonction =SOUS.TOTAL()

La syntaxe de la fonction sous.total() est :

=SOUS.TOTAL( fonction ; plage )

fonction désigne alors la fonction statistique que vous souhaitez appliquer aux résultats de votre filtre, elle est indiquée par un numéro
 compris entre 1 et 11, comme l'indique le tableau ci-dessous.
plage désigne l'adresse contenant les données à analyser.

Ainsi dans la seconde image ci contre :
La fonction :

=SOUS.TOTAL( 9 ; b2:b11 )

saisie en B13, Nous permettra d'obtenir la somme des prix uniquement pour la région "Aquitaine"

Moyenne	1	101
Nb	2	102
Nbval	3	103
Max	4	104
Min	5	105
Produit	6	106
Ecartype	7	107
EcartypeP	8	108
Somme	9	109
Var	10	110
Var.P	11	111

Si vous indiquez le code sur 3 chiffres par exemple 109 pour somme et non 9 alors Excel comprendra qu'il doit ignorer d'éventuelles lignes masquées dans votre liste pour la réalisation de la statistique.

Partager |

Compter et compter distinctement !

Lorsque vous travaillez sur une liste de données Excel, comment connaître le nombre de valeurs distinctes contenu dans un champ ?
Par exemple dans le champ "Pays" ci-joint à combien de pays différents fait on référence ?

La fonction de comptage =NBVAL( plage ) peut vous donner le nombre total de lignes (ici 10), pour la formule =NBVAL(B2:B11).
La fonction =NB.SI( plage ; critère ) peut vous retourner le nombre de fois où un pays est présent, (ici 4) pour l'expression = NB.SI(B2:B11;"Espagne"), c'est justement à partir de cette fonction que se trouve une solution possible.

La formule  =SOMME( 1 / NB.SI(B2:B11;B2:B11) ) répond à cette question, la fonction =NB.SI( ) comptant ici les valeurs de sa propre matrice. Attention toutefois la valeur retournée étant une matrice, vous devez valider la formule par SHIFT + CTRL + Entrée.
On voit alors qu'il est fait référence à 5 pays différents dans ce champ, et la formule étant matriciel elle apparaît entre { }.
Merci à Michel, un anonyme internaute pour m'avoir suggéré la réponse à ce problème, sur laquelle je butais depuis longtemps.

Partager |

VBA : Les différents types de données

Comment choisir le bon type de données lors de la création de vos variables ? Voici un tableau rappelant toutes les informations techniques nécessaires à un choix éclairé

Type	contenu	Plage de valeurs
Byte	Valeurs binaires	0 à 255 Taille en octets : 1
Boolean	Valeurs logiques	True ou False Taille en octets : 2
Currency	Utiliser pour les valeurs monétaires. 4 chiffres à droite du séparateur décimale et 15 à gauche maximum	+ / - 9E14 Taille en octets : 8
Date	Informations de date et heure	1er janvier 100 au 31 décembre 9999 Taille en octets : 8
Double	Nombre en virgule flottante (double précision)	+ / - 5E-324 à 1,8 Taille en octets : 8
Integer	Entiers	-32 768 à 32 767 Taille en octets : 2
Long	Entiers	+ / - 2E09 Taille en octets : 4
Object	N'importe quelle référence d'objet
Single	Nombre en virgule flottante (simple précision)	+ / - 1E-45 à 3E38 Taille en octets : 4
String	Texte	Longueur fixe : 1 à 65 400 Longueur variable : 0 à E09
Variant	N'importe lequel des types précédents	Nombres : cf double Caractères : cf texte à longueur variables

Partager |

VBA : Affecter une macro à un objet Spin ou ScrollBar

Dernière vidéo de la série macro commande ! J'explique ici comment placer et paramétrer un objet toupie ou barre de défilement dans le but de gérer l'incrémentation d'un compteur...

Partager |

VBA : Affecter une macro commande à un groupe d'options

Cette vidéo nous explique dans un premier temps, comment placer et paramétrer un groupe d'options puis comment placer et paramétrer des boutons d'options à l'intérieur du groupe avant de leurs affecter une macro-commande...

Partager |

VBA : Affecter une macro à une liste déroulante

Cette vidéo nous explique dans un premier temps, comment placer et paramétrer l'objet liste déroulante d'un formulaire Excel, puis ensuite comment lui affecter une macro-commande...

Partager |

VBA : Affecter une macro à une case à cocher

Cette vidéo nous explique dans un premier temps, comment placer et paramétrer l'objet case à cocher d'un formulaire Excel, puis ensuite comment lui affecter une macro-commande...

Partager |

VBA : Affecter une macro à un bouton de commande

Dans les semaines qui viennent je vais parler de  l'affectation des macro-commandes aux objets de formulaire Excel (dans la feuille de calcul). Cette première vidéo traite de l'objet bouton de commande....

Partager |

VBA : Affecter une macro à la barre d'accés rapide

Voyons dans cette vidéo, comment nous pouvons affecter une macro-commande à un bouton de la barre d'outils d'accès rapide. Cela s'avèrera fort utile pour les macro-commandes multi-classeur...

Partager |

VBA : Modifier une macro

Poursuivons notre tour d'horizon des macro commandes. Une fois la macro achevée il peut être utile voir indispensable de la modifier, pour cela il faut accéder à son code VBA et commencer à se familiariser avec le langage...

Partager |

VBA : Exécuter & supprimer une macro

 

Notre macro commande étant enregistrée, procédons maintenant à son exécution...

Partager |

VBA : Enregistrer une macro commande

Maintenant que nous savons naviguer dans l'interface de programmation, cette vidéo nous montre à travers un exemple très simple, comment on peut enregistrer une macro commande.

Partager |

VBA : Interface de développement

Suite aux deux derniers articles, je vais durant quelques semaines aborder les éléments de bases de la programmation VBA en insistant plutôt sur la notion de macro commandes. Cette semaine découvrons déjà l'environnement de travail du programmeur...
Ah oui j'oubliai, je vais le faire sous la forme de vidéo.

Partager |

VBA : La boucle TANT QUE

La seconde structure répétitive utilisée en algorithmie est la boucle TANT QUE, vous devez la choisir à chaque fois que le nombre d'itérations vous est inconnu au départ et que vous souhaitez évaluer votre condition en entrant dans la boucle. Sa notation générale pourrait s'écrire comme cela :
TANT QUE Condition FAIRE
    Action(s)
Fin de TANT QUE
Voyons comment utiliser cette structure en langage VBA.

- 1 - Dans ce premier exemple nous demandons à l'utilisateur de saisir un nombre entier qui sera stocké dans un tableau. La saisie s'arrêtera lorsque l'utilisateur répondra N à la question "voulez vous entrez un autre nombre ?" Ainsi on n'ignore au départ le nombre d'entier qui sera saisie (cela dépend de l'utilisateur).

Sub MaBoucle_1()
Dim MonTableau(100) As Integer
Dim reponse  As String
Dim x As Integer
x = 1
Do While reponse <> "N"
    MonTableau(x) = InputBox("Entrez un nombre entier ?", "Bonjour", 0)
    x = x + 1
    reponse = UCase(InputBox("Voulez vous rejouer o/n ?", "Bonjour", "O"))
Loop
End Sub

- 2 - Bien évidement l'une des actions contenue dans la boucle doit être susceptible de modifier la condition d'entrée dans la boucle, ainsi dans ce deuxième exemple (surtout ne le tester pas) nous courons droit au désastre puisque rien ne viendra jamais modifier la valeur de la variable reponse.

Sub MaBoucle_2()
Dim MonTableau(100) As Integer
Dim reponse  As String
Dim x As Integer
x = 1
Do While reponse <> "N"
    MonTableau(x) = InputBox("Entrez un nombre entier ?", " ", 0)
    x = x + 1
Loop
End Sub

- 3 - Il existe une autre syntaxe VBA pour l'écriture de la boucle tant que, dans ce troisième exemple nous allons colorier les cellules de la feuille de calcul à l'aide des 57 valeurs possibles de la propriété Interior.ColorIndex

Sub MaBoucle_3()
Dim nbCouleur As Byte
Dim ligne As Byte
nbCouleur = 0
ligne = 1
While ligne <= 57
    Range("A" & ligne).Select
    Selection.Interior.ColorIndex = nbCouleur
'changeons l'index de couleur et incrémentons la ligne pour espérer sortir de la boucle
    nbCouleur = nbCouleur + 1
    ligne = ligne + 1
Wend
End Sub

Partager |

VBA : La boucle POUR

La première structure répétitive utilisée en algorithmie est la boucle POUR, vous devez la choisir à chaque fois que le nombre d'itérations est connu au départ. Sa notation générale pourrait s'écrire comme cela :

POUR Compteur variant de Min à Max FAIRE

 Action(s)

Fin de POUR

Voyons comment utiliser cette structure en langage VBA.

- 1 - Dans ce premier exemple nous allons effectuer la multiplication par 7 d'un entier x variant de 1 à 10, et écrire le résultat dans la colonne A de notre feuille de calcul grâce à la fonction cells(ligne, colonne) :

Sub Ma_boucle_1()

Dim x As Integer

For x = 1 To 10

Cells(x, 1) = x * 7

Next x

End Sub

L'emploi de la variable x derrière l'instruction Next est facultative. Vous noterez que je n'ai pas initialisé la variable x au préalable, cette initialisation se fait en affectant la valeur 1 en entrée de boucle.

- 2 - Dans ce deuxième exemple nous allons préciser la valeur de l'incrément grâce à l'instruction Step (faute de quoi l'incrément est forcément de 1) :

Sub Ma_boucle_2()

Dim x As Integer

Dim y As Integer

y = 0

For x = 1 To 10 Step 3

y = y + 1

Cells(y, 1) = x * 7

Next

End Sub

La variable y me permet de bien écrire sur la ligne suivante à chaque itération, autrement j'écrirai une ligne sur 3.

- 3 - A l'inverse, il peut également y avoir décrémentation de la variable, faisons la table de 7 à l'envers...

Sub Ma_boucle_3()

Dim x As Integer

Dim y As Integer

y = 1

For x = 10 To 1 Step -1

Cells(y, 1) = x * 7

y = y + 1

Next x

End Sub

Attention, si vous ne précisez pas la valeur de décrément, la boucle ne fonctionnera pas.

- 4 - Pour finir un grand classique : L'imbrication de deux boucles POUR. Ici nous souhaitons créer une table avec tous les multiplicateurs de 1 à 10, nous devons donc imbriquer deux boucles pour gérer les écritures en lignes et en colonnes.

Sub table_multiplication()

Dim x As Integer

Dim y As Integer

Dim col As Integer

Dim lig As Integer

Cells(1, 1).Select

Cells(1, 1) = "*"

With Selection

.Font.Bold = True

.Font.Size = 16

.Font.ColorIndex = 3

.HorizontalAlignment = xlCenter

End With

'Ecrivons les entêtes de colonnes

For y = 1 To 10

Cells(1, y + 1).Select

Cells(1, y + 1) = y

With Selection

.Font.Bold = True

.Font.Size = 16

.Font.ColorIndex = 3

.HorizontalAlignment = xlCenter

End With

Next

'Ecrivons les entêtes de lignes

For x = 1 To 10

Cells(x + 1, 1).Select

Cells(x + 1, 1) = x

With Selection

.Font.Bold = True

.Font.Size = 16

.Font.ColorIndex = 3

.HorizontalAlignment = xlCenter

End With

Next

'nous devons connaitre les coordonnées de départ

Range("B2").Select

lig = Selection.Row

col = Selection.Column

For x = 1 To 10 Step 1

For y = 1 To 10 Step 1

Cells(lig, col) = x * y

col = col + 1

'il faut ramener le curseur en début de ligne

If col = 12 Then

col = 2

End If

Next y

lig = lig + 1

Next x

End Sub 

Partager |

VBA : Les constantes

Les constantes sont des emplacements de mémoires réservés pour le traitement de vos procédures, mais contrairement aux variables leurs contenus ne seront pas modifiés  au cours de l’exécution du programme. Peut employés elles sont pourtant fort utile pour simplifier l’écriture du code informatique…
A l’instar des variables il faudra les déclarer à l’aide du mot clé CONST puis les initialiser, la déclaration du type étant facultatif.

Const nom (as Type) = valeur 

- 1 – Dans ce premier exemple nous voyons que l’écriture du deuxième Msgbox est facilitée par l’emploie des constantes pi et message, mais il faut aussi considérer qu’il suffirait par exemple de modifier la valeur de la constante pi pour que cette valeur se modifie dans l’ensemble de votre programme ou vous y faites référence. Ainsi le passage de pi à la valeur 3.14 modifiera l’affichage du deuxième et troisième Msgbox, mais pas du premier. En limitant le nombre de saisie on limite évidement des erreurs éventuelles.

Sub ma_procedure_1()
          Const pi = 3.1415927
          Const message = "La valeur de Pi est : "
          Dim surface As Single
          Dim rayon As Single
          rayon = 2
          MsgBox "La valeur de Pi est : 3,1415927"
          MsgBox message & pi
          surface = pi * rayon * rayon
          MsgBox "Surface = " & surface & " M²"
End Sub

- 2 – Les procédures ont également une portée, ainsi dans ce deuxième exemple le résultat du calcul est nul car les constantes pi et messages sont locales à la  procédure « ma_procedure_1() »

Sub ma_procedure_2()
            Dim surface2 As Single
            Dim rayon2 As Single
            rayon2 = 1
            MsgBox "La valeur de Pi est : 3,1415927"
            MsgBox message & pi
            surface2 = pi * rayon2 * rayon2
            MsgBox "Surface = " & surface2 & " M²"
End Sub

Il faut donc dans ce cas déclarer les constantes comme locales à la feuille de code (globales aux procédures de cette feuille) de là manière suivante :

            Const pi = 3.1415927
            Const message as String = "La valeur de Pi est : "
___________________________________________________________
Sub ma_procedure_1()
            Dim surface As Single
            Dim rayon As Single
            rayon = 2
            MsgBox "La valeur de Pi est : 3,1415927"
            MsgBox message & pi
            surface = pi * rayon * rayon
            MsgBox "Surface = " & surface & " M²"
End Sub
____________________________________________________________
Sub ma_procedure_2()
Dim surface2 As Single
Dim rayon2 As Single
            rayon2 = 1
            MsgBox "La valeur de Pi est : 3,1415927"
            MsgBox message & pi
            surface2 = pi * rayon2 * rayon2
            MsgBox "Surface = " & surface2 & " M²"
End Sub

- 3 - Par contre si votre procédure se trouve sur une autre feuille de code (module de feuille ou de classeur) alors une déclaration publique dans le module générale de votre feuille de code est nécessaire. C'est-à-dire que vous rendez vos constantes utilisables dans l’ensemble du projet Visual Basic.

            Public Const pi = 3.1415927
            Public Const message As String = "La valeur de Pi est : "

- 4 - Enfin sachez que les programmeurs VBA aiment à utiliser les constantes pour la simplification de l’écriture des boîtes de dialogues, notamment du Msgbox. Comparez les deux procédures suivantes, vous constaterez que la seconde sera plus simple à modifier, surtout si l’on doit utiliser de nombreuses fois cette Msgbox.
Sub ma_procedure_3()
Dim choix As String
            choix = MsgBox("Aimez vous ce blog ? ", vbDefaultButton2 + vbYesNo + vbQuestion, "olivier-picot.fr")
            Select Case choix
                        Case Is = vbYes
                                    Exit Sub
                        Case Is = vbNo
                                    Exit Sub
            End Select
End Sub
_________________________________________________________________
Sub ma_procedure_4()
            Dim choix As String
            Const invite As String = "Aimez vous ce blog ? "
            Const typeboite As String = vbDefaultButton2 + vbYesNo + vbQuestion
            Const titre As String = "olivier-picot.fr"
            choix = MsgBox(invite, typeboite, titre)
            Select Case choix
                        Case Is = vbYes
                                    Exit Sub
                        Case Is = vbNo
                                    Exit Sub
            End Select
End Sub 

Partager |