Depreszzív, húsvéti Exceles poszt

több, mint 80 országban futnak szerte a világban. Ezidáig nem találkoztam olyan problémával, aminél széttártam volna a kezem. A VBA interpreter telis-tele van hibákkal, de eleddig mindenre sikerült valamilyen workaroundot találni (nota bene: ha saccolnom kellene, nem kevés időt tenne ki a különböző fejlesztésekből az, hogy az interpreterben maradt bugokra gyógyírt találjak). Ma azonban találtam valamit, ami előtt jelenleg szét kell, hogy tárjam a kezem és azt kell, hogy mondjam a kedves ügyfélnek: sajnálom, ezt nem tudjuk implementálni. Utálok ilyet tenni, különös tekintettel arra, hogy a jelenlegi problémának egyszerűen nem szabadna léteznie. A feladat az volt, hogy egy cirka 1 millió cella méretű tábla kb. 10%-ban relatíve bonyolult képleteket kell generálni, majd miután az Excel elvégezte a számítási műveleteket, a cellákban szereplő képleteket le kell cserélni a kiszámolt értékekre. A tábla egy kb. 7 MB méretű dokumentumban lakik, a tábla teljes struktúráját és az abban levő összes képletet függvények generálják, a felhasznált adat jó részét szintén függvények importálják külső adatforrásokból, a végfelhasználó pusztán statikus számokat gépel bele a táblázat celláiba. A dolog egyszerűnek hangzik, mint egy faék – az is lenne, ha a mocsadék interpreterben nem bujkálna valahol egy ordas memleak. A képlet értékre cserélését VBA-ból többféleképpen is meg lehet oldani: az egyik művelet a clipboardot használja, kb. így:

srcRange.Copy
srcRange.Cells(1,1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Ebben az esetben a srcRange változó által mutatott Range tartalma megy a Clipboardba, majd a következő sorral a kiszámolt értékek visszakerülnek a képletek helyére. Az Application.CutCopyMode = False hivatott arra, hogy a Clipboardot felszabadítsa. A másik megoldás nem használ Clipboardot az adatcseréhez:
srcRange.value = srcRange.value
Mindezt elvégezzük egy ~2000 soros táblában, a sorokon egy ciklussal lépkedve, egy sorban ~500 cella tartalmát változtatva egyszerre. A gond ott jön, hogy ahogy halad a ciklus, a művelet látványosan lassul (annak ellenére, hogy a táblában levő képletek fokozatosan tűnnek el, tehát épp az ellenkezőjének kellene történnie), majd egy idő után az interpreter egyszerűen nem hajlandó az aktuális value paste műveletet végrehajtani és a kód futatása megáll, amit egy generic, semmire nem jó hibaüzenettel dokumentál a rohadék (1004 – Application defined or object defined error). Ha ezen a ponton fejlesztő kollégaként úgy éreznéd, hogy kommentálnod kéne a dolgot, mert mondjuk nagy eséllyel nem szabadítok fel valami változót a ciklus belsejében és az nyeli a memóriát, akkor kérlek ne tedd: minden lehetséges megoldást kipróbáltam már, fejlesztői oldalról a memleak esélye jelen esetben teljesen kizárt. Az teljesen nyilvánvaló, hogy a képletek értékre cserélése felzabál egy erősen limitáltan rendelkezésre álló RAM-ot (HEAP, STACK?), ami 2014-ben egy 2010-es szoftvert használva azért elég vicces (a kódot futtató gépben jelenleg 32 GB RAM van, mi meg egymillió darab lebegőpontos számmal dolgozunk). Cirka 7 óra knowledge base bújás és egyéb keresés után sem tudok olyan megoldást, ami a paste value művelet elvégzése után felszabadítaná a művelet által elemésztett RAM-ot, ezért ordas memleakre gyanakszom. Ez a poszt így ahogy van, elmegy a Microsoft Tech Supportnak, de sajna túl sok reményt nem fűzök ahhoz, hogy bármi érdemi választ kapjak (=sok rossz tapasztalatom volt korábban). Ettől függetlenül a lepényhal megy elöl, a remény hal meg utoljára, szóval ha érdemi válasz jön, azzal nyilván kiegészítem a posztot, hadd legyen még egy 100%-ig elégedett ügyfelem.]]>

