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
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
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.