Avanceret Excel VBA tutorial

Microsoft Excels scriptsprog, Visual Basic for Applications, har mange værktøjer til at udvide Excels standard kapaciteter. Et sådant værktøj er evnen til at tilføje kontroller (såsom afkrydsningsfelter og knapper) direkte på regneark. Ved hjælp af Visual Basic kontroller og dens evne til at udnytte Excels kernefunktioner gør for en effektiv tutorial i at øge Excel via Visual Basic for Applications.

Projekt: Brug Visual Basic til at beregne Subtotaler

Foretage et projekt af computing subtotaler for de kolonner i et regneark tabel. Begynd med at åbne Excel og oprette en tabel overalt i regnearket. Tabellen oprindeligt ligner tabellen i Illustration 1. Vælg tabellen, herunder kolonneoverskrifterne, og skabe navnet "myTab" for denne tabel. Opret navnet ved at indtaste det i Excel navn Box, som er mod det øverste venstre hjørne af Excel arbejdsområdet.

Tilføj Visual Basic Controls

Føj afkrydsningsfelter til bordet, med hver afkrydsningsfeltet sidder over et bord kolonne. Du kan finde afkrydsningsfelterne under Developer> Indsæt> Form Controls. Også tilføje to knapper til regnearket. Træk og slip dem fra det samme område som afkrydsningsfelterne til venstre og højre for mit Tab bordet. Højreklik på den venstre knap og ændre sin tekst at læse "Do Subtotaler." Ændre teksten i den højre knap for at "Fjern Subtotaler." Placer knapper og afkrydsningsfelter, så dit bord ligner tabellen i Illustration 2.

Skriv koden

Nu hvor du har designet bordet og sin kontrol, vil du skrive koden, der gør Delsummerne på bordet. Indtast Visual Basic IDE (integreret udviklingsmiljø) og tilføje et modul. Klik på Indsæt> Module. I koden vinduet det nye modul, skal du indtaste dette program kode: Option Explicit Public Sub do Subtotal () Dim s As String Dim r Som Range Dim c As Object Dim ar () As Integer Dim Ifield As Integer Dim varItems Dim nChkd As Integer ReDim ar (0 Til 0) "fjerne tidligere subtotaler RemoveSubtotals" skabe den vifte af felter indeks (fra 1) til subtotal Ifield = 1 nChkd = 0 'Loop gennem afkrydsningsfelter. Hvis man er markeret, tilføje sit felt til matrix For Each c I ActiveSheet.CheckBoxes Hvis (c.Value = 1) Derefter nChkd = nChkd + 1 'tilføje dette felt til at række ar (UBound (AR)) = Ifield ReDim Bevar ar ( 0 Til UBound (AR) + 1) 'add element for næste valgte felt End If Ifield = Ifield + 1 Næste Hvis (nChkd = 0) Derefter MsgBox ("Tjek venligst mindst en kasse.") Exit Sub End If ReDim Bevar ar (0 Til UBound (AR) - 1) "Fjern tomme sidste element varItems = ar" Gør faktiske subtotal. . Først skal du finde det område at gøre subtotal på Set r = Application.Names ("myTab") RefersToRange 'hente det navngivne område at Subtotal r.Subtotal GroupBy: = 1, funktion: = xlSum, TotalList: = varItems, SummaryBelowData: = xlSummaryBelow End Sub Public Sub RemoveSubtotals () "Check for tidligere subtotal, ved hjælp af lagring tilvejebragt af kommentar-feltet i Namespace for min Tab (se Formler> Name manager) 'Vi gemmer bordet oprindelige, første kolonne. Hvis den aktuelle tabels udgangspunkt kolonne afviger, vi er nødt til at genskabe tabellen til den oprindelige placering. Dim R som Range Dim s As String Dim nOrigCol As Integer Set r = Application.Names ("myTab"). RefersToRange s = Application.Names ("myTab"). Kommentar »Ingen kommentarer betyder ingen forudgående løb, så ingen fjernelse af forudgående subtotal eller justering af den oprindelige interval, er nødvendig. "Men, redde tabellens start kolonnen for næste opkald til denne funktion. Hvis (s = "") Derefter Application.Names ("myTab") Kommentar = r.Column Exit Sub End If Application.Range. ("A1: xfd65536"). RemoveSubtotal 'Justering af rækkevidde: fjerne en kolonne, hvis man blev tilføjet nOrigCol = Cint (s) Hvis (nOrigCol <r.Column) Derefter r.Previous.EntireColumn.Delete End If End Sub

Kør programmet

Retur til Excel-regneark, der har min Tab bordet. Tildel knapperne til de makroer, du lige skrev. Højreklik på "Do Subtotaler" knappen og vælg "Assign Macro". I den dialogboks, der vises, skal du vælge "doSubtotal" -funktionen, som er en del af den kode, du lige har skrevet. Luk dialogboksen ved at klikke på "OK", og tildele den anden makro funktion, du skrev, RemoveSubtotals, at den anden knap. Kør dit program ved at kontrollere enhver kombination af afkrydsningsfelterne og klikke på knappen gøre subtotaler. Ryd subtotaler ved at trykke på den anden knap.