Diverses astuces VBA

Ouverture et utilisation d'un classeur Excel 97 fermé

Dim XLFichier As Workbook

Set XLFichier = Workbooks.Open("D:\Atelier\zebre.xls")

MsgBox XLFichier.Worksheets(1).Range("a1").Value

Set XLFichier = Nothing

Revenir au point de départ

Cette astuce permet de sélectionner tout le tableau courant, mettre toutes ses cellules en gras, et revenir à l'endroit initial. Pour mettre en œuvre cette routine :

1. Remplir de X les cellules de A1 jusqu'à B5

2. Se placer sur B4

3. Exécuter la routine

Voilà : Toutes les cellules sont en gras.

Dim Origine As Range

Set Origine = ActiveCell

Selection.CurrentRegion.Select

Selection.Font.Bold = True

Origine.Select

Accès complet à une cellule

Application.WorkBooks("Annual Sales.xls").WorkSheets("Second Quarter").Range("B3").Value = 3

Internet

Il est possible d'aller fouiller dans des dossiers sur le web FTP. Par exemple, si nous désirons aller ouvrir un fichier sur le site FTP de microsoft, il faut faire

Fichier/Ouvrir, et taper dans la zone Fichier :

ftp://ftp.microsoft.com/ et ENTER

on entre alors sur ce site

Onglets

Sélectionner un onglet :

Worksheets("OngletA").Activate

Sélection Multizones

Il est possible de déterminer le nombre de zones non adjacentes que l'utilisateur a sélectionné :

MsgBox Selection.Areas.Count

Cette fonction renvoie au moins 1 (Au moins la cellule active est sélectionnée). Sinon, elle renvoie le nombre de zones

Voir également Le comptage de colonnes multizones

ActiveCell

Lorsqu'on sélectionne une plage de cellules, c'est la cellule en haut à gauche qui retourne une valeur

EXEMPLE

si on sélectionne A3..F5, alors

MsgBox ActiveCell.Value

renverra la valeur de A3

Contenu des cellules

Une cellule vide n'est pas Null. Afin de constater qu'une cellule ne contient pas de valeur ni de texte, il faut faire :

If Not IsEmpty(ActiveCell.Value) Then

MsgBox "y'a kekchose"

End If

Compter le nombre de colonnes d'une sélection

ction.Columns.Count

Dans le 'une sélection multizones

Selection.Areas.Count <= 1 Then

MsgBox Selection.Columns.Count

For Ctr = 1 To areaCount

MsgBox "Zone N° " & Ctr & ". " & Selection.Areas(i).Columns.Count & " colonnes."

Next i

End If

Traiter une colonne entière

Cet exemple montre comment mettre en gras la police de la colonne 1 (colonne A) sur L'onglet OngletA.

Worksheets("OngletA").Columns(1).Font.Bold = True

Affecter une valeur dans une colonne de plage

Cet exemple montre comment affecter 5 à chaque cellule de la colonne 1 dans la plage nommée "maPlage".

Range("maPlage").Columns(1).Value = 5

Calculer

Calculate

Divers

Pour ne pas voir les différentes étapes de l'exécution d'un module VBA, il faut ajouter au début du module : application.MiseAJourEcran = Faux
Pour écrire une longue ligne de code en plusieurs lignes, on peut utiliser le trait de soulignement à la fin des lignes
Pour forcer la déclaration des variables, on peut indiquer Option ForceDcl avant la procédure
Le glossaire Français-anglais des mots réservés VBA se trouve dans ListeVBA.XLS

Visualisation de l'environnement VBA Excel

AffectationContenuCellule

Insertion d'une fonction

ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"

Selections

Range("Y3012").Select

Range("B2:c3").Select

Et si ça ne marche pas :

ActiveSheet.Range("A1").Select

Sélection d'un tableau entier

Selon qu'on désire une sélection absolue ou relative, la syntaxe est différente. Dans ce premier exemple, on imagine que la cellule active est A1. Alors donc, on sélectionne jusqu'à 3 lignes plus bas, et 3 colonnes plus à gauche :

ActiveCell.Range("A1:C4").Select

Dans ce 2ème exemple, il s'agit de références absolues :

Range("B14:D17").Select

Dans le cas ou on se trouve à l'angle en bas à droite d'un tableau et qu'on remonte dans le tableau, la syntaxe est plus complète :

ActiveCell.Offset(-3, -2).Range("A1:C4").Select

Dans ces exemples, j'ai utilisé CTRL SHIFT et flèches de direction. On constate que le tableau ne peut pas avoir une grandeur variable puisqu'apparemment Excel n'utilise pas les mots clés comme HOME ou END ou RIGHT ou LEFT, contrairement au déplacement simple (voir plus bas), ou ces mots clés sont utilisés par Excel.

Déplacement

la case la plus en bas	Selection.End(xlDown).Select
la case la plus à droite	Selection.End(xlToRight).Select
la case la plus à gauche	Selection.End(xlToLeft).Select
la case la plus haute	Selection.End(xlUp).Select
Une case vers le bas	ActiveCell.Offset(1, 0).Range("A1").Select
Une case vers le haut	ActiveCell.Offset(-1, 0).Range("A1").Select
Une case vers la gauche	ActiveCell.Offset(0, -1).Range("A1").Select
Une case vers la droite	ActiveCell.Offset(0, 1).Range("A1").Select

Récupérer l'information d'une cellule

Son simple contenu

ATTENTION : Si on omet ActiveSheet., alors on obtient des infos erronées.

ActiveSheet.Range("F1").Value

est largement préférables par rapport aux exemples suivants

MsgBox Range("Y3012").Value

Remarque : MsgBox Range("Y3012") suffit.

Des opérations sont évidemment possibles :

Range("f4") + Range("f5")

L'adresse 'une cellule nommée

MsgBox Range("CelluleNommée").Address

InputBox

Msg = "Entre le pourcentage d'un team-leader"

Title = "Pourcentage du team-leader"

Default = "30%"

Pourcent = InputBox(Msg, Title, Default)

Sheets("Terrain d'essais").Select

Range("B1").Select

ActiveCell.FormulaR1C1 = Pourcent

Imposer un contenu dans une cellule

Dans la cellule active	ActiveCell.FormulaR1C1 = "XXX"
Dans n'importe quelle cellule	Range("d6").Value = "XXX"
Dans une cellule nommée	Range("GainTotal").FormulaR1C1 = 99
Insertion d'un calcul	Range("GainTotal") = Range("B12") * 2

Imposer une formule dans une cellule

ATTENTION : Pour imposer une formule, il faut :

Utiliser Range("B5").Formula et PAS .Value
Utiliser le nom de fonction anglaise (Voir le tableau des traductions)
Utiliser des virgules à la place des points-virgules pour séparer les arguments

Exemple d'utilisation de RoundUp (Arrondi.Sup)

Range("B5").Formula = "=RoundUp(3.25,0)"

Dans le cas ou on veut récupérer la valeur ainsi obtenue :

Msgbox Range("B5").Value

(Le résultat est 4)

Voici un autre petit exemple avec des références :

Range("D4").Formula = "=SUM(D1:D3)"

Objet RANGE

Définition de la couleur de l'intérieur d'une sélection

Range("A3:b8").Interior.Color = 255

Barres d'outils

Cette procédure exemple remplit une feuille de calcul avec toutes les barres d'outils existantes avec toutes leurs propriétés

On Error GoTo FaireErreur

Dim BarreCommande As CommandBar

Dim Prop As PropertyTest

Range("A1").Value = "Application"

Range("B1").Value = "BuiltIn"

Range("C1").Value = "Context"

Range("D1").Value = "Controls"

Range("E1").Value = "Creator"

Range("F1").Value = "Enabled"

Range("G1").Value = "Height"

Range("H1").Value = "Index"

Range("I1").Value = "Left"

Range("J1").Value = "Name"

Range("K1").Value = "NameLocal"

Range("L1").Value = "Parent"

Range("M1").Value = "Position"

Range("N1").Value = "Protection"

Range("O1").Value = "RowIndex"

Range("P1").Value = "Top"

Range("Q1").Value = "Type"

Range("R1").Value = "Visible"

Range("S1").Value = "Width"

Range("A2").Select

For Each BarreCommande In CommandBars

ActiveCell.FormulaR1C1 = BarreCommande.Application