Daily Dose of Excel

I'm trying to run this code:

and gsSTAN is a global constant set to 'Standards.xls'. I forgot to create the folder gsTANDFLDR and, of course, the file is not in there either. I get this error message:

The third bullet point is what strikes me as strange. Why is the file name in there? You may have noticed that it's not a typical message box. The error is run through a error handler, but the message is unchanged.

One of the most popular downloads at my Web site is the J-Walk Enhanced DataForm v2. It's an add-in that provides an alternative to Excel's built-in Data Form (accessed with Data - Form). Personally, I think the most efficient way to enter data into a worksheet is to do it directly. But, for some reason, many seem to prefer using a form.

I'm going to make an Excel 2007 version of this add-in, and I thought I might add a few new features while I'm at it. Besides a new UI, I'm going to try to incorporate Andy Pope's clever Resizable UserForm technique.

Any other suggestions?

By the way, Excel's built-in data entry form is not present in the Excel 2007 UI. To use the feature, you must customize your Quick Access Toolbar and add theForm command from the Commands Not in the Ribbon group.

Frequent commenter Alex always has some interesting ideas, and this one didn't disappoint. If you copy a range, then hold down the Shift key, you will see a Paste Picture Link item under the Edit menu. With this, you can paste a picture onto your worksheet that is linked back to the copied range.

One interesting benefit to this feature is that you can magnify a range.

You can even drag a picture into the range and magnify it.

Then Alex made a workbook where he can drag a shape around, press a button, and magnify the area to which he dragged it.

He has to press a button because the Shape object doesn't have any decent events to speak of. After reviewing Alex's workbook, I set out to crash Excel. If a shape, such as a picture, can be shown simply by dragging it into a range, then any shape could be shown in that manner. Hey, the linking object is a shape. I figured if I drag the linking object into the range that is linked, then Excel will spiral into an eternal shape-within-a-shape calculation and crash. No such luck.

Excel survived. Now what I'd like to do is make a magnifying glass that can be drug around and magnify the range directly underneath it. Here are the problems I see:

Picture objects (they're deprecated, so you won't see them in the object browser) have a Formula property so they can be linked to a range, but they can't be made round like a proper magnifying glass.
Picture objects don't support automation events. That means I can't trap a move event to recajigger the range to which it is linked.
Even if I could set the range to be below the shape, I can't stop the shape from appearing in its own representation of the linked range.

To answer your next question: No, I don't have any use for this. I just thought it would be neat and I'm sure Andy Pope will use some combination of a scatter chart and the BESSELJ function to do it that will blow me away.

I've received a couple of trig questions lately. I liked trig in high school and college, but I can't say I put it to use much. There's only one thing that you need to know about Excel and trigonometric functions: Everything is in radians, not degrees. If you do anything trig-related in Excel, you have to convert to degrees (unless you want radians, of course).

Here's one problem:

In this example, Peter knows a fixed point on a plot of land. He also knows the coordinates were the corner of a building will be when it's built. He wants to figure the angle and distance from the known point to the corner of the building so he can point his theodolite at it.

I had to look up theodolite, and it turns out I've been calling it a transit all these years.

Finding the distance is easy, thanks to old Pythagoras and his right triangle formula, c² = a² + b². The formula in B4 is: =SQRT((B1^2)+(B2^2)). That one I didn't have to look up. The rest of of the trig functions weren't so quick to return to me.

Next I needed the angle. Cell B5 is =DEGREES(ATAN(B2/B1)). Note that I didn't forget to convert to degrees, since the ATAN function would return radians. To refresh my memory, I used this Trigonometric Formula page.

Finally, I turned that fractional angle into minutes and seconds, thusly:

B6: =TRUNC(B5)
B7: =TRUNC((B5-B6)*60)
B8: =ROUND((B5-B6-(B7/60))*60,0)

Now Peter can point his theodolite (or whatever you do with those things) at 75° 57' 1'' and 6,185mm away will be the start of the new building.

I need help. I have this:

I can tell my how many unique fruits are in column A with this array formula

E4: =SUM(1/COUNTIF(A2:A13,A2:A13))

And I can tell how many rows have both Apple in column A and 1 in column B with this array formula

E5: =SUM((A2:A13="Apple")*(B2:B13=1))

But I can't seem to figure out the formula to tell me the count of unique fruits that have a 1 in column B. The answer is two, apple and peach. What's the formula that gets me there?

The folks at Viziware have released an update of Navipane, previously discussed on DDoE here. Via email, they said

I would just like to inform you that we have an updated version of Navipane available, which fixes a lot of the problems that you mentioned in your blog post, and more. Additionally features include a much more powerful Sheets Organizer, History Complete, Integrated Search, among others.

Here's a quote from their website:

45-Day Money Back Guarantee. We don't want your money if you're not happy.

I like that attitude. I also note they have a 30-Day Trial period, which is great. If you're interested in purchasing Navipane, they've generously offered a discount to Daily Dose readers. Use the coupon code 40DISCOUNT to get 40% off all of their products.

Note: I don't work for Viziware. I don't get anything if you buy any of their products. They gave me a license to Navipane earlier this year so that I could write a review about it.