Daily Dose of Excel

keepITcool has developed a method to position a userform over a specific range. Finding a range’s screen coordinates is tricky. I usually resort to “close enough”.

He says:

First I found a bug. Excel 97 thru 2007.
The VisibleRange of Pane 2 and 3 (in a 4 pane window) are inconsistent.
depending on the sequence in which the vertical and horizontal split bars are set it will sometimes
return the range of the upper-right pane, sometimes the lower-left… and in both panes it may return activepane.index = 2
I found the cause and a workaround.

I soon got desperate using PointsToScreenPixels with non-standard zoom. Then I found that using XLM macro’s is the only reliable way to consistently get the “crosshair” on the screen under a variety of splits, zooms and display options. Some fiddling to offset from that point in case of frozen panes.. et voila!

You can download RangePos Beta1.zip.

Update: Download RangePos Beta3.zip

Sam Radakovitz said:

And that’s about everything this date picker can do in a nut shell. There are some enhancements I’d like to make, not sure if I’ll get to them though:

* Update it for Excel 2007 & create nice ribbon buttons
* Support for setting time, like 2:30pm
* If a cell has a formula of =A1, and A1 is just a date, then I’d like to set A1 instead of blowing away the formula that refers to A1
* More ‘semi-smart’ detection for when to show the icon in the cell based on the data or objects around it
* Multiple visible months (previous and next to the left and right of the current month)
* Ability to limit the days available to choose from based on a cell reference
* Make a managed code version of it, right now its VBA and User Forms
* International support

I recently had to add a time control to a form. I used the DTPicker, which allows the user to increment the hours and minutes separately using spin buttons. Similar to the windows Date and Time Properties box.

I long for a better way. I want a clock with one hand that I can move around. For 2:30, I'd put it between the 2 and the 3. Whipping the hand around once would switch between AM and PM. There's no built in control that comes close to that, I think, so I thought I'd try something else.

The scrollbar is set up with these properties:
Small scroll = 1
Large scroll = 15
Min = 1
Max = 1440
Value = 720 (always start at noon)

The code behind the form is:

The scroll bar suffers from a problem that would also plague my one-handed clock. Granularity. I can move that scroll bar and watch the time zip from 4:00 AM to about 10:00 PM with ease. But I can't stop at exactly 10:00 PM with anything close to ease. I need a control that gets more precise as I slow down the movement, like my mouse does (yes, I do have one). Now, I get close and then click on the ends to small-scroll one minute at a time.

Since Professional Excel Development was published, I've been using the error handling method described in chapter 12. It's very comprehensive and easy to implement. It's no guarantee that my code is right, of course, but it does guarantee that the end user won't be plopped into the VBE when an error occurs.

I also MZ-Tools while I'm coding. One of my favorite features is being able to add module headers, procedure headers, and error handling code easily. Below is what I have on the Error Handling tab under MZ-Tools > Options:

If you haven't read the book, all non-trivial, non-entry point procedures are made into Boolean functions that return TRUE if no error occurs. When I add the above code into a an entry point procedure, I have to delete any line with the variable bReturn in it because that's only applicable for functions.

The part that I'm not totally clear on is for functions that return something other than Boolean. For instance, I have a function that reads a Jet table, fills an array, and returns a Variant array to be used to populate a ListBox. I can't make this procedure a Boolean function because I need it to return an array. One option is to treat this function as an entry point, which has the effect of not pushing the error back into the calling procedure. This is the way I've been doing it. A second option is to make the function return a Boolean, but pass the "return" variable ByRef. This would change the way I call the procedure, but would still achieve the result I need.

Option 1:

Option 2:

I'm having second thoughts about choosing Option 1. How do you do it?

See Progress Bars for more information.

I have some applications, like QB SDK that not only take a long time, they also don't progress at a uniform rate. If I use a standard progress bar, it will whip through the first 50%, sit there for five minutes, then whip through the last half. Since the Quickbooks SDK doesn't have any callbacks, I can't report on the progress of that five minutes.

Progress bars should achieve a few goals: They should reassure the user that something is happening and that his computer hasn't stopped working; they should give the user some indication of how much time is left until the operation is complete; and optionally they should tell the user what's currently happening. When a progress bar sits on one task for far longer than other tasks, it doesn't really meet any of these goals. A stalled progress bar could easily be interpreted as a stalled computer and there's no indication of when it will start moving again, much less when it will complete.

I couldn't achieve either of the first two goals with VBA, but I was at least able to tell the user what the heck was taking so long. Instead of an animated progress bar, I used a listbox on a userform to announce which step was currently being performed.

To make this type of progress indicator, you only need a userform with a listbox on it. The userform's ShowModal property must be set to False. This allows code to continue running while the userform is displayed. Then, at certain intervals, you can use the AddItem method of the listbox to display the current task. Your code might look like this:

I use ellipses at the end of captions to indicate that there's more to come. Another method is to load the listbox with all of the tasks and check them off as they complete.

For this method, set the listbox's ListStyle property to 1 - fmListStyleOption and its MultiSelect property to 1 - fmMultiSelectMulti.

As many of the frequenters of this great place will know, Charles Williams and I created The Excel Name Manager. The # 1 (FREE) tool on the web to work with defined names in Excel.

Well, we do occasionally get feedback on this tool (which is always appreciated!) and in all these years, we got two reports stating the tool didn't work at all, showing an "Invalid Picure" error which effectively prevented the tool from working.

So this appears to be a rather obscure error.

Colin Delane was kind enough to help me troubleshoot this problem (as he was the one experiencing it).

After some detective work, we nailed down the problem to one particular commandbutton on the NM's main form. This button has a picture (as the others on the top have too):

Screenshot of Name Manager buttons. I circled the offending button. Note that this button only shows in the FastExcel version of NM.

The odd thing is, that it appeared to be the format of the picture that was causing havoc.

Both Meta and BMP are OK (no error on user's system), but when we loaded a GIF picture format, the error occured reproducably.

Has anyone seen this before?

Regards,

Jan Karel Pieterse
www.jkp-ads.com

This one comes from my old buddy Harald, who will be sorely missed at this year's summit.

This code allows you to reorder items in a Listbox by dragging and dropping them. Similar code in another Listbox could be used to drag items between controls, but that is not shown here. The only thing I'd like to see is some visual indicator of where I'm dropping the item. In the time I spent with this, I just wasn't able to come up with anything.