Nwlapcug.com


Tutorial VBA di Excel avanzato

Tutorial VBA di Excel avanzato


Linguaggio di scripting di Microsoft Excel, Visual Basic for Applications, ha molti strumenti per estendere le funzionalità di Excel predefinito. Uno di questi strumenti è la possibilità di aggiungere controlli (ad esempio caselle di controllo e pulsanti) direttamente su fogli di calcolo. Utilizzando i controlli di Visual Basic e la sua capacità di attingere a funzioni di base di Excel rende per un'esercitazione efficace nel miglioramento di Excel tramite Visual Basic, Applications Edition.

Progetto: Utilizzare Visual Basic per calcolare i subtotali

Tutorial VBA di Excel avanzato


Intraprendere un progetto di calcolo di subtotali per le colonne in una tabella di foglio di lavoro. Iniziare aprendo Excel e creazione di una tabella in un punto qualsiasi del foglio di lavoro. La tabella appare inizialmente come la tabella nella figura 1.

Selezionare la tabella, incluse le intestazioni di colonna e creare il nome "myTab" per questa tabella. Creare il nome immettendolo nella casella nome di Excel, che è verso l'angolo superiore sinistro dell'area di lavoro di Excel.

Aggiungere controlli di Visual Basic

Tutorial VBA di Excel avanzato


Aggiungere caselle di controllo nella tabella, con ogni casella di controllo che si siede sopra una colonna della tabella. È possibile trovare le caselle di controllo sotto sviluppatore > Inserisci > modulo controlli. Inoltre, è possibile aggiungere due pulsanti al foglio di lavoro. Trascinare e rilasciare loro dalla stessa zona come le caselle di controllo, a sinistra e a destra della tabella myTab. Fare clic destro il pulsante sinistro e modificare il testo per leggere "Do subtotali." Modificare il testo del pulsante destro a "Rimuovere i subtotali."

Posizionare i pulsanti e le caselle di controllo in modo che la tabella appare come la tabella in figura 2.

Scrivere il codice

Ora che aver progettato la tabella e i relativi controlli, si scriverà il codice che fa i subtotali sul tavolo. Immettere l'IDE di Visual Basic (ambiente di sviluppo integrato) e aggiungere un modulo. Fare clic su Inserisci > modulo. Nella finestra del codice del nuovo modulo, immettere questo codice di programma:

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 a 0)

' rimuovere i subtotali preventivo
RemoveSubtotals

' creare la matrice di indici di campi (a partire da 1) per calcolare il subtotale

iField = 1
nChkd = 0

' Loop through le caselle di controllo. Se uno è selezionato, aggiungere il campo a matrice
C In ActiveSheet.CheckBoxes

Se (c.Value = 1) quindi
nChkd = nChkd + 1
' aggiungere questo campo alla matrice

AR(UBound(AR)) = iField
ReDim Preserve ar (da 0 a UBound(ar)) + 1 ' Aggiungi elemento per prossimo campo selezionato

End If

iField = iField + 1

Successivo

Se (nChkd = 0) quindi

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

End If

ReDim Preserve ar (0 To UBound(ar) - 1) ' Remove vuoto ultimo elemento
varItems = ar

' Fare effettivo subtotale. In primo luogo, trovare la gamma fare subtotale su

Impostare r = Application.Names("myTab"). RefersToRange ' recuperare l'intervallo denominato per calcolare il subtotale

r.Subtotal GroupBy: = 1, funzione: = xlSum, as: = varItems, XlSummaryRow: = xlSummaryBelow

End Sub

Public Sub RemoveSubtotals()

' Verifica preventiva subtotale, utilizzo dell'archiviazione fornita dal campo commento nel Namespace per myTab (vedere formule > Nome manager)
' Memorizziamo originale della tabella, prima colonna. Se la colonna iniziale della tabella corrente differisce, dobbiamo ripristinare la tabella nella posizione originale.

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

Impostare r = Application.Names("myTab"). RefersToRange
s = Application.Names("myTab"). Commento

' Nessun commento significa nessun funzionamento precedente, quindi senza rimozione del subtotale preventiva, o la regolazione di gamma originale, è necessario.

' Ma, salvare il colonna iniziale della tabella per la successiva chiamata a questa funzione.
Se (s = "") quindi

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

End If

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

' Regolare intervallo: rimuovere una colonna, se uno è stato aggiunto
nOrigCol = CInt(s)
Se (nOrigCol < r.Column) quindi
r.Previous.EntireColumn.Delete

End If

End Sub

Eseguire il programma

Tornare al foglio di lavoro Excel contenente la tabella myTab. Assegnare i pulsanti per le macro che hai appena scritto. Fare clic destro sul pulsante "Do subtotali" e selezionare "Assegna Macro". Nella finestra di dialogo che appare, selezionare la funzione "doSubtotal", che è parte del codice che hai appena scritto. Chiudere la finestra di dialogo facendo clic su "OK" e assegnare l'altra funzione macro che hai scritto, RemoveSubtotals, per l'altro pulsante.

Eseguire il programma selezionando qualsiasi combinazione delle caselle di controllo e facendo clic sul pulsante subtotali. Deselezionare i subtotali premendo il pulsante altro.