Cómo cambiar referencias relativas y absolutas

Como ya sabrás, se pueden introducir fórmulas utilizando referencias de celdas relativas o absolutas. Por ejemplo, si la dirección en una fórmula es “A1”, es relativa, es decir, si copiamos la fórmula a una nueva ubicación, la dirección de la celda también cambia en relación a su ubicación anterior. Si utilizamos una dirección absoluta “$A$1”, no importará dónde se copie, la referencia seguirá siendo la misma celda.

Es muy fácil cambiar una referencia de celda en una fórmula para que sea absoluta, pero ¿qué podemos hacer si tenemos en nuestra hoja de cálculo cientos de referencias que cambiar? Hacerlo manualmente puede generar errores en la hoja de cálculo. Podemos escribir código para convertir automáticamente las fórmulas de relativas a absolutas en un sencillo procedimiento, pudiendo además ofrecer a los usuarios opciones acerca de qué tipo de referencia quieren incluir en sus fórmulas. Por ejemplo, podemos ofrecer la opción de fila y columna absolutas ($A$1), columna relativa y fila absoluta (A$1), columna absoluta y fila relativa ($A1) o columna y fila relativas (A1).

Para ofrecer al usuario la posibilidad de elegir el tipo de referencia, será necesario insertar un UserForm. También habrá que definir una variable global llamada Cancelar para comprobar si el usuario hace clic en el botón Cencelar del formulario. Para crear la variable insertaremos esta instrucción en un módulo estándar:

Global Cancelar As Integer

Un conjunto de botones de opción en un Frame hace que sea más fácil que el usuario pueda ver lo que está sucediendo. También insertaremos dos botones de comando (Aceptar y Cancelar). Arrastra los controles desde el cuadro de herramientas hasta un formulario en blanco.

Insertamos:

• Dos CommandButton (Aceptar y Cancelar)
• Un marco
• Cuatro OptionButton

Ahora cambiaremos la propiedad Caption de cada control.

Los botones Aceptar y Cancelar necesitan algo de código para que funcionen. Haz doble clic en uno de los botones e inserta el siguiente código:

Private Sub CommandButton1_Click()
      UserForm1.Hide
      canc = 
End Sub

Y ahora en el otro botón:

Private Sub CommandButton2_Click()
      UserForm1.Hide
      canc = 1
End Sub

En los dos casos, el UserForm se oculta utilizando el método Hide, que lo hace invisible, y luego pasa a la ejecución del código. La variable Cancelar que definimos antes, se establece en 0 si el usuario hace clic en Aceptar y 1 si hace clic en Cancelar. Esto nos servirá para saber sobre qué botón hizo clic el usuario y actuar en consecuencia.

El código principal de nuestro procedimiento, debemos insertarlo en el mismo módulo que el código anterior, y es el siguiente:

Sub conv_formula()
  UserForm1.Show
  If canc = 1 Then Exit Sub
  If UserForm1.OptionButton1.Value = True Then act = xlRelative
  If UserForm1.OptionButton2.Value = True Then act = xlRelRowAbsColumn
  If UserForm1.OptionButton4.Value = True Then act = xlAbsolute
  If UserForm1.OptionButton3.Value = True Then act = xlAbsRowRelColumn
  For Each window In Windows
    For Each Worksheet In window.SelectedSheets
       For Each cell In Application.Selection
         addr = Worksheet.Name & "!" & cell.Address
           If Range(addr).HasFormula = True Then Range(addr).Formula = _ 
           	Application.ConvertFormula(Formula:=Range(addr).Formula, _ 
              fromreferencestyle:=xlA1, toreferencestyle:=xlA1, toabsolute:=act)
       Next cell
   Next worksheet
   Next window
 End Sub

Pasamos a explicar el código anterior:

Lo primero que hace el procedimiento es mostrar el UserForm que hemos creado usando el método Show, y se queda esperando a que el usuario realice una selección y pulse alguno de los botones (Aceptar o Cancelar).

Si el usuario pulsa el botón Cancelar, el formulario se oculta y el procedimiento finaliza inmediatamente. Si se selecciona un botón de opción y se pulsa en Aceptar, también se oculta el formulario y el código principal se ejecuta.

Las siguiente cuatro lineas comprueban qué opción ha tomado el usuario. Ten en cuenta que solamente se puede seleccionar una opción, que toma el valor Verdadero. Automáticamente las demás opciones tomarán el valor Falso.

En función de qué opción ha sido seleccionada se ejecuta una de las 4 condiciones, que asigna a la variable act un valor constante.

A continuación, el código se desplaza por todas las ventanas de la colección y evalua cada celda de esa ventana.

La variable addr se carga con el nombre de la hoja y la dirección de la celda. A continuación se comprueba que la celda contiene una fórmula. Si es así, la fórmula se convertirá utilizando el método ConvertFormula y la constante proporcionada por los botones de opción.