Lezione 9 Oggetti Moduli e Controlli Registratore di

Lezione 9 Oggetti Moduli e Controlli Registratore di Macro Lez. 9 (10/11) - PB Elementi di Programmazione 1

Oggetti • Gli oggetti sono le componenti fondamentali di VB(A) • Un oggetto è un contenitore di variabili, funzioni e subroutine che perseguono uno scopo comune – Gli oggetti sono all’interno di una gerarchia • L’insieme è un oggetto che contiene al suo interno altri oggetti solitamente dell stesso tipo – L'insieme Workbooks di Microsoft Excel contiene, ad esempio, tutti gli oggetti Workbook aperti. Lez. 9 (10/11) - PB Elementi di Programmazione 2

Oggetti – I dati su cui lavorano gli oggetti sono detti proprietà. – Le funzioni contenute in un oggetto sono dette metodi. – Alle proprietà ed ai metodi di un oggetto si accede attraverso la variabile di tipo oggetto seguita dal carattere punto (. ). • Ad esempio l’oggetto Active. Cell indica la cella al momento attiva, contiene fra gli altri – Le proprietà Address, Formula e Value – I metodi Cells() e Border. Around() Lez. 9 (10/11) - PB Elementi di Programmazione 3

Oggetti Sub prova. Oggetto() Msg. Box ("cella attiva: " & Active. Cell. Address) Msg. Box ("la cella contiene: " & Active. Cell. Value) Msg. Box ("la formula nella cella è: " & _ Active. Cell. Formula) Active. Cell. Border. Around xl. Double, xl. Thick, _ Color: =RGB(255, 0) Active. Cells(4, 5). Select ’ si sposta relativamente alla cella precedente Active. Cell. Value = 90 End Sub Lez. 9 (10/11) - PB Elementi di Programmazione 4

Oggetti • L’istruzione with permette di evitare la ripetizione della catena di oggetti fino ai metodi ed alle proprietà dell’ultimo oggetto: Sub prova. Oggetto() with Active. Cell Msg. Box ("cella attiva: " &. Address) Msg. Box ("la cella contiene: " &. Value) Msg. Box ("la formula nella cella è: " &. Formula). Border. Around xl. Double, xl. Thick, Color: =RGB(255, 0, 0). Cells(4, 5). Select 'cambia cella attiva. Value = 90 End With End Sub Lez. 9 (10/11) - PB Elementi di Programmazione 5

Oggetti – Per assegnare ad una variabile o ad una proprietà un oggetto si deve far precedere l’istruzione di assegnamento dalla parola chiave Set. Sub uso. Set() Dim foglio As Worksheet Set foglio = Worksheets(1) foglio. Name = "ancora" End Sub Lez. 9 (10/11) - PB Elementi di Programmazione 6

Oggetto Range • E’ un oggetto particolarmente importante in VBA – le singole celle ed i gruppi di celle sono di tipo Range. – Alcune proprietà • Rows indica tutte le righe di Range • Columns indica tutte le colonne di Range Lez. 9 (10/11) - PB Elementi di Programmazione 7

Oggetto Range Sub modifica. Riga(rn As Range) Dim Cell As Range Dim I as Integer I = 0 For Each Cell In rn. Rows Cell = I I = I + 1 Next End Sub modifica. All(rn As Range) Dim Cell As Range Dim I as Integer I = 10 For Each Cell In rn Cell = I I = I + 1 Next End Sub richiama() modifica. Riga Range("a 1. . D 5") modifica. All Range("a 6. . D 10") End Sub Lez. 9 (10/11) - PB Elementi di Programmazione 8

Oggetto Range Sub modifica. Riga(rn As Range) Dim righe As Range, i As Integer Set righe = rn. Rows For i = 1 To righe. Count righe. Item(i) = -5 Next Item proprietà che permette di scorrere sugli elementi End Sub richiama() modifica. Riga Range("a 1. . D 5") End Sub Lez. 9 (10/11) - PB Elementi di Programmazione 9

