Now that you have a good understanding of VBA and how to use it to produce good interfaces to your worksheet applications, it is time to expand this knowledge to the unknown, incomprehensible world of the Windows application programming interface (API) that makes Microsoft Windows work.
KeywordsApplication Programming Interface Pixel Count Bitmap Image Window APIs Application Programming Interface Function
Now that you have a good understanding of VBA and how to use it to produce good interfaces to your worksheet applications, it is time to expand this knowledge to the unknown, incomprehensible world of the Windows application programming interface (API) that makes Microsoft Windows work.
Why should you care about it? For one simple reason: by using the Windows API, you can extend VBA capabilities to the next level, doing programming tricks that you see in other software interfaces and that you can’t do using just the regular VBA language.
In this chapter, I will give you some guidance about the Windows API: what it is, how to declare them, how to call DLL API procedures, and how you can use the enormous amount of VBA/DLL code available on the Internet.
You can obtain all the files and procedure code in this chapter by downloading the Chapter10.zip file from the book’s Apress.com product page, located at www.apress.com/9781484222041 , or from http://www.ProgrammingExcelWithVBA.4shared.com .
The Microsoft Windows API
In the old DOS days, every time an executable program was created, all the code it needed to perform its many functions was statically linked into the executable code. So, if 100 programs were doing string operations (like using the Left ( ) and Mid ( ) functions), all of them needed to compile these string functions inside the executable file, reproducing the same static code in every executable file that needed it.
Microsoft Windows changed this by exposing an operating system based on an API uses dynamic link libraries (DLLs) to offer all the code a program needs to exist on the Windows operating system. Instead of storing the code functionally inside each executable file, programmers have the ability to use small code declares that call the functionality they need from the desired DLLs, leaving to the operating system the task of keeping the code available. These commonly used DLLs are system files with the .dll extension normally stored in the \Windows\System folder, unless they were specifically created for the program that needs them, in which case they probably reside in the program folder.
Besides many other things, these DLLs functions are responsible for creating a window, changing the window properties, interacting with the many protocols available (like TCP/IP, HTTP, MailTo, and so on), playing multimedia files, printing, and saving files—all the operations you graciously perform on the Microsoft Windows system using the same dialog box in every program.
There is an important detail to know about DLLs: they are all written in the C or C++ language, which is quite different from VBA, from the number of variable types it can receive to the way they work. To use a DLL library of functions, you need to know which function you need to call and the DLL file where it resides, the arguments it needs, its presentation order, how they are manipulated by the DLL function, and what value it returns.
As you can see, when it comes to Windows API programming, there is a steep learning curve for VBA programmers.
In the next sections, I will give you some information about DLLs and the code you can grab from the Internet to create some special effects. This is just a primer of the possibilities to teach you simple tricks that may enhance your worksheet applications with a professional touch.
Using Declare Statements
By definition, a Declare statement is a way to grant access to a DLL function inside a VBA module. It must be made in the declaration section of the module as a private or public procedure, using one of these syntaxes, if it is a Function or Sub procedure:
Function, Sub: This indicates whether it is a call for a Function or Sub procedure.
PublicName: This is the procedure name in your VBA project.
LibName: This is the DDL file where the procedure resides.
Alias "alias": This is the original procedure name inside the DLL file.
Variable [As type]: These are the procedure arguments and expected type values.
DLLs declared on standard modules are always public by default.
DLLs declared on UserForm or Class modules are private by default and must be preceded by the Private keyword.
To avoid name conflicts on your code modules, you must use the optional Alias clause to give an alias name to each DLL function declared in your code.
These are the basics about DLLs declares. The next code instructions show how the SetTimer ( ) function of the User32.dll library can be declared as a public procedure on any standard module:
To avoid conflict with other possible declarations in other modules, it is usual to give the function call an alias by adding a personal prefix to the procedure name, as it must be used by the VBA project (I personally use the FM_ prefix, from Flavio Morgado, when necessary).
If you declare it inside a UserForm or a Class module , it will be private to the module, with no need to alias the procedure name, but it is imperative that you prefix it with the VBA Private keyword.
Besides those basic instructions, you do not need to know how a DLL procedure must be declared. Just copy and paste the declaration code from the Internet to the desired code module, turn it private or alias it, and it is ready to be used by your VBA code.
Many API procedures are based on predefined values that you must pass to their arguments so they work properly. These values are always documented along the API declaration and must be declared in the module declaration section where the procedure is declared.
These constant values are mainly bit flags, commonly declared as decimal or hexadecimal values (a value that begins with &H characters that define the constant using another number scale). The next instruction declares the GWL_STYLE constant using a decimal value:
The next instruction declares the WS_CAPTION constant using a hexadecimal value:
Since a single API procedure can use many different flags, alone or combined, to give the desired effect, any code module that uses API declarations ends up with a lot of constant declarations, and many of them are not always used in the code (as you will see in the next sections).
Have you ever wondered how the mechanism behind Microsoft Windows allows its window programs to react to mouse clicks? If you have multiple windows opened, one in front of the other, as soon as you click any part of a window that is underneath the window pile, that window immediately comes to the front, receiving the system focus and activating the selected window and its command.
This is possible because every window has a unique identifier called a window handle. You can think of this like a suitcase handle—it allows you to grab and take the suitcase anywhere you want.
The window handle is a long integer that uniquely identifies each window and allows the Microsoft Windows operating system to control it. In fact, in Microsoft Windows, not just an application window has a handle; everything that can react to mouse events has its own window handle. So, besides the application handle, every other window control has a handle. The borders and the close, minimize, restore, and maximize buttons have their own handles. And every other application part like menus, toolbar controls, and so on, has its own window handle.
To control such an immense number of window handles, Microsoft Windows has what is called a Windows handle tree, where each main application handle behaves like a basic folder, and all handles inside the application behave like subfolders. When you close an application handle, all the handles are also closed, releasing system resources.
This concept is important, because in Chapter 9, you used the Excel. Application . Hwnd property (which returns any Excel window application handle value) as a way to differentiate the Application object (the one where the code is running) from other possible open Excel. Application objects opened by the code by just comparing their Hwnd property values.
Be aware that not all window structures are equal! In fact, they belong to different window classes, according to the type of data they can contain. For example, applications that can open multiple documents—called multiple document interfaces—and use an MDIForm to be built (such as Excel, Word, Access, and so on) are associated with the Omain window classes, while the document opened within them comes from a different window class called MDIClient (like each workbook window inside Excel or document window inside Word), which comes from the Form class. The UserForm window you use from Visual Basic is still another different beast, coming from the ThunderFrame class. Each one has its own handle.
This is a pure concept, because when you come to use Windows DLLs, it is often necessary to grab the window handle of the object you want to manipulate in code so you can obtain the desired result.
Class Instance Handle
All objects you create as instances of a Class module (like the SheetDBEngine class) have their own handle, so they can react to system messages. To recover any class object instance handle, you must use the undocumented VBA ObjPtr ( ) function (read as “object pointer”), which returns the pointer to the interface referenced by an object variable, with this syntax:
ObjectVariableName: This is the class object variable instance whose pointer (handle) you want to recover.
The next example shows how you can recover a class object instance variable handle in your code:
Note in the previous example that the cClass1 object variable was declared As New Class1, meaning that it is created the first time it is referenced by the code.
Creating a Timer Class
SetTimer ( ) starts the timer and defines a VBA function to be called when the timer expires, beginning another timer. It returns a Long integer indicating the timer ID.
KillTimer ( ) kills a timer already set using the long integer that represents it.
Investigating the SetTimer ( ) function on MSDN web site, you will notice that its syntax is quite complex to understand from a novice VBA programmer’s perspective. It can be translated to the following:
hWnd: This is the window handle to be associated with the timer. Use the Application . Hwnd property to define it.
nIDEvent: This is a handle to an object that will receive the timer event (UserForm or Class handle).
uElapse: This is a long integer for the timer interval in milliseconds (maximum interval is 231 = 2.147.483.648 ms ≅ 596 hours, or 24.8 days).
lpTimerFunc: This is the address of the callback procedure to be called when the timer expires. This procedure must exist on the object represented by the nIDEvent.
If the function succeeds and the hWnd parameter is NULL, the return value is an integer identifying the new timer. An application can pass this value to the KillTimer function to destroy the timer.
If the function succeeds and the hWnd parameter is not NULL, then the return value is a nonzero integer. An application can pass the value of the nIDEvent parameter to the KillTimer function to destroy the timer.
If the function fails to create a timer, the return value is zero. To get extended error information, call the GetLastError API.
Although the documentation does not clearly explain it, most DLLs that need a callback Sub procedure to call require that it be declared this way (where publicname can be any name you want):
PublicName: This is the procedure name as declared in the VBA code module.
hWnd: This is the window handle associated with the timer.
uMsg: This is the timer message sent.
idEvent: This is a long integer that identifies the object handle that will receive the timer message when it fires (specified by the nIDEvent of the SetTimer ( ) API function).
dwTime: This is the number of milliseconds that have elapsed since the system was started. This is the value returned by the GetTickCount API function.
GetTickCount( ) is a DLL function from Kernel32.dll that retrieves the number of milliseconds that have elapsed since the system was started, up to 49.7 days using a 10 to 16 milisecond precision. It is a high-performance timer counter that can be used instead of the VBA Time( ) and Timer( ) functions.
Once a timer is set, you can reset it by calling again SetTimer ( ) with another timer interval or you can stop it by calling the KillTimer ( ) API function, which has this syntax:
hWnd: This is the window handle associated with the timer.
nIDEvent: This is a handle to the object that received the timer event.
To create a useful VBA timer code using the SetTimer ( ) and KillTimer ( ) Windows APIs, you must use a class module so you can create as many timer instances as needed using a single, centralized code.
Extract the Timer Class.xlsm macro-enabled workbook from the Chapter10.zip file, press Alt+F11 to show the VBA IDE, and double-click the Timer Class module to show its code. You will see that it declares three module-level variables (one to hold the timer ID and two others to hold the class Interval and Enabled property values), both SetTimer ( ) and KillTimer ( ) DLL procedures, and the Timer( ) event in the class module declaration section.
To set the timer interval, the Timer Class uses a pair of Property Let/Get procedures to implement the Interval property. Note that it just accepts values greater than zero, using the mlngInterval module-level variable to store it.
The timer is enabled/disabled using another pair of Property Let/Get procedures to implement its Enabled property.
As you can see, the Property Let Enabled( ) procedure is used to set the timer. It begins verifying whether the argument bolEnabled = True and whether the class module-level variable mlngInterval > 0, indicating that the Timer.Interval property has been set. If this is true, it calls the SetTimer ( ) API to set the timer; otherwise, it calls KillTimer ( ) and resets the mlngTimer pointer.
The mWnd argument is set to the Application .hWnd property (the handle of the Excel application window).
The nIDEvent argument uses the VBA ObjPr(Me ) function to return the class instance handle, as explained in the section “Class Handle” earlier in this chapter.
The uElapse argument is set to the mlngInterval module-level variable.
The lpTimerFunc argument uses the VBA AddressOf statement to return the address of the TimerProc ( ) procedure, from the basTimer standard module.
The SetTimer ( ) API will return a long integer to the mlngTimer module-level variable, indicating the ID of the timer associated to this instance of the Timer class.
To raise the Timer( ) event, the Timer class also declares the RaiseTimer( ) method (as a Public Sub procedure of the class module).
And whenever the class is destroyed, its Class_Terminate( ) event fires, calling the KillTimer ( ) API to stop the timer associated to this instance of the class module (if any). Note that it uses the Application . Hwnd property and the mlngTimer ID to stop the timer.
The TimerProc( ) Procedure
Since a call to the Timer class’s Let Enable(True) property procedure sets a new timer, passing to the SetTimer ( ) API the memory address pointer of the TimerProc ( ) procedure, if you inspect it in the basTimer module, you will note that it executes this code:
Note that it changes the original IDEvent as Long argument to the ByVal clsTimer as Timer object, which is evidence that object interfaces are in fact long integers. Since SetTimer ( ) uses the ObjPtr ( Me ) value (the handle to the Timer class instance) to the nIDEvent argument, this value is internally passed by SetTimer ( ) to the clsTimer argument, effectively identifying the class module. The code just calls the clsTimer.RaiseTimer event to raise the timer event.
Using the Timer Class
To use the Timer class, you just need to declare an object variable as Private WithEvents ... as Timer on the declaration section of the UserForm module.
You then use the Timer.Interval (in milliseconds) and Timer.Enabled properties to activate the timer.
You use the mTimer1_Timer( ) event to do whatever you want in the code.
The frmTimer UserForm declares four Timer module-level variables: mTimer1 to mTimer4.
When you click each timer’s Enable CheckBox, it instantiates the associated module-level variable, sets the timer interval, and enables/disables the timer. This is the chkInterval1_Click( ) event.
To see the mTimer1 object variable work, the UserForm code uses the mTimer1_Timer( ) event to set the txtTimer1 TextBox value.
To change the mTimer1.Interval while the timer is running, the code uses the txtInterval1 TextBox’s Change( ) event.
You can make a more robust Timer class by validating the Interval value in the Property Let Interval( ) procedure.
It is surprising that the VBA UserForm object doesn’t expose a Hwnd property, like the Application object does. Although the UserForm object doesn’t have a handle in design mode, it must have a handle as soon as it is loaded so it can react to system and mouse events. This is a runtime where only the Hwnd property is available.
The only way you can get any UserForm handle is to use the FindWindowA ( ) API function from User32.dll, which can be declared in this way:
lpClassName: This is the class name of the object whose handle you want to find. If lpClassName is NULL, it finds any window whose title matches the lpWindowName parameter.
lpWindowName: This is the window name (window caption text).
The FindWindow( ) API function returns a Long Integer indicating the window handle. Note that this API procedure declaration gives the fm_FindWindow alias to the FindWindowA ( ) function, which expects to receive two arguments: the VBA UserForm class name (ThunderFrame) or NULL and the UserForm caption property. This procedure returns to the UserForm handle if it finds the window by its caption property. It returns NULL if it fails to find any window match.
Many UserForm handle procedures you find on Internet will temporarily store the current UserForm caption in a local variable, change the UserForm.Caption property to an improbable value (such as Me .Caption and Timer, where Timer returns the number of seconds past since midnight), and call the FindWindow( ) API to get the UserForm handle, restoring the UserForm caption to its original value before the procedure finishes.
The technique is quite simple. It declares the varHwnd as Variant variable (since Variant is the only variable type that can receive a null value) to receive the fm_FindWindow( ) return value, and if this value is not null, it uses the VBA CLng(varHwnd) function to convert it to a Long Integer that is used as the Hwnd ( ) procedure returned value.
Setting Bit Values
The Windows system stores object values using a Long integer, because a Long integer has up to 32 bits that can be associated to 32 different Boolean options, which can be turned on/off by just changing any bit value from 0 (to disable) to 1 (to enable) and vice versa, using the OR, AND, and NOT VBA operators.
Let’s suppose that a given set of properties is stored using an 8-byte value (a value that has just 8 bits). All these properties are represented by the integer number 231 (using a decimal representation). By using the Windows Calculator applet with the Programming option set (Show ➤ Programming menu), you can easily see that the 231 decimal values is represented as the binary value 11100111.
Counting from right to left, the 1, 2, 3, 6, 7, and 8 bits are set (value = 1), while the 4 and 5 bits are not set (value = 0).
The OR operator combined all 8 bits from both binary numbers, setting just the fourth bit to 1 (or True, in programming language) and keeping all other bits on their default states. It OR ed two 8-byte numbers, meaning that each bit from these two numbers must be set if either one or the other is set!
Mathematically speaking, the OR operator is equal to the + operator, giving the same result as 231 + 8 = 239.
Although any number can be represented using a different number scale (like octal, decimal, or hexadecimal formats), it is important to note that the leftmost bit (the 8th bit for an 8-bit byte, or the 32nd bit for a 32-byte number), also known as the most significant bit, is reserved to the number sign on the decimal scale. So, it is not uncommon to face constant flags using decimal negative numbers, meaning that the most significant bit was set (like GWL_STYLE = -16).
Animating the UserForm Window
You can change the way a UserForm loads and unloads by using the Function AnimateWindow ( ) API from User32.dll, which has this syntax:
Hwnd : This is the handle of the UserForm.
dwTime: This is the time in milliseconds the animation takes to play.
- dwFlags: This is the type of animation associated to these constants.
AW_ACTIVATE = &H20000: This activates the window (do not use it with AW_HIDE ).
AW_BLEND = &H80000: This uses a fade effect to show the window (or hides it if used with AW_HIDE).
AW_CENTER = &H10: This expands the window outward.
AW_HIDE = &H10000: This hides the window if used with AW_BLEND (the windows is shown).
AW_HOR_POSITIVE = &H1: This animates the window from left to right.
AW_HOR_NEGATIVE = &H2: This animates the window from right to left.
AW_SLIDE = &H40000: This uses slide animation.
AW_VER_POSITIVE = &H4: This animates the window from top to bottom.
AW_VER_NEGATIVE = &H8: This animates the window from bottom to top.
Except when using animation to hide a window (using dwFlags = AW_BLEND or AW_HIDE), you can use the OR operator to combine AW_HOR_POSITIVE or AW_HOR_NEGATIVE with AW_VER_POSITIVE or AW_VER_NEGATIVE alone or with the AW_SLIDE flag to produce a diagonal animation. The AW_ACTIVATE flag must always be used to show the animation.
The UserForm_APIs.xlsm macro-enabled workbook (that you can extract from the Chapter10.zip file) has the basUserFormAPIs standard module, which declares in its Declaration section thefm_FindWindow( ) (to find the UserForm handle) and fm_ AnimateWindow ( ) (to animate a UserForm window) aliased API procedures, along with all animation constants needed.
To deal with the AnimateWindow ( ) API animation constants, basUserFormAPIs also declares the Animation enumerator to combine these animation flags using more significant names.
Note that most Enum Animation declarations use the desired animation OR ed with the AW_ACTIVATE flag (except the Disappear enumerator), and to produce a diagonal animation effect, the code uses more than one constant flag (like DiagonalToBottomLeft = AW_HOR_NEGATIVE Or AW_VER_POSITIVE Or AW_ACTIVATE).
Although Microsoft MSDN documentation for the WindowAnimate( ) function states that you can combine different flags with AW_HIDE to produce different closing effects, just the AW_BLEND constant works with AW_HIDE to produce a fade-out effect.
The Animate( ) Procedure
To produce the UserForm animation, use the basUserFormAPIs Public Sub Animate ( ) procedure, which executes this code:
The function Animate ( ) receives three arguments: frm as Object (a reference to a loaded UserForm), Animation as Animation (the desired animation enumerator), and the Optional Duration as Long = 1500 argument (animation duration, with 1,500 milliseconds—1.5 s—as default value).
It then uses the Hwnd (frm) function (as cited in the section “UserForm Handle”) to attribute the frm as Object UserForm handle to the lngHwnd variable.
The frm UserForm is then centralized inside the Excel window. (Note that it uses Application .Top + Application .Height / 2 to find the Excel window’s vertical center point and subtracts .Height / 2 = half the UserForm height; the same is done to find the horizontal center point.)
The UserForm associated to the lngHwnd Long Integer is animated as desired by calling the fm_ AnimateWindow ( ) aliased API.
To animate any UserForm when it is loaded, verify whether property ShowModal = False and make a call to Function Animate ( ) on the UserForm_Initialize ( ) event, as follows (note that the code uses the Appear enumerator in the Animation argument, accepting the default duration of 1500 ms):
If you do not set the UserForm property’s ShowModal = False, Visual Basic will raise error 400, “Form already displayed; can’t show modally,” when the loading animation finishes.
To animate any UserForm by an external procedure, you must first load the desired UserForm using the Load method and then call Function Animate ( ) to animate it, as follows:
To animate any UserForm when it is unloaded, make a call to Function Animate ( ) on the UserForm _QueryClose( ) event (the last event fired before the UserForm is terminated), using the Disappear enumerator on the Animation argument, like this (this code also uses a default duration of 1500 ms):
To apply successive animations to the frmAnimate UserForm, for every animation effect but Discard, the code needs to unload the UserForm, update the Excel interface, and load it again. This is made in the Sheet1_Change( ) event, which fires every time any Sheet1 cell value changes, executing this code:
The code declares the Animation as Animation enumerator value and verifies whether the change happens in the Animation or Duration range name.
If this is true, it uses a Select Case statement to set the desired animation enumerator constant according to the value selected in the Animation range name (note that it uses the VBA Trim( ) function to remove undesired spaces in the Animation range value, and if Disappear is selected, bolDisappear = True).
There is no way to programmatically iterate through VBA Enumerator items. The Selected Case statement is an alternative way to do this.
Then the code verifies whether Disappear was not selected by testing not bolDisappear. If this is true, it unloads frmAnimate, updates the Excel interface, and loads it again before applying the selected effect.
The animation effect (even Disappear) is then applied by calling Function Animate ( ) to frmAnimate, using the Animation variable value and the duration defined by the Duration range name.
If Disappear was selected, the UserForm is now hidden and must be unloaded.
Manipulating the UserForm Window
Thinking in API terms, when a UserForm.Show method is called, the Windows operating system gets from the UserForm the ThunderFrame class definition a Long integer that identifies many window properties and sets them accordingly.
In most form classes, these basic properties are stored in address –16 (FFFFFFFFFFFFFFF0 in hexadecimal) inside the form structure definition. If you check on the Internet for API code to manipulate the UserForm window, you will immediately note that this address is normally attributed to a constant: GWL_STYLE = -16.
This means that if you want to manipulate the UserForm window to change some of its properties, you must put code in the UserForm_Initialize ( ) event or call the UserForm.Hide method to hide it, change its properties, and call the UserForm.Show method to draw it again.
The API procedure that grabs these UserForm property values as a Long Integer number is called GetWindowLong( ) and has this syntax:
Hwnd : This is the handle of the UserForm.
Nindex: This is the position inside the UserForm class from where it must extract the Long integer.
The GetWindowLong( ) API function returns a long integer with all desired UserForm properties from the Nindex address position inside the class structure. Once you get it, you just need to know the bit position that you want to manipulate, change its value, and set it again to the UserForm structure, using the API procedure SetWindowLong( ), which has this syntax:
Hwnd : This is the UserForm handle.
NIndex: This is the position inside the UserForm class to where the Long integer must be set.
DwNewLong: This is the Long value that must be set.
Most Internet API code uses dedicated procedures to set a given UserForm property (read as “set the bit inside the ThunderClass frame”) and another procedure to verify whether the bit is set.
The UserForm Title Bar
The bit associated with the presence of the UserForm title bar inside the Long integer that represents the window properties is normally attributed to constant WS_CAPTION = &HC00000 (12582912 in decimal, 23rd and 24th bits set), although it can also be removed by setting the constant WS_DLGFRAME = &H400000 (4194304 in decimal, 23rd bit set).
It seems like it is the 23rd bit that sets/removes the UserForm title bar. You can achieve the same result using both the WS_CAPTION and WS_DLGFRAME constants.
To add/remove a UserForm title bar, you need to set/unset this bit on the Long integer property byte and call the DrawMenuBar ( ) API to change the UserForm appearance, which is declared in this way:
Hwnd : This is the UserForm handle.
So, to remove the UserForm caption, you can use code like the following (supposing that the fm_FindWindow( ), fm_GetWindowLong( ), fm_SetWindowLong( ), and fm_ DrawMenuBar ( ) aliased APIs were declared):
Do you get it? After using the Hwnd ( ) function to get the UserForm handle (associated with the frm as Object argument), you use the fm_GetWindowLong( ) aliased API to get the UserForm Long integer properties (using the GWL_STYLE constant to indicate from where this value must be retrieved), attributing it to the lngWinInfo variable.
To unset the WS_CAPTION bit from the lngWinInfo value, you use the And (Not WS CAPTION) operators, as explained in section “Setting Bit Values” earlier in this chapter.
Once the desired bit is unset, you call fm_SetWindowLong to update the UserForm Long Integer, effectively disabling the title bar bit, and call the fm_ DrawMenuBar lngHwnd API to set/remove the UserForm title bar.
This code will remove the UserForm title bar if called from the UserForm_Initialize ( ) event, because at this point the UserForm window is not still drawn by the Windows system. To call it from a Command button, you must call the UserForm Hide and Show methods to update the window, adding/removing the title bar.
By making a small code change, you can declare a bolEnabled as a Boolean argument and allow the procedure to either set or remove the UserForm title bar, as follows:
Note in the previous code that now you use the OR operator to set the desired bit (bolEnabled=True; UserForm has a title bar) or the AND NOT operators to remove the bit (bolEnabled = False). Also note that now the procedure calls the frm.Hide and frm.Show methods to allow the UserForm window to redraw, with or without a title bar.
To add/remove the UserForm Close button (the “X” button in the top-right corner), add/remove the maximize or minimize buttons, or add/remove a resizable border, you use the same code, changing the constant used to set/unset the lngWinInfo bit associated with these properties, as declared here:
The Appearance( ) Procedure
Instead of making one procedure to manipulate each property, you can write a single, centralized procedure that manipulates any one of them, according to the argument it receives.
By inspecting the basUserFormAPI standard module, you will notice that it declares all API functions and constants needed to manipulate the UserForm properties in the Declaration section. Note that it also declares Public Enum FormStyle to give more significant names to the property that must be set.
To manipulate the UserForm properties, it uses the Public Sub Appearance ( ) procedure, which executes this code:
As you can see, Sub Appearance ( ) receives four arguments: frm as Object (the UserForm to manipulate), Style as FormStyle (the attribute or attributes to be set), bolEnabled (to turn on/off the desired attribute or attributes), and the optional bolRepaint (to automatically repaint the frm UserForm).
As you will see later, sometimes it is not desirable that the UserForm automatically repaints as its properties are changed by the Sub Appearance ( ) procedure.
The code uses Public Function Hwnd ( ) to retrieve the frm UserForm handle and uses the fm_GetWindowLong( ) aliased API to retrieve the frm UserForm Long integer properties. According to the bolEnabled argument, it sets/unsets the desired bits represented by the Style argument and uses the fm_SetWindowLong( ) aliased API to set again the UserForm Long integer properties and eventually calls frm_DrawMenyBar, if the Style argument has the WSCAPTION flag set.
To effectively change the UserForm appearance regarding what it receives on the Style argument, the Hide and Show methods are called to force the UserForm to redraw, showing the appropriate properties.
The last paragraph uses “desired bits,” allowing more than one bit at a time, because you can call Appearance ( ) with some of the Style constants to set more than one property at once, if possible.
In the frmAppearance UserForm module you can see how some operations are easily made to change the UserForm window appearance, sometimes needing a small adjustment. This is the case with the chkTitleBar_Click( ) event, which fires whenever frmAppearance chkTitleBar changes its value.
When frmAppearance loads, the Initialize( ) event stores the UserForm.Height property on the mlngHeight module-level variable and uses this value to return it to the default size after a call is made to the Sub Appearance Me , TitleBar, Me .chkTitleBar procedure, adding/removing the UserForm title bar as the chkTitleBar CheckBox control changes its value. This is necessary because after the UserForm title bar is removed and set again, the form grows by the size of the title bar (comment the Me .Height = mlngHeight instruction to see this happen).
The UserForm Close, Maximize, and Minimize Buttons, and Resizable Border
To set the UserForm Close , Maximize, and Minimize buttons or a resizable border, you just need to make a call to the Sub Appearance ( ) procedure, passing it the appropriate Style constant. The next procedure code shows what happens when you click the chkCloseButton, chkMaximize, chkMinimize, and chkResizable check boxes to add/remove the UserForm properties:
Changing More Than One UserForm Property at Once
As explained, you can set the UserForm properties on the UserForm_Initialize ( ) event or change more than one property at any time by making a single call to the Appearance ( ) procedure.
The frmNoTitleBar UserForm from the UserForm_APIs.xlsm macro-enabled workbook does this! It removes the title bar using the UserForm_Initialize ( ) event and adds/removes the Close and Maximize buttons on the UserForm_Click( ) event (which fires when you click inside it), running this simple code:
Note that the UserForm_Click( ) event passes TitleBar + + ResizableBorder + MaximizeButton + CloseButton to the Sub Appearance ( ) Style argument, which allows you to make the UserForm border resizable while also enabling its Maximize and Close buttons. It uses the Static sbolEnabled as Boolean variable to alternate the on/off state of these properties.
To see them in action, double-click the frmNoTitleBar object in the VBA Object Explorer tree and press F5 to load it. You will notice that it appears without a title bar, but whenever you click inside it, the title bar automatically appears, showing the Minimize, Restore, Maximize, and Close buttons, with a resizable border.
When you set the bit that shows the Maximize and Minimize buttons, both buttons will become visible, but just the bit set will work. By showing just the Maximize button, the Minimize button will appear, but disabled, and vice versa.
Any UserForm that calls the Sub Appearance ( ) procedure from the Initialize( ) event must set the ShowModal property to False. Otherwise, the code will stop on the last procedure instruction (frm.Show) until the UserForm is closed, and a VBA runtime error 91 (“Object variable or With block not set”) could appear when the UserForm is closed, after setting its properties using the Windows APIs (change the frmNoTitleBar ShowModal property to True, open it, click inside it, and close it to see for yourself).
The UserForm Transparency
At the UserForm ThunderFrame class structure’s –20 position (FFFFFFFFFFFFFFEC in hexadecimal, normally associated with constant GWL_EXSTYLE = -20), you can recover extended window properties associated, for example, to the UserForm transparency: the value of the alpha channel or opacity value.
To create a transparent effect, you need to use the GetWindowLong( ) API to get this Long integer value, manipulate its 20th bit using constant WS_EX_LAYERED = &H80000 (10000000000000000000 in binary, 20th bit), and use the SetLayredWindowAttributes( ) API function to change the window alpha channel opacity from 0 (totally transparent) to 255 (totally opaque), which is declared in this way:
Hwnd : This is the UserForm handle.
crKey: This is the chroma key, which is a color reference that will be used as the transparent color. Use 0 to specify every color.
bAlpha: This is the alpha value used to describe the window opacity (must be between 0 = totally transparent and 255 = totally opaque).
- dwFlags: This is an action to be taken.
LWA_ALPHA = &H2: This determines the opacity of the layered window, if crKey = 0.
LWA_COLORKEY = &H1: This uses the crKey color value as the transparency color.
The Transparency( ) Procedure
To change the UserForm transparency, basUSerFormAPIs implements Public Function Transparency ( ), which executes this code:
The code calls function Hwnd (frm) to retrieve the UserForm handle, uses fm_GetWindowLong( ) with constant GWL_EXSTYLE to retrieve the Long integer associated with the extended form properties, and sets the WS_EX_LAYERED bit value to indicate that the transparency effect will be changed.
Since it receives the sngTransparency argument as a percent value (from 0 to 1) to the desired transparency effect, it must change it into an opacity value that goes from 0 to 255 (opacity is the reciprocal of transparency; 100 percent transparent means 0 percent opaque).
Once the opacity is calculated, it calls the fm_ SetLayeredWindowAttributes ( ) aliased API to change the UserForm opacity, using the crKey = 0 argument, to manipulate all colors opacity at once and passes LWA_ALPHA = &H26 (100110 in binary) to the dwFlags argument.
On the UserForm side, the frmAppearance uses the fraTransparency Frame control to contain the scrTransparency ScrollBar control and change its own transparency. The scrTransparency control has set its properties Mini = 0, Max = 100, SmallChange = 5, and LargeChange = 10, to allow changing the transparency value from 0 to 100 (by 5 transparency points when click the scroll bar arrows and by 20 transparency points inside the scroll bar at the left or right of the scroll bar button). Whenever the scrTransparency ScrollBar control value changes, it fires the scrTransparency_Change( ) event, executing this code:
In basUserFormAPIs, change the Function Transparency ( ) instruction lngHwnd = Hwnd (frm) to lngHwnd = Application . Hwnd and then drag the scrTransparency ScrollBar control on frmAppearance to change the Excel window’s transparency.
Note that if you drag the scrTransparency ScrollBar control to 100 percent transparency, the frmAppearance UserForm disappears from the Excel interface and cannot be selected anymore. It is still loaded but unreachable.
The Fade( ) Procedure
You can use Sub Transparency ( ) to create a “fade-in” or “fade-out” effect to animate any UserForm like the AnimateWindow ( ) API does using the AW_Blend constant.
You just need to use a For...Next loop to loop 100 times, changing the UserForm transparency from 100 percent to 0 percent to fade in and from 0 percent to 100 percent to fade out, taking care to use a specific delay in milliseconds at each loop step.
This was implemented in the basUserFormAPI standard module using two different procedures: Public Sub Fade ( ) is used to interact with the user regarding the fade effect, and Private Sub FadeEffect( ) is used to apply the UserForm transparency in the desired direction (fade-in or fade-out).
To allow an easy selection of the fade effect and speed, two enumerators were declared in basUserFormAPI: FadeMethod and FadeSpeed.
To apply the fade effect, use the Public Sub Fade ( ) procedure, which executes this code:
Note that it receives three arguments: frm as Object (the UserForm), Fading as FadeMethod (the fade direction), and the Optional Speed as FadeSpeed = Slow argument (the fade speed).
Independent of the fade method selected, it first calls FadeEffect(frm, Fadding , Speed) to apply the desired fade method at the desired speed, which executes this code:
Note that the Sub FadeEffect( ) declares the constants conSlow = 0;04 and conFast = 0.01, which relate to tenths of milliseconds, and attributes to sngMaxTime the desired constant value according to the Speed argument.
It then begins a For...Next loop that will execute 100 times, attributing to sngTransparency the desired transparency percentage.
If Fading = FadeIn or Fading = FadeInFadeOut, sngTransparency must go from 100 percent to 05 transparency, so it changes sngTransparency accordingly.
Apply the desired transparency effect to the UserForm associated to the frm as Object argument.
Now it is time to give a delay between each loop step. This is done by attributing the VBA function Timer( ) value (the number of seconds since midnight) to the sngTimer variable and executing a Do...Loop while the difference between sngTimer and Timer( ) is smaller than the sngMaxTime milliseconds value.
When the loop ends, the UserForm changes the transparency from 0 to 100 (or vice versa), during from 100 * 0.01 ms ≅ 1 second (if sngMaxTime = conFast = 0.01) to 100 * 0.04 ≅ 4 seconds (if sngMaxTime = conSlow = 0.04).
The code returns the control to the Sub Fade ( ) effect, which now verifies whether the argument Fading = Fad InFadeOut, and if this is true, it verifies whether the WaitSeconds argument is greater than zero (indicating that the fade must do a stop before fading out).
If WaitSeconds > 0, the desired delay is applied, using again a Do...Loop that uses the VBA DateDiff("s", sngTime, Time) function to define the difference between sngTime and another call to the VBA Time( ) function in seconds. The loop will last while this difference is smaller than WaitSeconds.
Having or not executing a delay, since argument Fading = FadeInFadeOut, it must now execute a fade-out effect at the same fade-in speed.
And if the Fading argument is either FadeOut or FadeInFadeOut, the frm UserForm must be unloaded, ending the fade effect.
Using frmFadeIn UserForm
You can use the frmFadeIn Slow ControlButton to open the frmFadeIn UserForm and watch it fade in using the default slow speed, because it executes this code on the UserForm_Initialize ( ) and Activate( ) events.
Note that it first applies a 100 percent transparency to itself using Transparency Me , 1 on the UserForm_Initialize ( ) event, and when the Activate( ) event fires, it uses the Repaint method to appear 100 percent transparent before calling Fade Me , FadeIn to slowly fade in to the user environment!
Using frmFadeOut UserForm
The frmFadeOut UserForm can be tested by clicking the frmFadeOut Fast ControlButton, which is waiting to be clicked to fade out, executing this code:
Note that now it needs to add a 0 percent transparency using Transparency Me , 0 on the UserForm_Activate( ) event before fading out itself using a call to Fade Me , FadeOut, Fast on the UserForm_QueryClose( ) event (the last event fired before the UserForm_Terminate ( ) event), when the UserForm is unloaded by any means.
Using frmFadeInFadeOut UserForm
To apply an automated fade-in/fade-out slow effect with a three-second wait between them, use the frmFadeInFadeOut ControlButton, which loads frmFadeInFadeOut that executes this codes:
This time, the UserForm is first made 100 percent transparent by calling Transparency Me , 1 on the UserForm_Initialize ( ) event, and when the Activate( ) event fires, it uses the Repaint method to repaint itself totally transparent and calls Fade Me , FadeInFadeOut, Slow, 3 to create a fade-in/fade-out effect with a three-second wait in between. This is the same thing most programs do to show the splash screen at startup (like Excel does).
You must make the UserForm 100 percent transparent or 100 percent opaque before applying a FadeIn or FadeOut effect, respectively.
Applying a Skin to a UserForm
Since the advent of Windows XP, a new class of window appeared in some popular applications, such as Windows Media Player, Nero Burning Room, and so on. Instead of using a rectangular window, they all use a specially designed version with different shapes, colors, and positions of the menu bar and window controls (such as minimize, restore, maximize, and close buttons, if any).
This type of form shape is usually associated with the word skin, and you can use a bunch of API procedures to apply a skin to any UserForm.
Produce a bitmap image that will shape the UserForm (reserve space to put controls, if necessary).
Surround the image with a chroma key background color that must be changed to transparent (usually white or black).
Use the same chroma key color in the UserForm Background property.
Attribute the bitmap image to the UserForm Picture property, setting PictureSizeMode to 3 – fmPictureSizeModeZoom.
Use a code procedure to remove the UserForm title bar and change every pixel associated with the chroma key color to transparent.
The first four steps are design specific and do not require much knowledge, except that the bitmap image produced must be a BMP file (avoid using JPEG files because they add no white pixel artifacts that can surround the image), be surrounded by the chroma key color as best as possible, and have the smallest possible size in terms of pixel count so that the process of changing the chroma key pixels to transparent run as fast as your computer can. To apply the fifth step, you need to use the Windows API functions.
Although a VBA UserForm does not offer a line or shape control, Microsoft Access forms offer both of them, and Visual Basic, since its first version, also offers a circle control. So, it is possible to draw on the UserForm surface.
This is possible because Windows offers what is called a device context, which is a programmable structure that can receive directly drawn color manipulation, like Paint does. The most famous device contexts are the display screen and the printer, although Windows supports many other devices, such as plotters, image acquisition devices, and so on.
Regarding the UserForm, the device context in design mode corresponds to the background area, which is the rectangular place where the layout controls produce the desired results. But when a UserForm is in running mode, the device context may be considered as the UserForm image, with everything you put on it.
To get the device context (DC) of any UserForm and manipulate the pixels, Windows offers the API Function GetDC ( ) from User32.dll, which has this syntax:
Hwnd : This is the window handle.
The GedDC( ) API function returns a Long Integer, representing the handle for the device context to the specified window.
And since the device context uses about 800 bytes of memory, once you finish manipulating it, you must release it from memory using the API’s Function ReleaseDC ( ), which has this syntax:
Hwnd : This is the window handle.
hdc: This is the handle to the device context to be released.
Once a UserForm device context handle had been obtained, you can loop through the pixels using the Windows API’s Function GetPixel ( ), which retrieves the RGB Long Integer color value of any pixel, given its x, y coordinates. It has this syntax:
hdc: This is the device context handle.
X, Y: This is a zero-based point to check in the logical coordinates of the bitmap image.
The GetPixel ( ) API returns a Long Integer indicating the pixel color value. If the pixel is outside the clipping region of the UserForm, it will return the CLR_INVALID = &HFFFFFFFF constant.
There is one warning regarding coordinate transformations between the UserForm device context pixel count: you need to multiply the UserForm internal dimensions by 4/3 (the basic image aspect ratio) to convert the UserForm InternalWidth and InternalHeight properties to the X, Y pixels count, respectively.
The next code fragment illustrates how you can iterate through all the pixels of a UserForm device context, getting one at a time:
That was easy, huh? There is no such complexity to step through each UserForm pixel and get its color.
Changing the UserForm Shape Using Windows Regions
Now that you can access the UserForm image and iterate through each of its pixels, you need to know that you will not change its colors to change the UserForm shape. Instead, you will analyze the UserForm picture, row by row, finding regions composed of continuous pixels where the colors are different than the one used as the chroma key (usually the white color).
These colored regions are then used to compose a new shape (representing the skin image), row by row, until all UserForm pixels are processed. When this new shape is created, it is applied as the new UserForm shape, effectively creating the “skin” effect.
These regions are graphic device interface (GDI) objects that describe an area in a device context object, having its own handle be manipulated by other Windows API functions. To create these pixel colored regions, you need to use the Windows API’s Function CreateRectRgn ( ) from gdi32.dll, which has this syntax:
Left , Top: These are coordinates that describe the upper-left corner.
Right , Bottom: These are coordinates that describe the lower-right corner. The Right and Bottom coordinates must be at least 1 pixel greater than the Left and Top coordinates, respectively, to create a rectangular region. They are not considered part of the region.
The CreateRectRgn ( ) API returns a long integer associated to the graphic device interface’s object handle. The next call to CreateRectRgn ( ) creates an empty region, with no pixel count, and stores its handle on lngHwndRgn.
Since the Right and Bottom coordinates do not belong to the region, the next call creates a 1-pixel region associated to the pixel position (0,0).
Whenever you create a device context region with the GetDC ( ) API, you must be sure to delete it to free the memory used by using Function DeleteObject ( ) from gdi32.dll, which has this syntax:
hObject: This is the handle to the graphic device interface object to be deleted.
To create a complex region, composed of different colored rectangular regions, you must use the Windows API’s Function CombinRgn( ) from gdi32.dll, which has this syntax:
hDestRgn: This is the handle to the new combined region with dimensions defined by combining two other regions (this new region must exist before CombineRgn ( ) is called).
hSrcRgn1: This is the handle to the first of two regions to be combined.
hSrcRgn2: This is the handle to the second of two regions to be combined.
- nCombineMode: This is the mode indicating how the two regions will be combined.
RGN_AND: This creates the intersection of the two combined regions.
RGN_COPY: This creates a copy of the region identified by hSrcRgn1.
RGN_DIFF: This combines the parts of hSrcRgn1 that are not part of hSrcRgn2.
RGN_OR: This creates the union of two combined regions.
RGN_XOR: This creates the union of two combined regions except for any overlapping areas.
NULLREGION: This region is empty.
SIMPLEREGION: This region is a single rectangle.
COMPLEXREGION: This region is more than a single rectangle.
ERROR: No region is created.
The CombineRgn ( ) API can be used to successively combine an empty region with different regions of a device context, using the RGN_XOR constant on the nCmbineMode argument and producing a complex, nonrectangular region (an irregular bitmap). Once you have such an irregular region, you can use the Function SetWindowRgn ( ) API from user32.dll to apply it as the new UserForm appearance, which has this syntax:
Hwnd : This is the window handle.
hRgn: This is the region handle. It sets the window region of the window to this region. If hRgn is NULL, the function sets the window region to NULL.
bRedraw: This uses True to specify that the system must redraw the window after setting the window region.
The SetWindowRgn ( ) API returns a nonzero value if it succeeds; it otherwise returns zero.
Supposing that you want to use the Figure 10-8 bitmap as a UserForm skin using the white color as the chroma key (the transparent color), you must apply it to the UserForm.Picture property, get the UserForm device context, and use two nested loops to run through each image row, pixel-by-pixel, until a nonwhite pixel is found, creating successive one-row rectangular regions of every other nonwhite colored pixel on the same row.
Since the bitmap row 0 is entirely white, you can create a one-row bitmap region from row 1, pixels 4 and 5, using CreateRectRgn ( ) in this way:
(left = 4, top = 1) indicates the rectangle’s (left, top) initial pixel coordinates, and Right = 6, Bottom = 2 indicates the (right, bottom) rectangle coordinates that do not belong to the first rectangular region.
You can create another one-row bitmap from row 2, pixels 3 to 6, using CreateRectRgn ( ) in this way:
( Left = 3, Top = 2) indicates the rectangle’s (left, top) initial pixel, and Right = 7, Bottom = 3 indicates the (right, bottom) rectangle coordinates that do not belong to this second rectangular region.
You can use the CombineRgn ( ) API to combine these two different one-row rectangular regions in this way:
This last code fragment begins defining lngSkinRgn as an empty rectangle and then uses the CombineRgn ( ) API to combine it with the first rectangular region comprising all of row 1’s colored pixels. After deleting the lngTempRgn region, it combines lngSkinRgn again with the second rectangular region comprising all of row 2’s colored pixels.
By continue to combine all rectangular regions of rows 3 to 8, lngSkinRgn will end up with a handle to the diamond shape bitmap that has an irregular border, using just its nonwhite pixels, which can be applied as the new UserForm window shape using the SetWindowRgn ( ) API.
The Skin( ) Procedure
The basUserFormAPI module declares the GetDC ( ), ReleaseDC ( ), GetPixel ( ), CreateRectRgn ( ), CombineRgn ( ), SetWindowRgn ( ), and DeleteObject ( ) aliased APIs.
The code also has the TransparentColor enumerator and the fully commented Public Sub Skin ( ), which uses these APIs to apply a UserForm skin :
The Skin ( ) procedure is in fact quite small, but it’s inflated a bit because of its long section declaration and its many comments. It receives two arguments: frm as Object (the UserForm reference) and TransparentColor as TransparentColor = White (the chroma key that will be discarded from the UserForm picture; the default is white).
As the first comment states, the code expects to receive a UserForm reference that has no title bar and is 100 percent transparent, so you need to call Sub Appearance ( ) and Sub Transparency ( ) before calling Sub Skin ( ) to apply a skin effect to the UserForm.
After declaring the many variables, it gets a handle to the UserForm and its device context, using the Function Hwnd ( ) and the fm_ GetDC ( ) aliased API, and effectively takes a picture of the UserForm appearance (the UserForm device context is independent of its alpha channel; in other words, it doesn’t care about the UserForm transparency).
It then sets the UserForm BackColor = TransparentColor argument to guarantee that it has the same chroma key color and sets UserForm BorderStyle = fmBorderStyleNone to remove the border.
To determine how many pixel columns and rows the UserForm has, it uses the UserForm InternalWidth and InternalHeight properties multiplied by the AspectRatio = 4/3 constant (which gives a pretty good pixel count approximation to most screen resolutions).
And before looping through the UserForm device context pixels, it declares an empty rectangular region using the fm_ CreateRectRgn ( ) aliased API, attributing the region handle to the lngSkinRgn variable.
It then sets two nested For...Next loops. The outer loop runs lngY through all the device context pixels rows, while the inner one runs lngX through all its pixels columns.
For each device context row, it takes each pixel column using the fm_ GetPixel ( ) aliased API and stores its color value on the lngPixel variable.
Then the code verifies whether the code has already begun to define a nonchroma key region (a bitmap region that has no transparent color), testing the bolNewRgn Boolean variable value. While bolNewRgn = False, the codes steps to the Else clause and verifies whether the lngPixel color is different from the selected TransparentColor argument (the chroma key). Note that the code tests it against the Color_Invalid constant, which may appear if the selected pixel is outside the device context dimensions because of the approximate pixel count provided by the AspectRatio approximation.
Now bolNewRgn = True, and the loop will find the next pixel of the chroma color on the same row (for the diamond shape of Figure 10-8, row 1, this pixel will be at column 6).
That is why it is so important that the bitmap be surrounded by the transparent color. When such a pixel is found, it is time to use the aliased API’s frm_CreateRctRgn( ) to create a new region that goes from (lngLeft, lngY) to (lngX+1, lngY+1), apply fm_ CombineRgn ( ) to combine it with the empty lngSkinRgn region, and use fm_ DeleteObject ( ) delete the new region. This will free its memory resources.
After the new region was combined with lngSkinRgn, it makes bolNewRgn = False so the code can chase another colored region on the same or next bitmap rows. Note that whenever the loop reaches the end of a row (by processing all its lngX pixels columns), it makes lngLeft = 0 and bolNewRgn = False to guarantee that just one-row rectangular pixel regions will be combined.
When all row pixels are processed, lngSkinRgn will have an irregular region associated to the picture whose skin you want to apply to the UserForm, so it calls the fm_ SetWindowRgn ( ) aliased API to apply it as the new UserForm window and uses the fm_ DeleteObject ( ) aliased API to delete this device context and free the memory it uses.
The frmSkin UserForm
The UserForm_APIs.xlsm macro-enabled workbook has the frmSkin ControlButton, which loads the frmSkin UserForm and applies its Picture property bitmap as a skin. If you inspect the UserForm in design mode, you will note that it uses the Caju.bmp bitmap (also found in the Chapter10.zip file) on the Picture property and has BackColor = &H00FFFFFF& (the hexadecimal value of the white background) to guarantee that the bitmap be totally surrounded by white, which is the chroma key (Figure 10-9).
Caju is a northeast Brazilian exquisite fruit, also famous for its external nut, which is appreciated worldwide. The Caju.bmp file is 172 KB using 200x293 24-bit color pixels.
The VBA Properties window returns color values in the hexadecimal color string because this color mode allows you to define by intuition the RGB red, green, and blue color components of the selected color. From right to left of the &H00FFFFFF& color associated to the white color, the first two FF characters are associated to the Blue component (255 in decimal), the most intense blue; the next two FF characters are associated to the Green component, or the most intense green; and the leftmost two FF characters are associated to the Red component, or the most intense red. The VBA Function RGB(255, 255, 255) produces white, the most intense visual color. The leftmost 00 characters are the alpha channel value—the color transparency, where 00 means totally opaque.
Note that frmSkin also has the small cmdExit CommandButton with property Caption = X to allow it to be easily closed.
Before applying the frmSkin background image as a skin, it first needs to become 100 percent transparent and with no title bar, which is done in the UserForm_Initialize ( ) event, calling Sub Appearance ( ) and Transparency ( ), which were already analyzed in this chapter.
Once the UserForm appearance is totally rendered with 100 percent transparency, the skin is applied on the UserForm_Activate( ) event.
Note that the code first calls the UserForm.Repaint method to force it to repaint and then calls the Skin Me procedure, accepting the default TransparentColor = White = 16777215 (the white color in decimal). To force the UserForm to appear, it calls again Transparency Me , 0, making it 100 percent opaque.
The effect is applied quite fast because the Caju.bmp bitmap is a small .bmp file. It could even be smaller as a JPG file, but if you use this resource, it is probably to show some strange artifacts surrounding the UserForm, such as almost white pixels, that are really not 100 percent white.
You can drag the frmSkin over the Sheet1 worksheet because it stores the UserForm position on the msngX and msngY module-level variables in the UserForm_MouseDown( ) event (which fires when you click any mouse button), just for the left mouse button (Button = 1).
The code repositions frmSkin when you drag the mouse by adding (X–msngX) to the current Letf property and (Y-sngY) to the current Top property, where X, Y are the new move coordinates.
When you close the frmSkin UserForm by either clicking cmdExit or double-clicking it, it disappears smoothly from the Sheet1 interface because of a call to Fade Me , FadeOut, Fast on the QueryClose ( ) event.
You can change the frmSkin Picture property to any other bitmap to verify how it behaves as a skin. Inside the Chapter10.zip file you will also find the Apple.bmp and Donut.bmp bitmaps that can be used as good skin examples.
The USDA Food Composer_frmAbout.xlsm Application
Note that even the donut hole is transparent!
To make it appear, it uses the same technique employed by frmSkin , but to disappear after a three-second wait, it sets the mTimer object variable on the UserForm_Initialize ( ) event with Interval = 3000 (3000 ms = 3 seconds).
After frmAbout is dismissed, you can click the About ControlButton on the My Recipes worksheet to force it to appear again over the application interface, but this time it does not automatically disappear. It shows the imgCloseButton Image control to be unloaded and can also be dragged onto the worksheet screen.
This is done with Sub ShowfrmAbout( ), from basControlButtons, which uses this code:
This code loads frmAbout by referencing it on a With frmAbout ...End With structure (cascade-firing the UserForm_Initialize ( ) event) and calling the CancelUnload( ) method. Then the code shows frmAbout with the UserForm.Show method (cascade-firing the UserForm_Activate( ) event). This is the code executed by the frmAbout .CancelUnload( ) method:
Quite simple, huh? It turns imgCloseButton visible and avoids the mTimer object to be activated by setting mboCancelUnload = True (the imgCloseButton_Click( ) event unloads the UserForm).
This chapter gave you some guidance about how to use the Windows API’s procedures inside VBA code to produce effects that are not allowed from the standard Visual Basic language.
It briefly discussed how to declare a DLL procedure, how and why you should care about aliasing procedures, why they use so many constant declarations, and how you set bits using the OR, AND, and NOT operators to produce the desired bit effect.
You were also introduced to the window Handle concept and how to obtain the UserForm handle to use the Windows APIs.
All the code inside this chapter was inspired from many Internet web sites that currently offer code without explaining what really happens when the code is executed. I hope that after reading the chapter you feel more comfortable searching for and copying and pasting other VBA DLLs procedures to use in your applications.
Daniel Appleman books such as Visual Basic Programmer’s Guide to the Win32 API, Win32 API Puzzle Book and Tutorial for Visual Basic Programmers, and Developing ActiveX Components With Visual Basic: A Guide to the Perplexed
Ken Gets books such as VBA Developer’s Handbook and Microsoft Access 2000 Developer’s Handbook
That Microsoft Windows bases its inner workings on a set of DLL files, each one with its own set of procedures, comprising what is called the API
How to declare a DLL procedure and its constant values
That you can avoid code conflict by giving an alias to your DLL procedure declarations
That most DLL procedures have a set of constant values that must also be declared
That these constant values can appear either in decimal or hexadecimal notation
The meaning of handle on the Microsoft Windows operating system
How to get the handle for the Application , UserForm, and Class module instance objects
That some API procedures need the address of a callback procedure, which is a VBA procedure that must be declared with specific arguments that will be called back by the API
How to create a Timer object using some Windows APIs and a Class module
How to change the UserForm window appearance using Windows APIs
How to create transparency and fade effects on a UserForm
How to implement a skin effect based on a bitmap image
How to produce a splash screen to be presented to the user when your Excel application starts
In the next chapter, you will learn how to create a personalized Microsoft Excel ribbon using third-party applications developed using VBA to enhance the professional appeal of your worksheet applications.