You Say CustomUI Editor, I Say TextPad

 

I have written before about hand-crafting XML so as to
customise an Office 2007 ribbon at XML
Is Such A Pain
and Autogen’ed
Ribbon Code

 

As I have mentioned before, the CustomUI editor is far from
perfect. It does allow adding of  image
files and to validate the XML, but the editing experience is absolutely awful.

 

My favourite editor is TextPad,
and one of the useful features of TextPad is the facility to incorporate syntax
files that are file type dependent, for instance a Visual Basic syntax file for
.bas and .vbs file types. The syntax files can highlight elements of your files
which assist in  entering script as well
as making the script more readable.

 

Although I rarely use the CustomUI editor when I am developing
Excel ribbons these days as I autogen everything, my autogen doesn’t yet handle
Word or Access ribbons. As I still don’t want to use the CustomUI editor, I
built myself a CustomUI syntax file for use with TextPad. I can now edit my XML
in TextPad, and copy and paste into the CustomUI editor when it is ready. I
find this much more productive.

 

It is still necessary to setup TextPad with a document class
for these files, setup the keyword colours and so on, but if you think it could
be useful, you can get it a copy via the RSS feed, or download it here.

Styles AIn’t What They Used To Be

 

Everybody knows
that if you are going to create a meaningful document in Word, you use Styles.
Excel has styles as well, but how many of us use these built-in styles, or add
to them with our own custom styles?

I have been building a styles utility recently, so I have
delved deeply into this functionality. Clearly, one of the principal aims of
such a utility would be the ability to edit styles. Being a rational coder, I
don’t want to re-invent the wheel, so I decided to use the built-in styles editor.
This is easily called using dialogs

 

Application.Dialogs(xlDialogApplyStyle).Show

 

From here, the style can be tailored to your needs, a new
style can be added, or a style can be deleted. This dialog defaults to the Normal style, but you can pre-load the style by passing
it as an argument

 

Application .(xlDialogApplyStyle).Show
“myParticularStyle”

 

So far so good. This all looks ideal.

Unfortunately, things are never that simple. The following
is a code snippet that should create a new style called NFx1Dec and open the styles edit dialog for that style.

 

Const StyleName As String = “NFx1Dec”

    On Error Resume Next
    ActiveWorkbook.Styles(StyleName).Delete
    On Error GoTo 0

    Activecell.Style = “Normal”
    ActiveWorkbook.Styles.Add StyleName, Activecell
    Application.Dialogs(xlDialogApplyStyle).Show ActiveWorkbook.Styles(StyleName)

If you call this dialog from VBA as shown above, select your
style to work with, and then click the Modify button, you are presented with a
series of tabs to adjust the particular style properties. From here, select the
Patterns tab and you see a colour palette, a pattern dropdown, and a sample preview box.

So, you want to change the fill colour. Simple, just select
a colour from the colour palette. Of course, it is not simple, otherwise I wouldn’t
be writing this. Select the colour, and nothing happens. You do not see that colour
in the Sample box (as you would if you
were modifying this style in Excel), and when you exit, that style does not have
that fill colour.

You can force it by selecting the pattern option to say solid,
but you don’t have to do this in Excel, so you shouldn’t have to just because it
is being invoked from VBA. You can also force it by selecting your colour, then
select No Colour, then select you desired
colour again (what!).

I tried various things to try and force the pattern to be
solid before invoking the tab, such as

 

   
ActiveWorkbook.Styles(StyleName).Interior.Pattern = xlSolid
   
ActiveWorkbook.Styles(StyleName).Interior.PatternColorIndex =
xlAutomatic

but no joy. So, a  real
failing on using what is a useful function of Excel from within VBA.

I was discussing this with Jan Karel Pieterse, a Dutch Excel
MVP. Jan Karel also has an interest in styles; he has a page about using styles on
his website http://www.jkp-ads.com/Articles/styles00.asp.
Jan Karel came up with the following workaround

 

Const StyleName As String = “NFx1Dec”
Dim CB As
CommandBar
Dim sMenuKeys As
String

Dim sMenuCap As String
    
    Set
CB = Application.CommandBars(“Worksheet Menu Bar”)
    sMenuCap = CB.FindControl(Id:=30006,
recursive:=True).Caption
    sMenuKeys = “%” &
Mid(sMenuCap, InStr(sMenuCap, “&”) + 1, 1)
    sMenuCap = CB.FindControl(Id:=254,
recursive:=True).Caption
    sMenuKeys = sMenuKeys &
Mid(sMenuCap, InStr(sMenuCap, “&”) + 1, 1)
   
    On Error
Resume Next

    ActiveWorkbook.Styles(StyleName).Delete
    On Error
GoTo 0

    Activecell.Style = “Normal”
    ActiveWorkbook.Styles.Add StyleName,
Activecell
    SendKeys sMenuKeys &
ActiveWorkbook.Styles(StyleName)

Although this uses the dreaded SendKeys, it does seem to work. As a stand-alone procedure, it does
work, but when embedded in an application, it had some serious problems.

… to be continued

Keep It Simple

We all know that coding is great fun, even code design is
fun, but testing and debugging are most certainly not fun. As such, we have to
do what we can to lighten that burden. 

One of my underlying principles in coding is in keeping the
code well structured, well laid out, and generally easy to follow, so as to
make it easier to maintain, easier to debug, and just generally a better
experience.

Whilst spending some time on a forum today, I came across
this code which had been found elsewhere. My question to you is, what is
wrong with the following code?

 

Sub Copy_and_Rename_To_New_Folder()
     ”MUST set
reference to Windows Script Host Object Model in the project using this code!
     ‘This procedure will copy all files
in a folder, and insert the last modified date into the file name’
     ‘it is identical to the other
procedure with the exception of the renaming…
     ‘In this example, the renaming has
utilized the files Last Modified date to “tag” the copied file.
     ‘This is very useful in quickly
archiving and storing daily batch files that come through with the same name on
     ‘a daily basis. Note: All files in
current folder will be copied this way unless condition testing applied as in
prior example.
    Dim
objFSO As New
Scripting.FileSystemObject, objFolder As
Scripting.folder, PathExists As Boolean
    Dim
objFile As Scripting.File, strSourceFolder As String, strDestFolder As
String

    Dim
x, Counter As Integer,
Overwrite As String,
strNewFileName As String
    Dim
strName As String,
strMid As String,
strExt As String
    Dim
sSavePath3 As String

    Application.ScreenUpdating = False ‘turn screenupdating
off
    Application.EnableEvents = False ‘turn events off
     ‘Call
Show_BrowseDirectory_Dialog ‘ Allows the Dynmaic selection of Save Path
     ‘identify path names below:
    strSourceFolder =
“C:\Test” ‘Source path
    
‘strDestFolder = “C:\Test\Destination” ‘destination path, does
not have to exist prior to execution
     ”””””NOTE: Path names can be
strings built in code, cell references, or user form text box strings”””
     ”””””example: strSourceFolder =
Range(“A1”)
     ‘below will verify that the
specified destination path exists, or it will create it:
    On
Error Resume Next
    x = GetAttr(strDestFolder) And 0
    If
Err = 0 Then ‘if
there is no error, continue below

        PathExists = True ‘if there is no error,
set flag to TRUE

        Overwrite = MsgBox(“The
folder may contain duplicate files,” & vbNewLine & _
        “Do you wish to overwrite
existing files with same name?”, vbYesNo, “Alert!”)
        
‘message to alert that you may overwrite files of the same name since
folder exists
        If
Overwrite <> vbYes Then Exit Sub ‘if the user clicks YES, then exit the routine..
         ‘Else:
‘if path does NOT exist, do the next steps
         ‘ PathExists = False ‘set flag
at false
         ‘ If PathExists = False Then
MkDir (strDestFolder) ‘If path does not exist, make a new one
    End
If
‘end the conditional testing
    On Error
Goto
ErrHandler
    Set
objFSO = CreateObject(“Scripting.FileSystemObject”) ‘creates a new File System Object reference
    Set
objFolder = objFSO.GetFolder(strSourceFolder) ‘get
the folder

    Counter = 0 ‘set
the counter at zero for counting files copied

    If Not
objFolder.Files.Count > 0 Then Goto NoFiles ‘if no files
exist in source folder “Go To” the NoFiles section
    For Each objFile In
