Everyone! We've moved to WordPress.

Loading...

Getting a Handle on hWnd

4
Sometimes, you'll want to programmatically take advantage of Windows outside the capabilities readily available through Visual Basic for Applications (VBA). For example, both eliminating the "Close"-button or creating a more opaque background on a window require the use of the Windows API.

Doing this in Visual Basic isn't too hard, but there are some distinct differences between what's readily available to programmers in Microsoft Office's Visual Basic (VBA) compared to its virtual twin, Visual Basic 6.0, and its smarter, object-oriented younger brother VB.net. Specifically, UserForms in VBA do not contain methods to find their Windows handle, (aka, their hWnd).

And most window modifying APIs require the target window's handle. Luckily, the Windows API also contains a function to find a given window's hWnd, called FindWindow():


Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long


So, to find your window's handle, you'll do something like this (assuming you're coding within the UserForm).


hWnd = FindWindow("ThunderDFrame", me.caption)


Here, FindWindow() takes two arguments. First, the class name of the window for which you're searching; and second, the window's caption.

ThunderDFrame, huh?

I know, right? The UserForms in Excel are actually of the Windows class ThunderDFrame, which is the class for all UserFroms in Microsoft Office applications after 2002 (it was "ThunderXFrame" before that). I've looked online for a good explanation for the name but haven't really found anything. Either way you'll need to pass that into the first argument of FindWindow whenever you need a UserForm's handle.


That's all for now!

-

ps I'm working on a better stylesheet for when I display code.

A High Ranking Function

0
Excel really doesn't have an explicit "between function" to allow users, given a set of ranges, to find in what range a selected number falls. The obvious and messy workaround is to use a bunch of nested IFs, which is tedious and error-prone. That's where the RANK function comes in.

Here's a breakdown.
RANK(number,ref,[order])
number - The number you want to find
ref - The reference to spreadsheet range
order - optional; excel defaults to descending order
The RANK function returns the location of a specified number in a given range. So let's say you have a given set, 1,2,3,4,5. Obviously, testing for the number three will return a location of 3. For our range test, we combine a number outside the set and see where it falls.

Check out the image below to see it in action:


Excel returns a rank of 4, since 3.2 will appear in the fourth location of the ranked set, 1,2,3,3.2,4,5.
At this point, there are two important items to point out.
  • How excel sorts the data for ranking is important. Above we use an ascending order (we pass a 1 to the last argument) and below we'll use a descending order (we'll pass a 0 to the last argument). You'll have to decide which best fits your analysis.
  • Because you combine two ranges into one, you must use parenthesis. If you miss this step, the RANK function will interpret B2 as the next parameter and return an error.
Here's a Weather Application



This example tests for a temperature's range and then displays how the day will feel corresponding to that range.

-

You could also use this trick when you must evaluate a value on a complex function. For example, you might have a graph with several curves that represents some utility for a given value, x. Thus, if you know the bounds of your curves, you can test in what range your x falls and apply its corresponding curve-function.

Finally, you might have noticed that this trick restricts you to inequalities with an inclusive lower bound and exclusive upper bound. There's ways to fix this and I plan to talk more about them in future posts.