MS Excel - Macro's.

Excel Macro's.

Om in Excel met Macro's, hetzij opgenomen via de Macro-recorder, hetzij via eigen ontwerp in de VBE editor, moet de Developer Tab in de Excel-interface zichtbaar zijn. En dit via volgende stappen:

  1. Kies in Menu File -> Options
  2. Kies in Excel Options -> Customize Ribbon
  3. Kruis de Developer checkbox aan.

Vervolgens moet het Excel-werkboek als Excel macro-enabled werkboek met de extensie xlsm" worden bewaard. Om de VBA editor te openen Kies DEVELOPER -> Visual Basic (Alt+ F11). Macro's worden ondergebracht in een module en zijn van het Sub type.
Case: voeg een nieuwe module toe en hernoem deze tot basMijnMacros. Voeg in de module een procedure toe en noem deze sMijnMacro1 met de volgende code: 

                Sub sMijnMacro()
            
                    ActiveCell.FormulaR1C1 = 'Mijn naam is haas''
                  ' deze Macro schrijft in de actieve cel van het werkblad 'Mijn naam is haas'.
            
                End Sub

Ga vervolgens naar een Excel werkblad en selecteer een cel. Druk op Macros onder de DEVELOPER tab, hier vindt men sMijnMacro terug, selecteer de Macro en druk op Run om de macro uit te voeren. Opgepast Een uitgevoerde Excel-macro kan niet ongedaan worden.

Top

Veiligheid

Wanneer men een werkboek met macro's opent bericht Excel dat de macro's uitgeschakeld zijn. Men moet er zelf voor kiezen om de macro's in te schakelen, dit wanneer men zeker is dat het werkboek niet van verdachte afkomst is. Wil men dit mecanisme voor macro's uitschakelen dan moet men het werkboek bewaren in een map 'Trusted location' . Ga daarbij als volgt tewerk:

  1. Kies de Macro Security knop op de DEVELOPER tab
  2. Dit activeert het Trust Center dialoogkader
  3. Klik de Trusted Locations knop, men krijgt een overzicht van alle mappen die als veilig beschouwd worden.
  4. Klik op de Voeg nieuwe map toe
  5. Blader naar de gewenst map

Werkboeken die in deze map bewaard worden openen automatisch met actieve macro's

Top

Willekeurige datum tussen twee datums.

Deze functie maakt gebruik van een van de vele Worksheet-functies die Excel rijk is.

            Public Function fRandomDatum(startDatum As Date, eindDatum As Date) As Date
                Dim RandomDatum As Date
                RandomDatum = WorksheetFunction.RandBetween(startDatum, eindDatum)
                fRandomDatum = Format(RandomDatum, "dd/mm/yyyy")
            End Function
Top

Een datum vertalen naar kwartaal.

            Public Function fVertaalInKwartaal(datDatum As Date) As String
                Dim intMaand As Integer
                Dim strkwartaal As String
                intMaand = Month(datDatum)
            
                Select Case intMaand
                    Case Is < 4
                        strkwartaal = "Kwartaal 1"
                    Case 4 To 6
                        strkwartaal = "Kwartaal 2"
                    Case 7 To 9
                        strkwartaal = "Kwartaal 3"
                    Case Is > 9
                        strkwartaal = "Kwartaal 4"
                End Select
            fVertaalInKwartaal = strkwartaal
            End Function
Top

Willekeurig getal tussen onder- en bovengrens

            Public Function fWilgetal(lngmin As Long, lngMax As Long) As Long
                Maakt gebruik van Worksheet-function
                fWilgetal = WorksheetFunction.RandBetween(lngmin, lngMax)
            End Function
Top

Worksheet functie via code in Range invoegen

Om een Worksheet-functie via code in een Range in te voegen gebruikt men volgende syntax

Waar x en y the coordinaten zijn relatief aan de Range waar de Formule moet ingevoerd worden
x is het aantal rijen rechts van de Formule-Range, is x negatief dan links van de Formule-Range
y is de kolom onder de Formule-Range, is y negatief boven.
De volgende code brengt een waarde in respectievelijk de cellen A1 en B1 en de Productfunctie in cel C1 

            Sub product()
            Dim shtTest As Excel.Worksheet
                Set shtTest = ThisWorkbook.Worksheets("Sheet4")
                shtTest.Range("A1") = 25
                shtTest.Range("B1") = 4
                shtTest.Range("C1").FormulaR1C1 = "=PRODUCT(RC[-2],RC[-1])"
            End Sub
Top

Kolomnummers omzetten naar Letters

In een scenario waar men bijvoorbeeld via de Range.Offset methode verticaal naar cellen met een waarde zoekt is het vrij eenvoudig het aantal kolommen te tellen maar soms wil men deze omzetten naar de cijferaanduiding in Excel.
Ik heb daartoe hier een functie gevonden die ik aangepast heb voor persoonlijk gebruik.

            Public Function KonverteerNrLetter(intKol As Integer) As String
            'functie maakt gebruik van VBA Chr() functie Chr(65) = A Chr(90) = Z 
                Dim intAlfa As Integer
                Dim intRest As Integer
                intAlfa = Int(intKol / 27) ' geeft 0 tem kolom 26 letter Z 
                intRest = intKol - (intAlfa * 26)
                If intAlfa > 0 Then
                    KonverteerNrLetter = Chr(intAlfa + 64)
                End If
                If intRest > 0 Then 'vanaf kolom 27
                    KonverteerNrLetter = KonverteerNrLetter & Chr(intRest + 64)
                End If
            End Function
Top

Unieke waarden genereren

Veronderstel een tabel met hoofdingen. In bepaalde kolommen komen de dezelfde data-waarden meerdere malen voor.
Graag wil met de unieke waarden uitfilteren. Dit kan zowel per kolom of op meerdere kolommen.Kiest men bijvoorbeeld 2 kolommen dan gaan de unieke gegeven per rij weergeven worden. Men kan dit verwezenlijken via de Data tab van de Ribbon.
Kies Data en vervolgens

Advance filter
Kies de optie Copy to another location
Duidt een range waar moet op gefilterd worden
Kies waar de gefilterde data moet geplaatst worden
Duidt Unique records aan.

Hetzelfde resultaat kan ook via volgende code.

            Public Sub sUniekeWaarden()
            Dim rngTeFilteren
            Dim rngGefilterd
                Set rngTeFilteren = ThisWorkbook.Worksheets("FilterUniek").Range("A1:B500")
                Set rngGefilterd = ThisWorkbook.Worksheets("FilterUniek").Range("D1")
            
                rngTeFilteren.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=rngGefilterd, Unique:=True
            End Sub
Top

hallo