Tagarchief: MsgBox

Opslag-datum



Ofwel de datum waarop een bestand is opgeslagen; in het Engels vaak aangeduid als SaveDate.

Word kent een handige manier om in de kop- en voetregel de datum, waarop het betreffende bestand voor de laatste keer is opgeslagen, weer te geven (SaveDate, een apart Veld binnen de optie Info over document).
Op de vraag, waar dat in Excel terug te vinden is, moest ik het antwoord schuldig blijven: Excel kent een zodanige optie niet en die is ook niet met een of andere info-functie na te bouwen.
Uiteraard even Google geraadpleegd: geen resultaten voor SaveDate in Excel, wel allerlei VBA-routines waarmee dit enigszins na te bootsen is.
Daarom in dit artikel enkele voorbeelden daarvan.

Bij opslaan datum toevoegen 1

De eerste methode, die de Word-functionaliteit goed benadert, is om de datum (en tijd) aan de voetregel toe te voegen op het moment dat het Excel-bestand wordt opgeslagen.
Drie regels binnen VBA volstaan:

Dit is een zogenaamde Event-SUBroutine; net voordat de werkmap wordt opgeslagen (BeforeSave) wordt deze procedure uitgevoerd.
De parameter SaveAsUI is verplicht. Deze parameter kan binnen de routine gebruikt worden; de waarde daarvan is True als de gebruiker Opslaan als heeft gekozen en bij gewoon Opslaan is de waarde False.
De tweede parameter (Cancel) is ook verplicht en heeft standaard de waarde False. Krijgt ergens in de procedure deze parameter de waarde True dan zal er daarna geen opslag-actie plaats vinden (opslaan wordt gecanceled).
De tweede regel is waar het om draait: links in de voetregel van het actieve werkblad wordt de combinatie van de huidige datum en tijd geplaatst. De datum heeft een speciale notatie gekregen (minimaal 1 positie voor de dagaanduiding, een uitgebreide omschrijving voor de maand (bijvoorbeeld oktober), 4 cijfers voor het jaar en dan nog een komma en een spatie).

LET OP de routine plaatst de datum ALLEEN in de voetregel van het actieve werkblad (ActiveSheet). Dit is een groot verschil met de Word-optie, die de datum in de voetregel van het document plaatst en daarmee op ALLE pagina’s.

NB1 deze routine moet in een speciale module van de betreffende werkmap geplaatst worden, namelijk in ThisWorkbook.

NB2 om er voor te zorgen, dat deze routine samen met de Excel-sheet wordt opgeslagen, dient de extensie xlsm te zijn (macro-enabled).

NB3 in alle voorbeelden in dit artikel wordt de datum links in de voetregel geplaatst. Uiteraard kunnen ook de opties CenterFooter, RightFooter, LeftHeader, CenterHeader en RightHeader gebruikt worden.

Bij opslaan datum toevoegen 2

In het Voorbeeldbestand is de routine wat uitgebreid:

Hoe plaats je een dergelijke routine in je eigen werkmap:

  1. ga naar de VBA-editor: kies binnen de menutab Ontwikkelaars de optie Visual Basic of druk op Alt-F11.
  2. dubbelklik op de module ThisWorkbook van de betreffende werkmap; rechts kan dan de projectcode voor deze module ingevoerd worden.
  3. tik de bovenstaande code in of, beter, kopieer deze uit het Voorbeeldbestand.
  4. verlaat de VBA-editor door Alt-F11 te drukken
  5. sla het bestand op; zorg dat het de extensie xlsm krijgt.

Een korte uitleg van de belangrijkste elementen van de routine:

  • eerst wordt een message-box getoond met de vraag of de voetregel aangepast moet worden; alleen Ja en Nee zijn toegestaan (vbYesNo)
  • als op de Nee-knop wordt geklikt, gebeurt er in deze subroutine niets meer. Wel slaat Excel het bestand dan nog op.
  • anders wordt de inhoud van de linker-voetregel in de variabele a geplaatst en via een tweede message-box op het scherm getoond.
  • wordt daar gekozen om niet door te gaan, dan krijgt u de waarschuwing te zien, dat het bestand niet wordt opgeslagen (doordat Cancel op True wordt gezet)
  • anders wordt de voetregel aangepast, waarna Excel het bestand opslaat.

NB1 de eerste 2 keren wordt MsgBox als functie gebruikt, waarvan het resultaat in de variabele a wordt geplaatst.
In het derde geval voert MsgBox alleen een opdracht uit; omdat het dan geen functie is, moeten de haakjes weggelaten worden.

NB2 de voetregel is iets uitgebreid: naast de tekst Opslagdatum wordt met behulp van &6 ook de lettergrootte aangepast.

Alternatief 1

Een groot nadeel van bovenstaande methodes is, dat de VBA-routine eerst in het betreffende bestand moet worden geplaatst en het bestand als xlsm moet worden opgeslagen.

Handige en/of veel gebruikte routines kunnen echter ook in een module van uw persoonlijke werkmap geplaatst worden.
De vraag is: waar kunt u die map vinden?

Ziet u in de VBA-editor ergens het project PERSONAL.XLSB dan kunt u een van de bestaande modules openen door daarop te dubbelklikken (of een extra module Invoegen).

Bestaat de persoonlijke werkmap nog niet, doe dan het volgende:

  1. open de menutab Ontwikkelaars (ziet u die niet? Klik met de rechter muisknop ergens in de menubalk en kies de optie Lint aanpassen en vink in het rechtergedeelte van het nieuw geopende scherm de optie Ontwikkelaars aan).
  2. kies de optie Macro opnemen.
    Een macro opnemen (en stoppen) kan ook altijd via de knop linksonder in de statusbalk
  3. in het midden van de pop-up kiest u Persoonlijke macrowerkmap, klik op de OK-knop en kies dan direct in de menubalk de optie Opname stoppen.

Als de persoonlijke werkmap bestaat, dan kunnen routines daarin geplaatst worden; deze zijn dan in iedere werkmap direct te gebruiken.

Het eerste alternatief is dan (routine vergelijkbaar met hierboven; tekst hieronder is makkelijk te kopiëren):
Sub DatumVoetregel()
Dim a
a = MsgBox("Wilt u datum en tijd links in de voetregel plaatsen?", _
vbYesNo, "Voetregel aanpassen")
If a = vbYes Then
a = ActiveSheet.PageSetup.LeftFooter
If a <> "" Then
a = MsgBox("In de voetregel staat nu: " & _
vbCrLf & a & vbCrLf & "Doorgaan?", _
vbYesNo, "Voetregel aanpassen")
If a = vbNo Then Exit Sub
End If
ActiveSheet.PageSetup.LeftFooter = _
"&6 " & Format(Date, "d mmmm yyyy, ") & Time
End If
End Sub

LET OP het woord Private is op de eerste regel weggelaten, anders is deze routine niet voor andere werkbladen dan de persoonlijke ‘zichtbaar’.

Deze procedure zal NIET automatisch starten wanneer u een bestand opslaat. Wat moet u doen:

  1. maak een nieuwe werkmap aan
  2. kies in de menutab Ontwikkelaars de optie Macro’s (of druk op Alt-F8)
  3. kies in het vervolgscherm de gewenste routine; in dit geval PERSONAL.XLSB!DatumVoetregel en klik op Uitvoeren (of dubbelklik op de gewenste routine)
  4. sla direct daarna het bestand op.
    LET OP Doet u dat met het voorbeeldbestand, dan zal de automatische routine ook nog opgestart worden

NB doordat er twee handelingen verricht moeten worden (de routine uitvoeren en het bestand opslaan) zal de tijd in de voetregel niet exact overeenkomen met de tijd van het opgeslagen bestand.

Alternatief 2

Een verkorte routine, die alleen de datum en tijd in de voetregel zet, is:
Sub DatumVoetregelKort()
ActiveSheet.PageSetup.LeftFooter = Format(Date, "d mmmm yyyy, ") & Time
End Sub

