Código VBA de una Macro para calcular préstamos en Excel

1595

Excel nos permite realizar cálculos de todo tipo, es uno de los software más potentes y completos que podemos tener. Una de sus principales atractivos es que puedes activar la pestaña Programador con la que multiplicarás las posibilidades de este programa.

A través de las Macros podemos automatizar tareas simples y complejas que sean repetitivas. Esto nos permitirá ahorrar una gran cantidad de tiempo. Es cierto que tendrás que saber cómo crear una macro en Excel, pero hoy en día puedes crearlas prácticamente sin saber nada de VBA, que es el lenguaje en el cual se crean.

Puedes simplemente conocer algunos aspectos básicos de VBA para aplicarlos a tus macros. Por ejemplo, con saber como se seleccionan filas, columnas y como funcionan los bucles, ya podrías empezar a crear macros realmente complejas. Esto es debido a que el resto de parámetros o modificaciones los puedes aprender o copiar de las macros que grabes.

Por ejemplo, si quieres saber cómo se formatea una celda (color, formato de número, etc.) podrás grabar una macro y observar qué código realiza esa tarea y usarlo en la macro que quieras. Además Excel cuenta con una amplia comunidad de personas y desarrolladores que comparten sus macros y están dispuestos a ayudarte.

Calcular préstamos a través de una Macro

Una tarea como calcular un préstamo también se puede realizar a través de una macro. Nosotros hemos desarrollado una macro que calcula préstamos simples. Su funcionamiento es bastante sencillo.

Simplemente tendremos que introducir la cantidad, el número de meses que dura el préstamo y el tipo de interés anual al que está sujeto. Luego, se genera un cuadro de amortización automático en el que podremos ver como se articula el préstamo. Además, podremos volver a pulsar en el botón para calcular un nuevo crédito. Es una macro bastante útil si en tu día a día tienes que andar realizando este tipo de cálculos.

Para que veas mejor como funciona, te dejamos un vídeo para que te hagas una idea.

Lo que realmente hace la macro es lo siguiente:

  1. Se borra el contenido y el formato de las celdas que intervienen en el proceso. Esto lo he hecho para que cada vez que se presione el botón se borre todo lo que había antes.
  2. Luego creamos 3 cajas para introducir texto (InputBox), que recogerán la cuantía del préstamo, el los meses y el tipo de interés. A estas cajas le he puesto condiciones (tienen que ser números mayores que cero y no pueden ser otros caracteres que no sean números). Y se ha puesto un pequeño en negrita el texto que saldrá en la celda contigua.
  3. A la cuantía y al tipo de interés le damos formato (moneda y porcentaje)
  4. Empezamos a crear la tabla de amortización. Cada cabecera de las columnas es formateada con el mismo color de letra y fondo.
  5. Tratamos a cada columna por separado introduciendo las fórmulas oportunas y creando bucles (For) para que rellenen cada celda.
  6. Para finalizar, la macro autoajustará las columnas automáticamente para adaptarse a la amplitud de su contenido. También le pasamos la tecla ESC.

En resumidas cuentas, este es el proceso general que se realiza cada vez que se pulsa el botón “Calcular préstamo”. Antes de poner el archivo para descargar y el código de la macro tengo que hacer algunas aclaraciones:

A tener en cuenta…

Hay parte del código como los formatos que podrían ser más breves, ahorrándonos código. Pero hemos decidido introducirlo así para que aquellos que tengan curiosidad y empiecen a crear sus propias macros, sepan y entiendan más o menos como funciona todo de una manera más clara. Obviamente si tienes un mayor grado de conocimiento, podrás reducir estas partes de código y hacer que funcione de manera más eficiente.

La segunda aclaración que quisiera hacer es que al crear una macro hay que tener cuidado con las fórmulas, porque las que se usan en VBA son las inglesas y no las que tengas en tu Excel. Por ejemplo, la fórmula para calcular la cuota del préstamo en Excel sería PAGO(……..) la equivalente en inglés es PMT(……..), su uso es el mismo, pero cambia la nomenclatura. Lo mismo sucede con el resto de fórmulas, así que si te interesa crear tus propias macros ten a mano una tabla con las equivalencias entre unas funciones y otras.

En el siguiente enlace podrás descargar el archivo de Excel con la macro para calcular préstamos y el código utilizado. Si no sabes como ver la macro en un Excel puedes consultar nuestra entrada sobre cómo crear una macro.

Macro para calcular préstamos

Código VBA para calcular préstamos en Excel

A continuación podrás copiar y pegar el código en tu Excel. Podrás utilizarlo tal como está, modificarlo, etc.

Sub prestamo()

Sheets("Hoja1").Select
Range("A1").Select
Range("B6:G1000").Select
Selection.ClearContents
Selection.ClearFormats
Range("B1:B3").Select
Selection.ClearContents
Range("A1").Select

'Creamos un cuado de texto para introducir la cuantía del préstamo
'si introducimos un importe menor que 0 o que no es numérico
'volverá a pedirnos la cuantía

cuantia:

Mensaje = "Introduce la cuantía del préstamo"
Título = "Importe del préstamo"
estandar = 1000

importe = InputBox(Mensaje, titulo, estandar)

If importe <= 0 Then GoTo cuantia
If IsNumeric(importe) = False Then GoTo cuantia

'Ponemos en A1 el texto del campo

Range("A1").Select
Selection.Font.Bold = True
ActiveCell = "Cantidad"
Range("B1").Select
Selection = importe

'Hacemos lo mismo con los meses, creamos un cuadro de texto
'para introducir la cantidad de meses que durará el préstamo
'si el valor es menor que 0 o no es numérico nos mandará introducirlo de nuevo

meses:

Mensaje = "Introduce el número de meses"
Título = "Número de meses"
estandar = 12

meses = InputBox(Mensaje, titulo, estandar)

If meses <= 0 Then GoTo meses
If IsNumeric(meses) = False Then GoTo meses

'Creamos el nombre del campo

Range("A2").Select
Selection.Font.Bold = True
ActiveCell = "Nº de meses"
Range("B2").Select
Selection = meses

'Volvemos a hacer lo mismo con los intereses, creamos un cuadro de texto
'para introducir el tipo de interés que tendrá el préstamo
'si el valor es menor que 0 o no es numérico nos mandará introducirlo de nuevo

interes:

Mensaje = "Introduce el tipo de interés anual"
Título = "Tipo de interés"
estandar = 3

interes = InputBox(Mensaje, titulo, estandar)

If interes <= 0 Then GoTo interes
If IsNumeric(interes) = False Then GoTo interes

'Introducimos el nombre del campo correspondiente

Range("A3").Select
Selection.Font.Bold = True
ActiveCell = "Tipo de interés anual"
Range("B3").Select
Selection = interes / 100

'Damos formato a algunas celdas, concretamete a la del importe y el tipo de interés

formato:

Range("B1").Select
Selection.NumberFormat = "#,##0.00 $"
Range("B3").Select
Selection.NumberFormat = "0.00%"

'Creamos la tabla del préstamo. Lo primero que creamos son los textos de las columnas
'luego creamos los bucles con los cálculos pertinentes

tabla:

'texto columnas

Range("B6").Select

Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With

ActiveCell = "Mes"

Range("C6").Select

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With

Selection.Font.Bold = True
ActiveCell = "Cuota mensual"

Range("D6").Select

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With

Selection.Font.Bold = True
ActiveCell = "Intereses"

Range("E6").Select

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With

Selection.Font.Bold = True
ActiveCell = "Amortización"

Range("F6").Select

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With

Selection.Font.Bold = True
ActiveCell = "Capital Vivo"

Range("G6").Select

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With

Selection.Font.Bold = True
ActiveCell = "Capital Amortizado"

'Nº de meses

Range("B7").Select
For contador = 0 To meses
Cells(contador + 7, 2).Select

Set actual = Application.ActiveCell
actual.Value = contador

Next contador

'Cálculo cuota

Range("C8").Select

For contador = 1 To meses
ActiveCell.Formula = "=PMT($B$3/12,$B$2,-$B$1)"
Selection.Copy
Cells(contador + 7, 3).Select
Selection.PasteSpecial

Next contador

'Cálculo intereses

Range("D8").Select

ActiveCell.Formula = "=IPMT($B$3/12,B8,$B$2,-$B$1)"
Selection.Copy

For contador = 1 To meses
Cells(contador + 7, 4).Select
Selection.PasteSpecial

Next contador

'Cálculo de la amortización

Range("E8").Select

ActiveCell.Formula = "=C8-D8"
Selection.Copy

For contador = 1 To meses

Cells(contador + 7, 5).Select
Selection.PasteSpecial

Next contador

'Cálculo del capital vivo

Range("F7").Select
Selection = "=B1"

Range("F8").Select

ActiveCell.Formula = "=F7-E8"
Selection.Copy

For contador = 1 To meses
Cells(contador + 7, 6).Select
Selection.PasteSpecial

Next contador

'Cálculo del capital amortizado

Range("G8").Select

ActiveCell.Formula = "=G7+E8"
Selection.Copy

For contador = 1 To meses

Cells(contador + 7, 7).Select
Selection.PasteSpecial

Next contador

'Autoajuste de columnas

Worksheets("Hoja1").Columns("A:G").AutoFit

'Tecla de escape

SendKeys "{ESC}"

End Sub

Crear el botón que lance la macro

Simplemente copiando el código anterior, tendremos la macro lista para hacerla funcionar seleccionándola desde el botón “Macros” de la pestaña “Programador”, pero no tendremos un botón como el que que se ve en el vídeo para lanzarla. Para crearlo es muy sencillo, haremos lo siguiente:

1. Le damos a Insertar.

crear boton lanzar macro

 

2. Tenemos dos opciones. Insertar un control de formulario o un control Activex. Podemos usar ambos pero nos decantaremos por el primero.

botones lanzar macro

 

3. Una vez seleccionado procederemos a colocarlo sobre nuestra hoja de Excel y se abrirá la siguiente ventana.

asignar boton a macro

4. En esta ventana nos aparecerán las macros que tenemos y seleccionamos aquella que queremos vincular con el botón, en nuestro caso “Préstamo”. Una vez hecho esto, ya podremos lanzar la macro pulsándolo.

Por último, si encuentras algún error en la macro o tienes alguna pregunta no dudes en comentarla. También puedes aportar nuevas sugerencias.