objFolder.Files ‘for every file in the folder…
         ‘parse the name in three pieces,
file name middle and extension.
        strName = Left(objFile.Name,
Len(objFile.Name) – 4) ‘remove extension and leave
name only
         ‘strMid =
Format(objFile.DateLastModified, “_mmm_dd_yy”) ‘insert and format
files date modified into name
         ‘strMid =
Format(Now(),”_mmm_dd_yy”) ‘sample of formatting the current date
into the file name
        strExt =
Right(objFile.Name, 4) ‘the original file extension
         ‘ For Valeo Daily
        Dim
strDate As String
        
‘strDate = Right(strName, 8)
         ‘strNewFileName = Mid(strDate,
3, 2) & “-” & Mid(strDate, 5, 2) & “-” &
Mid(strDate, 7, 2) & ” elec Valeo ” & _
        Left(strName, Len(strName) – 9)
& strExt ‘build the string file name (can be done below as well)
         ‘ End Valeo Daily
         ‘strNewFileName = strName & ”
TET” & strExt
        strNewFileName = “09
lqd ” & strName & ” TRS” & strExt
         ‘objFile.Copy
strDestFolder & “\” & strNewFileName ‘copy the file with NEW
name!
        objFile.Name =
strNewFileName ‘<====this can be used to JUST
RENAME, and not copy

         ‘The
below line can be uncommented to MOVE the files AND rename between folders,
without copying
         ‘objFile.Move
strDestFolder & “\” & strNewFileName
        
         ‘End
If ‘where conditional check, if applicable would be placed.
         ‘ Uncomment the If…End If Conditional
as needed
        Counter = Counter + 1
    Next
objFile ‘go to the next file
     ‘MsgBox
“All ” & Counter & ” Files from ” & vbCrLf
& vbCrLf & strSourceFolder & vbNewLine & vbNewLine & _
    ” copied/moved to: ” &
vbCrLf & vbCrLf & strDestFolder, , “Completed Transfer/Copy!”

     ‘Message to user confirming
completion
    Set
objFile = Nothing: Set
objFSO = Nothing: Set
objFolder = Nothing ‘clear
the objects

    Exit Sub
NoFiles:
     ‘Message
to alert if Source folder has no files in it to copy
    MsgBox “There Are no
files or documents in : ” & vbNewLine & vbNewLine & _
    strSourceFolder & vbNewLine &
vbNewLine & “Please verify the path!”, , “Alert: No Files
Found!”
    Set
objFile = Nothing: Set
objFSO = Nothing: Set
objFolder = Nothing ‘clear
the objects
    Application.ScreenUpdating = True ‘turn screenupdating
back on

    Application.EnableEvents = True ‘turn events back on
    Exit
Sub ‘exit sub here to
avoid subsequent actions

ErrHandler:
     ‘A general error message
    MsgBox “Error: ”
& Err.Number & Err.Description & vbCrLf & vbCrLf & vbCrLf
& _
    “Please verify that all files in
the folder are not currently open,” & _
    “and the source directory is
available”
    Err.Clear ‘clear the error
    Set objFile = Nothing: Set objFSO = Nothing:
Set objFolder = Nothing
‘clear the objects
    Application.ScreenUpdating = True ‘turn screenupdating
back on
    Application.EnableEvents = True ‘turn events back on
End Sub
Sub FolderExists()
    Dim FSO
    Dim
folder As String

    folder = “G:\Marketing\Market
Price Guides\1Valeo Power Summaries”
    Set
FSO = CreateObject(“Scripting.FileSystemObject”)
    If
FSO.FolderExists(folder) Then
        MsgBox folder & ” is a
valid folder/path.”, vbInformation, “Path Exists”
    Else
        MsgBox folder & ”
is NOT a valid folder/path. “, vbInformation, ” Invalid Path”
    End If
End Sub

 

That is a rhetorical question as I will tell you what is
wrong with it. It is over-commented that is what is wrong with it, grossly
over-commented.

Even allowing for the fact that many of the comments were
probably added because it was being posted as a response in an Excel forum,
they are totally self-defeating to my mind.

 Let’s look at in detail …

 

