Home > IT, Solved problems > Non-blocking notifications/ Toaster Popups for Microsoft Access (VBA)

Non-blocking notifications/ Toaster Popups for Microsoft Access (VBA)


Hello there!! Yes! Been another long time from my last post. Absolutely wonderful how much support, contacts, messages, critics & love I get. Thank you all and you guys are amazing. Smile SmileDue to the nature of my profession, I don’t quite get time to keep up with my blogs now. But I try to get some time for new posts. Like right now I’m writing this blog while crossing the English Channel. Yes off to Paris for a weekend.. Smile Smile

I thought this is worth sharing as there are plenty of developers still working on MS Access who wants some nice UI functions. So here it is!.

Billy, –my new online friend- who works on a Microsoft Access project, asked (wished himself) if there is a way to replicate “Toastr” pop-ups for Microsoft Access (VBA).

Similar to Toastr, popups should allow customisation. Such as the position, animation, background color, dragable, transparency etc.

Mainly to show non-blocking messages. Something that user should know but not necessarily need to interact with it.

To achieve this in VBA, a borderless form and few windows APIs can be used.

1. An API to place the form where we want (ideally somewhere near to where the user is focusing. In case of multiple screens)

2. To make the form transparent

3. Fade in/out (some animation) to mimic toasting effect.

4. Dragable. In case if the toast is blocking users view and user wants to move it somewhere.

 

The end result will look somewhat like this. (Written in pure VBA using some APIs)

nomal_toast[3]

