Cómo utilizar las funciones correspondiente Hoja de trabajo como BUSCARV en Excel VBA?
Las funciones como VLOOKUP, COUNTIF, SUMIF se denominan funciones de hoja de trabajo. Generalmente, las funciones que están predefinidas en Excel y listas para usar en una hoja de trabajo son funciones de hoja de trabajo. No puede alterar o ver el código detrás de estas funciones en VBA.
Por otro lado, las funciones definidas por el usuario y las funciones específicas de VBA como MsgBox o InputBox son funciones de VBA. Todos sabemos cómo usar las funciones de VBA en VBA. Pero, ¿qué pasa si queremos usar VLOOKUP en un VBA? ¿Como hacemos eso? En este artículo, exploraremos exactamente eso.
Uso de funciones de hoja de trabajo en VBA
Para acceder a la función de la hoja de trabajo usamos una clase de Aplicación. Casi todas las funciones de la hoja de trabajo se enumeran en la clase Application.WorksheetFunction. Y usando el operador de puntos, puede acceder a todos ellos.
En cualquier sub, escriba Application.WorksheetFunction. Y empieza a escribir el nombre de la función. Intellisense de VBA mostrará el nombre de las funciones disponibles para usar. Una vez que haya elegido el nombre de la función, le pedirá las variables, como cualquier función en Excel. Pero tendrá que pasar variables en formato VBA comprensible. Por ejemplo, si desea pasar un rango A1: A10, tendrá que pasarlo como un objeto de rango como Rango («A1: A10»).
Así que usemos algunas funciones de la hoja de trabajo para entenderlo mejor.
Cómo utilizar la función VLOOKUP en VBA
Para demostrar cómo puede usar una función BUSCARV en VBA, aquí tengo datos de muestra. Necesito mostrar el nombre y la ciudad de la identificación de inicio de sesión dada en un cuadro de mensaje usando VBA. Los datos se distribuyen en el rango A1: K26.
Presione ALT + F11 para abrir VBE e insertar un módulo. Consulte el siguiente código.
Sub WsFuncitons()
Dim loginID As String
Dim name, city As String
loginID = "AHKJ_1-3357042451"
'Using VLOOKUP function to get name of given id in table
name = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 2, 0)
'Using VLOOKUP function to get city of given id in table
city = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 4, 0)
MsgBox ("Name: " & name & vbLf & "City: " & city)
End Sub
Cuando ejecute este código, obtendrá este resultado.
Puede ver la rapidez con la que el VBA imprime el resultado en un cuadro de mensaje. Ahora examinemos el código.
¿Cómo funciona?
1.
Dim loginID como cadena
Dim nombre, ciudad como cadena
Primero hemos declarado dos variables de tipo cadena para almacenar el resultado devuelto por la función BUSCARV. He usado variables de tipo cadena porque estoy seguro de que el resultado devuelto por VLOOKUP será un valor de cadena. Si se espera que la función de la hoja de trabajo devuelva el tipo de valor de número, fecha, rango, etc., use ese tipo de variable para almacenar el resultado. Si no está seguro de qué tipo de valor devolverá la función de la hoja de trabajo, utilice variables de tipo de variante.
2.
loginID = «AHKJ_1-3357042451»
A continuación, hemos utilizado la variable loginID para almacenar el valor de búsqueda. Aquí hemos utilizado un valor codificado. También puedes usar referencias. Por ejemplo.
Puede usar Range («A2»). Value para actualizar dinámicamente el valor de búsqueda del rango A2.
3.
name = Application.WorksheetFunction.VLookup (loginID, Range («A1: K26»), 2, 0) Aquí usamos la función VLOOKUP para obtener. Ahora, cuando corrija la función y abra el paréntesis, le mostrará los argumentos requeridos pero no tan descriptivos como se muestra en Excel. Ver por ti mismo.
Debe recordar cómo y qué variable debe usar. Siempre puede volver a la hoja de trabajo para ver los detalles descriptivos de la variable.
Aquí, el valor de búsqueda es Arg1. Para Arg1 usamos loginID. La tabla de búsqueda es Arg2. Para Arg2 usamos Range («A1: K26»). Tenga en cuenta que no usamos directamente A2: K26 como lo hacemos en Excel. El índice de columnas es Arg3.
Para Arg3 usamos 2, ya que el nombre está en la segunda columna. El tipo de búsqueda es Arg4. Usamos 0 como Arg4.
ciudad = Application.WorksheetFunction.VLookup (loginID, Range («A1: K26»), 4, 0)
Del mismo modo, obtenemos el nombre de la ciudad.
4.
MsgBox («Nombre:» & nombre & vbLf & «Ciudad:» & ciudad)
Finalmente imprimimos nombre y ciudad usando Messagebox.
¿Por qué usar la función de hoja de trabajo en VBA? Las funciones de la hoja de trabajo poseen el poder de realizar cálculos inmensos y no sería inteligente ignorar el poder de las funciones de la hoja de trabajo. Por ejemplo, si queremos la desviación estándar de un conjunto de datos y desea escribir código completo para esto, puede llevarle horas. Pero si sabe cómo usar la función de hoja de trabajo STDEV.P en VBA para obtener el cálculo de una vez.
Sub GetStdDev()
std = Application.WorksheetFunction.StDev_P(Range("A1:K26"))
End Sub
Usando múltiples funciones de hoja de trabajo VBA
Digamos que necesitamos usar una coincidencia de índice para recuperar algunos valores. Ahora, ¿cómo escribirías la fórmula en VBA? Esto es lo que supongo que escribirás:
Sub IndMtch() Val = Application.WorksheetFunction.Index(result_range, _ Application.WorksheetFunction.Match(lookup_value, _ lookup_range, match_type)) End Sub
Esto no está mal, pero es largo. La forma correcta de usar múltiples funciones es usando un bloque With. Vea el siguiente ejemplo:
Sub IndMtch() With Application.WorksheetFunction Val = .Index(result_range, .Match(lookup_value, lookup_range, match_type)) val2 = .VLookup(arg1, arg2, arg3) val4 = .StDev_P(numbers) End With End Sub
Como puede ver, he usado With block para decirle a VBA que usaré las propiedades y funciones de Application.WorksheetFunction. Así que no necesito definirlo en todas partes. Acabo de usar el operador de puntos para acceder a las funciones INDICE, MATCH, VLOOKUP y STDEV.P. Una vez que usamos la instrucción End With, no podemos acceder a las funciones sin usar nombres de funciones completamente calificados.
Entonces, si tiene que usar varias funciones de hoja de trabajo en VBA, use con block.
No todas las funciones de la hoja de trabajo están disponibles a través de Application.WorksheetFunction Algunas funciones de la hoja de trabajo están disponibles directamente para su uso en VBA. No es necesario utilizar el objeto Application.WorksheetFunction.
Por ejemplo, funciones como Len () que se usa para obtener el número de caracteres en una cadena, izquierda, derecha, mitad, recorte, desplazamiento, etc. Estas funciones se pueden usar directamente en VBA. Aquí hay un ejemplo.
Sub GetLen() Strng = "Hello" Debug.Print (Len(strng)) End Sub
Mira, aquí usamos la función LEN sin usar el objeto Application.WorksheetFunction.
Del mismo modo, puede utilizar otras funciones como left, right, mid, char, etc.
Sub GetLen() Strng = "Hello" Debug.Print (Len(strng)) Debug.Print (left(strng,2)) Debug.Print (right(strng,1)) Debug.Print (Mid(strng, 3, 2)) End Sub
Cuando ejecute el sub anterior, devolverá:
5 He o ll
Así que sí, chicos, así es como pueden usar la función de hoja de trabajo de Excel en VBA. Espero haber sido lo suficientemente explicativo y este artículo te haya ayudado. Si tiene alguna pregunta sobre este artículo o cualquier otra cosa relacionada con VBA, pregunte en la sección de comentarios a continuación. Hasta entonces, puede leer sobre otros temas relacionados a continuación.
Artículos relacionados:
link: / excel-macros-and-vba-what-is-csng-function-in-excel-vba [Qué es la función CSng en Excel VBA] | La función SCng es una función VBA que convierte cualquier tipo de datos en un número de punto flotante de precisión simple («dado que es un número»). Utilizo principalmente la función CSng para convertir números con formato de texto en números reales.
link: / vba-how-to-get-text-number-in-reverse-through-vba-in-microsoft-excel [Cómo obtener texto y número en reverso a través de VBA en Microsoft Excel] | * Para invertir el número y texto usamos bucles y función intermedia en VBA. 1234 se convertirá en 4321, «usted» se convertirá en «uoy». Aquí está el fragmento.
enlace: / formateo-en-vba-formato-tabla-dependiendo-de-formatos-numéricos-usando-vba-en-microsoft-excel [Formatear datos con formatos numéricos personalizados usando VBA en Microsoft Excel] | Para cambiar el formato numérico de columnas específicas en Excel, use este fragmento de VBA. Convierte el formato numérico del formato especificado al formato especificado con un solo clic.
link: / tips-using-worksheet-change-event-to-run-macro-when-any-change-is-made [Uso de evento de cambio de hoja de trabajo para ejecutar macro cuando se realiza cualquier cambio] | Entonces, para ejecutar su macro cada vez que se actualiza la hoja, usamos los Eventos de la hoja de trabajo de VBA.
link: / events-in-vba-run-macro-if-any-change-made-on-sheet-range [Ejecutar macro si se realiza algún cambio en la hoja en el rango especificado] | Para ejecutar su código de macro cuando cambia el valor en un rango específico, use este código VBA. Detecta cualquier cambio realizado en el rango especificado y disparará el evento.
link: / events-in-vba-simple-vba-code-to-highlight-current-row-and-column-using [Código VBA más simple para resaltar la fila y columna actual usando] | Utilice este pequeño fragmento de VBA para resaltar la fila y columna actuales de la hoja.
Artículos populares:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-aumentar-su-productividad [50 accesos directos de Excel para aumentar su productividad] | Acelera tu tarea. Estos 50 atajos lo harán trabajar aún más rápido en Excel.
enlace: / fórmulas-y-funciones-introducción-de-función-vlookup [La función BUSCARV en Excel] | Esta es una de las funciones más utilizadas y populares de Excel que se utiliza para buscar valores de diferentes rangos y hojas.
enlace: / tips-countif-in-microsoft-excel [COUNTIF en Excel 2016] | Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar valores específicos.
La función Countif es esencial para preparar su tablero.
link: / excel-formula-and-function-excel-sumif-function [Cómo usar la función SUMIF en Excel] | Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.