Diferentes Formas de Expresar un Rango de Celdas

En VBA para crear o referirse a un objeto constituido por un rango de celdas pueden utilizarse diferentes formas, utilizándose las propiedades Range y Cells combinados con los operadores de rango (:) y de unión (,); así como el uso de nombre de rangos o variables. También se utilizan los métodos Offset y Resize. (Billo, 2007)

1.     Usando la propiedad Range con una dirección. La dirección está en formato A1 y debe estar entre comillas.

 

1.1.  Para referirse a una sola celda: Range(“A1”)

 

1.2. Dirección definida utilizando el operador de rango (:).

Para referirse a un rango de celdas: Range(“A1:A10”)

 

1.3. Dirección definida utilizando el operador de unión (,).

Para referirse a un rango de celdas: Range(“A1” , “A10”)

 

2.     Usando la propiedad Cells con las coordenadas de la celda, numero de fila y columna.

El primer número indica el número de la fila y el segundo el número de columna. Cells(fila, columna).

Para referirse a la celda que está ubicada en la fila 15, columna 5 (E15)

Cells(15,5).

 

3.     Usando la propiedad Cells con variables para la fila y la columna.

Cells(NumF,NumC)

Donde NumF y NumC son variables previamente definidas, con valores enteros asignados.

 

4.     Usando la propiedad Range con un nombre de rango, el nombre del rango debe colocarse entre comillas y debe haber sido creado previamente.

Range(“Dir1”)

El nombre del rango Dir1 ha sido creado previamente.

 

5.     Usando la propiedad Range con una variable.

Range(Dir2)

La variable Dir2 ha sido previamente definida, utilizando declaraciones como la siguiente:

Dir2=Selection.Address

 

6.     Usando la propiedad Range en combinación con Cells

Range(Cells(1,1), Cells(5,5))

Esta expresión se refiere al rango A1:E5. Este método es útil cuando tanto las referencias a las filas como a las columnas deben ser calculadas.

 

7.     Usando la propiedad Range con el operador & (ampersand)

FilaSup=2 : FilaInf=12

Range(“F”&FilaSup&”:G”&FilaInf)

Estas expresiones dan como resultado el rango (“F2:G12”)

 

8.     Usando la propiedad Range con Cells(Indice)

Range(“A1:A12”).Cells(5)

Esta expresión da como resultado la celda A5. Este método provee la forma de seleccionar una celda específica dentro de un rango de celdas. La numeración comienza en la primera celda del rango y se mueve a lo largo de la fila de izquierda a derecha, y luego avanza hacia la siguiente columna.

Diferentes formas de expresar un rango 1

En la gráfica se muestra el rango “B2:E4” y la forma en que se numeran las celdas. Si colocáramos los siguientes comandos estos se referirían a:

Range(“B2:E4”).Cells(1) se refiere a la celda B2, la cual es la primera celda del rango.

Range(“B2:E4”).Cells(5) se refiere a la celda B3, y

Range(“B2:E4”).Cells(12) se refiere a la celda E4.

 

9.     Usando la propiedad Range con el método Offset

El método Offset tiene como objetivo mover la celda activa, el primer número es el número de filas a mover y el segundo representa el número de columnas.

Si el número es positivo se mueve la celda hacia la derecha en el caso de las fila y hacia abajo en el caso de columnas. Si el número es negativo los movimientos son hacia la izquierda y hacia arriba para filas y columnas respectivamente.

Sub Test3()

Cells.Clear

Range(“A1”).Offset(3, 1) = “Hola”

End Sub

En este ejemplo se utiliza el método Offset para colocar la palabra Hola en la celda B4.

Diferentes formas de expresar un rango 2

 

 

En este ejemplo se utiliza el método Offset para seleccionar el rango B4:B15

 

Sub Test4()

Cells.Clear

Range(“A1:A12”).Offset(3, 1).Select

End Sub

Diferentes formas de expresar un rango3

10.           Usando la propiedad Range con los métodos Offset y Resize