Sub Copy_and_Rename_To_New_Folder()
”MUST set reference to Windows Script Host Object Model in the project
using this code!
‘This
procedure will copy all files in a folder, and insert the last modified date
into the file name’
‘it
is identical to the other procedure with the exception of the renaming…
‘In
this example, the renaming has utilized the files Last Modified date to
“tag” the copied file.
‘This
is very useful in quickly archiving and storing daily batch files that come
through with the same name on
‘a
daily basis. Note: All files in current folder will be copied this way unless
condition testing applied as in prior example.

 

A
relatively standard practice, say what it does. But what a lot of words to say
it, many of which I feel could have been dispensed with a meaningful procedure
name.

 The
library reference comment may be the only bit of this I find useful, but even
that is relatively obvious from the following variable declarations.

 

   
Application.ScreenUpdating = False ‘turn screenupdating off
   
Application.EnableEvents = False ‘turn events off

 

The code
says it all, no need for any comments here.

 

     ‘Call
Show_BrowseDirectory_Dialog ‘ Allows the Dynmaic selection of Save Path
    
‘identify path names below:

 

Presumably, this is some old version  code … so remove it.

 

    strSourceFolder =
“C:\Test” ‘Source path

 

The name
of the variable tells you all you need to know.

 

    
‘strDestFolder = “C:\Test\Destination” ‘destination path, does
not have to exist prior to execution
    
”””””NOTE: Path names can be strings built in code, cell
references, or user form text box strings”””
    
”””””example: strSourceFolder = Range(“A1”)
    
‘below will verify that the specified destination path exists, or it will
create it:

 

Old code
again, but even here what does the comments within say, it explained nothing to
me

 

    On Error Resume Next
   
x =
GetAttr(strDestFolder) And 0
    If Err = 0 Then ‘if there is no error, continue below

 

This is
obvious, , no need for any comments here.

 

        PathExists = True ‘if there is no error,
set flag to TRUE

 

The code
is clear, no need for any comments here. The only comment that would help IMO
is an explanation of what PathExists is used for, but the name tells you that.

 

        Overwrite =
MsgBox(“The folder may contain duplicate files,” & vbNewLine
& _
        “Do you
wish to overwrite existing files with same name?”, vbYesNo,
“Alert!”)
        
‘message to alert that you may overwrite files of the same name since
folder exists

 

Good idea,
add a  comment that essentially repeats
the message.

 

        If Overwrite <> vbYes Then
Exit Sub ‘if the user clicks YES, then exit the routine..

 

 

Totally pointless comment.

 

         ‘Else: ‘if path does NOT exist, do the next steps
        

PathExists = False ‘set flag at false
        
If PathExists = False Then MkDir (strDestFolder) ‘If path does not exist, make
a new one

 

Old code, but again with obvious comments.

 

    End If ‘end the conditional
testing

 

Totally pointless comment.

 

    On Error Goto ErrHandler
    Set objFSO =
CreateObject(“Scripting.FileSystemObject”) ‘creates
a new File System Object reference

 

The code tells you that.

 

    Set objFolder = objFSO.GetFolder(strSourceFolder) ‘get the folder

 

The code tells you that.

 

    Counter = 0 ‘set the counter at zero for counting files copied

 

The code tells you that, the only news here it is a files
counter, so just say that if anything.

 

    If Not
objFolder.Files.Count > 0 Then Goto NoFiles ‘if no files
exist in source folder “Go To” the NoFiles section

 

The code tells you that, res-state what the code says.

 

    For Each objFile
In objFolder.Files ‘for every file in the folder…

 

The code tells you that, basic usage of For.

 

        
‘parse the name in three pieces, file name middle and extension.

 

Some might find this useful, I wouldn’t, the code says it.

 

        strName =
Left(objFile.Name, Len(objFile.Name) – 4) ‘remove
extension and leave name only

 

Anyone familiar with filenames should get this, although it
would be better to use a technique that allows for variable extension types.

 

        
‘strMid = Format(objFile.DateLastModified, “_mmm_dd_yy”)
‘insert and format files date modified into name
        
‘strMid = Format(Now(),”_mmm_dd_yy”) ‘sample of formatting the
current date into the file name

 

Look at that, some of the code has been commented out,
rendering a previous comment incorrect.

 

        strExt =
Right(objFile.Name, 4) ‘the original file extension

 

I repeat my earlier comment on this.

 

         ‘ For Valeo Daily

 

I have absolutely no idea what this means, so it only serves
to confuse me.

 

        Dim strDate As String
        
‘strDate = Right(strName, 8)
        
‘strNewFileName = Mid(strDate, 3, 2) & “-” &
Mid(strDate, 5, 2) & “-” & Mid(strDate, 7, 2) & ”
elec Valeo ” & _
       
Left(strName, Len(strName) – 9) & strExt ‘build the string file name
(can be done below as well)
        
End Valeo Daily
        
‘strNewFileName = strName & ” TET” & strExt
        strNewFileName
= “09 lqd ” & strName & ” TRS” & strExt

 

As before, a lot of old code commented out, adding tgo the
confusion, reducing the readability.

 

 

         ‘objFile.Copy
strDestFolder & “\” & strNewFileName ‘copy the file with NEW
name!
        objFile.Name = strNewFileName ‘<====this can be used to JUST RENAME, and not copy
         ‘The
below line can be uncommented to MOVE the files AND rename between folders,
without copying
         ‘objFile.Move
strDestFolder & “\” & strNewFileName          

        
         ‘End
If ‘where conditional check, if applicable would be placed.
         ‘ Uncomment the If…End If
Conditional as needed

 

This could be useful comments, but I would assume that any
decent coder could work this out if they need to do it. Since when do we add
code, commented out, to cater for other situations?

 

        Counter =
Counter + 1

    Next objFile ‘go to the
next file

 

Totally unnecessary comment.

 

     ‘MsgBox “All ” & Counter & ” Files
from ” & vbCrLf & vbCrLf & strSourceFolder & vbNewLine
& vbNewLine & _
   

copied/moved to: ” & vbCrLf & vbCrLf & strDestFolder, ,
“Completed Transfer/Copy!”
    
‘Message to user confirming completion

 

Old code again, presumably.

 

    Set objFile = Nothing:
Set objFSO = Nothing:
Set objFolder = Nothing
‘clear the objects

 

Comment only says what the code says.

 

    Exit Sub

NoFiles:
    
‘Message
to alert if Source folder has no files in it to copy

    MsgBox “There
Are no files or documents in : ” & vbNewLine & vbNewLine & _
    strSourceFolder
& vbNewLine & vbNewLine & “Please verify the path!”, ,
“Alert: No Files
Found!”

 

Comment only says what the code says.

 

    Set objFile = Nothing:
Set objFSO = Nothing:
Set objFolder = Nothing
‘clear the objects

 

Comment only says what the code says.

 

   
Application.ScreenUpdating = True ‘turn screenupdating back on
   
Application.EnableEvents = True ‘turn events back on
   
Exit Sub ‘exit sub here to avoid subsequent actions

 

The code
says it all, no need for any comments here.

 

ErrHandler:
    
‘A
general error message
e
    MsgBox “Error:
” & Err.Number & Err.Description & vbCrLf & vbCrLf &
vbCrLf & _
    “Please
verify that all files in the folder are not currently open,” & _
    “and the
source directory is available”
    Err.Clear ‘clear
the error
    Set objFile = Nothing: Set objFSO =
Nothing: Set
objFolder = Nothing ‘clear
the objects

 

Comment only says what the code says.

 

   
Application.ScreenUpdating = True ‘turn screenupdating back on
   
Application.EnableEvents = True ‘turn events back on

 

The code
says it all, no need for any comments here.

 

End Sub
Sub FolderExists()
    Dim
FSO
    Dim
folder As String

    folder = “G:\Marketing\Market
Price Guides\1Valeo Power Summaries”
    Set
FSO = CreateObject(“Scripting.FileSystemObject”)
    If
FSO.FolderExists(folder) Then
        MsgBox folder & ” is a
valid folder/path.”, vbInformation, “Path Exists”
    Else
        MsgBox folder & ”
is NOT a valid folder/path. “, vbInformation, ” Invalid Path”
    End If
End Sub

Now I am ready to accept that I am in a minority ( a
minority of only two that I know of), but I generally find comments to be of no
use, and I fully expect the standard police to be down on me for my views. The
code above shows all of the bad usages of comments that I come across,

  • comments that just repeat what the code says
  • too much verbiage in the comments
  • comments that try so hard to be clear, they are
    incomprehensible
  • meaningless comments
  • out of date comments

