Category Archives: Excel

Az Excel és a sötétben bújkáló external referenciák

Ha egy tábla megnyitásakor kapsz egy popupot, ami azt kérdi tőled, hogy frissítenéd-e a táblában lakó külső file-okra mutató hivatkozásokat, ám te nem emlékszel arra, hogy külső hivatkozást definiáltál volna és a "[*]" keresés sem talál egyetlen cellában sem ilyet, de a

Sub getLinks()
    Dim myLinks As Variant, i as Long
 
    myLinks = ThisWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(myLinks) Then
        For i = LBound(myLinks) To UBound(myLinks)
            Debug.Print myLinks(i)
        Next i
    End If
End Sub

mégiscsak mutat külső hivatkozás(oka)t, és az Edit Linksben hiába nyomsz a Break Linkre, akkor sem szűnik meg a referencia, akkor gondolj arra, hogy a cellák validálásakor maradhatott bent kívülről jövő copy-paste során valami kiirthatatlannak látszó szemét, amit azonban a

Sub killValidations()
    Dim S as Worksheet
 
    For Each S in ThisWorkbook.Worksheets
        S.UsedRange.Validation.Delete
    Next S
End Sub

majd kidob (az összes validationnel egyetemben).

Mese az Excelről és a context menüről

TL;DR: 2010-es Excelnél újabb verzióban soha ne használd a Workbook_SheetBeforeRightClick() event handlert arra, hogy saját CommandBart hozz létre - helyette ott a Custom UI editor for Microsoft Office, amivel ugyan csak bedrótozott hierarchiát tudsz csinálni, viszont cserébe nem fagy majd random szénné alatta a host környezet.

A dolog úgy indult, hogy egy Office update után elkezdett furán viselkedni a VBA kód pár táblában. Jó szokás szerint a hibajelenségnek semmi köze nem volt a hiba okához, a Microsoftos alkalmazásnak pedig esze ágában sem volt bármiféle notificationt küldeni, hogy neki XY kóddarabbal lenne baja, sokkal inkább a számukra már sokszor bevált utat választba, nemes egyszerűséggel vagy rommá fagyasztotta a futtató környezetet, vagy a teljes Excel UI elfelejtett a végfelhasználóval kommunikálni. Mutatom:

Excel 2010+ event handler madness from Gabor Penoff on Vimeo.

A videóban jól látszik, hogy a testData tábla kiválasztása után az megnyílik, majd az Excel előbb mintha elfelejtené a screent update-elni, később viszont teljesen meggárgyul és a saját UI elemei is megszűnnek működni.

Nézzük végig, mi történik belül. A 2. kattintásra a standard context menünek kellene előbújnia, azonban én ezt elkapom és a saját cuccaimat pakolom a gyári menü helyére. Mindez úgy történik, hogy a Workbook_SheetBeforeRightClick() event handlert használom:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)
    rightClickMenuShow (rightClickMenuTitle)
    Cancel = RightClickCancel
End Sub

A rightClikMenuShow() törli az előző custom menüt, majd újra létrehozza és megjeleníti:

Sub rightClickMenuShow(rightClickMenuName As String)
    RightClickCancel = True
    Call rightClickMenuDelete(rightClickMenuName)
    Call rightClickMenuCreate(ActiveSheet)
    CommandBars(rightClickMenuTitle).ShowPopup
End Sub

A törlés így néz ki:

Sub rightClickMenuDelete(rightClickMenuName As String)
    Dim bar As CommandBar
 
    For Each bar In CommandBars
        If UCase(Trim(bar.Name)) = UCase(Trim(rightClickMenuTitle)) Then bar.Delete
    Next bar
End Sub

Az új menü létrehozása pedig így:

Sub rightClickMenuCreate(S_Caller As Worksheet)
    Dim rightClickMenuName As String
    Dim rightClickMenuIndex As Long
 
    rightClickMenuName = rightClickMenuTitle
    Call rightClickMenuDelete(rightClickMenuName)
    Application.CommandBars.Add Name:=rightClickMenuName, Position:=msoBarPopup
 
    rightClickMenuIndex = 0
    rightClickMenuIndex = rightClickMenuIndex + 1
    CommandBars(rightClickMenuName).Controls.Add Type:=msoControlButton, Before:=rightClickMenuIndex
    With CommandBars(rightClickMenuName).Controls(rightClickMenuIndex)
        .FaceId = 263
        .Style = msoButtonIconAndCaption
        .Caption = "Excel's &Default Shortcut Menu"
        .OnAction = "ShowDefaultRightClickMenu"
    End With
 
    rightClickMenuIndex = rightClickMenuIndex + 1
    CommandBars(rightClickMenuName).Controls.Add Type:=msoControlButton, Before:=rightClickMenuIndex
    With CommandBars(rightClickMenuName).Controls(rightClickMenuIndex)
        .Style = msoButtonCaption
        .Caption = "Freeze me please"
        .OnAction = "myTestSub"
        .BeginGroup = True
    End With
End Sub

Röviden ennyi a jobb klikkes menüből a myTestSub() makrót elindító event handler mechanizmus. Miután ebben hozzá nem nyúlok a képernyő frissítését szabályozó Application.ScreenUpdating metódushoz, nézzük meg, mit csinál a myTestSub():

Sub myTestSub()
    Dim f As Variant
 
    f = Application.GetOpenFilename
    If f = False Then
        MsgBox "No file selected."
    Else
        Workbooks.Open Filename:=f, ReadOnly:=True
    End If
End Sub

Láthatóan ennek sincs köze a ScreenUpdating-hez, mégis elpusztul tőle az Excel.
Miután a probléma nyilván nem egy ilyen ~20 soros toolban jött elő, pár órát elvett az életemből mire rájöttem, hogy az 2010-es verziónál frissebb Exceleknek azzal van baja, ahogyan én a right click menüt újradefiniálom a Workbook_SheetBeforeRightClick() event handlerben egy CommandBars(rightClickMenuTitle).ShowPopup() hívással. A helyes megoldás ilyenkor az, hogy ezt a popup generátort úgy ahogy van kidobod és létrehozol egy XML-t, ami a fenti két eljárás meghívásához legyártja majd az UI eleme(ke)t a jobb klikkre megjelenő context menüben:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <contextMenus>
        <contextMenu idMso="ContextMenuCell">
            <menu id="appMenu" label="engine" insertBeforeMso="Cut">
                <button id="item_id1" label="(Do not) freeze me please" onAction="myTestSub"/>
            </menu>
        </contextMenu>
    </contextMenus>
</customUI>

Ebben az esetben azonban ne felejtsd el, hogy fel kell készíteni a hívott rutinodat az IRibbonControl paraméter fogadására:

Sub myTestSub(control As IRibbonControl)

Tanulság nincs, csak ledokumentáltam 4 óra őrjöngés után a megoldást.

Excel VBA: Application.Evaluate – Error (2015|2029)

Ha legközelebb egy formulát akarsz kiértékeltetni a VBA engine Application.Evaluate() metódusával és a rohadék a nem túl beszédes "Error 2015" vagy "Error 2029" stringeket adná vissza cserébe, akkor ne a formulákban keresd a hibát, hanem azt nézd meg, hogy a kiértékelés forrásaként használt képlet és az aktív workbook referenciastílusa megegyezik-e. Ha nem, akkor állítsd át az

Application.ReferenceStyle = (xlA1|xlR1C1)

értékadással és problem solved, a redmondi felelősök meg süllyedjenek el szégyenükben a gusztustalan workaroundjukkal együtt.

Depreszzív, húsvéti Exceles poszt

Úgy érzem, hogy mindent tudok az Office VBA környezetről, különös tekintettel arra, amit az Excel hátába ékelt runtime interpreterrel művelni lehet. ~19 éve dolgozom különbőző feladatokon, ugyanazt az IDE-t (és ugyanazt a 6.0 VB interpretert) használva. Pár perces munkáktól a 11 hónapnyi fejlesztésig volt ebben minden - az így megírt szoftverek a mai napig 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.

Excel VBA SpecialCells sucks

Furcsa bugot talált Orsi az egyik VBA motorban. Mutatom, úgy egyszerűbb lesz - íme a mintatábla releváns darabja:

sample-spreadsheet

Kérdezzük meg a VBA engine-t, hogy melyek azok a cellák, amelyek szerinte üresek az E4:F4 range-ben:

?Range("E4:F4").SpecialCells(xlCellTypeBlanks).Address
$E$4

Eddig rendben vagyunk. Most nézzük meg mit mondd, ha a vizsgálandó range-ünk csak egy cellát tartalmaz - legyen ez az E4:

?Range("E4:E4").SpecialCells(xlCellTypeBlanks).Address
$A$1:$C$2,$E$1:$E$2,$G$1:$G$2,$A$3:$B$3,$D$1:$D$3,$F$1:$F$3,$A$4:$C$4,$E$4,$G$4,$A$5:$B$5,$D$5,$F$5,$H$1:$H$5,$A$6:$H$15,$A$16:$G$16

Anyád, az. Tehát amennyiben a range egyetlen elemű, a .SpecialCells metódus a teljes táblát vizsgálja, nem pedig a szülőjének definiált egyetlen cellát.
Ebből az lesz, hogy minden egyes .SpecialCells ellenőrzésnél egy újabb taknyolós workaroundot kell hegeszteni.

Excel – this is how we roll

tiddle.co.uk:

I literally just fixed a bug that has plagued me for the last few days. I originally wrote the code using the terrible reference literature for Excel 03. The issue is actually that the index it tells you to use doesn’t exist! The text below is taken from the Borders object page, specifically relating to FormatConditions (the conditional formatting object) and is wrong:

Use Borders(index), where index identifies the border, to return a single Border object. The following example sets the color of the bottom border of cells A1:G1 to red.

Worksheets("Sheet1").Range("A1:G1"). _
Borders(xlEdgeBottom).Color = RGB(255, 0, 0)

Index can be one of the following XlBordersIndex constants: xlDiagonalDown, xlDiagonalUp, xlEdgeBottom, xlEdgeLeft, xlEdgeRight, or xlEdgeTop, xlInsideHorizontal, or xlInsideVertical.

I was trying to apply formatting to the bottom border, so obviously I used xlEdgeBottom, and hence continually received the aforementioned runtime error. After what seems like hours of [non-sequential] Googling, I found a post where someone was doing something similar enough to me, successfully, to learn from it. Almost immediately I saw the difference; their chosen indexes did not contain “Edge”. I removed them from my code, and voila it works perfectly, all errors were gone and the sheet formats beautifully.

Ok, so I probably should have found a better reference to the object library, but the Microsoft online one is no better or easier to use than the offline one, which coincidently loads faster! On a similar note, if you’ve ever looked up an error code to find a bug solution page by Microsoft, you’ll know the wide range of issues and limitations of their software that intersect in very weird ways. Surprising the main application runs really! I hope someone finds this and it helps them, as otherwise I just wasted time documenting this !

Mutatom, hogy néznek ki az emlegetett konstansok:

?xlEdgeLeft, xlLeft
 7            -4131 
?xlEdgeTop, xlTop
 8            -4160 
?xlEdgeRight, xlRight
 10           -4152 
?xlEdgeBottom, xlBottom
 9            -4107

Szerencsétlen flótás kétségbeesett próbálkozása valóban működőképes és segít az Edge attribútumoknál - persze az Inside és Diagonal konstansok továbbra sem működnek ilyen esetekben. Nagy eséllyel ugyanígy jó lesz az elkövetkező tizenX év Exceleiben is, ugyanis a VBA engine bugjai 1995 óta alig-alig tünedeznek el (2003 óta van az egyik lezárt és aláírt gyári Microsoft add-inben egy sor, amely minden egyes kalkulációnál debug üzenetet pakol az Immediate ablakba, hogy hadd örüljön a fejlesztő, illetve hadd lassuljon bármi, ami a bekapcsolt add-innel egyidőben jelen van az Excelben).

A "1004 Unable to set the LineStyle property of the Border class" hibaüzenet okára keresve egyébként számtalan olyan megoldási javaslat jön szembe, hogy valószínűleg korrupt az Excel file és csináld újra. Kérdem én: a táblával _dolgozó és nem játszó_ júzernek vajon az-e hobbija, hogy hetente korrumpál egy-egy ilyen állományt? Remélem Redmondban csuklanak.

A kis rohadék hiba azért alattomos, mert csak bizonyos range-ek esetén jön elő. Ennek az az oka, hogy csak azokra a cellákra "allergiás" a border propertyt beállító kód, amelyek már rendelkeznek valamilyen border propertyvel (ez az idióta hiba egyébként számos helyen fellelhető a VBA motorban). Érdemes ilyenkor reflexből propertyt resetelni, még ha erőforrás pazarlásnak tűnik is:

Sub setBorder(ByVal myRange As Range, ByVal borderArray As Variant)
    ' call setBorder(Selection, array(array(xlEdgeTop, xlthick, xlContinuous)))
    Dim i As Long, j As Long
 
    For i = LBound(borderArray) To UBound(borderArray)
        With myRange.Borders(borderArray(i)(0))
            .LineStyle = xlLineStyleNone
            .LineStyle = borderArray(i)(2)
            .Weight = borderArray(i)(1)
        End With
    Next i
