Hur använder jag kalkylbladsfunktioner som VLOOKUP i VBA Excel?

Innehållsförteckning:

Anonim

Funktionerna som VLOOKUP, COUNTIF, SUMIF kallas kalkylbladsfunktioner. I allmänhet är de funktioner som är fördefinierade i Excel och redo att användas i ett kalkylblad kalkylbladsfunktioner. Du kan inte ändra eller se koden bakom dessa funktioner i VBA.

Å andra sidan är de användardefinierade funktionerna och funktionerna specifika för VBA som MsgBox eller InputBox VBA -funktioner.

Vi vet alla hur man använder VBA -funktioner i VBA. Men tänk om vi vill använda VLOOKUP i en VBA. Hur gör vi det? I den här artikeln kommer vi att utforska exakt det.

Använda kalkylbladsfunktioner i VBA

För att komma åt kalkylbladets funktion använder vi en applikationsklass. Nästan alla kalkylbladsfunktioner listas i Application.WorksheetFunction -klassen. Och med hjälp av punktoperatör kan du komma åt dem alla.

I en del skriver du Application.WorksheetFunction. Och börja skriva namnet på funktionen. VBA: s intellisense visar namnet på de funktioner som är tillgängliga att använda. När du väl har valt funktionsnamnet kommer det att be om variablerna, precis som vilken funktion som helst i Excel. Men du måste passera variabler i VBA -begripligt format. Om du till exempel vill passera ett intervall A1: A10 måste du skicka det som ett intervallobjekt som Range ("A1: A10").

Så låt oss använda några kalkylbladsfunktioner för att förstå det bättre.

Hur man använder VLOOKUP -funktionen i VBA

För att demonstrera hur du kan använda en VLOOKUP -funktion i VBA, här har jag provdata. Jag måste visa namn och stad för det givna inloggnings -id i en meddelanderuta med VBA. Data sprids inom intervall A1: K26.

Tryck på ALT+F11 för att öppna VBE och sätt in en modul.

Se koden nedan.

Sub WsFuncitons () Dim loginID As String Dim name, city As String loginID = "AHKJ_1-3357042451" 'Använda VLOOKUP-funktionen för att få namnet på det givna id i tabellnamn = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26" ), 2, 0) 'Använda VLOOKUP -funktionen för att hämta stad med givet id i tabellstad = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 4, 0) MsgBox ("Namn:" & namn & vbLf & "Stad:" & stad) Slut Sub 

När du kör den här koden får du detta resultat.

Du kan se hur snabbt VBA skriver ut resultatet i en meddelanderuta. Låt oss nu undersöka koden.

Hur fungerar det?

1.

Dim loginID som sträng

Dim namn, stad As String

Först har vi deklarerat två variabler av strängtyp för att lagra resultatet som returneras av VLOOKUP -funktionen. Jag har använt strängtypvariabler eftersom jag är säker på att resultatet som returneras av VLOOKUP kommer att vara ett strängvärde. Om din kalkylbladsfunktion förväntas returnera nummer, datum, intervall, etc. typ av värde, använd den typen av variabel för att lagra resultatet. Om du inte är säker på vilken typ av värde som ska returneras av kalkylbladsfunktionen använder du variabler av varianttyp.

2.

loginID = "AHKJ_1-3357042451"

Därefter har vi använt loginID -variabeln för att lagra uppslagsvärde. Här har vi använt ett hårdkodat värde. Du kan också använda referenser. Till exempel. Du kan använda intervall ("A2"). Värde för att dynamiskt uppdatera uppslagsvärde från område A2.

3.

name = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 2, 0)

Här använder vi VLOOKUP -funktionen för att få. Nu när du höger funktionen och öppnar parentesen, kommer det att visa dig nödvändiga argument men inte så beskrivande som det visas i Excel. Se efter själv.

Du måste komma ihåg hur och vilken variabel du behöver använda. Du kan alltid gå tillbaka till kalkylbladet för att se den beskrivande variabeln.

Här är uppslagsvärdet Arg1. För Arg1 använder vi loginID. Uppslagstabellen är Arg2. För Arg2 använde vi Range ("A1: K26"). Observera att vi inte använde direkt A2: K26 som vi gör i Excel. Kolumnindexet är Arg3. För Arg3 använde vi 2, eftersom namnet finns i den andra kolumnen. Uppslagstypen är Arg4. Vi använde 0 som Arg4.

city ​​= Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 4, 0)

På samma sätt får vi stadsnamnet.

4.

MsgBox ("Namn:" & namn & vbLf & "Stad:" & stad)

Slutligen skriver vi ut namn och stad med Messagebox.

Varför använda kalkylbladsfunktion i VBA?

Arbetsbladets funktioner har enorma beräkningar och det är inte smart att ignorera kraften i kalkylbladets funktioner. Om vi ​​till exempel vill ha standardavvikelsen för en datamängd och du vill skriva hela koden för detta kan det ta dig timmar. Men om du vet hur du använder kalkylbladsfunktionen STDEV.P i VBA för att få beräkningen på en gång.

Sub GetStdDev () std = Application.WorksheetFunction.StDev_P (Range ("A1: K26")) End Sub 

Använda flera kalkylbladsfunktioner VBA

Låt oss säga att vi måste använda en indexmatchning för att hämta några värden. Nu hur skulle du göra formeln i VBA. Det här är vad jag antar att du kommer att skriva:

Sub IndMtch () Val = Application.WorksheetFunction.Index (result_range, _ Application.WorksheetFunction.Match (lookup_value, _ lookup_range, match_type)) End Sub 

Detta är inte fel men det är långt. Det rätta sättet att använda flera funktioner är att använda ett With -block. Se exemplet nedan:

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 

Som du kan se har jag använt With block för att berätta för VBA att jag kommer att använda egenskaperna och funktionerna för Application.WorksheetFunction. Så jag behöver inte definiera det överallt. Jag använde precis punktoperatören för att komma åt INDEX, MATCH, VLOOKUP och STDEV.P funktioner. När vi väl använder Sluta med uttalande kan vi inte komma åt funktioner utan att använda fullt kvalificerade funktionsnamn.

Så om du måste använda flera kalkylbladsfunktioner i VBA, använd med block.

Alla arbetsbladsfunktioner är inte tillgängliga via Application.WorksheetFunction

Vissa arbetsbladsfunktioner är direkt tillgängliga för användning i VBA. Du behöver inte använda Application.WorksheetFunction -objektet.

Till exempel funktioner som Len () som används för att få antalet tecken i en sträng, vänster, höger, mitt, trim, offset etc. Dessa funktioner kan användas direkt i VBA. Här är ett exempel.

Sub GetLen () Strng = "Hej" Debug.Print (Len (strng)) Avsluta sub 

Se, här använde vi LEN -funktionen utan att använda Application.WorksheetFunction -objektet.

På samma sätt kan du använda andra funktioner som vänster, höger, mitten, tecken etc.

Sub GetLen () Strng = "Hej" Debug.Print (Len (strng)) Debug.Print (vänster (strng, 2)) Debug.Print (höger (strng, 1)) Debug.Print (Mid (strng, 3, 2)) Slut Sub 

När du kör ovanstående del kommer den tillbaka:

5 Han kommer att 

Så ja grabbar, så här kan du använda kalkylbladsfunktionen i Excel i VBA. Jag hoppas att jag var tillräckligt förklarande och att den här artikeln hjälpte dig. Om du har några frågor angående denna artikel eller något annat relaterat VBA, ställ i kommentarsfältet nedan. Tills dess kan du läsa om andra relaterade ämnen nedan.

Vad är CSng -funktion i Excel VBA | SCng-funktionen är en VBA-funktion som konverterar vilken datatyp som helst till en enkel precision flytande punkt ("givet att det är ett tal"). Jag använder mest CSng -funktion för att konvertera textformaterade nummer till faktiska nummer.

Så här får du text och nummer i omvänt genom VBA i Microsoft Excel | För att vända tal och text använder vi loopar och mittfunktion i VBA. 1234 kommer att konverteras till 4321, "du" kommer att konverteras till "uoy". Här är utdraget.

Formatera data med anpassade nummerformat med VBA i Microsoft Excel | Om du vill ändra nummerformatet för specifika kolumner i excel använder du detta VBA -kodavsnitt. Det täcker nummerformatet för specificerat till specificerat format med ett klick.

Använda ändringshändelse för kalkylblad för att köra makro när någon ändring görs | Så för att köra ditt makro när arket uppdateras använder vi Arbetsbladshändelser i VBA.

Kör makro om någon ändring görs på blad i specificerat intervall | Använd den här VBA -koden för att köra din makrokod när värdet i ett visst område ändras. Den upptäcker alla ändringar som gjorts i det angivna intervallet och avfyrar händelsen.

Enklaste VBA -kod för att markera aktuell rad och kolumn med | Använd det här lilla VBA -utdraget för att markera den aktuella raden och kolumnen i arket.

Populära artiklar:

50 Excel -genvägar för att öka din produktivitet | Bli snabbare på din uppgift. Dessa 50 genvägar får dig att arbeta ännu snabbare med Excel.

VLOOKUP -funktionen i Excel | Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.

COUNTIF i Excel 2016 | Räkna värden med förhållanden med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifika värden. Countif -funktionen är avgörande för att förbereda din instrumentpanel.

Hur man använder SUMIF -funktionen i Excel | Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.