Pochi sanno che la prima versione del popolare prodotto Microsoft Excel è apparso nel 1985. Da allora ha subito diverse modifiche e la domanda di milioni di utenti in tutto il mondo. Tuttavia, solo molti lavorano con un po 'di capacità di bit di questo foglio di calcolo e non so nemmeno come si potrebbe rendere la vita più facile per la capacità di programmazione di Excel.
Che cosa è VBA
Programmazione in Excel è fatto da Basic for Application linguaggio di programmazione Visual che è stato originariamente costruito nel più famoso foglio di calcolo da Microsoft.
Per suo merito gli esperti attribuiscono la relativa facilità di sviluppo. Come dimostra la pratica, VBA può padroneggiare le basi, anche gli utenti che non hanno competenze di programmazione professionali. Per VBA caratteristiche includono l'esecuzione dello script in ambiente Office.
Lo svantaggio del programma sono i problemi connessi con la compatibilità di diverse versioni. Essi sono causati dal fatto che il codice VBA del programma fa riferimento alla funzionalità che è presente nella nuova versione del prodotto, ma non in quello vecchio. V'è anche un grande svantaggio ed eccessivamente elevata apertura di codice per cambiare il volto di uno sconosciuto. Tuttavia, Microsoft Office e IBM Lotus Symphony permette agli utenti di impostazioni di crittografia codice di ingresso e la password per vederlo.
Oggetti, collezioni, proprietà e metodi
E 'con questi concetti è necessario comprendere coloro che stanno andando a lavorare in un ambiente VBA. Prima di tutto, è necessario capire ciò che l'oggetto è. In Excel, in questo atto come un foglio, un libro, e di celle. Questi oggetti hanno gerarchia speciale, vale a dire obbedire vicenda.
Primo fra tutti è l'applicazione, il programma Excel corrispondente stesso. Le cartelle di lavoro seguita, fogli di lavoro, e la gamma. Ad esempio, per fare riferimento alla cella A1 di un foglio specifico deve indicare la strada, tenendo conto della gerarchia.
Per quanto riguarda il concetto di "raccolta", questo gruppo di oggetti della stessa classe, in cui la registrazione è dato ChartObjects. I suoi elementi sono anche oggetti.
La prossima cosa – proprietà. Si tratta di una caratteristica necessaria di qualsiasi oggetto. Ad esempio, per la Range – è un valore o una formula.
Metodi – è il comando per dimostrare che si vuole fare. Quando si scrive codice in VBA loro di essere separati dal punto dell'oggetto. Ad esempio, come si vedrà in seguito, molto spesso durante la programmazione nel comando "Excel" uso Cells (1,1) .Select. Ciò significa che è necessario selezionare una cella con coordinate (1,1), cioè A1.
Tuttavia, è spesso usato Selection.ClearContents. La sua attuazione è di compensazione il contenuto della cella selezionata.
Come iniziare
Prima di tutto, si desidera creare il file e salvarlo, assegnare un nome e selezionare il tipo di «Libro Excel con attivazione macro."
Poi, vai all'applicazione VB, che è sufficiente per utilizzare una combinazione di «Alt» chiave e «F11». Avanti:
- nella barra dei menu nella parte superiore della finestra, fare clic sull'icona accanto all'icona di Excel;
- Mudule comando selezionato;
- tenere cliccando sull'icona con il dischetto;
- scrivere, diciamo, un contorno di codice.
Esso si presenta come segue:
sottoprogramma ()
'Il nostro codice
End Sub
Si prega di notare che la linea " 'Il nostro codice" verrà evidenziato con un colore diverso (verde). La ragione di questa apostrofe, consegnato all'inizio della stringa, il che indica che ciò che segue è un commento.
Ora è possibile scrivere codice e creare per se stessi un nuovo strumento in VBA di Excel (vedere i programmi di esempio. Ecc). Naturalmente, coloro che hanno familiarità con i principi fondamentali di Visual Basic, sarà molto più facile. Tuttavia, anche coloro che non hanno, se si vuole essere in grado di mettersi a proprio agio abbastanza rapidamente.
Le macro in Excel
Dietro a questo nome si nasconde i programmi scritti in Visual Basic for Application lingua. Così, la programmazione in Excel – è quello di creare una macro per il codice desiderato. Grazie a questa funzionalità, Microsoft foglio di sé si sviluppa, adattandosi alle esigenze di un particolare utente. Dopo aver affrontato come creare moduli per la scrittura di macro, è possibile procedere con esempi concreti di programma VBA di Excel. E 'meglio cominciare con i codici più elementari.
ESEMPIO 1
Compito: Scrivere un programma che copierà il valore del contenuto di una cella e poi scrivere un altro.
Per fare questo:
- aprire la scheda "Visualizza";
- muovere l'icona "macro";
- agitare per il "Macro Record";
- compilare il modulo aperto.
Per semplicità, nel "Nome macro" lasciare "Makros1" e nel "Tasti di scelta rapida" viene inserito, per esempio, HH (questo significa che è possibile eseguire il programma di esempio sarà «Ctrl + h» squadra Blitz). Premere Invio.
Ora che avete iniziato la registrazione della macro, costituiscono il contenuto di una cella all'altra. Ritorna alla icona originale. Clicca su "Registra macro". Questa azione segna il completamento di applet.
Avanti:
- nuovamente passare alla stringa "macro";
- è selezionato nella lista "Macro 1";
- fai clic su "Esegui" (la stessa azione inizia Lanciato tasti di scelta rapida Ctrl + «hh»).
Di conseguenza, l'azione che è stata eseguita durante la registrazione della macro.
Ha senso per vedere come il codice è simile. Per fare questo, tornare alla stringa "Macro" e fare clic su "Modifica" o "Invio". Di conseguenza, si trovano in un ambiente VBA. In realtà, il codice stesso è situato tra le linee macro Makros1 Sub () ed End Sub.
Se è stata eseguita la copia, per esempio, da una cella A1 nella cella C1, una delle linee di codice sarà simile Range ( "C1"). Selezionare. Nella traduzione, sembra che "Range (" C1 "). Selezionare", in altre parole, fa un passaggio alla VBA di Excel, nella cella C1.
Una parte attiva del codice completa ActiveSheet.Paste squadra. Significa registrazione contenuti selezionati cella (in questo caso, A1) nella cella C1 selezionata.
ESEMPIO 2
cicli VBA aiutano a creare diverse macro in Excel.
cicli VBA aiutano a creare diverse macro. Si supponga che v'è una funzione y = x + x 3 + 3x 2 – cos (x). Si desidera creare una macro per la sua grafica. Questo può essere fatto solo utilizzando cicli VBA.
Per il valore iniziale e finale delle funzioni argomenti prendere x1 = 0 e x2 = 10. Inoltre, è necessario introdurre una costante – il valore di passo cambia l'argomento e un valore iniziale per il contatore.
Tutti gli esempi di macro VBA di Excel vengono creati utilizzando la stessa procedura come esposto sopra. In questo caso particolare, il codice è simile:
programm Sub ()
x1 = 1
x2 = 10
shag = 0,1
i = 1
Do While x1 <x2 (ciclo verrà eseguito fino a quando l'espressione è vera x1 <x2)
y = x1 + x1 ^ 2 + 3 * x1 ^ 3 – Cos (x1)
Cellule (i, 1) .Value = x1 (valore x1 scritti nella memoria con le coordinate (i, 1))
Cellule (i, 2) .Value = y (valore y viene scritto nella cella con le coordinate (i, 2))
i = i + 1 (valore valido);
x1 = x1 + shag (argomento è cambiato la dimensione del passo);
cappio
End Sub.
Come risultato di questa macro run "Excel" ottenere due colonne, il primo dei quali sono registrati i valori per x, e la seconda – a y.
Quindi pianificare in grado di costruire su di essi, lo standard per la "Excel".
ESEMPIO 3
Per implementare i cicli in VBA Excel 2010, così come in altre versioni, insieme al già ridotto Do Mentre il design utilizzato per.
Si consideri un programma che crea una colonna. In ogni cella sarà registrato caselle corrispondenti numero di riga. Per l'uso del disegno permetterà di registrarlo molto brevemente, senza l'uso di un contatore.
In primo luogo è necessario creare una macro, come descritto sopra. Quindi, scrivere il codice stesso. Noi crediamo che ci interessa i valori per le 10 celle. Il codice è il seguente.
Per i = 1 a 10 successivi
Il comando viene trasferito al linguaggio "umano", come "ripetuto da 1 a 10 in incrementi di uno."
Se il compito di ricevere la colonna con le piazze, ad esempio, tutti gli interi dispari tra 1 e 11, ci scrivono:
Per i = 1 a 10 passaggio 1 Avanti.
Ecco, passo – passo. In questo caso, è pari a due. Per default, l'assenza della parola nel ciclo significa che un singolo passo.
I risultati devono essere memorizzati nella cella (i, 1). Poi ogni volta che si avvia il ciclo con un aumento del valore dei passo crescerà automaticamente e il numero di riga. Così, ci saranno ottimizzazione del codice.
In generale, il codice sarà simile:
sottoprogramma ()
Per i = 1 a 10 Fase 1 (può essere scritta solo per i = 1 a 10)
Cellule (i, 1) .Value = i ^ 2 (cioè valore quadratico viene scritto nella cella (i, 1) i)
Next (in un certo senso gioca il ruolo dei mezzi contatore e un altro inizio del ciclo)
End Sub.
Se fatto correttamente, compresi registrazione e funzionamento macro (cfr. Le istruzioni sopra), allora si chiama ogni volta che una data dimensione sarà ottenuta colonna (in questo caso costituito da 10 celle).
ESEMPIO 4
Nella vita di tutti i giorni, molto spesso è necessario prendere questa o quella decisione in base ad alcune condizioni. Non può fare a meno di loro in VBA di Excel. Esempi di programmi in cui è stato selezionato l'ulteriore corso dell'algoritmo anziché inizialmente predeterminati, design più comunemente usato di Se … Si (per i casi difficili) Se … Allora … END Se.
Si consideri il caso particolare. Si supponga di voler creare una macro per "Excel" per la cella con le coordinate (1,1) è stato registrato:
1 se l'argomento è positivo;
0 se l'argomento è zero;
1, se l'argomento è negativo.
La creazione di una macro per "Excel" si avvia in modo standard, attraverso l'utilizzo di tasti "caldi" Alt e F11. Ulteriori scritto il seguente codice:
sottoprogramma ()
x = Cells (1, 1) .Value (Questo comando assegna il valore delle coordinate x del contenuto della cella (1, 1))
Se x> 0 allora Cells (1, 1) .Value = 1
Se x = 0 allora Cells (1, 1) .Value = 0
Se x <0 allora Cells (1, 1) .Value = -1
End Sub.
Resta da eseguire una macro ed entrare in "Excel" valore desiderato per l'argomento.
funzioni VBA
Come avrete notato, nel più famoso programma di foglio di calcolo di Microsoft non è troppo difficile. Soprattutto se si impara come utilizzare le funzioni VBA. In totale, questo linguaggio di programmazione creato appositamente per la scrittura di applicazioni in "Excel" e la Parola, circa 160 funzioni. Essi possono essere suddivisi in diverse grandi gruppi. Essi sono:
- Funzioni matematiche. La loro applicazione all'argomento del valore del coseno è ottenuto, il logaritmo naturale, e quindi tutta la parte.
- funzioni finanziarie. Grazie alla loro disponibilità e l'utilizzo di programmazione in Excel, è possibile ottenere strumenti efficaci per gli insediamenti contabile e finanziaria.
- funzioni di elaborazione matrice. Questi includono Array, IsArray; LBound; UBound.
- Funzione VBA di Excel per la linea. Questo è un grande gruppo. Esso include, per esempio, la funzione spazio per creare una stringa con un numero di lacune uguale all'argomento intero o trasferimenti Asc simboli per codici ANSI. Tutti loro sono ampiamente utilizzati e consentono di lavorare con le stringhe in "Excel" per la creazione di applicazioni molto più facile lavorare con queste tabelle.
- funzioni di conversione tipo di dati. Ad esempio, CVar ritorna argomento espressione, convertendolo in Variant tipo di dati.
- funzioni di data. Hanno l'espansione delle caratteristiche standard di "Excel". Quindi, la funzione WeekdayName restituisce il nome (completo o parziale) del giorno della settimana con il suo numero. Ancora più utile è il timer. Egli dà il numero di secondi trascorsi dalla mezzanotte per un particolare momento della giornata.
- Funzione per convertire un argomento numerico nei diversi sistemi numerici. Ad esempio, ott uscite alla rappresentazione ottale del numero.
- funzioni di formattazione. Il più importante di questi è il formato. Esso restituisce un Variant con un'espressione formattata in base alle istruzioni fornite nella descrizione del formato.
- e così via.
Lo studio delle proprietà di queste funzioni e la loro applicazione amplierà in modo significativo la portata di "Excel".
ESEMPIO 5
Cerchiamo di risolvere i problemi più complessi. Ad esempio:
Dan documento cartaceo il livello effettivo dei costi della relazione impresa. richiede:
- sviluppare la sua porzione con motivi dal foglio di calcolo "Excel";
- fare un programma VBA che richiedere dati grezzi a riempirlo, per effettuare i calcoli necessari e compilare il modello della cella corrispondente.
Prendere in considerazione una delle seguenti soluzioni.
Creazione di un modello
Tutte le azioni vengono eseguite su un normale foglio in Excel. cellule gratuito riservato per l'inserimento dei dati sui mese, anno, società di gestione titoli-consumo, l'ammontare delle spese, il loro livello di fatturato. Poiché il numero di aziende (aziende), per le quali il rapporto non viene registrata, le cellule a fare sulla base di valori e nome d'arte non è prenotato in anticipo. Foglio di lavoro assegnato a un nuovo nome. Ad esempio, "Օ Report".
variabili
Per scrivere il programma compila automaticamente nel modello, selezionare la notazione. Essi saranno utilizzati per le variabili:
- NN- numero della riga corrente della tabella;
- TP e TF – pianificato e fatturato reale;
- SF e SP – attuali e previsti costi totali;
- IP e IF – il livello dei costi previsti ed effettivi.
Indichiamo con le stesse lettere, ma con un "prefisso» itog accumulo totale per quella colonna. Ad esempio, ItogTP – Per quanto riguarda la colonna dal titolo, come "il giro d'affari previsto."
Soluzione del problema utilizzando la programmazione VBA
Utilizzando questa notazione, si ottiene la formula per la varianza. Se si desidera effettuare il calcolo in% abbiamo (F – P) / P * 100, e nella quantità di – (F – P).
I risultati di questi calcoli può essere migliore solo per fare foglio di calcolo "Excel" nelle celle appropriate.
Per i risultati e la prognosi effettivamente ottenuto dalle formule ItogP = ItogP + P e ItogF = ItogF + F.
Per deviazioni utilizzano = (ItogF – ItogP) / ItogP * 100 se il calcolo viene effettuato in percentuale, e se il valore totale – (ItogF – ItogP).
I risultati ancora una volta registrati nelle celle appropriate, quindi non c'è alcuna necessità di assegnare alle variabili.
Prima di iniziare a creare un programma che si desidera salvare la cartella di lavoro, ad esempio, sotto il nome di "Otchet1.xls".
Premere il tasto "Creare una tabella dei rapporti," è necessario premere solo 1 volta dopo aver inserito le informazioni di intestazione. Si deve sapere e le altre norme. In particolare, il pulsante "Inserisci linea" deve essere premuto ogni volta dopo l'ingresso nella tabella di valori per ciascuna attività. Dopo aver inserito tutti i dati necessari per premere il tasto "Fine", e quindi passa a "Excel" nella finestra.
Ora che sapete come risolvere il problema per Excel con le macro. La possibilità di utilizzare VBA Excel (vedi programmi di esempio. Sopra) e potrebbe essere necessario per lavorare in un ambiente tra i più popolare in questo momento, l'editor di testo "Parola". In particolare, registrando, come indicato all'inizio di questo articolo, o scrivendo codice per creare i pulsanti del menu attraverso il quale molte delle operazioni sul testo può essere fatto premendo i tasti di turno o attraverso l'icona "Visualizza" e scheda "Macro".