End Sub

VBA turbo: findRangeRecursive()

Aaron Blood, az ozgrid.com Excel coder site fórumán postolta még 2004-ben az alábbi nagyszerű függvényt, melynek tech nyelven summázott dolga az, hogy egy tetszőleges forrásrange-ből filterezzen ki egy subRange-et a megadott matching pattern alapján:

Function findRange(findItem As Variant, searchRange As Range, Optional lookIn As Variant, Optional lookAt As Variant, Optional matchCase As Boolean) As Range
    Dim C As Range, firstAddress As String
 
    If IsMissing(lookIn) Then lookIn = xlValues 'xlFormulas
    If IsMissing(lookAt) Then lookAt = xlWhole ' xlPart
    If IsMissing(matchCase) Then matchCase = False
 
    With searchRange
        Set C = .Find( _
        What:=findItem, _
        lookIn:=lookIn, _
        lookAt:=lookAt, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        matchCase:=matchCase, _
        SearchFormat:=False)
        If Not C Is Nothing Then
            Set findRange = C
            firstAddress = C.Address
            Do
                Set findRange = Union(findRange, C)
                Set C = .FindNext(C)
            Loop While Not C Is Nothing And C.Address <> firstAddress
        End If
    End With
 
End Function

Ehhez kellett ma némi extra. A feladat az volt, hogy n darab párhuzamos range-ben kell egymás után keresni és a végén megtalált target range-et visszaadni. Magyarra fordítva ez kb. úgy fest egy példával illusztrálva, hogy "keressük meg azokat a sorokat egy táblában, ahol az A oszlopban 12 van, a B oszlopban 300, a C oszlopban meg mondjuk 'kisMukk'".
A megoldást a FindRangeRecursive() függvény szolgáltatja, mindenki fogyassza igénye szerint:

Function findRangeRecursive(findItems As Variant, searchRanges As Variant, RC As Byte, Optional lookIn As Variant, Optional lookAt As Variant, Optional matchCase As Boolean) As Range
    Dim fii As Long, baseRange As Range, resultRange As Range
    Dim rOffset As Long, cOffset As Long
 
    If IsMissing(lookIn) Then lookIn = xlValues 'xlFormulas
    If IsMissing(lookAt) Then lookAt = xlWhole ' xlPart
    If IsMissing(matchCase) Then matchCase = False
 
    Set baseRange = searchRanges(LBound(searchRanges))
    For fii = LBound(findItems) To UBound(findItems)
        If fii < UBound(searchRanges) Then
            If RC = 1 Then rOffset = searchRanges(fii + 1).Row - baseRange.Row
            If RC = 2 Then cOffset = searchRanges(fii + 1).Column - baseRange.Column
        End If
 
        Set resultRange = findRange(findItem:=findItems(fii), searchRange:=baseRange, lookIn:=lookIn, lookAt:=lookAt, matchCase:=matchCase)
        If resultRange Is Nothing Then
            Set baseRange = Nothing
            Exit For
        Else
            Set baseRange = IIf(fii < UBound(searchRanges), resultRange.Offset(rOffset, cOffset), Nothing)
        End If
    Next fii
 
    Set findRangeRecursive = resultRange
End Function

HOWTO: Disable Office 2010 online help

Halálba idegesített már, hogy ott az offline help db-je a VBA motornak, ez meg minden egyes F1-re igyekszik az MSOffice online help site-ról tölteni. Természetesen körülnéztem az egész helpet, hogy hol lehet az offline helpet elsődleges prioritásúvá tenni, de sehol semmi. Végül kitúrtam, hogy hol lehet az egész online help hozzáférést tokkal-vonóval letiltani - itt van (nem volt egyértelmű rátalálni):

File/Options/Trust Center/Trust Center Settings.../Privacy Options/Connect to Office.com for updated content when I'm connected to the Internet.

VBA binary ops WTF

Update: megkövetem a szidott codereket, természetesen helyes az eredmény. Ebből is látszik, hogy nem kellene éjjel fosni a kódot. Megyek, beáztatom a kenderkötelet.

Szerintük így korrekt:

?&H0100& and &H00FF&
 0

Sok minden hiányossággal számoltam már a VBA-val kapcsolatban, de arra még sose mertem gondolni, hogy egy binary and csak 8 bitig fog menni...

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.