Een invoegtoepassing maken in Excel

Een invoegtoepassing maken in Excel 1. Inleiding Excel is een krachtige applicatie met duizenden opties en functies om rekenmodellen, rapporten en analyses te bouwen. Het komt toch nog regelmatig voor dat je in je dagelijkse werk wat functionaliteit zou kunnen gebruiken die Excel niet biedt. Met behulp van VBA is het mogelijk om functionaliteit aan Excel toe te voegen. In dit artikel laat ik zien hoe je een kleine invoegtoepassing maakt in Excel VBA. Het artikel bespreekt alle benodigde stappen die ik nam om de invoegtoepassing te bouwen. De principes die ik tijdens het bouwen heb gebruikt helpen jou hopelijk bij het maken van je eigen tool!

2. specificatie Een heel belangrijke stap bij het maken van software is het bepalen wat de software moet kunnen; de specificatie. Laten we eens proberen een specificatie op te stellen.

2.1.

Doel

De primaire doelstellingen van de invoegtoepassing die ik voor ogen heb zijn:  Een eenvoudige methode maken zodat de gebruiker aan zijn Excel bestand een inhoudsopgave toe kan voegen (of bijwerken);  Een hulpmiddel op het lint om het navigeren tussen werkbladen makkelijker te maken. Zoals je je kunt voorstellen is meer detail nodig voordat we ons in het programmeerwerk onder kunnen dompelen. We hebben nog meer specificaties nodig.

2.2.

Meer gedetailleerde specificatie

2.2.1. Werking van de inhoudsopgave Ik preciseer hier nader wat de invoegtoepassing zal doen  De Inhoudsopgave zal geplaatst worden op een tabblad genaamd Inhoud, beginnende in cel C2.  De tabel zal worden “Opgemaakt als tabel”  De tabel zal in kolom C een lijst met alle werkbladnamen bevatten, met in kolom D een directe snelkoppeling naar cel A1 van dat werkblad. Kolom E geeft de gebruiker om naast het werkblad een opmerking in te voegen.  Deze opmerkingen zullen behouden blijven bij het vernieuwen van de Inhoudsopgave, zodanig dat –op basis van de tabnaam– de opmerking bij het juiste tabblad blijft staan. 2.2.2. Het lint De invoegtoepassing zal een aangepaste tab op het lint maken met daarin één groep met daarop:  Een vervolgkeuzelijst die alle werkbladen zal bevatten van de actieve werkmap;  Een knop om de inhoudsopgave te maken of te vernieuwen

2.2.3. Andere functies  

Zodra de gebruiker een andere werkmap selecteert wordt de lijst met werkbladen in de vervolgkeuzelijst automatisch bijgewerkt; Als een ander werkblad wordt geselecteerd wordt deze op de vervolgkeuzelijst weergegeven.

2.3.

Is dat alles?

In “echte” softwareprojecten zijn er natuurlijk nog allerlei andere vragen die ook nog beantwoord moeten worden. In willekeurige volgorde (niet pretenderend dat deze lijst volledig is):  Zorg voor een sponsor, je moet tenslotte je rekeningen betalen;  Doe marktonderzoek (als je tenminste van plan bent om die nieuwe invoegtoepassing te verkopen. Het zou toch leuk zijn als er ook een markt voor bestaat;)  Google. Twitter. Vraag het aan de buurman. Zorg er in ieder geval voor dat je niet iets gaat bouwen dat al bestaat. De kans is aanzienlijk dat iemand jou voorging met dit idee en misschien kan je zijn invoegtoepassing gewoon gebruiken;  Planning: Wanneer moet het klaar zijn?

2.4.

Andere dingen om aan te denken

En dan hier nog een lijstje met dingen die je nodig zou kunnen hebben die eigenlijk weinig te maken hebben met de basisfunctionaliteit van je invoegtoepassing:  Ontwerpen van de gebruikersinterface;  Internationalisatie (vertalen in andere talen)  Bugs waar je omheen zal moeten werken;  Installatie;  Activatie (Mocht je van plan zijn te licenties te verkopen, hoe zorg je er dan voor dat er ook wordt betaald);  Demo versie;  Hoe zorg ik ervoor dat updates bij mijn klanten komen;

3. De basis: een lijstje met werkbladen maken op een tabblad Allereerst moeten we een nieuw werkblad invoegen met de naam “Inhoud”, tenzij dat werkblad al bestaat: If Not IsIn(Worksheets, "Inhoud") Then With Worksheets.Add(Worksheets(1)) .Name = "Inhoud" End With Set oToc = Worksheets("Inhoud") ActiveWindow.DisplayGridlines = False ActiveWindow.DisplayHeadings = False Else Set oToc = Worksheets("Inhoud") vRemarks = oToc.Range(oToc.Range("C2"), _ oToc.Range("C2").End(xlDown).End(xlToRight)).Value End If

De functie IsIn wordt hier gebruikt om te bepalen of het werkblad al bestaat en zo ja, dan wordt dat werkblad bijgewerkt met de inhoudsopgave:

Function IsIn(vCollection As Variant, ByVal sName As String) As Boolean '------------------------------------------------------------------------' Procedure : IsIn Created by Jan Karel Pieterse ' Company : JKP Application Development Services (c) 2005 ' Author : Jan Karel Pieterse ' Created : 28-12-2005 ' Purpose : Determines if object is in collection '------------------------------------------------------------------------Dim oObj As Object On Error Resume Next Set oObj = vCollection(sName) If oObj Is Nothing Then IsIn = False Else IsIn = True End If If IsIn = False Then sName = Application.Substitute(sName, "'", "") Set oObj = vCollection(sName) If oObj Is Nothing Then IsIn = False Else IsIn = True End If End If End Function

Nu het werkblad is gevonden (of ingevoegd) wijzen we dat toe aan de object variabele oTOC zodat we er in de rest van de code makkelijk mee kunnen werken: Set oToc = Worksheets("Inhoud")

Vervolgens kijken we of er al een tabel op het werkblad staat. (ListObject). Zo niet, dan maken we deze aan: If oToc.ListObjects.Count = 0 Then oToc.Range("C2").Value = "Werkblad" oToc.Range("D2").Value = "Snelkoppeling" oToc.Range("E2").Value = "Opmerkingen" oToc.ListObjects.Add xlSrcRange, oToc.Range("C2:E2"), , xlYes End If

Dan maken we de lijst leeg en plaatsen we alle werkbladnamen er weer in: On Error Resume Next oToc.ListObjects(1).DataBodyRange.Rows.Delete For Each oSh In Worksheets lRow = oSh.Index oToc.Range("C2").Offset(lRow).Value = oSh.Name oToc.Range("C2").Offset(lRow, 1).FormulaR1C1 = "=HYPERLINK(""#'""&RC[1]&""'!A1"",RC[-1])" oToc.Range("C2").Offset(lRow, 2).Value = "" For lCt = LBound(vRemarks, 1) To UBound(vRemarks, 1) If vRemarks(lCt, 1) = oSh.Name Then oToc.Range("C2").Offset(lRow, 2).Value = vRemarks(lCt, 3) Exit For End If Next Next oToc.ListObjects(1).Range.EntireColumn.AutoFit

Tevens zorgen we ervoor, dat eventuele opmerkingen die er al in stonden weer naast de juiste werkbladen worden gezet: For lCt = LBound(vRemarks, 1) To UBound(vRemarks, 1) If vRemarks(lCt, 1) = oSh.Name Then oToc.Range("C2").Offset(lRow, 2).Value = vRemarks(lCt, 3) Exit For End If Next

De hele routine vind u in het bestand dat u na de dag kunt downloaden.

4. Het lint aanpassen Voor de aanpassingen aan het lint gebruiken we de tool genaamd “Custom UI editor for Office”. U kunt dit bestand gratis downloaden van de Microsoft website. We openen de tool en openen ons Excel bestand erin:

Vervolgens kiezen we “Insert”, “Office 2007 Custom UI Part”. Een leeg venster verschijnt, waarin we onderstaande XML code plakken: <customUI xmlns=http://schemas.microsoft.com/office/2006/01/customui onLoad="rxJKPSheetToolscustomUI_onLoad">
size="large" onAction="rxJKPSheetToolsbtnInsertToc" imageMso="CustomTableOfContentsGallery" />

Deze code verzorgt het maken van de volgende tab op het lint:

Van groot belang zijn de zogeheten callbacks die in de xml staan. Bijvoorbeeld: getItemCount="rxJKPSheetToolsbtnSheets_Count"

Hiermee maak je aan Excel duidelijk, dat bij het vernieuwen van het lint de VBA routine “rxJKPSheetToolsbtnSheets_Count” moet worden aangeroepen. De Custom UI editor heeft een knop waarmee automatisch (op basis van de xml code) alle routines worden aangemaakt die een callback behoeven:

Door op deze knop te drukken wordt de volgende lijst met (blanco) routines geleverd: 'Callback for customUI.onLoad Sub rxJKPSheetToolscustomUI_onLoad(ribbon As IRibbonUI) End Sub 'Callback for rxJKPSheetToolsbtnSheets getItemCount Sub rxJKPSheetToolsbtnSheets_Count(control As IRibbonControl, ByRef returnedVal) End Sub 'Callback for rxJKPSheetToolsbtnSheets getItemLabel Sub rxJKPSheetToolsbtnSheets_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal) End Sub 'Callback for rxJKPSheetToolsbtnSheets onAction Sub rxJKPSheetToolsbtnSheets_Click(control As IRibbonControl, id As String, index As Integer)

End Sub 'Callback for rxJKPSheetToolsbtnSheets getSelectedItemIndex Sub rxJKPSheetToolsbtnSheets_getSelectedItemIndex(control As IRibbonControl, ByRef returnedVal) End Sub

4.1.

Werking Sheets dropdown en lint

Zodra het lint wordt vernieuwd wordt als eerste de callback aangeroepen die een verwijzing geeft naar het lint object: Sub rxJKPSheetToolscustomUI_onLoad(ribbon As IRibbonUI) Set moRibbon = ribbon ThisWorkbook.Worksheets("Sheet1").Range("RibbonPointer").Value = ObjPtr(moRibbon) End Sub

moRibbon is een object variabele die bovenaan de module is gedeclareerd. Dim moRibbon As IRibbonUI

Omdat een module variabele haar waarde verliest zou het vernieuwen van het lint ophouden zodra er een fout optreedt in onze code. Daarom gebruiken we een speciale truc om de verwijzing naar het lint als een getal in een cel op het werkblad van onze tool op te slaan. Bij het “invalideren” van het lint halen we zo nodig dat getal op uit het werkblad om opnieuw deze objectvariabele moRibbon de juiste verwijzing te geven. De dropdown moet gevuld worden met een lijst met werkbladen. Als het lint moet worden vernieuwd wordt als eerste de routine “rxJKPSheetToolsbtnSheets getItemCount” aangeroepen. In die routine moet worden doorgegeven aan het lint hoeveel werkbladen er in de lijst moeten worden geladen: 'Callback for rxJKPSheetToolsbtnSheets getItemCount Sub rxJKPSheetToolsbtnSheets_Count(control As IRibbonControl, ByRef returnedVal) returnedVal = ActiveWorkbook.Sheets.Count End Sub

Vervolgens zal de routine “rxJKPSheetToolsbtnSheets getItemLabel” dat aantal keer worden aangeroepen om de naam van ieder werkblad op te vragen, dus als er drie werkbladen zijn, wordt die routine drie keer aangeroepen om de naam van dat werkblad op te vragen: Public Sub rxJKPSheetToolsbtnSheets_getItemLabel(control As IRibbonControl, _ Index As Integer, ByRef returnedVal) returnedVal = Sheets(Index + 1).Name End Sub

Index is een geheel getal dat bij nul begint, maar de sheets in Excel beginnen bij 1, daarom tellen we er 1 bij op. Als een ander werkblad gekozen wordt willen we eigenlijk dat de keuzelijst dat ook aangeeft. Daarvoor zorgt de callback routine “rxJKPSheetToolsbtnSheets_getSelectedItemIndex”. Deze wordt aangeroepen zodra alle werkbladen in de keuzelijst gezet zijn: 'Callback for rxJKPSheetToolsbtnSheets getSelectedItemIndex Sub rxJKPSheetToolsbtnSheets_getSelectedItemIndex(control As IRibbonControl, _

ByRef returnedVal) returnedVal = ActiveSheet.Index - 1 End Sub

Tenslotte willen we natuurlijk dat een werkblad wordt geselecteerd als die wordt gekozen uit de keuzelijst: Sub rxJKPSheetToolsbtnSheets_Click(control As IRibbonControl, id As String, _ Index As Integer) Sheets(Index + 1).Activate End Sub

4.1.

Het lint vernieuwen

Zodra we een ander werkblad selecteren op een andere manier dan via de keuzelijst, willen we eigenlijk dat het lint zich vernieuwd, zodat het juiste werkblad ook te zien is op die keuzelijst. Ook als we van bestand veranderen moet er iets gebeuren, want we willen dan de lijst met werkbladen geactualiseerd hebben. We gebruiken hiertoe een klassemodule genaamd “clsApp” met daarin de volgende code: Option Explicit Public WithEvents App As Application Private Sub App_SheetActivate(ByVal Sh As Object) InvalidateRibbon End Sub Private Sub App_WorkbookActivate(ByVal Wb As Workbook) InvalidateRibbon End Sub Private Sub App_WorkbookOpen(ByVal Wb As Workbook) InvalidateRibbon End Sub Private Sub Class_Terminate() Set App = Nothing End Sub

Iedere keer dat hetzij een ander werkblad wordt geselecteerd, hetzij een ander bestand, wordt nu de routine “InvalidateRibbon” aangeroepen, die ervoor zorgt dat het lint wordt vernieuwd. De routine InvalidateRibbon ziet er als volgt uit: Sub InvalidateRibbon() On Error Resume Next GetRibbonObjectReference.Invalidate End Sub

De werking van GetRibbonObjectReference wordt hier verder niet uitgelegd. Om deze klasse module haar werk te laten doen is wat code in een normale module noodzakelijk, de module modInit: Option Explicit

'Variable to hold instance of class clsApp Dim mcApp As clsApp Public Sub Init() 'Rest mcApp in case it is already loaded Set mcApp = Nothing 'Create a new instance of clsApp Set mcApp = New clsApp 'Pass the Excel object to it so it knows what application 'it needs to respond to Set mcApp.App = Application End Sub

De routine “Init” wordt aangeroepen vanuit de Workbook_Open routine die zich in de ThisWorkbook module bevindt: Option Explicit Private Sub Workbook_Open() Application.OnTime Now, "Init" End Sub

Nu hebben we alle code die nodig is om onze tool haar werk te laten doen.

5. Invoegtoepassing maken Nu alle code is geschreven is het tijd om de invoegtoepassing te maken. Dit is de simpelste stap van allemaal: Bestand, Opslaan Als en dan het juiste bestandstype kiezen:

Zodra “Excel-invoegtoepassing” is gekozen springt Excel automatisch naar de map met invoegtoepassingen (rechter rode kader). Je kan simpel terug naar de map waar het “originele” bestand staat door op de terug-knop te klikken (linker rode kader):

6. Conclusie Er komt heel wat kijken bij het maken van een invoegtoepassing. Slechts 20 procent van het werk is het schrijven van de code die het werk doet (het maken van de lijst). 80 procent gaat “verloren” met het maken van de gebruikersinterface.

7. Copyright Niets uit dit artikel mag worden vermenigvuldigd of anderszins verspreid zonder voorafgaande schriftelijke toestemming van JKP Application Development Services.