wimgielis.com

Springen naar een VBA module

• 07/08/2011

Inleiding

Dit artikel is bedoeld om zeer interessante VBA code te tonen, die VBE objecten kan benaderen. De uitmuntende referentiepagina omtrent alles wat met de VBE te maken heeft (dus daar waar we VBA code schrijven), is die van Chip Pearson. Op die pagina kan je heel veel nuttige zaken lezen en uitproberen, niettemin zijn er nog steeds af en toe nieuwigheden in de VBE te ontdekken.

De VBE programmatorisch benaderen

Op MrExcel was er de vraag of we met een macro kunnen springen naar een bepaalde module in de VBE: hier. Nalani wenste te weten hoe we een module naam kunnen kiezen, en vervolgens met een macro rechtstreeks in VBE kunnen gaan en de gekozen module selecteren. Dit kan wel handig zijn bij een groot project met honderden modules bijvoorbeeld.

Wat wel geweten is, is dat je kan springen naar een procedure: dit doe je via de gewone F5 toets (of Ctrl + G) en dan bij Referentie geef je de naam van de procedure in:

Druk dan op OK. In VBA code levert dit het volgende stukje op:

		
Sub SpringNaarProcedure()
    Application.GoTo "MijnProcedure"

End Sub

Je raadt het uiteraard al: je kan met deze code direct de procedure openen, maar niet een module. Daar gaan we creatief te werk moeten gaan. Het plan is om te bepalen wat de naam van de eerste procedure binnen de gekozen module is, en dan naar die procedure te springen.

Er stellen zich 2 problemen die we in de code moeten opvangen. Indien er geen procedures in de gekozen module zijn (de module is leeg), dan kunnen we uiteraard niet naar de eerste procedure in de module springen. Ons plan mislukt. Maar we kunnen wel code schrijven om een tijdelijke procedure in te voegen, daar naartoe springen, en vervolgens de tijdelijke procedure weer verwijderen.

Probleem 2 stelt zich wanneer de gekozen module niet bestaat in het VBProject. Dan gaan we code moeten schrijven die de module aanmaakt als een lege module. Daarna volgen we dezelfde stappen als hierboven.

' Wim Gielis
' https://www.wimgielis.com
'''''
' VBA code to open the VBE at a certain module
' 07/08/11
'''''
Sub SpringNaarModule()
    On Error Resume Next
    Dim ProcKind As VBIDE.vbext_ProcKind
    sModule = InputBox("Geef de naam van de module op", "Module naam", "Module1")
    With ActiveWorkbook.VBProject
        If Len(.VBComponents(sModule).Name) = 0 Then .VBComponents.Add(vbext_ct_StdModule).Name = sModule
        With .VBComponents(sModule).CodeModule
            If .CountOfLines > .CountOfDeclarationLines Then
                'er is ten minste 1 procedure in de module
                Application.Goto .ProcOfLine(.CountOfDeclarationLines + 1, ProcKind)
            Else
                'er zijn geen procedures in de module
                .InsertLines 1, "Sub TestProc()"
                Application.Goto "TestProc"
                .DeleteLines 1
            End If
        End With
    End With

End Sub

Inhoud van de procedure

De code in deze procedure doet het volgende:

1. Vraag de naam van de module aan de gebruiker.
2. Test of die module bestaat. Indien niet, maak aan.
3. Binnen de code module, test of er na de declaratieregels nog meer regels code staan. Hiermee testen we dus of er effectief 1 of meer procedures in de module zijn.
4. Als er procedures gevonden zijn, spring naar de eerste procedure in die module.
5. Vinden we geen procedures, zoals in vorige paragrafen al aangehaald: maak een nieuwe tijdelijke procedure aan, spring er naartoe en verwijder de procedure weer.

De (nog) wat ingekorte versie van bovenstaande is als volgt (o.a. er wordt geen nieuwe module aangemaakt als die nog niet bestaat):

Sub SpringNaarModule_korter()
    On Error Resume Next
    Dim ProcKind As VBIDE.vbext_ProcKind
    mMod = InputBox("Please provide the module name", "", "Module1")
    With ActiveWorkbook.VBProject.VBComponents(mMod).CodeModule
        If .CountOfLines > .CountOfDeclarationLines Then
            Application.Goto .ProcOfLine(.CountOfDeclarationLines + 1, ProcKind)
        Else
            .InsertLines 1, "Sub TestProc()"
            Application.Goto "TestProc"
            .DeleteLines 1
        End If
    End With

End Sub

Referentie

Voor bovenstaande code: vergeet niet in VBA een referentie te leggen naar Microsoft Visual Basic For Applications Extensibility 5.3. Zorg er ook voor dat je toegang hebt tot het VBProject via VBA code: meer uitleg op de site van Chip Pearson (cf. supra).

Dit is zeker 1 van de leukere stukken code van de laatste tijd, hopelijk leren we er allemaal wat van.

Toevoeging

UPDATE: Snb bestudeerde bovenstaande code en wilde deze suggestie nog doen, waarvoor dank. Het is inderdaad beter om deze code te gebruiken dan de bovenstaande. Zo is het robuuster aangezien, indien een bereik genaam TestProc bestaat in het bestand, Application.GoTo daar naartoe zal springen. En niet naar de VBA procedure. Al is de kans uiteraard klein.

Private Sub CommandButton1_Click()
    On Error Resume Next
    c01 = InputBox("Geef de naam van de VBA-module")
    With ActiveWorkbook.VBProject
        .VBComponents(c01).Activate
        If Err.Number <> 0 Then
            .VBComponents.Add(vbext_ct_StdModule).Name = c01
            .VBComponents(c01).Activate
        End If
    End With

End Sub

• « Vorig Excel artikel
• Volgend Excel artikel »