Realizzazione di un Database "SelfMade"
In molti mi hanno
scritto chiedendomi delucidazioni circa il codice utilizzato nel programma
"Gestione database" presente in questa sezione. Non potendoli accontentare,
ho realizzato questo programma di cui posso fornire routine e codice, con
relative spiegazioni. Premetto che le procedure impiegate, sono utilizzabili
in tutti quei casi dove sia necessario gestire elenchi di dati, anche
lunghi, come indirizzari, gestione articoli magazzino, elenchi fatture, ecc.
ecc. Il programma viene esemplificato comunque per un "indirizzario", ma
chiunque potrà adattarlo alle proprie esigenze. Chi è interessato, è pregato
di leggersi TUTTE le istruzioni.
Il programma si
basa su dati inseriti su un foglio di lavoro, su cui dovrà essere costruito
lo "scheletro", cioè lo schema che conterrà i dati stessi. Come ogni
database che si rispetti, questi dati saranno organizzati in righe e
colonne. Le colonne conterranno i "campi" del database con relative
"intestazioni di colonna", cioè i nomi dei "campi" (N°, Nominativo,
Indirizzo, Città, ecc.ecc.), e le righe saranno i "record", cioè la zona
dove fisicamente saranno inseriti i dati, ognuno nel proprio "campo"
(colonna) di pertinenza. Molti di voi sanno già cosa si intende per
"Database", quindi proseguiamo col vedere come ho impostato il lavoro. Il
programma si apre sul Foglio1 dove è stata predisposta la tabella che
conterrà i dati. Nell'esempio mi sono limitato ad inserire 6 campi, il N°
progressivo dei record, Nominativo, Indirizzo, Città, Telefono, Cellulare,
E-mail. I dati potranno essere inseriti sul foglio, direttamente, a mano, ma
lo scopo era quello di usare una maschera di introduzione, modifica, ricerca
dei dati presenti nell'elenco sul foglio1. Per questo, sul foglio, è
presente un pulsante per l'apertura della maschera, una Userform dove sono
stati inseriti gli "oggetti" necessari al progetto:
-
2 OptionButton,
per la selezione del metodo di ricerca.
-
7 TextBox che
servono per la gestione della ricerca, e dell'immissione, controllo,
modifica dei dati.
-
7 CommandButton
per l'attivazione delle routine necessarie al progetto.
La tabella inizia dalla riga 2 dove sono inseriti i nomi dei campi; questo
perchè nelle routine utilizzo il metodo End
Select, che partendo dall'alto per trovare la prima riga libera, deve
trovare due celle occupate, altrimenti si precipiterebbe a fine pagina se le
riga successiva alla prima fosse vuota (quando l'elenco è da iniziare, la
prima cella sotto l'intestazione di campo è vuota), per cui nella riga 1,
faccio inserire il segno meno ( - ) e questo serve a far trovare le prime
due celle occupate (la riga1 col meno, la riga2 col nome del campo). Il
formato celle di tutta la tabella è stato impostato a "Testo",
in questo modo i numeri di telefono che partono con lo zero, verranno
registrati correttamente (Excel si rifiuta di accettare numeri che inizino
con zero, a meno che non gli si dica che anzichè un numero, si sta scrivendo
del testo). Inoltre l'elenco l'ho impostato per contenere 150 record di
dati. Chiunque potrà "allungare" a piacere il range operativo, modificando i
riferimenti nelle istruzioni. La colonna A, quella che contiene il N°
progressivo del record, viene inizializzata a mano, sfruttando la capacità
di Excel di completare le "serie": si scrive in
A3 il numero 1, in A4 il numero 2, si selezionano entrambe le caselle, ci si
sposta nell'angolo in basso a destra della casella A4, e quando compare il
puntatore del mouse fatto come una piccola croce nera, si clicca sinistro e
si trascina verso il basso: Excel capisce che si vuole completare una serie,
ed in ogni cella seguente le prime due, inserisce i numeri incrementandoli
di uno.
Vediamo ora le procedure inserite nella Userform, partendo dal pulsante "Inserisci
Nuovo". Quando apriamo la UserForm, le Textbox sono tutte vuote. Se
vorremo inserire un nuovo nominativo, dovremo scrivere i dati nelle
rispettive TextBox, DOPODICHE' cliccheremo sul pulsante. L'istruzione
comincia con un controllo sulla TextBox2, quella destinata a contenere il
"Nominativo": se la trova vuota, avvisa con un messaggio, riposiziona il
focus sella textbox2, ed esce dalla routine senza eseguire il resto delle
istruzioni. Se invece la textbox2 conterrà dei dati , viene posta una
domanda di conferma registrazione dati (questo per
evitare, mentre siamo in modalità "consultazione dati", di premere
inavvertitamente il pulsante che senza un controllo di conferma,
registrerebbe di nuovo i dati già presenti), se si risponderà Si, inizia la copia dei
dati presenti dalle textbox alle celle sul foglio di lavoro. Dopo la copia,
avvisa con un messaggio l'avvenuta esecuzione, indi pulisce le textbox.
Vediamo nel dettaglio le istruzioni:
Range("B1").End(xlDown).Offset(1, 0).Select
- questo è il comando che, seleziona la cella B1, si sposta verso il
basso (End(xlDown) cercando l'ultima cella occupata, trovata questa, si
sposta di una riga sotto, stessa colonna (Offset(1, 0)),
e la seleziona (Select) e la rende
Attiva; questa cella è ovviamente vuota.
ActiveCell.Value = TextBox2 - comincia a
copiare il contenuto delle textbox sulla userform con un semplice segno di
uguale (=), cioè :la cella sul foglio di lavoro in quel momento attiva,
viene resa uguale ai dati contenuti nella textbox2, poi a seguire:
ActiveCell.Offset(0, 1).Value = TextBox3 -
viene cercata con Offset la prima cella a destra di quella attiva in quel
momento, e viene resa uguale al contenuto della textbox successiva, la 3. Si
continua proseguendo con lo "scarto" (Offset)
di una cella, stessa riga, per ogni textbox interessata. Completate queste
istruzioni, appare il messaggio di esecuzione completata, indi
TextBox2 = "" - si puliscono le celle
predisponendole per nuovi inserimenti.
Private Sub
CommandButton2_Click()
If TextBox2 = "" Then
MsgBox "Devi Inserire almeno il nominativo"
TextBox2.SetFocus
Exit Sub
End If 'questa
l'istruzione per la domanda di conferma
Dim irisposta As Integer
irisposta = MsgBox("Confermi la registrazione" _
& " di " & TextBox2.Value & " ?", vbYesNo)
If irisposta = vbYes Then
Range("B1").Value = "-"
Range("B1").End(xlDown).Offset(1, 0).Select
ActiveCell.Value = TextBox2
ActiveCell.Offset(0, 1).Value = TextBox3
ActiveCell.Offset(0, 2).Value = TextBox4
ActiveCell.Offset(0, 3).Value = TextBox5
ActiveCell.Offset(0, 4).Value = TextBox6
ActiveCell.Offset(0, 5).Value = TextBox7
MsgBox "Ok Capo, eseguito!"
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
TextBox6 = ""
TextBox7 = ""
End If
End Sub |
Ad ogni click sul
pulsante assegnato, ripeteremo la stessa procedura.
Passiamo ad esaminare le istruzioni legate al pulsante "Cerca".
Ho inserito due OptionButton (pulsanti di
opzione), che ci consentono di scegliere, selezionando uno dei due Optbutton,
se vorremo una ricerca basata sull'
esatto nome che scriveremo nella textbox sottostante (la textbox1),
oppure una ricerca basata su parte del nome che
andremo cercando: se scriveremo "ci"
verranno trovati tutti quei nomi in cui appare la coppia di lettere "ci",
siano posizionate all'inizio o all'interno del nome cercato (per esempio:
Ciccillo Gaetano oppure Romboni Placido,
ecc.). Ho usato quindi due istruzioni diverse, anche per mostrare che i
cicli di ricerca si possono impostare usando diverse modalità di
programmazione, ogni procedura legata alla selezione di un opzione. Vediamo
quindi la procedura legata al primo pulsante di opzione, quella che ci trova
il dato se avremo scritto il nome esatto. Poichè, credo, risulterà nel
tempo, difficile ricordarsi se avremo registrato un nominativo usando
lettere maiuscole o minuscole, (la ricerca sarebbe
CaseSensitive, cioè riconosce le lettere e se non sono scritte
uguali non vengono identificate : Ennius è
diverso da ennius) ho usato in questo caso la
funzione Option Compare Text che ci
consente di riconoscere una parola indipendentemente dalle
maiuscole/minuscole usate. Solo che questa funzione, NON può lavorare al di
fuori di un "Modulo" (vba), cioè esterno alle
istruzioni che invece sono posizionate tutte nella UserForm. Per cui è
sufficiente inserire un modulo nel progetto, e nella zona "Dichiarazioni"
- "Generale" del modulo stesso, inserire la
funzione, e subito sotto, posizionare la routine (assegnandoli un nome) su
cui agirà la funzione Option Compare Text.
Sulla Userform, nell'evento Click del pulsante "Cerca",
sarà sufficiente richiamare detta routine semplicemente scrivendo il nome
della routine stessa. Ci penserà il codice, ad andarsi a leggere le
istruzioni di questa routine, (sotto), in verde sono le spiegazioni. Unica
precisazione: trattandosi di istruzioni contenute in un Modulo, per
identificare dove si trovano le TextBox, è necessario identificare
l'"oggetto" che le contiene, e quindi la riga d'istruzione và così
impostata:
UserForm1.TextBox2 = CL.Value .
(nelle istruzioni contenute nella userform, sarebbe stato
sufficiente
TextBox2 = CL.Value).
Questo è il codice inserito nel Modulo :
Option Compare Text
____________________________________________
Sub primo()
Dim CL As Object 'dichiarazione del tipo di
variabile per CL
For Each CL In Range("B3:B152")
'per ogni CL (cella) tra B3 e B152
Dim X As String 'dichiar. di var. per la X
X = UserForm1.TextBox1.Value
'la X sarà uguale al dato nella textbox1 che è il
'nominativo da cercare
If CL = X Then
'se la cella (CL) è uguale a X
CL.Select
'faccio selezionare (fermo il ciclo) questa cella
Y = CL.Value
'con Y prelevo il dato contenuto nella cella(CL)
'sotto: carico le textbox della userform con i
dati nella cella in quel momento selezionata, e 'nelle celle adiacenti,
sulla stessa riga, ma con "scarto" di una rispetto alla selezionata
UserForm1.TextBox2 = CL.Value
UserForm1.TextBox3 = CL.Offset(0, 1).Value
UserForm1.TextBox4 = CL.Offset(0, 2).Value
UserForm1.TextBox5 = CL.Offset(0, 3).Value
UserForm1.TextBox6 = CL.Offset(0, 4).Value
UserForm1.TextBox7 = CL.Offset(0, 5).Value
Dim irisposta As Integer
'Imposto la msgbox e relativa domanda
irisposta = MsgBox("Trovato " & Y & ". Vuoi fermarti ?", vbYesNo)
If irisposta = vbYes Then
'se rispondo SI allora
Exit For
'esco dal ciclo
End If
End If
Next CL
'altrimenti proseguo alla successiva cella
End Sub |
Ed ora vediamo
tutto il codice inserito nel CommandButton1_Click. Cominciamo con le
spiegazioni: intanto viene inserito il controllo se la textbox1,
quella che dovrà contenere la parola da cercare, sarà vuota, allora
avvisa con un messaggio, pone il focus sella textbox1, ed esce dal ciclo
senza continuare l'esecuzione delle istruzioni sottostanti, in caso contenga
del testo, avvia l'esecuzione del codice sottostante
Private Sub
CommandButton1_Click()
'controlla che la textbox1
contenga dati
If TextBox1 = "" Then
MsgBox "Inserisci Nominativo da cercare"
TextBox1.SetFocus
Exit Sub
End If
'se la textbox1 contiene
dati, controlla quale optionbutton è attiva (selezionata).La
'proprietà che attiva la optionbutton è la proprietà Value, che dovrà
essere impostata a 'True (di default è impostata a False). In questo
esercizio, ho impostato a True questa 'proprietà, per avere un opzione
attivata all'avvio della userform.
If OptionButton1.Value = True Then 'quindi,
se è attiva la optionbutton1
'allora chiama ed esegue
la sub "primo" contenuta nel modulo
primo
End If
'se invece sarà la optionbutton2 ad
essere attiva, esegue queste altre istruzioni:
If OptionButton2.Value = True Then
'con il Foglio1, nella
colonna B da B3 a B150 (zona su cui avviene la ricerca)
With Worksheets(1).Range("B3:B150")
Dim X As String 'dichiarazione del tipo
di variabile assegnata ad X
X = TextBox1.Value 'la X sarà uguale al
dato nella textbox1 che è il nominativo da 'cercare
'sotto: l'istruzione Set
serve ad assegnare a C il riferimento a cui accedere da parte del
'metodo Find (trova); cioè cercherà in C il valore rappresentato dalla
X, e lo cercherà 'secondo l'istruzione LookIn=xlValues che consente di
cercare dati che contengano il 'valore di X. E' questa istruzione che
consente di trovare una parola, digitando anche solo 'una parte di
essa. Se si volesse una ricerca esatta allora bisognerebbe completare
l'istruzione Find così: Find(X, LookIn:=xlValues, LookAt:=xlWhole)
Set C = .Find(X, LookIn:=xlValues) ',
LookAt:=xlWhole
'a questo punto C
corrisponderà alla prima cella corrispondente al valore cercato, se C corrisponde al valore cercato
If Not C Is Nothing Then
'viene memorizzato come
primo indirizzo il riferimento alla cella rappresentata da C
firstAddress = C.Address
'inizia
il ciclo Do....Loop. Le istruzioni Do...Loop consentono di eseguire un
blocco di 'istruzioni per un numero indefinito di volte. Le istruzioni
vengono ripetute fino a quando 'una condizione è True o fino a quando
non diventa True
Do
'se
la condizione cercata sarà True, cioè se viene rintracciata una cella
che corrisponde al 'valore cercato, allora questa cella viene
selezionata
C.Cells.Select
'ora il contenuto della
cella selezionata e di quelle adiacenti (scart) viene riportato nelle
textbox della userform, in modo che si possano visualizzare i dati
TextBox2 = C.Value
TextBox3 = C.Offset(0, 1).Value
TextBox4 = C.Offset(0, 2).Value
TextBox5 = C.Offset(0, 3).Value
TextBox6 = C.Offset(0, 4).Value
TextBox7 = C.Offset(0, 5).Value
Y = C.Value 'assegno a Y in valore
contenuto nella cella selezionata e interrompo il ciclo 'con una
msgbox di domanda
irisposta = MsgBox("Trovato " & Y & " . Vuoi fermarti ?", vbYesNo)
If irisposta = vbYes Then 'se rispondo SI
allora
GoTo 10 'esco dal ciclo andando a cercare
l'istruzione End With
End If
'in caso risponda NO,
continuo a cercare (FindNext(C))
Set C = .FindNext(C)
'sotto dico: gira (Loop)
fintantochè (While) C non viene trovato e le celle sono successive alla
prima (identificata con il riferimento firstAddress)
Loop While Not C Is Nothing And C.Address <> firstAddress
'nel caso la ricerca non abbia dato
esito, allora (Else) viene dato il messaggio
Else
MsgBox "Nome non Trovato"
End If
10:
End With 'finisce il ciclo
End If
End Sub |
Queste due
routine, quella assegnata all'optionbutton1 e quella assegnata
all'optionbutton2, sono simili nell'uso pratico, solo che la prima cercando
SOLO valori esatti, può essere utilizzata in tutti quei casi dove si voglia
la corrispondenza esatta e basta, per esempio nella ricerca di un codice
articolo, o di un numero. In questi casi, se i valori saranno solo numeri,
occorrerrà modificare la prima istruzione: intanto, sarà possibile inserirla
nella routine stessa attivata dal commandbotton1 (Cerca) e non in un modulo
(non ci sarà più la necessità di usare Option Compare Text), e andrà
posizionata al posto del richiamo alla Sub primo contenuta appunto nel
modulo), e poi, trattandosi di una ricerca Numerica, bisogna che il codice
sappia che il valore da cercare non sarà più un Testo,
ma un Numero, e adopereremo questa modifica
nell'assegnazione della variabile alla X
anzichè
Dim X As String 'dichiar.
di var. per la X
X = UserForm1.TextBox1.Value
'la X sarà uguale al dato nella textbox1 che è
ec.ecc.
si userà
Dim X
'dichiar. di var. per la X di tipo Variant
X = Val(TextBox1)
'la X sarà uguale al numero nella textbox1 che è
il
______________________________________________________
Ora vediamo rapidamente le istruzioni collegate agli altri pulsanti.
Condizione essenziale comune a due di queste routine seguenti, (Modificare o
Cancellare) è che tutte lavorano SE SI E' SVOLTA PRIMA UNA RICERCA, e quindi
la textbox2 conterrà dei dati (nominativo). Quindi le procedure seguenti si
potranno attivare SOLO se prima si è chiamato il record sul quale AGIRE,
(non avrebbe senso infatti, Modificare o Cancellare qualcosa che non si sia
prima evidenziato. Due spiegazioni:
Pulsante "Modifica".
Dato che avremo già una cella "Attiva" e di cui vedremo i dati
direttamente nelle textbox, queste istruzioni si limitano a "riscrivere"
nelle stesse celle, i valori che si trovano nelle textbox, compreso quei
valori che nel frattempo possiamo aver modificato.
Pulsante "Cancella
record" . Anche in questo caso vedremo già i dati nelle textbox
perchè "trovati" col pulsante "Cerca", e quindi avremo una cella attiva
sulla quale intervenire col metodo "Delete".
Faccio eliminare l'intera riga, compreso quindi la cella nella colonna A,
quella dove esiste il N° riga progressivo, e quindi ho inserito una
semplice istruzione, che cancella tutto il range della colonna A, poi
assegna il valore 1 alla prima cella (A3) e poi aggiunge 1 alle celle
seguenti.
Queste le due
routine, in sequenza:
Private Sub
CommandButton3_Click() 'Pulsante
"Modifica"
If TextBox2 = "" Then
MsgBox "Devi Cercare un nominativo per modificarlo"
Exit Sub
End If
ActiveCell.Value = TextBox2
ActiveCell.Offset(0, 1).Value = TextBox3
ActiveCell.Offset(0, 2).Value = TextBox4
ActiveCell.Offset(0, 3).Value = TextBox5
ActiveCell.Offset(0, 4).Value = TextBox6
ActiveCell.Offset(0, 5).Value = TextBox7
MsgBox "Ok! Eseguito!"
End Sub |
Private Sub
CommandButton4_Click() 'pulsante
"Cancella"
If TextBox2 = "" Then Exit Sub 'se
la textbox2 è vuota, esce dalla routine
'se la textbox2 contiene un
dato perchè ottenuto con la ricerca, allora seleziona la cella 'attiva
per confermare l'eliminazione dell'intera riga, facendo prima una
domanda
ActiveCell.Select
Dim irisposta As Integer
irisposta = MsgBox("Vuoi cancellare il Nominativo: " &
ActiveCell.Value & " ?", _ vbYesNo)
If irisposta = vbYes Then
ActiveCell.EntireRow.Delete
'poi pulisce la colonna A, seleziona la
cella A3, inserisce 1 e incrementa le celle sotto di 1 'fino alla fine
del range previsto (A3:A152)
Dim CA As Object
Range("A3:A152").ClearContents
Range("A3").Value = 1
For Each CA In Range("A3:A152")
If CA.Offset(1, 0) = "" Then
CA.Offset(1, 0) = CA + 1
End If
Next
End If
End Sub |
I pulsanti per la navigazione tra i record, sfruttano entrambi la
selezione di una cella, e con la
cella attiva, si muovono verso l'alto o verso il
basso sfruttando lo "scarto" (Offset)
verso l'alto (-1) o verso il basso (+1).
Per renderli funzionanti anche se non si è eseguita nessuna ricerca, faccio
selezionare all'apertura della UserForm, la cella B3, quella nella
quale si troverà il primo nominativo. Ho inserito due controlli perchè
avvisino e si fermino se saremo a inizio o a fine elenco.
questa l'istruzione nell'Initialize della UserForm:
Private Sub UserForm_Initialize()
Worksheets(1).Range("B3").Select
End Sub e
queste le istruzioni inserite nei pulsanti di navigazione:
Private Sub
CommandButton5_Click() 'pulsante per
"Scorri in Su"
If ActiveCell.Offset(-1, 0).Value = "Nominativo" Then
MsgBox "Siamo a inizio elenco, impossibile salire oltre"
Exit Sub
End If
ActiveCell.Offset(-1, 0).Select
TextBox2 = ActiveCell.Offset(0, 0).Value
TextBox3 = ActiveCell.Offset(0, 1).Value
TextBox4 = ActiveCell.Offset(0, 2).Value
TextBox5 = ActiveCell.Offset(0, 3).Value
TextBox6 = ActiveCell.Offset(0, 4).Value
TextBox7 = ActiveCell.Offset(0, 5).Value
End Sub |
Private Sub
CommandButton6_Click() 'pulsante per
"Scorri in Giù"
If ActiveCell.Offset(1, 0).Value = "" Then
MsgBox "Siamo a fine elenco, impossibile proseguire"
Exit Sub
End If
ActiveCell.Offset(1, 0).Select
TextBox2 = ActiveCell.Value
TextBox3 = ActiveCell.Offset(0, 1).Value
TextBox4 = ActiveCell.Offset(0, 2).Value
TextBox5 = ActiveCell.Offset(0, 3).Value
TextBox6 = ActiveCell.Offset(0, 4).Value
TextBox7 = ActiveCell.Offset(0, 5).Value
End Sub |
File consultabile
e scaricabile : MioDB2-2000.zip
28 Kb |