A Favorite Utility – Chip Pearson’s Cell View

I’ve been using Chip Pearson’s Cell View for years. It does just one major thing, but it does it extremely well: It shows you the ascii or hex codes for every character in a cell, and if there are hidden or trailing (or leading) characters, it flags them. A common example would be the non-breaking space, ascii(160). If you’re wondering why your FIND(” “,A1) isn’t working, Cell View will show you if it’s ascii(32) or ascii(160) that you have at hand. Here’s Chip’s example with tab character ascii(9) in the middle. Note the red caret indicating the special character.

If ascii isn’t what you want, you can optionally display the codes in hexadecimal. It does this magic by adding a “View Cell Contents” item at the bottom of the View Menu, just where you’d want it to be. Chip gives this utility away, and even provides the password with an invitation to change it. With the temerity gathered from hanging around here, I altered some minor things to suit my preferences a bit more.

The first two of these changes are in the frmShowChars code (right-click on the form frmShowChars and View Code). Chip allows you to select a starting position by counting by one’s. I made a mod to make it count by ten’s. Change the With statement in Sub UserForm_Activate() as so:

Private Sub UserForm_Activate()
   Dim N       As Long
   With Me.cbxStart
      .Clear
      .AddItem Format(1, “##0”)   ‘Added
      For N = 10 To Len(ActiveCell.Text) Step 10   ‘Changed
        .AddItem Format(N, “##0”)
      Next N
      If .ListCount > 0 Then
         .ListIndex = 0
      End If
   End With
   Me.lblVarType.Caption = vbNullString
   DoIt
End Sub

Counting by one’s seemed like overkill when coupled with a desire for the caption to tell me the cell’s total length. I changed the very top lines of code in Sub DoIt() as follows:

   If Me.chkHex.Value Then
      Me.lblCode.Caption = “Hex”
   Else
      Me.lblCode.Caption = “Ascii”   ‘Changed
   End If

   N = VBA.Len(ActiveCell.Text)   ‘Added
   C = IIf(N <> 1, ” characters.”, ” character.”)   ‘Added

   Me.Caption = “Character Codes For Cell: “ & ActiveCell.Address(False, False) _
                & “.  This cell” & Chr(146) & “s length is “ & N & C   ‘Added with underscore

 
With these changes, Cell View looks like this, showing special character acsii(160):

“View Cell Contents” is enabled even when no worksheet is visible or when no workbook is open. If you call it then, it throws an error. A fix was to edit the Sub ShowTheForm() found in the regular module modMain by calling Chip’s splash screen and then exiting:

Sub ShowTheForm()
  If ActiveCell Is Nothing Then   ‘Added
    frmAbout.Show   ‘Added
    Exit Sub   ‘ Added
  End If   ‘Added
  #If VBA6 Then
    frmShowChars.Show vbModeless
  #Else
    frmShowChars.Show
  #End If
End Sub

 

For Mac users, this version of Cell View does not work. A previous version called HexChars.xla does. The bad news is that you need a PC to unlock the code. Maybe it’ll be fixed in Excel 2011. It’s on my birthday list.

Cell View is a .xla file (CellView.xla). Put it in your add-ins folder and load it via Tools/Add-Ins…

…mrt

Posted in Uncategorized

3 thoughts on “A Favorite Utility – Chip Pearson’s Cell View

  1. Hi Dan –

    The link is in the first line at the top. If you want a version with the discussed mods in it, you’ll have to roll your own. Chip’s website gives the VBA password (the letter “a”, no quotes).

    I should point out that the “count by tens” mod eliminates the full functionality of the delete character button, a feature I never use.

    …mrt

  2. The horizontal layout confuses me. I know that’s how people read, but isn’t this begging for a listbox and a scrollbar? That would completely eliminate the need for a “start at char” combo and users already get how to use a listbox.


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

Leave a Reply

Your email address will not be published.