Billy was happy but NOT “happy happy” with this result. Since MS office is single threaded, fading-in, fading-out or any loop operation will eat the entire applications processing cycle. Billy didn’t like the fact that his application was waiting when showing multiple toasts.(VBA really struggled when 10 or more toasts were cooked simultaneously. (Don’t ask me why on earth he wanted to show 10 toasts at a time)

In essence, this was not a “non-blocking” notifications.

He said,

No wonder, why I couldn’t find anything similar online. It’s not worth doing in VBA because of the single thread limitation”

Oh well! no one done it yet? sounds like a challenge to me! Smile

I thought, what if you could

Write your own .NET DLL?

Surely this should save (single threaded) host application’s processing cycle?

A quick search on the internet gave me various results how to access .NET DLLs from VBA. one in particular(1,2) was very interesting. Which wraps C# DLLs in a way that VBA could read it. I quickly followed Robert Giesecke template and re-wrote the VBA toasting script within C# class.

Here comes the technical part:

You can consume .dlls in VBA in two different way. Either the .dll is already available in GAC (com visible / registered .Dlls) or load the .dll dynamically.

I didn’t like the fact that your .dll have to be registered. Clients might or might not want to install/register a .dll. So, I went with loading dynamically option.

To load a .dll dynamically in VBA, you need to prepare something.

1.Register/Declare the Windows API calls to load & unload DLLs

2.Wrap all your dll functions within a class and pass it to the caller as object.

 

Loading and unloading would be: (add ptrSafe accordingly)

Private Declare Function FreeLibrary Lib “kernel32” (ByVal hLibModule As Long) As Long
Private Declare Function LoadLibrary Lib “kernel32” Alias “LoadLibraryA” (ByVal lpLibFileName As String) As Long

 

I have wrapped all our functions within a class (KRISH_VBA_TOOLS). We also need to register this class within VBA so we could consume it like any other classes.

I put the .dll in my app folder hence I don’t need the absolute path name.

#IF VBA7

Public Declare PtrSafe Function KRISH_VBA_TOOLS Lib “VBA_TOOLS.dll” () As Object

#ELSE

Public Declare Function KRISH_VBA_TOOLS Lib “VBA_TOOLS.dll” () As Object

#END if

NOTE: MS ACCESS VBA will expect you to provide absolute path for any .dlls that you load. In our case, we are going to load the .dll manually and leave the abstract declaration pretending that we provided the dll path. (I needed this way because I use option Explicit in module level)

Loading the vba_tools.dll and toasting some notifications would be.

Public Function FN_TOAST_DLL(iMessage As String, Optional iCLOSE_DURATION As Long = 3000, Optional iType As String = “success”, Optional iANIME_DURATION As Long = 1000, Optional iFONT_COLOR As String = “#FFFFFF”, Optional iX As Long = 0, Optional iY As Long = 0, Optional iANIME_DIRECTION As Integer = 1, Optional iPARENT_HWND As Long = 0)

On Error GoTo LABEL_EXIT_ROUTINE:

If gTOASTER Is Nothing Then
LoadLibrary (FN_APP_GET_BASE_PATH & “CCMSDLL.dll”)
Set gTOASTER = KRISH_VBA_TOOLS()
GoTo LABEL_TOAST
Else
GoTo LABEL_TOAST
End If

LABEL_EXIT_ROUTINE:
On Error GoTo 0
Exit Function

LABEL_TOAST:

Select Case iType
Case “error”
iType = “#F76160”
Case “success”
iType = “#26ad82”
Case Else
iType = “#26ad82”
End Select

Dim mRect As RECT
If iPARENT_HWND <= 0 Then
If iX = 0 And iY = 0 Then
GetWindowRect Application.hWndAccessApp, mRect

iANIME_DIRECTION = 0
End If
Else
GetWindowRect iPARENT_HWND, mRect
End If

‘set up the position
iX = mRect.Left + 360
iY = mRect.Top + 1

On Error Resume Next
gTOASTER.FN_SHOW_TOAST iMessage, iCLOSE_DURATION, iType, iANIME_DURATION, iFONT_COLOR, iX, iY, iANIME_DIRECTION

End Function

Public Function FN_APP_GET_BASE_PATH()
Dim FN As String
FN = Application.CurrentProject.path
If VBA.Right(Application.CurrentProject.path, 1) <> “\” Then FN = FN & “\”
FN_APP_GET_BASE_PATH = FN
End Function

Note: FN_APP_GET_BASE_PATH is a function that returns application path. gTOASTER  is a public/Global Object where I save the KRISH_VBA_TOOLS class temporarily. By checking if gToaster nothing or not, I could determine whether I need to reload the dll or not.

Now we have our DLL loaded and got the KRISH_VBA_TOOLS object from the .dll

 

Using the gToaster object, we can call any function that is available within the KRISH_VBA_TOOLS class. In out case, “fn_show_toast”

fn_show_toast takes following parameters

Message, Duration for close in milliseconds , type of toast {error, succes, etc..}, animation duration in milliseconds, optional font color, x position on the screen, y position on the screen, animation direction {0: down, 1: up [by default its down]}

The VBA Function FN_TOAST_DLL need only one parameter to work, by default a success message is shown on the left top corner of the Access application.

 

lets see that in action…

Private Sub Command35_Click()
Dim mArgs() As String
mArgs = VBA.Split(Me.txt_toastr_Value.Value, “;”)

FN_TOAST_DLL mArgs(0), (mArgs(2)), (mArgs(1)), iPARENT_HWND:=Me.hWnd, iANIME_DIRECTION:=0

End Sub

Dll_toast[6]

 

Nice…., now Billy can show 10 or even 100 toasts without stopping his application. Moreover he can now take full advantage of C# Smile Smile

PS:Code is written to prove this concept (can be clunky ) and I’m releasing this code without any copyrights but I’ll be glad to get some credits if you are copying/improving or re-distributing this code.

If you fancy having this in your Microsoft Access application.. Please get a copy of the dll and post your comments how you liked it 🙂 🙂

here get it Smile

Download DLL

64bit:

Download 64bit

 

What a timing, we finally arrived port of Calais 🙂

DSC_1846[1]

Ahh.. you can buy me something! Only if you feel so Smile

Donate

 

Thank you for reading and post your comments.

 

 

Advertisements
  1. June 18, 2017 at 1:05 am

    Hey Krish, Very interested in this. Was trying to download the dll to check it out. No longer available? Thank you in advance…

    Like

  2. Timo Schwenk
    August 31, 2017 at 9:13 pm

    Hi Krish, i tried to transform it to Excel 2007 but it didn´t work. Would you upload all files in one zip-file?

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: