Announcing: Name Manager 4.0 Beta

Hi everyone!

Finally I got round to doing what has been requested a lot of times for the Name Manager: being able to rename a name.

Name Manager now includes a rename option, which will enable you to change the name of a name and have all objects that use that name update too. (Well, that is, that is the intention)!

It looks like this:

This new feature has yet to be tested thoroughly though, so that is why this version is still in Beta stage.
Test drive version 4 and report any problems to me.

The feature is thoroughly tested and works as expected. Download Name Manager here.

Hint: to rename a name, double click on the name in the list and change it in the subsequent inputbox.

Regards,

Jan Karel Pieterse
JKP ApplicationDevelopment Services

Error Writing Long Strings

Why does this procedure run without error in 2000, but errors at i = 912 in 2003. Alex and I want to know.

Sub Longstring()
 
    Dim str1 As String
    Dim Ax As Variant
    Dim rngA As Range
    Dim i As Long
   
    Set rngA = ActiveSheet.Range(“A1:B1”)
   
    rngA.ClearContents
    Ax = rngA
    For i = 1 To 1016
        str1 = str1 & “a”
        Ax(1, 1) = str1
        rngA.Value = Ax
    Next i
       
End Sub

NewsGator

I’m trying out NewsGator, which I hear has been recently updated, to read my RSS feeds. I’m about ready to wash my hands of it and uninstall it, but I thought see what other people think of it.

You only have to look at the About page to see that I’m quite fond of free software. It was a major leap for me to even consider paying for an application to replace the free and quite suitable Bloglines that I’ve been using for years.

One thing I’ve never liked about Bloglines is the all-or-nothing style of reading posts. I’ll be honest, I’m not always diligent about keeping up with my blog reading. Sometimes the blogs end up with a hundred or so unread posts. There’s no suitable way for me to read, say, five of those posts and leave the rest unread for another time. There is a “Keep New” checkbox, but every click is a browser refresh and I just don’t like it.

NewsGator puts the RSS posts into Outlook and I can ignore them like do all my email. Also, they have this service that syncs what I read in Outlook with what I can access from NewsGator Online. One of the reasons I’ve never considered a desktop RSS reader is because I want to be able to read from any computer. I thought $20 per year was pretty reasonable for that service and you get a year free when you purchase the software ($30, I think).

So why uninstall it? I have three major problems and a few minor ones. First, NewsGator Online takes about five times longer to aggregate feeds in Firefox than it does in IE. I’m not switching to IE just for an RSS reader. When I’m reading online, I can’t see any images in the posts. In Firefox, I get nothing. In IE, I see my old friend the red X. Finally, some posts from j-walkblog.com simply did not show up in Outlook. That’s kind of an important one, I think.

There are some minor annoyances too, but many of them are Outlook annoyances. One of the minor ones deals with images in the Outlook posts. I have to add the feed’s domain to my Trusted Zone in IE to see images. It’s a pain and NewsGator should have an option to do that automatically, but I could live with it. If they would fix those big three, I’d probably pony up the dough.

I should also note that their support pages suck eggs. I typed some carefully selected search terms in their forum search box and got 500 results that weren’t related to my search terms. Then I typed complete gibberish into the search box and got 500 results that weren’t related to my search gibberish. It was neither supportive nor helpful.

Does anyone else use it and have an opinion to share?

Inheriting Formulas

Jason writes:

I had a colleague ask me an interesting question the other day. He has repeating ranges of data that are structured similarly, and he wants to perform calculations on each block. The formulas can get kind of long and error prone. When he catches an error, he has to go back and Copy – Paste Special – Formulas again for each range. … He asks is there any quick formula to use another cell’s formula. For example if A1 has =SUM(B2:D3), typing =FOO(A1) in D2 would return the same as =SUM(E3:G4).

Oh, I’m familiar with writing buggy formulas and feeling like I’m forever fixing and copying. I think any solution is going to create so much overhead that it will be better to stick with copying. However, as Jamie Collins would say, it’s the Everest complex – do it because it’s there.

My first thought, of course, would be to parse the formula by looping through the Precedents property of the range with the formula. It would limit it’s effectiveness to formulas that only referred to ranges on the same sheet, but I knew parsing with string functions would be a losing proposition. Fortunately, I came to my senses and remembered the ever-handy ConvertFormula method of the Application object. Here’s my first hack at it:

Function InheritFormula(rtarget As Range) As Variant
 
    Dim sForm As String
   
    Application.Volatile
   
    sForm = Application.ConvertFormula(rtarget.Formula, xlA1, xlR1C1, , rtarget)
    sForm = Application.ConvertFormula(sForm, xlR1C1, xlA1)
   
    InheritFormula = Application.Evaluate(sForm)
 
End Function

The inner workings of ConvertFormula have always been a mystery to me. Maybe if I new why it was invented, I would have a better of understanding of why it works the way it does. Like why the FromReferenceStyle is required but the ToReferenceStyle is optional. Anyway, the first ConvertFormula converts the formula (well, the name works) from A1 to R1C1 relative to the target cell. So =SUM(A3:A5) gets converted to =SUM(R[2]C:R[4]C). The second ConvertFormula converts it back to A1, presumably relative to the calling cell.

I’m sure this will break with many, many formulas. Array formulas come to mind and something that probably wouldn’t work too well with the Evaluate method. Come to think of it, maybe an array formula is what Jason’s “colleague” needs. (We know it’s really you Jason.) He could highlight the range, edit the formula, and control+shift+enter to fill it in the range. I suppose that’s not much better than just copying and pasting.

Below are the formulas on which I tested it. You’ll notice that I very cowardly didn’t include any complex or difficult formulas. I really don’t want to know where it doesn’t work.

img: simple formulas in excel

Name Manager UI Poll

Hi everyone,

I’ve had some remarks about the design of my Name Manager.
Because it has so many functions, I (to be more precise, we: Charles Williams and I) had a hard time cramming it al into one user interface.

Because (in my opinion) this is a tool aimed at the more proficient Excel user and even more so, the developer I deliberately tried to keep it all on one form so as to have all this functionality on names in one spot.

What would I like you all to do? Below there are three screenshots.

A. Current UI, icons on command buttons, buttons at the bottom of the screen
B. First Alternative: Icons on buttons, buttons at top, making it look like a toolbar.
C. Alternative 2: Buttons at top, text on buttons: no need to wait for the tooltips.

So which do you like best, A, B or C?

A:
B:
C: