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

Calculul Consumului de Apă

Această aplicaţie este destinată calculul consumului lunar de apă rece pentru un bloc.

Variantă prezentată calculează consumul de apă rece, pentru un imobil cu 75 de apartamente cu maximum două contoare pe apartament.
Algoritmul se poate modifica foarte uşor pentru un alt număr de apartamente. 

Versiunea prezentată necesită Excel 2003 sau mai recent.

Scurtă prezentare

Fişierul Excel conţine 4 sheeturi:

contoare – conţine un tabel cu citirile lunare a apometrelor pe fiecare apartament

În afară de datele propriu-zise, are implementat, un sistem de filtrare (click pe săgeţile din colţul dreapta jos la capul de tabel) care permit selecţia după an, lună, apartament.

Acest tabel cu citiri este completat automat de program.

lista – conţine un tabel cu numărul apartamentului şi cu numele proprietarului
Se completează la început sau în caz de modificare.

fisa – este pagina cu care lucrăm

Cred că din imaginea este suficient de ilustrativă.
În A2 se completează  numărul de apartamente a imobilului. Varianta actuală permite maximum 200 de apartamente.

Evident trebuie să fie concordanţă între numărul de apartamente şi conţinutul din lista.

În celula E1, este stocat numărul de înregistrări din contoare, a NU se modifica.

Utilizatorul poate modifica numărul apartamentului, luna şi anul, respectiv poate adăuga un rând nou la tabelul cu indexul contoarelor.

 

Funcţionare

Vizualizare situaţie consumuri pe apartament

După ce am introdus în A1 numărul apartamentului , pentru a vizualiza fişa de consum avem următoarele două posibilităţi, apăsăm butonul Vizualizare sau facem dublu click pe celula A1 sau A2.

Programul nu ne lasă să introducem un număr mai mare pentru numărul apartamentului decât cel maxim stabilit.

Înregistrare consum

În A1, introducem 1 (numărul primului apartament), completăm luna şi anul, facem click pe Vizualizare ca să avem fişa pentru primul apartament, în rândul corespunzător  lunii completăm indexul curent a contoarelor. Pentru a înregistra efectiv consumul avem doua posibilităţi, apăsăm butonul Inregistrare sau  după ce am completat indexul contoarelor  cu săgeta dreapta sau cu TAB facem două mişcări spre dreapta, ca să ajungem în coloana E.
A doua variantă pare mai rapidă.

Programul nu permite înregistrarea de indecşi mai mici, decât cei din luna anterioară.
Indiferent ce variantă am ales, indexul contoarelor o să fie înregistrat în contoare, numărul apartamentului se va incrementa, o să fie încărcate datele existente pentru acest apartament,
şi de aici reluăm introducerea indexurilor.

Corectarea eventualelor greşeli de introducere, mergem în pagina contoare facem o filtrare după numărul apartamentului şi corectăm manual.

Prin apăsarea butonului Calcul Consum programul face calculul consumului  pentru luna curentă.

consum - aici sunt  calculate consumurile pentru  întregul bloc.

 

Ce am folosit din Excel

Am folosit funcţii Excel numai în pagina fisa.
În celula B1 am folosit funcţia VLOOKUP

Această funcţie permite căutarea într-un tabel  după o cheie şi are următoarea sintaxă

VLOOKUP(continut_cautat; tabel_sursa; coloana;0)
continut_cautat               cheia de căutare după care căutam in tabel
tabel_sursa                       locul tabelului în care căutăm, cheia de căutare trebuie să fie în prima coloană
coloana                                               coloana din care se introduce rezultatul

În cazul nostru:
=VLOOKUP(A1;lista!A1:B200;2;0)
căutăm după celula A1, numărul apartamentului, în tabelul din lista zona  A1:B200 , şi se va întoarce conţinutul coloanei a doua unde Excelul în prima coloană va găsi numărul apartamentului indicat de noi

În coloana D avem următoarea funcţie

 

Această permite calculul consumului pentru amândouă contoare dar numai în cazul dacă conţinutul coloanei B este completat

În coloanele E şi F avem următoarele formule

 

Formulele lucrează numai dacă celulele din coloana B sau C sunt completate şi ne dă un mesaj de eroare dacă indicele din luna această ar fi mai mic decât cel din luna anterioară.

Celelalte funcţiuni sunt realizate cu VBA, la acestea o să revin intr-o postare următoare.


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