Macro’s en VBA¶
VBA is bedoelt om het werken met Excel (of een andere Office toepassing) makkelijker te maken.
Heb je nog nooit van een formule gehoord, of wat een Macro doet en waarvoor je hem gebuikt, dan is deze pagina niet de juiste plek voor jou.
Misschien moet je eerst wat meer ervaring opdoen met Excel, door bijvoorbeeld deze handleindingen te volgen.
Hieronder een aantal tips voor Excel Macro’s en Visual Basic (VBA).
Ik gebruik Office 2007, het kan dus zijn dat in andere versies de handleiding niet helemaal klopt.
Commentaar toevoegen¶
Ik plaats altijd een stukje uitleg bij mijn code.
In VBA termen heet dit commentaar.
Dit is om de code voor mijzelf, en eventueel anderen, makkelijk leesbaar te houden.
Dit kun je doen direct tijdens het maken van de code of na afloop.
Commentaar zet je in de code door een
' (accent grave) voor de regel te plaatsen.Voorbeeld:
Sub Vervang()
'Vervang de punt door een spatie
ActiveSheet.Columns("A").Replace _
What:=".", Replacement:=" ", _
SearchOrder:=xlByColumns, MatchCase:=True
End Sub
Je kunt het commentaar teken ook gebruiken als je tijdens het testen van je code maar een stukje wilt testen.
Ga naar de eerste pagina (Word)¶
Dit is een macro om snel en naar de eerste pagina van je document te gaan:
Sub Pagina1()
'Ga naar de eerste pagina van je document
Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Name:="1"
End Sub
Kraak het wachtwoord van een Excel werkblad¶
Soms is een werkblad beveiligd met een wachtwoord.
Mocht je dit wachtwoord willen verwijderen (omdat je het vergeten bent).
Je kunt dan onderstaande VBA code gebruiken:
Sub WachtwoordCrack()
Dim a, b, c, d, e, f, g, h, i, j, k, m As Integer
begin = TimeValue(Time)
On Error Resume Next
For a = 65 To 66: For b = 65 To 66: For c = 65 To 66
For d = 65 To 66: For e = 65 To 66: For f = 65 To 66
For g = 65 To 66: For h = 65 To 66: For i = 65 To 66
For j = 65 To 66: For k = 65 To 66: For m = 32 To 126
ActiveSheet.Unprotect Chr(a) & Chr(b) & Chr(c) & _
Chr(d) & Chr(e) & Chr(f) & Chr(g) & Chr(h) & _
Chr(i) & Chr(j) & Chr(k) & Chr(m)
If ActiveSheet.ProtectContents = False Then
einde = TimeValue(Time)
duur = Format(einde - begin, "hh:mm:ss")
MsgBox "Werkblad is wachtwoord-vrij." & Chr(10) & "in: " &
Chr(10) & Chr(10)
Exit Sub
End If
Next: Next: Next
Next: Next: Next
Next: Next: Next
Next: Next: Next
End Sub
Lettertype aanpassen (Word)¶
Pas het lettertype aan naar je eigen wensen.
De dingen die je niet nodig hebt, hoef niet te vermelden in de macro:
Sub Lettertype()
' Lettertype Aanpassen
Selection.WholeStory
Selection.Font.Name = "Arial"
Selection.Font.Size = 12
Selection.Font.Underline = True
Selection.Font.Bold = False
Selection.Font.Italic = False
Selection.Font.StrikeThrough = False
Selection.Font.Subscript = False
Selection.Font.Superscript = False
Selection.Font.ColorIndex = wdBlue
Selection.Fields.Unlink 'Verwijder alle hyperlinks
End Sub
Maak een lijst van je werkbladen¶
Dit is een macro om snel een lijst te maken van alle werkbladen in het actieve werkboek van Excel:
Sub ListSheetNames()
'Maak van alle werkbladen een lijst in het actieve werkboek vanaf de geselecteerde cel
Dim R As Range
Dim WS As Worksheet
Set R = ActiveCell
For Each WS In ThisWorkbook.Worksheets
R.Value = WS.Name
Set R = R(2, 1)
Next WS
End Sub
Maak jaar en maandbladen (Excel)¶
Dit zijn een paar stukken code om snel en makkelijk extra werkbladen te maken die je een jaar lang kunt gebruiken.
Ik heb drie verschillende versies gemaakt.
52 lege weekbladen:
`Maak 52 lege werkbladen voor elke week 1.
Sub Maaknieuweweekmap()
For wknr = 1 To 52
With ActiveWorkbook.Sheets.Add(After:=Worksheets(Worksheets.Count))
.Name = "Week " & wknr
End With
Next
End Sub
52 werkbladen die allemaal een kopie van blad 1 zijn:
`Maak 52 werkbladen die allemaal een kopie van blad 1 zijn
Sub Gebruikvoorbeeld()
Dim werkbl As Worksheet
Set werkbl = ActiveSheet
For wknr = 1 To 52
werkbl.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Week " & wknr
Next
werkbl.Activate
End Sub
Maak 31 werkbladen voor de dagen van de maand:
`Maak 31 werkbladen voor de dagen van de maand
Sub Dagen()
Dim werkbl As Worksheet
Set werkbl = ActiveSheet
For dagnr = 1 To 31
werkbl.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Dag " & dagnr
Next
werkbl.Activate
End Sub
Maak werkbladen van een lijst¶
Maak van een lijst werkbladen in het actieve werkboek:
Sub CreateSheetsFromAList()
'Als je veel werkbladen in een werkboek moet maken is dit een snelle methode om dat te doen.
'Maak in Blad1 een lijst met namen en gebruik deze macro om deze naar werkbladen om te zetten.
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("Blad1").Range("A1") 'Naam blad, en eerste cel
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'Maakt een nieuw werkblad
Sheets(Sheets.Count).Name = MyCell.Value ' Hernoemt het werkblad.
Next MyCell
End Sub
Toevoegen van een automatisch doortellend nummer (Excel)¶
Dit kun je bijvoorbeeld gebruiken bij het maken van facturen.
VBA code:
Sub NieuwNummer()
`Vervang "A1" door het celnummer waar het nummer moet komen.
Range("A1").Value = Range("A1").Value + 1
End Sub
Nu moet je ook nog een knop maken zodat je de macro makkelijk kan gebruiken.
Tab Ontwikkelaars
Invoegen
Formulierbesturingselementen
Knop (eerste knop)
Teken op het Excel sheet de plaats waar de knop moet komen
Kies Macro NieuwFactuurnummer
Ok
Knop wordt gemaakt
Klik met rechtermuiskinop op de knop
Tekst bewerken
Verander de naam in Nummer toevoegen
Verander eventueel de grootte van de knop
Als je nu op de knop klikt zul je zien dat het getal in cel A1 met 1 is verhoogd.
Verklein alle images naar hetzelfde formaat (Word)¶
Met deze macro verklein je in een keer alle images naar het gewenste formaat.
Vervang in Height en Width de maten die je hebben wilt:
Sub Verklein_Images
' Verklein alle images naar de gewenste maat
Dim i As Long
With ActiveDocument
For i = 1 To .InlineShapes.Count
With .InlineShapes(i)
.Height = 141.75 ' Hoogte resultaat
.Width = 188.2 ' Breedte resultaat
End With
Next i
End With
End Sub
Vervang tekst in een document¶
Ik weet dat met Zoeken/Vervangen je tekst makkelijk en snel kunt vervangen, maar als je vaak dezelfde tekst in een document moet vervangen is het handig om daar een macro van te maken.
Ik vervang hier het woord piet door Piet de Groot:
Sub VervangTekst()
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "piet" 'Zoek naar deze tekst
.Replacement.Text = "Piet de Groot" 'Vervang door deze tekst
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub
Verwijder hyperlinks (Word)¶
Met deze macro verwijder je alle hyperlinks uit een Word document:
Sub RemoveHyperlinks()
' Verwijder alle hyperlinks in een document
With ActiveDocument
Do Until .Hyperlinks.Count = 0
.Hyperlinks(1).Delete
Loop
End With
End Sub
.. index:: VBA Excel; Voeg tekst toe
Voeg tekst toe aan het begin van een cel (Excel)¶
Met deze macro kun je tekst die in een cel staat aanpassen door er tekst voor of achter te zetten.
Je kunt de tekst natuurlijk vervangen of toevoegen.
Voeg tekst toe vooraan een cel:
Sub VoorToevoegen()
'voeg tekst toe vooraan een cel
For Each cl In ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)
cl.Value = "....." & cl.Value
'vervang punten door spaties of een woord/letters
Next
End Sub
Voeg tekst toe achteraan een cel:
Sub AchterToevoegen()
'voeg tekst toe achteraan een cel
For Each cl In ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)
cl.Value = cl.Value & "....."
'vervang punten door spaties of een woord/letters
Next
End Sub
Zoek de actieve printer¶
Soms heb je meerdere printers tot je beschikking, en gebruik je deze ook.
Hiermee kun je makkelijk zien wat je actieve printer is:
Sub TestActivePrinter()
MsgBox "De naam van de actieve printer is: " & _
Application.ActivePrinter
End Sub
