Category Archives: Windows API

Happy Holidays 2015

OK, so I know a few days ago I said I had written my last blog posted before the end of the holiday. But then I’ve released holiday cards in both 2013 and 2014, so this year should be no different. So I went with a topical Star Wars theme. The text being shown is  an abridged version of the introduction chapter in Dashboards for Excel.

Recording #3

Speaking of which, if you haven’t gotten your copy yet, you should. The book currently has a 4 out-of 5 on Amazon, with the most recent reviews being the most complimentary. (And I swear, I don’t know the people writing these reviews.) Aside from some complaints about the book not being in color, the book is doing well. For what it’s worth, I inquired about color printing with my publisher long before it was printed, but most tech-trade books are not published in color and the few that are already have a strong history of sales and success. (But also: don’t stop asking! If enough people ask for a color imprint, I can make a stronger case to my publisher for the second edition!) Remember the eBook versions are in color too, if that’s your thing. And, of course, I have all the print materials at the ready, so you can always ask me directly if you have trouble understanding anything.

Download File: Happy Holidays 2015.xlsm

image

Remember to hit the download icon from within Google Drive to get the file.

If you want to change the Star-Wars-text for your own application (aren’t there like a million business uses for this?!?!), simply click the worksheet tab called “Backend” and type new text into the textbox.

Thanks to all of you for a wonderful year. Next year will be even better!

Jordan

Advertisements

Handling Excel Rollover Popups

Various people have written to me asking what they should do about popups generated by the rollover method that don’t go away on their own. Essentially, the rollover method provides for an “on mouse over” style routine but not for a “on mouse out.”

The “moat” method

One method to address this is to create a “hotspot moat” around the initial hotspot zone. In the moat, you would again use rollover technique; now, it would hide any popups initiated by the original hot spot zone.

image

The moat method has a rather significant drawback: if you move your mouse too quickly over the moat, you might not trip the hyperlink to remove the popup. When that happens, your pop-up remains no matter what.

The Selection Change Method

My friend Robert Mundigl of ClearlyAndSimply.Com in his Bruce Springsteen Discography in Excel Dashboard noticed this drawback as well. So, he employed another trick: use the Worksheet’s Selection_Change event like you would the moat. With this technique, when the user selects anything else on the spreadsheet, the popup is always hidden.

The Timer Method

The final method – the one for which I’ve received the most requests for instruction over email  – is a mechanism to hide a popup box after a few seconds. A screen cast of it appears below.

popup fade

This method uses the windows Timer API to begin fading the popup after a very small amount of time. One clear advantage to this method is that it takes care of any popups when your mouse leaves the hotspot zone. However, if you pay careful attention to the screencast, you’ll see that a popup might begin fading even if your mouse remains in the hotspot zone. (You can see this when my mouse hovers over “3”… wait for it.) If you keep your mouse still, the rollover won’t get re-tripped.

So you need to do something to “retrip” it. Here’s my hack. The numbers in the hotspots are actually a reference from in Column A. See below:

image 

So, in my rollover function, I’ve included this additional line:

   1: Sheet1.Range("a4:a6").Cells(index, 1).Value = index

 

This simple bit of code does nothing more than reassign the cells in Column A to the same values that they were. In the picture above, when your mouse is over the hotspot in C4, the code snippet instructs cell A4 to be reassigned the value of one. However, since cell C4 is also dependent on the value in A4, Excel treats the assignment as a change in its dependency chain. This causes the Hyperlink function to update – and this update retriggers the rollover action. Problem solved.

image

You can download the timer-based popup here:

Textbox fade example2.xlsm

Feel free to use it in your projects without attribution. But, if you really like it, I’d love to hear from you.

Turn an Excel Spreadsheet into a Bitmap Image

So I’ve created a spreadsheet program that will import a selected bitmap and use Excel as a canvas to display it.  Each cell is a pixel.  Here’s a picture of president Obama in 8-bit glory:

And here’s my current March Madness bracket in stunning 24-bit:

You can download the file below, and instructions are included on the Instructions tab.  Don’t worry, it’s not hard to use.  If you can use MSPAINT, you’ll be making your very own Excel art in no time!

For now, the code is locked, but I might make it available later after I’m sure it’s error free. Speaking of which, there are a few problems you may (but hopefully won’t) run into:

1.  The picture shows up discolored and slanted.  While I’m fairly certain that I’ve fixed this issue, I admit that I am by no means perfect.  When bitmaps are saved, they are “padded” in the file to make their height evenly divisible by four.  The program should compensate for this padding, but if it doesn’t — again, I’m fairly certain this issue is resolved — just resize the bitmap it to the nearest number evenly divisible by four.  (That number is at most three digits away.)

2.  Too many Different Cell Formats error.  Sometimes you’ll get this annoying popup.  If that happens, try zooming in on the canvas screen first and then reuploading your file.  If that doesn’t work, either crop the original image or resize to a smaller one.  Or, if you are uploading a 24-bit bitmap, consider resaving as an 8-bit and then reuploading.

If you find other issues, please, let me know.  It would be great help.

Also, this is best used with Excel 2007 and above.  It will work in 2003, but remember that 2003 has a column size limitation, which may cause in an error for files that are too large.  If you are concerned, smaller is better: resize or choose to save in the 8-bit format.  When you are done, remember to save as in the Excel 2007 file type (check to see if you are in compatibility mode) so that you get all the columns required to display your most productive spreadsheet, ever.

I’m releasing this now because I’ve been sitting on it for way, way too long.  I was hoping I’d come up with some real, professional use for it, but I haven’t thought of anything yet.  For a moment, I was trying to apply a Hough Transform to the bitmaps to see if I could take images and turn them into cool free form shape, but this proved too taxing for Excel.  I also made an attempt at Eigenfaces which ended ridiculously.  So if anyone out there has some good ideas, please email me and we’ll work together.

Lastly, if you like what you see and choose to host a spreadsheet made with this program (or screen capture of one) on your blog or website, I’d be so grateful if you mentioned this blog!  Have fun.

File
Bitmap to Excel.xlsm

Getting a Handle on hWnd

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.