Avansert Excel VBA Tutorial

Microsoft Excel skriptspråk, Visual Basic for Applications, har mange verktøy for å utvide Excel standardfunksjoner. Et slikt verktøy er muligheten til å legge til kontroller (for eksempel avmerkingsboksene og knapper) direkte på regneark. Ved hjelp av Visual Basic kontroller og dens evne til å benytte seg Excel kjernefunksjoner gir en effektiv opplæring i å forbedre Excel gjennom Visual Basic for Applications.

Prosjekt: Bruk Visual Basic for å beregne delsummer

Gjennomføre et prosjekt for å beregne delsummer for kolonnene i et regneark bord. Begynn med å åpne Excel og lage et bord hvor som helst i regnearket. Tabellen i utgangspunktet ser ut som tabellen i Figur 1.

Marker tabellen, inkludert kolonneoverskriftene, og skape navnet "myTab" for denne tabellen. Lag navnet ved å skrive det i Excel navn Box, som er mot den øvre venstre hjørne av Excel arbeidsområdet.

Legg Visual Basic Controls

Legg avmerkingsboksene til bordet, med hver boksen sitter ovenfor en tabellkolonne. Du kan finne avmerkingsboksene henhold Utvikler> Sett> desse. Også legge til to knapper til regnearket. Dra og slipp dem fra samme område som de avmerkingsboksene, til venstre og høyre for myTab tabellen. Høyreklikk på venstre knapp og endre teksten for å lese "Do delsummer." Endre teksten i den høyre knappen "Fjern delsummer."

Plasser knapper og avmerkingsbokser slik tabellen ser ut tabellen i Figur 2.

Skriv koden

Nå som du har designet bordet og kontrollene, vil du skrive koden som gjør delsummene på bordet. Angi Visual Basic IDE (integrert utviklingsmiljø) og legge til en modul. Klikk på Sett inn> Module. I koden vinduet av den nye modulen, angi dette programmet kode:

Option Explicit

Public Sub doSubtotal ()

Dim s As String
Dim r As 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 delsummer
RemoveSubtotals

'Opprette rekke felt indekser (fra 1) for å delsum

Ifield = 1
nChkd = 0

'Loop gjennom avmerkingsboksene. Hvis man er krysset av, legge sitt felt for å rekke
For hver c I ActiveSheet.CheckBoxes

Hvis (c.Value = 1) Deretter
nChkd = nChkd + 1
Legg til dette feltet for å rekke

ar (UBound (ar)) = Ifield
ReDim Bevar ar (0 For å UBound (ar) + 1) 'legge element for neste valgte felt

Slutt om

Ifield = Ifield + 1

neste

Hvis (nChkd = 0) Then

MsgBox ("Please check at least one box.")
Exit Sub

Slutt om

ReDim Bevar ar (0 For å UBound (ar) - 1) "Fjern tom siste elementet
varItems = ar

"Gjør faktiske delsum. Først må du finne området å gjøre delsum på

Still r = Application.Names ( "myTab"). RefersToRange 'hente navngitte området til delsum

r.Subtotal GroupBy: = 1, Funksjon: = xlSum, TotalList: = varItems, SummaryBelowData: = xlSummaryBelow

End Sub

Public Sub RemoveSubtotals ()

"Se etter foregående delvis, ved hjelp av lagrings levert av kommentarfeltet i navnerom for myTab (se Formler> Navn manager)
'Vi lagrer tabellen opprinnelige første spalte. Hvis nåværende tabellens startkolonne forskjellig, må vi gjenopprette bordet til den opprinnelige plasseringen.

Dim r As Range
Dim s As String
Dim nOrigCol As Integer

Still r = Application.Names ( "myTab"). RefersToRange
s = Application.Names ( "myTab»). Comment

«Ingen kommentar betyr ingen tidligere løp, så ingen fjerning av foregående delvis, eller justering av originale serien, er nødvendig.

Men, redde tabellens start kolonnen for neste anrop til denne funksjonen.
Hvis (s = "") Så

Application.Names("myTab").Comment = r.Column
Exit Sub

Slutt om

Application.Range ( "a1: xfd65536") RemoveSubtotal.

'Justere Område: fjerne en kolonne hvis man ble lagt
nOrigCol = Cint (r)
Hvis (nOrigCol <r.Column) Deretter
r.Previous.EntireColumn.Delete

Slutt om

End Sub

Kjør Program

Gå tilbake til Excel-regneark som har myTab tabellen. Tildele knappene til makroene du nettopp skrev. Høyreklikk på "Do Subtotals" -knappen og velg "Tilordne makro". I dialogboksen som vises, velger du "doSubtotal" -funksjonen, som er en del av koden du nettopp skrev. Lukk dialogboksen ved å klikke på "OK", og tildele den andre makrofunksjonen du skrev, RemoveSubtotals, til den andre knappen.

Kjør programmet ved å sjekke hvilken som helst kombinasjon av avmerkingsboksene og klikke på knappen gjøre delsummer. Tømme delsummer ved å trykke på en annen knapp.