Calculul Consumului de Apă 2 - VBA

Elemente de comandă

Programul nostru este comandat de câteva butoane:

    - buton pentru anulare filtrare în sheet-ul contoare,

    - 3 butoane           Vizualizare         -              vizualizare date înregistrate pentru un apartament
                                Înregistrare       -              înregistrarea consumului actual
                                Calcul Consum -              calcul consum lunar pentru întreg bloc

În afară de aceste butoane, mai sunt două zone de comandă „invizibile”:

-          dacă facem DoubleClick în zona A1:A2 , obţinem acelaşi efect ca prin apăsarea butonului Vizualizare

-          în timpul înregistrării consumului, dacă folosim TAB-ul sau săgeata dreaptă şi după ce am introdus cele două contoare ajungem cu în coloana E, ajungerea în această coloană face o comandă identică cu apăsarea Înregistrare

În f – fisa avem cele două subrutine prin care DoubleClick-ul  respectiv selectarea colanei E activează rutina formareFisa respectiv adaugConsum.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'       vizualizare fisa
If ActiveCell.Column = 1 And ActiveCell.Row = 1 Then formareFisa
If ActiveCell.Column = 1 And ActiveCell.Row = 2 Then formareFisa
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'       comanda pentru adaug consum
If ActiveCell.Column = 5 And ActiveCell.Row > 5 Then adaugConsum
End Sub

 La prima procedură evenimentul care comandă acţiunea este click dublu, la a două schimbarea selecţiei (zonei active).
Evenimentele  pornesc automat procedurile şi dacă sunt indeplinite condiţiile specificate sunt pornite procedurile formareFisa respectiv adaugConsum.

Celelalte rutine sunt grupate în două module inregistrareConsum şi calculConsum.

inregistrareConsum

În prima linie declarăm a parte din variabile publice, adică sunt accesibile în toate procedurile din toate modulele şi prin ele se transmit valorile între rutine.

formareFisa  - este rutina cadru prin care se vizualizează datele înregistrate pentru un apartament, în afara de sortarea tabelului care conţine indexurile contoarelor pentru fiecare apartament restul instrucţiunilor sunt apelări de subrutine.
Observaţie: Application.ScreenUpdating = False opreşte actualizarea ecranului, prin această creşte viteza de execuţie semnificativ.
Pot să scriu mai multe instrucţiuni intr-un singur rând dacă le separ cu    : 

cautContoare – caută indexurile contoarelor pentru apartamentul specificat şi le scrie în variabila apCont
Observaţie:   instrucţiunea  Debug.Print  permite vizualizarea în timpul execuţie  a unor variabile din program, la fiecare trecere printr-o astfel de instrucţiune este „printat” in Immediate Window valoarea momentană a variabilelor. Această ne permite să testăm rutina noastră în timpul încercărilor. După ce am pus la punct rutina cu    '     transformăm instrucţiunea intr-un comentariu care nu este executabil. Dacă avem probleme cu funcţionarea programului putem transformăm din comentariu înapoi în instrucţiune executabilă.

scriuFisa  - este afişată conţinutul indexurilor în sheet-ul fisa

anulareFiltru  - anulează eventualele filtrări făcute de utilizator în pagina contoare

adaugConsum  - rutină adaugă în tabelul din sheetul contoare situaţia actuală a contoarelor

Rutina afişează mesaje de eroare dacă indexul introdus este mai mic decât cel din luna anterioară sau dacă pentru apartamentul selectat există deja înregistrare pentru luna curentă.

Dacă nu există erori atunci înscrie indecşi în sheet-ul contoare după care incrementează numărul apartamentului şi afişează datele pentru acest apartament.

testUnic – verifică dacă pentru apartamentul ales există deja înregistrarea contoarelor pentru luna curentă. (conTrol =0 nu există, =1 există)

calculConsum – cum spune şi denumirea face calculul consumului lunar

După o resetare a matricei în care o să fie înregistrată consumul  se face o sortare a tabelului în care se află indecşi. Operaţie se face pentru a avea în rânduri succesive indecşi pentru un apartament pentru luni succesive.

For i = 0 To 200: For j = 0 To 7: coNsum(i, j) = "": Next: Next
c.Select: Columns("A:E").Select  'sortare apart, an,luna,
Selection.sort Key1:=Range("c2"), Order1:=xlAscending, Key2:=Range("a2"), Order2:=xlAscending, Key3:=Range("b2"), Order3:=xlAscending, Header:=xlYes

Printr-un ciclu Do While se calculează consumurile propriu-zise pentru fiecare apartament.

După calculul consumului lunar această este afişată în consum.

Observaţii

 

În acest blog încerc să vă prezint câteva aplicaţii făcute cu VBA, dar nu o să intru în detalii despre parte teoretică a acestui limbaj, ci o să încerc să vă sugerez câteva tehnici de programare care v-ar putea ajuta.

Pentru partea teoretică a VBA vă rog să vizitaţi:http://invatamvba.blogspot.ro/

Structura programului după cum cred că aţi observat încerc să folosesc pentru fiecare funcţie a programului o rutină separată, chiar dacă această uneori duce la câteva linii de cod suplimentare. De ce? E mult mai uşor de testat. Funcţionarea câtorva instrucţiuni este mult conceput şi verificat, decât câteva sute de linii de cod.

Sintaxa folosită la cuvinte cheie: nume de rutină, variabile – este util să folosiţi denumiri care sugerează parţial funcţia, conţinutul lor. Mult mai sugestiv este calculConsum decât subroutine32 sau coNsum(i, j) decât a(i,j). La aceste denumiri folosesc caractere mici, dar în majoritatea cuvintelor cheie am şi un caracter scris cu litere mari. Mă ajută să verific dacă nu am făcut eroare de introducere caractere. Textul cod scriu evident cu caractere mici, dar facă anterior am declarat o variabilă prin Public sau Dim atunci după ce am terminat de introdus linia VBE automat îmi corectează cuvintele cheie după forma în care am declarat. Evident pentru VBA faptul că scriu o denumire cu litere mici sau mari este complet indiferent.

Pentru variabilele de ciclare (în For..Next sau Do While… ) folosesc literele i, j, k sau o combinaţie a lor.

Indentare – in scrierea codului sursă prefer formă în care  fiecare ciclu sau if succesiv este cu un spaţiu TAB mai la dreapta

 

Scris în acest mod este mult mai uşor de parcurs un program  şi se vede dacă setul de instrucţiuni este sau nu „închis”.

Comentarii – este bine să vă obişnuiţi în a scrie câteva cuvinte de comentariu în rutine. Este foarte util în cazul dacă vă foloseşte şi altcineva programul dar şi în ipoteza că aţi făcut un program f. bun şi peste vreo doi-trei ani vreţi să modificaţi ceva. Să ştiţi că fără comentarii şi fără cuvinte cheie adecvate o să  ia un timp până o să înţelegeţi cum funcționează  programul pe care Dvs aţi scris, nici nu demult.

Debug.Print  despre care am scris şi mai sus, dar reiau încă odată, consider extrem de utilă mai ales în timpul punerii la punct a aplicaţiei.

Transferul de informaţii intre proceduri – Personal prefer sa transmit parametrii pentru proceduri prin variabile declarate Publice, in loc sa am proceduri gen subrutine sau funcţie cu argumente. După părerea mea în acest mod sunt definite fără echivoc denumirile precum şi conţinutul lor. Acelaşi denumire însemnă totdeauna acelaşi varibilă. Dacă foloseam modul de transmisie a parametrilor prin argumente,  era permis să am în procedura apelantă  variabila numită xx, iar în procedura apelată să am zz, care uneori poate da naştere la confuzii.

Din punctul  de vedere  a VBA următoarele sunt perfect corecte:

   Sub ceva(a1,a2,a3)
    …….

   End Sub

   ……
   ceva b1,b2,b3
    ……

De să fac în Excel şi ce în VBA?

Este o întrebare bună şi la care nu se poate da un răspuns foarte  clar.  Evident depinde de experienţa fiecăruia atât în Excel, cât şi in VBA.
Dacă programul este folosit şi de persoane mai puţin avizate în d’ale calculatoarelor este recomandat să folosim mai mult VBA şi mai puţin formule Excel. Formulele Excel, pentru care poate ne am chinuit mult, pot să fie şterse foarte uşor de o persoană neavizată, doar prin câteva clickuri bine plasate. VBA este mult mai greu de şters. În schimb anumite operaţii, cum este de ex. formatarea este mult mai uşor de făcut în Excel decât să scrii codul. Anumite funcţii de ex. radicalul nu sunt implementat la nivel VBA, Excelul în schimb le are.

Fişierul Excel se poate descărca de aici:http://www.fileshare.ro/e29733265