Alternatief 3

Een derde alternatief, waarbij de SaveDate uit de eigenschappen van het document wordt opgehaald:
Sub DatumVoetregelKort2()
ActiveSheet.PageSetup.LeftFooter = Format( _
ActiveWorkbook.BuiltinDocumentProperties("Last Save Time"), "d mmmm yyyy, hh:mm:ss")
End Sub

De werkwijze is dan net andersom:

  1. sla de werkmap op
  2. voer de routine uit
  3. print de pagina

Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Mastermind

MastermindMastermind! Wie kent het niet?

Dit is een spel, dat waarschijnlijk iedereen wel eens ooit heeft gespeeld.
Ik moest er aan denken, toen ik bij een (gedwongen) opruimronde op zolder één van mijn eerste computers, een TRS-80, tegenkwam.

trs80aNostalgie: ongeveer 40 jaar geleden is mijn “computer-verslaving” begonnen:  eerst met een TRS-80 (van de firma Radio Shack) met een intern geheugen van 4Kb en een cassetterecorder als opslagmedium, al vrij snel met een geheugenuitbreiding tot 16Kb.
trs80bTig jaar later was de luxe niet te overzien: een modern apparaat met 2 diskette-stations en 16Mb intern geheugen!
diskZo’n diskette kon wel 500 Kb aan data bevatten.
En het gemak: na een avond lang programmeren, gewoon op disk wegschrijven; geen angst meer, dat het niet goed op de cassette was terecht gekomen, waar je pas achter kwam als het te laat was (ik geloof, dat ik in die periode pas echt heb leren vloeken!).

Maar even terug naar het onderwerp: Mastermind.
Ik kwam bij het opruimen nog aantekeningen tegen uit die eerste TRS-80-periode: de opzet voor een computer-variant van dit spel; waarschijnlijk gemaakt, zodat ik ook zonder echte tegenstander aan een andere verslaving toe kon komen: spelletjes spelen!

Mastermind in Excel

Een uitdaging dus om te kijken of we Mastermind ook in Excel zouden kunnen  spelen. Het kostte toch nog wel een paar avonden ploeteren, maar het is gelukt.

mastermindKijk maar eens in het Voorbeeldbestand.

Als je op de knop Nieuwe ronde drukt, zal Excel 4 kleuren kiezen uit een serie van 8 (dubbelen zijn toegestaan).
Aan jou, als speler, om in de cellen achter Ronde 1, je eerste gok te plaatsen.
Druk dan op de knop Controleer en onder Resultaat komt het resultaat (sic!).
Z(wart) betekent dat er een kleur goed is gekozen EN op de juiste plaats, W(it) geeft aan dat er een kleur goed is, maar niet op de juiste positie.
Vul dan Ronde 2 in, druk op Controleer etc.

Mijn beste spelronde tot nu toe was: geraden in 4 keer binnen 40 seconden. Ben benieuwd naar jullie resultaten!

Hoe werkt het programma?

Dit artikel en het Voorbeeldbestand zijn deze keer vooral ook voor de fun; dus maar een korte, compacte toelichting.
Geïnteresseerden in VBA vinden in Module1 (te bereiken via Alt-F11) de achterliggende routines. Vooral interessant is daar de combinatie tussen variabelen binnen VBA (in de vorm van een array/matrix) en celbereiken in de Excel-sheets.

Op het tabblad Parameters staan de 8 kleuren, waaruit Excel en jij, als speler, mogen kiezen; deze cellen hebben de naam Kleuren gekregen.

mastermind2Wanneer Excel de opdracht krijgt om aan een Nieuwe ronde te beginnen, dan worden op het tabblad CompKeuze de cellen B3:E3 van willekeurige getallen tussen 1 en 8 voorzien; in de rij daaronder komen dan automatisch de overeenkomende kleuren.

NB wel eerlijk spelen; dus niet gauw hier kijken wat de combinatie is!

Het tabblad Raden bevat het “spelbord”.

Excel-opties

In het Voorbeeldbestand wordt veel met Namen gewerkt om verwijzingen overzichtelijk te houden. Daarnaast zorgt Voorwaardelijke opmaak voor de juiste kleuren. De functie Index wordt her en der gebruikt om kleuren op te zoeken.

En uiteraard wordt er gebruik gemaakt van VBA-routines (Visual Basic for Applications), aangestuurd door knoppen.

Heb je vragen over één van deze toepassingen en het gebruik binnen Mastermind: schroom niet om contact op te nemen met G-Info!


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Standaardinstellingen Excel (VBA)

Vorige week hebben we gezien dat het handig is om bij het starten van Excel standaardinstellingen in te laden (zie Standaardinstellingen Excel).
Ik heb toen ook beloofd om de gebruikte VBA-routine toe te lichten.

Deze keer zal ik dat dan ook doen, maar wil allereerst voor diegene die nog nooit of nauwelijks met VBA  hebben gewerkt, een korte, algemene toelichting op VBA geven. Het is daardoor ook een wat uitgebreider verhaal geworden; en zeker ook voor de beginnende VBA-programmeur niet al te makkelijk!

VBA (Visual Basic for Applications)

Kortweg zou je kunnen zeggen, dat dit een programmeertaal is waarmee je handelingen, die je in Excel wilt uitvoeren, kunt automatiseren.
Wanneer je bijvoorbeeld vaak je naam en adres-gegevens in Excel moet invoeren, dan kun je dat met VBA simpel zodanig automatiseren, dat dit met ‘één druk op de knop’ wordt uitgevoerd.
Of als je regelmatig een bestand krijgt, waarin na iedere regel met gegevens een lege rij voorkomt, die eruit moet: automatiseren via VBA!
Dit soort handelingen kun je als Macro opnemen en daarna opnieuw ‘afspelen’.  De macro kun je in de VBA-editor bekijken (en daar kun je veel van leren hoe het programmeren in zijn werk gaat!) en eventueel aanpassen. Om dit verder uit te leggen gaat echter te ver voor dit blog.

Het automatiseren van handelingen (macro’s opnemen en uitvoeren) is niet het enige dat met VBA kan: je kunt ook zelf macro’s schrijven (door programmeurs sub-routines genoemd) of eigen functies bedenken, wanneer de standaard-functies van Excel niet voldoende voor je zijn.

VBA-editor

Hieronder gaan we zelf aan de slag met programmeren in VBA. Programmeren is niets anders dan het in de goede volgorde zetten van opdrachten die uitgevoerd moeten worden (klinkt simpel, toch?).
Voor die opdrachten gebruik je een soort tekstverwerker, de zogenaamde VBA-editor.  Waar vinden we de editor, is dan de volgende vraag.
Net als met zoveel zaken in Excel, kun je die op verschillende manieren vinden; vaak nog afhankelijk van de versie die je gebruikt. In de 2010-versie en hoger is deze ‘verstopt’ op het Ontwikkelaars-tabblad, dat standaard niet in de menu-balk zit.
Gelukkig werkt rechtsklikken op een tabblad onderin (en dan Programmacode weergeven kiezen) nog steeds, maar nog makkelijker: er is ook een toets-combinatie: Alt-F11 (dat doet me er aan denken om binnenkort eens een blog aan sneltoetsen te wijden!)
Dus ga naar Excel (als er geen werkmap geopend is, maak dan een nieuwe aan; onthoud even de naam van de geopende werkmap), hou de Alt-toets vast en druk op F11: en daar is de VBA-editor!VBA2

In het linker gedeelte van het scherm zie je een
overzicht van alle openstaande ‘projecten’. De editor noemt iedere werkmap, die in gebruik is, ‘project’. Maar daarnaast zijn add-ins ook projecten.
Klik op het project met de naam
van de werkmap, die je net hebt onthouden (anders even terug naar Excel (Alt-F11), kijken hoe de werkmap heet en weer naar de VBA-editor (Alt-F11)).
Macro’s/subroutines/programma’s/functies worden binnen een project weggeschreven in een Module. Standaard is die er nog niet, dus: kies boven in het menu Invoegen en dan Module.