15 thoughts on “Depreszzív, húsvéti Exceles poszt

  1. ern0

    Inkább csak gondolatébresztőnek, mivel ugye soha nem VBA-ztam: próbáld ki, hogy nem magukba a target cellákba pastolsz, hanem $a$1-be, és onnan vmi VBA művelettel teszed a helyére a cuccot, mint értéket. Ezzel mindössze az a baj, hogy pont nem fognak működni a képletek, viszont választ kapsz arra, hogy a sok helyre paste zavarja vagy a sokszori paste.

    Reply
    1. eFi Post author

      Már megvolt sajna, minden elképzelhetőt kipróbáltam. Amíg egy targetbe pasztálsz, addig láthatóan nem fogy a RAM. A probléma azzal van, hogy ez nem megoldás: hiába másolod a range value verzióját mindig ugyanoda, a value adat visszamozgatása a saját helyére újból egy olyan művelet, amitől lefossa a falat.

      Reply
  2. Gábor

    Én kipróbálnám az újraindítást egy kis csavarral:
    1. X%-át a képleteknek lecserélni (mondjuk X=20)
    2. Menteni munkafüzetet + restart infót perzisztens módon
    3. Exit Excel
    4. Restart Excel, munkafüzet megnyit, restart infó beolvas
    5. következő X% konvertál
    6. if (X<100%) THEN GOTO 2 ELSE "ÖRÜL"
    Mindezt valami batch/shell scripttel automatizálva, PiciPuhát csókoltatva.
    Hmm?

    Reply
    1. eFi Post author

      Kötegelve természetesen megy a dolog, de ez nem az az ügyfél, aki bohóckodni szeretne az alkalmazással. Azt senkinek nem tudod elmagyarázni, hogy 2014-ben egy mai irodai gépen hogy létezik az, hogy azért kell saját magát szivatnia, mert az interpreter elfogyaszt valami RAM-ot, miközben ciklikusan ciklusonként párszáz lebegőpontos számmal játszik.

      Reply
  3. Gál Gergely

    Efi, két kérdés:
    – miért nem adatbáziskezelőt használnak?
    – a Ctrl-A – Copy – Paste Special As Value to A1 megoldáa nem műxik?

    Reply
  4. eFi Post author

    Az adatbáziskezelő témát inkább hagyjuk, a user vezérelte copy-paste módszer pedig nem megoldás, mivel egy motor legyárt kb. 200ezer szanaszét levő cellában buzi nagy képleteket, amelyeknek a kiszámolt eredményét ugyanezekbe a cellákba vissza kell rámolni, mindezt úgy, hogy sok másik cellában meg meg kell maradnia az ott levő képleteknek.
    Az egész rohadás percek alatt lefutNA, ha nem bujkálna az interpreterben valami ordas memleak, illetve ha végre ki tudnám találni, hogy hogy a rákban tudom felszabadítani azt az eltűnő RAM-ot.

    Reply
  5. Zoli

    Hello,
    Mi lenne ha a clipboard helyett más módszerrel másolnál? Pl.:
    Sheets(“Sheet1”).Range(“D1:D5”) = Sheets(“Sheet2”).Range(“H1:H5”).Value
    A clipboard akkor ürül ha az alkalmazást bezárod. A cutcopymode sajnos nem törli a tartalmat.
    -Zoli

    Reply
  6. eFi Post author

    Zoli: amit írsz, az ugyanaz, mint a posztban emlegetett srcRange.value = srcRange.value (a .value a range object default propertyje). Soha eszemben nincs range copyhoz clipboardot használni pont a fent emlegetett okok miatt, itt csak kipróbáltam, még a fekete kakas teliholdkor történő áldozása előtt – hiába.

    Reply
  7. Tibor

    Es ahhoz mit szolsz, hogy
    * osszes szukseges adatot beker egy vagy tobb sheetbe
    * sheet bemasol Array()-be (esetleg fuggvenyestul)
    * osszes muveletet elvegez az Array()-en belul (a cellak egymasra hivatkozasara kell irni egy kis interpretert, de a Te szinteden ez 2 perc 🙂
    * visszamasol egy sheetbe
    ?

    Reply
  8. eFi Post author

    Beküldtem mindenféle techsupportoknak, megírtam szépen angolul, csináltam mellé kibelezett mintafile-t, de szerintem soha senki nem olvasta el, mert semmiféle feedback nem jött idáig.
    Nem is számítottam nagyon másra – ha emlékszel még, anno a 2003-as Excel verziójával csatáztam velük egy nagyot. Akkor az analysis toolpak VBA add-inben az egyik fejlesztő benne felejtett egy Debug.Print utasítást, ami fosta a VBA konzolra a szöveget és egyrészt baromi lassú volt, másrészt eléggé ellehetetlenítette a munkát. Az add-in aláírt és password protected volt, kinyitni ugyan ki tudtam, de onnantól elveszett a tanusítvány. Írtam nekik, hogy mi a baja – semmi válasz. Írtam még egyszer, hogy megcsináltam, itt az add-in, publikálják már, ne szívjon vele más – semmi válasz. Írtam harmadszor, hogy engedelmükkel kiraknám a netre a patchelt verziót – na erre jött egy tizenoldalas jogi válasz, hogy mibe fog ez fájni nekem, ha én a szellemi terméküket ilyetén módon gyaláznám meg. Anno ott feladtam, a saját usereink, akik használták az add-int, megkapták a patchelt verziót én meg nem rugóztam tovább a dolgon.
    Most ugyanez lesz valószínűleg – a kuncsaft épp ma jön hozzám, hogy befejezzük a toolját. Jelenleg működik az eszköz, de úgy, hogy minden lépésnél naplózza az utolsó jól végrehajtott állapotot és újraindításkor az előző befejezettől folytatja. Undorító, de így megy.

    Reply

Leave a Reply

Your email address will not be published.