VBA - Guida rapida
VBA sta per Visual Basic for Applicazioni un linguaggio di programmazione basato sugli eventi di Microsoft che ora viene utilizzato prevalentemente con applicazioni per ufficio Microsoft come MSExcel, MS-Word e MS-Access.
Aiuta i tecnici a creare applicazioni e soluzioni personalizzate per migliorare le capacità di tali applicazioni. Il vantaggio di questa funzionalità è che NON È NECESSARIO avere visual basic installato sul nostro PC, tuttavia, l'installazione di Office aiuterà implicitamente a raggiungere lo scopo.
È possibile utilizzare VBA in tutte le versioni per ufficio, direttamente da MS-Office 97 a MS-Office 2013 e anche con qualsiasi delle ultime versioni disponibili. Tra VBA, Excel VBA è il più popolare. Il vantaggio dell'utilizzo di VBA è che puoi creare strumenti molto potenti in MS Excel utilizzando la programmazione lineare.
Applicazione di VBA
Potresti chiederti perché utilizzare VBA in Excel poiché MS-Excel stesso fornisce un sacco di funzioni integrate. MS-Excel fornisce solo funzioni integrate di base che potrebbero non essere sufficienti per eseguire calcoli complessi. In tali circostanze, VBA diventa la soluzione più ovvia.
Ad esempio, è molto difficile calcolare il rimborso mensile di un prestito utilizzando le formule integrate di Excel. Piuttosto, è facile programmare un VBA per tale calcolo.
Accesso all'editor VBA
Nella finestra di Excel, premere "ALT + F11". Si apre una finestra VBA come mostrato nello screenshot seguente.
In questo capitolo imparerai come scrivere una semplice macro in modo graduale.
Step 1- Innanzitutto, abilita il menu "Sviluppatore" in Excel 20XX. Per fare lo stesso, fare clic su File → Opzioni.
Step 2- Fare clic sulla scheda "Personalizza barra multifunzione" e selezionare "Sviluppatore". Fare clic su "OK".
Step 3 - Il nastro "Sviluppatore" viene visualizzato nella barra dei menu.
Step 4 - Fare clic sul pulsante "Visual Basic" per aprire l'editor VBA.
Step 5- Avvia lo scripting aggiungendo un pulsante. Fare clic su Inserisci → Seleziona il pulsante.
Step 6 - Fai clic con il pulsante destro del mouse e scegli "proprietà".
Step 7 - Modifica il nome e la didascalia come mostrato nella seguente schermata.
Step 8 - Ora fai doppio clic sul pulsante e lo schema della sottoprocedura verrà visualizzato come mostrato nella schermata seguente.
Step 9 - Inizia a scrivere codice semplicemente aggiungendo un messaggio.
Private Sub say_helloworld_Click()
MsgBox "Hi"
End SubStep 10- Fare clic sul pulsante per eseguire la sottoprocedura. L'output della sottoprocedura è mostrato nella seguente schermata. Assicurati di aver attivato la modalità di progettazione. Basta fare clic per accenderlo se non lo è.
Note - In ulteriori capitoli, dimostreremo l'utilizzo di un semplice pulsante, come spiegato dal passaggio # 1 al punto 10. Quindi, è importante comprendere a fondo questo capitolo.
In questo capitolo, acquisirai familiarità con le terminologie VBA excel comunemente utilizzate. Queste terminologie verranno utilizzate in ulteriori moduli, quindi la comprensione di ciascuna di esse è importante.
Moduli
I moduli è l'area in cui è scritto il codice. Questa è una nuova cartella di lavoro, quindi non ci sono moduli.
Per inserire un modulo, vai a Inserisci → Modulo. Una volta inserito un modulo, viene creato "modulo1".
All'interno dei moduli, possiamo scrivere codice VBA e il codice viene scritto all'interno di una procedura. Una procedura / procedura secondaria è una serie di istruzioni VBA che indicano cosa fare.
Procedura
Le procedure sono un gruppo di istruzioni eseguite nel loro insieme, che indica a Excel come eseguire un'attività specifica. L'attività svolta può essere un'attività molto semplice o molto complicata. Tuttavia, è buona norma suddividere procedure complicate in procedure più piccole.
I due tipi principali di procedure sono Sub e Function.
Funzione
Una funzione è un gruppo di codice riutilizzabile, che può essere chiamato ovunque nel programma. Ciò elimina la necessità di scrivere lo stesso codice più e più volte. Questo aiuta i programmatori a dividere un grande programma in un numero di funzioni piccole e gestibili.
Oltre alle funzioni integrate, VBA consente di scrivere anche funzioni definite dall'utente e le istruzioni vengono scritte tra Function e End Function.
Sottoprocedure
Le procedure secondarie funzionano in modo simile alle funzioni. Sebbene le procedure secondarie NON restituiscano un valore, le funzioni possono o non possono restituire un valore. Le procedure secondarie POSSONO essere chiamate senza parola chiave di chiamata. Le procedure secondarie sono sempre racchiuse all'internoSub e End Sub dichiarazioni.
I commenti vengono utilizzati per documentare la logica del programma e le informazioni utente con cui altri programmatori possono lavorare senza problemi sullo stesso codice in futuro.
Include informazioni sviluppate da, modificate da e può anche includere logica incorporata. I commenti vengono ignorati dall'interprete durante l'esecuzione.
I commenti in VBA sono indicati con due metodi.
Qualsiasi istruzione che inizia con una virgoletta singola (') viene considerata come commento. Di seguito è riportato un esempio.
' This Script is invoked after successful login
' Written by : TutorialsPoint
' Return Value : True / FalseQualsiasi istruzione che inizia con la parola chiave "REM". Di seguito è riportato un esempio.
REM This Script is written to Validate the Entered Input
REM Modified by : Tutorials point/user2Il MsgBox function visualizza una finestra di messaggio e attende che l'utente faccia clic su un pulsante, quindi viene eseguita un'azione in base al pulsante selezionato dall'utente.
Sintassi
MsgBox(prompt[,buttons][,title][,helpfile,context])Descrizione dei parametri
Prompt- Un parametro obbligatorio. Una stringa visualizzata come messaggio nella finestra di dialogo. La lunghezza massima del prompt è di circa 1024 caratteri. Se il messaggio si estende a più di una riga, è possibile separare le righe utilizzando un carattere di ritorno a capo (Chr (13)) o un carattere di avanzamento riga (Chr (10)) tra ciascuna riga.
Buttons- Un parametro facoltativo. Un'espressione numerica che specifica il tipo di pulsanti da visualizzare, lo stile dell'icona da utilizzare, l'identità del pulsante predefinito e la modalità della finestra di messaggio. Se lasciato vuoto, il valore predefinito per i pulsanti è 0.
Title- Un parametro facoltativo. Un'espressione String visualizzata nella barra del titolo della finestra di dialogo. Se il titolo viene lasciato vuoto, il nome dell'applicazione viene inserito nella barra del titolo.
Helpfile- Un parametro facoltativo. Un'espressione String che identifica il file della Guida da utilizzare per fornire la guida sensibile al contesto per la finestra di dialogo.
Context- Un parametro facoltativo. Un'espressione numerica che identifica il numero di contesto della Guida assegnato dall'autore della Guida all'argomento della Guida appropriato. Se viene fornito il contesto, è necessario fornire anche il file di aiuto.
Il Buttons il parametro può assumere uno dei seguenti valori:
0 vbOKOnly - Visualizza solo il pulsante OK.
1 vbOKCancel - Visualizza i pulsanti OK e Annulla.
2 vbAbortRetryIgnore - Visualizza i pulsanti Interrompi, Riprova e Ignora.
3 vbYesNoCancel - Visualizza i pulsanti Sì, No e Annulla.
4 vbYesNo - Visualizza i pulsanti Sì e No.
5 vbRetryCancel - Visualizza i pulsanti Riprova e Annulla.
16 vbCritical - Visualizza l'icona del messaggio critico.
32 vbQuestion - Visualizza l'icona di query di avviso.
48 vbExclamation - Visualizza l'icona del messaggio di avviso.
64 vbInformation - Visualizza l'icona del messaggio di informazioni.
0 vbDefaultButton1: il primo pulsante è l'impostazione predefinita.
256 vbDefaultButton2 - Il secondo pulsante è l'impostazione predefinita.
512 vbDefaultButton3 - Il terzo pulsante è l'impostazione predefinita.
768 vbDefaultButton4 - Il quarto pulsante è l'impostazione predefinita.
0 vbApplicationModal Applicazione modale: l'applicazione corrente non funzionerà fino a quando l'utente non risponde alla finestra di messaggio.
4096 vbSystemModal System modal - Tutte le applicazioni non funzioneranno fino a quando l'utente non risponde alla finestra di messaggio.
I valori di cui sopra sono logicamente suddivisi in quattro gruppi: Il first group(Da 0 a 5) indica i pulsanti da visualizzare nella finestra del messaggio. Ilsecond group (16, 32, 48, 64) descrive lo stile dell'icona da visualizzare, il third group (0, 256, 512, 768) indica quale pulsante deve essere il valore predefinito e il fourth group (0, 4096) determina la modalità della finestra di messaggio.
Valori restituiti
La funzione MsgBox può restituire uno dei seguenti valori che possono essere utilizzati per identificare il pulsante su cui l'utente ha fatto clic nella finestra del messaggio.
- 1 - vbOK - È stato fatto clic su OK
- 2 - vbCancel - È stato fatto clic su Annulla
- 3 - vbAbort - È stato fatto clic su Abort
- 4 - vbRetry - È stato fatto clic su Riprova
- 5 - vbIgnore - Ignora è stato selezionato
- 6 - vb Sì - È stato fatto clic su Sì
- 7 - vbNo - No è stato cliccato
Esempio
Function MessageBox_Demo()
'Message Box with just prompt message
MsgBox("Welcome")
'Message Box with title, yes no and cancel Butttons
int a = MsgBox("Do you like blue color?",3,"Choose options")
' Assume that you press No Button
msgbox ("The Value of a is " & a)
End FunctionProduzione
Step 1 - La funzione di cui sopra può essere eseguita facendo clic sul pulsante "Esegui" nella finestra VBA o chiamando la funzione dal foglio di lavoro di Excel come mostrato nella schermata seguente.
Step 2 - Viene visualizzata una finestra di messaggio semplice con un messaggio "Benvenuto" e un pulsante "OK"
Step 3 - Dopo aver fatto clic su OK, viene visualizzata un'altra finestra di dialogo con un messaggio insieme ai pulsanti "sì, no e annulla".
Step 4- Dopo aver fatto clic sul pulsante "No", il valore di tale pulsante (7) viene memorizzato come un numero intero e visualizzato come una finestra di messaggio per l'utente, come mostrato nella schermata seguente. Utilizzando questo valore, è possibile capire su quale pulsante l'utente ha cliccato.
Il InputBox functionrichiede agli utenti di inserire valori. Dopo aver inserito i valori, se l'utente fa clic sul pulsante OK o preme INVIO sulla tastiera, la funzione InputBox restituirà il testo nella casella di testo. Se l'utente fa clic sul pulsante Annulla, la funzione restituirà una stringa vuota ("").
Sintassi
InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile,context])Descrizione dei parametri
Prompt- Un parametro obbligatorio. Una stringa visualizzata come messaggio nella finestra di dialogo. La lunghezza massima del prompt è di circa 1024 caratteri. Se il messaggio si estende a più di una riga, è possibile separare le righe utilizzando un carattere di ritorno a capo (Chr (13)) o un carattere di avanzamento riga (Chr (10)) tra ciascuna riga.
Title- Un parametro opzionale. Un'espressione String visualizzata nella barra del titolo della finestra di dialogo. Se il titolo viene lasciato vuoto, il nome dell'applicazione viene inserito nella barra del titolo.
Default- Un parametro opzionale. Un testo predefinito nella casella di testo che l'utente desidera venga visualizzato.
XPos- Un parametro opzionale. La posizione diXl'asse rappresenta la distanza del prompt dal lato sinistro dello schermo in orizzontale. Se lasciato vuoto, la casella di immissione è centrata orizzontalmente.
YPos- Un parametro opzionale. La posizione diYl'asse rappresenta la distanza del prompt dal lato sinistro dello schermo verticalmente. Se lasciato vuoto, la casella di immissione è centrata verticalmente.
Helpfile- Un parametro opzionale. Un'espressione String che identifica il file della guida da utilizzare per fornire la Guida sensibile al contesto per la finestra di dialogo.
context- Un parametro opzionale. Un'espressione numerica che identifica il numero di contesto della Guida assegnato dall'autore della Guida all'argomento della Guida appropriato. Se viene fornito il contesto, è necessario fornire anche il file di aiuto.
Esempio
Calcoliamo l'area di un rettangolo ottenendo i valori dall'utente in fase di esecuzione con l'aiuto di due caselle di input (una per la lunghezza e una per la larghezza).
Function findArea()
Dim Length As Double
Dim Width As Double
Length = InputBox("Enter Length ", "Enter a Number")
Width = InputBox("Enter Width", "Enter a Number")
findArea = Length * Width
End FunctionProduzione
Step 1 - Per eseguire lo stesso, chiamare utilizzando il nome della funzione e premere Invio come mostrato nella schermata seguente.
Step 2- Al momento dell'esecuzione, viene visualizzata la prima casella di immissione (lunghezza). Immettere un valore nella casella di input.
Step 3 - Dopo aver immesso il primo valore, viene visualizzata la seconda casella di immissione (larghezza).
Step 4- Dopo aver inserito il secondo numero, fare clic sul pulsante OK. L'area viene visualizzata come mostrato nella seguente schermata.
Variableè una posizione di memoria con nome utilizzata per contenere un valore che può essere modificato durante l'esecuzione dello script. Di seguito sono riportate le regole di base per denominare una variabile.
Devi usare una lettera come primo carattere.
Non è possibile utilizzare uno spazio, un punto (.), Un punto esclamativo (!) O i caratteri @, &, $, # nel nome.
Il nome non può superare i 255 caratteri di lunghezza.
Non è possibile utilizzare parole chiave riservate di Visual Basic come nome di variabile.
Syntax
In VBA, è necessario dichiarare le variabili prima di utilizzarle.
Dim <<variable_name>> As <<variable_type>>Tipi di dati
Esistono molti tipi di dati VBA, che possono essere suddivisi in due categorie principali, ovvero tipi di dati numerici e non numerici.
Tipi di dati numerici
La tabella seguente mostra i tipi di dati numerici e l'intervallo di valori consentito.
| genere | Gamma di valori |
|---|---|
| Byte | Da 0 a 255 |
| Numero intero | Da -32.768 a 32.767 |
| Lungo | -2.147.483.648 a 2.147.483.648 |
| Single | Da -3.402823E + 38 a -1.401298E-45 per valori negativi Da 1.401298E-45 a 3.402823E + 38 per valori positivi. |
| Doppio | -1.79769313486232e + 308 a -4.94065645841247E-324 per valori negativi 4.94065645841247E da 324 a 1.79769313486232e + 308 per valori positivi. |
| Moneta | -922.337.203.685.477.5808 a 922.337.203.685.477.5807 |
| Decimale | +/- 79.228.162.514.264.337.593.543.950.335 se non viene utilizzato alcun decimale +/- 7,9228162514264337593543950335 (28 cifre decimali). |
Tipi di dati non numerici
La tabella seguente mostra i tipi di dati non numerici e l'intervallo di valori consentito.
| genere | Gamma di valori |
|---|---|
| Stringa (lunghezza fissa) | Da 1 a 65.400 caratteri |
| Stringa (lunghezza variabile) | Da 0 a 2 miliardi di caratteri |
| Data | Dal 1 gennaio 100 al 31 dicembre 9999 |
| Booleano | Vero o falso |
| Oggetto | Qualsiasi oggetto incorporato |
| Variante (numerica) | Qualsiasi valore grande come il doppio |
| Variante (testo) | Uguale alla stringa di lunghezza variabile |
Example
Creiamo un pulsante e chiamiamolo "Variables_demo" per dimostrare l'uso delle variabili.
Private Sub say_helloworld_Click()
Dim password As String
password = "Admin#1"
Dim num As Integer
num = 1234
Dim BirthDay As Date
BirthDay = DateValue("30 / 10 / 2020")
MsgBox "Passowrd is " & password & Chr(10) & "Value of num is " &
num & Chr(10) & "Value of Birthday is " & BirthDay
End SubOutput
All'esecuzione dello script, l'output sarà come mostrato nello screenshot seguente.
La costante è una posizione di memoria denominata utilizzata per contenere un valore che NON PU essere modificato durante l'esecuzione dello script. Se un utente tenta di modificare un valore Constant, l'esecuzione dello script finisce con un errore. Le costanti vengono dichiarate nello stesso modo in cui vengono dichiarate le variabili.
Di seguito sono riportate le regole per denominare una costante.
Devi usare una lettera come primo carattere.
Non è possibile utilizzare uno spazio, un punto (.), Un punto esclamativo (!) O i caratteri @, &, $, # nel nome.
Il nome non può superare i 255 caratteri di lunghezza.
Non è possibile utilizzare parole chiave riservate di Visual Basic come nome di variabile.
Sintassi
In VBA, dobbiamo assegnare un valore alle costanti dichiarate. Viene generato un errore, se proviamo a modificare il valore della costante.
Const <<constant_name>> As <<constant_type>> = <<constant_value>>Esempio
Creiamo un pulsante "Constant_demo" per dimostrare come lavorare con le costanti.
Private Sub Constant_demo_Click()
Const MyInteger As Integer = 42
Const myDate As Date = #2/2/2020#
Const myDay As String = "Sunday"
MsgBox "Integer is " & MyInteger & Chr(10) & "myDate is "
& myDate & Chr(10) & "myDay is " & myDay
End SubProduzione
All'esecuzione dello script, l'output verrà visualizzato come mostrato nello screenshot seguente.
Un Operator può essere definito utilizzando una semplice espressione - 4 + 5 è uguale a 9. Qui vengono chiamati 4 e 5 operands e + è chiamato operator. VBA supporta i seguenti tipi di operatori:
- Operatori aritmetici
- Operatori di confronto
- Operatori logici (o relazionali)
- Operatori di concatenazione
Gli operatori aritmatici
I seguenti operatori aritmetici sono supportati da VBA.
Supponiamo che la variabile A contenga 5 e la variabile B contenga 10, quindi -
Mostra esempi
| Operatore | Descrizione | Esempio |
|---|---|---|
| + | Aggiunge i due operandi | A + B darà 15 |
| - | Sottrae il secondo operando dal primo | A - B darà -5 |
| * | Moltiplica entrambi gli operandi | A * B darà 50 |
| / | Divide il numeratore per il denominatore | B / A darà 2 |
| % | Operatore modulo e il resto dopo una divisione intera | B% A darà 0 |
| ^ | Operatore di esponenziazione | B ^ A darà 100000 |
Gli operatori di confronto
Sono disponibili i seguenti operatori di confronto supportati da VBA.
Supponiamo che la variabile A contenga 10 e la variabile B ne contenga 20, quindi -
Mostra esempi
| Operatore | Descrizione | Esempio |
|---|---|---|
| = | Controlla se il valore dei due operandi è uguale o meno. Se sì, la condizione è vera. | (A = B) è False. |
| <> | Controlla se il valore dei due operandi è uguale o meno. Se i valori non sono uguali, la condizione è vera. | (A <> B) è vero. |
| > | Controlla se il valore dell'operando sinistro è maggiore del valore dell'operando destro. Se sì, la condizione è vera. | (A> B) è False. |
| < | Controlla se il valore dell'operando sinistro è inferiore al valore dell'operando destro. Se sì, la condizione è vera. | (A <B) è vero. |
| > = | Controlla se il valore dell'operando sinistro è maggiore o uguale al valore dell'operando destro. Se sì, la condizione è vera. | (A> = B) è False. |
| <= | Controlla se il valore dell'operando sinistro è minore o uguale al valore dell'operando destro. Se sì, la condizione è vera. | (A <= B) è vero. |
Gli operatori logici
I seguenti operatori logici sono supportati da VBA.
Supponiamo che la variabile A contenga 10 e la variabile B contenga 0, quindi -
Mostra esempi
| Operatore | Descrizione | Esempio |
|---|---|---|
| E | Chiamato operatore AND logico. Se entrambe le condizioni sono vere, allora l'espressione è vera. | a <> 0 AND b <> 0 è False. |
| O | Chiamato Operatore OR logico. Se una delle due condizioni è vera, la condizione è vera. | a <> 0 OPPURE b <> 0 è vero. |
| NON | Chiamato operatore NOT logico. Utilizzato per invertire lo stato logico del suo operando. Se una condizione è vera, l'operatore NOT logico la renderà falsa. | NOT (a <> 0 OR b <> 0) è falso. |
| XOR | Chiamata esclusione logica. È la combinazione di NOT e OR Operator. Se una, e solo una, delle espressioni restituisce True, il risultato è True. | (a <> 0 XOR b <> 0) è vero. |
Gli operatori di concatenazione
I seguenti operatori di concatenazione sono supportati da VBA.
Supponiamo che la variabile A contenga 5 e la variabile B contenga 10, quindi -
Mostra esempi
| Operatore | Descrizione | Esempio |
|---|---|---|
| + | Aggiunge due valori come variabile. I valori sono numerici | A + B darà 15 |
| & | Concatena due valori | A & B darà 510 |
Assumi la variabile A = "Microsoft" e la variabile B = "VBScript", quindi -
| Operatore | Descrizione | Esempio |
|---|---|---|
| + | Concatena due valori | A + B darà MicrosoftVBScript |
| & | Concatena due valori | A & B darà MicrosoftVBScript |
Note- Gli operatori di concatenazione possono essere utilizzati sia per i numeri che per le stringhe. L'output dipende dal contesto, se le variabili contengono un valore numerico o un valore stringa.
Il processo decisionale consente ai programmatori di controllare il flusso di esecuzione di uno script o di una delle sue sezioni. L'esecuzione è regolata da una o più istruzioni condizionali.
Di seguito è riportata la forma generale di una tipica struttura decisionale presente nella maggior parte dei linguaggi di programmazione.
VBA fornisce i seguenti tipi di dichiarazioni decisionali. Fare clic sui seguenti collegamenti per verificarne i dettagli.
| Sr.No. | Dichiarazione e descrizione |
|---|---|
| 1 | istruzione if Un if L'istruzione consiste in un'espressione booleana seguita da una o più istruzioni. |
| 2 | if..else istruzione Un if elseL'istruzione consiste in un'espressione booleana seguita da una o più istruzioni. Se la condizione è True, le istruzioni sottoIfvengono eseguite le istruzioni. Se la condizione è falsa, il fileElse parte dello script viene eseguita. |
| 3 | if ... elseif..else istruzione Un if dichiarazione seguita da una o più ElseIf istruzioni, che consiste in espressioni booleane e quindi seguita da un opzionale else statement, che viene eseguito quando tutte le condizioni diventano false. |
| 4 | istruzioni if annidate Un if o elseif dichiarazione dentro un'altra if o elseif dichiarazione (i). |
| 5 | istruzione switch UN switch consente di verificare l'uguaglianza di una variabile rispetto a un elenco di valori. |
Potrebbe esserci una situazione in cui è necessario eseguire un blocco di codice più volte. In generale, le istruzioni vengono eseguite in sequenza: la prima istruzione in una funzione viene eseguita per prima, seguita dalla seconda e così via.
I linguaggi di programmazione forniscono varie strutture di controllo che consentono percorsi di esecuzione più complicati.
Un'istruzione loop ci consente di eseguire un'istruzione o un gruppo di istruzioni più volte. Di seguito è riportata la forma generale di un'istruzione loop in VBA.
VBA fornisce i seguenti tipi di loop per gestire i requisiti di loop. Fare clic sui seguenti collegamenti per verificarne i dettagli.
| Sr.No. | Tipo e descrizione del loop |
|---|---|
| 1 | per loop Esegue una sequenza di istruzioni più volte e abbrevia il codice che gestisce la variabile del ciclo. |
| 2 | per ..ogni ciclo Viene eseguito se è presente almeno un elemento nel gruppo e ripetuto per ogni elemento in un gruppo. |
| 3 | while..wend loop Questo verifica la condizione prima di eseguire il corpo del ciclo. |
| 4 | do.. while loop Le istruzioni do..While verranno eseguite fintanto che la condizione è True. (Cioè,) Il ciclo dovrebbe essere ripetuto fino a quando la condizione è False. |
| 5 | fare..fino a loop Le istruzioni do..Until verranno eseguite fintanto che la condizione è False. (Cioè,) Il ciclo dovrebbe essere ripetuto fino a quando la condizione è True. |
Dichiarazioni di controllo del loop
Le istruzioni di controllo del ciclo cambiano l'esecuzione dalla sua sequenza normale. Quando l'esecuzione esce da un ambito, tutte le istruzioni rimanenti nel ciclo NON vengono eseguite.
VBA supporta le seguenti istruzioni di controllo. Fare clic sui seguenti collegamenti per verificarne i dettagli.
| S.No. | Dichiarazione di controllo e descrizione |
|---|---|
| 1 | Esci per istruzione Termina il For loop istruzione e trasferisce l'esecuzione all'istruzione immediatamente successiva al ciclo |
| 2 | Esci dall'istruzione Do Termina il Do While istruzione e trasferisce l'esecuzione all'istruzione immediatamente successiva al ciclo |
Le stringhe sono una sequenza di caratteri, che può essere composta da alfabeti, numeri, caratteri speciali o tutti. Si dice che una variabile sia una stringa se è racchiusa tra virgolette doppie "".
Sintassi
variablename = "string"Esempi
str1 = "string" ' Only Alphabets
str2 = "132.45" ' Only Numbers
str3 = "!@#$;*" ' Only Special Characters
Str4 = "Asc23@#" ' Has all the aboveFunzioni stringa
Esistono funzioni stringa VBA predefinite, che aiutano gli sviluppatori a lavorare con le stringhe in modo molto efficace. Di seguito sono riportati i metodi String supportati in VBA. Fare clic su ciascuno dei metodi per conoscere in dettaglio.
| Sr.No. | Nome e descrizione della funzione |
|---|---|
| 1 | InStr Restituisce la prima occorrenza della sottostringa specificata. La ricerca avviene da sinistra a destra. |
| 2 | InstrRev Restituisce la prima occorrenza della sottostringa specificata. La ricerca avviene da destra a sinistra. |
| 3 | Lcase Restituisce il minuscolo della stringa specificata. |
| 4 | Ucase Restituisce il maiuscolo della stringa specificata. |
| 5 | Sinistra Restituisce un numero specifico di caratteri dal lato sinistro della stringa. |
| 6 | Destra Restituisce un numero specifico di caratteri dal lato destro della stringa. |
| 7 | Mid Restituisce un numero specifico di caratteri da una stringa in base ai parametri specificati. |
| 8 | Ltrim Restituisce una stringa dopo aver rimosso gli spazi sul lato sinistro della stringa specificata. |
| 9 | Rtrim Restituisce una stringa dopo aver rimosso gli spazi sul lato destro della stringa specificata. |
| 10 | Trim Restituisce un valore stringa dopo aver rimosso gli spazi vuoti iniziali e finali. |
| 11 | Len Restituisce la lunghezza della stringa data. |
| 12 | Sostituire Restituisce una stringa dopo aver sostituito una stringa con un'altra stringa. |
| 13 | Spazio Riempie una stringa con il numero di spazi specificato. |
| 14 | StrComp Restituisce un valore intero dopo aver confrontato le due stringhe specificate. |
| 15 | Corda Restituisce una stringa con un carattere specificato per il numero di volte specificato. |
| 16 | StrReverse Restituisce una stringa dopo aver invertito la sequenza dei caratteri della stringa data. |
Le funzioni di data e ora di VBScript aiutano gli sviluppatori a convertire la data e l'ora da un formato a un altro o ad esprimere il valore della data o dell'ora nel formato che si adatta a una condizione specifica.
Funzioni di data
| Sr.No. | Descrizione della funzione |
|---|---|
| 1 | Data Una funzione, che restituisce la data di sistema corrente. |
| 2 | CDate Una funzione, che converte un dato input in data. |
| 3 | DateAdd Una funzione, che restituisce una data alla quale è stato aggiunto un intervallo di tempo specificato. |
| 4 | DateDiff Una funzione, che restituisce la differenza tra due periodi di tempo. |
| 5 | DatePart Una funzione, che restituisce una parte specificata del valore della data di input specificato. |
| 6 | DateSerial Una funzione, che restituisce una data valida per l'anno, il mese e la data specificati. |
| 7 | FormatDateTime Una funzione, che formatta la data in base ai parametri forniti. |
| 8 | IsDate Una funzione, che restituisce un valore booleano indipendentemente dal fatto che il parametro fornito sia o meno una data. |
| 9 | Giorno Una funzione, che restituisce un numero intero compreso tra 1 e 31 che rappresenta il giorno della data specificata. |
| 10 | Mese Una funzione, che restituisce un numero intero compreso tra 1 e 12 che rappresenta il mese della data specificata. |
| 11 | Anno Una funzione, che restituisce un numero intero che rappresenta l'anno della data specificata. |
| 12 | MonthName Una funzione, che restituisce il nome del mese specifico per la data specificata. |
| 13 | WeekDay Una funzione, che restituisce un numero intero (da 1 a 7) che rappresenta il giorno della settimana per il giorno specificato. |
| 14 | WeekDayName Una funzione, che restituisce il nome del giorno della settimana per il giorno specificato. |
Funzioni temporali
| Sr.No. | Descrizione della funzione |
|---|---|
| 1 | Adesso Una funzione, che restituisce la data e l'ora del sistema corrente. |
| 2 | Ora Una funzione, che restituisce un numero intero compreso tra 0 e 23 che rappresenta la parte oraria dell'ora specificata. |
| 3 | Minuto Una funzione, che restituisce un numero intero compreso tra 0 e 59 che rappresenta la parte dei minuti del tempo specificato. |
| 4 | Secondo Una funzione, che restituisce un numero intero compreso tra 0 e 59 che rappresenta la parte dei secondi del tempo specificato. |
| 5 | Tempo Una funzione, che restituisce l'ora di sistema corrente. |
| 6 | Timer Una funzione, che restituisce il numero di secondi e millisecondi a partire dalla mezzanotte. |
| 7 | TimeSerial Una funzione, che restituisce l'ora per l'immissione specifica di ora, minuti e secondi. |
| 8 | TimeValue Una funzione, che converte la stringa di input in un formato ora. |
Sappiamo molto bene che una variabile è un contenitore per memorizzare un valore. A volte, gli sviluppatori sono in grado di contenere più di un valore in una singola variabile alla volta. Quando una serie di valori viene archiviata in una singola variabile, è nota comearray variable.
Dichiarazione di matrice
Gli array vengono dichiarati nello stesso modo in cui è stata dichiarata una variabile, tranne per il fatto che la dichiarazione di una variabile array utilizza le parentesi. Nell'esempio seguente, la dimensione dell'array è menzionata tra parentesi.
'Method 1 : Using Dim
Dim arr1() 'Without Size
'Method 2 : Mentioning the Size
Dim arr2(5) 'Declared with size of 5
'Method 3 : using 'Array' Parameter
Dim arr3
arr3 = Array("apple","Orange","Grapes")Sebbene la dimensione dell'array sia indicata come 5, può contenere 6 valori poiché l'indice dell'array inizia da ZERO.
L'indice dell'array non può essere negativo.
Gli array VBScript possono memorizzare qualsiasi tipo di variabile in un array. Quindi, un array può memorizzare un numero intero, una stringa o caratteri in una singola variabile di array.
Assegnazione di valori a un array
I valori vengono assegnati all'array specificando un valore di indice dell'array rispetto a ciascuno dei valori da assegnare. Può essere una stringa.
Esempio
Aggiungi un pulsante e aggiungi la seguente funzione.
Private Sub Constant_demo_Click()
Dim arr(5)
arr(0) = "1" 'Number as String
arr(1) = "VBScript" 'String
arr(2) = 100 'Number
arr(3) = 2.45 'Decimal Number
arr(4) = #10/07/2013# 'Date
arr(5) = #12.45 PM# 'Time
msgbox("Value stored in Array index 0 : " & arr(0))
msgbox("Value stored in Array index 1 : " & arr(1))
msgbox("Value stored in Array index 2 : " & arr(2))
msgbox("Value stored in Array index 3 : " & arr(3))
msgbox("Value stored in Array index 4 : " & arr(4))
msgbox("Value stored in Array index 5 : " & arr(5))
End SubQuando si esegue la funzione di cui sopra, produce il seguente output.
Value stored in Array index 0 : 1
Value stored in Array index 1 : VBScript
Value stored in Array index 2 : 100
Value stored in Array index 3 : 2.45
Value stored in Array index 4 : 7/10/2013
Value stored in Array index 5 : 12:45:00 PMArray multidimensionali
Gli array non sono limitati solo a una singola dimensione, ma possono avere un massimo di 60 dimensioni. Gli array bidimensionali sono quelli più comunemente usati.
Esempio
Nell'esempio seguente, una matrice multidimensionale viene dichiarata con 3 righe e 4 colonne.
Private Sub Constant_demo_Click()
Dim arr(2,3) as Variant ' Which has 3 rows and 4 columns
arr(0,0) = "Apple"
arr(0,1) = "Orange"
arr(0,2) = "Grapes"
arr(0,3) = "pineapple"
arr(1,0) = "cucumber"
arr(1,1) = "beans"
arr(1,2) = "carrot"
arr(1,3) = "tomato"
arr(2,0) = "potato"
arr(2,1) = "sandwitch"
arr(2,2) = "coffee"
arr(2,3) = "nuts"
msgbox("Value in Array index 0,1 : " & arr(0,1))
msgbox("Value in Array index 2,2 : " & arr(2,2))
End SubQuando si esegue la funzione di cui sopra, produce il seguente output.
Value stored in Array index : 0 , 1 : Orange
Value stored in Array index : 2 , 2 : coffeeDichiarazione ReDim
L'istruzione ReDim viene utilizzata per dichiarare le variabili di matrice dinamica e allocare o riallocare lo spazio di archiviazione.
Sintassi
ReDim [Preserve] varname(subscripts) [, varname(subscripts)]Descrizione dei parametri
Preserve - Un parametro facoltativo utilizzato per preservare i dati in un array esistente quando si modifica la dimensione dell'ultima dimensione.
Varname - Un parametro obbligatorio, che denota il nome della variabile, che dovrebbe seguire le convenzioni di denominazione delle variabili standard.
Subscripts - Un parametro obbligatorio, che indica la dimensione dell'array.
Esempio
Nell'esempio seguente, un array è stato ridefinito e quindi i valori conservati quando la dimensione esistente dell'array viene modificata.
Note - Dopo aver ridimensionato un array più piccolo di quello che era originariamente, i dati negli elementi eliminati andranno persi.
Private Sub Constant_demo_Click()
Dim a() as variant
i = 0
redim a(5)
a(0) = "XYZ"
a(1) = 41.25
a(2) = 22
REDIM PRESERVE a(7)
For i = 3 to 7
a(i) = i
Next
'to Fetch the output
For i = 0 to ubound(a)
Msgbox a(i)
Next
End SubQuando si esegue la funzione di cui sopra, produce il seguente output.
XYZ
41.25
22
3
4
5
6
7Metodi di matrice
Ci sono varie funzioni integrate in VBScript che aiutano gli sviluppatori a gestire gli array in modo efficace. Di seguito sono elencati tutti i metodi utilizzati insieme agli array. Fare clic sul nome del metodo per conoscerlo in dettaglio.
| Sr.No. | Descrizione della funzione |
|---|---|
| 1 | LBound Una funzione, che restituisce un numero intero che corrisponde al più piccolo indice degli array dati. |
| 2 | UBound Una funzione, che restituisce un numero intero che corrisponde all'indice più grande degli array dati. |
| 3 | Diviso Una funzione, che restituisce una matrice che contiene un numero specificato di valori. Dividi in base a un delimitatore. |
| 4 | Aderire Una funzione, che restituisce una stringa che contiene un numero specificato di sottostringhe in una matrice. Questa è una funzione esattamente opposta del metodo Split. |
| 5 | Filtro Una funzione, che restituisce una matrice in base zero che contiene un sottoinsieme di una matrice di stringhe in base a criteri di filtro specifici. |
| 6 | IsArray Una funzione, che restituisce un valore booleano che indica se la variabile di input è o meno un array. |
| 7 | Cancellare Una funzione, che recupera la memoria allocata per le variabili dell'array. |
UN functionè un gruppo di codice riutilizzabile che può essere chiamato ovunque nel programma. Ciò elimina la necessità di scrivere lo stesso codice più e più volte. Ciò consente ai programmatori di dividere un grande programma in un numero di funzioni piccole e gestibili.
Oltre alle funzioni integrate, VBA consente di scrivere anche funzioni definite dall'utente. In questo capitolo imparerai come scrivere le tue funzioni in VBA.
Definizione di funzione
Una funzione VBA può avere un'istruzione return facoltativa. Ciò è necessario se si desidera restituire un valore da una funzione.
Ad esempio, puoi passare due numeri in una funzione e quindi puoi aspettarti che la funzione restituisca la loro moltiplicazione nel programma chiamante.
Note - Una funzione può restituire più valori separati da una virgola come un array assegnato al nome della funzione stessa.
Prima di utilizzare una funzione, dobbiamo definire quella particolare funzione. Il modo più comune per definire una funzione in VBA è utilizzare l'estensioneFunction parola chiave, seguita da un nome di funzione univoco e può contenere o meno un elenco di parametri e un'istruzione con End Functionparola chiave, che indica la fine della funzione. Di seguito è riportata la sintassi di base.
Sintassi
Aggiungi un pulsante e aggiungi la seguente funzione.
Function Functionname(parameter-list)
statement 1
statement 2
statement 3
.......
statement n
End FunctionEsempio
Aggiungi la seguente funzione che restituisce l'area. Notare che un valore / valori possono essere restituiti con il nome della funzione stessa.
Function findArea(Length As Double, Optional Width As Variant)
If IsMissing(Width) Then
findArea = Length * Length
Else
findArea = Length * Width
End If
End FunctionChiamare una funzione
Per richiamare una funzione, chiamare la funzione utilizzando il nome della funzione come mostrato nella schermata seguente.
L'output dell'area come mostrato di seguito verrà visualizzato all'utente.
Sub Procedures sono simili alle funzioni, tuttavia ci sono alcune differenze.
Le procedure secondarie NON restituiscono un valore mentre le funzioni possono o non possono restituire un valore.
Le procedure secondarie POSSONO essere chiamate senza una parola chiave di chiamata.
Le procedure Sub sono sempre racchiuse nelle istruzioni Sub e End Sub.
Esempio
Sub Area(x As Double, y As Double)
MsgBox x * y
End SubProcedure di chiamata
Per richiamare una procedura da qualche parte nello script, è possibile effettuare una chiamata da una funzione. Non saremo in grado di utilizzare lo stesso modo di una funzione in quanto la procedura secondaria NON restituirà un valore.
Function findArea(Length As Double, Width As Variant)
area Length, Width ' To Calculate Area 'area' sub proc is called
End FunctionOra sarai in grado di chiamare solo la funzione ma non la procedura secondaria come mostrato nello screenshot seguente.
L'area viene calcolata e visualizzata solo nella casella Messaggio.
La cella del risultato visualizza ZERO poiché il valore dell'area NON viene restituito dalla funzione. In breve, non è possibile effettuare una chiamata diretta a una procedura secondaria dal foglio di lavoro di Excel.
VBA, una programmazione guidata dagli eventi può essere attivata quando si modifica manualmente una cella o un intervallo di valori di cella. L'evento di modifica può semplificare le cose, ma puoi terminare molto rapidamente una pagina piena di formattazione. Esistono due tipi di eventi.
- Eventi del foglio di lavoro
- Eventi cartella di lavoro
Eventi del foglio di lavoro
Gli eventi del foglio di lavoro vengono attivati quando c'è una modifica nel foglio di lavoro. Viene creato facendo clic con il pulsante destro del mouse sulla scheda del foglio e scegliendo "Visualizza codice", quindi incollando il codice.
L'utente può selezionare ciascuno di questi fogli di lavoro e scegliere "Foglio di lavoro" dal menu a discesa per ottenere l'elenco di tutti gli eventi del foglio di lavoro supportati.
Di seguito sono riportati gli eventi del foglio di lavoro supportati che possono essere aggiunti dall'utente.
Private Sub Worksheet_Activate()
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Private Sub Worksheet_Calculate()
Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_Deactivate()
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)Esempio
Diciamo, dobbiamo solo visualizzare un messaggio prima del doppio clic.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
MsgBox "Before Double Click"
End SubProduzione
Facendo doppio clic su qualsiasi cella, la finestra di messaggio viene visualizzata all'utente come mostrato nello screenshot seguente.
Eventi cartella di lavoro
Gli eventi della cartella di lavoro vengono attivati quando c'è una modifica nella cartella di lavoro nel suo complesso. Possiamo aggiungere il codice per gli eventi della cartella di lavoro selezionando "ThisWorkbook" e selezionando "cartella di lavoro" dal menu a discesa, come mostrato nello screenshot seguente. Immediatamente la procedura secondaria Workbook_open viene visualizzata all'utente come mostrato nello screenshot seguente.
Di seguito sono riportati gli eventi della cartella di lavoro supportati che possono essere aggiunti dall'utente.
Private Sub Workbook_AddinUninstall()
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Private Sub Workbook_Deactivate()
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Private Sub Workbook_Open()
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Private Sub Workbook_WindowResize(ByVal Wn As Window)Esempio
Diciamo, dobbiamo solo visualizzare un messaggio all'utente che un nuovo foglio è stato creato con successo, ogni volta che viene creato un nuovo foglio.
Private Sub Workbook_NewSheet(ByVal Sh As Object)
MsgBox "New Sheet Created Successfully"
End SubProduzione
Dopo aver creato un nuovo foglio Excel, viene visualizzato un messaggio all'utente come mostrato nella seguente schermata.
Esistono tre tipi di errori nella programmazione: (a) errori di sintassi, (b) errori di runtime e (c) errori logici.
Errori di sintassi
Gli errori di sintassi, chiamati anche errori di analisi, si verificano al momento dell'interpretazione per VBScript. Ad esempio, la riga seguente causa un errore di sintassi perché manca una parentesi di chiusura.
Function ErrorHanlding_Demo()
dim x,y
x = "Tutorialspoint"
y = Ucase(x
End FunctionErrori di runtime
Gli errori di runtime, chiamati anche eccezioni, si verificano durante l'esecuzione, dopo l'interpretazione.
Ad esempio, la riga seguente causa un errore di runtime perché qui la sintassi è corretta ma in fase di runtime sta cercando di chiamare fnmultiply, che è una funzione inesistente.
Function ErrorHanlding_Demo1()
Dim x,y
x = 10
y = 20
z = fnadd(x,y)
a = fnmultiply(x,y)
End Function
Function fnadd(x,y)
fnadd = x + y
End FunctionErrori logici
Gli errori logici possono essere il tipo di errore più difficile da rintracciare. Questi errori non sono il risultato di un errore di sintassi o di runtime. Invece, si verificano quando commetti un errore nella logica che guida il tuo script e non ottieni il risultato che ti aspettavi.
Non è possibile rilevare questi errori, perché dipende dai requisiti aziendali che tipo di logica si desidera inserire nel programma.
Ad esempio, dividendo un numero per zero o uno script scritto che entra in un ciclo infinito.
Err Object
Supponiamo che se abbiamo un errore di runtime, l'esecuzione si interrompe visualizzando il messaggio di errore. In qualità di sviluppatore, se vogliamo acquisire l'errore, alloraError L'oggetto viene utilizzato.
Esempio
Nell'esempio seguente, Err.Number fornisce il numero di errore e Err.Description fornisce la descrizione dell'errore.
Err.Raise 6 ' Raise an overflow error.
MsgBox "Error # " & CStr(Err.Number) & " " & Err.Description
Err.Clear ' Clear the error.Gestione degli errori
VBA abilita una routine di gestione degli errori e può essere utilizzato anche per disabilitare una routine di gestione degli errori. Senza un'istruzione On Error, qualsiasi errore di runtime che si verifica è irreversibile: viene visualizzato un messaggio di errore e l'esecuzione si interrompe bruscamente.
On Error { GoTo [ line | 0 | -1 ] | Resume Next }| Sr.No. | Parola chiave e descrizione |
|---|---|
| 1 | GoTo line Abilita la routine di gestione degli errori che inizia dalla riga specificata nell'argomento di riga richiesto. La riga specificata deve essere nella stessa procedura dell'istruzione On Error, altrimenti si verificherà un errore in fase di compilazione. |
| 2 | GoTo 0 Disabilita il gestore degli errori abilitato nella procedura corrente e lo reimposta su Nothing. |
| 3 | GoTo -1 Disabilita l'eccezione abilitata nella procedura corrente e la reimposta su Nothing. |
| 4 | Resume Next Specifica che quando si verifica un errore di runtime, il controllo passa all'istruzione immediatamente successiva all'istruzione in cui si è verificato l'errore e l'esecuzione continua da quel punto. |
Esempio
Public Sub OnErrorDemo()
On Error GoTo ErrorHandler ' Enable error-handling routine.
Dim x, y, z As Integer
x = 50
y = 0
z = x / y ' Divide by ZERO Error Raises
ErrorHandler: ' Error-handling routine.
Select Case Err.Number ' Evaluate error number.
Case 10 ' Divide by zero error
MsgBox ("You attempted to divide by zero!")
Case Else
MsgBox "UNKNOWN ERROR - Error# " & Err.Number & " : " & Err.Description
End Select
Resume Next
End SubQuando si programma utilizzando VBA, ci sono pochi oggetti importanti con cui un utente avrebbe a che fare.
- Oggetti dell'applicazione
- Oggetti cartella di lavoro
- Oggetti del foglio di lavoro
- Oggetti intervallo
Oggetti dell'applicazione
L'oggetto Application è costituito da quanto segue:
- Impostazioni e opzioni a livello di applicazione.
- Metodi che restituiscono oggetti di primo livello, come ActiveCell, ActiveSheet e così via.
Esempio
'Example 1 :
Set xlapp = CreateObject("Excel.Sheet")
xlapp.Application.Workbooks.Open "C:\test.xls"
'Example 2 :
Application.Windows("test.xls").Activate
'Example 3:
Application.ActiveCell.Font.Bold = TrueOggetti cartella di lavoro
L'oggetto Cartella di lavoro è un membro della raccolta Cartelle di lavoro e contiene tutti gli oggetti Cartella di lavoro attualmente aperti in Microsoft Excel.
Esempio
'Ex 1 : To close Workbooks
Workbooks.Close
'Ex 2 : To Add an Empty Work Book
Workbooks.Add
'Ex 3: To Open a Workbook
Workbooks.Open FileName:="Test.xls", ReadOnly:=True
'Ex : 4 - To Activate WorkBooks
Workbooks("Test.xls").Worksheets("Sheet1").ActivateOggetti del foglio di lavoro
L'oggetto foglio di lavoro è un membro della raccolta fogli di lavoro e contiene tutti gli oggetti foglio di lavoro in una cartella di lavoro.
Esempio
'Ex 1 : To make it Invisible
Worksheets(1).Visible = False
'Ex 2 : To protect an WorkSheet
Worksheets("Sheet1").Protect password:=strPassword, scenarios:=TrueOggetti intervallo
Gli oggetti intervallo rappresentano una cella, una riga, una colonna o una selezione di celle contenenti uno o più blocchi continui di celle.
'Ex 1 : To Put a value in the cell A5
Worksheets("Sheet1").Range("A5").Value = "5235"
'Ex 2 : To put a value in range of Cells
Worksheets("Sheet1").Range("A1:A4").Value = 5Puoi anche leggere il file Excel e scrivere il contenuto della cella in un file di testo utilizzando VBA. VBA consente agli utenti di lavorare con file di testo utilizzando due metodi:
- Oggetto File System
- utilizzando il comando di scrittura
Oggetto file system (UST)
Come suggerisce il nome, gli UST aiutano gli sviluppatori a lavorare con unità, cartelle e file. In questa sezione discuteremo come utilizzare un UST.
| Sr.No. | Tipo di oggetto e descrizione |
|---|---|
| 1 | Drive Drive è un oggetto. Contiene metodi e proprietà che consentono di raccogliere informazioni su un'unità collegata al sistema. |
| 2 | Drives Drives è una collezione. Fornisce un elenco delle unità collegate al sistema, fisicamente o logicamente. |
| 3 | File Il file è un oggetto. Contiene metodi e proprietà che consentono agli sviluppatori di creare, eliminare o spostare un file. |
| 4 | Files File è una raccolta. Fornisce un elenco di tutti i file contenuti in una cartella. |
| 5 | Folder La cartella è un oggetto. Fornisce metodi e proprietà che consentono agli sviluppatori di creare, eliminare o spostare cartelle. |
| 6 | Folders Folders è una raccolta. Fornisce un elenco di tutte le cartelle all'interno di una cartella. |
| 7 | TextStream TextStream è un oggetto. Consente agli sviluppatori di leggere e scrivere file di testo. |
Guidare
Driveè un oggetto che fornisce l'accesso alle proprietà di una particolare unità disco o condivisione di rete. Le seguenti proprietà sono supportate daDrive oggetto -
- AvailableSpace
- DriveLetter
- DriveType
- FileSystem
- FreeSpace
- IsReady
- Path
- RootFolder
- SerialNumber
- ShareName
- TotalSize
- VolumeName
Esempio
Step 1- Prima di procedere allo scripting utilizzando FSO, dovremmo abilitare Microsoft Scripting Runtime. Per fare lo stesso, vai su Strumenti → Riferimenti come mostrato nello screenshot seguente.
Step 2 - Aggiungi "Microsoft Scripting RunTime" e fai clic su OK.
Step 3 - Aggiungi i dati che desideri scrivere in un file di testo e aggiungi un pulsante di comando.
Step 4 - Ora è il momento di Script.
Private Sub fn_write_to_text_Click()
Dim FilePath As String
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long
Dim fso As FileSystemObject
Set fso = New FileSystemObject
Dim stream As TextStream
LastCol = ActiveSheet.UsedRange.Columns.Count
LastRow = ActiveSheet.UsedRange.Rows.Count
' Create a TextStream.
Set stream = fso.OpenTextFile("D:\Try\Support.log", ForWriting, True)
CellData = ""
For i = 1 To LastRow
For j = 1 To LastCol
CellData = Trim(ActiveCell(i, j).Value)
stream.WriteLine "The Value at location (" & i & "," & j & ")" & CellData
Next j
Next i
stream.Close
MsgBox ("Job Done")
End SubProduzione
Quando si esegue lo script, assicurarsi di posizionare il cursore nella prima cella del foglio di lavoro. Il file Support.log viene creato come mostrato nella seguente schermata in "D: \ Try".
I contenuti del file sono mostrati nella seguente schermata.
Scrivi comando
A differenza di FSO, NON è necessario aggiungere alcun riferimento, tuttavia NON saremo in grado di lavorare con unità, file e cartelle. Saremo in grado di aggiungere semplicemente lo stream al file di testo.
Esempio
Private Sub fn_write_to_text_Click()
Dim FilePath As String
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long
LastCol = ActiveSheet.UsedRange.Columns.Count
LastRow = ActiveSheet.UsedRange.Rows.Count
FilePath = "D:\Try\write.txt"
Open FilePath For Output As #2
CellData = ""
For i = 1 To LastRow
For j = 1 To LastCol
CellData = "The Value at location (" & i & "," & j & ")" & Trim(ActiveCell(i, j).Value)
Write #2, CellData
Next j
Next i
Close #2
MsgBox ("Job Done")
End SubProduzione
All'esecuzione dello script, il file "write.txt" viene creato nella posizione "D: \ Try" come mostrato nella seguente schermata.
Il contenuto del file è mostrato nella seguente schermata.
Utilizzando VBA, puoi generare grafici in base a determinati criteri. Vediamolo usando un esempio.
Step 1 - Immettere i dati rispetto ai quali deve essere generato il grafico.
Step 2 - Crea 3 pulsanti: uno per generare un grafico a barre, un altro per generare un grafico a torta e un altro per generare un grafico a colonne.
Step 3 - Sviluppa una macro per generare ciascuno di questi tipi di grafici.
' Procedure to Generate Pie Chart
Private Sub fn_generate_pie_graph_Click()
Dim cht As ChartObject
For Each cht In Worksheets(1).ChartObjects
cht.Chart.Type = xlPie
Next cht
End Sub
' Procedure to Generate Bar Graph
Private Sub fn_Generate_Bar_Graph_Click()
Dim cht As ChartObject
For Each cht In Worksheets(1).ChartObjects
cht.Chart.Type = xlBar
Next cht
End Sub
' Procedure to Generate Column Graph
Private Sub fn_generate_column_graph_Click()
Dim cht As ChartObject
For Each cht In Worksheets(1).ChartObjects
cht.Chart.Type = xlColumn
Next cht
End SubStep 4- Facendo clic sul pulsante corrispondente, viene creato il grafico. Nell'output seguente, fare clic sul pulsante Genera grafico a torta.
UN User Formè una finestra di dialogo personalizzata che rende l'immissione di dati utente più controllabile e più facile da usare per l'utente. In questo capitolo imparerai a progettare un modulo semplice e ad aggiungere dati in Excel.
Step 1- Passa alla finestra VBA premendo Alt + F11 e vai al menu "Inserisci" e seleziona "Modulo utente". Dopo la selezione, il modulo utente viene visualizzato come mostrato nella seguente schermata.
Step 2 - Progettare i moduli utilizzando i controlli forniti.
Step 3- Dopo aver aggiunto ogni controllo, i controlli devono essere denominati. La didascalia corrisponde a ciò che appare nel modulo e il nome corrisponde al nome logico che apparirà quando scrivi il codice VBA per quell'elemento.
Step 4 - Di seguito sono riportati i nomi rispetto a ciascuno dei controlli aggiunti.
| Controllo | Nome logico | Didascalia |
|---|---|---|
| A partire dal | frmempform | Modulo dipendente |
| Etichetta di identificazione del dipendente | empido | ID Dipendente |
| Firstname Label Box | nome di battesimo | Nome di battesimo |
| Lastname Etichetta Box | cognome | Cognome |
| dob Etichetta Box | dob | Data di nascita |
| Mailid Etichetta Box | mailid | E-mail identificativo utente |
| Etichetta porta passaporto | Porta passaporto | Porta passaporto |
| Casella di testo ID Emp | txtempid | Non applicabile |
| Casella di testo del nome | txtfirstname | Non applicabile |
| Casella di testo del cognome | txtlastname | Non applicabile |
| Casella di testo ID e-mail | txtemailid | Non applicabile |
| Casella combinata data | cmbdate | Non applicabile |
| Casella combinata mese | cmbmonth | Non applicabile |
| Casella combinata anno | cmbyear | Non applicabile |
| Sì pulsante di opzione | radio sì | sì |
| Nessun pulsante di opzione | radiono | No |
| Pulsante Invia | btnsubmit | Invia |
| Pulsante Annulla | btncancel | Annulla |
Step 5 - Aggiungi il codice per l'evento di caricamento del modulo facendo clic con il pulsante destro del mouse sul modulo e selezionando "Visualizza codice".
Step 6 - Seleziona "Modulo utente" dal menu a discesa degli oggetti e seleziona il metodo "Inizializza" come mostrato nella seguente schermata.
Step 7 - Dopo aver caricato il modulo, assicurarsi che le caselle di testo siano deselezionate, le caselle a discesa siano riempite e i pulsanti di opzione siano ripristinati.
Private Sub UserForm_Initialize()
'Empty Emp ID Text box and Set the Cursor
txtempid.Value = ""
txtempid.SetFocus
'Empty all other text box fields
txtfirstname.Value = ""
txtlastname.Value = ""
txtemailid.Value = ""
'Clear All Date of Birth Related Fields
cmbdate.Clear
cmbmonth.Clear
cmbyear.Clear
'Fill Date Drop Down box - Takes 1 to 31
With cmbdate
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
.AddItem "7"
.AddItem "8"
.AddItem "9"
.AddItem "10"
.AddItem "11"
.AddItem "12"
.AddItem "13"
.AddItem "14"
.AddItem "15"
.AddItem "16"
.AddItem "17"
.AddItem "18"
.AddItem "19"
.AddItem "20"
.AddItem "21"
.AddItem "22"
.AddItem "23"
.AddItem "24"
.AddItem "25"
.AddItem "26"
.AddItem "27"
.AddItem "28"
.AddItem "29"
.AddItem "30"
.AddItem "31"
End With
'Fill Month Drop Down box - Takes Jan to Dec
With cmbmonth
.AddItem "JAN"
.AddItem "FEB"
.AddItem "MAR"
.AddItem "APR"
.AddItem "MAY"
.AddItem "JUN"
.AddItem "JUL"
.AddItem "AUG"
.AddItem "SEP"
.AddItem "OCT"
.AddItem "NOV"
.AddItem "DEC"
End With
'Fill Year Drop Down box - Takes 1980 to 2014
With cmbyear
.AddItem "1980"
.AddItem "1981"
.AddItem "1982"
.AddItem "1983"
.AddItem "1984"
.AddItem "1985"
.AddItem "1986"
.AddItem "1987"
.AddItem "1988"
.AddItem "1989"
.AddItem "1990"
.AddItem "1991"
.AddItem "1992"
.AddItem "1993"
.AddItem "1994"
.AddItem "1995"
.AddItem "1996"
.AddItem "1997"
.AddItem "1998"
.AddItem "1999"
.AddItem "2000"
.AddItem "2001"
.AddItem "2002"
.AddItem "2003"
.AddItem "2004"
.AddItem "2005"
.AddItem "2006"
.AddItem "2007"
.AddItem "2008"
.AddItem "2009"
.AddItem "2010"
.AddItem "2011"
.AddItem "2012"
.AddItem "2013"
.AddItem "2014"
End With
'Reset Radio Button. Set it to False when form loads.
radioyes.Value = False
radiono.Value = False
End SubStep 8- Ora aggiungi il codice al pulsante Invia. Facendo clic sul pulsante di invio, l'utente dovrebbe essere in grado di aggiungere i valori nel foglio di lavoro.
Private Sub btnsubmit_Click()
Dim emptyRow As Long
'Make Sheet1 active
Sheet1.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer information
Cells(emptyRow, 1).Value = txtempid.Value
Cells(emptyRow, 2).Value = txtfirstname.Value
Cells(emptyRow, 3).Value = txtlastname.Value
Cells(emptyRow, 4).Value = cmbdate.Value & "/" & cmbmonth.Value & "/" & cmbyear.Value
Cells(emptyRow, 5).Value = txtemailid.Value
If radioyes.Value = True Then
Cells(emptyRow, 6).Value = "Yes"
Else
Cells(emptyRow, 6).Value = "No"
End If
End SubStep 9 - Aggiungere un metodo per chiudere il modulo quando l'utente fa clic sul pulsante Annulla.
Private Sub btncancel_Click()
Unload Me
End SubStep 10- Eseguire il modulo facendo clic sul pulsante "Esegui". Immettere i valori nel modulo e fare clic sul pulsante "Invia". Automaticamente i valori fluiranno nel foglio di lavoro come mostrato nello screenshot seguente.
