Francesco Foti's weblog

How to detect windows session lock/unlock in VBA

There are (at least) 3 solutions to detect when a user locks his/her workstation on Windows and react on the event. A light and efficient solution using Classic Visual Basic (here VB5). A very bad solution in VBA, which involves subclassing and using the Windows API directly. An hybrid solution for an Access (VBA) application,… Continue reading How to detect windows session lock/unlock in VBA

How to implement a circular queue in VBA (Excel)

Temporal modules Queues are part of the “temporal modules” family, with stacks and general double ended queues. They are containers that follow the FIFO (first in, first out) principle for storing and retrieving elements. While stacks follow the LIFO (last in, first out) principle, and general double ended queues can store and retrieve elements in… Continue reading How to implement a circular queue in VBA (Excel)

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.… Continue reading How to round a number to a five cents multiple 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

Public Function GetFileExt(ByRef psFilename As String) As String

1	Public Function GetFileExt(ByRef psFilename As String) As String

Get the file extension part of a filename, without the leading dot (“.”). Example (type in immediate window):

  ?GetFileExt("test.txt")
  txt
  ?GetFileExt("https://www.example.com/index.html")
  html

?GetFileExt("test.txt")

txt

?GetFileExt("https://www.example.com/index.html")

html

StripFileExt

Public Function StripFileExt(ByRef psFilename As String) As String

1	Public Function StripFileExt(ByRef psFilename As String) As String

Get the left part of a filename (and path), without… Continue reading Manipulating filenames in Excel or Access, or VB/A

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… Continue reading A simplified sprintf function for VBA

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… Continue reading The case for the accented characters

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… Continue reading How to measure any text with Word, vba code included

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… Continue reading How do I compute the length of the longest text for a date in Word ?

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… Continue reading Comment connaître la longueur du plus long texte de la date du jour de l’année avec Word ?

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… Continue reading Tracking active directory user logon/off for the just cause – Part 1