and so on.

But worse of all, and my biggest gripe against comments is
that they make the code so hard to read. When I am debugging, I am reading the
code, I am looking back at what has happened, I am looking forward at what is
about to happen, and those comments just get in the way. If they were helpful
in other ways, then …, but they rarely are.

Let’s be honest, how many of us really find other people’s
comments helpful, and with our own they usually only tell us what we can read
from (our own) code. And of course, out of date comments are not only
unhelpful, they can be mis-leading, and lead to errors. But of course, we are all
excellent of keeping the documentation up to date aren’t we? 

So my advice, ditch the comments, if you can’t read the code,
leave it alone.

 

I have re-cut that code above without comments, and with better
spacing. I am not saying it is perfect, or the best way, it is just a way that I
find better. I have ditched all the comments, none gave me anything, and I
think the code is now ready for debugging.

As an aside, I have a routine that strips comments from
code, which I wrote so I copuld strip those forum postings where comments gets
in the way.

 

Sub Copy_and_Rename_To_New_Folder()
    Dim
objFSO As New
Scripting.FileSystemObject, objFolder As
Scripting.folder, PathExists As Boolean
    Dim
objFile As Scripting.File, strSourceFolder As String, strDestFolder As
String

    Dim
x, Counter As Integer,
Overwrite As String,
strNewFileName As String
    Dim
strName As String,
strMid As String,
strExt As String
    Dim
sSavePath3 As String

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    strSourceFolder = “C:\Test”

    On
Error Resume Next
    x = GetAttr(strDestFolder) And 0
    If
Err = 0 Then

        PathExists = True
        Overwrite = MsgBox(“The
folder may contain duplicate files,” & vbNewLine & _
        “Do you wish to overwrite
existing files with same name?”, vbYesNo, “Alert!”)
        If
Overwrite <> vbYes Then Exit Sub
    End
If

    On Error
Goto
ErrHandler

    Set
objFSO = CreateObject(“Scripting.FileSystemObject”)
    Set
objFolder = objFSO.GetFolder(strSourceFolder)

    Counter = 0
    If Not
objFolder.Files.Count > 0 Then Goto NoFiles

    For Each objFile In
objFolder.Files

        strName =
