Swiss Money Coins

How to round a number to a five cents multiple in VB/A

In switzerland, we like rounding monetary numbers to 2 decimal places, with multiples of 5 for the fractional part. This is (probably) because our (physical) money has no “cent” coins, but we have 5 cents coins. That’s commonly called “5 cents” rounding.

Example:

  • 1.6777777 becomes 1.70
  • 1.4444444 becomes 1.45
  • 1.0333333 becomes 1.05
and so on.

Years ago, I was “gifted” with the knowledge of a “magic” trick to achieve that.
And that’s how it was gifted to me:

multiply by 20, cut the integer part, compare to .5 to do the rounding, adding 1 if it equals or overflows .5, and finally divide by 20 for the result.
Pretty simple. It works. However, this may not be a so easy task to do in VB/A as it seems to be.

Here’s my version of this algorithm, in the form of my FiveCt() function.
Note that I do use Double to Currency type conversion in this code, it will also work with Single type values.

You can use this function in Excel or Access (or any VB/A) calculations, as in Access queries for example.

FiveCt()

Round a double precision number to a five cents value.
Double in, Double out.

Notes and references on floating point variable types in VB/A

Manipulating filenames in Excel or Access, or VB/A

Do you often need to manipulate filenames in VBA ?
Here’s a small set of very useful functions that I regularly use:

GetFileExt

Get the file extension part of a filename, without the leading dot (“.”).

Example (type in immediate window):

 

StripFileExt

Get the left part of a filename (and path), without the file extension part.

Example (type in immediate window):

 

StripFilePath

Get only the filename of a full or partial filename and path.

Example (type in immediate window):

 

StripFileName

Get only the denormalized(*) path of a full or partial filename and path.

Example (type in immediate window):

(*)denormalized: without a path separator (“\”) at the end.

 

ChangeExt

Change the current extension of a filename (ex. “pdf” in c:\temp\test.pdf) to the specified new extension, and return the new filename.

Example (type in immediate window):

 

Code

A simplified sprintf function for VBA

Did you ever need to quickly replace some placeholders in a string with a variable number of literals or variable values ?

Here’s a ssprintf() VB/A variant of C’s sprintf() function.
Let’s say that the first “s” in the name stands for “simplified”.
We’re – of course – far away from the full fledged power of the C function, but this may nonetheless come in handy or just be a starting point for your own custom implementation.

Check the comments for details.

The case for the accented characters

Do you want of the get rid of the accented characters when you convert a string to upper case in VBA ?

Sure, if you’re developing an Access (or another Office – Excel, Word, etc… – or VB/A) application targeting french, you’ll want to sometimes avoid the À for Ucase$(“à”).
I’m speaking of the default behavior of VB/A’s Ucase$() function:

This UUCase() function may spare you some time (talking about minutes, of course):

Voilà.

How to measure any text with Word, vba code included

If you’re trying to put up a nice document template with Microsoft Word® you’ll probably have to precisely position different elements of your composition. When these elements are text, it may help to know precisely how much space you need to preserve for their layout.

Sometimes, this can even be tricky. My previous post is the starting point of this discussion, as we were wondering how much space would a date expression take on a document.

With a more generic solution, here is a new Word document with a VBA macro that will compute the length of any text in your unit of choice (millimeters, inches or centimeters).
You can use the document straight out of the box by simply opening it and following the on-screen instructions.
You can also access the code by pressing [ALT]+[F11] if you’re curious to see how it’s done.

Download this template from devinfo.net: [Text Width Calculator.docm]

Cheers.

How do I compute the length of the longest text for a date in Word ?

A Word document to compute the length of the longest text for a date in Word.

Working on the layout of a Microsoft Word French and English versions of a document template with a customer, we were trying to determine where to position a tab, so that we had the document date appear on the right of the page, near the right border. We put a tab and wrote the date, Monday 18 January 2016, as an example, to have some visual feedback. “Do you think that our tab position is far enough on the left, so that our date text will not horribly wrap on the next line ?” (that’s me translating the customer’s question). Good point !

In our template, the date of the day is expressed in the format <day name> <day number> <month name> <year>, meaning we have: Tuesday 19 January 2016 for the 1.19.2015.

We’re dealing with proportional fonts (an “i” takes less space than a “c” to print), so how can we know the maximum space to reserve for our date text, or at what distance from the right border of the page should we position our tab to avoid letting the date text wrap ?

Espace à réserver et tabulateur

Illustration – Where should I put the tab ?

Solution for French and English

To determine the space we have to preserve for the text of the date, we’re simply going to compute the length in centimeters (Europe here guys!) of the date texts for all the dates of the current year (we could have gone further on 10 years but that would be an overkill here). We then keep the length of the last of the longest texts.

To compute this length, for date texts in English or French, you can download this Word document (this is a zip archive, you can extract it with 7zip). Double click it and follow the instructions on the page.

Free download and unlimited use. Download this template from devinfo.net: word_longest_day.zip

Note on our Word documents containing macros

This Word document templates contains macros. Which means that it comes bundled with executable code. Such downloadable documents that we post on devinfo.net are numerically signed with our code certificate. This ensures that you got a valid copy that has not been tampered with malicious code. When you open the document for the first time, you’ll see the Word standard dialog for numerically signed documents, where you’ll be able to trust our publisher certificate.

Dialogue signature numérique
Word dialog for signed documents

To compute other text lengths

Need to have the same feature for any other text ? – Drop me a comment to see what I can do for the next update.

How is it done ?

I’ll post source code along with my comments in a future post.
But I’ll need you to motivate me, so drop me an inspiring comment to speed up the next post, thanks 😉 !

Enjoy !

Comment connaître la longueur du plus long texte de la date du jour de l’année avec Word ?

