7/8/2015 EXCEL FORO: EJERCICIOS, EJEMPLOS, SOLUCIONES, DUDAS: VBA: Funciones de la hoja de cálculo en VBA.

data:text/html;charset=utf­8,%3Ch3%20class%3D%22post­title%20entry­title%22%20style%3D%22margin%3A%200px%3B%20padding%3A%200px%3B%2… 1/4

VBA: Funciones de la hoja de cálculo en VBA.Dedicaré hoy unas líneas para aclarar algo más el uso de funciones en nuestras macros. Es sabido por todos nosotrosque podemos generar nuestras propias funciones mediante los procedimientos Function, y que estas UDF (funcionespersonalizadas) pueden emplearse tanto en la hoja de cálculo como en otros procedimientos. Podemos ver un ejemplo deUDF en ver.Este aspecto, por lo general suele estar bastante claro.

Sobre lo que voy a hablar en esta entrada es del uso de las funciones estándar de la hoja de cálculo en nuestrosprocedimientos... hablo de las funciones habituales como COINCIDIR, BUSCAR, PAGO, o cualquier otra de listado de más de350 funciones existentes en Excel. E igualmente diferenciarlas de las funciones específicas de VBA.

Comenzaremos detallando cómo llamar a las funciones de la hoja de cálculo y la ventaja de usar dichas funciones. En VisualBasic las funciones de hoja de calculo de Excel pueden ejecutarse mediante elobjeto WorksheetFunction.Por ejemplo, para obtener el valor máximo de un rango de celdas, declararíamos una variable 'rng' como un objeto Rangey, a continuación, lo estableceríamos como el rango A1:B13 de la hoja 'Hoja1'. Asignamos una segunda variable, 'resp', seasigna al resultado de aplicar la función Max a 'rng'.Por último, el valor de respuesta se muestra en un cuadro de mensaje.

view plain print ?

01. Sub funcionHojaCalculo() 02. Dim rng As Range 03. 'definimos la variable rng como el rango A1:B13 04. Set rng = Worksheets("Hoja1").Range("A1:B13") 05. 'asociamos una segunda variable 'resp' 06. 'como el valor máximo del rango anterior 07. 'empleando la función de hoja de cálculo MAX 08. resp = Application.WorksheetFunction.Max(rng) 09. 'mostramos el resultado en un MsgBox 10. MsgBox resp 11. End Sub

Es fácil identificar qué funciones de hoja de cálculo podemos emplear, ya que al escribir el objeto WorksheetFunction.aparecerá una etiqueta con un desplegable de todas las funciones... Ojo por que como siempre en nuestro editor de VB, el nombre de las funciones aparecerán en su versión en inglés!!!.

Una ventaja de emplear estas funciones es que son fáciles de implementar en nuestros procedimientos, y con ellasevitamos ciertos desarrollos personalizados que sobrecargarían nuestras macros. En general, siempre que sea posible, esmás óptimo emplear estas funciones que escribir algún código que realice la misma acción.

Otra manera de trabajar con nuestras funciones de Hoja de cálculo en nuestros procedimientos, es la deInsertar unafunción de hoja de cálculo en una celda.Para insertar una función de hoja de cálculo en una celda, especificaremos la función como el valor de la propiedadFormula del objeto Range correspondiente.Podemos ver un ejemplo en la siguiente entrada.O el siguiente ejemplo, en el que la función ALEATORIO (que genera un número aleatorio ­ RAND en inglés) se asigna a lapropiedad Formula del rango B1:D5 de la Hoja1 del libro activo.

view plain print ?

7/8/2015 EXCEL FORO: EJERCICIOS, EJEMPLOS, SOLUCIONES, DUDAS: VBA: Funciones de la hoja de cálculo en VBA.

data:text/html;charset=utf­8,%3Ch3%20class%3D%22post­title%20entry­title%22%20style%3D%22margin%3A%200px%3B%20padding%3A%200px%3B%2… 2/4

