Cómo usar las funciones de la hoja de cálculo VBA en Excel 2016

  1. Software
  2. Oficina de Microsoft
  3. Excel
  4. Cómo usar las funciones de la hoja de cálculo VBA en Excel 2016

Libro Relacionado

Programación de Excel VBA para Dummies, 4ª Edición

Por John Walkenbach

Aunque VBA ofrece un buen surtido de funciones incorporadas, es posible que no siempre encuentre exactamente lo que necesita. Afortunadamente, también puede utilizar la mayoría de las funciones de hoja de cálculo de Excel en sus procedimientos de VBA. Las únicas funciones de la hoja de trabajo que no puede utilizar son las que tienen una función VBA equivalente. Por ejemplo, no puede utilizar la función RAND de Excel (que genera un número aleatorio) porque VBA tiene una función equivalente: Rnd.

VBA hace que las funciones de hoja de cálculo de Excel estén disponibles a través del objeto WorksheetFunction, que se encuentra en el objeto Application. He aquí un ejemplo de cómo puede utilizar la función SUM de Excel en una sentencia VBA:

Total = Hoja de trabajo de la aplicaciónFunción.SUM(Rango("A1:A12"))

Puede omitir la parte de la aplicación o la parte de la función de la hoja de cálculo de la expresión. En cualquier caso, VBA se da cuenta de lo que está haciendo. En otras palabras, estas tres expresiones funcionan exactamente igual:

Total = Hoja de trabajo Función SUM(Rango("A1:A12"))Total = Hoja de trabajo Función SUM(Rango("A1:A12"))Total = Hoja de trabajo SUM(Rango("A1:A12"))

Mi preferencia personal es usar la parte WorksheetFunction sólo para dejar perfectamente claro que el código está usando una función de Excel.

Ejemplos de funciones de la hoja de trabajo

Aquí descubrirá cómo utilizar las funciones de las hojas de cálculo en sus expresiones VBA.

Encontrar el valor máximo en un rango

He aquí un ejemplo que muestra cómo utilizar la función de hoja de cálculo MAX de Excel en un procedimiento VBA. Este procedimiento muestra el valor máximo en la columna A de la hoja de trabajo activa:

Usando una función de hoja de trabajo en su código VBA.

 Sub ShowMax() Dim TheMax As Double TheMax = WorksheetFunction.MAX(Range("A:A")) MsgBox TheMaxEnd Sub

Puede utilizar la función MIN para obtener el valor mínimo de un rango. Y como es de esperar, puede utilizar otras funciones de la hoja de trabajo de manera similar. Por ejemplo, puede utilizar la función LARGE para determinar el valor más grande de kth en un rango. La siguiente expresión lo demuestra:

Segundo más alto = Hoja de trabajoFunción.LARGE(Rango ("A:A"),2)

Observe que la función LARGE utiliza dos argumentos. El segundo argumento representa la parte kth – 2, en este caso (el segundo valor más grande).

Calculando un pago de hipoteca

El siguiente ejemplo utiliza la función de hoja de trabajo PMT para calcular un pago de hipoteca. Se utilizan tres variables para almacenar los datos que se pasan a la función Pmt como argumentos. Un cuadro de mensaje muestra el pago calculado.

Sub PmtCalc() Dim IntRate As Double Dim LoanAmt As Double Dim Periods As Long IntRate = 0.0625 / 12 Periods = 30 * 12 LoanAmt = 150000 MsgBox WorksheetFunction.PMT(IntRate, Periods, -LoanAmt)End Sub

Como muestra la siguiente sentencia, también puede insertar los valores directamente como argumentos de la función:

Hoja de trabajo MsgBoxFunction.PMT(0.0625 /12, 360, -150000)

Sin embargo, el uso de variables para almacenar los parámetros hace que el código sea más fácil de leer y modificar, si es necesario.

Utilización de una función de búsqueda

El siguiente ejemplo utiliza las funciones InputBox y MsgBox de VBA, además de la función VLOOKUP de Excel. Solicita un número de pieza y luego obtiene el precio de una tabla de búsqueda. Abajo, el rango A1:B13 se llama Lista de precios.

La gama, denominada Lista de precios, contiene precios de las piezas.

 Sub GetPrice() Dim PartNum As Variant Dim Price As Double PartNum = InputBox("Enter the Part Number") Sheets("Prices") Activate Price = WorksheetFunction.VLOOKUP(PartNum, Range("PriceList"), 2, False) MsgBox PartNum & " costs " & PriceEnd Subtítulo

Así es como funciona el procedimiento GetPrice:

  • La función InputBox de VBA pide al usuario un número de pieza

.

  • El número de pieza que el usuario introduce se asigna a la variable PartNum.
  • La siguiente sentencia activa la hoja de trabajo Precios, por si acaso no es ya la hoja activa.
  • El código utiliza la función VLOOKUP para encontrar el número de pieza en la tabla.
  • Observe que los argumentos que utiliza en esta expresión son los mismos que los que utilizaría con la función en una fórmula de hoja de cálculo. Esta expresión asigna el resultado de la función a la variable Precio.
  • El código muestra el precio de la pieza a través de la función MsgBox.
  • Este procedimiento no tiene ningún manejo de errores, y falla miserablemente si ingresa un número de parte inexistente. (Pruébelo.) Si se tratara de una aplicación real que se utiliza en un negocio real, le convendría añadir algunas frases que traten los errores con más gracia.

    Introducción de funciones de hoja de trabajo

    No puede utilizar el cuadro de diálogo Pegar función de Excel para insertar una función de hoja de cálculo en un módulo VBA. En su lugar, ingrese a tales funciones de la manera tradicional: a mano. Sin embargo, puede utilizar el cuadro de diálogo Pegar función para identificar la función que desea utilizar y conocer sus argumentos.

    También puede aprovechar la opción Miembros de la lista automática de VBE, que muestra una lista desplegable de todas las funciones de la hoja de trabajo. Simplemente escriba Application.WorksheetFunction, seguido de un punto. A continuación, verá una lista de las funciones que puede utilizar. Si esta función no funciona, elija el comando Herramientas de VBE → Opciones, haga clic en la ficha Editor y coloque una marca junto a Miembros de la lista automática.

    Obtener una lista de funciones de la hoja de trabajo que puede utilizar en su código VBA.

    Más información sobre el uso de las funciones de las hojas de trabajo

    Los recién llegados a VBA a menudo confunden las funciones incorporadas de VBA con las funciones del libro de trabajo de Excel. Una buena regla a recordar es que VBA no intenta reinventar la rueda. En su mayor parte, VBA no duplica las funciones de las hojas de cálculo de Excel.

    Para la mayoría de las funciones de la hoja de trabajo que no están disponibles como métodos del objeto WorksheetFunction, puede utilizar un operador o una función VBA integrada equivalente. Por ejemplo, la función de hoja de cálculo MOD no está disponible en el objeto WorksheetFunction porque VBA tiene un equivalente: su operador Mod incorporado.

    ¿En resumen? Si necesita utilizar una función, primero determine si VBA tiene algo que satisfaga sus necesidades. Si no es así, revise las funciones de la hoja de trabajo. Si todo lo demás falla, es posible que pueda escribir una función personalizada utilizando VBA.

    Post Your Thoughts