Je travaillais hier matin avec un client sur son modèle de lettre Word.
Sur ce modèle de lettre que l’on veut décliner en français et en anglais, la date est positionnée grâce à un tabulateur, vers le bord droit de la page. On a naturellement écrit la date du jour, lundi 18 janvier 2016, pour avoir un visuel concret du modèle. “Pensez-vous que la date est positionnée suffisament en retrait et laisse assez d’espace pour accomoder le texte d’une date un peu plus longue ? – Ce serait embêtant que la date passe à la ligne…”. Ouf (!), question pertinente !

Dans le modèle en question, la date du jour est exprimée dans le format <jour> <numéro du jour> <mois> <année>. Autrement dit, pour le 19/01/2016, nous avons l’expression suivante de la date: mardi 19 janvier 2016.

Etant donné que nous sommes sur une police de caractères à espacement proportionnel (le caractère “i” ne prends pas la même place que le caractère “c”), comment savoir quel est l’espace maximal à réserver, ou autrement dit à quel distance de la marge de droite positionner notre tabulateur pour éviter le problème de retour à la ligne intempestif ?

Espace à réserver et tabulateur
Illustration – Espace à réserver et tabulateur (Les bulles sont en anglais, mais pointent les éléments en question)

Solution pour le français et anglais

Pour connaître la largeur de l’espace à reserver, préférablement en centimètres, il faut “simplement” calculer la largeur de toutes les dates de l’année exprimée dans le format souhaité (on pourrait aller jusqu’à 10 dans le futur si on pinaille) et retenir simplement la dernière des plus longues des expressions mesurées.

Pour découvrir cette valeur, pour les textes de date en français ou en anglais, vous pouvez téléchargez ce document word (archive zip à décompresser, utilisez par exemple 7zip), double-cliquez le et suivez les instructions affichées à son ouverture.
Télécharger et utilisez gratuitement le document sur devinfo.net: word_longest_day.zip

Remarque sur les documents avec macros

C’est un document Word avec une macro, autrement dit qui exécute un programme lorsque vous appuyez sur le bouton proposé. Je veille à ce que le code des documents VBA en provenance de devinfo.net (là où se trouve ce document) soit signé numériquement, ce qui garantit qu’il arrive chez vous sans modification malicieuse, mais vous devez autoriser explicitement l’exécution de ces macros à l’ouverture du document.
Lorsque Word vous propose une boîte de dialogue similaire à celle-ci, choisissez la dernière option (faire confiance à l’éditeur) et cliquez sur le bouton [OK] :

Dialogue signature numérique
Boîte de dialogue de signature numérique

Pour calculer d’autres largeurs de textes

Avez-vous besoin de la même fonction pour calculer d’autres largeur de textes ? – Laissez un commentaire avec vos remarques pour la prochaine mise-à-jour.

Comment c’est fait ?

Je dévoilerai et expliquerai volontiers le code source de cette macro dans un post futur.
Mais j’ai besoin de vous, aidez-moi en posant un commentaire motivant, Merci 😉 !

Enjoy !

Tracking active directory user logon/off for the just cause – Part 1

Doing some work remotely, typically using a VPN to access our customer(s) network, or some other type of remote access software (like TeamViewer) is unavoidable when we’re maintaining our customers infrastructures.

The recurring problem I encounter and that initially got me started on tracking user logons and logoffs is to determine a convenient moment where I can interrupt my customer’s business, typically to reboot a server or a shared resource.

That’s where it comes in very handy to know if someone is still working on her/his PC or has left a session open, before firing an unscheduled interruption. Rebooting a Windows SBS 2011 server can take it out for 15 minutes, or even more if it has to apply everlasting updates.

I’ll discuss two methods I use to track users sessions and see who’s logged on.

These two methods require that you have a hand in the administration of the Active Directory server that manages your domain, because we’re going to implement a group policy to help us do that.

For this first method, we’ll simply use two batch files. One that is run for each user at his/her logon time, and the other for the logoff.

You just need to use a location either on the server file system or a network shared directory where the scripts must have write access. I do use a shared directory on a NAS box and I set the “hidden” property of the shared directory to true (like ending the shared resource name with ‘$’ on windows).

Let’s say we use a NAS named “MYNAS” and the shared directory “loggedon”. Then:

At user logon, we execute:

And at logoff, we execute:

So, with that, you end up with a directory full of text files for each currently open user session.
You can peek into that directory to know who’s logged on.

How do you setup these scripts on your Windows server ? Here, on technet

Cool, but wouldn’t it be cooler if we had a mobile app where we can select a customer domain and see a list of all last logons and logoffs ?
Sure, wait for it, that’s coming – if ever 😉 – on part 2.

A pure (windows) “C” version of urlencode

This is a windows pure C language version of a urlencode() function.
Sometimes, when I need simple tools and self contained windows EXEs (“just run it”, no “dll”s to copy or register), I find it simpler to code in C than in other languages. Particularly when you want to link with specialized libraries available only in C. So, I scavenged the web for a ready to use urlencode routine and found nothing really useable out of the box. Although this one is largely inspired by what I found.

Example:

Enjoy !

Using the free zip library in VBA

In a previous post I briefly explained how you can compile the free zlib library with Visual 2013 Studio and produce a 32 and a 64 bits dll that you can then use in VBA (Word, Excel, Access, etc…). If you follow this link to the article, you can even download the binaries I baked.

However, to use these dlls in a real world project, we need to wrap them in some 32/64 bits VBA code.
Inspired by samples I found on the web, here a quickly reusable module for your VBA projects.
Look at the TestXXXX procedures (change the pathes and filenames, of course, and execute them in the immediate window) for some samples.

Enjoy!