FAQ VBAConsultez toutes les FAQ

Nombre d'auteurs : 10, nombre de questions : 133, dernière mise à jour : 30 mars 2017 

 
OuvrirSommaireExcelPlagesValeurs et formules

Il faut utiliser les formats de cellules.

 
Sélectionnez
objSheet.UsedRange.NumberFormat = "0;-0;;@"
Créé le 3 février 2004  par 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

 
Sélectionnez
Dim ObjCell As Range

For Each ObjCell In Range("A1:J65535").Cells
    ObjCell.Value = ObjCell.Value * 2 + 3
Next

et

 
Sélectionnez
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.

Créé le 3 février 2004  par bidou

Regardons ensemble. Un objet Range possède des propriétés Formula, FormulaArray, FormulaLocal, FormulaR1C1, FormulaLocalR1C1 La propriété FormulaArray sert à entrer des formules matricielles. Par exemple

 
Sélectionnez
Range("E1:F11").FormulaArray = "=LINEST(R1C3:R20C3,R1C2:R20C2,TRUE,TRUE)"

Ce code renvoie la matrice de réponses de la formule DROITEREG d'Excel. Notez que ce n'est pas DROITEREG mais LINEST qui est utilisé. Pourquoi ? Intrinsèquement, Excel utilise les formules internationales (donc anglaise). Mais il n'est pas toujours évident de connaître la syntaxe internationale. On peut donc toujours utiliser la syntaxe locale en appelant la propriété locale équivalente. Ainsi les deux formules suivantes sont équivalentes

 
Sélectionnez
Range("E18").Formula = "=OFFSET(C1,MATCH(16,A1:A20,0)-1,0)"
Range("E18").FormulaLocal = "=DECALER(C1;EQUIV(16;A1:A20;0)-1;0)"

Je peux aussi entrer la formule avec une référence LC (RC international) et j'utilise alors la version R1C1 des propriétés

 
Sélectionnez
Range("E18").FormulaR1C1 = "=OFFSET(R1C3,MATCH(16,R1C1:R20C1,0)-1,0)"
Range("E18").FormulaR1C1Local = "=DECALER(L1C3;EQUIV(16;L1C1:L20C1;0)-1;0)"
Créé le 3 février 2004  par bidou, Etienne Bar

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 Evidemment la recopie doit pouvoir trouver la valeur de l'incrément, il faut donc que la source contiennent au moins deux cellules.

  • 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
 
Sélectionnez
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.

 
Sélectionnez
Cells(29, 6).Resize(10).FillDown
Créé le 3 février 2004  par bidou

Généralement lorsqu'on utilise Excel on utilise un collage spécial pour cela. Mais par le code il vaut mieux passer par une astuce toute simple.

 
Sélectionnez
Range("F1:H20").Value = Range("F1:H20").Value

Pourquoi cela fonctionne ? Car l'affectation explicite d'une valeur à la propriété value revient à supprimer la valeur de la propriété Formula. En effet on ne peut pas affecter une valeur à une cellule contenant une formule sans écraser celle-ci afin d'éviter un conflit entre la valeur calculée et la valeur affectée.

Créé le 4 février 2004  par bidou

On utilise les validations comme par le menu. L'exemple suivant restreint les valeurs saisissables de G1 entre 10 et 100

 
Sélectionnez
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
Créé le 4 février 2004  par bidou

La formule générique est

 
Sélectionnez
NumCol = Cells(1, 72).Column
MsgBox IIf(NumCol > 26, Chr(64 + NumCol \ 26) & Chr(64 + NumCol Mod 26), Chr(64 + NumCol))
Créé le 4 février 2004  par bidou
  

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2009 Developpez Developpez LLC. Tous 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'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.