excel-vba
Områden och celler

Syntax

• Set - Operatören som används för att ställa in en referens till ett objekt, till exempel ett intervall
• För varje - Operatören brukade gå igenom varje objekt i en samling

Anmärkningar

Skapa ett intervall

Ett intervall kan inte skapas eller fyllas på samma sätt som en sträng skulle:

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

Det anses vara bästa praxis för att kvalificera dina referenser , så från och med nu kommer vi att använda samma strategi här.
Mer om att skapa objektvariabler (t.ex. Range) på MSDN . Mer om Set Statement på MSDN .

Det finns olika sätt att skapa samma intervall:

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

Observera i exemplet att celler (2, 1) motsvarar intervallet ("A2"). Det beror på att Cells returnerar ett Range-objekt.
Några källor: Chip Pearson-celler inom räckvidden ; MSDN-Range Object ; John Walkenback-hänvisar till områden i din VBA-kod .

Observera också att i alla fall där ett nummer används i deklarationen för intervallet och själva numret är utanför citattecken, till exempel Range ("A" & 2), kan du byta det numret för en variabel som innehåller en heltal / lång. Till exempel:

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

Om du använder dubbla öglor är celler bättre:

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

Sätt att hänvisa till en enda cell

Det enklaste sättet att hänvisa till en enda cell i det aktuella Excel-kalkylbladet är helt enkelt att bifoga A1-formen för referensen i fyrkantiga parenteser:

[a3] = "Hello!"

Observera att fyrkantiga parenteser är bara bekväma syntaktiskt socker för Evaluate för Application , så tekniskt är detta identiskt med följande kod:

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

Du kan också ringa Cells metoden som tar en rad och en kolumn och returnerar en cellreferens.

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

Kom ihåg att varje gång du skickar en rad och en kolumn till Excel från VBA är raden alltid först, följt av kolumnen, vilket är förvirrande eftersom det är motsatsen till den vanliga A1 notationen där kolumnen visas först.

I båda dessa exempel specificerade vi inte ett kalkylblad, så Excel kommer att använda det aktiva arket (arket som är framme i användargränssnittet). Du kan specificera det aktiva arket uttryckligen:

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

Eller så kan du ange namnet på ett visst ark:

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

Det finns en mängd olika metoder som kan användas för att komma från ett område till ett annat. Till exempel kan Rows metoden användas för att komma till de individuella raderna i valfritt område, och Cells metoden kan användas för att komma till enskilda celler i en rad eller kolumn, så följande kod hänför sig till cell C1:

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

Spara en referens till en cell i en variabel

För att spara en referens till en cell i en variabel måste du använda Set syntaxen, till exempel:

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

senare...

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

Varför krävs Set nyckelordet? Set säger till Visual Basic att värdet på höger sida om = är tänkt att vara ett objekt.

Offsetfastighet

• Offset (rader, kolumner) - Operatören som används för att statiskt referera till en annan punkt från den aktuella cellen. Används ofta i öglor. Det bör förstås att positiva siffror i raderna rör sig åt höger, när negativen rör sig åt vänster. Med kolumnavsnittet flyttas positiva ner och negativa rör sig upp.

dvs.

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

Den här koden väljer B2, sätter en ny sträng där och flyttar sedan den strängen tillbaka till A1 efter att rensa ut B2.

Hur man transponerar områden (horisontellt till vertikalt och 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

Obs: Copy / PasteSpecial har också ett Paste Transpose-alternativ som också uppdaterar de transponerade cellers formler.