Finch’s weblog

dinsdag, 19 februari 2008

Excel: Professional Excel Development

Filed under: Excel, Excel VBA — thefinch @ 16:41

Vandaag is het boek Professional Excel Development, dat ik een tijdje geleden bestelde, toegekomen. Impressies volgen later, maar met 900+ pagina’s zal ik er wel even zoet mee zijn.

Advertenties

zaterdag, 9 februari 2008

Excel: Dynamische afhankelijke datavalidatie

Filed under: Excel — thefinch @ 16:10

Recentelijk kreeg ik in mijn mailbox een aantal vragen rond dynamische afhankelijke datavalidatie, daarom dat we dit laatste even onder de loep nemen. Dynamische afhankelijke datavalidatie is een mond vol. Daarom eerst een beetje uitleg wat dat begrip nu eigenlijk inhoudt. In deze context wil datavalidatie zeggen dat we op celniveau de inhoud kunnen kiezen uit een keuzelijst. Met afhankelijk bedoelen we dat de waarde in cel 1 de keuzemogelijkheden van de lijst in cel 2 zal bepalen. Bijvoorbeeld stel dat we in cel 1 automerken kunnen kiezen, en in cel 2 types van deze automerken. Wanneer we nu in cel 1 Volkswagen kiezen, dan krijgen we in cel 2 de keuze uit VW types zoals Golf, Passat, Fox, enz..Kiezen we achter Audi in cel 1 dan zal cel 2 uit keuzemogelijkheden zoals A4, A6 en A8 bestaan. Met dynamisch bedoelen we dat wanneer we meer keuzemogelijkheden aan een lijst toevoegen, deze ook automatisch in het keuzelijstje verschijnen zonder enige formule aan te passen.

Afhankelijke datavalidatie

Laat ons eerst kijken naar het verhaal van afhankelijke datavalidatie, vervolgens naar dynamische datavalidatie en in een laatste fase pas naar dynamische afhankelijke datavalidatie.
In het voorbeeld vetrekken we van een automerk, binnen dat automerk hebben we verschillende types van modellen, en elk model heeft weer specifieke motorisaties. We willen dus datavalidatie op 3 velden: merk – type – motorisatie.

We vetrekken van deze opzet: we hebben 2 merken, VW en Audi, en binnen elk merk hebben we 2 types en binnen elk type 2 motorisaties.
Om deze gegevens te gebruiken in een afhankelijke datavalidatie, gaan we moeten werken met naamgevingen. Onderstaande tabel geeft de benodigde naamgevingen weer:

Het is belangrijk op te merken dat de keuzemogelijkheden die je wil krijgen in je validatielijst best exact dezelfde naam hebben als de waarde die je kan selecteren in de voorgaande cel. Dit wil dus zeggen dat wanneer je als merk VW kiest, de types van VW ook de naam “VW” dragen, en bv. niet “Volkswagen” of “VWTypes” of dergelijk. (Onderaan deze topic vindt u manier om dit te omzeilen –maar deze is minder duidelijk te vatten en ook minder efficiënt).
Nu elk lijstje een naam gekregen heeft, kunnen we deze gaan gebruiken voor datavalidatie. Aangezien we voor de velden “type” en “motorisatie” niet rechtstreeks kunnen verwijzen naar een gedefinieerde naam, omdat deze afhankelijk is van een andere waarde, dienen we gebruik te maken van de Indirect functie zoals weergegeven op onderstaande afbeelding.

Op deze manier kunnen we dus afhankelijke datavalidatie bekomen.

NOOT 1:

Eerder gaf ik aan dat het raadzaam was om de lijsten allemaal dezelfde naam te geven als de keuzemogelijkheden. Een manier om dit te omzeilen is een lijstje te maken met in de ene kolom de keuzemogelijkheid en in de andere kolom de naam van het bereik. Middels vert.zoeken binnen de lijstvalidatie kom je dan ook tot de gewenste oplossing. Enkele afbeeldingen die dat aantonen:


Dit bereik heb ik gemakkelijkheidhalve even de naam “NaamLijst” gegeven.

We kunnen nu de formule “=INDIRECT(VERT.ZOEKEN(E1;Naamlijst;2;ONWAAR))” voor de lijstvalidatie gebruiken.


NOOT 2:

Soms heb je keuzemogelijkheden die uit twee woorden bestaan, zoals bv. het automerk Alfa Romeo. Aangezien spaties binnen Excel naamgevingen niet toegelaten zijn, dienen we hierbij gebruik te maken van de substitueren formule (ervan uitgaande dat de naam gelijk is aan de keuzemogelijkheid maar dan zonder spatie). Hieronder enkele afbeeldingen die deze opzet verduidelijken:



Dynamische datavalidatie

Bij dynamische validatie, wordt de lengte van de keuzelijst automatisch aangepast aan het aantal waarden waar ze uit bestaat, m.a.w. de lengte van deze lijst dienen we te berekenen met behulp van formules.

We concentreren ons hier enkel op de autotypes van het automerk VW. In een eerste instantie hebben we slechts 2 mogelijkheden nl. Golf en Passat. Willen we dit berekenen met formules, gebruiken we de aantalarg formule i.c.m. de verschuiving formule.

De aantalarg formule zal ons vertellen uit hoeveel elementen de lijst bestaat, en via de verschuiving functie kunnen we dat dan in een bereik gieten.

In het geval dat deze keuzemogelijkheden in kolom B staan, kunnen we volgende formule bij naamgeving gebruiken:

Wanneer we nu deze naam gebruiken bij de lijstvalidatie, dan bekomen we een dynamische validatie.

Wanneer we nu elementen aan de lijst in kolom B gaan toevoegen, komen deze ook automatisch in onze validatielijst te staan.

Dynamische afhankelijke datavalidatie

Na het lezen van de twee voorgaande paragrafen zou je tot de conclusie komen dat dynamische afhankelijke datavalidatie feitelijk een combinatie is van beide technieken. Hoewel dit niet correct gaan we deze piste toch even verder bekijken, om te besluiten met de correcte werkwijze om te komen tot het gewenste resultaat.

Wederom ons voorbeeld van auto’s:

Volgende afbeelding toont de dynamische naamgeving voor de automerken en ook voor de modellen van het merk VW.

Proberen we deze nu te gebruiken bij een afhankelijke datavalidatie, dan krijgen we volgende opzet:

Wanneer we de datavalidatie op cel F2 proberen in te voeren, krijgen we echter deze foutmelding:

De reden waarom de datavalidatie op deze manier mislukt, is te verklaren door het feit de indirect functie die we gebruiken enkel overweg kan met referenties en niet met formules.

Een manier om toch tot dynamische afhankelijke validatie te komen, is de verschuiving formule die we in de naamgeving gebruiken overhevelen naar de formule van de datavalidatie. Hiervoor dienen we wel 2 naamgevingen te gebruiken om alles tot een goed eind te brengen.

Laat ons dit even illustreren a.d.h.v. het autovoorbeeld. We hebben weer onze twee merken VW en Audi, en de overeenkomstige types.

Om de types van VW dynamisch te kunnen kiezen, geven we de eerste cel van kolom B (=B1) de naam “VW” en de hele kolom B geven we een naam waar VW in voorkomt, bv. “VWKOL”. Voor Audi voeren we uiteraard een gelijkaardige handeling uit:

Nu de voorbereidende werken achter de rug zijn, kunnen we ons gaan concentreren op de formule bij de lijstvalidatie. We zeiden eerder al dat we de verschuiving formule op dat niveau moesten gaan gebruiken. Onze startcel is steeds de eerste cel van de overeenkomstige kolom met keuzemogelijkheden, die hebben we een naam gegeven. Het aantal elementen dat we in die kolom willen gebruiken kunnen we wederom berekenen met de aantalarg formule. Als argument van deze formule gebruiken we steeds de hele overeenkomstige kolom, die we ook van een naam voorzien hebben. Aangezien beide namen uit verwijzingen bestaan, kunnen we nu wel gebruik maken van de indirect formule (dit keer zonder foutmeldingen!). Het resultaat staat in volgende afbeelding:

Op deze manier kunnen we dus gebruik maken van dynamische afhankelijke datavalidatie. Uiteraard is dezelfde methode ook toepasbaar voor de motorisaties.

NOOT 3:

De eerder beschreven opzet met spaties in de keuzemogelijkheden en de daarvoor voorziene oplossing met de substitueren formule is hier ook toepasbaar.

zaterdag, 2 februari 2008

Excel: Bestelling boek

Filed under: Excel — thefinch @ 17:55

Ik heb vandaag het boek “Professional Excel Development” van Stephen Bullen, Rob Bovey en John Green in bestelling geplaatst.
Dit boek wordt op het internet door iedereen die een beetje serieus met Excel bezig is de hemel in geprezen. De verwachtingen zijn in ieder geval hoog gespannen.

I’ll keep you in touch!

donderdag, 31 januari 2008

Excel VBA: Code library

Filed under: Excel, Excel VBA — thefinch @ 8:35

