Tag Archives: Excel

Napi Excel WTF

Alattomos kis hiba - Orsi találta tegnap délután:

Summa summarum, a 16 helyiérték hosszú numerikus adat végét az Excel angolosan levágja - sehol egy warning, egy "micsinálsz-hülye-nem-tudok-ekkorát", semmi. Ha egy szám (szerinte hibásan) szövegként kerül be egy cellába, azért bezzeg tud zöld warning háromszöggel rinyálni.

Ennek a bugnak az az igazi szépsége, hogy VBA kódból elő tudsz állítani egy cellában 16 jegyű numerikus tartalmat úgy, hogy a cella azt az adatot jeleníti meg, amit te beleírtál, DE ugyanakkor nem azzal számol, hanem a lecsapott utolsó digitű változattal! Tegnap ez úgy jött elő, hogy egy SUMIF lookup-hoz használta egy rutin a 16 jegyű számot és mivel volt több olyan cella a SUMIF lookup range-ben, amely 16 jegyű numerikus adatot tartalmazott és az első 15 jegyük megegyezett, a rohadék egy kalap alá vette mindet, dacára a 16. digit különbözőségének.

A fent leírt SUMIF hiszti megoldása persze egyszerű - numerikus adat helyett szövegként kell használni a cellában a hosszú numerikus értéket (ehhez elég egy ' karakter a cellatartalom elejére, vagy a cellaformátum Text állapotúvá kapcsolása). Nem is ezzel van a baj, hanem a módszerrel, ahogy a drágaságos Office alkalmazás a problémát kezeli.

Hogy dolgozzam így hatékonyan, idióták?!

A probléma

Amennyiben Excel 2007 feletti verziókban úgy akarsz conditional formattingot (=feltételes formázás) létrehozni, hogy az képlettel számolódik és a felhasznált képlet relatív hivatkozást tartalmaz, akkor a VBA engine hibásan az conditional formatting target range-ének első celláját használja fel _abszolút_ címzéssel a kért relatív címzés helyett.

A M$ féle "megoldás"

Ne használj relatív címzést. Köszi. Az nem tudom feltűnt-e az ominózus WORKAROUND írójának, hogy a spreadsheet egyik legnagyobb előnye a relatív címzés használata.

Mindezek után a probléma közérthetőbben, példával

Képzeld el, hogy az a feladat, hogy 15000 cellát kell ugyanazzal a képlettel számoló feltételes formázással ellátnod (a 15000 cella még "öcsi", bőven tudnak az userek ettől izmosabbat kérni). Nos, eddig ezt megtehetted úgy, hogy az összes cellára kiadtad a formázási utasítást, mostantól azonban szerintük ez úgy fasza, ha egyesével végigiteráltatod egy ciklussal minden egyes cellát és úgy "oldod meg" azt a problémát, amit az agyatlan codereik generáltak (ugyanis Excel 97-Excel 2007 között ez normálisan működött). Lehet találgatni, melyik mennyi időt vesz igénybe...

Charles Simonyi agyoncsapná a codereiteket ezért, ha látná, hogy mit műveltek! Van fogalmatok ott Redmondban arról, hogy ezzel mekkora bajt okoztok a már kész VBA alkalmazásokban, amelyek használatánál a szerencsétlen döntéshozók a conditional formatting által vizualizált inputra támaszkodnak?

Forr a vérem.

Update: pont ez a feladat jött szembe ma az ügyfélnél, gondoltam mérek egyet: 6610 cellán futott le a kétféle conditional formattinggal ugyanaz az algoritmus - íme a futási eredmény hh:mm:ss bontásban, hogy a redmondi srácok is értsék, mekkora szart kavartak.

Conditional formatting, range-re alkalmazva:

cf-on-range

Conditional formatting, ugyanarra a range-re cellánként iterálva:

cf-on-iterated-cells

VBA Color hiszti

Okos, offsetelős color kezelést kell csinálnom Excelben (az Excel és az "okos" color kezelés két külön fogalom, de ettől most tekintsünk el).

Elsőnek csinálunk egy Long típusú color adatot épeszű RGB-re felbontó függvényt:

Sub color2RGB(myColor As Long, RGBComponents() As Byte)
    RGBComponents(0) = (myColor And &HFF0000) \ &H10000
    RGBComponents(1) = (myColor And &HFF00&) \ &H100
    RGBComponents(2) = (myColor And &HFF&)
End Sub

A fenti függvény debilnek látszó részeihez némi magyarázat:

  • Azon hexa számok végén lakik &, akik max. 16 biten elférnek, de én 32 bitre szeretném őket erőltetni (=force 32 bit). A 16 bitnél hosszabb számok mögé is pakolhatsz &-t, de azt még az IDE legyilkolja.
  • A \ egy spéci osztás, ami az osztás eredményének egész részét adja vissza (=int(a/b))

Ezek után írjunk egy color offsetelőt, ami egy Long colort tud offsetelni:

Function offsetColor(myColor As Long, Optional R As Integer = 0, Optional G As Integer = 0, Optional B As Integer = 0) As Long
    Dim RGBComponents(2) As Byte
 
    Call color2RGB(myColor, RGBComponents())
 
    R = (R + RGBComponents(0)) Mod &HFF
    If R < 0 Then R = 0
 
    G = (G + RGBComponents(0)) Mod &HFF
    If G < 0 Then G = 0
 
    B = (B + RGBComponents(0)) Mod &HFF
    If B < 0 Then B = 0
 
    offsetColor = RGB(R, G, B)
End Function

Meg is volnánk, próbáljuk ki:

?Hex(offsetColor(myColor:=0, R:=1, G:=2, B:=3))
30201

A bolygón minden más programozási nyelven azt várnád, hogy 0x010203 lesz az eredmény - ehelyett a "drágaszág" RGB() függvény az R és a B értékeit láthatóan megcserélve tárolja. Nice.
Biztosra akartam menni, hogy nem én vagyok az idióta és megnéztem az Excel VBA helpet - íme a gyári példa:

MyObject.Color = RGB(255, 0, 0)    ' Set the Color property of MyObject to Red.

Értem én, hogy annak a Long-nak a struktúrája az engine belső magánügye, de könyörgöm, fiúk, ott Redmondban, mi a francért kell ezt másképp csinálni, mint az egész világ? Persze lehet, hogy az egész csak byte-sorrend kérdése, de már akar a franc belegondolni.