01. Sub InsertarFormula() 02. 'insertamos en un rango de la hoja de cálculo 03. 'la función ALEATORIO ­ RAND en inglés 04. Worksheets("Hoja1").Range("B1:D5").Formula = "=RAND()" 05. End Sub

Ojo por que como siempre en nuestro editor de VB, el nombre de las funciones aparecerán en su versión en inglés!!!.Si queremos emplear la notacion en el lenguaje de nuestro sistema, emplearemos la propiedad .formulalocal en vez de.formula:

view plain print ?

01. Sub InsertarFormula() 02. 'insertamos en un rango de la hoja de cálculo 03. 'la función ALEATORIO 04. Worksheets("Hoja2").Range("B1:D5").FormulaLocal = "=ALEATORIO()" 05. End Sub

Nos queda por hablar de las funciones de VBA. Las funciones de Visual Basic no usan el calificadorWorksheetFunction.Una peculiaridad de las funciones de VB es que puede tener el mismo nombre que una función de hoja de cálculo y, sinembargo, podrían dar otros resultados...Veamos un ejemplo sencillo, donde hemos empleado algunas funciones de VBA (Rnd, Int, Abs o Sgn):

view plain print ?

01. Sub FuncionVBA() 02. Dim aleatorio As Long 03. 'definimos los valores del intervalo 04. sup = Application.Max(Range("A1:A10")) 05. inf = Application.Min(Range("A1:A10")) 06. 'producimos un entero aleatorio en el intervalo dado 07. aleatorio = Int((sup ­ inf + 1) * Rnd + inf) 08. 'mostramos el aleatorio en un MsgBox 09. MsgBox "1:= " & aleatorio 10. 11. valor = Application.Average(sup, inf) 12. 'con Sgn determino signo de la media 13. 'y luego multiplico por la parte entera del valor absoluto dicha media 14. aleatorio2 = Sgn(valor) * Int(Abs(valor)) 15. MsgBox "2:= " & aleatorio2 16. End Sub

En general, en este último uso, habrá que tener especial cuidado en diferenciar propiedades de algún objeto, como porejemplo, Max, Min o Average, de lo que son estrictamente hablando Funciones de VBA; ya que las primeras requieren ladefinición del objeto (normalmente Application), frente a las funciones que pueden ser usadas directamente.Un listado (no completo) de las funciones de Visual Basic más empleadas sería:

FUNCIÓN DESCRIPCIÓNAbs Devuelve el valor absoluto de un númeroAsc Obtiene el valor ASCII del primer caracter de una cadena de textoCBool Convierte una expresión a su valor booleanoCByte Convierte una expresión al tipo de dato ByteCCur Convierte una expresión al tipo de dato moneda (Currency)CDate Convierte una expresión al tipo de dato fecha (Date)CDbl Convierte una expresión al tipo de dato doble (Double)CDec Convierte una expresión al tipo de dato decimal (Decimal)Choose Selecciona un valor de una lista de argumentosChr Convierte un valor ANSI en valor de tipo textoCInt Convierte una expresión en un dato de tipo entero (Integer)CLng Convierte una expresión en un dato de tipo largo (Long)

7/8/2015 EXCEL FORO: EJERCICIOS, EJEMPLOS, SOLUCIONES, DUDAS: VBA: Funciones de la hoja de cálculo en VBA.

data:text/html;charset=utf­8,%3Ch3%20class%3D%22post­title%20entry­title%22%20style%3D%22margin%3A%200px%3B%20padding%3A%200px%3B%2… 3/4

CreateObject Crea un objeto de tipo OLECStr Convierte una expresión en un dato de tipo texto (String)CurDir Devuelve la ruta actualCVar Convierte una expresión en un dato de tipo variant (Variant)Date Devuelve la fecha actual del sistemaDateAdd Agrega un intervalo de tiempo a una fecha especificadaDateDiff Obtiene la diferencia entre una fecha y un intervalo de tiempo especificadoDatePart Devuelve una parte específica de una fechaDateSerial Convierte una fecha en un número serialDateValue Convierte una cadena de texto en una fechaDay Devuelve el día del mes de una fechaDir Devuelve el nombre de un archivo o directorio que concuerde con un patrónEOF Devuelve verdadero si se ha llegado al final de un archivoFileDateTime Devuelve la fecha y hora de la última modificación de un archivoFileLen Devuelve el número de bytes en un archivoFormatCurrency Devuelve un número como un texto con formato de monedaFormatPercent Devuelve un número como un texto con formato de porcentajeHour Devuelve la hora de un valor de tiempoIIf Devuelve un de dos partes, dependiendo de la evaluación de una expresiónInputBox Muestra un cuadro de diálogo que solicita la entrada del usuarioInStr Devuelve la posición de una cadena de texto dentro de otra cadenaInStrRev Devuelve la posición de una cadena de texto dentro de otra cadena pero empezando desde el finalInt Devuelve la parte entera de un númeroIsDate Devuelve verdadero si la variable es una fechaIsEmpty Devuelve verdadero si la variable está vacíaIsError Devuelve verdadero si la expresión es un valor de errorIsNull Devuelve verdadero si la expresión es un valor nuloIsNumeric Devuelve verdadero si la variable es un valor numéricoJoin Devuelve una cadena de texto creada al unir las cadenas contenidas en un arrregloLBound Devuelve un tipo Long que contiene el subíndice más pequeño disponible para la dimensión indicada de una matriz.LCase Devuelve una cadena convertida en minúsculasLeft Devuelve un número específico de caracteres a la izquierda de una cadenaLen Devuelve la longitud de una cadena (en caracteres)LTrim Elimina los espacios a la izquierda de una cadenaMid Extrae un número específico de caracteres de una cadena de textoMinute Devuelve el minuto de una dato de tiempoMonth Devuelve el mes de una fechaMsgBox Despliega un cuadro de dialogo con un mensaje especificadoNow Devuelve la fecha y hora actual del sistemaReplace Reemplaza una cadena de texto con otraSpace Devuelve una cadena de texto con el número de espacios especidicadosSplit Devuelve un arreglo formado for cadenas de texto que formaban una sola cadenaStr Devuelve la representación en texto de un númeroRight Devuelve un número especificado de carecteres a la derecha de una cadena de textoRnd Devuelve un número aleatorio entre 0 y 1Round Redondea un número a una cantidad específica de decimalesRTrim Elimina los espacios en blanco a la derecha de una cadena de textoSecond Devuelve los segundos de un dato de tiempoStrComp Compara dos cadenas de textoStrReverse Invierte el orden de los caracteres de una cadenaTime Devuelve el tiempo actual del sistemaTimer Devuelve el número de segundos desde la media nocheTimeValue Convierte una cadena de texto a un númer de serie de tiempoTrim Elimina los espacios en blanco al inicio y final de una cadena de textoTypeName Obtiene el nombre del tipo de dato de una variableUBound Devuelve un tipo Long que contiene el mayor subíndice disponible para la dimensión indicada de una matriz.UCase Convierte una cadena de texto en mayúsculasVal Devuelve el número contenido en una cadena de textoWeekday Devuelve un número que representa un día de la semanaWeekdayName Devuelve el nombre de un día de la semana

7/8/2015 EXCEL FORO: EJERCICIOS, EJEMPLOS, SOLUCIONES, DUDAS: VBA: Funciones de la hoja de cálculo en VBA.

data:text/html;charset=utf­8,%3Ch3%20class%3D%22post­title%20entry­title%22%20style%3D%22margin%3A%200px%3B%20padding%3A%200px%3B%2… 4/4

Year Obtiene el año de una fecha