Ik ben al enige tijd op zoek naar een code library. De reden: eigenlijk overduidelijk, je hebt ooit eens een stukje code geschreven voor een bepaalde taak, en zou het nu opnieuw kunnen gebruiken. Maar waar heb je die code neergeschreven? Momenteel vertrouw ik eigenlijk op mijn geheugen om het juiste bestand met dat stukje code in te vinden. Vaak lukt dat, maar soms kom ik ook terug van een kale reis, en kan ik “from scratch” beginnen coderen. Onlangs kwam ik op deze link terecht van XL-Dennis http://xldennis.wordpress.com/2007/12/03/net-co-library-is-released/.

XL-Dennis is geen onbekende binnen het Excel wereldje en heeft een zekere staat van verdienste. Ik heb deze code library gedownload en heb deze tool dan ook momenteel in gebruik. Het is echter nog te vroeg om een definitief oordeel te vellen, maar voorlopig voldoet het aan mijn verwachtingen. Deze code library voegt een extra menu optie toe aan je VBE, waardoor je hem altijd snel kan raadplegen. Geef het ook eens een kans zou ik zeggen.

Graag had ik geweten wat jullie als code library gebruiken? Of hoe hergebruiken jullie je code? Laat hiervoor een comment op deze post.

maandag, 28 januari 2008

Excel: Kleur cel achterhalen zonder VBA

Filed under: Excel — thefinch @ 9:55

Volgende vragen komen vaak op Excel fora voor:

  • “Ik heb bepaalde cellen een achtergrond kleur gegeven, kan ik nu sorteren op die kleuren?”
  • “Ik heb bepaalde cellen gekleurd, kan ik die kleur nu gebruiken in een som.als formule?”

Dit soort vragen komt feitelijk neer op de volgende “kan ik de achtergrondkleur van een cel achterhalen?”.

NOOT:
Aangezien Excel 2007 reeds meer (maar nog geen volledige) ondersteuning biedt voor het werken met kleuren (sorteren op kleur kan nu bijvoorbeeld wel al), en het kleurenpallet in deze versie enorm is uitgebreid richt ik me in dit artikel voornamelijk op Excel versie 2003 en vroeger. De techniek werkt ook binnen Excel 2007, maar misschien niet feilloos, en is daar soms overbodig.

Om de achtergrondkleur van een cel te weten te komen wordt veelal als antwoord gegeven: “Dat kan alleen maar middels VBA”. Inderdaad, met VBA kan je de celkleur achterhalen, bijvoorbeeld via deze code:

Sub KleurenVBA()
Dim i As Long

For i = 1 To 8
Cells(i, 1).Offset(0, 2) = Cells(i, 1).Interior.ColorIndex
Next i

End Sub

Middels de standaard formules van Excel is het inderdaad niet mogelijk de celkleur te achterhalen, toch bestaat er een achterpoortje om de kleur van een cel uit te lezen zonder gebruik te maken van VBA. Daarvoor dienen we gebruik te maken van een XLM, of anders verwoord, een Excel4Macro. In Excel versie 5 werd voor het eerst VBA geïntroduceerd, daarvoor (vanaf versie 2) gebruikte men XLM macro’s. De XLM macro taal bestond uit functies die sequentieel werden uitgevoerd. Excel4Macro’s worden in huidige versies nog wel ondersteund. Hoewel de functies niet rechtstreeks te gebruiken zijn op een werkblad, bestaat er toch een omweg om dit voor elkaar te krijgen. Wanneer we een bereik een naam willen geven, dan kunnen we in het verwijzingsveld ook formules invoeren, of zelfs een matrixformule. Kort door de bocht mogen we namen van bereiken binnen Excel zien als een formule. Wonderwel kunnen we bij namen XLM-functies wel gebruiken, en vervolgens die naamgeving gewoon gebruiken op een werkblad.

De XLM-functie in kwestie is de get.cell(63,verwijzing) functie, we moeten deze wel aanspreken in de taal van de Excel versie, wanneer dit een Nederlandstalige versie is, dienen we cel.lezen(63,verwijzing) te gebruiken. Verwijzingen worden bij voorkeur in R1C1-notatie (NL: R1K1) gedaan. Hoewel dit laatste niet strikt noodzakelijk is, merk ik minder problemen wanneer ik R1C1-verwijzingen gebruik.

Laat ons bovenstaande even testen op onderstaande situatie (gebruik makend van een Nederlandstalige versie, onderaan staan de formules voor een Engelstalige versie):

Vervolgens willen we in kolom B de kleur te weten komen van de overeenkomstige rij in kolom A. Daarvoor dienen we eerst een naam “kleur” te definiëren, deze naam (lees: formule) moet telkens verwijzen naar de cel links van de cel waar de naam (formule) in komt te staan, en dit in R1K1 notatie. Met de formule “=indirect(“RK”;ONWAAR)” verwijzen we naar de cel waar de formule in gebruikt wordt, en dit in R1K1 notatie. Met “=verschuiving(indirect(“RK”;ONWAAR);0;-1)” verwijzen we dus in R1K1 notatie naar de cel die zich 1 kolom links, en op dezelfde rij bevindt. Deze verwijzing gaan we nu gebruiken bij de XLM functie cel.lezen. Het geheel wordt dan “=cel.lezen(63; verschuiving(indirect(“RK”;ONWAAR);0;-1)”.

Engelstalige formule: =get.cell(63,offset(indirect(“RC”,FALSE),,-1))

Nu kunnen we deze naam gebruiken in kolom B, we krijgen dan dit als resultaat:

Als test gaan we nu in kolom C de kleurindex tonen, bekomen met bovenstaande VBA code.

We merken dat deze overeenkomstige waarden opleveren, enkel wanneer we zoals in A8 geen kleur definiëren, dan merken we een verschil. Bij de XLM functie is de standaardwaarde gelijk aan 0, terwijl via VBA code deze -4142 is. Voor de volledigheid gaan we in kolom D even de uitkomst zetten van de XLM macro, maar dan aangeroepen via VBA. De code en het resultaat is als volgt:

Sub KleurenXLM()
Dim sXLMarg As String
Dim rBron As Range
Dim rCell As Range

Set rBron = Range(“A1:A8”)

For Each rCell In rBron

sXLMarg = “get.cell(63,” & rCell.Parent.Name & “!” & rCell.Address(ReferenceStyle:=xlR1C1) & “)”
rCell.Offset(, 3).Value = Application.ExecuteExcel4Macro(sXLMarg)

Next rCell

End Sub

 

Nu we de kleur van een cel kunnen achterhalen, is het uiteraard ook mogelijk deze gegevens te gebruiken in andere formules. Zo kunnen we een som.als formule gebruiken op basis van deze kleurwaarde, kunnen we sorteren op kleurwaarde (eventueel in combinatie met een bepaalde rangorde voor kleuren), enz.
Hieronder een voorbeeldje met de som.als formule, merk op dat de naam (formule) kleur pas getriggerd wordt, en dus een aanpassing krijgt bij een herberekening. Het veranderen van de achtergrondkleur zorgt echter niet voor herberekening, dus moet je even F9 drukken of wachten tot een andere bewerking om een herberekening vraagt. Kolom C kan uiteraard in onderstaand voorbeeld verborgen worden indien gewenst.

NOOT:
Het gebruik van Excel4Macro’s wordt in huidige (PC) versies van Excel nog steeds ondersteund, maar naargelang de bron zou die ondersteuning kunnen wegvallen in volgende releases. Anderen beweren dan weer dat ook volgende versies XLM macro’s zullen supporteren.

Het gebruik van XLM-functies kan ook een zeker veiligheidsrisico inhouden. Excel zal dit ook melden bij het openen van zulk bestand. Het copieren van een cel waarin verwezen wordt naar een XLM-macro naar een ander tabblad kan er soms ook voor zorgen dat Excel gaat crashen. Gebruik XLM-functies dan ook gecontroleerd.

Je kan een voorbeeld van bovenstaande techniek HIER downloaden.

vrijdag, 25 januari 2008

Excel: Subtotaal

Filed under: Excel — thefinch @ 9:15

Om maar met de deur in huis te vallen, de formule subtotaal dient zoals de naam aangeeft om subtotalen te maken in of van lijsten. De syntax om de formule te gebruiken is als volgt:

SUBTOTAAL(Functiegetal;verwijzing1;verwijzing2;…) (met maximaal 254 verwijzingen).

Naast een argument “functiegetal”, kan je dus 1 of meerdere bereiken opgeven bij een subtotaal formule. Maar laat ons eerst het argument “functie” bekijken. Dit argument kent een aantal (voorgedefinieerde) getallen, hieronder volgt de opsomming met hun omschrijving:

NOOT:     Functiegetallen 101 tot en met 111 zijn pas aanwezig vanaf Excel versie 2003. In eerdere versies kan je enkel functiegetal 1 tot en met 11 gebruiken.

    In onderstaand artikel bespreken we enkel de subtotaal functie in vergelijking met de som formule (dus functiegetal 9 en 109), andere functiegetallen vertonen analoog gedrag in vergelijking met de overeenkomstige functies.

Op het eerste zicht lijkt de subtotaal precies een vervanger te zijn te zijn voor de functies in bovenstaand lijstje. Is er een verschil tussen de drie formules “=som(B2:B14)“, “=subtotaal(9;B2:B14)” en “=subtotaal(109;B2:B14)“? Zo ja, welke verschillen dan?

Laat ons dit eens bekijken aan de hand van een voorbeeld. Stel volgende datatabel:

We merken dat de som berekend via de drie eerder aangehaalde formules gelijk is. Wanneer we echter de data gaan filteren op bijvoorbeeld afdeling “Zuid”, dan ontstaat volgende situatie:

Nu pas worden verschillen zichtbaar tussen de som formule en de subtotaal formule. Een som formule houdt voor zijn resultaat geen rekening met het feit of de rijen al dan niet gefilterd zijn (en dus verborgen zijn). De subtotaal formule, zowel met functiegetal 9 als 109, echter wel. Gefilterde (verborgen) rijen worden niet meegeteld om het resultaat te bekomen.

We kennen nu het verschil tussen een som formule en een subtotaal formule, maar waar zit dan het verschil of we die subtotaal formule nu gebruiken met een functiegetal kleiner dan 100 of juist groter dan 100? Om dit verschil aan te tonen gebruiken we een op het eerste zicht gelijkaardige datatabel. Het enige verschil is dat we de filtermogelijkheid op deze tabel achterwege laten.

Zoals we verwachten in deze uitgangssituatie zijn de resultaten van de drie formules wederom gelijk, maar laten we nu in plaats van een filter toe te passen eens enkele rijen handmatig verbergen (merk op dat de rijnummers nu niet blauw worden, dit in tegenstelling tot een gefilterde rij).

Zoals je ziet heeft het handmatig verbergen van rij 5 en 10 geen invloed op de uitkomst van de som formule, en ook geen invloed op de uitkomst van de subtotaal formule met functiegetal 9. De subtotaal formule met functiegetal 109 heeft echter wel rekening gehouden met deze handmatig verborgen rijen. We mogen dan ook terecht concluderen dat de subtotaal formule met een functiegetal groter dan 100 zijn resultaat laat afhangen van handmatig verborgen rijen, terwijl deze met een functiegetal kleiner dan 100 dat niet doet (echter, in het geval dat er reeds een filter actief is, hangt zijn resultaat ook af van handmatig verborgen rijen – test dit zelf maar even na).

Als laatste punt bespreken we een andere eigenschap die de subtotaal formule onderscheidt van zijn som tegenhanger.

Onze uitgangssituatie is nu als volgt: we hebben een aantal omzetgegevens gerangschikt per maand, en voegen daar nu subtotalen aan toe. Excel kan dat automatisch door in de ribbon “gegevens” de knop subtotaal te drukken:

Uitgangssituatie:

Na het automatisch invoegen van subtotalen krijgen we dit resultaat (ik heb nadien de lay-out wel een beetje gewijzigd).

In een volgende stap gaan we een kwartaalresultaat aan deze cijfers toevoegen, we maken hierbij gebruik van zowel de som formule als van de subtotaal formule, en dit voor beiden op het bereik B2:B13. Laten we even naar de resultaten kijken:

Wanneer we het kwartaaltotaal via beide formules bekijken geeft enkel het totaal bekomen met de subtotaal formule een correct resultaat, hij heeft de andere cellen met een subtotaal formule in niet dubbel geteld. Dat laatste doet de normale som formule echter wel. Dit kan voorkomen worden op een aantal manieren, bv. enkel de som nemen van cel B4, B8 en B13 (“=B4+B8+B13″) of via deze formule “=SOM(B2:B13)/2” of … . Geen enkel van deze varianten is echter zo gebruiksvriendelijk of veilig als de eerder aangehaalde subtotaal formule.

woensdag, 23 januari 2008

Excel: Draaitabellen

Filed under: Excel — thefinch @ 10:05

In het artikel van vandaag bespreken we een handige, maar minder bekende, handeling met draaitabellen.

Situatieschets:

Je hebt een lijst met 3 gegevens: afdeling, datum en verkoopresultaten. Onderstaande afbeelding geeft die bedoeling weer:

De bedoeling is nu dat we voor elke afdeling een nieuwe sheet aanmaken met daarin de verkopen, gegroepeerd per maand en jaar.

 

Oplossing:

Een opzet middels een draaitabel ligt voor de hand. Middels een draaitabel is het immers mogelijk datums te groeperen per maand en jaar, maar we kunnen ook die nodige sheets per afdeling aanmaken. Hoe? Dat doen we in dit artikel uit de doeken.

Allereerst maken we het geraamte van onze draaitabel aan:

 

 

De datums hebben we kunnen onderverdelen in jaren en maanden middels de “groeperen” functionaliteit van de draaitabel. Deze bekomen we door het datumveld als rijlabel te definiëren, vervolgens gaan we in de draaitabel in een datumcel staan, en kiezen rechtermuisknop. We krijgen dan volgend contextmenu:

 

 

Daar kiezen we dan de optie “groeperen”, en vervolgens komen we dan in dit scherm terecht:

 

 

We duiden daar “Maanden” en “Jaren” aan. Na het klikken op OK, merken we dat het datumveld opgesplitst wordt per jaar en per maand. Onze definitie van de draaitabel ziet er nu als volgt uit:

 

 

Met deze definitie hebben we een eerste vereiste van ons resultaat bereikt, het groeperen van de datums. Willen we nu nog een sheet per afdeling aanmaken, moeten we de definitie van onze draaitabel lichtjes wijzingen.

Verplaatsen we nu “afdeling” van een rijlabel naar een rapportfilter, dan wordt dit het uitzicht van de draaitabel, met bijhorend definitie.

 


 

Dit is een noodzakelijke stap om onze doelstelling van aparte sheets per afdeling te bereiken. Wanneer we nu op de “opties” ribbon van “hulpmiddelen voor draaitabellen”, vervolgens op het pijltje van de knop opties (zie onderstaande afbeelding) drukken, merken we dat de optie “Rapportfilterpagina’s weergeven” beschikbaar wordt.

 

 

In Excel 2003 en vorige versies vinden we deze optie terug op volgende wijze:

 

 

 

Door deze optie aan te klikken krijgen we volgende venster voorgeschoteld:

 

 

We kiezen hier voor de (enigste) mogelijkheid “afdeling”. Wanneer je meerdere rapportfilters hebt staan, dien je te kiezen welke rapportfilter je wilt gebruiken om pagina’s aan te maken. Na het drukken op de OK-knop, gaan we zien dat Excel voor elke afdeling een nieuwe sheet heeft aangemaakt, met daarin de gefilterde gegevens.

 

 

(voorbeeld sheet van de afdeling Noord, de rest van de sheets worden analoog opgebouwd)

 

Op die manier hebben we beide vooropgestelde doelstelling bereikt, door een paar simpele muisklikken.

maandag, 21 januari 2008

Excel VBA: Opslaan verboden?

Filed under: Excel, Excel VBA — thefinch @ 8:55

De post van vandaag is geïnspireerd op volgende vraag: “Ik wil dat gebruikers mijn bestand niet kunnen opslaan.” Deze vraag werd op het helpmij.nl forum gesteld, en kan je hier vinden. De meest eenvoudige manier om het opslaan te verhinderen is ingrijpen op het Workbook_BeforeSave event dat in Excel beschikbaar is. Volgende code kunnen we hiervoor gebruiken.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox “Opslaan is niet toegestaan”, vbInformation + vbOKOnly, “Verboden actie…”
Cancel = True
End Sub

Bovenstaande code plaatsen we achter ThisWorkbook in de VBE (zie volgende afbeelding).

Deze code stelt echter volgend probleem, ook opgemerkt door Ginger. Hoe kan je je bestand nu opslaan met de code erin, terwijl diezelfde code opslaan zal verhinderden? De oplossing is echter zeer simpel, zet Excel even in ontwerpmodus en sla dan het bestand op. Om Excel in ontwerpmodus te zetten klik je even op het icoontje met een blauwe driehoek, een meetlat en een potlood (zie icoon met rode omkadering):

Op deze manier kan je dus het bestand opslaan, zonder dat het Workbook_BeforeSave event aangeroepen wordt.

vrijdag, 18 januari 2008

Excel VBA: Range.SpecialCells(xlCellTypeBlanks) vs Range.SpecialCells(xlCellTypeLastCell)

Filed under: Excel, Excel VBA — thefinch @ 10:04

Een paar dagen geleden kreeg ik de vraag “Kan ik binnen VBA te weten komen hoeveel cellen binnen een bepaald bereik een waarde bevatten (zonder gebruik te maken van werkbladfuncties)?”. In feite niet zo een moeilijke vraag, maar wat het eerste in me op kwam was: tel het aantal cellen in het bereik en trek daar het aantal blanco cellen vanaf. Aangezien beide delen binnen VBA redelijk efficiënt bekomen konden worden schreef ik volgende code:

Function TelIngevuld(r As Range) As Long
TelIngevuld = r.Count – r.SpecialCells(xlCellTypeBlanks).Count
End Function

Om deze code uit te testen gebruikte ik onderstaande VBA code:

Sub TestFunctie()
Debug.Print TelIngevuld(Range(“A1:A20”))
End Sub

Om bovenstaande code te schrijven had ik gewoon Excel (2007) geopend, VBE geactiveerd (ALT+F11) en een nieuwe module aangemaakt. Bij het uitvoeren van de sub “TestFunctie” kreeg ik dadelijk een error:

Toen ik het hoe en waarom van deze foutmelding probeerde uit te zoeken kwam ik tot de vaststelling dat het kwam omdat er in het werkblad nog nooit “enige actie had plaatsgevonden”. Voerde ik een waarde in A5, dan was het resultaat van deze functie 16. Vreemd, ik had 1 als resultaat verwacht. Blijkbaar had Excel enkel de 4 eerste cellen (A1:A4) als blanco geteld en dat afgetrokken van het aantal cellen in mijn gevraagd bereik, nl. 20.

Wanneer ik nu mijn waarde in A5 uitveegde, en in A21 een willekeurige waarde plaatste, werd het resultaat van mijn functie 0. Net hetgeen ik verwachtte.

In een volgende stap heb ik in beide situaties een extra debug code toegevoegd, namelijk:

Debug.Print Cells.SpecialCells(xlCellTypeLastCell).Address

Met een waarde in A5 ingevuld, was die uitkomst hiervan $A$5, en met een waarde in A21 kreeg ik de waarde $A$21 terug.

Om dus een correct resultaat met bovenstaande code te bekomen, is het dus een vereiste dat het bereik dat je op lege cellen wil testen in zijn geheel ingesloten wordt door het “werkgebied” van Excel. Dit is iets wat ik pas recent te weten ben gekomen, ondanks het feit dat ik dergelijke code reeds vele malen op voorhand gebruikt hebt, maar dan meestal binnen een Excel bestand waar reeds de nodige data stond, zodat ik nooit (althans bewust) geconfronteerd werd met bovenstaande situatie. Wat dan ook wil zeggen dat ik nooit ben blijven stilstaan bij deze randvoorwaarde om correcte resultaten te krijgen. Tot nu dus. Achteraf beschouwd zit dat allemaal logisch in elkaar, maar, en misschien juist daardoor, was ik dit nog niet op voorhand in de literatuur tegengekomen. Vandaar ook deze post.

donderdag, 17 januari 2008

Excel: Verticaal zoeken

Filed under: Excel — thefinch @ 9:53

Vandaag gaan we het makkelijk houden, en beperken we ons tot de formule vert.zoeken (Vlookup in een Engelstalige versie). Veel beginnende Excel gebruikers zijn vaak niet bekend met deze formule en proberen de functionaliteit na te bootsen met geneste ALS formules, maar lopen dan al heel snel tegen problemen aan. Op forums moet je geregeld het antwoord geven: “dat is een probleem dat eenvoudig op te lossen is met de vert.zoeken formule”. Vandaar de reden dat ik dit artikel schrijf. In essentie kunnen we de vert.zoeken formule omschrijven als een formule die een gevraagde waarde gaat opzoeken in een zoektabel en dan overeenkomstige gegevens gaat weergeven.

Voorbeeld 1:

Voor het eerste voorbeeld nemen we volgende gegevenstabel als uitgangssituatie:

Deze tabel bevat klantgegevens van een onderneming. Telkens men een factuur gaat aanmaken vult men alle gegevens over die klant handmatig in, in de daarvoor bestemde velden. Echt handig is dat niet, het is tijdrovend de juiste klant op te zoeken, en dan al de gegevens één voor één intypen. Bovendien is de kans op foutieve invoer zeer reëel. Beter zou zijn enkel de klantID in te vullen, aangezien deze uniek dient te zijn, en dat dan al de overeenkomstige gegevens automatisch ingevuld worden.

Onderstaande afbeelding bevat een voorbeeld hoe bovenstaande situatieschets in Excel verwerkt kan worden:

Voor het automatisch ophalen van de gevraagde data, gebruiken we zoals reeds aangehaald de vert.zoeken formule. Deze formule heeft 4 argumenten, waarvan 1 optioneel. Deze 4 argumenten zijn:

  1. Zoekwaarde: Dit is de waarde die we wensen op te zoeken in onze tabelmatrix. Vaak gebruiken we een celverwijzing om de zoekwaarde te bepalen. In ons voorbeeld is dat B1.
  2. Tabelmatrix: Dit is de tabel waar we in de eerste kolom gaan zoeken, en waaruit we de gevraagde gegevens gaan halen (uit de volgende kolommen). In ons voorbeeld is dat F1:L11.
  3. Kolomindex_getal: Een getal dat aangeeft uit welke kolom van de tabelmatrix we de gegevens willen halen. In ons voorbeeld levert een 2 als argument naam gegevens op, en een 4 de postcode.
  4. Benaderen: Dit is het booleaans optionele argument. Wordt de waarde weggelaten, gebruikt Excel de waarde WAAR, wat wil zeggen dat wanneer er geen exacte match is met de zoekwaarde, de gegevens van de waarde die juist kleiner is dan zoekwaarde worden getoond. Je kan ook de waarde ONWAAR meegeven met de formule. Dan wordt er enkel een resultaat teruggegeven wanneer er sprake is van een exacte match. Aangezien in ons voorbeeld exacte gelijkenis wenselijk is zetten we deze waarde op ONWAAR.

OPMERKING: De zoekwaarde wordt altijd gezocht in de eerste kolom, en ook enkel in de eerste kolom, van de tabelmatrix! Dit impliceert dan ook dat gegevens die je kan ophalen zich ook altijd bevinden na de eerste kolom van de tabelmatrix, en dus nooit ervoor.

De tabelmatrix is in de meeste gevallen een vast bereik, en wordt daarom best in de formule met absolute celverwijzingen “aangesproken” (de $-tekens voor de kolomnaam en het rijnummer). Ditzelfde geldt vaak ook voor de zoekwaarde. In ons voorbeeld is de zoekwaarde steeds B1, vandaar ook weer de absolute celverwijzingen.

Toegepast op onze situatie krijgen we volgende formules:

Wanneer we nu in B1 de waarde veranderen, gaan de cellen B2:B7 automatisch de gevraagde gegevens ophalen en tonen, wanneer de zoekwaarde niet gevonden wordt, krijgen we echter een foutmelding.

Voorbeeld 2:

Als afsluiter voor vandaag nog een tweede voorbeeldje met de vert.zoeken formule. Dit keer gaan we de parameter “benaderen” van de formule op WAAR instellen. Daarvoor nemen we het uitgangspunt van (vereenvoudigde) puntenscores.

Bij een score van 0 tot en met 49 willen we de omschrijving “Buis!” weergeven, een score tussen 50 en 70 levert de omschrijving “Geslaagd” op, enz. Deze opzet in Excelformaat kan er bijvoorbeeld zo uitzien:

De formule in B2 ziet er als volgt uit: “=VERT.ZOEKEN($B$1;$E$2:$F$8;2;WAAR)”. Deze laatste optie waar konden we weglaten aangezien dat de standaard parameter is.

Aangezien we benaderen op waar hebben gezet zouden we bij een score van bv. 79 de omschrijving “Onderscheiding” willen zien, en bij een score van 80 “Grote Onderscheiding”. Hieronder 2 screenshots die dat aantonen:

Randopmerkingen:

Om af te sluiten wil ik nog een paar randopmerkingen rond het gebruik van de vert.zoeken formule geven:

  • Alvorens vert.zoeken te gebruiken maak je er best de gewoonte van de tabelmatrix oplopend te sorteren op het eerste veld. In sommige gevallen, zouden anders resultaten wel eens anders kunnen uitvallen dan verhoopt.
  • Wanneer de sleutelwaarde in je tabelmatrix niet uniek is (d.w.z. in kolom 1 van je tabelmatrix staan dubbels) dan “kiest” Excel van welk rij de gegevens worden weergegeven. Meestal is dat de rij van de eerste waarde die gevonden wordt, maar dat is niet altijd het geval.
  • Binnen de formule kan je ook werken met namen van bereiken. Namen geef je aan bereiken door in Excel 2007 in de ribbon “Formules” te klikken op “Namen beheren” vervolgens “nieuw” en daarna een geldige naam in te vullen alsook een bereik. (In Excel versies voor 2007 wordt dit Invoegen > Naam > Definiëren en ook een geldige naam en bereik opgeven). Stel dat we in het laatste voorbeeld volgende naamgeving hadden toegepast:
    • Naam: Zoekwaarde     Bereik: $B$1
    • Naam: Zoektabel         Bereik: $E$2:$F$8

    Dan konden we ook deze formule gebruiken: “=VERT.ZOEKEN(Zoekwaarde;Zoektabel;2;WAAR)”

  • In ons eerste voorbeeld diende we bij elk veld steeds het derde argument handmatig te veranderen om de veranderde gewenste kolom aan te duiden. Door gebruik te maken van de formule RIJ() hadden we dit kunnen vermijden. Deze formule geeft het rijnummer terug van het argument (welke een celverwijzing is), of bij het ontbreken van een argument het rijnummer van de huidige cel. Door de formule voor B2 te veranderen in: “=VERT.ZOEKEN($B$1;$F$2:$L$11;RIJ(A2);ONWAAR)” en deze door te voeren naar de cellen B3:B7 krijgen we ook steeds de gewenste gegevens terug.
Older Posts »

Maak een gratis website of blog op WordPress.com.