excel-vba
Gammes et cellules

Syntaxe

• Set - Opérateur utilisé pour définir une référence à un objet, tel qu'une plage
• For Each - L'opérateur a l'habitude de parcourir tous les éléments d'une collection

Remarques

Créer une plage

Une plage ne peut pas être créée ou remplie de la même manière qu'une chaîne:

Sub RangeTest()
    Dim s As String
    Dim r As Range 'Specific Type of Object, with members like Address, WrapText, AutoFill, etc.
    
    ' This is how we fill a String:
    s = "Hello World!"

    ' But we cannot do this for a Range:
    r = Range("A1") '//Run. Err.:  91 Object variable or With block variable not set//

    ' We have to use the Object approach, using keyword Set:
    Set r = Range("A1")
End Sub

La qualification de vos références est considérée comme la meilleure pratique. Nous utiliserons donc désormais la même approche.
En savoir plus sur la création de variables d'objet (par exemple, plage) sur MSDN . En savoir plus sur Set Statement sur MSDN .

Il existe différentes manières de créer la même gamme:

Sub SetRangeVariable()
    Dim ws As Worksheet
    Dim r As Range

    Set ws = ThisWorkbook.Worksheets(1) ' The first Worksheet in Workbook with this code in it
    
    ' These are all equivalent:
    Set r = ws.Range("A2")
    Set r = ws.Range("A" & 2)
    Set r = ws.Cells(2, 1) ' The cell in row number 2, column number 1
    Set r = ws.[A2] 'Shorthand notation of Range.
    Set r = Range("NamedRangeInA2") 'If the cell A2 is named NamedRangeInA2. Note, that this is Sheet independent.
    Set r = ws.Range("A1").Offset(1, 0) ' The cell that is 1 row and 0 columns away from A1
    Set r = ws.Range("A1").Cells(2,1) ' Similar to Offset. You can "go outside" the original Range.

    Set r = ws.Range("A1:A5").Cells(2) 'Second cell in bigger Range.
    Set r = ws.Range("A1:A5").Item(2) 'Second cell in bigger Range.
    Set r = ws.Range("A1:A5")(2) 'Second cell in bigger Range.
End Sub

Notez dans l'exemple que Cells (2, 1) est équivalent à Range ("A2"). Cela est dû au fait que Cells renvoie un objet Range.
Quelques sources: Chip Pearson-Cells Within Ranges ; MSDN-Range Object ; John Walkenback-Se référant aux gammes dans votre code VBA .

Notez également que dans toute instance où un nombre est utilisé dans la déclaration de la plage et que le nombre lui-même est en dehors des guillemets, tels que Range ("A" & 2), vous pouvez échanger ce nombre contre une variable contenant un entier / long. Par exemple:

Sub RangeIteration()
    Dim wb As Workbook, ws As Worksheet
    Dim r As Range

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets(1)

    For i = 1 To 10
        Set r = ws.Range("A" & i)
        ' When i = 1, the result will be Range("A1")
        ' When i = 2, the result will be Range("A2")
        ' etc.
        ' Proof:
        Debug.Print r.Address
    Next i
End Sub

Si vous utilisez des doubles boucles, Cells est mieux:

Sub RangeIteration2()
    Dim wb As Workbook, ws As Worksheet
    Dim r As Range

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets(1)

    For i = 1 To 10
        For j = 1 To 10
            Set r = ws.Cells(i, j)
            ' When i = 1 and j = 1, the result will be Range("A1")
            ' When i = 2 and j = 1, the result will be Range("A2")
            ' When i = 1 and j = 2, the result will be Range("B1")
            ' etc.
            ' Proof:
            Debug.Print r.Address
        Next j
    Next i
End Sub

Façons de se référer à une seule cellule

La manière la plus simple de faire référence à une seule cellule de la feuille de calcul Excel actuelle consiste simplement à inclure la forme A1 de sa référence entre crochets:

[a3] = "Hello!"

Notez que les crochets ne sont que du sucre syntaxique pratique pour la méthode Evaluate de l'objet Application , ce qui est techniquement identique au code suivant:

Application.Evaluate("a3") = "Hello!"

Vous pouvez également appeler la méthode Cells qui prend une ligne et une colonne et retourne une référence de cellule.

Cells(3, 1).Formula = "=A1+A2"

Rappelez-vous que chaque fois que vous passez une ligne et une colonne à Excel à partir de VBA, la ligne est toujours la première, suivie de la colonne, ce qui est déroutant car elle est opposée à la notation A1 commune.

Dans ces deux exemples, nous n'avons pas spécifié de feuille de calcul. Par conséquent, Excel utilisera la feuille active (la feuille qui se trouve dans l'interface utilisateur). Vous pouvez spécifier explicitement la feuille active:

ActiveSheet.Cells(3, 1).Formula = "=SUM(A1:A2)"

Ou vous pouvez fournir le nom d'une feuille particulière:

Sheets("Sheet2").Cells(3, 1).Formula = "=SUM(A1:A2)"

Il existe une grande variété de méthodes qui peuvent être utilisées pour aller d'une gamme à une autre. Par exemple, la méthode Rows peut être utilisée pour accéder aux lignes individuelles de n'importe quelle plage et la méthode Cells peut être utilisée pour accéder aux cellules individuelles d'une ligne ou d'une colonne. Le code suivant fait référence à la cellule C1:

ActiveSheet.Rows(1).Cells(3).Formula = "hi!"

Enregistrement d'une référence à une cellule dans une variable

Pour enregistrer une référence à une cellule dans une variable, vous devez utiliser la syntaxe Set , par exemple:

Dim R as Range
Set R = ActiveSheet.Cells(3, 1)

plus tard...

R.Font.Color = RGB(255, 0, 0)

Pourquoi le mot-clé Set -il requis? Set indique à Visual Basic que la valeur du côté droit de = est censée être un objet.

Propriété décalée

• Décalage (lignes, colonnes) - L'opérateur a utilisé pour référencer statiquement un autre point de la cellule en cours. Souvent utilisé dans les boucles. Il faut comprendre que les nombres positifs dans la section des lignes se déplacent à droite, à mesure que les négatifs se déplacent vers la gauche. Avec la section des colonnes, les points positifs se déplacent vers le bas et les négatifs montent.

c'est à dire

Private Sub this()
    ThisWorkbook.Sheets("Sheet1").Range("A1").Offset(1, 1).Select
    ThisWorkbook.Sheets("Sheet1").Range("A1").Offset(1, 1).Value = "New Value"
    ActiveCell.Offset(-1, -1).Value = ActiveCell.Value
    ActiveCell.Value = vbNullString
End Sub

Ce code sélectionne B2, y place une nouvelle chaîne, puis déplace cette chaîne vers A1 après avoir effacé B2.

Comment transposer des plages (horizontales à verticales et vice versa)

Sub TransposeRangeValues()
    Dim TmpArray() As Variant, FromRange as Range, ToRange as Range

    set FromRange = Sheets("Sheet1").Range("a1:a12")         'Worksheets(1).Range("a1:p1")
    set ToRange = ThisWorkbook.Sheets("Sheet1").Range("a1")  'ThisWorkbook.Sheets("Sheet1").Range("a1")

    TmpArray = Application.Transpose(FromRange.Value)
    FromRange.Clear
    ToRange.Resize(FromRange.Columns.Count,FromRange.Rows.Count).Value2 = TmpArray
End Sub

Remarque: Copy / PasteSpecial a également une option Paste Transpose qui met également à jour les formules des cellules transposées.