Month: December 2019

How to get useful Windows device information in VBA

Here are (at least) 3 properties of hardware like hard disks (whether they are HDD, SSD, USB or else), printers or anything else that Windows considers a device. These can be very useful to obtain in applications that manage enterprise assets like data and documents:

  • Vendor name,
  • Vendor ID,
  • and serial number.

These hardware properties allow us to uniquely an reliably identify a physical device.

Unfortunately, they’re not so easy to obtain. Particularly if we develop our applications with the “high level” languages that we use in database management systems like Microsoft Access (but also WinDev or FileMaker to name a few others), or in development environments that do not have the necessary, production ready, components or libraries to interact with the system.

Then there are different technologies we can use to get to these hardware properties, like:

  • Windows Management Instrumentation (or WMI), which has already morphed into MI; it is feature rich and COM/ActiveX friendly, but – boy – what a learning curve and a maintenance nightmare threat that would be, just do such a simple thing;
  • .NET, although it would be like using a cannon to kill a mosquito to fire up the .NET runtime engine just to use a nugget of its capacities to bring this small functionality to applications that are not .NET based.
  • The venerable and almighty Win32 API, that we’ll use here from C, to bridge its functionality to Visual Basic, because the Win32 API is mostly C/C++ friendly; my winner here, attaboy.

So, let’s start at the end (!), because we want to get into action immediately, don’t we ?
Some explanation will follow later in this post.

Just before jumping there, let me point out where I am going with all this stuff next:

  • We’re going to combine this code in an out of process, Visual Basic 5 ActiveX EXE server. That will allow us to detect when a USB (or other device type) is plugged or unplugged into the system, in real time;

  • That’s for a bit later, we have some other things to talk about first, but I can’t resist the urge to spoil it:
    using a similar technique (the ActiveX OOP EXE server), we’ll turn Access into a (local machine/network) Web server (whaaat ?!? – Yes, no apache, nginx, node, or whatever needed!)

Watch the (short) demo video

Get device information from an access database demo

Download

  • binaries

    • deviceinfo.dll (32bits version, MD5 sum: fb86ea314cdc4b2fd42f01b3876230d3)
    • deviceinfo64.dll (64bits version, MD5 sum: c27ce08c1c7d982bc475181d6f1cc613)
  • C DLL source code on my github

  • Access Demo database

    • deviceinfo_dll.accdb (contains the MDeviceInfo.bas module with the necessary declares, and additional wrapping to easily use the DLL with one function call and a supporting Type definition).
      Important note: copy the two DLLs (deviceinfo.dll and deviceinfo64.dll) in the same directory where you put the demo database deviceinfo_dll.accdb. The DLLs have to be in one of your PATH directories, or in the same directory of the application using them.

Some (quick) explanation

We can see the VB/A declarations that import the DLL functions in the declarations section of the MDeviceInfo.bas module:

Get to one API function

Instead of crippling our code with calls to each of the DLL functions, we can create one single function that does it all:

This way, we don’t have to worry about forgetting to call the DICreateDeviceInfo() and DIDestroyDeviceInfo() functions that are necessary for the DLL to do its job without crashes.

We gather the retrieved device information in an easy to access structure, in the following (public) Type definition:

Another way of doing it is using a class module. We’ll do that in the next post, where we set up the ActiveX OOP EXE server that will notify us of the devices arrival/removal in real time.

Sample usage

Take a look at the demo() procedure in the MMain.bas module:

About commercial use

If you’re following me thru this coding journey in my latest posts, you’ve probably noticed that I’m putting out all of the code and other assets under either the MIT or the GPL v2 licenses. While the works under the MIT license are completely free to use in all scenarios (commercial or not), that’s not true for works under the GPL v2 license, where you have to give these assets or your derived ones back to the community or whomever you distribute it.

Talking about the journey, if you’re wondering why I’m starting to making code and posts rain in these days (well, I mean, considering my personal publishing scale), allow me to give you a hint: I’ve started my sole ownership company as an independent software developer, back in may 1995…

So, I’m however going to set up a dual licensing system, so you can reuse the code/assets I publish here (or thru devinfo.net), in your commercial applications without worries. I know one buck has not the same value depending on our situation or where we live. Fear not, I can’t make specific promises for now, but I’ll do my best to be fair and affordable for everyone, leaving open the option to paypal me for a coffee or a beer, or just leave a donation (on which I’ll give back somehow), when it is reasonable.

Anyway, if you made it thru here, thank for your attention, don’t hesitate to share a link to this post where you see fit, open or join the conversation here, or drop me a tweet (I’m @francescofoti), I’ll always appreciate that.

And see you soon, because we’re not done yet 😉

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, which uses an out of process 32bits ActiveX EXE server written in Classic Visual Basic (VB5), that’ll work with 32 and 64bits VBA versions.

I’m trying not to use C (or any other language/tool) to write a DLL, although that would be also easily possible.

The windows API function that allows us to be notified when a user session is locked or unlocked (among other notifications) is the WTSRegisterSessionNotification() function.

Solution 1: The Classic Visual Basic 5 solution

We generate a simple 32bits standalone EXE application:

Get the binaries:

SessionLockDetector.exe (13.5 K, MD5: 57f23cb31c6f9ccd2a35c52384b90ff5)
If you need the VB5 runtime DLL (should already be present on XP up), download it here, unzip it and drop it in the same directory as the SessionLockDetector.EXE (.zip MD5: 7f32b01c04b0208ac917d7e266eb6709, extracted .dll MD5: eac679185ad621eeace9b6b286372f27).

Get the source code: SessionLockDetector (standalone) Visual Basic 5 project.

This task in quite easy in VB5 and it is also nicely stable. We just have to subclass the Form to be able to receive and handle the windows messages sent by the system.

Solution 2: The (very) bad solution

You might be tempted, as I did, to simply jump into a new Access database and apply the same technique. Just add the standard and class modules from the vb5 project, and subclass an Access Form like we did for the VB5 Form.

But Access is a different beast than vb5.

Subclassing the Form (in VBA) will make Access quite irresponsive at some point, because the subclassed form will throw a lot of messages in the WindowProc hooked into the (single threaded) VBA interpreter. Access will enter an almost unbreakable loop trying to keep up handling the event cascade while also trying to do its own work (you will see the VBA IDE window title bar endlessly update its text, if you try to run the sample database; but don’t do it please, take my word for it). At this point, even Windows itself (the desktop explorer process) will become frighteningly irresponsive. So take at look at this database code if you’re curious, but, once again, DO NOT EXECUTE / OPEN the “frmMain” form it contains (open in design mode only, don’t double-click it).

You’ve been warned. Download the very bad database that you shouldn’t open, at your own risk.

Solution 3: The hybrid solution

This is where we build an ActiveX EXE server with the same VB5 code that we used in the first solution, but we wrap the functionality inside an automation class. This will be the “LockUnlockDetector” class (which fully qualified with the name of the ActiveX server is “SessionLockDetector.LockUnlockDetector”), that will throw just two events, one for when the session was locked (OnSessionLock), and the other when it unlocks (OnSessionUnlock).

Building the VB5 ActiveX EXE server

Here’s the code of the LockUnlockDetector class module (VB5, 32bits):

By the way, according to the coding guidelines published here, we’re creating a class which will be made available outside of the VB project (that’s the goal), so we do not use any prefix before the class name (no “C” preceding the name).

To create an ActiveX EXE server, we set the properties of the project like that:

Now every object instance of class LockUnlockDetector will run in its own thread, and as this thread is in the EXE, it runs in the background and isolates the host (Access) from any problem occurring in the EXE.

Best of all, even if the EXE is 32bits, thanks to the beautiful architecture of COM (ie. ActiveX), cross platform calls are transparently marshalled and a 64bits Access VBA host can also use these 32bits automation classes.

The only disadvantage of this solution, is that we have to register the EXE server on the target machine.
In order to do that, we open an console window in administrator mode, navigate to the directory where the compiled EXE server is, and run it. This will silently write the required windows registry entries, and we’ll see the automation server appear in the Project References dialog of the VBA IDE in Access.

To use the detector class in Access, we then add the reference to our project:

Then we create a form (named frmMain here), and we just need this code:

Then we can respond to Lock/Unlock events as we wish:

In the provided sample Access database I added an history table to store the events, that’s what the AddHistoryRecord function does:

Downloads

AxSessionLockDetector.EXE – ActiveX EXE server binary (if you’re missing MSVBVM50.dll see the above download link in solution 1).
SessionLockDetector – VB5 ActiveX EXE server project and source code.
SessionLockDetector.accdb sample database using the EXE server (remember to register the SessionLockDetector ActiveX server (AxSessionLockDetector.exe) binary by running it once with administrator privileges).

Then

  • You can extend the functionality of the ActiveX EXE server to be notified for other events supported by the WTSRegisterSessionNotification windows API, and raise appropriate events. You can write a windows service that directly uses the server or transform solution 1 to be a windows service.
  • You can also modify solution 1 to be notified for other supported events and take any action you need, like running a batch file.
  • You can use the hybrid solution in your Access projects to be able to do smart things like freeing database or resource locks once a user locks its workstation.

MIT Licence

Copyright © 2019, Francesco Foti, devinfo.net.
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. The Software is provided “as is”, without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fitness for a particular purpose and noninfringement. In no event shall the authors or copyright holders (Francesco Foti) be liable for any claim, damages or other liability, whether in an action of contract, tort or otherwise, arising from, out of or in connection with the software or the use or other dealings in the Software. Except as contained in this notice, the name of Francesco Foti or devinfo.net shall not be used in advertising or otherwise to promote the sale, use or other dealings in this Software without prior written authorization from Francesco Foti.

How to implement a circular queue in VBA (Excel)

Animated view of the class in action

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 both ways.

These temporal modules can be implemented in many ways, using for instance fixed or dynamic memory arrays, or linked linked lists.

This is a sample implementation of a queue in a fixed size memory “circular” memory array, in a VBA class module.

“Circular” meaning that we use a fixed size array to store queue elements (in this case strings), and as we push (QPush) an pop (QPop) elements in the queue, we use “pointers” (“qfront”, “qback”) to keep track of where the queue head and tail elements are in the array. “Pointers” being in this case array element indices of the (fixed size) memory array.

Download this Excel workbook here. (MD5 checksum: 46de8d5d18a25c02dbcff7dac9dc88ee)

Please note that this is a workbook containing macros (.xlsm), you’ll have to allow them to execute when opening the document.

About this implementation

The queue itself is implemented in the “CQueue” class module.

We create a queue with the (spoiler) “CreateQueue” method, which takes the maximum number of elements we can store in the queue, as a parameter. Here’s the function signature:

To be able to show the inner workings of the module in the Excel worksheet, I had to expose (as Friend) some methods that should remain private in a final implementation; they are wrapped in a conditional compilation directive, testing on the “USE_FOR_EXCEL_DEMO” project conditional compilation argument that you’ll find defined in the project properties (from the VBA editor, menu tools, entry project properties):

From here

  • We can change the type of the array to store something else than strings (variants, object references, integers, etc…). We could for instance use integers that would be array indices of another array where the data elements are stored.
  • We can persist the queue on disk quite easily, by writing the header information in a file, and then the queue elements. We a bit of more work, the queue can then be shared and accessed by different applications using simple file/region locking mechanisms of the operating system.
  • We could implement stacks and general double ended queues in the same way. We would have something like “Push” and “Pop” methods for a stack, and “RQPush”, “RQPop”, “LQPush”, “LQPop” for a general double ended queue.

The code presented here follows these coding guidelines. Enjoy 🙂

Copyright © 2019, Francesco Foti, devinfo.net.
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. The Software is provided “as is”, without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fitness for a particular purpose and noninfringement. In no event shall the authors or copyright holders X be liable for any claim, damages or other liability, whether in an action of contract, tort or otherwise, arising from, out of or in connection with the software or the use or other dealings in the Software. Except as contained in this notice, the name of Francesco Foti or devinfo.net shall not be used in advertising or otherwise to promote the sale, use or other dealings in this Software without prior written authorization from Francesco Foti.