Moduli e Controlli • In alcuni casi è utile facilitare l’utente nell’inserimento dei dati questo può avvenire attraverso i moduli (Form) e attraverso i controlli in essi contenuti. – I controlli sono degli oggetti contenuti in un oggetto di tipo Form o in un foglio di lavoro (p. es. i bottoni) e possono associare a dei precisi eventi (schiacciare un tasto, schiacciare il mouse, . . . ) una funzione appositamente scritta. Lez. 9 (10/11) - PB Elementi di Programmazione 10

Moduli e Controlli – Vedremo alcune delle principali tipologie di controlli e degli eventi da cui sono attivati – Sulla barra sono presenti i seguenti controlli (da sx verso dx): • • • Casella di controllo Casella di testo Pulsante di comando (bottone) Pulsante di opzione Casella di riepilogo Casella combinata Interruttore Pulsante di selezione Barra di scorrimento Etichetta Immagine Altri controlli Lez. 9 (10/11) - PB Elementi di Programmazione controlli 11

Moduli e Controlli – Fra i controlli quello più utile da inserire direttamente in un foglio Excel è il Pulsante di comando che può attivare una routine per effettuare dei controlli o per elaborare dei valori presenti nel foglio. • Creare un bottone che permette di verificare se i valori presenti nel foglio elettronico dalle cella A 1 alla cella A 10 sono in progressione aritmetica (la differenza fra due valori consecutivi è costante). L’esito della verifica è scritto nella cella B 1 Lez. 9 (10/11) - PB Elementi di Programmazione 12

Moduli e Controlli • Innanzitutto nel foglio di lavoro – Si attiva la modalità progettazione – si sceglie il controllo bottone e si posiziona il mouse dove si desidera che sia inserito • Con il tasto destro si seleziona la voce proprietà e si modificano quelle desiderate, ad esempio: – (Name): il nome dell’oggetto in VBA – Caption: indica il testo contenuto nel bottone • Si schiaccia due volte il bottone si apre l’editor di VBA su di una Sub chiamata con il nome immesso nel campo (Nome) seguita da _Click() – Questo è il codice che sarà richiamato quando si schiaccia sul bottone • Per attivare il bottone bisogna uscire dalla modalità progettazione Lez. 9 (10/11) - PB Elementi di Programmazione 13

Lez. 9 (10/11) - PB Elementi di Programmazione 14

Moduli e Controlli Private Sub Succ. Arit_Click() Dim diff As Integer, x As Range Dim prog. Ar As Boolean, prec As Integer prog. Ar = True diff = Range("A 1") - Range("A 2") prec = Range("A 2"). Value For Each x In Range("A 3. . A 10") If (prec - x. Value <> diff) Then prog. Ar = False End If prec = x. Value Next If prog. Ar Then Range("B 1") = "in progressione aritmetica" Else Range("B 1") = "non in progressione aritmetica" End If End Sub Lez. 9 (10/11) - PB Elementi di Programmazione 15

Moduli e Controlli – In un foglio elettronico far richiamare con un pulsante una funzione che svuota le celle da A 1 a D 25 Lez. 9 (10/11) - PB Private Sub Svuotamento_Click() Dim x As Range For Each x In Range("A 1. . D 25") x. Value = "" Next End Sub Elementi di Programmazione 16

Moduli e Controlli – Il modulo è un metodo comodo permettere la selezione e la validazione di un certo numero di voci – Per creare un modulo (User. Form o Form) occorre: • Entrare nell’editor VBA • Selezionare la voce User. Form dal menù Inserisci – In alternativa si può selezionare l’icona • Aggiungere i controlli desiderati Lez. 9 (10/11) - PB Elementi di Programmazione 17

Moduli e Controlli – Le subroutine che terminano con _Click() si attivano quando si schiaccia/seleziona l’oggetto • Tabulazione_click() è richiamata dal pulsante di comando con scritto Tabula! • User. Form_Click() è richiamata quando si seleziona il bottone chiudi del User. Form – Al termine dell’esecuzione si deve chiudere il modulo con l’istruzione Me. hide – Per poter utilizzare la user form si crea nella sezione Moduli del progetto VBA la sub: Sub Tabula() User. Form 1. Show End Sub Lez. 9 (10/11) - PB Elementi di Programmazione 18

Moduli e Controlli – Vediamo attraverso alcuni esempi i principali controlli e alcune loro proprietà • I moduli saranno richiamati tramite – un pulsante di comando – Una (nuova) macro di Excel selezionabile da Strumenti->Macro Lez. 9 (10/11) - PB Elementi di Programmazione 19

Moduli e Controlli – Costruiamo un semplice esempio di Modulo usato per creare una sequenza di 10 valori ordinati a partire di valore 0 con un incremento scelto dall’utente. • Useremo una User. Form contenente – Due Option. Button uno per ciascuno dei possibili passi (0, 5; 1) » Selezionando l’opzione si attiva il passo » Il primo valore è quello usato in caso di mancata scelta – Due Label con la legenda di ogni opzione » Schiacciando sull’etichetta si attiva il passo scelto – Un Button (pulsante di comando) per attivare la creazione della sequenza a partire dalla cella attiva chiamato “Tabulazione” Lez. 9 (10/11) - PB Elementi di Programmazione 20

Moduli e Controlli – Con l’editor dei form si crea la seguente situazione: Lez. 9 (10/11) - PB Elementi di Programmazione 21

Moduli e Controlli Option Explicit Dim Passo As Double Private Sub Tabulazione_Click() Dim i As Double, j As Integer Private Sub Label 1_Click() Passo = 0. 5 Option. Button 1. Value = True End Sub If Passo = 0 Then Passo = 0. 5 End If i = 0 For j = 1 To 10 Active. Cell. Offset(j - 1, 0). Value = i i = i + Passo Next j Me. Hide End Sub Private Sub Label 2_Click() Passo = 1# Option. Button 2. Value = True End Sub Private Sub Option. Button 1_Click() Passo = 0. 5 End Sub Private Sub User. Form_Click() Me. Hide End Sub Private Sub Option. Button 2_Click() Passo = 1# End Sub Lez. 9 (10/11) - PB Elementi di Programmazione 22

Moduli e Controlli – Creiamo una User. Form per scegliere fra tre tipi di funzioni e quindi, prelevando il valore dalla cella attiva, si applica a questo la funzione scelta scrivendo il risultato nella cella accanto • Si utilizza una Combo. Box – Si crea l’elenco delle voci in un foglio di lavoro compreso nel file Excel si dà un nome all’elenco (Inserisci->Nome>crea) – si mette questo nome nella proprietà Row. Source – Infine si scrive il codice da utilizzare quando l’utente sceglie (Sub Combo. Box 1_Change()) Lez. 9 (10/11) - PB Elementi di Programmazione 23

Moduli e Controlli Option Explicit Private Sub Combo. Box 1_Change() If Me. Combo. Box 1. Value = 0 Then Active. Cell. Offset(0, 1). Value = Sin(Active. Cell. Value) Else If Me. Combo. Box 1. Value = 1 Then Active. Cell. Offset(0, 1). Value = Cos(Active. Cell. Value) Else Active. Cell. Offset(0, 1). Value = Tan(Active. Cell. Value) End If Me. Combo. Box 1. Value = "" Me. Hide End Sub Private Sub User. Form_Click() Me. Combo. Box 1. Value = "" Me. Hide End Sub Lez. 9 (10/11) - PB Elementi di Programmazione 24

Moduli e Controlli – Creiamo un esempio nel quale dai dati contenuti in una tabella calcoliamo • Coefficiente di correlazione lineare • Quindi se è compreso fra -1 e -0, 3 o fra 0, 3 ed 1 calcoliamo i valori per la retta di interpolazione lineare y=a 0+a 1 x Lez. 9 (10/11) - PB Elementi di Programmazione 25

Moduli e Controlli option Explicit Private Sub calcolo. Coeff_Click() Dim r As Double, a 0 As Double, a 1 As Double Dim Sx As Double, Sy As Double, Sx 2 As Double, Sy 2 As Double Dim Sxy As Double Dim i As Integer, j As Integer Sx = 0 Sy = 0 Sx 2 = 0 Sy 2 = 0 Sxy = 0 i = 2 j = 0 While Is. Numeric(Cells(i, 1). Value) And Not Is. Empty(Cells(i, 1)) Sx = Sx + Cells(i, 1). Value Sy = Sy + Cells(i, 2). Value Sx 2 = Sx 2 + Cells(i, 1). Value ^ 2 Sy 2 = Sy 2 + Cells(i, 2). Value ^ 2 Sxy = Sxy + Cells(i, 1). Value * Cells(i, 2). Value j = j + 1 i = i + 1 Wend Lez. 9 (10/11) - PB Elementi di Programmazione 26

Moduli e Controlli r = (j * Sxy - Sx * Sy)/((j * Sx 2 - Sx ^ 2) * (j * Sy 2 - Sy ^ 2))^(0. 5) If r >= -0. 3 And r <= 0. 3 Then Msg. Box ("non esiste correlazione far i dati") Else a 1 = (Sx * Sx 2 - Sx * Sxy) / (j * Sx 2 - Sx ^ 2) a 0 = (j * Sxy - Sx * Sy) / (j * Sx 2 - Sx ^ 2) For i = j + 1 To 2 Step -1 Cells(i, 3). Value = Cells(i, 1). Value * a 1 + a 0 Next Cells(3, 6). Value = r Cells(4, 6). Value = a 0 Cells(5, 6). Value = a 1 End If End Sub Lez. 9 (10/11) - PB Elementi di Programmazione 27

Aggiungere funzioni • E’ possibile aggiungere alle funzioni predefinite in Excel e richiamabili nel foglio elettronico delle funzioni scritte dall’utente – Le nuove funzioni vanno aggiunte in un modulo dall’editor di VBA: • Inserisci->Modulo – Si scrivono le funzioni che compariranno nell’insieme delle funzioni selezionabili sotto la voce Definite dall’utente Lez. 9 (10/11) - PB Elementi di Programmazione 28

Aggiungere funzioni – Creare la funzione prog. Geom che ha come argomento un intervallo di valori e restituisce True se i valori in esso contenuto sono in progressione geometrica (il rapporto fra due valori consecutivi è costante), altrimenti False Option Explicit Function prog. Geom(r As Range) As Boolean Dim x As Range, i As Integer prog. Geom = True If (r. Count > 2) Then For i = 1 To (r. Count - 3) If (r. Item(i). Value / r. Item(i + 1). Value _ <> r. Item(i + 1). Value / r. Item(i + 2). Value) Then prog. Geom = False End If Next End If End Function Lez. 9 (10/11) - PB Elementi di Programmazione 29

Function prog. Arit(r As Range) As Boolean Dim i As Integer prog. Arit = True With r If (. Count > 2) Then For i = 1 To (r. Count - 3) If (. Item(i). Value -. Item(i + 1). Value _ <>. Item(i + 1). Value -. Item(i + 2). Value) Then prog. Arit = False End If Next End If End With End Function Lez. 9 (10/11) - PB Elementi di Programmazione 30

Registratore di macro – Excel fornisce un utile strumento per poter registrare il codice generato in base alle azioni che compiamo mentre lavoriamo. – In questo modo è possibile esaminare il codice prodotto e quindi regolarsi sui comandi da impiegare – Per attivare questa funzionalità: Strumenti -> Macro -> Registra macro • Viene chiesto un nome da dare alla macro • Inizia la registrazione che termina schiacciando il tasto STOP Tasto STOP Registra dando riferimenti relativi • Il codice generato si troverà nella cartella moduli di VBA Lez. 9 (10/11) - PB Elementi di Programmazione 31

Registratore di Macro – Utilizziamo il registratore di macro per registrare i passi relativi alla creazione di un grafico a partire dai dati presenti in una tabella • Si ha un insieme di valori nel range A 2: B 6 • Si deve creare un grafico di dispersione Lez. 9 (10/11) - PB Elementi di Programmazione 32

