excel-vba
Gamme e celle

Sintassi

• Set - L'operatore utilizzato per impostare un riferimento a un oggetto, ad esempio un intervallo
• Per ogni : l'operatore ha utilizzato per scorrere tutti gli elementi di una raccolta

Osservazioni

Creazione di un intervallo

Un Range non può essere creato o popolato nello stesso modo in cui una stringa:

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

È considerata la migliore pratica per qualificare i tuoi riferimenti , quindi d'ora in poi utilizzeremo lo stesso approccio qui.
Ulteriori informazioni sulla creazione di variabili oggetto (ad es. Intervallo) su MSDN . Ulteriori informazioni su Imposta dichiarazione su MSDN .

Esistono diversi modi per creare la stessa gamma:

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

Nota nell'esempio che Cells (2, 1) è equivalente a Range ("A2"). Questo perché le celle restituiscono un oggetto Range.
Alcune fonti: Chip Pearson-Cells Within Ranges ; Oggetto MSDN-Range ; John Walkenback-Referring To Ranges Nel tuo codice VBA .

Si noti inoltre che in qualsiasi istanza in cui un numero viene utilizzato nella dichiarazione dell'intervallo e che il numero stesso non rientra tra virgolette, come Intervallo ("A" e 2), è possibile scambiare quel numero per una variabile che contiene un integer / lungo. Per esempio:

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

Se si utilizzano i cicli doppi, le celle sono migliori:

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

Modi per fare riferimento a una singola cella

Il modo più semplice per riferirsi a una singola cella nel foglio di lavoro Excel corrente è semplicemente racchiudere la forma A1 del suo riferimento tra parentesi quadre:

[a3] = "Hello!"

Nota che le parentesi quadre sono solo convenienti zucchero sintattico per il metodo di Evaluate dell'oggetto Application , quindi tecnicamente, questo è identico al seguente codice:

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

Puoi anche chiamare il metodo Cells che prende una riga e una colonna e restituisce un riferimento di cella.

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

Ricorda che ogni volta che passi una riga e una colonna in Excel da VBA, la riga è sempre la prima, seguita dalla colonna, che è confusa perché è l'opposto della notazione A1 comune in cui la colonna viene visualizzata per prima.

In entrambi questi esempi, non abbiamo specificato un foglio di lavoro, quindi Excel utilizzerà il foglio attivo (il foglio che si trova di fronte nell'interfaccia utente). È possibile specificare il foglio attivo esplicitamente:

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

Oppure puoi fornire il nome di un particolare foglio:

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

Esiste un'ampia varietà di metodi che possono essere utilizzati per passare da un intervallo a un altro. Ad esempio, il metodo Rows può essere utilizzato per ottenere le singole righe di qualsiasi intervallo e il metodo Cells può essere utilizzato per ottenere singole celle di una riga o di una colonna, quindi il seguente codice fa riferimento alla cella C1:

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

Salvataggio di un riferimento a una cella in una variabile

Per salvare un riferimento a una cella in una variabile, è necessario utilizzare la sintassi Set , ad esempio:

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

dopo...

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

Perché è necessaria la parola chiave Set ? Set dice a Visual Basic che il valore sul lato destro di = è pensato per essere un oggetto.

Proprietà offset

• Offset (Righe, Colonne) - L'operatore ha utilizzato il riferimento statico a un altro punto della cella corrente. Spesso utilizzato nei loop. Dovrebbe essere chiaro che i numeri positivi nella sezione delle righe si spostano a destra, mentre i negativi si spostano a sinistra. Con la sezione delle colonne, i positivi si spostano verso il basso e i negativi si spostano verso l'alto.

vale a 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

Questo codice seleziona B2, inserisce una nuova stringa lì, quindi sposta quella stringa su A1 dopo aver eliminato B2.

Come trasporre gli intervalli (da orizzontale a verticale e viceversa)

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

Nota: Copy / PasteSpecial ha anche un'opzione Trasporta Incolla che aggiorna anche le formule delle celle trasposte.