Fara multe reguli de formatare conditionata in fisierele cu macro

trucuri, sfaturi si alte idei pentru imbunatatirea lucrului cu Excel
IPP
Moderator
Moderator
Mesaje: 3901
Membru din: Mie Iul 29, 2009 7:26 am
Localitate: Cluj-Napoca

Fara multe reguli de formatare conditionata in fisierele cu macro

Mesaj de IPP » Joi Noi 14, 2019 12:23 pm

Am stat sa ma gandesc daca are rost sa pun acest subiect aici sau ca subiect de "rant" in sectiunea Excel 2016
Dvs. ce ati face daca ati fi facut un macro de creere a unui raport in Excel 2007 cu durata de executie sub 1 secunda pentru a vedea ca exact pe acelasi calculator, folosind Excel 2016 Home and Business (dar si pe unul cu Excel 365), acelasi fisier, acelasi raport rulat pe aceasi zi in, practic, aceleasi conditii (inclusiv numar de programe deschise in paralel) durata de executie ar fi.... 172,15 secunde?

Daca acest articol va va salva cel putin jumatate din numarul de ore pierdut de mine cautand explicatii si tot consider ca isi are loc in aceasta sectiune.

Putina (si incompleta teorie): Cu fiecare versiune lansata auzim sau citim despre imbunatatirile de care "neaparat avem nevoie". Una dintre ele este legata de faptul ca s-a inlocuit "algoritmul" de (re)calculare formule din fisier(e) valabil pana la Excel 2003 (inclusiv) in favoarea unuia nou si evident "mai bun" care sa micsoreze timpii de recalcularea formulelor din fisier.
Cand vine vorba de macro, suspendarea (re)calcularii automate pe durata procedurii se face cu instructiunea Application.Calculation=xlCalculationManual. Asta pana aflam ca de fapt sunt unele situatii in care ea nu produce niciun efect, de exemplu la inserare/stergere de randuri iar acest lucru se va rasfrange direct in durata crearii oricarui raport in care macro va face modificari ce se vor incadra in situatia semnalata din cauza multiplelor recalculari intermediare nedorite.

In poza atasata gasiti rezultatele (pentru masurarea duratei de executie am folosit codul de aici)

Testul pe Excel 2003 l-am facut dupa salvarea fisierului .xlsm in .xls si o minora modificare de cod legata de ultimul rand (inlocuire A1000000 in A65536). Pentru versiunile 2003 si 2010 s-a folosit un calculator, pentru 2007 si 2016/365 altul. Toate versiunile de Excel sunt pe 32 biti.

In fisierul meu original nu existau reguli de formatare conditionata deloc si pana in ziua de azi ma intreb cum a reusit utilizatorul sa ajunga la cele peste 9400. De fapt stiu, si e legat de ceea ce eu consider ca este bug (incepand cu versiunea 2007) dar lumea considera ca e "feature": daca exista o regula de formatare conditionata pe baza de formula intr-o celula si se doreste a fi copiata, la destinatie regula respectiva va fi adaugata celei/celor existente nu o va suprascrie.

Constatari:
-Pentru situatii "normale" (cazul de fata: o lista-sursa de 9 coloane si 11000 randuri din care in raport ajungea informatie de pe 4 coloane, formule putine si nepretentioase: sum, + si -; raportul presupunea stergerea randurilor pentru ziua anterioara, undeva intre 2 si 45 randuri si inserarea altora cf. necesarului pentru ziua de interes) Excel 2003 e imbatabil.
-Excel 2007 nu pare a fi prea afectat de recalculari nedorite in timpul rularii macro.
-de la Excel 2010 in sus situatia se schimba dramatic, lucru vizibil dupa ce am sters regulile de formatare conditionata din fisier si am rulat macro pentru exact acelasi raport.

Concluzie:
-Daca aveti de rulat macro intr-un fisier cu multe formule si multe reguli de formatare conditionata, Excel 2007 ar fi cea mai buna varianta in ceea ce priveste timpii de executie. Din pacate nu cred ca se mai gaseste de cumparat si nu mai beneficiaza de suport sau reclama.
-Pentru versiunile noi, in unele conditii, nici macar o varianta cu macro nu ajuta prea mult la obtinerea unui raport in timpi decenti daca aveti in fisierul respectiv multe reguli de formatare conditionate.

Solutii pentru situatia actuala? Cat mai putine reguli de formatare conditionata (de la caz la caz, colorarea s-ar putea face direct din codul macro) si/sau evitarea pe cat posibil a scrierii unui cod ce provoca (fara voia dvs. evident) recalculari intermediare in foaie.

IP
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

Indigo-ONE
Mesaje: 165
Membru din: Mar Dec 11, 2018 8:54 pm

Re: Fara multe reguli de formatare conditionata in fisierele cu macro

Mesaj de Indigo-ONE » Joi Noi 14, 2019 2:34 pm

E ceva ce nu inteleg...
-Pentru situatii "normale" (cazul de fata: o lista-sursa de 9 coloane si 11000 randuri din care in raport ajungea informatie de pe 3 coloane, formule putine si nepretentioase: sum, + si -; raportul presupunea stergerea randurilor pentru ziua anterioara, undeva intre 2 si 45 randuri si inserarea altora cf. necesarului pentru ziua de interes)
ar fi posibil daca formatarea este facuta in toata foaia, ceea ce ar fi o prostie.
Cu sau fara rulare cod VBA acel fisier ar "functiona" ca melcul...

1. daca se sterg randurile, se sterg si formatarile conditionate (cel putin in office 365 Insider)
2. cel mai bine ar trebui sterse toate coloanele de interes (sau toata foaia, daca nu mai sunt si alte date) , inainte de copierea datelor.
3. Aplicati aceeasi procedura ca in cazul cu acel TRIM, care spuneati odata, ca trebuie pus in formula si daca este necesar si daca nu.
Anulati din cod formatarea conditionata din rangeul/foaia unde se vor copia datele inainte de copierea datelor.
Cand vine vorba de macro, suspendarea (re)calcularii automate pe durata procedurii se face cu instructiunea Application.Calculation=xlCalculationManual. Asta pana aflam ca de fapt sunt unele situatii in care ea nu produce niciun efect, de exemplu la inserare/stergere de randuri iar acest lucru se va rasfrange direct in durata crearii oricarui raport in care macro va face modificari ce se vor incadra in situatia semnalata din cauza multiplelor recalculari intermediare nedorite.
Lucru perfect normal as spune eu, deoarece trebuiesc reactualizate formulele care s-ar afla in foaie si evident refacute calcule pentru orice posibila modificare.
Iar daca nu sunt formule in foaie atunci nu vad de ce a aparut aceea incetinire.


Stiu ca stii, dar nu stiu ce stii...

IPP
Moderator
Moderator
Mesaje: 3901
Membru din: Mie Iul 29, 2009 7:26 am
Localitate: Cluj-Napoca

Re: Fara multe reguli de formatare conditionata in fisierele cu macro

Mesaj de IPP » Joi Noi 14, 2019 3:26 pm

Buna ziua

nu am pus fisierul de test pentru ca are prea multe informatii confidentiale si nu am atat timp sa fac altul sau sa il modific pe cel existent astfel incat premisele sa ramana aceleasi.
Niste clarificari ce nu ar trebui sa afecteze concluzia ca regulile de formatare conditionata pot afecta puternic timpii de rulare a unui macro in anumite conditii.
Fisierul are o foaie sursa: acolo existau reguli de formatare conditionata ajunse sau multiplicate de niste utilizatori si niste formule simple de tipul =a+b-c. Pur si simplu le-am lasat asa doar pentru testare, daca se mai umbla acolo probabil acelea se multiplicau (nu se va mai intampla, sper, am vorbit deja cu utilizatorii iar cele existente le-am sters intre timp) + o foaie raport. Macro parcurgea lista sursa si daca intalnea data calendaristica de interes scria informatia aferenta acesteia in foaia raport.
-nu, in conditiile de mai sus lucratul normal in fisier nu a fost deloc afectat (cred ca stiu la ce va refereati: o multime mare de formule complicate + multe informatii, la modul de calcul automatic in fisier produce latente la operare pentru ca la orice modificare se produc recalculari si trebuie asteptat sa se termine; am si cazuri din acestea dar nu grave si nu aici)

Incerc sa raspund punctual:
1. stergerea de randuri respectiv inserarea (ambele operatiuni ca urmare a rularii macro) are loc exclusiv in foaia Raport unde nu au fost si nu vor fi deloc formatari conditionate
2. randurile din raportul vechi sunt sterse cu totul si inserat un numar de randuri egal cu cel necesar pentru informatia noua. Informatia noua nu vine cu copy paste ci executarii unor linii de cod de genul: Cells(myRw, 5) = cDataIn.Offset(0, 5) 'incasari
3. Poate nu m-am exprimat prea bine, generarea acelui raport nu are nicio legatura directa cu formatarea conditionata din fisier. Incercam sa atrag atentia ca, daca aceasta exista si este multa ea va afecta vrand-nevrand timpii de executie. Ca si alte formule, se pare.

Faptul ca trebuie refacute calculele, perfect de acord dar asta ar fi trebuit sa se intample la sfarsitul procedurii, odata cu instructiunea
Application.Calculation=xlCalculationAutomatic nu de fiecare data cand, in timpul procedurii sunt sterse/inserate randuri.

Multumesc pentru interes
IP

PS. Daca, in scop de test, am transformat toate formulele din foaia sursa in valori, in loc de o rulare (pe Excel 2016) de 173 secunde s-a ajuns la una de 152 secunde. Din punctul meu de vedere normalitatea ar fi trebuit sa fie acea 1 secunda de executie pentru ca, in timpul prelucrarii, macro nu intervine DELOC asupra niciunei celule (in sensul modificarii) care contribuie la un rezultat sau chiar contine, ea insasi, o formula. Deci de ce ar face o recalculare de formule mai ales ca si stergerea/inserarea de randuri nu afecteaza direct foaia sursa si nu exista nicio formula in foaia raport care sa aiba referinte in foaia-sursa, sunt doua foi practic independente?

Indigo-ONE
Mesaje: 165
Membru din: Mar Dec 11, 2018 8:54 pm

Re: Fara multe reguli de formatare conditionata in fisierele cu macro

Mesaj de Indigo-ONE » Joi Noi 14, 2019 6:20 pm

Pentru ca nu a fost foarte clara locatia din care rula codul VBA, eu am presupus ca acel cod se va rula din fisierul/foaia Raport. Si de aici toate explicatiile mele.

Totusi....
Daca codul ruleaza din foaia de unde se extrag datele, acolo s-ar fi putut observa, ca sunt multe formatari...
2. randurile din raportul vechi sunt sterse cu totul si inserat un numar de randuri egal cu cel necesar pentru informatia noua.
Se stie in avans cate randuri vor avea noile date?
Informatia noua nu vine cu copy paste ci executarii unor linii de cod de genul: Cells(myRw, 5) = cDataIn.Offset(0, 5) 'incasari
Mie mi se pare ca este acelasi lucru (doar ca se executa in registri diferiti)
PS. Daca, in scop de test, am transformat toate formulele din foaia sursa in valori
Formulele contau doar daca contineau functii volatile, sau daca inserarea de date afecteaza direct formulele.
Daca intro foaie sunt sute de formatari + ceva formule matriceale (nu multe, doar cateva) si/sau formule cu functii volatile acel fisier se va "misca" foarte greu.

PS oricand e loc pentru o dezbatere si loc pentru a invata mai mult. Nimeni nu-I perfect.


Stiu ca stii, dar nu stiu ce stii...

Scrie răspuns

Înapoi la “Tips and Tricks Excel”