Urenbestand Premium Macro's

Macro’s ingeven

Doormiddel van Macro’s kun je urenbestanden bewerken voordat de gegevens binnen Easyflex worden verwerkt. De Macro’s zijn een unieke ‘taal’ waarmee je veel verschillende bewerkingen kan inrichten. Je kan een macro inrichten via ‘Beheer > Urenbestand definities > Urenbestand definitie (als type: Excel bestand (urenbestanden Premium) > bestandformaat’. Selecteer een voorbeeld bestand en definieer de gewenste cellen. Wanneer je dit hebt gedaan krijg je rechts onderin het veld ‘Macro’s' te zien.

In dit veld kun je de gewenste macro typen die van toepassing is op het geselecteerde celbereik. De macro zal vervolgens op iedere regel binnen het geselecteerde bereik worden toegepast. Bij het schrijven van een macro moet er worden verwezen naar de kolom of een combinatie van de kolom en de rij waar de gegevens uitgehaald moeten worden bijvoorbeeld: D of D8 .

• Bij D worden de gegevens van alle regels van het celbereik in kolom D gebruiken. Dit word het meeste gebruikt tijdens het schrijven van een macro omdat je de macro dan voor de gehele kolom uitvoert, voorbeelden hiervan zijn te vinden verderop in deze procesbeschrijving.

• Bij D8 worden alleen de gegevens vanuit kolom D, rij 8 gebruikt. Je kan dit bijvoorbeeld gebruiken om gegevens te verplaatsen binnen het bestand. Stel je hebt op plek D1 ‘Ma’ (afkorting van maandag) staan maar de gegevens van maandag staan in kolom B startend vanaf rij 5. Op deze manier kan het bestand niet worden verwerkt. Om de verwerking wel te laten slagen kun je de cel B4 definiëren als ‘Dag van de week’ en in de macro typen: D1 . Hiermee wordt de waarde van D1 op de plek van B4 gezet en kunnen de gegevens verwerkt worden. LET OP: De gegevens worden niet visueel zichtbaar in het scherm maar kunnen gecontroleerd worden d.m.v. ‘Testen’ rechts onderin.
  

  

  Mocht het zo zijn dat ‘Di’ t/m ‘Vr’ in de cellen E1 t/m H1 staan en je wil deze verplaatsen naar C4 t/m F4 dan moet je C4 , D4 , E4 en F4 los definiëren met ‘Dag van de week’ en hierin de zelfde manier de corresponderende kolom / cel waarde invullen, dit omdat er maar één macro in een definitie kan worden toegepast en er in deze situatie naar verschillende kolommen moet worden gekeken.

• Het is ook mogelijk op VALUE te gebruiken i.p.v. D of D8 om de waarde van de cel te gebruiken. Wanneer je VALUE gebruikt

Let op: Tijdens het gebruik van macro’s is het erg belangrijk om de ingevulde macro te testen d.m.v. de ‘Testen’ knop en de gegevens te controleren voordat je de urenbestand definitie gaat gebruiken!

Type gegevens

Binnen de macro heb je te maken met verschillende type gegevens. Denk hierbij aan tekst, getal of tijd. In veel gevallen zal de macro het type overnemen vanuit het Excel bestand echter kan het zo zijn dat gegevens in Excel het type ‘tekst’ hebben maar dat het verwerkt moet worden als een ‘getal’, hiervoor kun je de volgende opties gebruiken:

• int(...) → Een heel getal

• number(...) → Een decimaal getal

• bool(...) → Een Boolean, een waarde die altijd true of false is, waar of niet waar.

• string(...) → Een tekst LET OP: Om een string te maken gebruik je de dubbele aanhalingstekens: "

  • Bijvoorbeeld string("Jan Janssen") geeft Jan Janssen

• date(...) → Een datum

• time(...) → Een tijd

Het is hierbij belangrijk dat je begint met het ‘type’ en daarna pas je de rest van je macro. Bijvoorbeeld number(C1), hiermee word het type in cel C1 omgezet naar een getal of string(H15) hiermee word het type omgezet naar een tekst.

Vergelijkingen

Doormiddel van vergelijkingen kun je verschillende waardes van het document met elkaar vergelijken. De volgende vergelijkingen worden ondersteund:

• > → Groter dan → Kijkt of de eerste waarde groter is dan de tweede

  • Bijvoorbeeld 4.5 > 3 geeft true, 3 > 4.5 geeft false en 7 > 7 geeft false.

• < → Kleiner dan → Kijkt of de eerste waarde kleiner is dan de tweede

  • Bijvoorbeeld 9 < 5 geeft false, 5 < 9 geeft true en 7 < 7 geeft false.

• >= → Groter gelijk dan → Kijkt of de eerste waarde groter is dan de tweede

  • Bijvoorbeeld 4 >= 3 geeft true, 3 >= 4 geeft false, en 7 >= 7 geeft true.

• <= → Kleiner gelijk dan → Kijkt of de eerste waarde kleiner is dan de tweede

  • Bijvoorbeeld 9 <= 5 geeft false, 5 <= 9 geeft true, en 7 <= 7 geeft true.

• == → Gelijk aan→ Kijkt of twee waardes gelijk aan elkaar zijn

  • Bijvoorbeeld 5 == 5 geeft true en datum(1, 1, 2000) == datum(1, 1, 2000) geeft true, en "ABC" == "DEF" geeft false.

• != → Niet gelijk aan → Kijkt of twee waardes niet gelijk aan elkaar zijn

  • Bijvoorbeeld 4.7 != 4.7 geeft false en datum(1, 1, 2020) != datum(1, 1, 2000) geeft true, en "ABC" != "DEF" geeft true.

Rekenkundige opties

Wanneer er vanuit de waardes uit het urenbestand nog verder gerekend moet worden kan er d.m.v. de volgende opties een berekening worden toegevoegd in de macro:

• + → Optellen → Tel twee getallen bij elkaar op

  • Bijvoorbeeld 2 + 5 of A1 + B1

• - → Aftrekken -> Trek twee getallen van elkaar af

  • Bijvoorbeeld 9 - 3 of B1 - A1

• / → Delen → Deel twee getallen door elkaar

  • Bijvoorbeeld 8 / 2 of C1 / D1

• * → Vermenigvuldig → Vermenigvuldig twee cijfers met elkaar

  • Bijvoorbeeld 3 * -2 of D1 * E1

Waardes in macro’s

Tijdens het gebruik van een macro kun je verwijzen naar verschillende waardes waaronder lege waardes of de waarde in een cel. Dit kun je doen doormiddel van de volgende opties:

• Waarde in cel → VALUE

  • Bijvoorbeeld: int(VALUE) → Hiermee verander je alle waardes in het celbereik in het type tekst → Dit kan overigens ook met int(B)

• Een eigen variabelen vastleggen → Hiermee kun je gegevens ‘opslaan’ in een variabelen die je zelf naam geeft

  • Bijvoorbeeld VOORNAAM = A → Hiermee word de waarde van kolom A (per regel) opgeslagen als VOORNAAM. Deze variabelen kun je vervolgens verderop in je macro gebruiken. LET OP: Zorg er wel voor dat de variabelen een unieke naam hebben en niet overeen komen met een kolom.

• Lege tekst → ""

  • Bijvoorbeeld VALUE != "" → Hiermee zeg je dat de waarde in de cel niet gelijk mag zijn aan ‘niks’ → Indien er dus een lege cel tussen zit word deze genegeerd

• Lege waarde → _

  • Bijvoorbeeld VALUE != _ → Hiermee zeg je dat de waarde in de cel niet gelijk mag zijn aan ‘niks’ → Indien er dus een lege cel tussen zit word deze genegeerd

• Beschrijving → #

  • Bijvoorbeeld #Berekening van de uren → Dit is een opmerking dus hier wordt niks mee gedaan door Easyflex en is dus echter ter verduidelijking van de macro

• Volgende regel → ;

  • Bijvoorbeeld:
    #Voor- en achternaam combineren
VNAAM = A;
ANAAM = B;
VNAAM + " " + ANAAM
    Hiermee wordt de voor- en achternaam aan elkaar gezet in het celbereik wat gedefinieerd is. Door " " komt er een spatie tussen de twee waardes.
    

    

• Lijst → [lijst] of [!lijst]

  • Bijvoorbeeld: match("test123", "test[123456789]") -> true of match("test123", "test[!123456789]*") -> false → Hiermee zeg je dat de gegevens na een waarde (in dit geval test gelijk ([lijst])moet zijn aan de waardes binnen de lijst of niet gelijk ([!lijst]) moet zijn aan de waardes binnen de lijst.

• Regel negeren in combinatie met een if formule → SKIP

  • Bijvoorbeeld: if(VALUE == 123, A, SKIP) → Hiermee zeg je als de waarde gelijk is aan 123 geef dan de waarde uit kolom A terug, indien de waarde niet gelijk is aan 123 moet je de regel overslaan.

Wildcards

Wildcards kun je het beste omschrijven als jokers. De volgende wildcards zijn beschikbaar binnen de macro functionaliteit:

• * → Wanneer je een * gebruikt voor en/of na een waarde geef je eigenlijk aan dat het niet uitmaakt wat er voor en/of na de waarde komt.

  • Bijvoorbeeld if(match(VALUE, "Test*"), A, B) → Wanneer de waarde in de cel begint met Test zal de de macro true terug geven dus ook als er bijvoorbeeld Test123456789 staat of TestTest zolang het maar begint met Test. Indien de waarde niet begint met Test zal de macro false terug geven bijvoorbeeld bij 123456789Test of estTest.

• ? → Wanneer je een ? gebruikt in je macro geef je aan dat het niet uitmaakt wat er op die positie staat.

  • Bijvoorbeeld if(match(VALUE, "Test????"), A, B) → Wanneer de waarde in de cel TestTest staat zal de de macro true terug geven dus ook als er bijvoorbeeld Test1234 staat zolang het maar begint met Test en hierna 4 (er is 4x een ? toegevoegd) tekens heeft. Indien de waarde niet begint met Test of meer/minder dan 4 waardes heeft na Test zal de macro false terug geven bijvoorbeeld bij 1234Test of TestTes.

Functies

Binnen de macro functionaliteit is het mogelijk om verschillende functies toe te passen. Doormiddel van deze functies kun dynamische situaties en acties inrichten die er voor zorgen dat het urenbestand op de juiste manier word verwerkt. De volgende functies zijn beschikbaar, de waarde tussen haakjes geeft aan welk type gegeven er ingevuld moet worden:

Hieronder volgen een aantal extra voorbeelden van veel gebruikte macro’s.

• replace(cel/hoofd waarde [type: string], zoek waarde [type: string], vervang waarde [type: string]) geeft string → Met deze functie kan een stuk tekst vervangen worden.

  • Voorbeeld 1: replace("Week 45", "Week ", "") geeft 45

  • Voorbeeld 2: Cel/hoofdswaarde B5 (zie afbeelding) geeft 2

    

• substring(cel/hoofdwaarde [type: string], startpositie in cel/hoofdwaarde [type: int], lengte aantal posities [type: int]) geeft string → Met deze functie kan je een gedeelte van een stuk tekst pakken.

  • Voorbeeld 1: substring("26-02-2024 maandag", 1, 10) geeft 26-02-2024, 'startpositie' is de begin positie (6 is het 6e teken), 'lengte aantal posities' is het aantal tekens dat je wilt opvragen

  • Voorbeeld 2: Cel/hoofdwaarde B4 (zie afbeelding) geeft 2024
    

    

• and(...) geeft true als alle input waardes waar zijn en false als er één of meer input waardes onwaar zijn

  • Voorbeeld 1: in cel A1 staat 10 en in cel B1 staat 5 en je vult de volgende macro in and(A1 == 10, B1 == 2) Dit geeft nu false terug omdat één van de 2 waarde niet klopt (in dit geval B1). Als je de macro and(A1 == 10, B1 == 5) hebt krijg je de waarde true terug omdat beide waardes kloppen.

  • Voorbeeld 2:
    

    

    Met dit voorbeeld zeg je het volgende: er mag geen lege waarde in de cel staan ("") en de waarde in de cel mag niet gelijk zijn aan Totaal.

• or(...) geeft true als één van de input waardes waar is en false als alle waardes onwaar zijn,

  • Voorbeeld 1: in cel A1 staat 10 en in cel B1 staat 5 en je vult de volgende macro in or(A1 == 10, B1 == 2) Dit geeft nu true terug omdat één van de 2 waarde klopt (in dit geval A1). Als je de macro or(A1 == 11, B1 == 2) hebt krijg je de waarde false terug omdat beide waardes niet kloppen.

  • Voorbeeld 2:
    

    

    
    Met dit voorbeeld zeg je het volgende: Laat de waardes zien die gelijk is aan een lege cel ("") of de waarde moet gelijk zijn aan Totaal.

• if(Voorwaarde [Type: bool], Waarde als waar[Type: All], Waarde als nietwaar [Type: All]) geeft true of false aan de hand of er wordt voldaan aan de voorwaarde

  • Voorbeeld 1: In cel A1 staat Jumbo. Je zou hier graag automatisch een kostenplaats aan willen koppelen. Door dit te doen kun je B1 selecteren en hier het veld ‘Kostenplaats code’ selecteren. Vervolgens geef je de volgende macro in: if(A1 == "Jumbo", "123456", ""). Hiermee zeg je: als de waarde in cel A1 gelijk is aan Jumbo dan moet je de waarde 123456 overnemen, als de waarde niet gelijk is aan Jumbo moet de cel leeg blijven ("").

  • Voorbeeld 2: In kolom M staat het aantal uur per dag wat de flexwerker heeft gewerkt met als type int. In kolom N wil ik de overwerk uren per dag berekenen, alle uren boven de 8 uur moeten worden gezien als overwerkuur.
    

    

    In dit voorbeeld zeg je het volgende: als de waarde, in dit geval een tijd, in kolom M groter is dan een tijd van 8 moet de tijd in kolom M worden verminderd met een tijd van 8, indien de waarde niet groter is dan een tijd van 8 moet er een tijd van 0 worden ingevuld. Op deze manier komt er in kolom N alle uren te staan die meer zijn gewerkt dan 8 uur.

  • Voorbeeld 3: Zoals in voorbeeld 2 staat het aantal uur per dag wat de flexwerker heeft gewerkt in kolom M. Maar aangezien in kolom M de loon normale uren staan mag dit maar maximaal 8 uur zijn.
    

    

    In dit voorbeeld zeg je het volgende: als de waarde, in dit geval een tijd, groter is dan een tijd van 8 moet er een tijd van 8 worden ingevuld, indien de waarde niet groter is dan een tijd van 8 moet de tijd van de waarde blijven staan. Op deze manier komt er in kolom M uren te staan tot en met 8 uur. Als je voorbeeld 2 en 3 combineert krijg je wanneer er 09:35 uur is gewerkt en staat ingevuld op M op een dag 8 normale uren en 1:35 overwerk uren.

Functies combineren

Het is mogelijk om meerdere functies met elkaar te combineren om uiteindelijk op de juiste waarde uit te komen. Hiervoor kun je o.a. de functies substring, or, and en if voor gebruiken. Hieronder vind je een aantal voorbeelden van het combineren van functies.

• replace(substring(…), zoek waarde [type: string], vervang waarde [type string])

  • Voorbeeld: replace(substring(A, 6,100), ",", " ")
    

    

    In dit voorbeeld combineer je een replace met een substring, wat je zegt is pak het stuk vanaf het 6e teken tot en met het 100e teken van kolom A en vervang de , met een spatcie.

• or(and(...),and(...))

  • Voorbeeld: or(and(VALUE != "", VALUE != "Totaal"), and(VALUE == "55", VALUE == "99"))
    

    

    In dit voorbeeld zeg je dat de waarde niet gelijk mag zijn aan niks ("") en de waarde mag niet gelijk zijn aan Totaal of de waarde is gelijk aan 55 en de waarde is niet gelijk aan 99.

• if(substring(...), Waarde als waar[Type: All], if(...))

  • Voorbeeld: if(substring(D, 1,7) == "januari", string(01), if(substring(D, 1,8) == "februari", string(02), ""))
    

    

    In dit voorbeeld gebruik je meerdere functies in één functie in dit geval een if functie. Je zegt met de functie als positie 1 t/m 7 van kolom D overeen komt met januari dan moet je deze vervangen door de tekst 01, indien dit niet het geval is ga je verver met de volgende if functie. Die zegt op zijn beurt als positie 1 t/m 8 overeen komt met februari dan moet je deze vervangen door de tekst 02 in dien dit niet het geval is mag de cel leeg blijven. Je kan deze macro op deze manier bijvoorbeeld uitbreiden tot dat je alle maanden hebt gehad.

Voorbeeld bestanden

Om het gebruik van Urenbestanden premium i.c.m. macro’s zo duidelijk mogelijk te maken bied Easyflex een drietal voorbeelden aan die kunnen worden gebruikt als voorbeeld bestand. In deze urenbestand definities zijn ingevulde bestandsformaten en macro’s te zien die alleen werken i.c.m. het bijgeleverde document. Wanneer je dezelfde macro wil gebruiken in een document wat voor jou van toepassing is, dien je de macro aan te passen. De urenbestand definitie kan worden geüpload via ‘Beheer > Urenbestand definities > ’. Als je deze vervolgens wijzigt kun je naar ‘Bestandformaat’ gaan en hier het voorbeeldbestand inladen.

• Voorbeeld 1:

  • Urenbestand definitie: urenbestand_definitie_6132.xml

  • Excelbestand: Voorbeeld 1.xlsx

    • Voorbeeld functies in dit bestand:

      • De voorletters bepalen en deze weghalen d.m.v. substring(...) → Celbereik A2:A1048576

      • De waarde 0,00 vervangen met niks d.m.v. replace(...) → Celbereik A2:A1048576 en F2:Q1048576

      • Het gebruik van een Variabele → Celbereik A2:A1048576

      • Waardes negeren die gelijk zijn aan "" → Celbereik B2:B1048576

      • Overwerk uren van de normale uren afhalen → Celbereik E2:E1048576

• Voorbeeld 2:

  • Urenbestand definitie: urenbestand_definitie_6135.xml

  • Excelbestand: Voorbeeld 2.xlsx

    • Voorbeeld functies in dit bestand:

      • Waardes negeren welke leeg zijn en waarin TOTAAL UREN staat d.m.v. and(...) → Celbereik A3:A1048576

      • De waarde uit een andere cel aanpassen zodat deze gebruikt kan worden voor het bepalen van de datum d.m.v. replace(...) → Celbereik B2:AF2

      • Het gebruik van een Variabele → Celbereik B2:AF2

      • Waardes samenvoegen tot datum → Celbereik B2:AF2

      • Looncomponent code ingeven waardoor deze kan worden gehercodeerd → Celbereik B1:AF1

• Voorbeeld 3:

  • Urenbestand definitie: urenbestand_definitie_6136.xml

  • Excelbestand: Voorbeeld 3.xlsx

    • Voorbeeld functies in dit bestand:

      • Volgorde van flexwerker naam omdraaien m.b.v. Variabele → Celbereik A2:B1048576

      • Het gebruik van een Variabele → Celbereik M2:M1048576 en N2:N1048576

      • Alle drie de pauze bij elkaar optellen en deze vervolgens van de totale tijd af halen, hierna het aantal overwerkuren bepalen en deze ook van de totale tijd af halen zodat er altijd maximaal 8uur normale uren zijn d.m.v. if(...) i.c.m. or(...) → Celbereik M2:M1048576

      • Het aantal overwerk uren bepalen door de uren te tellen boven de 8uur excl. pauze tijden d.m.v. if(...) i.c.m. or(...) → Celbereik N2:N1048576

• Voorbeeld 4:

  • Urenbestand definitie: urenbestand_definitie_6055.xml

  • Excelbestand: Voorbeeld 4.xlsx

    • Voorbeeld functies in bestand:

      • Datum aanpassen naar juiste format → Cerbereik A5:A1045476

      • Waardes vervangen → Celbereik F5:F1048576 en G5:G1048576

      • Zoek een flexwerker en sla deze op als een Variabele m.b.v. search_row_up, replace en een Wildcard → Celbereik I5:I1048576

      • Zoeken naar het badgenummer met search_row_up en een Wildcard en filter deze er uit d.m.v. right → J5:J1048576

Foutmeldingen

Binnen de macro functionaliteit is ‘syntax highlighting’ ingebouwd, dit wil zeggen dat o.a. kolommen, variabelen en tekst een aparte kleur word tijdens het in vullen van de macro. Ook geeft het een rode lijn, bijvoorbeeld: , onder functies of waardes waar een fout in zit. Wanneer je met je muis op de fout blijft hangen krijg je de foutmelding te zien. Ook wanneer je het document wilt testen krijg je een foutmelding en word je direct doorverwezen naar de locatie/ gedefinieerde waarde waar de fout zit. In onderstaande tabel staan de foutmeldingen en de oorzaak van de foutmelding.