Lezione 10
Funzioni e Moduli
Macro
Analisi del flusso
Esercizi
Lez. 10 (11/12) - PB
Elementi di Programmazione
1
Form e Controlli (13)
– Creiamo una UserForm 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 ComboBox
– 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à RowSource
– Infine si scrive il codice da utilizzare quando l’utente
sceglie (Sub ComboBox1_Change())
Lez. 10 (11/12) - PB
Elementi di Programmazione
2
Form e Controlli (14)
Option Explicit
Private Sub ComboBox1_Change()
If Me.ComboBox1.Value = 0 Then
ActiveCell.Offset(0, 1).Value = Sin(ActiveCell.Value)
Else
If Me.ComboBox1.Value = 1 Then
ActiveCell.Offset(0, 1).Value = Cos(ActiveCell.Value)
Else
If Me.ComboBox1.Value = 2 Then
ActiveCell.Offset(0, 1).Value = Tan(ActiveCell.Value)
End If
End If
End If
Me.ComboBox1.Value = ""
Me.Hide
End Sub
Private Sub UserForm_Click()
Me.ComboBox1.Value = ""
Me.Hide
End Sub
Lez. 10 (11/12) - PB
Elementi di Programmazione
3
Form e Controlli (15)
‘ Inizializzazione della Combo Box all’apertura del form
Private Sub UserForm_Initialize()
With ComboBox1
.AddItem "0"
.AddItem "1"
.AddItem "2"
End With
End Sub
Lez. 10 (11/12) - PB
Elementi di Programmazione
4
Aggiungere funzioni (1)
• 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. 10 (11/12) - PB
Elementi di Programmazione
5
Aggiungere funzioni (2)
– Creare la funzione progGeom 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 progGeom(r As Range) As Boolean
Dim x As Range, i As Integer
progGeom = 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
progGeom = False
End If
Next
End If
End Function
Lez. 10 (11/12) - PB
Elementi di Programmazione
6
Aggiungere funzioni - Esercizio
– Creare la funzione progArit che ha come argomento un
intervallo di valori e restituisce True se i valori in esso
contenuto sono in progressione aritmetica (la differenza
fra due valori consecutivi è costante), altrimenti False
Lez. 10 (11/12) - PB
Elementi di Programmazione
7
Aggiungere funzioni - Soluzione
–
Creare la funzione progArit che ha come argomento un intervallo di valori e
restituisce True se i valori in esso contenuto sono in progressione aritmetica (la
differenza fra due valori consecutivi è costante), altrimenti False
Function progArit(r As Range) As Boolean
Dim i As Integer
progArit = 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
progArit = False
End If
Next
End If
End With
End Function
Lez. 10 (11/12) - PB
Elementi di Programmazione
8
Registratore di Macro (1)
– 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. 10 (11/12) - PB
Elementi di Programmazione
9
Registratore di Macro (2)
– 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 A2:B6
• Si deve creare un grafico di dispersione
Lez. 10 (11/12) - PB
Elementi di Programmazione
10
Registratore di Macro (3)
Sub Grafico()
Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData Source:=Sheets("Foglio3").Range("A2:B6"), PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(1).Name = "=""valori Sperimentali"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Foglio3"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "valori Sperimentali"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = True
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
End Sub
Lez. 10 (11/12) - PB
Elementi di Programmazione
11
Registratore di Macro (4)
N.B.: le macro possono essere diverse a
seconda dei passi esatti seguiti durante la
“performance”.
Quindi non stupirsi di eventuali differenze…
Lez. 10 (11/12) - PB
Elementi di Programmazione
12
Analisi del flusso - 1
Option Explicit
Sub prova(ByRef a As_
Integer, _
ByVal b As Integer)
a = a * 2
b = b * 2
End Sub
Sub richiama()
Dim x As Integer, _
y As Integer
x = 2
y = 10
call prova (x, y)
End Sub
Lez. 10 (11/12) - PB
richiama()
x
y
0
0
2
0
2
10
2
10
4
10
4
10
4
10
prova()
a
b
(^x)
10
(^x)
10
(^x)
20
tempo
Elementi di Programmazione
13
Analisi del flusso - 2
Sub prova()
Dim v As Integer, q As Integer
v = 10
q = 20
Do While v > 0 And q > 5
v=v-1
q=q-1
Loop
v
10
9
8
q
20
19
18
1
0
11
10
…
…
Range("D1") = v
Range("D2") = q
End Sub
Lez. 10 (11/12) - PB
Elementi di Programmazione
14
Analisi del flusso - 3
Option Explicit
Sub prova()
Dim v As Integer
v=4
Do While True
v=v+1
Range("F1") = v
Loop
v
4
5
6
:
:
32767
Range("D1") = v
End Sub
Lez. 10 (11/12) - PB
Elementi di Programmazione
15
Analisi del flusso - 4
Sub primaProc(v1 As Integer, _
ByVal v2 As Integer)
Dim L As Integer
v1 = v1 * 3
v2 = v2 * 2
L = v1 + v2
End Sub
Sub richiama()
Dim x As Integer, y As Integer
x = 8
y = 100
richiama()
x
y
8
100
8
100
24
100
24
100
24
100
24
100
primaProc()
v1
v2
L
^x
100
0
^x
100
0
^x
200
0
^x
200 224
primaProc x, y
End Sub
Lez. 10 (11/12) - PB
Elementi di Programmazione
16
Analisi del flusso - 5
Option Explicit
Sub uno(a As Double)
Dim b As Double
b = a * 2
a = a - 2
End Sub
Sub richi()
Dim x As Double, a As Double
x = 10
a = 50
call uno (x)
call uno (a)
End Sub
Lez. 10 (11/12) - PB
richi()
x
a
0
0
10
0
10
50
10
50
10
50
8
50
8
50
8
50
8
50
8
48
8
48
Elementi di Programmazione
uno()
a
(^x)
(^x)
(^x)
b
0
20
20
(^arichi) 0
(^arichi) 100
(^arichi) 100
17
Analisi del flusso - 6
Option Explicit
Function raddoppia(A As Double) _
As Double
raddoppia = A * 2
A = A - 2
End Function
Sub rch()
Dim x As Double, y As Double
Dim z As Double, w As Double
x = 10
y = 20
z = 30
Call raddoppia(x)
call raddoppia(y)
w = raddoppia(z)
rch()
x y
0 0
10 20
10 20
10 20
8 20
z
0
30
30
30
30
w
0
0
0
0
0
8
8
8
8
20
20
20
18
30
30
30
30
0
0
0
0
8
8
8
8
18
18
18
18
30
30
30
28
0
0
0
0
End Sub
8 18
Lez. 10 (11/12) - PB
raddoppia()
A
raddoppia
(^x)
0
(^x)
20
(^x)
20
20 <(^y)
0
(^y)
40
(^y)
40
40 <(^z)
0
(^z)
60
(^z)
60
60 <-
28 60
Elementi di Programmazione
18
Esercizi
Lez. 10 (11/12) - PB
Elementi di Programmazione
19
Esercizio 1
• Scrivere la funzione sommaDis() da
aggiungere a quelle definite nel foglio
elettronico in uso in modo che calcoli la
somma algebrica dei numeri contenuti in
un intervallo.
– L’intervallo di celle può essere formato da più
intervalli.
• Il parametro ha quindi un numero variabile di
argomenti quindi è un ParamArray
Lez. 10 (11/12) - PB
Elementi di Programmazione
20
Esercizio 1
Function sommaDis(ParamArray r() As Variant) As Double
Dim i As Integer, y As Variant
sommaDis = 0
For i = LBound(r) To UBound(r)
Indispensabile per gestire
più intervalli di valori
For Each y In r(i)
If (IsNumeric(y)) Then
sommaDis = sommaDis + y
End If
Next
Next
End Function
Lez. 10 (11/12) - PB
Elementi di Programmazione
21
Esercizio 2
• Calcolare in valor medio dell’intervallo di celle
A1:B8 usando la funzione predefinita dei fogli di
lavoro Excel
– Tutte le funzioni contenute nel foglio di lavoro si
possono utilizzare mediante l’oggetto
Application.WorksheetFunction
– I nomi delle funzioni sono quelle usate nella versione
in Inglese
• L’elenco delle funzioni disponibili si può ottenere
– Selezionare la voce Guida di Riferimento a Visual Basic per
Microsoft Excel
– Quindi selezionare Concetti della programmazione
– Quindi Eventi, funzioni di Foglio di lavoro, Forme
– Infine la guida contiene un collegamento a tutte queste funzioni
Lez. 10 (11/12) - PB
Elementi di Programmazione
22
Esercizio 2
Option Explicit
Sub calcola()
Range("D3") = _
Application.WorksheetFunction. _
Average(Range("A1:B8"))
End Sub
L’intervallo è di tipo Range
Lez. 10 (11/12) - PB
Elementi di Programmazione
23
Esercizio 2.1
• Ripetere l’esercizio precedente quando i
valori sono nell’intervallo A1:B8 ed F1:F8
– Appoggiare nella soluzione i valori nella
variabile x
• La variabile sarà di tipo Variant o Double
Lez. 10 (11/12) - PB
Elementi di Programmazione
24
Esercizio 2.1
Option Explicit
Sub calcola()
Dim x As Double
x = _
Application.WorksheetFunction. _
Average(Range("A1:B8", "F1:F8"))
Range("D3") = x
End Sub
Lez. 10 (11/12) - PB
Elementi di Programmazione
25
Esercizio 3
• Leggere il contenuto del file mieiDati.txt contenuto nella
stessa cartella del foglio di lavoro. Questo file contiene
su ogni riga due numeri decimali. Scrivere il contenuto
del file nel foglio di lavoro a partire dalla cella A3 in
questo modo:
– Nella colonna A il primo valore della riga di dati
– Nella colonna B il secondo valore della riga di dati
– Utilizzando le funzione predefinite dal foglio elettronico min e
max calcolare per ogni colonna questi valori e scriverli nelle
righe 1 e 2 del foglio di lavoro.
– Utilizzando la formula ben formata per Excel scritta come stringa
nella cella D1 che ha come variabile _x, applicarla ad ogni
elemento della colonna A a partire da A3 scrivendo il risultato
nella colonna C nella riga corrispondente
Lez. 10 (11/12) - PB
Elementi di Programmazione
26
Esercizio 3 (sol v1)
Option Explicit
Sub scaricaCalcola()
Dim riga As Integer
Dim v1 As Double, v2 As Double
Dim rg As Range, frm As String
Dim frms As String, i As Integer
riga = 2
Open ThisWorkbook.Path & "\" & _
"mieiDati.txt" For Input As #1
Do While Not EOF(1)
riga = riga + 1
Input #1, v1, v2
Cells(riga, 1) = v1
Cells(riga, 2) = v2
Loop
If riga <> 2 Then
Set rg = Range("A3:A" & riga)
Range("A1") = Application.WorksheetFunction.Min(rg)
Range("A2") =
Application.WorksheetFunction.Max(rg)
Set rg = Range("B3:B" & riga)
Range("B1") = Application.WorksheetFunction.Min(rg)
Range("B2") =
Application.WorksheetFunction.Max(rg)
End If
Close #1
frm = Range("D1").Value
For i = 3 To riga
frms = Replace(frm, "_x", CStr(Cells(i, 1).Value))
frms = "=" & Replace(frms, ",", “.")
Cells(i, 3).Formula = frms
Next
End Sub
Lez. 10 (11/12) - PB
Elementi di Programmazione
27
Esercizio 3 (sol v2)
Option Explicit
Option Base 1
Sub esercizio()
Dim v1 As Double, v2 As Double
Dim i As Integer, r As Range
Dim formula As String, fmls As String
Open ThisWorkbook.Path & _
"\mieiDati.txt" For Input As #1
Set r = Range("A3:A" & (i - 1))
Cells(1, 1).Value =
_
Application.WorksheetFunction.Min(r)
Cells(2, 1).Value =
_
Application.WorksheetFunction.Max(r)
Set r = Range("B3:B" & (i - 1))
Cells(1, 2).Value = _
Application.WorksheetFunction.Min(r)
formula = Range("D1")
i = 3
Do While Not EOF(1)
Input #1, v1, v2
Cells(i, 1).Value = v1
Cells(i, 2).Value = v2
Cells(2, 2).Value = _
Application.WorksheetFunction.Max(r)
Close #1
End Sub
fmls = "=" & Replace(formula, _
"_x", _
Replace(CStr(v1), ",", "."))
Cells(i, 3).formula = fmls
i = i + 1
Loop
Lez. 10 (11/12) - PB
Elementi di Programmazione
28
Esercizio 4
• Eliminare dal foglio di lavoro tutti i valori
non numerici contenuti nell’intervallo
A1:C7
– Usare la funzione isnumeric()
Lez. 10 (11/12) - PB
Elementi di Programmazione
29
Esercizio 4
Option Explicit
Sub cancella()
Dim el As Range
For Each el In Range("A1", "C7")
If Not IsNumeric(el.Value) Then
el.Value = ""
End If
Next
End Sub
Lez. 10 (11/12) - PB
Elementi di Programmazione
30
Esercizio 5
• Dato un foglio di lavoro in cui nella
colonna A e nella colonna B ci sono dei
valori numerici, disegnare i grafico di
dispersione della colonna B in funzione
della colonna A
– Non so dove finiscono le due colonne
Lez. 10 (11/12) - PB
Elementi di Programmazione
31
Esercizio 5
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 12/18/2009 by %USERNAME%
'
Ricavato col registratore di macro;
Poi è adattato nella pagina
successiva
'
Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData Source:=Sheets("Sheet4").Range("B1:B" & ), PlotBy:= _
xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Sheet4!R1C1:R3C1"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet4"
ActiveSheet.Shapes("Chart 1").IncrementLeft 157.5
ActiveSheet.Shapes("Chart 1").IncrementTop -80.25
ActiveChart.PlotArea.Select
Selection.Left = 1
Selection.Top = 16
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 1").IncrementLeft -135#
ActiveSheet.Shapes("Chart 1").IncrementTop 165.75
End Sub
Lez. 10 (11/12) - PB
Elementi di Programmazione
32
Esercizio 5
Option Explicit
Sub disegna()
Dim riga As Integer
riga = 1
While Not IsEmpty(Cells(riga, 1))
riga = riga + 1
Wend
riga = riga - 1
Punti in cui è stato modificato
If riga = 0 Then
Exit Sub
End If
Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData Source:=Sheets("Sheet4").Range("B1:B" & riga), PlotBy:= _
xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Sheet4!R1C1:R" & riga & "C1"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet4"
ActiveChart.ChartArea.Select
End Sub
Lez. 10 (11/12) - PB
Elementi di Programmazione
33