Förstå Excel-celler vs Range-funktioner i VBA

Excel är kraftfullt. Om du använder det mycket, vet du förmodligen många knep med formler eller autoformatering, men använder dig av Celler och Räckvidd funktioner i VBA, kan du öka din Excel-analys till en helt ny nivå.

Problemet med att använda Cells and Range-funktionerna i VBA är att på avancerade nivåer har de flesta svårt att förstå hur dessa funktioner faktiskt fungerar. Att använda dem kan bli mycket förvirrande. Så här kan du använda dem på sätt som du förmodligen aldrig föreställt dig.

Cellfunktionen

Funktionerna Cells and Range kan du berätta för ditt VBA-skript 4 Fel du kan undvika när du programmerar Excel Makroer med VBA 4-fel som du kan undvika när du programmerar Excel Makroer med VBA Enkel kod och makron är nycklarna till Microsoft Excel-supermakter. Även icke-programmörer kan enkelt lägga imponerande funktionalitet till sina kalkylblad med virtuella grunder för applikationer (VBA). Undvik bara dessa programmering nybörjare misstag! Läs mer exakt var på ditt arbetsblad du vill skaffa eller placera data. Huvudskillnaden mellan de två cellerna är vad de refererar till.

Celler brukar referera en enda cell åt gången, medan Räckvidd refererar en grupp av celler på en gång. Formatet för den här funktionen är Celler (rad, kolumn).

Detta refererar till varje cell i hela arket. Det är det enda exemplet där cellfunktionen inte refererar till en enda cell:

Kalkylblad ( "Blad1"). Celler

Detta hänvisar till den tredje cellen från vänster, i den övre raden. Cell C1:

Kalkylblad ( "Blad1"). Celler (3)

Följande kodreferenser cell D15:

Arbetsblad ( "Blad1"). Cells (15,4)

Om du ville, kan du också referera till cell D15 med “Celler (15,”D”)”-du får använda kolumnbokstaven.

Det finns mycket flexibilitet att kunna referera till en cell med ett nummer för kolumn och cell, speciellt med skript som kan loop genom ett stort antal celler (och utföra beräkningar på dem) mycket snabbt. Vi kommer att få reda på det mer detaljerat nedan.

Range-funktionen

På många sätt är Range-funktionen mycket kraftfullare än att använda celler, eftersom den låter dig referera till antingen en enda cell eller ett specifikt antal celler, samtidigt. Du kommer inte att vilja slinga genom en Range-funktion eftersom referenserna för celler inte är siffror (om du inte bäddar in cellfunktionen inuti den).

Formatet för den här funktionen är Range (Cell # 1, Cell # 2). Varje cell kan betecknas med ett bokstavsnummer.

Låt oss titta på några exempel.

Här refererar intervallfunktionen cell A5:

Arbetsblad ( "Blad1"). Range ( "A5")

Här refererar intervallfunktionen alla celler mellan A1 till E20:

Arbetsblad ( "Blad1") Range. ( "A1: E20")

Som nämnts ovan behöver du inte använda cellblockuppdrag. Du kan faktiskt använda två cellfunktioner inuti en Range-funktion för att identifiera ett intervall på arket, så här:

 Med kalkylblad ("Sheet1") .Range (.Cells (1, 1), _ .Cells (20, 5)) Sluta med 

Koden ovan refererar till samma intervall som Sortimentet (“A1: E20”) funktionen gör. Värdet i att använda det är att det skulle låta dig skriva kod som dynamiskt arbetar med intervall med hjälp av slingor. Hur fungerar-medan slingor arbetar med dataprogrammering Hur gör-medan slingor arbetar i datorprogrammering Slingor är en av de första kontrolltyperna du " ll lära sig i programmering. Du vet förmodligen om stund och för slingor, men vad åstadkommer en stund-slinga? Läs mer .

Nu när du förstår hur du formaterar funktionerna Cells and Range, låt oss dyka in hur du kan göra kreativ användning av dessa funktioner i din VBA-kod.

Bearbetning av data med cellfunktion

Funktionen Cells är mest användbar när du har en komplex formel som du vill utföra över flera cellintervall. Dessa intervall kan också existera över flera ark.

Låt oss ta ett enkelt exempel. Låt oss säga att du hanterar ett säljteam på 11 personer, och varje månad vill du titta på deras prestanda.

Du kan ha Blad1 som spårar deras försäljningsantal och deras försäljningsvolym.

På Sheet2 är där du spårar deras feedbackbedömning under de senaste 30 dagarna från företagets kunder.

Om du vill beräkna bonusen på det första arket med värden från de två arken finns det flera sätt att göra detta. Du kan skriva en formel i den första cellen som utför beräkningen med data över de två arken och dra den ner. Det kommer att fungera.

Ett alternativ till detta är att skapa VBA-skript som du antingen utlöser för att köra när du öppnar arket eller utlöses av en kommandoknapp på arket så att du kan styra när det beräknas. Du kan använda ett VBA-skript för att dra in alla försäljningsdata från en extern fil ändå.

Så varför inte bara utlösa beräkningarna för bonuskolumnen i samma skript på den tiden?

Cellens funktion i åtgärd

Om du aldrig har skrivit VBA i Excel innan måste du aktivera menyalternativet Utvecklare. För att göra detta, gå till Fil > alternativ. Klicka på Skräddarsy bandet. Slutligen väljer du Utvecklare från den vänstra rutan, Lägg till det till den högra rutan och kontrollera att kryssrutan är vald.

Nu när du klickar ok och gå tillbaka till huvudbladet så får du se menyn Alternativ för utvecklare.

Du kan använda Föra in menyn för att infoga en kommandoknapp, eller klicka bara på Visa kod att börja kodning.

I det här exemplet gör vi att man kör skriptet varje gång arbetsboken öppnas. För att göra detta, klicka bara på Visa kod från utvecklarmenyn och klistra in följande nya funktion i kodfönstret.

Privat Sub Workbook_Open () End Sub

Ditt kodfönster kommer att se ut så här.

Nu är du redo att skriva koden för att hantera beräkningen. Med en enda loop kan du gå igenom alla 11 anställda, och med Cells-funktionen drar du in de tre variablerna som behövs för beräkningen.

Kom ihåg att cellfunktionen har rad och kolumn som parametrar för att identifiera varje enskild cell. Vi ska göra “x” raden, använd ett nummer för att begära varje kolonns data. Antalet rader är antalet anställda, så detta kommer att vara från 1 till 11. Kolonnidentifieraren kommer att vara 2 för försäljningsräkning, 3 för försäljningsvolymen och 2 från ark 2 för återkopplingsresultat.

Den slutliga beräkningen använder följande procentandelar för att lägga till upp till 100 procent av det totala bonuspoänget. Det baseras på ett idealt försäljningsantal som är 50, försäljningsvolymen är 50 000 dollar och ett återkopplingsresultat på 10.

• (Försäljningsräkning / 50) x 0,4
• (Försäljningsvolym / 50 000) x 0,5
• (Feedback Poäng / 10) x 0,1

Denna enkla metod ger säljare en viktad bonus. För ett tal på 50, en volym på $ 50.000 och en poäng på 10-får de hela maximala bonusen för månaden. Men vad som helst under perfekt på vilken som helst faktor minskar bonusen. Något bättre än perfekt ökar bonusen.

Låt oss nu se hur all den logiken kan dras av i ett mycket enkelt, kortt VBA-skript:

Private Sub Workbook_Open () För x = 2 till 12 arbetsblad ("Sheet1"). Celler (x, 4) = (Arbetsblad ("Sheet1"). Celler (x, 2) .Value / 50) * 0.4 _ + ("Sheet1"). Celler (x, 3) .Value / 50000) * 0,5 _ + (Arbetsblad ("Sheet2"). Celler (x, 2) .Value / 10) * 0,1 _ Nästa x Slutdel 

Detta är vad produktionen av det här skriptet kommer att se ut.

Om du vill ha bonuskolonnen visar den faktiska dollarn bonusen istället för procentdelen, kan du multiplicera den med det maximala bonusbeloppet. Bättre än det, placera det beloppet i en cell på ett annat ark och hänvisa det till i din kod. Detta skulle göra det enklare att ändra värdet senare utan att behöva redigera din kod.

Skönheten i Cells-funktionen är att du kan bygga en ganska kreativ logik för att dra in data från många celler över många olika ark, och utför några ganska komplexa beräkningar med dem.

Du kan utföra alla typer av åtgärder på celler med hjälp av Cells-funktionen - saker som att rensa cellerna, ändra fontformatering och mycket mer.

För att utforska allt du kan göra längre, kolla in Microsoft MSDN-sidan för Cells-objektet.

Formatera celler med omfångsfunktion

För att slinga igenom många celler en åt gången är cellfunktionen perfekt. Men om du vill tillämpa något på ett helt sortiment av celler samtidigt är Range-funktionen mycket effektivare.

Ett användningsfall för detta kan vara att formatera ett antal celler med script, om vissa villkor är uppfyllda.

Låt oss till exempel säga om volymen av all försäljningsvolym över alla försäljningsanställda överstiger $ 400.000 totalt, du vill markera alla celler i bonuskolonnen i grön för att indikera att laget har tjänat en extra lagbonus.

Låt oss ta en titt på hur du kan göra det med ett IF-uttalande Hur man använder IF-uttalanden i Microsoft Excel Hur man använder IF-uttalanden i Microsoft Excel Oavsett om du är en erfaren expert eller ett kalkylblad börjar du kolla här guide till IF-uttalanden i Excel. Läs mer .

Private Sub Workbook_Open () Om kalkylblad ("Sheet1"). Celler (13, 3) .Value> 400000 Då ActiveSheet.Range ("D2: D12"). Interior.ColorIndex = 4 Avsluta om End Sub

När detta körs, om cellen ligger över lagsmålet fylls alla celler i intervallet grönt.

Detta är bara ett enkelt exempel på de många åtgärderna du kan utföra på grupper av celler med hjälp av Range-funktionen. Andra saker du kan göra är att:

• Applicera en översikt runt gruppen
• Kontrollera stavningen av text inom ett antal celler
• Rensa, kopiera eller skära celler
• Sök igenom ett intervall med “Hitta” metod
• Mycket mer

Se till att du läser Microsoft MSDN-sidan för Range-objektet för att se alla möjligheter.

Ta Excel till nästa nivå

Nu när du förstår skillnaderna mellan Celler och Range-funktionerna är det dags att ta din VBA-skript till nästa nivå. Danns artikel om att använda räkna och lägga till funktioner i Excel gör det möjligt att bygga ännu mer avancerade skript som kan ackumulera värden i alla dina datasatser mycket snabbt.

Och om du bara har börjat med VBA i Excel, glöm inte att vi har en fantastisk inledande guide till Excel VBA. Programmet för Excel VBA Programmering för nybörjare Excel VBA Programmering Handledning för nybörjare VBA är ett Microsoft Office-verktyg. Du kan använda den för att automatisera uppgifter med makron, inställningsfunktioner och mycket mer. Vi introducerar dig till Excel visuell grundläggande programmering med ett enkelt projekt. Läs mer för dig också.

.