Función de hoja de trabajo de VBA | ¿Cómo utilizar WorksheetFunction en VBA?

Funciones de la hoja de trabajo de Excel VBA

La función de hoja de trabajo en VBA se usa cuando tenemos que referirnos a una hoja de trabajo específica, normalmente cuando creamos un módulo, el código se ejecuta en la hoja actualmente activa del libro de trabajo, pero si queremos ejecutar el código en la hoja de trabajo específica, usamos la función de hoja de trabajo, esta función tiene varios usos y aplicaciones en VBA.

Lo mejor de VBA es que, como usamos fórmulas en la hoja de trabajo de manera similar, VBA también tiene sus propias funciones. Si esto es lo mejor, entonces también tiene algo hermoso que es "también podemos usar funciones de hoja de trabajo en VBA".

¡¡¡Si!!! Lo escuchó bien, también podemos acceder a las funciones de la hoja de trabajo en VBA. Podemos acceder a algunas de las funciones de la hoja de trabajo mientras escribimos el código y hacerlo parte de nuestro código.

¿Cómo usar las funciones de la hoja de trabajo en VBA?

Puede descargar esta plantilla de hoja de trabajo de VBA aquí - Plantilla de hoja de trabajo de VBA

En la hoja de trabajo, todas las fórmulas comienzan con el signo igual (=), de manera similar en la codificación VBA para acceder a las fórmulas de la hoja de trabajo, debemos usar la palabra "WorksheetFunction".

Antes de ingresar cualquier fórmula de la hoja de trabajo, debe mencionar el nombre del objeto “WorksheetFunction” y luego poner un punto (.) Y luego obtendrá una lista de todas las funciones disponibles bajo este objeto.

En este artículo, nos concentraremos exclusivamente en cómo usar la función de hoja de trabajo en la codificación VBA, lo que agregará más valor a su conocimiento de codificación.

# 1 - Funciones simples de la hoja de trabajo SUM

Ok, para comenzar con las funciones de la hoja de trabajo, aplique la función SUMA simple en Excel para agregar números de la hoja de trabajo.

Suponga que tiene datos de costos y ventas mensuales en la hoja de trabajo como la siguiente.

En B14 y C14 necesitamos llegar al total de los números anteriores. Siga los pasos a continuación para iniciar el proceso de aplicación de la función "SUMA" en Excel VBA.

Paso 1: cree un nombre de macro de Excel simple.

Código:

 Sub Worksheet_Function_Example1 () End Sub

Paso 2: Como necesitamos el resultado en la celda B14, inicie el código como Rango ("B14"). Valor =

Código:

 Sub Worksheet_Function_Example1 () Rango ("B14"). Valor = End Sub

Paso 3: En B14 necesitamos el valor como resultado de la suma de los números. Entonces, para acceder a la función SUMA desde la hoja de trabajo, inicie el código como "WorksheetFunction".

Código:

Sub Worksheet_Function_Example1 () Rango ("B14"). Valor = WorksheetFunction. End Sub

Paso 4: En el momento en que ponga un punto (.) Comenzará a mostrar las funciones disponibles. Así que seleccione SUM de esto.

Código:

 Sub Worksheet_Function_Example1 () Rango ("B14"). Valor = WorksheetFunction.Sum End Sub

Paso 5: Ahora dé la referencia de los números anteriores, es decir, Rango ("B2: B13").

Código:

 Sub Worksheet_Function_Example1 () Range ("B14"). Value = WorksheetFunction.Sum (Range ("B2: B13")) End Sub

Paso 6: De manera similar, para la siguiente columna, aplique el código similar cambiando las referencias de celda.

Código:

 Sub Worksheet_Function_Example1 () Range ("B14"). Value = WorksheetFunction.Sum (Range ("B2: B13")) Range ("C14"). Value = WorksheetFunction.Sum (Range ("C2: C13")) End Sub

Paso 7: Ahora ejecute este código manualmente o usando la tecla F5 para tener un total en las celdas B14 y C14.

Vaya, tenemos nuestros valores. Una cosa que debe notar aquí es que no tenemos ninguna fórmula en la hoja de trabajo, pero acabamos de obtener el resultado de la función "SUMA" en VBA.

# 2 - Use VLOOKUP como una función de hoja de trabajo

Veremos cómo usar VLOOKUP en VBA. Suponga que a continuación se encuentran los datos que tiene en su hoja de Excel.

En la celda E2, había creado una lista desplegable de todas las zonas.

Según la selección que realizó en la celda E2, necesitamos obtener el código PIN para la zona respectiva. Pero esta vez a través de VBA VLOOKUP, no de la hoja de trabajo VLOOKUP. Siga los pasos a continuación para aplicar VLOOKUP.

Paso 1: Cree un nombre de macro simple en el Subprocedimiento.

Código:

 Sub Worksheet_Function_Example2 () End Sub

Paso 2: Necesitamos el resultado en la celda F2. Así que comience el código como Rango ("F2"). Valor =

Código:

 Sub Worksheet_Function_Example2 () Rango ("F2"). Valor = End Sub

Paso 3: Para acceder a la función de la hoja de trabajo BUSCARV inicia el código como " Función de la hoja de trabajo.VLOOKUP ".

Código:

 Sub Worksheet_Function_Example2 () Rango ("F2"). Valor = WorksheetFunction.Vlookup (End Sub

Paso 4: Uno de los problemas aquí es que la sintaxis no le dará ningún tipo de guía para trabajar con BUSCARV. Debe estar absolutamente seguro de la sintaxis en la que está trabajando.

La primera sintaxis de BUSCARV es "Valor de búsqueda". En este caso, nuestro valor de búsqueda es el valor de celda E2, así que escriba el código como Rango ("E2"). Valor

Código:

 Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value, End Sub

Step 5: Now the second argument is our table array, in this case, our table array range is from A2 to B6. So the code will be Range (“A2:B6”)

Code:

 Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value,Range ("A2:B6"), End Sub

Step 6: The Third argument will be from which column we need the data from the table array. Here we need the data from the 2nd column, so the argument will be 2.

Code:

 Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value,Range ("A2:B6"),2, End Sub

Step 7: The final argument is range lookup, we need an exact match so the argument is zero (0).

Code:

 Sub Worksheet_Function_Example2() Range("F2").Value = WorksheetFunction.VLookup(Range("E2").Value, Range("A2:B6"), 2, 0) End Sub

So, we are done with the coding part. Now go to the worksheet and select any of the range.

Now go to your coding module and run the macro Using F5 key or manually to get the pin code of the selected zone.

We cannot go back and run the macro every time, so let’s assign a macro to shapes. Insert one of the shapes in a worksheet.

Add a text value to inserted shape.

Now right click and assign the macro name to this shape.

Click on ok after selecting the macro name.

Now, this shape holds the code of our VLOOKUP formula. So whenever you change the zone name click on the button, it will update the values.

Things to Remember

To access worksheet functions we need to write the word “WorksheetFunction” or “Application.WorksheetFunction”
We don’t have access to all the functions only a few.
We don’t see the actual syntax of worksheet functions, so we need to be absolutely sure of the function we are using.