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

Loan Calculator in Excel VBA

Nota: estas instrucciones no le enseñan cómo dar formato a la hoja de trabajo. Suponemos que ya sabe cómo cambiar los tipos de letra, insertar filas y columnas, bordes añadir, cambiar los colores de fondo, etc.

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

  1. Añadir los dos controles de la barra de desplazamiento. Haga clic en Insertar y una de la ficha Programador continuación, haga clic en la barra de desplazamiento en la sección Controles ActiveX.

Create Scroll Bars

  1. Añadir los dos botones de opción. Haga clic en Insertar y una de la ficha Programador continuación, haga clic en el botón de opción en la sección Controles ActiveX.

Create Option Buttons

Cambie las siguientes propiedades de los controles de la barra de desplazamiento (asegúrese de que se selecciona el modo de diseño).

  1. Haga clic con el ratón en el primer control de barra de desplazamiento y, a continuación, haga clic en Propiedades. Conjunto Min a 0, máxima a 20, SmallChange a 0 y 2. LargeChange a

  2. Haga clic con el ratón en el segundo control de barra de desplazamiento y, a continuación, haga clic en Propiedades. Conjunto min a 5, máxima a 30, SmallChange a 1, LargeChange a 5, y LinkedCell a F8.

Explicación: cuando se hace clic en la flecha, el valor de barra de desplazamiento sube o baja por SmallChange. Al hacer clic entre el cursor y la flecha, el valor de barra de desplazamiento sube o baja por LargeChange.

Crear un evento de cambio de hoja de cálculo. Código añadido al evento de cambio de hoja de trabajo será ejecutado por Excel VBA cuando se cambia una celda en una hoja de cálculo.

  1. Abra el Editor de Visual Basic.

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

  3. Seleccione la hoja de trabajo de la lista desplegable-izquierda y seleccione Cambiar en la lista desplegable-derecha.

Worksheet Change Event in Excel VBA

  1. La hoja de evento de cambio de escucha a todos los cambios en la Hoja 1. Sólo queremos Excel VBA para ejecutar el sub Calcular si algo cambia en la celda D4.

Para lograr esto, añada la siguiente línea de código al evento de cambio de hoja de cálculo (más sobre el sub Calcular más adelante).

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

  1. Obtener el porcentaje correcto en la celda F6 (cambiar el formato de la celda F6 al porcentaje). Botón derecho del ratón en el primer control de barra de desplazamiento, y luego haga clic en Ver código. Añadir las siguientes líneas de código:

Private Sub ScrollBar1_Change()

Range("F6").Value = ScrollBar1.Value / 100

Application.Run "Calculate"

End Sub

  1. Haga clic con el ratón en el segundo control de barra de desplazamiento y, a continuación, haga clic en Ver código. Agregue la siguiente línea de código:

Private Sub ScrollBar2_Change()

Application.Run "Calculate"

End Sub

  1. Haga clic con el ratón en el primer control de botón de opción y, a continuación, 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

  1. botón derecho del ratón en el segundo control de botón de opción y, a continuación, 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

  1. Tiempo para crear la sub. Puede ir a través de nuestro capítulo Función y Sub para aprender más sobre los submarinos. Si se tiene prisa, simplemente coloque el sub llamado Calcular 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 adecuados para la función de hoja de Pmt. La función PAGO de 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 utiliza tasa / 12 por la tasa y NPER * 12 para NPER (número total de pagos). El resultado es un número negativo, porque los pagos se consideran una de débito. Multiplicando el resultado por -1 da un resultado positivo.

Loan Calculator Result