18 thoughts on “Characters Count”

1. Yes, that doesn’t seem right. However, I’ve never used the Characters object. I was vaguely aware of its existence, but that’s about it. Further proof that I don’t understand it…

   This displays the first character in the active cell:

   MsgBox ActiveCell.Characters(1, 1).Caption

   Based on my limited understanding, this should also display the first character (but it doesn’t):

   Dim c As Characters
   Set c = ActiveCell.Characters
   MsgBox c(1, 1).Caption

2. On second thought, I have spent some time with the Characters object. In fact, I wrote an add-in that makes heavy use of it:

   http://j-walk.com/ss/excel/files/supersub.htm

   Man, the old memory is rapidly fading. It’s a wonder I can even function in society any more. What were we talking about???

3. Ditto on the memory thing. Out with the old, in with the new most days.

   Just in case anyone stumbles by and wonders what the answer to the orginal question is (plus I’d already written this before you posted your comment), it is that Range.Characters() is a method that takes 0, 1, or 2 arguments and returns a Characters object. The Characters object itself doesn’t expose a default property with which you can subsequently access some subset of its contents using parentheses – it’s basically an immutable pointer to some range of characters in the original text. The For Loop in the original sample above calls the Characters() method 24 times (2 times per character position), returning 24 independent Characters objects, each spanning as many characters as specified by the “i” loop variable, which is why they each report their length as being the number of characters included during their instantiation via the Range.Characters() method.

4. Dick,

   I believe this accomplishes what you had originally expected your code to do. I’m sure you’ve already figured it out, but it gives me an excuse to post:

   Debug.Print .Characters(i, 1).Caption, Len(.Characters(i, .Characters.Count).Text)

5. Change that inner line to:

   Debug.Print .Characters(1, i).Caption, .Characters(1, i).Count

   and you get a different output:

   This is text 12
   T 1
   Th 1
   Thi 1
   This 1
   This 1
   This i 1
   This is 1
   This is 1
   This is t 1
   This is te 1
   This is tex 1
   This is text 1

   It seems that .Count mixes up .Characters(start, length) and treats it as .Characters(length, start).

6. After a little experimentation, my money’s moving on to “bug”….

   I thought that by explicitly creating a new Characters object, the “curiosity” might be circumvented:

   Dim i As Long
   Dim chars As Characters
   With ActiveCell
   Debug.Print .Characters.Text, .Characters.Count
   For i = 1 To .Characters.Count
   Set chars = .Characters(i, 1)
   Debug.Print chars.Text, chars.Count
   Next
   End With

   But it isn’t. Which I’m finding difficult to figure. I can’t find an obvious way to create a “New Characters” and load it with text (hardly surprising, I suppose, given its purpose) so it’s hard to figure out what’s going on here. It certainly appears that Count is not derived directly from Text or Caption (the point of having both for the minute escapes me here). I’m guessing that when a Characters object is created as a result of accessing the Property, the content is loaded and the Count value explicitly set – in this instance, to the wrong thing. Maybe there’s a Unicode consideration that I’m missing.

7. The characters object of a autoshape does not suffer with this.

   So Jon’s ‘discovery’ of the swapped arguments, for me at least, would point to a bug!

8. Whoa. Didn’t see that behavior last night. Clearly a bug in Count(). Perhaps, as suggested, because it is internally relying on the wrong argument. Interesting. Better then to use Len(somerange.Characters(x, y).Text). Good catch.

9. Andy –

   “So Jon’s ‘discovery’ of the swapped arguments, for me at least, would point to a bug!”

   I don’t know what’s a bug anymore. I’ve been working with (or trying to work with) Excel 2007’s charts, and I keep finding potential showstoppers. The biggie I discovered today is that a combination chart with XY and line series cannot use a single date-scale axis for the X values (dates). This breaks with tradition; I’ve been doing it this way since at least Excel 97, and I’ve incorporated it into many projects and several tutorials on my web site. The upshot is that charts like this are currently not possible:

   http://peltiertech.com/Excel/ChartsHowTo/StockChartsAndLineChartTricks.html#OCticks

   The generally less efficient interface of Excel 2007 is another turn-off. Here are a few issues:

   * Let’s leave alone for today the lack of customization in general and specifically the ability to drag UI elements (command bars and the tearaway controls) to where they’re needed.

   * Dialogs have more tabs than ever before, requiring me to click back and forth several times, for example, while formatting the patterns of a chart series. To me there seems to be plenty of room on the tabs to allow recombining them into fewer tabs. To format a line chart series, the Excel 2003 Patterns tab has been replaced by six tabs for marker options, marker fill, line color, line style, marker line color, and marker line style; that’s seven if you count the shadow tab, but I’m willing to attribute that one to the new graphics formatting system.

   * The F4 function key isn’t as widely used as ‘Repeat Last Action’. Change one series from one chart type to another, select another series, press F4, and nothing happens. Ditto for changing between primary and secondary axes. I have some charts with dozens of series, and F4 reduces my effort to a couple seconds per series, rather than 10 ro 20 to keep reloading the dialog.

   * On the Chart Design contextual tab, there is no indication of which chart element is selected, so I have to click back and forth to make sure I’ve selected the correct series before I change the chart type. The group is on both the Layout and Format tabs, violating the new sacred principle of locating a UI element in one place only.

   * The loss of double-clicking to open the format dialog is significant. Now double-clicking activates the Chart Design tab, and 90% of the time what I want is on the other tabs, but especially in the format dialog.

   * There is wasted effort due to the replacement of sets of option buttons by dropdowns, as in the Format Axis dialog when formatting the axis ticks. If I can’t see all the choices, I need to waste a click to drop down the options. Sure, I know what they are, but it’s nice to have them all available at a glance.

   Don’t get me wrong, there’s a lot of cool stuff in Excel 2007. I just feel like there was a lot of unjustified hype about the new user interface which hasn’t turned out to be true (the ribbon’s vertical height is more than in an earlier beta when the Office 2007 blog proved it was the same as in Excel 2003, and the taller row height means substantially less of my sheet is showing). I hope they come back and tell me that all of these shortcomings were addressed between Beta 2 TR and the RTM, and I will gladly thank them and retract my statements right here.

10. Jon, put that Chart Elements drop-down on your QAT so it’s always visible. But be careful. When you do that, the built-in Chart Element drop-downs are no longer reliable after you add something like a trend line or error bars. Big bug. Probably too late to fix.

    Re: lack of double-click in charts. No biggie. It takes only a few sessions to get in the habit of using Ctrl+1. But it’s really annoying when you select an element in a different chart and see the Format dialog box disappear. Yet it remains visible (and completely useless) when you click in the worksheet.

    All in all, I think Excel 2007 is an improvement. I also think that MS is very busy working on Office 14 right now. Internally, they probably think of ’07 as a revenue-generating beta.

11. Jon, it’s a thin line between bug and feature ;)

12. Andy –

    “Jon, it’s a thin line between bug and feature”

    I’ll bet it requires at least two tabs to format it, line color and line style.

    John –

    “Jon, put that Chart Elements drop-down on your QAT so it’s always visible. But be careful….”

    I’ve so far not used the QAT. It seems like such a tacky little bangle strapped on at the last minute to appease the lowly 2% of us who unreasonably expect to customize our interfaces. It seemed temporary to me, and I guess I’m still holding out on a full-featured replacement. And I know of at least two smart people who are working on such things. So whether I’m waiting for that better mousetrap, or just procrastinating, I still haven’t used the QAT.

    “All in all, I think Excel 2007 is an improvement.”

    You’re probably right. You’ve used it more than I have, because you’re writing books while I have a real job <g>. You’ve also had more of an attitude change about it; ISTR you were really negative about the whole ribbon thing at first. I started out neutral, thinking that I’d give it a chance, and I’ve given it half a chance, but there are some inherent inefficiencies to its implementation and to that of the new formatting dialogs, and these distract me to the point that I put away the beta and work on something else. I’m still pretty much neutral, but things like the bugs that launched my earlier rant are barriers to my acceptance.

    I don’t know what’s up with Office 14, but I’ll bet Office 12 SP1 will be a biggie.

13. John says:

    >It takes only a few sessions to get in the habit of using Ctrl+1

    What…you mean everyone doesn’t do that anyway? :-)

    Its the shortest and most consistent way for each object in Excel

    But the irony is that for those of us writing (big) reports in Word…Ctrl+1 does not bring up the formatting panes … there’s a cross over I’d like to see adopted.

    cheers,
    Christopher

14. Christopher –

    The problem with Ctrl+1 in other apps is that Word already uses it to apply single spaced paragraph formatting to the selection.

    Also, in Excel, if I have just used the mouse to select an object, it’s easier to double click. I always use Ctrl+1 if I’m already on the keyboard. Just as Dick doesn’t like moving his hand off the keyboard to use the mouse, if I’m using the mouse, and I’ll use it in another second to select options in a dialog, I’d like to use the mouse to open the dialog.

15. John,

    Re:Memory…
    If like me you have a memory like one of those metal things with the holes in it that you use for cooking spaghetti. Just rely on that old quote by Mr. Whatshisname “I have forgotten more than you will ever know”…

    If, that is, you can remember it.

16. Office 14? Isn’t Office 2007 version 12, as in INFO(“Release”)? Did Office 13 go the way of Office 6?

    As for the ribbon, since it can be set to automatically collapse after every action, configured that way it’s just a new menu. Pity USER-customized toolbars are no longer simple to create and impossible to locate outside the ribbon.

    Myself, in Excel 2003 I had added an item to my standard toolbar named Tool&bars that pointed to the built-in Toolbar menu that gave me keyboard access to showing/hiding toolbars. Not much use for Excel’s bundled toolbars, but VERY useful for some in-house custom toolbars that were alternative menus with their own keyboard accellerators. No simple way to choose which old-style toolbars are shown/hidden in the Add-in tab in the ribbon. NOT NICE AT ALL!

    As a single-user tool, Excel 2007 may be an improvement. However, for Excel as part of larger systems, Excel 2007 is a MAJOR PITA to the point of being unusable. Until there’s a way to hide the ENTIRE Excel UI, as there has been through Excel 2003, there are more than a few corporations that won’t be upgrading.

17. Corel had Corel X3 instead of Ver 13

    Now where’s that Black Cat, Ladder and Mirror

    Maybe Microsoft could get the Myth Busters in to help with that…

    I agree with Jon Peltier’s comments on 2007 above, too much glam on the front at the cost of usability.

    Hui…

18. fzz –

    “Until there’s a way to hide the ENTIRE Excel UI”

    Using the RibbonX XML based system, you can dispense with the default ribbon and build your own from the bottom up. However, it’s less integrated than the menu/commandbar system in Excel 97-2003, because it’s not exposed via the object model.

    I guess we just have to get used to it, or rely on third-party solutions to bring beck some familiar functionality.

Posting code? Use <pre> tags for VBA and <code> tags for inline.