/ / Calculadora de préstamos en Excel VBA

Calculadora de préstamos en Excel VBA

Esta página te enseña cómo crear un simple calculadora de préstamo en Excel VBA. La hoja de trabajo contiene los siguientes controles ActiveX: dos barras de desplazamiento y dos botones de opción.

Calculadora de préstamos en Excel VBA

Nota: las instrucciones a continuación no le enseñan cómo formatear la hoja de trabajo. Suponemos que sabe cómo cambiar los tipos de fuente, insertar filas y columnas, agregar bordes, cambiar los colores de fondo, etc.

Ejecute los siguientes pasos para crear la calculadora de préstamos:

1. Agregue los dos controles de la barra de desplazamiento. Haga clic en Insertar en la pestaña Desarrollador y luego haga clic en Barra de desplazamiento en la sección Controles ActiveX.

Crear barras de desplazamiento

2. Agregue los dos botones de opción. Haga clic en Insertar en la pestaña Desarrollador y luego haga clic en el botón de opción en la sección Controles ActiveX.

Crear botones de opción

Cambie las siguientes propiedades de los controles de la barra de desplazamiento (asegúrese de que esté seleccionado el Modo de diseño).

3. Haga clic con el botón derecho del mouse en el primer control de la barra de desplazamiento y luego haga clic en Propiedades. Establezca Min en 0, Max en 20, SmallChange en 0 y LargeChange en 2.

4. Haga clic con el botón derecho del mouse en el segundo control de la barra de desplazamiento y luego haga clic en Propiedades. Establezca Min a 5, Max a 30, SmallChange a 1, LargeChange a 5 y LinkedCell a F8.

Explicación: al hacer clic en la flecha, el valor de la barra de desplazamiento sube o baja en SmallChange. Cuando hace clic entre el control deslizante y la flecha, el valor de la barra de desplazamiento sube o baja en LargeChange.

Crear un evento de cambio de hoja de trabajo. El código agregado al evento de cambio de la hoja de trabajo será ejecutado por Excel VBA cuando cambie una celda en una hoja de trabajo.

5. Abra el Editor de Visual Basic.

6. Haga doble clic en Sheet1 (Sheet1) en el Explorador de proyectos.

7. Elija Hoja de trabajo de la lista desplegable de la izquierda y elija Cambiar de la lista desplegable de la derecha.

Evento de cambio de hoja de cálculo en Excel VBA

8. El evento de cambio de la hoja de trabajo escucha todos los cambios en la Hoja1. Solo queremos que Excel VBA ejecute el subálculo Calcular si algo cambia en la celda D4. Para lograr esto, agregue la siguiente línea de código al Evento de cambio de la hoja de trabajo (más información sobre el subálculo Calcular más adelante).

If Target.Address = "$D$4" Then Application.Run "Calculate"

9. Obtenga el porcentaje correcto en la celda F6 (cambie el formato de la celda F6 al porcentaje). Haga clic con el botón derecho del mouse en el primer control de la barra de desplazamiento y luego haga clic en Ver código. Agregue las siguientes líneas de código:

Private Sub ScrollBar1_Change()

Range("F6").Value = ScrollBar1.Value / 100
Application.Run "Calculate"

End Sub

10. Haga clic con el botón derecho del mouse en el segundo control de la barra de desplazamiento y luego haga clic en Ver código. Agregue la siguiente línea de código:

Private Sub ScrollBar2_Change()

Application.Run "Calculate"

End Sub

11. Haga clic con el botón derecho del mouse en el primer control del botón de opción y luego haga clic en Ver código. Agregue la siguiente línea de código:

Private Sub OptionButton1_Click()

If OptionButton1.Value = True Then Range("C12").Value = "Monthly Payment"
Application.Run "Calculate"

End Sub

12. Haga clic con el botón derecho del mouse en el segundo control de botón de opción y luego haga clic en Ver código. Agregue la siguiente línea de código:

Private Sub OptionButton2_Click()

If OptionButton2.Value = True Then Range("C12").Value = "Yearly Payment"
Application.Run "Calculate"

End Sub

13. Es hora de crear el sub. Puede consultar nuestro Capítulo de funciones y subcapítulos para obtener más información sobre los subs. Si tiene prisa, simplemente coloque el sub nombre Cálculo en un módulo (en el Editor de Visual Basic, haga clic en Insertar, Módulo).

Sub Calculate()

Dim loan As Long, rate As Double, nper As Integer

loan = Range("D4").Value
rate = Range("F6").Value
nper = Range("F8").Value

If Sheet1.OptionButton1.Value = True Then
    rate = rate / 12
    nper = nper * 12
End If

Range("D12").Value = -1 * WorksheetFunction.Pmt(rate, nper, loan)

End Sub

Explicación: el sub obtiene los parámetros correctos para la función de hoja de trabajo Pmt. La función Pmt en Excel calcula los pagos de un préstamo basándose en pagos constantes y una tasa de interés constante. Si realiza pagos mensuales (Sheet1.OptionButton1.Value = True), Excel VBA usa tasa / 12 para tasa y nper * 12 para nper (número total de pagos). El resultado es un número negativo, porque los pagos se consideran un débito. Multiplicando el resultado por -1 da un resultado positivo.

Resultado de la calculadora de préstamos

Lea también: