En este capítulo vamos a aprender un poco más sobre cómo hacer referencia a celdas, rangos, hojas y libros. Supongo que todos estos conceptos están claros, que todo el mundo sabe lo que es una celda o un rango, o la diferencia entre un libro y una hoja.

Todos los ejemplos de este capítulo están incluídos en este fichero:

Excel2.xls (39kbs)

Bueno, para entrar en calor vamos a hacer una pequeña rutina que pida al usuario un valor y luego sume ese valor a la celda activa.

Sub Sumar_A_Celda()
   Dim i As Double

   If MsgBox("Esta macro pide al usuario 
   un valor y lo suma a la celda activa.", _
      vbOK, "Sumar a celda") = vbCancel Then Exit Sub
   i = Val(InputBox("Importe a sumar: ", "Sumar a celda activa"))
   ActiveCell.Value = ActiveCell.Value + i
End Sub

La primera línea es la declaración de la variable "i". Ya hemos comentado anteriormente lo que es la declaración de variables, lo único nuevo es el tipo de variable (double). Cuando declaramos una variable hay que indicar qué tipo de variable queremos que sea. Los tipos de variables más importantes son:

En este caso declaramos la variable como Double, por si acaso al usuario se le ocurre meter un valor con decimales.

Siguiendo con nuestra rutina, primero presentamos al usuario un pequeño mensaje diciendo lo que vamos a hacer. Esto está explicado en la entrega anterior, no creo que necesite nada más. Si el usuario pulsa "Cancelar", salimos de la rutina y no hacemos nada.

En la siguiente línea obtenemos del usuario el valor que quiere sumar a la celda activa. La función InputBox ya la hemos visto antes, obtiene del usuario una entrada, pero en formato de cadena de caracteres. Como nosotros lo que queremos es un valor numérico, utilizamos la función Val() para convertir la cadena de caracteres proveniente de InputBox en un número. Lo ponemos todo en la misma línea, pero también se podía haber escrito en dos líneas:

   Dim s as String
   s = InputBox("Importe a sumar: ", "Sumar a celda activa")
   i = Val(s)

Bueno, seguramente queda más claro que de la otra forma, pero normalmente se escribe de la primera forma. Cada uno que lo escriba como quiera, tampoco tiene demasiada trascendencia.

La última línea es la que hace realmente la suma, dando valor a la propiedad Value de ActiveCell. Ya hemos visto en la entrega anterior lo que es ActiveCell. Una aclaración sobre ActiveCell y ActiveSheet (que también vimos en la entrega anterior): en realidad, ActiveCell no es un objeto, sino que es una "propiedad" de un objeto especial llamado Application, pero cuando escribimos "ActiveCell" Excel entiende que estamos escribiendo "Application.ActiveCell". Application es un objeto que representa la aplicación Excel completa. A través de Application se puede acceder a todas las opciones de la aplicación (las que podemos cambiar a través de Herramientas, Opciones), acceder a las funciones de hoja de cálculo (SUMA, PROMEDIO, etc) y también podemos obtener objetos que referencian a la celda activa (ActiveCell), la hoja activa (ActiveSheet), el libro activo (ActiveWorkbook), etc.

Un pequeño ejemplo del uso del objeto Application: para mostrar y ocultar la barra de estado (la que aparece en la parte de abajo, debajo de las etiquetas de las hojas), podemos usar el siguiente código:

   Application.DisplayStatusBar = False

   Application.DisplayStatusBar = True

El objeto Application tiene un montón de propiedades y métodos, que están muy bien explicados en la ayuda de Excel, y su uso es bastante sencillo.

Siguiendo con la última línea de la rutina, podrás comprobar que utilizamos la propiedad Value de ActiveCell. Si hubiésemos utilizado la propiedad Formula en lugar de Value, la rutina tendría que ser un poco distinta. Utilizando Value, estamos dando a la celda activa un valor directamente, es decir, si miras el contenido de la celda verás que no hay ninguna fórmula; la suma la ha hecho nuestra rutina, y ha puesto en la celda el resultado de dicha suma.

Sin embargo, si queremos que la fórmula de la suma quede en la celda debemos usar la propiedad Formula. La propiedad Formula es una "cadena de carácteres", que incluye todo lo que se escribiría normalmente en una celda de una hoja de cálculo para introducir una fórmula (incluyendo el signo "=" al principio). Por ejemplo, si queremos que poner en la celda A2 una fórmula que nos de el doble del valor de la celda A1, haremos:

Como es una cadena de caracteres, hay que rodearla con comillas dobles, y entre éstas ponemos la fórmula tal como lo haríamos en la hoja de cálculo. Tras esta explicación, si queremos cambiar nuestra rutina para introducir la fómula de suma en la celda, nuestro primer intento sería escribir:

Si ejecutamos este código veremos que el resultado puede variar dependiendo de lo que contenga la celda activa:

• Si la celda activa contiene un número (no una fórmula), el resultado es el mismo que antes. La razón es que como no hay ninguna fórmula ActiveCell.Formula devuelve un número, igual que ActiveCell.Value. Luego le sumamos nuestro valor y asignamos el valor resultante como fórmula de la celda.

• Si la celda activa contiene una fórmula, el resultado es que la macro se para con un error. Esto es debido a que ActiveCell.Fórmula nos devuelve una cadena de caracteres (por ejemplo, la cadena "=A1*2"), y luego estamos intentando sumar un número (i) a una cadena de caracteres, cosa que evidentemente no se puede hacer y Excel nos da un error de tipos de datos incompatibles.

Para conseguir lo que realmente queremos (introducir una fórmula en la celda), debemos asignar a ActiveCell.Formula una cadena de caracteres con una fórmula escrita correctamente. Vamos a intentarlo:

El operador & ya lo vimos en la primera entrega, se utiliza para concatenar cadenas de caracteres. Partimos de la fórmula que actualmente tiene la celda activa (por ejemplo, "=A1*2") y luego le añadimos un signo "+" y luego nuestro valor (i), con lo que la fórmula pasará a ser "=A1*2+3" (si i tiene el valor 3).

El problema que se nos presenta ahora es que si en la celda activa no hay una celda, sino un valor (por ejemplo, 2), nosotros asignaremos a ActiveCell.Formula la cadena "2+3" (si i tiene el valor 3). ¿Veis el error? Evidentemente el error es que falta el signo "=" al principio de la fórmula para decirle a Excel que es una fórmula. O sea que deberíamos añadir delante de la fórmula un "=", lo que podemos hacer de la siguiente forma:

¿Cuál es el problema ahora? Pues que si la celda activa tiene una fórmula nos encontramos con que nuestra fórmula definitiva tiene dos signos "=" al principio, uno el que ya tenía y otro el que le hemos puesto nosotros. El resultado es que introducimos una fórmula incorrecta y Excel nos vuelve a dar un error.

Bueno, creo que he montado un pequeño lío, pero en realidad no tiene ninguna dificultad. Lo que hay que hacer es comprobar si en la celda activa hay una fórmula (esto se puede hacer mirando si el primer carácter es el signo "=", utilizando por ejemplo la función Left). Si hay una fórmula, sólo añadimos la parte del final, y si no hay una fórmula añadimos primero un signo "=" y luego la parte del final. Esto queda como ejercicio, y si no te sale me escribes y lo comentamos.

Pues aparte de todo este lío ya lo hemos visto todo. Si ejecutas la rutina Sumar_A_Celda() comprobarás que primero te pide un valor y luego suma ese valor a la celda activa. Por cierto, que nadie piense que esta macro es ninguna maravilla, porque lo mismo que hace se puede hacer más rápido utilizando "Pegado especial..", que además es capaz de restar, multiplicar y dividir. Ya llegaremos a eso también, pero para ir aprendiendo un poco no nos viene mal.

Ahora vamos a avanzar otro paso: la rutina anterior sumaba un valor a la celda activa, y ahora vamos a sumar ese valor a todo el rango que seleccione el usuario. Como siempre, aquí va la rutina completa, luego la explicamos:

Sub Sumar_A_Rango()
    Dim i As Double
    Dim h As Object
    
    If MsgBox("Esta macro pide al usuario un valor y lo 
	suma a todas las celdas " & _
            Chr(13) & "del rango seleccionado.", vbOK, "Copiar 
			celda izquierda") _
            = vbCancel Then Exit Sub
    i = Val(InputBox("Importe a sumar: ", "Sumar a rango seleccionado"))
    For Each h In Selection.Cells
        h.Value = h.Value + i
    Next h
End Sub

Lo primero que hacemos es declarar las variables. La primera variable (i), es de tipo Double, al igual que en la rutina anterior. La segunda variable (h) la declaramos como una variable de tipo Object, para que apunte a cualquier tipo de objeto de Excel (una hoja, un rango, una celda, etc.)

Las dos siguientes líneas son iguales que antes: primero presentamos un pequeño mensaje al usuario, y luego obtenemos el valor y convertimos la cadena a un valor numérico.

En las tres siguientes líneas está todo lo nuevo. Vamos por pasos: Selection es una propiedad del objeto Application, o sea que al escribir Selection Excel supone que estamos escribiendo Application.Selection. Pues bien, la propiedad Selection nos devuelve el rango que está seleccionado en la hoja activa, es decir, un objeto de tipo Range.

Con Selection obtenemos un objeto de tipo Range que apunta al rango seleccionado de la hoja activa. Si accedemos a la propiedad Cells de ese objeto Range, obtendremos un "conjunto" de todas las celdas de ese rango.

¿Que es un conjunto? Un conjunto es un objeto que contiene otros objetos, generalmente del mismo tipo (la definición la he copiado de la ayuda de Windows). La propiedad Cells de un objeto Range nos devuelve un conjunto que contiene todas las celdas de ese rango.

Luego viene una instrucción "For". En la entrega anterior vimos una instrucción For, pero un poco distinta a esta. En ese caso lo que hacíamos era dar a una variable distintos valores, desde el 1 hasta el 10, y para cada valor de la variable ejecutábamos unas instrucciones. Análogamente, en esta ocasión vamos a dar a la variable "h" (de tipo Object) unos valores, y para cada valor de "h" vamos a ejecutar una instrucción.

Para dar los distintos valores a "h" vamos a utilizar la instrucción "For Each", que "repite un grupo de enunciados para cada elemento de una matriz o de un conjunto". El conjunto que vamos a utilizar es Selection.Cells, que como sabemos son todas las celdas del rango seleccionado. Es decir, que con

For Each h In Selection.Cells
   h.Value = h.Value + 1
Next h

lo que hacemos es ejecutar una instrucción para cada una de las celdas del conjunto Selection.Cells. La primera vez que se ejecute la instrucción de dentro del buble, h representará la primera celda del conjunto de celdas, la segunda vez representará la segunda celda del conjunto, etc. En este caso lo que hemos hecho ha sido sumar el valor uno a todas las celdas del rango seleccionado.

La instrucción que va dentro del bucle es la misma que en el ejemplo anterior; tan sólo fíjate que utilizamos "h" en lugar de ActiveCell.

Vamos a hacer otro pequeño ejemplo del uso de "For Each" y de conjuntos. En esta ocasión vamos a cambiar el nombre de todas las hojas del libro activo. Para obtener una referencia al libro activo utilizaremos la propiedad ActiveWorkbook del objeto Application. Para acceder a todas las hojas de cálculo del libro activo, utilizaremos la propiedad Worksheets de dicho objeto, ActiveWorkbook. He utilizado la propiedad Worksheets porque esta nos devuelve sólo las hojas de cálculo (no las hojas de gráficos, módulos, etc.). Si queremos acceder a todas las hojas (de cualquier tipo) del libro, utilizaremos la propiedad Sheets, que funciona igual que Worksheets.

Sub NombresHojas()
   Dim h As Object
   Dim i As Integer

   i = 15
   For Each h In ActiveWorkbook.Worksheets
      h.Name = "Hoja " & i
      i = i + 1
   Next h
End Sub

Lo que hace esta rutina es cambiar los nombres de las hojas de cálculo del libro activo, poniéndoles los nombres "Hoja15", "Hoja16", etc. No es que sea una rutina demasiado útil, pero como siempre sirve para aprender un poco.

Como siempre, empezamos con la declaración de variables. Luego le damos a la variable "i" el valor 15, porque nos da la gana, podíamos haberle dado cualquier otro. Luego viene lo que nos interesa, el "For Each". Para cada hoja de cálculo (Worksheets) del libro activo (ActiveWorkbook) vamos a ejecutar las dos instrucciones que están dentro del bucle.

Para cambiar el nombre de una hoja utilizamos la propiedad Name. Luego incrementamos el valor de "i" para dar el nombre a la hoja siguiente. (Si intentamos dar a dos hojas el mismo nombre Excel nos dará un mensaje de error)

Bueno, otra rutina para jugar un poco con hojas y celdas:

Sub ListaHojas()
    Dim h As Object
    Dim r As Range
    
    If MsgBox("Esta macro crea en la celda 
	activa una lista con los nombres de todas" & _
            Chr(13) & "las hojas del libro activo.", vbOK, 
			"Lista Hojas") = vbCancel _
        Then Exit Sub
    Set r = ActiveCell
    For Each h In ActiveWorkbook.Sheets
        r.Value = h.Name
        r.Offset(0, 1).Value = TypeName(h)
        Set r = r.Offset(1, 0)
    Next h
End Sub

Lo que hace esta rutina es crear en la hoja de cálculo activa una tabla con los nombres de todas las hojas del libro. Primero, con "Set r = ActiveCell", hacemos que la variable "r" apunte a la celda activa.

Luego viene el bucle "For Each / Next", que al igual que en el ejemplo anterior hace que el código entre estas dos líneas se ejecute una vez para cada hoja del libro. La única diferencia es que en el ejemplo anterior utilizábamos "ActiveWorkbook.Worksheets" (que pasa por todas las hojas de "cálculo" del libro) y aquí "ActiveWorkbook.Sheets" (que pasa por todas las hojas del libro, de todo tipo).

Primero damos a la celda "r" el nombre de la primera hoja del libro. En la siguiente línea, ponemos en a celda de la derecha de "r" (con el Offset(0,1)) el tipo de hoja de que se trata, utilizando la función "TypeName(h)": esta función devuelve una cadena de carácteres que proporciona información sobre la variable "h".

En la siguiente línea, utilizando otra vez Offset, "movemos" la variable "r" para que apunte a la celda inferior. Y así, vuelta a empezar.

Y para terminar, vamos a aprender a utilizar el método Copy, que se aplica a objetos de Excel que puedan ser copiados, como por ejemplo un objeto de tipo Range, aunque también a otros objetos como hojas enteras o gráficos.

Lo que vamos a hacer es copiar en la celda activa el contenido de la celda de su izquierda:

Sub Copiar_CeldaIzquierda()
    If MsgBox("Esta macro copia en la celda 
	activa el contenido de la celda situada" & _
       Chr(13) & "a su izquierda.", vbOK, "Copiar celda 
	   izquierda") = vbCancel Then Exit Sub
    ActiveCell.Offset(0, -1).Copy ActiveCell
End Sub

La sintaxis de Copy es muy sencilla: se ejecuta el método Copy sobre el rango de "origen", es decir, el que queremos copiar. En el ejemplo, hacemos "ActiveCell.Offset(0,-1).Copy", ya que queremos copiar la celda que está a la izquierda de la celda activa.

El parámetro del método Copy indica el rango de "destino" para la copia. En nuestro ejemplo ponemos "ActiveCell", ya que queremos que lo que hemos copiado se copie en la celda activa. Si omitimos el parámetro del método Copy la copia se hará en el "Portapapeles", y luego deberemos utilizar otros métodos como "Paste" o "PasteSpecial" para pegar el contenido del portapapeles en otra celda.

Hasta aquí llega esta segunda entrega del curso. En este capítulo hemos aprendido a trabajar con celdas, rangos, libros y hojas, que es una de las partes más útiles de la programación en Excel, permitiéndonos hacer cualquier operación (mover, copiar, borrar, hacer operaciones aritméticas, etc) con los datos de una hoja de cálculo.

Recuerda que los ejemplos de este capítulo están incluídos en este fichero:

Excel2.xls (39kbs)