Esta función nos permitirá la cuota a pagar de un préstamo en función del importe del mismo, el tipo de interés y el plazo en meses de amortización.
Esta función recibirá tres parámetros: Importe, interés y meses.
El código de esta función lo podéis encontrar en Usar funciones EXCEL en nuestras propias funciones (UDF).
Para llamar a esta función bastará con que nos coloquemos en una celda y la llamemos pasando los parámetros específicos: el importe del préstamo, el tipo de interés y el número de meses en el que deseamos amortizar el préstamo.
=CuotaPrestamo(150000,4,75%,360)
El resultado de esta función nos devolverá la cuota a pagar de nuestro préstamo.
8 de noviembre de 2008
Usar funciones EXCEL en nuestras propias funciones (UDF)
Aunque Visual Basic para Aplicaciones (VBA) incluye numerosas funciones equivalentes a las funciones integradas para hojas de cálculo de Excel, alguna vez nos puede ocurrir que prefiramos usar una determinada función estándar de Excel dentro de la función que estamos desarrollando en lugar de programar nosotros código específico para ello.
Se puede llamar a una función integrada de la hoja de cálculo de Excel directamente desde un código Visual Basic para Aplicaciones (VBA); con el objeto WorksheetFunction (válido a partir de Excel 97).
Cuándo desarrollemos funciones en VBA que deban usar funciones estándar de Excel, deberemos tener en cuenta que el nombre de la función que deberemos usar es el nombre de la función en inglés y no la localización específica para nuestro país. En este enlace encontrarás una equivalencia del nombre de las funciones estándar en inglés con las localizaciones específicas para España, Francia, Alemania, Italia, Portugal (y Brasil), Holanda, Finlandia y Suiza.
En el siguiente ejemplo, usaremos la función PAGO (de la localización de España) y que equivale a la función PMT de Excel en inglés.
El código de la función es el siguiente:
Public Function CuotaPrestamo(Importe As Double, Interes As Double, Meses As Integer) As Double
' Usamos la función PMT de Excel en inglés que equivale
' a la función PAGO de EXCEL localizada para España
CuotaPrestamo = WorksheetFunction.Pmt(Interes / 12, Meses, Importe) * -1
End Function
Para llamar a esta función bastará con que nos coloquemos en una celda y la llamemos pasando los parámetros específicos: el importe del préstamo, el tipo de interés y el número de meses en el que deseamos amortizar el préstamo.
=CuotaPrestamo(150000,4,75%,360)
El resultado de esta función nos devolverá la cuota a pagar de nuestro préstamo.
Se puede llamar a una función integrada de la hoja de cálculo de Excel directamente desde un código Visual Basic para Aplicaciones (VBA); con el objeto WorksheetFunction (válido a partir de Excel 97).
Cuándo desarrollemos funciones en VBA que deban usar funciones estándar de Excel, deberemos tener en cuenta que el nombre de la función que deberemos usar es el nombre de la función en inglés y no la localización específica para nuestro país. En este enlace encontrarás una equivalencia del nombre de las funciones estándar en inglés con las localizaciones específicas para España, Francia, Alemania, Italia, Portugal (y Brasil), Holanda, Finlandia y Suiza.
En el siguiente ejemplo, usaremos la función PAGO (de la localización de España) y que equivale a la función PMT de Excel en inglés.
El código de la función es el siguiente:
Public Function CuotaPrestamo(Importe As Double, Interes As Double, Meses As Integer) As Double
' Usamos la función PMT de Excel en inglés que equivale
' a la función PAGO de EXCEL localizada para España
CuotaPrestamo = WorksheetFunction.Pmt(Interes / 12, Meses, Importe) * -1
End Function
Para llamar a esta función bastará con que nos coloquemos en una celda y la llamemos pasando los parámetros específicos: el importe del préstamo, el tipo de interés y el número de meses en el que deseamos amortizar el préstamo.
=CuotaPrestamo(150000,4,75%,360)
El resultado de esta función nos devolverá la cuota a pagar de nuestro préstamo.
Calcular EDAD
Esta función nos permitirá calcular la edad de una persona. La función recibirá dos parámetros, el primero será la fecha de nacimiento y el segundo la fecha en la cuál deseamos calcular la edad.
El código de la función es el siguiente:
Public Function YearsOld(FNac As Date, FActual As Date) As Byte
' Calculamos la diferencia entre el año de nacimiento
' y la fecha en la cuál deseamos calcular la edad
YearsOld = Year(FActual) - Year(FNac)
If Month(FActual) < Month(FNac) Then
YearsOld = YearsOld - 1
ElseIf Month(FActual) = Month(FNac) And Day(FActual)< Day(FNac) Then
YearsOld = YearsOld - 1
End If
End Function
Para usar esta función la llamaremos desde cualquier celda de nuestra hoja indicándole las fechas entre las cuáles deseamos calcular la edad:
= YearsOld(Fecha(1968;08;15); Hoy())
Una vez invocada la función nos aparecerá la media del rango seleccionado.
NOTA:
El código de la función es el siguiente:
Public Function YearsOld(FNac As Date, FActual As Date) As Byte
' Calculamos la diferencia entre el año de nacimiento
' y la fecha en la cuál deseamos calcular la edad
YearsOld = Year(FActual) - Year(FNac)
If Month(FActual) < Month(FNac) Then
YearsOld = YearsOld - 1
ElseIf Month(FActual) = Month(FNac) And Day(FActual)< Day(FNac) Then
YearsOld = YearsOld - 1
End If
End Function
Para usar esta función la llamaremos desde cualquier celda de nuestra hoja indicándole las fechas entre las cuáles deseamos calcular la edad:
= YearsOld(Fecha(1968;08;15); Hoy())
Una vez invocada la función nos aparecerá la media del rango seleccionado.
NOTA:
- Fecha(año;mes;dia) es una función estándar de EXCEL que convierte un año, un mes y un día en una fecha válidad para EXCEL.
- Hoy() es una función estándar de EXCEL que devuelve la fecha actual.
Función con un RANGO como parámetro
En el siguiente ejemplo muestra como pasar un rango de celdas como parámetro de nuestra función.
Nuestra función recibirá un rango de celdas como parámetro y devolverá la media del rango seleccionado.
Nuestra función recibirá un rango de celdas como parámetro y devolverá la media del rango seleccionado.
Para ello crearemos un módulo de VBA, tal y como se indica en el apartado Crear un función personalizada (UDF). Una vez creado el módulo, añadiremos el siguiente código:
Public Function MediaRango(Rango As Range) As Double
Dim intRowsInRange As Integer
Dim intColsInRange As Integer
Dim intRow As Integer
Dim intCol As Integer
Dim intItems As Integer
' Obtenemos el número de filas y columnas del rango seleccionado
MediaRango = 0
intintems = 0
intRowsInRange = Rango.Rows.Count
intColsInRange = Rango.Columns.Count
' Recorremos las filas del rango
For intRow = 1 To intRowsInRange
' Recorremos las columnas del rango
For intCol = 1 To intColsInRange
MediaRango = MediaRango + Rango.Cells(intRow, intCol).Value
intItems = intItems + 1
Next intCol
Next intRow
' Calculamos la media del rango
MediaRango = MediaRango / intItems
End Function
Ahora ya sólo nos queda salvar la función y usarla desde nuestra hoja Excel. Para ello bastará con llamarla desde cualquier celda de nuestra hoja indicándole el rango de celdas del cuál deseamos calcular la media.
= MediaRango(B1:B3)
Una vez invocada la función nos aparecerá la media del rango seleccionado.
1 de noviembre de 2008
Agregar índice hojas EXCEL de un libro
Algunas veces, nuestros trabajos en EXCEL nos obliga a crear un número importante de hojas dentro de un libro.
Cuándo esto ocurre se nos hace muy incomodo el poder acceder a una determinada hoja, puesto que para buscarla debemos ir pulsando los botones de desplazamiento de hojas. Esto hace que la búsqueda de la hoja que deseamos sea lenta y tediosa.
A continuación presentamos una macro (o un procedimiento) que nos permitirá solucionar de una forma bastante elegante éste problema. Esta macro, nos añadirá automáticamente una hoja llamada “RESUMEN”, la cuál contendrá la lista de todas las hojas de nuestro libro, creando además un enlace ha ésta. El aspecto de la hoja resumen que nos añadirá es el siguiente:
De este modo, cuándo deseemos acceder a una hoja del libro, bastará con que nos situemos en la hoja “RESUMEN” y hagamos clic sobre el nombre de la hoja a la que deseamos acceder.
En este procedimiento utilizaremos la función SheetExists que nos permitirá conocer si una hoja Excel existe.
Para crear nuestra macro o procedimiento seguiremos los siguientes pasos:
- Abriremos el libro EXCEL en el cuál deseamos añadir nuestra macro.
- En el menú de EXCEL seleccionaremos Herramientas / Macro / Editor de Visual Basic
- Nos aparecerá el editor de Visual Basic (VBA, Visual Basic for Applications)
- En el menú del Editor seleccionaremos
- Esto nos añadirá un nuevo elemento en el área Proyecto – VBAProject
- Ahora escribiremos el siguiente código dentro del módulo que hemos creado (Modulo1).
Public Sub AddResumeSheets()
Dim CellCnt As Integer
Dim Sht As Object
Dim ListPos As Integer
' Verificamos si la hoja de Resumen de Hojas existe,
' sino es así, la creamos
If Not SheetExists("Resumen") Then
Sheets.Add
ActiveSheet.Select
ActiveSheet.Name = "Resumen"
End If
' Ponemos la hoja resume la primera de todas
Sheets("Resumen").Move
Before:=Sheets(1)
' Borramos todo el contenido de
Cells.Select
Selection.ClearContents
Columns("A:A").ColumnWidth = 70 ' Ajustamos ancho columna 'A'
' Añadir listado de las hojas EXCEL del Libro
Range("A1").Select
ActiveCell.Value ="LISTADO DE LAS HOJAS EXCEL DEL LIBRO"
With Selection.Font
.Name = "Arial"
.Size = 16
.Bold = True
End With
' Recorremos todas las hojas del libro
CellCnt = 3
For Each Sht In ActiveWorkbook.Sheets
' Añadimos en el resumen todas las hojas a excepción de la hoja
' RESUMEN que hemos creado
If Sht.Name <> ActiveSheet.Name Then
If TypeName(Sht) = "Worksheet" Then
If Sht.Visible = True Then
Range("A" & CellCnt).Select
ActiveCell.FormulaR1C1 = Sht.Name
ActiveSheet.Hyperlinks.Add _
Anchor:=Selection, _
Address:="", _
SubAddress:= "'" & Sht.Name & "'!A1", _
TextToDisplay:=Sht.Name
End If
End If
CellCnt = CellCnt + 1
End If
Next Sht
End Sub
Private Function SheetExists(strSheetName As String) As Boolean
On Error Resume Next
Dim objSheet As Object
' Seleccionamos la hoja de cálculo
Set objSheet = ActiveWorkbook.Sheets(strSheetName)
' Comprobamos si existe o no la hoja
If Err = 0 Then
SheetExists = True
Else
SheetExists = False
End If
End Function
- Una vez añadido todo el código en el módulo que hemos insertado, será necesario que nada más abrir el libro se ejecute la macro para que nos añada la hoja resumen, para ello seleccionaremos el elemento ThisWorkbook en el área Proyecto – VBAProject, y haremos doble click para añadir el siguiente código:
Private Sub Workbook_Open()
' Añadimos
' del LIBRO
AddResumeSheets
End Sub
- A continuación, y antes de continuar, guardaremos el trabajo realizado. Para ello iremos al menú del editor de Visual Basic y seleccionaremos Archivo / Guardar.
- Una vez guardado, saldremos del editor de Visual Basic, seleccionando las opciones Archivo / Cerrar y volver a Microsoft Excel.
En estos momentos, nuestra hoja EXCEL ya dispone de una macro que nos facilitará la búsqueda de hojas dentro de nuestro Libro de trabajo.
Verificar si un fichero existe
Esta función nos permitirá conocer si un determinado fichero existe en la ubicación especificada. La función recibira cómo parámetro la ruta y el nombre del fichero y devolverá verdadero en caso de que éste exista o falso en caso contrario.
El código de la función es el siguiente:
Private Function FileExists(strFilename As String) As Boolean
' Comprobamos si existe el fichero existe
If Dir(strFilename) = "" Then
FileExists = False
Else
FileExists = True
End If
End Function
El código de la función es el siguiente:
Private Function FileExists(strFilename As String) As Boolean
' Comprobamos si existe el fichero existe
If Dir(strFilename) = "" Then
FileExists = False
Else
FileExists = True
End If
End Function
Crear una función personalizada (UDF)
La mayoría de nosotros está acostumbrado a usar funciones estándar de EXCEL (como por ejemplo, SUMA, BUSCARV, SI, etc). A menudo nos ocurre que necesitaríamos una fución que nos facilitase el trabajo, pero nos encontramos que el estándard de EXCEL no dispone de esta.
Las funciones personalizadas (UDF, User Defined Function), nos van a permitir desarrollar nuestras propias funciones en nuestra hoja EXCEL. Esto nos va a permitir de dotar a nuestras hojas de una potencia que hasta ahora no tenían.
Vamos a crear nuestra primera función personalizada. Como ejemplo, desarrollaremos una par de funciones, estas son:
Para crear nuestras funciones seguiremos los siguientes pasos:
Public Function GradosF2C(gradosFahrenheit As Double) As Double
Las funciones personalizadas (UDF, User Defined Function), nos van a permitir desarrollar nuestras propias funciones en nuestra hoja EXCEL. Esto nos va a permitir de dotar a nuestras hojas de una potencia que hasta ahora no tenían.
Vamos a crear nuestra primera función personalizada. Como ejemplo, desarrollaremos una par de funciones, estas son:
- GradosC2F que convertirá grados Fahrenheit en grados Centígrados
- GradosF2C que convertirá grados Centígrados en grados Fahrenheit.
Para crear nuestras funciones seguiremos los siguientes pasos:
- Crearemos un nuevo Libro EXCEL de trabajo. Le llamaremos Temperaturas.xls
- En el menú de EXCEL seleccionaremos Herramientas / Macro / Editor de Visual Basic
- Nos aparecerá el editor de Visual Basic (VBA, Visual Basic for Applications)
- En el menú del Editor seleccionaremos la opción Insertar / Módulo
- Esto nos añadirá un nuevo elemento en el área Proyecto - VBAProject
- Ahora escribiremos el siguiente código dentro del módulo que hemos creado (Modulo1).
Public Function GradosC2F(gradosCelsius As Double) As Double
' Convertir grados centígrados (ºC) en grados Fahrenheit (ºF): ºF= (ºC x 1,8) + 32GradosC2F = (gradosCelsius * 1.8) + 32
End FunctionPublic Function GradosF2C(gradosFahrenheit As Double) As Double
' Convertir grados Fahrenheit (ºF) en grados centígrados (ºC): ºC = (ºF - 32) / 1,8
GradosF2C = (gradosFahrenheit - 32) / 1.8End Function
- A continuación, y antes de continuar, guardaremos el trabajo realizado. Para ello iremos al menú del editor de Visual Basic y seleccionaremos Archivo / Guardar Temperatura.xls.
- Una vez guardado, saldremos del editor de Visual Basic, seleccionando las opciones Archivo / Cerrar y volver a Microsoft Excel.
En estos momentos, nuestra hoja EXCEL ya dispone de las dos funciones que hemos creado, ahora sólo falta utilizarlas en nuestra hoja EXCEL.
Para poder utilizar nuestras funciones, bastará con que nos situaremos en cualquier celda de nuestra hoja. Vayamos la barra de edición de fórmulas y tecleemos el nombre de nuestra nueva función (GradosC2F ó GradosF2C). La siguiente figura muestra un ejemplo de uso de nuestras nuevas funciones:
Verificar si una hoja EXCEL existe
Esta función nos permitirá conocer si una determinada hoja existe dentro de un libro EXCEL. La función recibirá cómo parámetro el nombre de la hoja a buscar dentro del libro, y devolverá verdadero en caso de que ésta exista o falso en caso contrario.
El código de la función es el siguiente:
Private Function SheetExists(strSheetName As String) As Boolean
On Error Resume Next
Dim objSheet as Object
' Seleccionamos la hoja de cálculo
Set objSheet = ActiveWorkbook.Sheets(strSheetName)
' Comprobamos si existe o no la hoja
If Err = 0 Then
SheetExists = True
Else
SheetExists = False
End If
End Function
El código de la función es el siguiente:
Private Function SheetExists(strSheetName As String) As Boolean
On Error Resume Next
Dim objSheet as Object
' Seleccionamos la hoja de cálculo
Set objSheet = ActiveWorkbook.Sheets(strSheetName)
' Comprobamos si existe o no la hoja
If Err = 0 Then
SheetExists = True
Else
SheetExists = False
End If
End Function
Suscribirse a:
Entradas (Atom)
Entradas
