| auteur : Bidou |
C'est un appel implicite à la méthode Evaluate. Il s'agit en fait d'un interpréteur (le même que celui qui interprète les
formules Excel). On peut faire des utilisations très complexes de cette méthode.
Par exemple:
Vba |
MaValeur = Application. Evaluate (" LOG(A1*3+2) " )
|
Cela peut s'écrire aussi:
Notez qu'il n'y a pas de guillemets lorsqu'on utilise la notation crochets. L'interprétation des plages,
dans notre cas A1, demande que le système de référence soit homogène. Si ma feuille était en référence LC j'obtiendrais
une incompatibilité de type.
|
| auteur : Bidou |
Il faut utiliser les formats de cellules.
Vba |
objSheet. UsedRange . NumberFormat = " 0;-0;;@ "
|
|
| auteur : SilkyRoad |
Sélectionnez toutes les cellules de la feuille.
Effectuez un Clic droit.
Sélectionnez l'option "format de cellule" dans le menu contextuel.
Sélectionnez l'onglet Protection.
Décochez l'option "Verrouillée".
Cliquez sur le bouton OK pour valider.
Ensuite, sélectionnez uniquement la plage de cellules que vous souhaitez protéger.
Effectuez un Clic droit.
Sélectionnez l'option "format de celllule" dans le menu contextuel.
Sélectionnez l'onglet Protection.
Cochez l'option "verrouillée".
Cliquez sur le bouton OK pour valider.
Ensuite, utilisez le menu Outils /Protection / Protéger la feuille .
Assurez vous que l'option "protéger la feuille et le contenu des cellules verrouillées" est cochée.
Saisissez votre mot de passe (en option).
Cliquez sur le bouton OK pour valider.
|
| auteur : SilkyRoad |
Des erreurs peuvent parfois apparaître dans les formules de calcul.
L'erreur peut être la conséquence d'une saisie erronée, mais peut parfois aussi être inévitable: Exemple des formules dans un tableau
de bord prérempli et complété à chaque fin de mois. Les cellules contenant des formules pour les prochains mois peuvent logiquement
renvoyer une erreur car les données ne sont pas encore renseignées.
Excel dispose de plusieurs outils qui permettent d'identifier et gérer ces erreurs. Il est intéressant de connaître la signification
les codes d'erreur, de cerner la cause et pouvoir ainsi trouver une solution corrective plus facilement.
Consultez l'article sur la gestion des erreurs.
|
| auteur : Bidou |
Ce n'est pas toujours une nécessité, mais cela permet d'accélérer le code. Sur les grandes collections,
il convient d'éviter l'énumération qui est très lente.
Comparez la rapidité d'exécution du code entre
Vba |
Dim ObjCell As Range
For Each ObjCell In Range (" A1:J65535 " ). Cells
ObjCell. Value = ObjCell. Value * 2 + 3
Next
|
et
Vba |
Dim Montab As Variant, cmpt1 As Long, cmpt2 As Long
Montab = Range (" A1:J65535 " ). Value
For cmpt1 = LBound (Montab, 1 ) To UBound (Montab, 1 )
For cmpt2 = LBound (Montab, 2 ) To UBound (Montab, 2 )
Montab (cmpt1, cmpt2) = Montab (cmpt1, cmpt2) * 2 + 3
Next cmpt2
Next cmpt1
Range (" A1:J65535 " ). Value = Montab
|
Le deuxième code s'exécute environ 20 fois plus vite.
|
| auteur : Bidou |
Il existe globalement deux types de recopies. La recopie incrémentée (AutoFill) et la non-incrémentée. Une recopie
incrémentée consiste à recopier une plage en incrémentant une des propriétés suivantes
* xlFillDefault : laisse excel choisir l'incrément
* xlFillSeries : Recopie avec les paramètres de la série si ceux-ci existent
* xlFillCopy : Sans incrément (utilisez plutôt la méthode Fill)
* xlFillFormats : Recopie le format sans les valeurs
* xlFillValues : Utilise l'incrément par défaut, mais ne recopie pas les formules
* xlFillDays : Incrémente les jours
* xlFillWeekdays : Incrémente les noms de jours
* xlFillMonths : Incrémente les mois
* xlFillYears : Incrémente les années
* xlLinearTrend : Tendance linéaire
* xlGrowthTrend : Tendance géométrique
Evidemment la recopie doit pouvoir trouver la valeur de l'incrément, il faut donc que la source contiennent au moins deux cellules.
Vba |
Range (" F29:F30 " ). AutoFill Destination:= Range (" F29:F43 " ), Type:= xlGrowthTrend
Cells (29 , 6 ). Resize (2 ). AutoFill Destination:= Cells (29 , 6 ). Resize (10 ), Type:= xlFillDefault
|
La recopie non incrémentée utilise une des méthodes Fill directionnelles: FillDown, FillUp, FillLeft, FillRight.
Il faut que la cellule opposée au sens contienne une valeur ou une formule.
Vba |
Cells (29 , 6 ). Resize (10 ). FillDown
|
|
| auteur : SilkyRoad |
Cet exemple extrait la liste des noms contenus dans la Feuil1.
Vba |
Dim Plage As Range
Dim Nm As Name
On Error Resume Next
For Each Nm In ThisWorkbook. Names
Set Plage = Nm. RefersToRange
If Not Plage Is Nothing Then
If Worksheets (" Feuil1 " ). Name = Plage. Worksheet . Name Then _
MsgBox Nm. Name & " : " & Plage. Address
End If
Set Plage = Nothing
Next Nm
|
|
| auteur : SilkyRoad |
Sélectionnez la plage de cellules.
Appuyez sur la touche F5 du clavier.
Cliquez sur le bouton "Cellules".
Sélectionnez "Constantes".
Cochez le ou les types de données à supprimer (texte, nombre ...).
Cliquez sur le bouton "Ok".
Les cellules correspondantes sont sélectionnées.
Vous pouvez alors appuyer sur la touche "Suppr" du clavier pour supprimer le contenu des cellules tout en conservant
les formules dans la plage initialement sélectionnée.
La même solution par macro:
Vba |
Range (" A1:A10 " ). SpecialCells (xlCellTypeConstants, 3 ). ClearContents
|
|
| auteur : SilkyRoad |
Pour insérer la date du jour rapidement, sélectionnez une cellule puis utilisez le raccourci clavier
Ctrl + ; (Touche Ctrl et le point virgule).
Pour insérer l'heure, utilisez le raccourci clavier Ctrl + : (Touche Ctrl et les deux points).
|
| auteur : SilkyRoad |
Sélectionnez la ou les cellules à paramétrer.
Ensuite, utilisez le menu Données/Validation.
Sélectionnez l'onglet Options.
Dans le champ Autoriser:, sélectionnez la ligne "Longueur du texte".
Dans le champ Données:, sélectionnez la ligne "inférieure ou égale à".
Dans le champ Longueur:, précisez le nombre de caractères à autoriser dans les cellules sélectionnées.
Cliquez sur le bouton OK pour valider.
Désormais, un message d'alerte s'affiche lorsque vous saisissez un nombre de caractères supérieur à la valeur paramétrée.
|
| auteur : Bidou |
On utilise les validations comme par le menu. L'exemple suivant restreint les valeurs saisissables de G1, entre 10 et 100
Vba |
With Range (" G1 " ). Validation
. Add Type:= xlValidateWholeNumber, AlertStyle:= xlValidAlertStop, _
Operator:= xlBetween, Formula1:= " 10 " , Formula2:= " 100 "
. IgnoreBlank = True
. InCellDropdown = True
. ErrorMessage = " La valeur doit être comprise entre 10 et 100 "
. ShowInput = True
. ShowError = True
End With
|
|
| auteur : Ouskelnor |
UsedRange (premier exemple).
Cet exemple envoie le nombre de lignes renseignées dans la feuille, où qu'elles soient.
Si les lignes 1 à 10 et 17 à 19 sont vides, mais que les lignes 11 à 16 et 20 à 24 sont renseignées, le résultat est 11 (6 + 5).
Vba |
Dim NbLignes As Integer
NbLignes = ActiveSheet. UsedRange . Rows . Count
|
Nota:
Pensez à adapter le type de variable utilisé.
Dans l'exemple précédent, un message d'erreur (dépassement de capacité) survient si la procédure renvoie plus de 32767 lignes.
UsedRange (2eme exemple).
Renvoie le numéro de la première ligne renseignée dans la feuille.
Vba |
Dim LaPremiereLigne As Integer
LaPremiereLigne = ActiveSheet. UsedRange . Row
|
CurrentRegion (premier exemple).
Cette propriété Définit une plage de toutes les cellules contiguës, délimitées par les premières lignes et colonnes vides.
Ici la procédure renvoie la dernière ligne de la Plage.
S'il n'y a pas de ligne vide dans la plage de données, que la cellule "Cel" se trouve dans la
plage de données, la macro renvoie la dernière ligne de la plage de données, non de la feuille.
Vba |
Dim DerniereLigne As Integer
DerniereLigne = Range (Cel). CurrentRegion . End (xlDown). Row
|
CurrentRegion (deuxième exemple).
Renvoie l'adresse de la plage des cellules contiguës:
Vba |
Dim strPlage As String
strPlage = Range (" A15 " ). CurrentRegion . Address (False , False )
|
Range(Col 65536).End(xlUp)
Renvoie la dernière ligne renseignée dans la colonne spécifiée.
Pour connaître le numéro de la dernière ligne absolue renseignée dans la colonne A, utilisez:
Vba |
Dim DerniereLigne As Integer
DerniereLigne = Range (" A65536 " ). End (xlUp). Row
|
Range("A1").End(xlDown)
Renvoie le numéro de la dernière ligne, avant la première cellule vide, en descendant à partir de la cellule spécifiée.
Vba |
Dim DerniereLigne As Integer
DerniereLigne = Range (" A1 " ). End (xlDown). Row
|
Un autre exemple qui renvoie le numéro de la dernière ligne, avant la première cellule vide de la colonne C,
en partant de la cellule C15:
Vba |
Dim DerniereLigne As Integer
DerniereLigne = Range (" C15 " ). End (xlDown). Row
|
Range(...).SpecialCells(xlCellTypeLastCell)
Renvoie la dernière ligne absolue non vide dans la feuille.
Attention: Si des lignes sont supprimées dans la plage, enregistrez préalablement le fichier pour que la
procédure renvoie la réelle dernière ligne (idem pour les colonnes supprimées).
Vba |
Dim DerniereLigne As Integer
DerniereLigne = Range (" A1 " ). SpecialCells (xlCellTypeLastCell). Row
|
Range(...).SpecialCells(xlCellTypeLastCell).Address
Renvoie l'adresse absolue de la dernière cellule renseignée dans la feuille:
Vba |
Dim AdresseAbsolue As String
AdresseAbsolue = Range (" A1 " ). SpecialCells (xlCellTypeLastCell). Address
|
Le principe est identique pour les colonnes:
Vba |
DerniereColonne = ActiveSheet. UsedRange . Columns . Count
DernièreColonne = Selection. CurrentRegion . End (xlToRight). Column
DerniereColonne = Range (" A1 " ). End (xlToRight). Column
DerniereColonne = Range (" IV1 " ). End (xlToLeft). Column
DerniereColonne = Range (" A1 " ). SpecialCells (xlCellTypeLastCell). Column
|
Identifier des plages de cellules.
Toutes les combinaisons sont possibles.
Exemple:
Vba |
Dim strPlage As String
strPlage = Range (Cells (1 ,1 ), Cells (Range (" A65536 " ). End (xlUp). Row , _
Range (" IV1 " ). End (xlToLeft). Column )). Address
|
Ou en tant qu'objet, avec une instance :
Vba |
Dim objPlage As Range
Set objPlage = Range (Cells (1 ,1 ), Cells (Range (" A65536 " ). End (xlUp). Row , _
Range (" IV1 " ). End (xlToLeft). Column ))
|
Vba |
Dim strPlage As String
srtPlage = Range (" A1: " & Range (" A1 " ). SpecialCells (xlCellTypeLastCell). Address ). Address
|
Pour sélectionner la plage, utilisez:
Vba |
Range (" A1: " & Range (" A1 " ). SpecialCells (xlCellTypeLastCell). Address ). select
|
|
| auteur : SilkyRoad |
Pour transformer une plage verticale de cellules sous forme de plage horizontale:
Sélectionnez votre tableau.
Effectuez un "Copier".
Sélectionnez ensuite une cellule vide sous le tableau.
Menu Edition/Collage spécial
Cochez l'option "Transposé" dans la boîte de dialogue.
Cliquez sur le bouton OK pour valider.
|
| auteur : SilkyRoad | Vba |
Sub Test ()
Deplace Range (" K20 " )
End Sub
Sub Deplace (Cellule As Range)
Dim Ligne As Integer, Colonne As Integer
Ligne = Cellule. Row
Colonne = Cellule. Column
With ActiveWindow
. ScrollRow = Ligne
. ScrollColumn = Colonne
End With
End Sub
|
|
| auteur : SilkyRoad |
Cet exemple applique une couleur jaune dans les cellules fusionnées de la feuille nommée Feuil1.
Vba |
Sub VisualiserCellulesFusionnees ()
Dim Cell As Range
For Each Cell In Worksheets (" Feuil1 " ). UsedRange . Cells
If Cell. MergeCells Then Cell. MergeArea . Interior . ColorIndex = 6
Next Cell
End Sub
|
|
| auteur : SilkyRoad |
La propriété d'identification des dépendants fonctionne uniquement sur la feuille active et ne peut pas suivre les références
distantes (sur les autres feuilles).
La procédure suivante recherche les dépendants d'une cellule dans toutes les feuilles du classeur.
Nota:
Cet exemple ne gère pas les cellules et les plages nommées.
Vba |
Option Explicit
Sub Test ()
ListeDependents Worksheets (" Feuil2 " ). Range (" D4 " )
End Sub
Sub ListeDependents (Cellule As Range)
Dim Ws As Worksheet
Dim Plage As Range, Cell As Range, DirectDep As Range
Dim i As Integer, x As Integer
Dim Cible As String , strDepenDent As String , strRefer As String
Dim Un As New Collection
Cellule. Parent . Activate
strDepenDent = Cellule. Parent . Name & " ! " & Cellule. Address (0 , 0 )
On Error Resume Next
Set Plage = Cellule. DirectDependents . Cells
On Error GoTo 0
If Not Plage Is Nothing Then
For Each DirectDep In Cellule. DirectDependents . Cells
Un. Add Cellule. Parent . Name & " ! " & DirectDep. Address , _
Cellule. Parent . Name & " ! " & DirectDep. Address
Next DirectDep
End If
Set Plage = Nothing
For Each Ws In ThisWorkbook. Worksheets
If Ws. Name < > Cellule. Parent . Name Then
On Error Resume Next
Set Plage = Ws. UsedRange . SpecialCells (xlCellTypeFormulas)
On Error GoTo 0
If Not Plage Is Nothing Then
For Each Cell In Plage
Cible = Replace (Cell. Formula , " $ " , " " )
If InStr (1 , Cible, Cellule. Parent . Name ) > 0 Then
i = 0
i = InStr (1 , Cible, strDepenDent)
If i > 0 And Not IsNumeric (Mid (Cible, i + Len (strDepenDent), 1 )) Then
Un. Add Ws. Name & " ! " & Cell. Address , Ws. Name & " ! " & Cell. Address
Else
For x = 1 To Len (Cible)
i = 0
i = InStr (1 , Cible, " : " )
If i > 0 Then
strRefer = ExtractionReferences (Cible)
If VerifIntersect (Cellule, Range (strRefer)) And _
InStr (1 , Cible, Cellule. Parent . Name & " ! " & strRefer) > 0 Then
On Error Resume Next
Un. Add Ws. Name & " ! " & Cell. Address , Ws. Name & " ! " & Cell. Address
On Error GoTo 0
Exit For
End If
Cible = Mid (Cible, i + 1 )
Else
Exit For
End If
Next x
End If
End If
Next Cell
End If
End If
Set Plage = Nothing
Next Ws
For i = 1 To Un. Count
Debug. Print Un. Item (i)
Next i
End Sub
Function ExtractionReferences (Chaine As String ) As String
Dim i As Integer, j As Integer
Dim strPlage As String , Caract As String
i = InStr (1 , Chaine, " : " )
For j = i - 1 To 1 Step - 1
Caract = Mid (Chaine, j, 1 )
Select Case Asc (Caract)
Case 48 To 57 , 65 To 90 , 97 To 122
strPlage = Caract & strPlage
Case Else : Exit For
End Select
Next j
strPlage = strPlage & " : "
For j = i + 1 To Len (Chaine)
Caract = Mid (Chaine, j, 1 )
Select Case Asc (Caract)
Case 48 To 57 , 65 To 90 , 97 To 122
strPlage = strPlage & Caract
Case Else : Exit For
End Select
Next j
ExtractionReferences = strPlage
End Function
Function VerifIntersect (objDepend As Range, objReference As Range) As Boolean
Dim objRange As Range
Set objRange = Intersect (objDepend, objReference)
If objRange Is Nothing Then
VerifIntersect = False
Else
VerifIntersect = True
End If
End Function
|
|
| auteur : SilkyRoad |
Utilisez simultanément sur les touches clavier Ctrl+Maj+Fin.
Vous remarquerez que la dernière cellule est parfois au-delà de la plage de données utilisée. Supprimer tous ces
lignes et colonnes, entre cette cellule et la plage de données utile, permet:
* De ne pas imprimer des pages vides supplémentaires.
* De libérer de l'espace mémoire et donc de diminuer sensiblement la taille de votre classeur.
|
| auteur : DarkVader |
Cet exemple renvoie alternativement Vrai/Faux dans la cellule A1, toutes les secondes.
Vba |
Option Explicit
Private Sub Workbook_Open ()
GetTempo
End Sub
Private Sub Workbook_BeforeClose (Cancel As Boolean)
Application. OnTime EarliestTime:= Now , Procedure:= " GetTempo " , Schedule:= False
End Sub
|
Vba |
Option Explicit
Dim vTempo As Boolean
Sub GetTempo ()
vTempo = Not vTempo
Application. OnTime Now + TimeValue (" 00:00:01 " ), " GetTempo "
[Feuil1!A1] = vTempo
End Sub
|
|
| auteur : Cafeine |
Vous devez activer la référence Microsoft DA0 3.6 Object Library pour
utiliser la bibliothèque DAO:
Dans l'éditeur de macros,
Menu Outils
Références
Cochez la ligne "Microsoft DA0 3.6 Object Library".
Cliquez sur le bouton OK pour valider.
Vba |
Sub DoCmdRunSQL (ByVal sql As String , ByVal rDest As Range)
Dim db As DAO. Database
Dim rs As DAO. Recordset
Set db = DAO. OpenDatabase (ActiveWorkbook. FullName , False , False , " Excel 8.0;HDR=YES; " )
Set rs = db. OpenRecordset (sql, DAO. dbOpenSnapshot )
rDest. CopyFromRecordset rs
Set rs = Nothing
Set db = Nothing
End Sub
|
Un exemple d'appel de la procédure:
Vba |
DoCmdRunSQL " SELECT * FROM [feuil1$A3:D600] WHERE age > 18 " , sheets (" Résultat " ). range (" A1 " )
|
La table s'écrit [NomOnglet$PlageCellules].
Dans cet exemple, le résultat de la requête est affiché dans la cellule A1 de la feuille "Résultat".
|
lien : Plus d'informations sur la Bibliothèque DAO
|
| auteur : SilkyRoad |
Sélectionner la (les) cellule(s).
Menu Données.
Validation
Dans la liste déroulante "Autoriser:", choisissez "Personnaliser".
Dans le champ "Formule:", saisissez =""
Cliquez sur le bouton OK pour valider.
Nota:
Dans Excel2007, le menu validation est accessible depuis l'onglet "Données" dans le ruban. Ensuite cliquez sur le bouton
"Validation de données" dans le groupe "Outils de données".
|
| auteur : SilkyRoad | Vba |
Sub Test ()
Dim Cible As Range
Set Cible = DerniereCellule (ActiveSheet)
If Not Cible Is Nothing Then
MsgBox Cible. Address & vbCrLf & Cible. Value
Else
MsgBox " La feuille est vide. "
End If
End Sub
Function DerniereCellule (Ws As Worksheet) As Range
Dim NumLigne As Double, NumCol As Integer
If Ws. UsedRange . Cells . Address = " $A$1 " And _
IsEmpty (Ws. Range (" A1 " )) Then Exit Function
With Ws
NumLigne = . Cells . Find (" * " , . Range (" A1 " ), , , xlByRows, xlPrevious). Row
NumCol = . Cells . Find (" * " , . Range (" A1 " ), , , xlByColumns, xlPrevious). Column
Set DerniereCellule = . Cells (NumLigne, NumCol)
End With
End Function
|
|
| auteurs : SilkyRoad, Bidou | Vba |
Dim Plage1 As Range
Set Plage1 = Range (" A1 " ). CurrentRegion
MsgBox Plage1. Parent . Name & " ! " & Plage1. Address
|
Remarque:
Parent renvoie Object, donc vous perdez l'intellisense et vous passez en liaison tardive.
Privilégiez l'utilisation de Worksheet qui renvoie un objet Worksheet. Donc vous gardez l'intellisense et en cas de pilotage
vous restez en liaison précoce:
Vba |
Dim Plage1 As Range
Set Plage1 = Range (" A1 " ). CurrentRegion
MsgBox Plage1. Worksheet . Name & " ! " & Plage1. Address
|
|
Consultez les autres F.A.Q's
Les sources présentés sur cette pages sont libre de droits,
et vous pouvez les utiliser à votre convenance. Par contre cette page de présentation de ces sources constitue une oeuvre intellectuelle protégée par les droits d'auteurs.
Copyright ©2008
Developpez LLC. Tout droits réservés Developpez LLC.
Aucune reproduction, même partielle, ne peut être faite de ce site et de
l'ensemble de son contenu : textes, documents et images sans l'autorisation
expresse de Developpez LLC. Sinon vous encourez selon la loi jusqu'à 3 ans
de prison et jusqu'à 300 000 E de dommages et intérets.
Cette page est déposée à la SACD.
|