El método Resize se utiliza para cambiar el tamaño de un rango de celdas. En el siguiente ejemplo se selecciona la celda B4.

 

Sub Test5()

Cells.Clear

Range(“A1:A12”).Offset(3, 1).Resize(1, 1).Select

End Sub

Diferentes formas de expresar un rango 4

Anuncios

La Propiedad Range

Esta propiedad de Worksheets es al principio es un poco difícil de comprender, porque genera un objeto, entonces existe esa dualidad propiedad objeto.

La propiedad Range devuelve una celda o rango de celdas. La sintaxis de esta propiedad es:

Objeto.Range(referencia)

La referencia es la dirección de una celda o rango de celdas, también se pueden referenciar múltiples celdas o rangos no contiguos. Existen diferentes formas de hacer las referencias , de la cual la mas usual es en el estilo A1.

Si quisiéramos hacer una referencia completa hasta un rango de celdas específicos escribiríamos

Application.Worbooks(“nombre del Libro”).worksheets(“nombre de la hoja”).Range(“A1:B5”)

Si estamos ubicados dentro de la hoja activa, podemos ahorrarnos las direcciones de los objetos precedentes y escribir simplemente

Range(“A1:B5”)

Para definir un rango se puede utilizar los dos puntos ( : ) que es el operador de rango, o también se puede utilizar la coma ( , ) que es el operador de unión.

Así el Rango de celdas de A1 a B5 también se puede escribir como:

Range(“A1”, “B5”)

En la siguiente tabla se muestran algunos ejemplos del código que debe introducirse para crear rangos (objetos del tipo Range).

Acción a Ejecutar Código VBA a introducir
Seleccionar una sola celda, por ejemplo B4. Range( “B4” ).Select
Seleccionar un rango de celdas, por ejemplo A4:B12. Range( “A4:B12” ).Select
Seleccionar celdas no contiguas, por ejemplo B5, G5, M5 Range( “B5, G5, M5” ).Select
Seleccionar rango de celdas y celdas no contiguas, por ejemplo A1:B5, C6, D5 Range( “A1:B5, C6, D5” ).Select

Guardando Macros



Los macros creados por Excel pueden ser almacenados en 3 ubicaciones:

Guardando macros

Libro de Macros Personal (Personal Macro Workbook)

Los macros que se almacenan en esta ubicación estarán disponibles siempre que utilices Excel. El libro Personal está ubicado en la carpeta XLStart. Si esta carpeta no existe, Excel la creara la primera vez que selecciones esta opción.

 

Libro Nuevo (New Workbook)

Excel coloca el macro en un libro nuevo.

 

Este Libro (This Workbook)

Excel almacenara el macro en el libro que está siendo utilizado al momento de guardar el macro.

Introducción a los Macros en Excel

Un macro es un procedimiento generado por el grabador de macros, el cual es una herramienta que viene incluida dentro de las aplicaciones de Microsoft Office, tal como Excel, Word o Power point entre otras.

El grabador de Macros permite grabar un conjunto de acciones cuando estas se ejecutan dentro de la aplicación, en el caso específico que estamos tratando, Excel. Las instrucciones de los macros se graban dentro de un módulo, utilizando el lenguaje Visual Basic para Aplicaciones, el cual es un lenguaje de programación orientado a objetos. El código generado puede ser visto y editado de ser necesario entrando en el editor de Visual Basic para Aplicaciones, llamado VBE.

Para poder tener acceso al grabador de macros y al editor de VBA, primero hay que activar el menú de DESARROLLADOR, para que este aparezca en la cinta de menú. Para hacer esto hay que ir a las opciones de Excel y seleccionar Personalizar cinta de opciones. Allí hay que activar en la casilla la opción DESARROLLADOR.

La secuencia es Archivo y seleccionar Opciones

Captura de pantalla 2015-10-12 10.28.27

En opciones de Excel, seleccionar personalizar cinta de Opciones y activar DESARROLLADOR.

Captura de pantalla 2015-10-12 10.23.21

Esto permitirá el acceso al menú de DESARROLLADOR en la cinta de menú.

 cinta desarrollador

De momento solo estamos interesados en el menú de código.

menu codigo

En primer lugar aparece un icono de Visual Basic que da acceso al editor de Visual Basic para Aplicaciones.

Luego aparece el icono de Macros, que da acceso al listado de macros creados y diferentes opciones para ejecutar, modificar o eliminar macros entre otras.

menu macros

Y posteriormente aparecen los iconos de Grabar macro, Usar referencias relativas y Seguridad.

Al hacer clic en el icono de Grabar macro se inicia la grabación de todas las acciones que ejecutemos con la aplicación. Al finalizar debemos hacer clic para detener la grabación.

Otra forma de iniciar la grabación de un macro es haciendo clic sobre el icono que aparece en la barra de notificación, en la parte inferior de la ventana.

boton macro inferior

Hagamos un ejemplo sencillo para entender la mecánica de cómo funciona una macro generada con el Grabador de macro.

Supongamos que debemos hacer una tabla diariamente en donde recojamos los datos de venta de productos y que se desea que esta tenga un formato estándar.

1.- Activamos el grabador de macros y aparecerá el siguiente cuadro de dialogo, para indicar el nombre del Macro, El método Abreviado, el cual es el conjunto de teclas que deben pulsarse con Ctrl para ejecutar el macro. Donde se guardara el macro y la descripción del macro.

menu grabar macro

2.- Al hacer clic en aceptar se comienzan a grabar todas las acciones que ejecutemos con la aplicación.

Comenzamos colocando el Texto Reporte Diario en la celda A2, luego colocamos en las fila 5 los encabezados de las columnas A, B, C y D con Producto, Cantidad, Vendedor y Precio Unitario respectivamente.

3.- Al finalizar hacemos clic para detener la grabación, ya sea en el icono en la cinta de menú, o en el icono en la barra de notificación

4.- El resultado de nuestras acciones se pueden ver en la siguiente figura

hoja con macro

5.- El código generado por el grabador de macros lo podemos inspeccionar en el VBE, haciendo clic en el icono de Visual Basic o con la combinación Alt+f11.

Sub ReporteDiario()

‘ ReporteDiario Macro

‘ Reporte diario de Ventas

‘ Acceso directo: Ctrl+Mayús+V

Range(“A2”).FormulaR1C1 = “Reporte Diario de Ventas”

Range(“A5”).Select

ActiveCell.FormulaR1C1 = “Producto”

Range(“B5”).Select

ActiveCell.FormulaR1C1 = “Cantidad”

Range(“C5”).Select

ActiveCell.FormulaR1C1 = “Vendedor”

Range(“D5”).Select

ActiveCell.FormulaR1C1 = “Precio Unitario”

Range(“D5”).Select

Columns(“D:D”).ColumnWidth = 14.29

Range(“A2”).Select

With Selection.Font

.Name = “Calibri”

.Size = 18

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ThemeColor = xlThemeColorLight1

.TintAndShade = 0

.ThemeFont = xlThemeFontMinor

End With

Selection.Font.Bold = True

Range(“A5:D5”).Select

Selection.Font.Bold = True

With Selection.Font

.Name = “Calibri”

.Size = 12

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ThemeColor = xlThemeColorLight1

.TintAndShade = 0

.ThemeFont = xlThemeFontMinor

End With

Range(“D8”).Select

End Sub

6.- Para ejecutar el macro, hacemos clic en el icono macro, y aparecerán los macros que han sido creados en los libros abiertos. Seleccionamos el macro que deseamos ejecutar y hacemos clic en ejecutar.

ejecutar macro

7.- Se repetirán las acciones grabadas y los textos y formatos serán copiados en la hoja activa, tantas veces como se desee. Este es un ejemplo muy sencillo, pero da la idea de la utilidad de los macros, permitir repetir un conjunto de acciones de manera rápida.

Todos los macros comienzan con la declaración Sub  (Sub es un diminutivo para subrutina, comúnmente llamada macro) que incluye el nombre del macro, seguido de un par de paréntesis. Aquí, la declaración Sub es simplemente Sub Nombre_del_Macro().

Debido a que este macro fue grabado, existe una serie de líneas de comentarios debajo de la declaración Sub que el grabador de macros quiere que tú conozcas. Por ejemplo, veras el nombre del macro, la descripción del macro que hayas introducido en la caja de diálogos, y la notación de las teclas de atajo que le hayas asignado al macro.

Las líneas de comentarios comienzan con un apostrofe, son de color verde lo que las distingue de las líneas de códigos. Las líneas de comentarios no son ejecutables a diferencia de las líneas de códigos que realizan alguna acción cuando el macro es ejecutado.

Las demás líneas en el macro son declaraciones VBA, y representan cada acción que se realizó mientras que el grabador de macro estaba encendido.

Mejorando un Macro con Comentarios y Optimizando el código

El grabador de Macro por lo general crea mucho más código del que es requerido para ejecutar las acciones. La función del grabador de Macro, y la cual la ejecuta impecablemente, es la de producir código VBA que representa cada una de las acciones en la pantalla.

Para Macros que son sencillos, es posible que no se requiera hacer el proceso de edición y si el macro hace la tarea que debe hacer es mejor dejarlo así.

Sin embargo, la mayoría del código VBA que es producido por el grabador de Macros, es de naturaleza excesiva y superflua, lo que es difícil de ignorar. Y si este código va a ser compartido con otros, desearías que tuviera un aspecto pulido y profesional.

Una regla de tonto en VBA es no seleccionar (Select) o activar (Activate) objetos a menos que sea necesario. Los métodos Select y Activate son los grandes culpables de la ejecución de macros retorcidos y lentos.

Por ejemplo en estas dos líneas de macro:

Columns (“A:A”).Select

Selection. Insert Shift:=xlToRight

Estas líneas pueden y deben ser consolidadas en una línea, evitando la actividad de seleccionar.

Columns (“A:A”). Insert Shift:=xlToRight

Lo mismo puede decirse de las siguientes dos declaraciones:

Columns(“C:C”). Select

Selection.Cut Destination:=Columns(“A:A”)

Lo cual puede ser expresado de una manera más compacta

Columns(“C”).Cut Destination:=Columns(“A”)

En VBA se puede actuar directamente en la mayoría de los objetos, la mayoría de las veces, sin la necesidad de seleccionarlos. Cuando se elimina la columna C, no se necesita tocarla para que VBA haga el trabajo.

Columns(“C:C”). Select

Selection.Delete Shift:=xlToLeft

La cual en forma más compacta es:

Colums(“C:C”).Delete Shift:=xlToLeft

El siguiente es un Macro grabado para simplemente mover la columna C a la Columna A.

Sub MyMacro()

‘ MyMacro Macro

‘ Cambia de Columna de 1 a 3

‘ Acceso directo: Ctrl+Mayús+S

Columns(“A:A”).Select

Selection.Insert Shift:=xlToRight

Columns(“D:D”).Select

Selection.Copy

Range(“A1”).Select

ActiveSheet.Paste

Columns(“D:D”).Select

Application.CutCopyMode = False

Selection.Delete Shift:=xlToLeft

End Sub

La siguiente es una edición del Macro, en el cual se han aplicado las reglas aprendidas

Sub MyMacro()

‘ MyMacro Macro

‘ Cambia de Columna de 1 a 3

‘ Acceso directo: Ctrl+Mayús+S

Columns(“A:A”).Insert Shift:=xlToRight

Columns(“D:D”).Cut Destination:=Columns(“A:A”)

Columns(“D:D”).Delete Shift:=xlToLeft

Application.CutCopyMode = False

End Sub

En la versión obtenida de la grabadora de macros se tienen 9 líneas de código y en la versión editada solo 4. Ambas versiones hacen las mismas acciones.