Left(objFile.Name, Len(objFile.Name) – 4
        strExt =
Right(objFile.Name, 4)
        Dim
strDate As String
        strNewFileName = “09
lqd ” & strName & ” TRS” & strExt
        objFile.Name =
strNewFileName
        Counter = Counter + 1
    Next
objFile

    Set
objFile = Nothing: Set
objFSO = Nothing: Set
objFolder = Nothing

    Exit Sub

NoFiles:
    MsgBox “There Are no files or
documents in : ” & vbNewLine & vbNewLine & _
    strSourceFolder & vbNewLine &
vbNewLine & “Please verify the path!”, , “Alert: No Files
Found!”

    Set
objFile = Nothing: Set
objFSO = Nothing: Set
objFolder = Nothing

    Application.ScreenUpdating = True
    Application.EnableEvents = True

    Exit Sub ‘exit sub here to avoid subsequent actions

ErrHandler:
    MsgBox “Error: ” &
Err.Number & Err.Description & vbCrLf & vbCrLf & vbCrLf & _

    “Please verify that all files in
the folder are not currently open,” & _
    “and the source directory is
available”
    Err.Clear ‘clear the error

    Set objFile = Nothing: Set objFSO = Nothing:
Set objFolder = Nothing

    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Sub FolderExists()
    Dim
FSO
    Dim
folder As String

    folder = “G:\Marketing\Market
Price Guides\1Valeo Power Summaries”
    Set
FSO = CreateObject(“Scripting.FileSystemObject”)

    If
FSO.FolderExists(folder) Then

        MsgBox folder & ” is a
valid folder/path.”, vbInformation, “Path Exists”
    Else

        MsgBox folder & ” is NOT
a valid folder/path. “, vbInformation, ” Invalid Path”
    End If
End Sub

Regression Can Be Sweet

My laptop blew this last weekend. Unfortunately, my backups were yet to be done, so I have lost a fair amount of work. That is incredibly frustrating, but this post is on another topic.

I was an Excel 2000 user for many years. I did dabble with 2002/XP for a while, but I didn’t see any real advantage to it,  didn’t really enjoy it, so I went back to Excel 2000.

When I did finally take the plunge, I upgraded to Excel 2003, and after a few querulous moments adapting to this flashy beast, where I first took notice of Microsoft’s drive to push the visual effects often to the detriment of the content, I settled down with Excel 2003 and have enjoyed a fruitful relationship for some years. Of course, I have dabbled like so many with the new kid on the block, Excel 2007, but I can’t say I have been won over yet. I don’t like the Ribbon, I don’t hate it, I just don’t see the rationale for it. I think some things have been really messed up, conditional formatting stands out for me. I think some new things have been poorly implemented, the NameManager as an example and the weak set of new functions, and there are many missed opportunities. But worst of all, I think glitzy effects are being added far too loosely, without thinking, or without knowing what is good visualisation. Maybe 2010 will set the product right, but in the meantime, Excel 2003 is still my Excel of choice.

But here is a strange thing. After my laptop blew, I dug my old Sony Vaio out of the cupboard. I had work to do, so I needed a laptop, but this machine only had Office 2000 on it. Now I have to admit that Outlook 2000 feels positively ancient, I long for Outlook 2003. But … I love using Excel 2000 again. I have been using it for 3 days now and I haven’t missed Excel 2003 once. This has completely surprised me.

OpenOffice Is Looking Good

Normal
0

false
false
false

MicrosoftInternetExplorer4

<!–
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{mso-style-parent:””;
margin-top:0cm;
margin-right:0cm;
margin-bottom:6.0pt;
margin-left:0cm;
mso-pagination:widow-orphan lines-together;
font-size:11.0pt;
mso-bidi-font-size:12.0pt;
font-family:Arial;
mso-fareast-font-family:”Times New Roman”;
mso-bidi-font-family:”Times New Roman”;}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;
text-underline:single;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;
text-underline:single;}
@page Section1
{size:595.3pt 841.9pt;
margin:2.0cm 2.0cm 2.0cm 2.0cm;
mso-header-margin:35.45pt;
mso-footer-margin:35.45pt;
mso-paper-source:0;}
div.Section1
{page:Section1;}
–>

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-parent:””;
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin:0cm;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:”Times New Roman”;
mso-ansi-language:#0400;
mso-fareast-language:#0400;
mso-bidi-language:#0400;}

I have blogged previously
about what a rubbish job MS have done with Conditional Formatting in Excel
2007, but it seems it is worse than I feared.

In this same file I now had CF setup as I wanted, after much
trials and tribulations, but I needed to insert a new column, but the CF was
required to be different than the CF on either side.

First thing I tried was to simply insert the column. Of
course, this meant that it inherited the CF from the column to the left, which
was not what I wanted, so I opened the CF dialog and thought I would change the
range that the rule applies to from those two columns to just my newly inserted
column, and change that rule. This I did, but it wiped out the rule entirely
from the other column. And to compound the problem, it cleared the format fill
colour from the newly inserted column, and when I changed the rule and added
back the fill colour, every cell in my range showed the colour, even though
only a single cell matched the condition being tested for.

So I tried another approach. I created a column off the data
area, setup the CF exactly as I wanted for that column, then added an index
number in the first free row so that I could sort the columns into my required
order. For some unknown reason, this removed my CF, gave it the CF of the
column to its (new) left, and also managed to setup data validation as was
applied to its (new)O column to the right. Genius! I knew what I wanted to do,
but the designers at MS decide they know better, in two ways.

So how did I fix it? I first deleted the CF from the column
that is already present, then inserted my new column. I then re-instated the CF
that was in the previous column, and added the CF in my new column. Not a great
chore, but I SHOULDN’T HAVE TO FIGHT THE SOFTWARE to do what I want to
do. And you can bet that the next time I need to do it, I will have forgotten,
and I will go through all this pain again.

Whilst I am having a good rant, when are MS going to default CF (and Data
Validation) edit boxes to update, so as to avoid accidentally inserting rubbish
when you hit the arrow keys. I can’t recall ever thinking the default was
useful.