Wim‘s Excel, TM1 & soccer site

Het Excel selectiedeelvenster voor objecten

27/04/2019

	Voorbeeldbestanden bij dit artikel:
	Het Excel selectiedeelvenster

Inleiding

Hallo, hier ben ik weer met een nieuw artikel over Excel ! Ik zou jullie graag het selectiedeelvenster in Excel willen tonen, en hoe het jullie kan helpen in het zien en vinden van objecten die jullie anders niet zouden zien. Tevens geef ik jullie graag een aantal tips and tricks om te begrijpen waarom jullie favoriete Excel bestand ineens of over tijd zeer groot wordt.

Het gaat als volgt. Ik heb een Excel bestand voor kruiswoordraadsels. Eén stukje daaruit is een werkblad met een heleboel oplossingen van de raadsels. Echter ik wil het niet zozeer hebben over de puzzels maar wel over de objecten/vormen in het bestand.

Je kan zeker mijn (groot) bestand downloaden bovenaan deze pagina. Wanneer je het bestand opent zal je enkel antwoorden op kruiswoordraadsels vinden. Niets spannends. Je zal de roosters zien in de kolommen A tot R, en ook 2 knoppen:

Toegegeven, het bestand is nu niet echt klein te noemen en bevat ongeveer 170 kruiswoordraadsels met wat opmaak (randen) om ze te doen lijken op puzzels. Echter, dit alles kan niet verklaren waarom de bestandsgrootte ongeveer 2 MB is. Er moet iets anders aan de hand zijn.

Wat zou het kunnen zijn ? Als ik Ctrl + End druk kom ik hier uit: cell S2407.

Is dat zeer ver naar rechts of naar onder ? Neen. Mocht dat het geval zijn, dan kan het zeker leiden tot 'file bloat' (een opgeblazen bestand) en een groter-dan-normaal bestand aangezien Excel ook rekening houdt met alle cellen tot aan die zogenoemde 'laatste cel'. Zelfs als je denkt dat je maar een beperkt aantal cellen gebruikt, Excel kan er anders over denken ! De oplossing zou hier zijn om de onnodige rijen en kolommen te verwijderen, het bestand op te slaan en de bestandsgrootte zakt weer naar normale proporties. In mijn bestand van kruiswoordraadsels is de te grote 'used range' echter niet de oorzaak.

Wat zou het nog kunnen zijn ? Tientallen duizenden regels code in VBA ? Neen, want het bestand is een xlsx-bestand, dit is een bestandsformaat vrij van macro's. Onnoemelijk veel opmaak en voorwaardelijke opmaak ? Neen, er zit geen voorwaardelijke opmaak in het bestand en de gewone opmaak is ook al beperkt. Verborgen werkbladen ? Neen, die zijn er ook niet. Linken naar andere bestanden ? Een hoop benoemde bereiken ? Ook niet het geval. Caches van draaitabellen die nog in het bestand zitten ? Ook niet. Laten we eens kijken naar objecten en vormen !

In feite vult de gebruiker van deze file de oplossingen in gebaseerd op een foto van de oplossing van internet (een populaire krant in België). Wat er gebeurt is dat de gebruiker de oplossing kopieert van internet naar Excel onder een bitmap formaat - een afbeelding. Vervolgens wordt de oplossing manueel overgetypt in een rooster. Ik vermoed dat bij het kopiëren van de foto van internet naar Excel ook kleinere afbeeldingen mee gekopieerd worden, terwijl die wel doorzichtig zijn (transparant). Nadien worden de foto's wel verwijderd maar de doorzichtige en dus onzichtbare afbeeldingen blijven mee in de file zitten en zorgen voor de massale stijging in bestandsgrootte. Zij zijn dus de ware schuldige !

Daar heb je het. Heel veel onzichtbare afbeeldingen die optellen in grootte en deel uitmaken van het bestand. Hoe kunnen we die localiseren in het werkblad ?

De eerste stap is het "Selectiedeelvenster" te tonen:

Dan kan je objecten (afbeeldingen) selecteren en ze onzichtbaar maken met het oog icoon (hoewel de meeste afbeeldingen al transparant zijn). Maar wat je vooral zal willen doen is is uitzoeken waar die verdomde afbeeldingen nu echt zitten in de file. Dat kan je doen door de Tab toets in te drukken, of als je in de andere richting wil gaan: Shift + Tab. Nu kan je gemakkelijk de objecten (afbeeldingen) deleten.

Indien je een oplossing met VBA wenst, hier is code voor jou. De code loopt over alle vormen in het werkblad. Onthoud dat de knoppen ook vormen zijn (en die wil je per slot van rekening niet deleten), en gegevensvalidatie, filterknoppen, ... dat zijn voor Excel allemaal vormen. Kijk dus even goed uit of je zou teveel kunnen verwijderen !

Sub Loop_over_vormen()
' Wim Gielis
' https://www.wimgielis.com
'''''
' Code om over vormen in huidige werkblad te lopen
' 27/04/19
'''''

    Dim s                     As Shape

    For Each s In ActiveSheet.Shapes
        Debug.Print s.TopLeftCell.Address(0, 0), s.Type, s.TextFrame.Characters.Caption
    Next

End Sub

Mijn code zal de details van elke vorm dumpen in het 'Immediate window' in VBA zodat je een kijkje kan nemen. Eerst krijg je de cel waar de vorm zich bevindt, dan het type vorm (er zijn veel verschillende types van vormen) en tot slot ook de tekst op de vorm (zo hebben de 2 knoppen een tekst). Gebruik s.Delete naast of in plaats van de Debug.Print lijn als je af wil van de vormen maar delete er niet te veel.

Een kleine oefening voor de lezer: wie kan er mij zeggen waarom er een lege lijn tekst in de output zit ? Het heeft te maken met de vorige lijn en de knop daar:

Een zeer nuttige tip is om zeker de F5 toets (of Ctrl + g) te gebruiken. Als je dan Speciaal... kiest kan je vragen voor alle objecten in de selectie of het hele werkblad. Kijk hieronder eens, stap 1: