excel-vba
Gamas y celulas

Sintaxis

• Establecer : el operador utilizado para establecer una referencia a un objeto, como un rango
• Para cada - El operador solía recorrer cada elemento de una colección.

Observaciones

Creando un rango

Un Rango no puede ser creado o poblado de la misma manera que una cadena:

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

Se considera una buena práctica calificar sus referencias , por lo que a partir de ahora usaremos el mismo enfoque aquí.
Más información sobre la creación de variables de objeto (por ejemplo, rango) en MSDN . Más sobre Set Statement en MSDN .

Hay diferentes maneras de crear el mismo rango:

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

Observe en el ejemplo que las celdas (2, 1) son equivalentes al rango ("A2"). Esto se debe a que las celdas devuelven un objeto Range.
Algunas fuentes: Chip Pearson-Cells Within Ranges ; Objeto de rango MSDN ; John Walkenback-Referente a rangos en su código de VBA .

También tenga en cuenta que en cualquier caso en que se use un número en la declaración del rango, y el número en sí esté fuera de comillas, como el rango ("A" y 2), puede intercambiar ese número por una variable que contenga un entero / largo Por ejemplo:

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 está utilizando bucles dobles, las celdas son mejores:

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

Maneras de referirse a una sola celda

La forma más sencilla de referirse a una sola celda en la hoja de cálculo de Excel actual es simplemente encerrar la forma A1 de su referencia entre corchetes:

[a3] = "Hello!"

Tenga en cuenta que los corchetes son solo azúcar sintáctica conveniente para el método Evaluate del objeto Application , por lo que técnicamente, esto es idéntico al siguiente código:

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

También puede llamar al método de Cells que toma una fila y una columna y devuelve una referencia de celda.

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

Recuerde que siempre que pase una fila y una columna a Excel desde VBA, la fila siempre es la primera, seguida de la columna, lo cual es confuso porque es lo opuesto a la notación A1 común donde la columna aparece primero.

En ambos ejemplos, no especificamos una hoja de trabajo, por lo que Excel usará la hoja activa (la hoja que está al frente en la interfaz de usuario). Puede especificar la hoja activa explícitamente:

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

O puede proporcionar el nombre de una hoja en particular:

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

Hay una amplia variedad de métodos que se pueden utilizar para ir de un rango a otro. Por ejemplo, el método Rows se puede usar para llegar a las filas individuales de cualquier rango, y el método Cells se puede usar para llegar a las celdas individuales de una fila o columna, por lo que el siguiente código se refiere a la celda C1:

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

Guardar una referencia a una celda en una variable

Para guardar una referencia a una celda en una variable, debe usar la sintaxis Set , por ejemplo:

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

luego...

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

¿Por qué se requiere la palabra clave Set ? Set le dice a Visual Basic que el valor en el lado derecho de = está destinado a ser un objeto.

Propiedad compensada

• Desplazamiento (filas, columnas) : el operador utiliza para hacer referencia estáticamente a otro punto de la celda actual. A menudo se utiliza en bucles. Debe entenderse que los números positivos en la sección de filas se mueven a la derecha, mientras que los negativos se mueven a la izquierda. Con la sección de columnas, los positivos se mueven hacia abajo y los negativos se mueven hacia arriba.

es decir

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

Este código selecciona B2, coloca una nueva cadena allí, luego mueve esa cadena de nuevo a A1 después de eliminar B2.

Cómo Transponer Rangos (Horizontal a Vertical y 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 también tiene una opción Pegar Transposición que actualiza las fórmulas de las celdas transpuestas también.