Escribir formulas en la hoja desde VBA

Puede descargar el archivo

• CuadroAmortizacion_con_Formulas.xlsm

Hoja 1

Disponemos de cuatro valores numéricos en las celdas amarillas (rango B4:B7). Deseamos calcular la media de estos valores desde una macro. Existen dos métodos:

• La macro calcula la media y la deja en una celda de la hoja de cálculo como valor
• La macro deja en al celda de la hoja de cálculo la fórmula de Excel que calculará la media

La diferencia entre ambos métodos radica en que en el primero lo que se ve en la fórmula es simplemente un VALOR, no hay fórmula. Por tanto, si los valores de las celdas amarillas cambian la media no se actualiza hasta que se vuelva a lanzar la macro.

Por el contrario, si lo que hacemos es pedirle a la macro que deje la fórmula en la celda, lo que veremos en la celda será la fórmula que estará siempre actualizada aunque los valores de las celdas amarillas cambien.

El primer método se hace con la macro Media1 y el segundo método se hace con diferentes variantes con las macros Media2, Media3, Media4 y Media5.

Sub Media1()
Range("D4") = WorksheetFunction.Average(Range("B4:B7"))
End Sub
Sub Media2()
Range("D5").Formula = "=(B4+B5+B6+B7)/4"
End Sub
Sub Media3()
Range("D6").Formula = "=Average(B4:B7)"
End Sub
Sub Media4()
Dim R As Range
Set R = Range("B4:B7")
Range("D7").Formula = "=Average(" & R.Address & ")"
End Sub
Sub Media5()
Range("D8").Formula = "=Average(" & Range("B4:B7").Address & ")"
End Sub
Sub Limpia()
Worksheets("Hoja1").Range("D4:D8").ClearContents
End Sub
Sub Genera()
Range("B4") = Evaluate("=Rand()")
Range("B5") = Evaluate("=int(Rand()*100)")
Range("B6") = WorksheetFunction.RandBetween(10, 100)
Range("B7") = Evaluate("=int(Rand()*90)+10")
End Sub

Observe que Media1 usa WorksheetFunciton.Función en inglés
Este es el médodo que hace que en la celda veamos al valor que calcula la fórmula. La función PROMEDIO en inglés es AVERAGE.

Las macros Media2 a Media5 usan la expresión Rango.Formula="=Formula en inglés"
Esto permite dejar la formula en la celda. Al depositar la fórmula en la celda lo hace en el idioma en el que estemos trabajando en Excel, en nuestro caso la fórmula quedará en español.

Observe que la macro Genera lo que hace es proporcionar valores aleatorios a las celdas amarillas. Observe el uso de EVALUATE que lo que hace es dejar el valor en la celda y no la fórmula.

Hoja 2

Vamos a crear el cuadro de amortización de un préstamo tipo francés. Disponemos de dos macros una de ellas genera el cuadro calculando en VBA los valores de las celdas y luego deposita en la hoja estos valores. En este caso no veremos en las celdas las fórmulas, únicamente se podrá ver el valor numérico calculado. Si variamos los datos de partida (celdas de color rosa) el cuadro no se altera.

La segunda macro deposita en cada celda la fórmula que permitirá calcular cada uno de los conceptos financieros del cuadro de amortización. Si cambiamos el principal del préstamo o el tipo de interés nomina (TIN) el cuadro se actualizará ya que son fórmulas. Si alteramos los años será necesario volver a lanzar la macro para que se ajuste el tamaño del cuadro.

• Las celdas rosas contienen los datos del caso práctico.
• La macro CuadroAmortizacion1 (primer botón) genera el cuadro con valores. Usa WorksheetFunction.Pmt(….)
• La macro CuadroAmortizacion2 (segundo botón) genera el cuadro con fórmulas. Usa Rango.Formula="PMT(….)"
• Si usamos la segunda macro, al llevar fórmulas, podremos introducir valores en la columna H que corresponde a la amortización anticipada (AA)

Sub CuadroAmortizacion1()
Dim Principal As Double
Dim TIN As Double
Dim annos As Double
Dim i12 As Double
Dim n As Double 'meses
Dim i As Integer
Worksheets("Hoja2").Activate
Borra
Principal = [C4]
TIN = [C5]
annos = [C6]
i12 = TIN / 12
n = annos * 12
[B9] = 0
[F9] = Principal
For i = 1 To n
  Cells(i + 9, "B") = i
  Cells(i + 9, "C") = WorksheetFunction.Pmt(i12, n - (i - 1), -Cells(i + 8, "F")) + Cells(i + 9, "H")
  Cells(i + 9, "D") = i12 * Cells(i + 8, "F")
  Cells(i + 9, "E") = Cells(i + 9, "C") - Cells(i + 9, "D")
  Cells(i + 9, "E") = Cells(i + 9, "C") - Cells(i + 9, "D")
  Cells(i + 9, "F") = Cells(i + 8, "F") - Cells(i + 9, "E")
  Cells(i + 9, "G") = Principal - Cells(i + 9, "F")
Next i
Bordes
End Sub

En relación a la columna de Amortización Anticipada (AA), la primera macro calculará bien el cuadro si hemos puesto los valores de la AA en las celdas de la columna H antes de lanzar la macro 1, pero no lo hará correctamente si añadimos importe de la AA después de haber lanzado la macro 1.

Sub CuadroAmortizacion2()
Dim n As Double 'meses
Dim i As Integer
Worksheets("Hoja2").Activate
Borra
n = [F6]
For i = 0 To n
  Cells(i + 8, "B") = i
Next i
[F9].Formula = "=C4"
[C10].Formula = "=Pmt($F$5,$F$6-B9,-F9)+ H10"
[D10].Formula = "=$F$5*F9"
[E10].Formula = "=C10-D10"
[F10].Formula = "=F9-E10"
[G10].Formula = "=$C$4-F10"
'Copiamos las fórmulas del periodo 1 al resto del cuadro
Range("C10:G10").AutoFill Destination:=Range("C10:G" & n + 9)
Range("C10:G" & n + 9).Select
Range("A1").Select
Bordes
End Sub

Por el contrario, la macro 2 al generar fórmulas sobre la hoja admite que podamos añadir valores a la columna H de Amortización Anticipada incluso después de haber lanzado la macro. Esta es una diferencia importante.