Registratore di Macro Sub Grafico() Charts. Add Active. Chart. Type = xl. XYScatter. Smooth Active. Chart. Set. Source. Data Source: =Sheets("Foglio 3"). Range("A 2: B 6"), Plot. By _ : =xl. Columns Active. Chart. Series. Collection(1). Name = "=""valori Sperimentali""" Active. Chart. Location Where: =xl. Location. As. Object, Name: ="Foglio 3" With Active. Chart. Has. Title = True. Chart. Title. Characters. Text = "valori Sperimentali". Axes(xl. Category, xl. Primary). Has. Title = True. Axes(xl. Category, xl. Primary). Axis. Title. Characters. Text = "X". Axes(xl. Value, xl. Primary). Has. Title = True. Axes(xl. Value, xl. Primary). Axis. Title. Characters. Text = "Y" End With Active. Chart. Axes(xl. Category). Has. Major. Gridlines = False. Has. Minor. Gridlines = True End With Active. Chart. Axes(xl. Value). Has. Major. Gridlines = True. Has. Minor. Gridlines = False End With End Sub Lez. 9 (10/11) - PB Elementi di Programmazione 33

Moduli e Controlli - Addendum • In un foglio elettronico si desiderano calcolare i valori della funzione tasso di Poisson (K) e C di Erlang a partire dai valori N > 0, numero di servitori, e 0<=ρ<=1 utilizzazione usando le formule: Lez. 9 (10/11) - PB Elementi di Programmazione 34

(Tasso di Poisson) Un processo di Poisson è un processo stocastico definito riguardo il manifestarsi di eventi. Questo processo di conta, dato come una funzione del tempo N(t), rappresenta il numero di eventi a partire dal tempo t = 0. Inoltre il numero di eventi tra il tempo a e il tempo b è dato come N(b) - N(a) ed ha una distribuzione di Poisson. Lez. 9 (10/11) - PB Elementi di Programmazione 35

(Formula di Erlang C) Erlang C è la distribuzione che descrive la probabilità di attesa in coda in un sistema in cui ci sono N serventi e viene offerto un traffico pari a A Erlang. dove: • A è il traffico totale offerto in Erlang • N è il numero di serventi (operatori) presenti • P(>0) è la probabilità di attesa in coda Lez. 9 (10/11) - PB Elementi di Programmazione 36

Moduli e Controlli Private Sub Calcolo_Click() Dim N As Integer, rho As Double, h As Integer Dim prec As Double, num As Double, den As Double 'verifica coerenza If Is. Numeric(Range("B 9"). Value) = False Then Msg. Box ("fornire un valore numerico per N!") Range("B 9"). Value = "" Exit Sub Else If Int(Range("B 9"). Value) <> Range("B 9"). Value Then Msg. Box ("fornire un numero intero per N!") Range("B 9"). Value = "" Exit Sub End If Il valore di N in B 9 di rho in B 10 End If di K in B 14 di C in B 15 N = Range("B 9"). Value If N <= 0 Then Msg. Box "Il valore di N deve essere positivo", vb. Critical Range("B 9"). Value = "" Exit Sub End If Lez. 9 (10/11) - PB Elementi di Programmazione 37

Moduli e Controlli If Is. Numeric(Range("B 10"). Value) = False Then Msg. Box ("fornire un valore numerico per rho!") Range("B 10"). Value = "" Exit Sub End If rho = Range("B 10"). Value If rho <= 0 Or rho >= 1 Then Msg. Box "rho fra 0_ ed 1 E. E", vb. Critical Range("B 10"). Value = "" Exit Sub End If 'calcolo K Il valore di prec = 1 num = 1 For h = 1 To N - 1 prec = prec * N * rho / h num = num + prec Next den = num + prec * N * rho / N N in B 9 di rho in B 10 di K in B 14 di C in B 15 Range("B 14"). Value = num / den Range("B 15"). Value = (1 - Range("B 14"). Value) / (1 - Range("B 14"). Value * rho) End Sub Lez. 9 (10/11) - PB Elementi di Programmazione 38