Daily Dose of Excel

Hi All,

As many of you know I give away a number of tools for Excel through my site www.jkp-ads.com. Today I have updated the two most popular downloads:

Name Manager (which I created together with Charles Williams, www.decisionmodels.com):

Most important change: the unused names filter now includes objects in its search, as well as VBA code. It makes the filter much slower, but way more useful in my opinion. Also, I have added the Greek character set so Name Manager doesn’t (wrongfully) think range names with Greek characters are corrupt.

So far, Name Manager has been downloaded about 50,000 times since I posted it on my site.

Flexfind

I have updated the user interface of Flexfind so (in my opinion) it is easier to use. Also, I have mimicked the find all behaviour of Excel: if you select multiple items in the found items list, Flexfind will create a (multiple) selection of areas of the found cells.

Flexfind is less popular than Name Manager, the download count is at about 23,000.

Enjoy!

Of course I am open to any comments, suggestions and -most importantly- lots of praise

Regards,
Jan Karel Pieterse
www.jkp-ads.com

Hi all,

Let me start this post with a shameless plug: Charles Williams and I developed Name Manager. From my website alone this tool is good for about 60 downloads a day.

Just a couple of weeks ago, all of a sudden Charles and I started receiving complaints about compile errors, which neither of us could reproduce. Because the version we had available back then used the treeview control from the Windows common controls library, we blamed that control. And indeed, removing the control from the form that housed it fixed the problem for some of our users. But not for all.

Since this kind of errors is extremely hard to troubleshoot, I thought it might be useful to share our experiences.

Luckily we found a user who was willing to help us trouble-shoot the matter. We asked him to do all sorts of things: try on a different client, try logging on as administrator, removing accounts from client, you name it.
After exchanging a host of emails and screenshots and trying all sorts of variations we discovered the culprit: problematic .EXD files in one of the system folders of the client computer (these are just an example, your system may show others too or even none at all):

These files are typically stored in this location:

C:\Documents and Settings\[UserName]\Application Data\Microsoft\Forms\

So far, removing these files from that folder has resolved the issue for the people that experienced trouble with Name Manager.

So, if you distribute an addin to other users and you get a complaint about compile errors in your work: start off by asking the user to weed out the Forms folder I showed above. If it doesn’t help, it certainly wont hurt!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Hi,

A while ago I requested beta testers for a new utility, now called “ExcelRefTool”.

Thanks to my beta testers, the tool is now mature enough to be exposed to the general public.

Have a look here, download the demo if you like and give it a spin.

And thanks again, to everyone who took the trouble of beta testing this tool for me!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

I use 2 computers at work. One has Excel 2003, the other has Excel 2007. I’ve been using the one with 2003 longer and had a few macros stored in my Personal workbook. I was in the process of copying and pasting them into the other computer after sending them there by email. (Yeah, I know I can install both Excel versions in the same computer but this is how things turned out and I find it handy for testing)

Anyway, I wondered how I was going to call them. I had the macros in the 2003 computer assigned to toolbar buttons with icons but how about 2007? I could use the QAT but realized the face ids were going to be different. Not such a big problem, but I didn’t feel like going through the drama of selecting a whole lot of new ones, not to mention updating personal workbooks for both computers every time I added any new code. So I came up with the below right click menu addin that I use for both computers on a shared drive.

Just paste or write any macro into the addin and update the menu by pushing “Make Right Click Menu”, either in the VBE or right click menu itself to add and save. The module names in the addin act as sub menu names and the macro names get added as menu items. Numbers get added to act as keyboard shortcuts.

Also, there is some code to add spaces to module and macro names so that Private Sub DeleteAllFilesInFolder() in module MiscellaneousMacros would show in the menu as DIY Context Menu - Miscellaneous Macros - 1. Delete All Files In Folder

It’s a dynamic menu and a pretty simple one to use. Here is the download link if you want to try it. Some example macros are also included. I think it should work with Excel versions 97 - 2007.

I've heard several people complain about the fact that the chart pattern fills are not available in Excel 2007. This feature can be useful if you print charts on a non-color printer. Although charts created with previous versions of Excel continue to display the pattern fills, there is no way to apply patterns fills using the Excel 2007 UI.

Microsoft's Eric Patterson addressed this problem by creating an add-in. You can download it here: Chart Pattern Fills.

This add-in creates a new group (Patterns) on the Chart Tools / Format tab. The group has one control that, when clicked, shows the available patterns to apply to the selected chart element.

I have a few add-ins and files for download on my site, one of which I have recently been working quite a bit.

It’s my New Book Navigator addin and like the name says it helps you navigate around a workbook, as well as select, deselect and highlight cells.

The “New” part is to distinguish it from the previous version. Anyway, it’s evolved into a pretty good add-in over time (my opinion) and it does a lot of things you might find come in handy.

One of those things is what I call Quick Filter. It’s not a filter in the ordinary sense but unlike Excel’s inbuilt filters, it allows you to find and go to cells in both rows or columns, you can even use it to go to hidden cells without the need to unhide them.

Another thing is that you can see what range is selected at any time and copy that range if desired to the clipboard. The range details can also changed to show as Absolute or Relative, A1 or R1C1 references. The default setting is to update the toolbar as you select ranges automatically but you set this to Manual in the Options settings if you want to avoid the screen from flickering when running code. Details of this and all of the other features are described in both the read me files and Help files.

My download page is here. I hope you find New Book Navigator and my other stuff useful.