Programma-1

Aan de slag: we gaan eerst een simpel programmaatje schrijven:

  1. maak een nieuw werkblad in Excel aan; onthoud de naam
  2. ga naar de VBA-editor mbv Alt-F11
  3. zoek het project met de naam uit stap 1 en klik daarop
  4. maak een Module aan: Invoegen/Module
  5.  in je project zie je een nieuwe module met de naam Module1; dubbelklik daarop; we krijgen dan een leeg ‘vel’ in onze ‘tekstverwerker’
  6. nu gaan we programmeren!
    Tik in: Sub ToonBericht()
    en druk op Enter
  7. als alles goed is gegaan heeft de editor een nieuwe regel toegevoegd: End Sub
  8. VBA kent nu een programma (ofwel subroutine, vandaar het woordje Sub) met de naam ToonBericht; hij weet waar dit begint (bij Sub) en waar deze routine eindigt (bij End Sub). Zo kunnen er diverse programma’s in één module komen.
  9. het programma doet nog niets. Tik daarom op de lege regel (tussen Sub en End) het volgende: MsgBox “Een bericht vanuit VBA!”, vbInformation, “Informatie-bericht” (MsgBox is het VBA-commando om een bericht weer te geven; let goed op wat er allemaal op je scherm verschijnt als je iets intikt; het eerste gedeelte achter MsgBox is de boodschap die je weer wilt laten geven; dan komt er een ‘code’ waardoor VBA weet, wat er rond het bericht moet komen (vbInformation is de vb-code voor een Information-boodschap); als derde komt de titel van het bericht)VBA3
  10. nu het programma klaar is gaan we het oproepen vanuit Excel:
    ga van VBA naar Excel: Alt-F11.
    Nu moeten we de macro aan het werk zetten; ga naar het macro-overzicht (via Alt-F8)
    klik op de macro met de naam ToonBericht en kies Uitvoeren
  11. Gelukt?

Programma-2

De techniek uit het eerste programma gebruiken we nu ook voor het ophalen van de directory waarde templates met standaardinstellingen moeten staan:

  1. ga opnieuw naar de VBA-editor mbv Alt-F11
  2. dubbelklik zonodig op Module1
  3. Tik onder het vorige programma:
    Sub ToonDir()
    MsgBox Application.StartupPath, vbInformation, “Gevonden!”
    End Sub
  4. de MsgBox zal het opstartpad van de aanroepende applicatie (Excel dus) tonen
  5. ga van VBA naar Excel: Alt-F11 en voer de macro ToonDir uit(via Alt-F8)

Programma-3

Omdat het resultaat van het vorige programma moeilijk te kopiëren is, heb ik vorige keer een routine gebruikt die het gewenste resultaat in een Excel-cel wegschrijft.
Hieronder staat het programma weergegeven:
VBA1

De groene teksten zijn geen opdrachten binnen VBA, maar is commentaar/uitleg die de programmeur toevoegt, zodat de routine beter door anderen (en later door de programmeur zelf!) te begrijpen is.
Alles wat achter een apostrof-teken (dus ‘) staat, wordt door VBA als commentaar gezien.

Hoewel het bovenstaande in eerste instantie misschien ingewikkeld overkomt, valt het (na wat oefening) heel erg mee. Door zelf macro’s op te nemen en binnen VBA de commando’s te bestuderen, ontstaat er snel inzicht in de structuur van het programmeren en de te gebruiken commando’s.

In het Voorbeeldbestand heb ik bovenstaande routines opgenomen, inclusief een beschrijving hoe een macro te starten m.b.v. een knop.
Ook heb ik daar de 2 voorbeelden van het begin (NAW-gegevens invoegen en lege regels verwijderen) uitgewerkt.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: