excel-vba
Bereiken en cellen

Syntaxis

• Set - De operator die wordt gebruikt om een verwijzing naar een object in te stellen, zoals een bereik
• Voor elke - De operator liep altijd door elk item in een verzameling

Opmerkingen

Een bereik maken

Een bereik kan niet op dezelfde manier worden gemaakt of gevuld als een tekenreeks:

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

Het wordt als beste praktijk beschouwd om uw referenties te kwalificeren , dus vanaf nu zullen we dezelfde aanpak hier gebruiken.
Meer over het creëren van objectvariabelen (bijv. Bereik) op MSDN . Meer over Set Statement op MSDN .

Er zijn verschillende manieren om hetzelfde bereik te maken:

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

Merk in het voorbeeld op dat cellen (2, 1) gelijk is aan Bereik ("A2"). Dit komt omdat cellen een Range-object retourneren.
Enkele bronnen: Chip Pearson-Cells Within Ranges ; MSDN-bereikobject ; John Walkenback-verwijzend naar bereiken in uw VBA-code .

Merk ook op dat in elk geval waarin een nummer wordt gebruikt in de declaratie van het bereik en het nummer zelf niet tussen aanhalingstekens staat, zoals Bereik ("A" & 2), u dat nummer kunt omwisselen voor een variabele die een bevat integer / lang. Bijvoorbeeld:

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

Als u dubbele lussen gebruikt, is cellen beter:

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

Manieren om naar een enkele cel te verwijzen

De eenvoudigste manier om naar een enkele cel op het huidige Excel-werkblad te verwijzen, is om de A1-vorm van de verwijzing tussen vierkante haken te plaatsen:

[a3] = "Hello!"

Merk op dat vierkante haakjes gewoon handige syntactische suiker zijn voor de Evaluate methode van het Application object, dus technisch gezien is dit identiek aan de volgende code:

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

U kunt ook de methode Cells aanroepen die een rij en een kolom gebruikt en een celverwijzing retourneert.

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

Onthoud dat wanneer u vanuit VBA een rij en een kolom naar Excel doorgeeft, de rij altijd eerst is, gevolgd door de kolom, wat verwarrend is omdat deze het tegenovergestelde is van de algemene A1 notatie waar de kolom als eerste verschijnt.

In beide voorbeelden hebben we geen werkblad opgegeven, dus zal Excel het actieve blad gebruiken (het blad dat zich vooraan in de gebruikersinterface bevindt). U kunt het actieve blad expliciet opgeven:

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

Of u kunt de naam van een bepaald blad opgeven:

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

Er zijn veel verschillende methoden die kunnen worden gebruikt om van het ene bereik naar het andere te komen. De methode Rows kan bijvoorbeeld worden gebruikt om naar de afzonderlijke rijen van een willekeurig bereik te gaan en de methode Cells kan worden gebruikt om naar afzonderlijke cellen van een rij of kolom te gaan, dus de volgende code verwijst naar cel C1:

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

Een verwijzing naar een cel in een variabele opslaan

Als u een verwijzing naar een cel in een variabele wilt opslaan, moet u de syntaxis Set , bijvoorbeeld:

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

later...

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

Waarom is het sleutelwoord Set vereist? Set vertelt Visual Basic dat de waarde aan de rechterkant van de = bedoeld is als een object.

Offset eigendom

• Offset (rijen, kolommen) - De operator gebruikte om statisch naar een ander punt uit de huidige cel te verwijzen. Vaak gebruikt in lussen. Het moet duidelijk zijn dat positieve getallen in het rijengedeelte naar rechts bewegen, terwijl negatieven naar links bewegen. Met de kolomsectie gaan positieven omlaag en negatieven omhoog.

d.w.z

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

Deze code selecteert B2, plaatst daar een nieuwe string en verplaatst die string vervolgens terug naar A1 om vervolgens B2 te wissen.

Reeksen transponeren (horizontaal naar verticaal en 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

Opmerking: Copy / PasteSpecial heeft ook een optie Plakken Transpose die ook de formules van de getransponeerde cellen bijwerkt.