Pagina's

Totaaloverzicht

De functie Aantal.als op een niet aaneengesloten bereik

22 februari 2016 | onderwerpen: excel, vba, visual basic

De Excel functie berekent het aantal keren dat een bepaalde waarde in en opgegeven bereik voorkomt. Bijvoorbeeld =AANTAL.ALS(A1:A9;B1) levert het aantal keren dat de cellen A1 t/m A9 de inhoud van cel B1 bevat.

Als het opgegeven bereik niet aaneengesloten is, schiet Excel in de fout. Stel dat je cellen A2, A4, A6 e A8 niet wilt mee laten nemen (=AANTAL.ALS((A1;A3;A5;A7;A9);B1)) geeft Excel een foutwaarde: #WAARDE.

Om dit op te lossen biedt een zelf geschreven functie uitkomst:

Function BepaalAantal(Bereik As Range, waarde As String) As Long
Dim cl
BepaalAantal = 0
For Each cl In Bereik
If cl.Value = waarde Then BepaalAantal = BepaalAantal + 1
Next cl
End Function

De functie controleert van alle cellen in het opgeven bereik of de waarde correspondeert met de gevraagde waarde. In ons voorbeeld lever dit: =BepaalAantal((A1;A3;A5;A7;A9);B1)

In het download-bestand is een voorbeeld van de functie opgenomen.

Totaaloverzicht

De kracht van het Change Worksheet commando

11 maart 2015 | onderwerpen: excel, vba, visual basic

Automatische aanpassingen aan een Excel werkblad / worksheet of aan een heel Excel bestand zijn eenvoudig uit te voeren met het Change commando in Visual Basic. Je kunt daarbij een macro laten draaien nadat een bepaalde cel is gewijzigd. Pas bijvoorbeeld de keur van cellen aan, spring naar een ander deel van je bestand, laat berekeningen uitvoeren etc. etc. De mogelijkheden zijn eindeloos.

De code heeft deze opzet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column=1
Target.Font.ColorIndex = 5
End If
End Sub

In het te downloaden bestand (zie de rechterkolom) vind je een invulformulier met een aantal gegevens die van elkaar afhankelijk zijn:

  • aantal woningen
  • woninggrootte
  • marktwaarde per m2
  • marktwaarde per woning
  • totale marktwaarde.

Je kunt de berekeningen natuurlijk met formules doen,  maar soms weet degene die het formulier moet invullen alleen de marktwaarde per m2 of alleen de totale marktwaarde. Dan is het Change-commando ideaal. Degene die de gegevens invult hoeft dan niet zelf de berekeningen te doen. Bijkomend voordeel is dat het formulier geen formules bevat die per ongeluk verwijderd konden worden.

Na aanpassen van bijvoorbeeld de marktwaarde per m2, worden de marktwaarde per woning en de totale marktwaarde automatisch aangepast. Verandering van de woninggrootte leidt automatisch tot aanpassing van alle marktwaarde variabelen.

Loop

Met een eenvoudige toevoeging van het commando Application.EnableEvents = False voorkom je dat de automatische aanpassingen in een oneindige loop terecht komen.

Vragen?

Heb je vragen? Neem dan contact met mij op!

Mer informatie vind je bijvoorbeeld hier en hier.

Totaaloverzicht

Totaaloverzicht

11 maart 2015 | onderwerpen: excel, vba, visual basic, data-analyse

Het kan handig zijn om de gegevens die je in Excel op een aantal werkbladen hebt ingevoerd samen te voegen op een verzamelblad. En wel zo dat aanpassingen op een van de bladen automatisch worden doorgevoerd op het verzamelblad. Ook hier biedt een klein aantal macro-regels de oplossing.

Kijk in het voorbeeldbestand!

Sub TotaalOverzicht()
Dim sh As Integer, x As Long, LastRow As Long
Dim rng As Range
x = 16
Range(“D16:G1000”).ClearContents
For sh = 1 To Sheets.Count – 1
Set rng = Sheets(sh).Cells
LastRow = Sheets(sh).Cells(rng.Rows.Count, “A”).End(xlUp).Row
Sheets(sh).Range(“A2:D” & LastRow).Copy
Sheets(Sheets.Count).Select
ActiveSheet.Paste Destination:=Cells(x, 4)
x = x + LastRow – 1
Next sh
End Sub

Vragen?

Heb je vragen? Neem dan contact met mij op!

ExcelMeester

Excel & Visual Basic

TIPS & TRUCS

Ik ontwikkel onder andere rekenmodellen in Excel en Visual Basic. Daarbij gebruik ik de vele mogelijkheden die Excel biedt: rekenfuncties, grafieken, analysetools als draaitabellen, formulieren, etc. De kennis die ik daarbij heb opgedaan en nog steeds opdoe, gebruik ik ook in bijdragen op Twitter (@jeroenneele) en in Excel groepen op LinkedIn (bijvoorbeeld deze (NL) en deze (ENG)).

Neem contact met me op voor het:

  • verbeteren van uw Excelbestanden: aanpassen, samenvoegen, beveiligen,
  • automatiseren van terugkerende handelingen met macro’s in Visual Basic,
  • opstellen van rapportages (draaitabellen | powerpivot | powerquery),
  • schrijven van formules zodat ingewikkelde berekeningen teruggebracht worden tot één formule,
  • opstellen van scenarioberekeningen zodat u betere beslissingen kunt nemen.

AANTAL ALS 

Een variant op de Aantal.als functie van Excel in Vba die ook werkt voor een niet aaneengesloten bereik. Excel geeft daarvoor een foutmelding.

Lees verder >>

Excel vba aantal als

Change Worksheet commando

Aanpassingen aan een Excel werkblad / worksheet of aan een heel Excel bestand zijn eenvoudig uit te voeren met het Change commando in Vba.

Lees verder >>

Excel Vba Worksheet.Change

Totaaloverzicht

Voegt de inhoud van zes werkbladen samen tot één totaaloverzicht. Geen knippen en plakken, maar met een paar macroregels.

Lees verder >>

Totaaloverzicht

Ontdubbelen 2 kolommen

Verwijdert de gegevens die in twee kolommen voorkomen. Geen handwerk meer, maar een korte macro doet het werk voor u.

Lees verder >>

Ontdubbelen in Excel

Filter op geselecteerde cellen

Snel filteren op de inhoud van geselecteerde cellen kan met deze tool. Het werkt zowel voor tekst als getallen. Met voorbeeldbestand.

Lees verder >>

Filteren op inhoud geselecteerde cellen

Tekstbox (on)zichtbaar maken

Met een korte macro kun je een tekstbox zichtbaar of onzichtbaar (toggle) maken. Bevat een voorbeeldbestand.

Lees verder >>

TextBox Zichtbaar maken

Specifieke tekst vet maken

Deze macro maakt een combinatie van letters en/of cijfers binnen cellen in Excel vet. Met voorbeeldbestand.

Lees verder >>

Excel Vba Specifieke tekst vet maken

Som meerdere tabbladen

Met een zelfgeschreven somfunctie is een flexibele totaaltelling van een cel op meerder tabbladen mogelijk.

Lees verder >>

Excel som meerdere tabbladen

Aantal weekdagen

Tellen van het aantal weekdagen in een bepaalde maand met behulp van een zelfgeschreven functie.

Lees verder >>

Excel functie aantal weekdagen

Getalsnotatie 24 uur

Optellen van aantallen uren vraagt in Excel bij waarden van meer dan 24 uur om een speciale formaat van de cel.

Lees verder >>

Excel Datum Formaat

Dynamische grafiek

Maak gebruik van namen met een dynamisch bereik zodat figuren zich automatisch aanpassen.

Lees verder >>

Excel Dynamisch Grafiek

Onderdrukken DELETE-knop

Hoe voorkom je met een scriptje in Visual Basic het gebruik van de delete knop? Het lijkt moeilijker dan het is.

Lees verder >>

Excel Delete

Staafgrafiek bandbreedte

Maak een een dubbel gestapelde staafgrafiek met twee variabele waarden. Volg het stappenplan en je kunt het zelf!

Lees verder >>

Bandbreedtegrafiek0

Op 1 na meest voorkomend?

Gerbuik een matrixfunctie om de één na meest voorkomende waarde in een databestand op te sporen.

Lees verder >>

excel modus

Frequentieverdeling met klassen

In één oogopslag heb je een beeld van de verdeling van het totale bezit ten opzichte van de huurprijsklassen.

Lees verder >>

grafiek-klein

Specifieke tekst zoeken in Excel

Vind met de Reguliere Expressie in Visual basic elke specifieke tekst in Excel. Een uitkomst voor niet eenduidige databestanden.

Lees verder >>

Specifiek zoeken

TextBox Zichtbaar maken

Textbox (on)zichtbaar maken

15 november 2014 | onderwerpen: excel, vba, visual basic

Een vraag op LinkedIn ging over het zichtbaar en onzichtbaar (toggle) maken van een textbox.

Bijgaand voorbeeldbestand geeft hiervan een voorbeeld. Aan de “LEES MIJ”- knop zit een klein aantal macro-regels verbonden. Deze zorgen ervoor dat de tekstbox zichtbaar of zichtbaar wordt gemaakt.

Zet daarvoor in de het macroblad (Alt-F11) van het betreffende blad deze code:

Private Sub CommandButton1_Click()
If ActiveSheet.Shapes(“Tb.1”).Visible = False Then
ActiveSheet.Shapes(“Tb.1”).Visible = True
Else
ActiveSheet.Shapes(“Tb.1”).Visible = False
End If
End Sub

Excel Vba Specifieke tekst vet maken

Specifieke tekst vet maken

10 november 2014 | onderwerpen: excel, vba, opmaak, tekst, zoeken

Op LinkedIn verscheen een vraag over het vet maken van delen van een tekst binnen cellen in Excel.

Omdat het om veel cellen ging, zou handmatig vet maken veel tijd kosten. Onderstaande vba code doet het in een handomdraai!

Selecteer het gebied waarbinnen je wilt zoeken naar een bepaalde combinatie van letters en/of cijfers. Activeer daarna de macro “MaakVet”.

Wat de macro doet is het volgende:

  1. Er verschijnt een invoerscherm waarin je tekst opgeeft die je vet en rood wilt maken
  2. De macro loopt alle cellen af binnen het geselecteerde bereik en zoekt naar de opgegeven tekst
  3. Niet gevonden? Zet de opmaak van de tekst op normaal
  4. Wel gevonden? Maakt alleen de opgegeven tekst binnen de cel vet en rood.

Sub MaakVet()
 Application.ScreenUpdating = False
 Dim txt As String
 Dim first As Integer
 Dim rCell As Range
 Dim rRng As Range
 txt = InputBox("What text do you want to make bold & red?")
 Set rRng = Selection
 For Each rCell In rRng.Cells
 Range(rCell.Address).Select
 first = InStr(ActiveCell.Text, txt)
 ActiveCell.Font.ColorIndex = xlAutomatic
 ActiveCell.Font.Bold = False
 If first <> 0 Then
 ActiveCell.Characters(Start:=first, Length:=Len(txt)).Font.Bold = True
 ActiveCell.Characters(Start:=first, Length:=Len(txt)).Font.Color = -16776961
 End If
 Next rCell
End Sub
Filteren op inhoud geselecteerde cellen

Filteren op geselecteerde cellen

17 november 2014 | onderwerpen: excel, vba, visual basic, data-analyse

Excel heeft met de filter-functie een handige tool. Maar filteren op één of meerdere waarden vraagt bij een groot bestand met veel verschillende waarden om veel handelingen:

  1. activeren filter-tool
  2. uitvinken van ‘Alles selecteren’
  3. aanvinken van de juiste items na zoeken en scrollen

Dat kan sneller!

In plaats van zoeken naar items die wilt filteren in een soms lange lijst, is het makkelijker en sneller als je kunt filteren op de cellen die je in je werkblad selecteert. Dat kan er één zijn, maar ook meerdere tegelijk. Met deze tool kan dat. Zowel voor tekstkolommen als voor getalkolommen. En ook na aanvullen van de tabel met nieuwe gegevens.

Video: Filteren op inhoud geselecteerde cellen
Klik voor vergroting
Filteren op geselecteerde cellen
 
 

Private Sub FilterButton_Click()
 Application.ScreenUpdating = False
 
 Dim r As Long, f As Long, veld As Byte
 Dim SingleCell As Excel.Range
 Dim ar As String, st As String, v As String, bereik As String
 
 'geselecteerde cellen opnemen in array
 For Each SingleCell In Selection.Cells
 v = SingleCell.Text 'gebruik Text i.p.v. Value voor de getallen
 st = st & v & "-"
 Next
 st = Mid(st, 1, Len(st) - 1)
 Arr1 = Split(st, "-")
 
 'selecteren van de tabel op het werkblad
 bereik = Range("tbl.naam").Value
 veld = ActiveCell.Column - (Range(bereik).Column - 1) 'kolomnummer binnen de tabel
 r = Range(bereik).Rows.Count 'aantal rijen van de tabel
 f = Range(bereik).SpecialCells(xlCellTypeVisible).Rows.Count 'aantal van de zichtbare rijen van de tabel
 
 'check of de cel binnen de tabel valt
 If r > f Then
 ActiveSheet.Range(bereik).AutoFilter
 Else
 If Not Intersect(ActiveCell, Range(bereik)) Is Nothing Then
 ActiveSheet.ListObjects(bereik).Range.AutoFilter Field:=veld, Criteria1:=Arr1, Operator:=xlFilterValues
 Else
 MsgBox "Selecteer cellen in de tabel", vbExclamation, "ExcelMeester | Jeroen Neele"
 End If
 End If
End Sub
Excel som meerdere tabbladen

Som van meerdere tabbladen

28 oktober 2014 | onderwerpen: excel, vba, visual basic, functie

Op LinkedIn verscheen een vraag over een som van meerdere tabbladen.

“Ik wil in meerdere tabbladen cel q4 sommeren. Dit lukt met de formule =SOM(‘===>>>:<<<==='!Q4).
Hierbij is ===>>> het eerste en <<<=== het laatste tabblad. De tabbladen hiertussen worden geteld.
Nu wil ik de verwijzing Q4 variabel maken met verticaal zoeken (afhankelijk van maand en jaartal moet worden verwezen naar andere cel). Ik heb dit geprobeerd met de formule INDIRECT en ook met TEKST.SAMENVOEGEN. Geen van beide geeft het gewenste resultaat.”

De functie INDIRECT werkt bij deze somfunctie inderdaad niet.

Oplossing met functie

Met een somfunctie is een flexibele totaaltelling met een zelf toegevoegde functie mogelijk. Zet daarvoor onderstaande code in een module (Alt-F11) in het bestand. De formule die daarbij hoort is:

=SomSheets(cel1;cel2;cel3)

Hierin verwijzen de cellen 1, 2 en 3 naar:

  • cel1: naam eerste tabblad dat in de berekening moet worden meegenomen
  • cel2: naam laatste tabblad dat in de berekening moet worden meegenomen
  • cel3: naam cel waarvan de inhoud moet worden opgeteld

In A1 staat dan de naam van het eerste blad (in het voorbeeld ===>>>), in cel A2 de naam van het laatste blad (<<<===) en in cel A3 de naam van de cel (Q4). Cel A3 kun je dan laten verwijzen naar andere cellen.

Public Function SomSheets(sh1 As String, sh2 As String, cl As String) As Double
Dim a As Integer, x As Integer, y As Integer
x = Sheets(sh1).Index
y = Sheets(sh2).Index
SomSheets = 0
For a = x To y
SomSheets = Sheets(a).Range(cl).Value + SomSheets
Next
End Function

Excel functie aantal weekdagen

Aantal weekdagen in een bepaalde maand

27 oktober 2014 | onderwerpen: excel, vba, visual basic, functie

Op Twitter verscheen de volgende vraag: “Ik wil alle zaterdagen tellen in een bepaalde maand. Weet iemand hoe ik dat doe in #Excel?

Een zelfgeschreven functie biedt hier uitkomst. Die voorkomt dat je allerlei extra kolommen moet gaan toevoegen aan je Excel bestand.

De functie die je hieronder ziet telt het aantal weekdagen in de opgegeven maand.

Stel dat je in cel A2 het jaar hebt opgegeven, in cel B2 het maandnummer en in cel C2 het nummer van de weekdag dan luidt de formule:

=NDagInMaand(A2;B2;C2)

De zaterdag heeft in dit voorbeeld het nummer 7. Excel start de week standaard met de zondag.

Zet daarvoor in de het macroblad (Alt-F11) van het betreffende blad deze code:

Excel functie aantal weekdagen


Video aantal weekdagen

video functie ‘aantal weekdagen
klik voor vergroting


Public Function NDagInMaand(Jaar As Integer, Maand As Integer, Weekdag As Byte) As Byte

Dim x As Date

start = DateSerial(Jaar, Maand, 1)

If Maand = 12 Then
last = DateSerial(1 + Jaar, 1, 1)
Else
last = DateSerial(Jaar, 1 + Maand, 1)
End If

NDagInMaand = 0

For x = start To last – 1
If WeekDay(x, vbSunday) = Weekdag Then NDagInMaand = NDagInMaand + 1
Next x

End Function

Specifieke tekst zoeken in Excel

8 oktober 2014 | onderwerpen: excel, vba, visual basic, reguliere expressie

Hoe kun je een specifieke tekst zoeken in Excel? In een brij van letters en cijfers? De Regulier Expressie in Visual Basic biedt uitkomst. Hier een korte uitleg met een praktijkvoorbeeld.

“Een reguliere expressie is een manier om patronen te beschrijven waarmee een computer tekst kan herkennen. Er bestaat hiervoor een formele syntaxis, die grotendeels is gestandaardiseerd.

Reguliere expressies worden bijvoorbeeld in teksteditors gebruikt om stukken tekst te doorzoeken, te manipuleren, in andere programma’s worden ze gebruikt om te controleren dat bepaalde patronen voorkomen.” (bron: Wikipedia)

Specifieke tekst zoeken in Excel

Reguliere Expressie in Visual Basic

Ook de programmeertaal van Office, Visual Basic, kent de reguliere expressie. Die biedt uitkomst om specifieke teksten te zoeken in Excel. Bijvoorbeeld uit bestanden die geen eenduidige opbouw hebben. Zoals in dit voorbeeld:

Lease sept 1-AAA-00 2014
1AAA00 Lease Sept 2014
Lease september 1-AAA-00
1-AAA-00 Lease Sept. 14
Lease sept. 1AA00

De RegEx functie in Visual Basic helpt om de kentekens uit dit bestand te halen.

Met de code die in de rechterkolom is weergegeven volstaat deze functie om het kenteken uit de tekst van cel A1 te halen:

=ALS(ISLEEG(Kentekenstreep(A1));Kenteken(A1);Kentekenstreep(A1))

De functie ‘Kenteken’ zoekt naar de kentekens zónder streepjes en de functie ‘KentekenStreep’ naar de kentekens mét streepjes. Dit werkt alleen voor de nieuwste kentekens (sidecode 8), bv 6-KBB-86.


Function Kenteken(cel) 
Dim regEx As New VBScript_RegExp_55.RegExp 
Dim matches, s 
regEx.Pattern = "([0-9]{1})([a-z]{3})([0-9]{2})" 
regEx.IgnoreCase = True 
regEx.Global = True 
If regEx.Test(cel) Then 
Set matches = regEx.Execute(cel) 
For Each Match In matches 
s = Match.Value 
Next 
Kenteken = s 
End If 
End Function 

Function KentekenStreep(cel) 
Dim regEx As New VBScript_RegExp_55.RegExp 
Dim matches, s 
regEx.Pattern = "([0-9]{1})-([a-z]{3})-([0-9]{2})" 
regEx.IgnoreCase = True 
regEx.Global = True 
If regEx.Test(cel) Then 
Set matches = regEx.Execute(cel) 
For Each Match In matches 
s = Match.Value 
Next 
KentekenStreep = s 
End If 
End Function