Category Archives: Uncategorized

PowerPivot BlogRoll – 20th January 2013

PowerPivot activity announced on Twitter for the week ending 20th January 2013.

 

PowerPivotPro
Modeling Viral Growth vs. Traditional Advertising in PowerPivotbit.ly/10EOrS3

Javier Guillén Pervasive Business Intelligence
Grouping by an Aggregation in DAX – bit.ly/SeC22c

PowerPivotPro
Calendar Tables: Not Just for Formulas. Use Them on Your Pivots Too! – bit.ly/W6YrMa

Javier Guillén Pervasive Business Intelligence
Leveraging DAX query tables for staging Data Transformations – bit.ly/13Awule

Some Random Thoughts
Row Selection Using Slicers in PowerPivot – Part 1 – bit.ly/108bp2H

Chris Webb’s BI Blog
A Different Approach To Last-Ever Non-Empty in DAX – bit.ly/Y82Owx

Excel Do, Dynamic Does
CUBEs For Dessert – Cube formulae with MDX – bit.ly/13uGmNf

SQLBI – Marco Russo
PowerPivot Comatibility across versions – bit.ly/UKJ9MM

SQLBI
Linkback Tables in PowerPivot for Excel 2013 –bit.ly/UBhmAi

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.

Coding Hurts, But the Ribbon Breaks Your Heart

Normal

0

false

false

false

false

EN-US

X-NONE

X-NONE

MicrosoftInternetExplorer4


I was
building a ribbon this week (yes, I can’t get enough of them), when I cam across
what is a really stupid problem.

My
application is building a cascading dynamic menu , a list of all files in the
selected directory, and all sub-directories in the parent directory are added
as sub-dynamic menus (creating the cascade when they are clicked).

No sweat I
thought, just use FSO to get the files and the folders, and string the XML
together dynamically … I’ve done it a hundred times, it will be a breeze.

Problem was
that on testing, I kept getting a rather unhelpful message like the following


It is most
unhelpful as the XML is being built dynamically, so it is not easy to see the
string (you need to debug  the code and output
the string!).

This wasn’t
happening every time, just sometimes. Because it was in an application with
other functionality, I decided to build a test workbook focussing
specifically on the problem.

I built a
workbook with a single dynamic menu control. That control invoked code that
queried for a directory, and then builds the XML for the files and the
first-level of sub-directories.

One thing
to note, the crux of the problem, is that I use the tag property to hold the
full file or directory path, as this is intended to be a technique to open
these files, or trigger another dynamic list for the sub-directory.

It turned
out to be that when the directory name includes an & (ampersand) character,
and we load the tag property with that value, the XML complains.

It also
turns out that you cannot load the tag attribute with such a directory name in
the CustomUI editor.

I know I
can replace these characters with some other unique string before loading, but
then I would need to decipher it before using it. I feel madness rushing up on
me.

I am sure
there is some excuse, I mean valid reason, that is all down to the way that XML
handles these characters, but … it is just text and the tag attribute is just a
string value; an & is perfectly allowable within a directory name, so this
strikes me as being nuts.

This is the
custom XML and code in my test workbook

<customUI xmlns=http://schemas.microsoft.com/office/2006/01/customui>

   
<
ribbon>

        <tabs>

            <tab idMso=TabHome>

                <group id=rxgrpInvalid label=Invalid Chars>

 

                    <dynamicMenu id=rxdynInvalid

                                 label=Dir List

                                 size=large

                                 imageMso=SmartArtOrganizationChartRightHanging

                                 getContent=rxGetContent />

 

                    <button id=rxbtnRefresh

                            label=Refresh

                            imageMso=HighImportance

                            size=large

                            onAction=rxGetButtonAction“/>

                 </group>

            </tab>

        </tabs>

   
</
ribbon>

</customUI>

 

Option
Explicit

Private rxIRibbonUI As IRibbonUI

Global cntDiv As Long

Sub rxIRibbonUI_onLoad(ribbon As IRibbonUI)
    Set rxIRibbonUI = ribbon
End Sub

Public Sub rxGetContent(control As IRibbonControl, ByRef Content)
Dim mpDialog As FileDialog
Dim FSO As Object
Dim TemplateFolder As Object
Dim XML As String

    Stop
    Set mpDialog =
Application.FileDialog(FileDialogType:=msoFileDialogFolderPicker)
    With mpDialog
       
        .AllowMultiSelect = False
        If .Show = -1 Then

            Set FSO =
CreateObject(“Scripting.FileSystemObject”)
            Set TemplateFolder =
FSO.GetFolder(.SelectedItems(1))

            XML = “<menu
xmlns=””http://schemas.microsoft.com/office/2006/01/customui””>”
& vbNewLine
            XML = XML &
XMLFiles(TemplateFolder, True)
            Content = XML &
“</menu>”

            Set TemplateFolder = Nothing
            Set FSO = Nothing
        End If
    End With

    Set mpDialog = Nothing

End Sub

Public Sub rxGetButtonAction(control As IRibbonControl)
    Select Case control.Id

        Case “rxbtnRefresh”
            rxIRibbonUI.InvalidateControl
(“rxdynInvalid”)

        Case Else
            If Left$(control.Id, 9) =
“rxbtnFile” Then Workbooks.Open (control.Tag)
    End Select
End Sub

Public Function XMLFiles( _
    ByRef Folder As Object, _
    ByVal TopMenu As Boolean) As String
Dim thisXML As String
Dim subFolder As Object
Dim file As Object
Dim cntFiles As Long

    For Each file In Folder.Files

        ‘Check if file is a temporary
file
        If Not Left(file.Name, 2) =
“~$” Then

           Select Case True

                Case file.Type Like
“*Microsoft*Excel*”

                    cntFiles = cntFiles +
1
                    thisXML = thisXML
& “<button ” & vbNewLine & _
                                  id=””rxbtnFile” & cntDiv
& cntFiles & “”” ” & vbNewLine & _
                                  label=””” & file.Name
& “”” ” & vbNewLine & _
                                 
imageMso=””FileSaveAsExcel97_2003″” ” &
vbNewLine & _
                                 
onAction=””rxGetButtonAction”” ” &
vbNewLine & _
                                  tag=””” & file.Path
& “””/>” & vbNewLine
           End Select
        End If
    Next file

    If cntFiles = 0 Then

        ‘Create a “No
Templates” button if no applicable files found
        thisXML = thisXML &
“<button ” & vbNewLine & _
                      id=””rxbtnNoFiles” &
cntDiv & “0”” ” & vbNewLine & _
                      label=””No Files
Found””/>” & vbNewLine
    End If

    For Each subFolder In
Folder.subFolders

        cntDiv = cntDiv + 1
       
thisXML = thisXML & “<menuSeparator id=””div”
& cntDiv & “””/> ” & vbNewLine & _
                  “<dynamicMenu
” & vbNewLine & _
                     
id=””rxdmnuSub” & ClearId(subFolder.Name) &
“”” ” & vbNewLine & _
                      label=””” &
subFolder.Name & “”” ” & vbNewLine & _
                      imageMso=””FileOpen””
” & vbNewLine & _
                      getContent=””rxGetContent””
” & vbNewLine & _
                      tag=””” & subFolder.Path
& “””/>” & vbNewLine
    Next subfolder

    XMLFiles = thisXML

End Function

Private Function ClearId(ByVal Id As String) As String
Dim RegEx As Object
    Set RegEx =
CreateObject(“VBScript.RegExp”)
    RegEx.Pattern = “[\
\\/:\*\?””<>\|]”
    RegEx.Global = True
    ClearId = RegEx.Replace(Id,
“”)
    Set RegEx = Nothing
End Function

 

Hand-holding for Dummies

Normal
0

false
false
false

MicrosoftInternetExplorer4


I am an inveterate builder of addins for Excel. They are so
flexible, so easy to build, but they have a potential deployment issue. Yes I
know that for most of you installing an addin is trivial, but I create them for
corporations as well, and they either

a) cannot afford to go to every desktop and install addins
manually, or

b) cannot assume that all of their staff who might need the
addin can install it themselves (even if they have the necessary permissions) .

Of course, the solution is well known, build a
self-installing executable. There are many good products around, such as Wise,
Setup Factory. The downside of these is that there is a cost, a healthy cost,
and as well as being an inveterate addin builder, I am an inveterate
cheapskate. We also have the Windows Installer (is this free?), but I have only
ever used it to install a product, I have never built installers with this.

Which brings me to the point of this post. I have been using
a remarkable free installer for some time now, Inno Setup. Jordan Russell’s tool is superb, to
quote it’s own PR … Inno Setup is a free installer for Windows programs.
First introduced in 1997, Inno Setup today rivals and even surpasses many
commercial installers in feature set and stability…
I don’t think there is
anything to argue with there. It is even free for commercial use.

The installation details are scripted in an .iss file, basically a batch instruction file, which
is divided into various sections where you add the various installation specific details. The sections include:

[Setup] defines
the basic setup details, such as the default directory name for the application
(it has some constants, such as pf
for Program Files), license file if there is one, the images to use in the
installer and so on

[Messages]
runtime messages can be defined here

[CustomMessages]
custom messages can be defined here, and used in the code section (see later)

[Files] a simple list of all of the files to be installed,
and where to store them

[Registry] any registry
key updates that the installer will run

[Code] where you
can add installation specific code. This code has to be written in Pascal, the
product is written in Delphi after all. Here you can use the messages mentioned
above, such as a message to shutdown all versions of Excel before continuing.
It could test if Excel is running

            if
CurPageID = wpWelcome then

 

                        begin

 

                                    while
XLIsRunning do

                                                begin

                                                            mpRet
:= MsgBox(‘Please close Excel before continuing’, mbError, mb_RETRYCANCEL);

                                                            if
mpRet = IDCancel then Abort;

                                                end;

                        end;

//—————————————————————————————————–

function XLIsRunning(): Boolean;

//—————————————————————————————————–

// Note – this will not detect invisible instances of xl
running (but that should be unlikely)

//—————————————————————————————————–

begin

            Result :=
False;

            if
FindWindowByClassName(‘XLMAIN’) <> 0 then Result := True;

end;

Most interestingly for me, it can be used to automatically
update the registry so that the next time that Excel starts the addins are already
added to the addins list, and thus will build their menus, initiate and so on
when Excel starts. If you want your addin in the addins list automatically, you
have to update the registry (when you install an addin manually, the registry
is updated the next time that you close Excel, that is why it is there next
time).

In Excel addin details are stored in HKCU\software\Microsoft\Office\n.n\Excel\Options,
where n.n is the version number. Each addin is assigned the next available key
OPEN, OPEN1, OPEN2 etc. These are simple string value keys with the full path
of the addin.

Of course, life is never that simple. If your addin could be
deployed across many Excel versions, you have to cater for them all. The key
given above is the key location for Excel 2003 (version 11.0),  is also true for Excel 2002 (version 10.0),
Excel 2000 (version (9.0), and even Excel 2007 (version 12.0), but is not true Excel
97 (version  8.0), this key is HKCU\Software\Microsoft\Office\8.0\Excel\Microsoft
Excel
. Thus any installation code needs to account for this.

My usage of the tool just scratches the surface of its full
capabilities, but even so, it has become indispensable to me. I look forward to
seeing how it fares when I start deploying .Net solutions, I am very confident.

As I said, another great tool which I use for all of my
addins. There is even an active support forum.

 

Because it is such a good tool, there is the usual spate of
added-value addons.

Bjørnar Henden has
created a GUI front-end for creating and editing Inno Setup Scripts, ISTool. I have used this, and it is good, but
personally I am not GUI mad, so I stick to the batch file style. After all,
most are copies and then a few updates.

Another GUI front-end for creating and editing Inno Setup
Scripts is Jonny Kwekkeboom’s ScriptMaker. I have not tried
this myself.

A new one that I haven’t tried, but looks very interesting,
is a tool that adds customizable skin support to Inno Setup installations, Codejock
Software
’s ISSkin.

I did also find a decompiler somewhere, that saved me once
when I lost my source file, but it is not listed on the Inno site, so I will
have to look again.

 

Time has come today

Normal
0

false
false
false

MicrosoftInternetExplorer4


I saw a question in the newsgroups today where a guy was asking
how he could get more than 10,000 hours in a cell, adding a hour number larger
that 10,000 to another time number the
formula did not give a correct answer. 

Some of the answers suggested that Excel cannot hold a value
of 10,000 or more hours. This is actually incorrect. It is true that it is only
possible to enter a single value of 9,999:59:59 into a cell, but it can be
tricked into holding more.

For example, enter 5000:00 in cell A1 and the same in B1,
and a formula of =A1+B1 in another cell will show 10000:00.

It is even possible to enter it in a cell using a formula
such as

=”5678:00″+”5876:00″

and we get an answer of 11554:00.

Of course, the cell does have to be entered as [h]:mm to
see the result as more than 24 hours.

 

Names Should Be Seen

Normal

0

false

false

false

MicrosoftInternetExplorer4


Doing some work on an Excel spreadsheet this morning
reminded me that there are some great products that have been developed that
enormously enhance the ease of use, flexibility, and general usefulness  of Excel. Whilst this blog is not really about
promoting Excel products, there is one product that I believe stands head
and shoulders above any other out there. I am not a great fan of installing
Excel addins, they usually have 200 functions of which I only want 1 or 2, but I
have installed this addin and I don’t believe there is ever a day that I do
not use it. Because of this, and because the price is spot on (it is free), I
am going to shout the praises of Jan Karel Pieterse’s NameManager addin.

This tool has been around for a number of years, and has
been indispensable if you use Excel names extensively (which I do). There is a
debate to be had about whether we should use names, some swear by them, some
swear at them, but that is for another day.

Using the names dialog in pre 2007 versions of Excel was
painful. I am of course referring to the Insert>Name>Define… dialog which
threw up this incredibly helpful beast

There were a few other concessions to usability, Debra
Dalgleish is highlighting the Create
Names From Excel Labels
facility on her blog today, but generally it
was hard work. That is, until JKPs addin came along. Suddenly, it was possible
to see all of your names in a sensibly structured dialog, there were filtering options,
you could evaluate names, see if they were being used, and much more. Compare
this dialog

to that previous dialog, look at the richness of facilities,
the options, but most of all the sensible presentation. When managing names, it
is imperative in my view to see as much information as possible, limited by my
choice, not the limitations of the tool.

Of course, MS have revamped Excel, and in Excel 2007, they
introduced their own version of Name Manager. With the experience of
running the old dialog for many years; the example of better version to
draw upon (JKPs addin); and the fact that they can tap into the heart of
Excel, MS were bound to produce the definitive Name Manager. Right? Well, not
quite. This is an example of the dialog

It is undoubtedly better than MS’ previous attempt. Seeing the names in a
resizable dialog, with the Refersto Value and the scope is good, but it still
falls far short of JKPs NameManager. It is cleaner than JKPs NameManager, but that is because it lacks so much. There is no option to evaluate a name, not
all names resolve to a single value, which is incredibly useful; no option to
highlight where names are used; no capability to redefine the scope of a name
(if you try in the Edit dialog, it tells you that the scope cannot be changed –
why?); changing a name’s name does not interact with VBA as NameManager does;
but worst of all, it seems totally oblivious to hidden names. (BTW, you can add comments to names in Excel 2007. I cannot
see where they appear, so fail to see their usefulness. Does anyone think this
is a good addition that they will use?).

All in all, the 2007 Name Manger is a big disappointment to
me, and JKP’s NameManger cannot be retired just yet. If you use names a lot, do
yourself a favour, rush out and buy a copy of JKPs NameManger today. You CAN
afford it, it is available here.

Perhaps JKP should rename it to ‘The Real NameManager’.

 

As Constant As The Wind

I had an odd problem with VBA today.

I used a table driven menu builder (don’t we all), and I define the column numbers of that table in an enumerated list.  This is that list

Private Enum CB_COLUMNS
    CB_LEVEL_COL = 1
    CB_CAPTION_COL = 2
    CB_POS_MACRO_COL = 3
    CB_TAG_COL = 4
    CB_PARAMETER_COL = 5
    CB_SHORTCUT_COL = 6
    CB_SPACES_COL = 7
    CB_DIVIDER_COL = 8
    CB_FACEID_COL = 9
    CB_VISIBLE_COL = 10
    CB_ENABLED_COL = 11
    CB_TYPE_COL = 12
    CB_DEBUG_COL = 13
End Enum

I was making some changes to the application today, and I suddenly got an error where the values in this list were being used, an error suggesting that a constant expression was required (sic!).

Very odd. How did I fix it? I changed the enum scope from Private to Public, and then my application compiled fine. I set it back to Private and it still compiled fine, and is working okay again.

Anyone else ever seen this?

Clear As Mud

Normal
0

false
false
false

MicrosoftInternetExplorer4


A question on one of the forums recently asked about
hiding sheets in a workbook to be posted as an example, but where that workbook contained sensitive information. As I replied, hiding the sheets doesn’t really
hide the data so he would be just as exposed.

Jimmy Pena has a a great application for generating
random data
, but sometimes it is just better to scramble the data that you already
have. I suggested to the poster that they should really be scrambling the data,
and further suggested that it should not be too difficult to write some code to
do so.

That got me thinking as to how to do it, in a generic
manner. It should cater for names, ids, amounts, currencies and so on.

A Simple Scrarmbling
App

My first thought was to insert a row at the top of the
target sheet, and add an entry for all columns to be obfuscated. So, for
instance, if it had ‘Name’ at the top, each entry in that column would be
changed to Name #1, Name #2 etc. If it had Number<7>, you would generate
a random number up to 9,999,999. If it had Decimal<5,2> you would
generate numbers up 99,999.99, and so on. If the value in row 1 was blank, that
column gets ignored.

So you would insert a row with values such as ‘Name’, ‘Number<7>’,
and so on.

The code for this was relatively simple to write, just a
loop through each column and change the values. To ensure that all like values
get changed to the same value, I did a Replace on the whole column once a value
to be changed was found. In other words, if Bob appeared 100 times in the
column, each instance of Bob would be changed to the same value. To ensure that
I didn’t then go and change the second, changed, instance of Bob to some new
value, I pre-pended the new value with ‘Obfuscate’, which I stripped off with a
Replace at the end.

The main loop was like this

            mpDataType
= .Cells(1, j).Value

            mpNextItem
= 0

       

            If
mpDataType <> “” Then

               
mpLastRow = .Cells(.Rows.Count, j).End(xlUp).Row

 

                For i
= 2 To mpLastRow


                    If
Not .Cells(i, j).Value Like “Obfuscated*” Then

                   

                       
mpNextItem = mpNextItem + 1

                       
Select Case True

                       

                           
Case mpDataType Like “Number<*”

                                ‘number
processing

                           

                           
Case mpDataType Like “Decimal<*”

                                ‘decimal processing

                       

                           
Case mpDataType Like “Currency<*”

                               ‘currency
processing

                               

                           
Case Else                                

                               
.Columns(j).Replace What:=.Cells(i, j).Value, _

                                                   
Replacement:=”Obfuscated” & mpDataType & ”
#” & mpNextItem, _

                                                   
LookAt:=xlWhole, _

                                                   
SearchOrder:=xlByRows

                       
End Select

                   
End If

                Next i

 

           
.UsedRange.Replace What:=”Obfuscated”, _

                          
Replacement:=””, _

                          
LookAt:=xlPart, _

                          
SearchOrder:=xlByRows

 

So far, so good, but I could see one major problem. If there
is a formula that refers to data somewhere else on the spreadsheet, that table
needs to be obfuscated too, but in a smart way. For instance, say that there is
a lookup table of names Bob, Simon and Alex. If there is a formula somewhere of
=VLOOKUP(A20,lookup_table,2,FALSE), and A20 is one of those values in the table,
then that value in the lookup table should change to the same value that A20
switches too. Unfortunately, it isn’t only VLOOKUP, it is LOOKUP, HLOOKUP,
COUNTIF, and so on. Tough!

It is actually worse if the values are used elsewhere a
simple cell reference or by a code event update, there is no way in my code to
recognise that.

Obfuscator

In the end, I decided to avoid this route, far too
difficult, and I opted to save all the before values, and all of after values in
separate arrays, and after having removed the ‘Obfuscated’ tag I went through
each sheet and checked if any of the before values still remained, if so I
replaced them.

A bit brute force, but it seems to work okay.

The data is first changed like so

 

                           
Case Else

                                mpIdxChange =
mpIdxChange + 1

                               
mpBefores(mpIdxChange) = .Cells(i, j).Value

                               
mpAfters(mpIdxChange) = mpDataType & ” #” & mpNextItem

                                If mpIdxChange
Mod 1000 = 0 Then

                           
    

                                    mpSizeArray
= UBound(mpBefores) + 1000

                                    ReDim
Preserve mpBefores(1 To mpSizeArray)

                                    ReDim
Preserve mpAfters(1 To mpSizeArray)

                                End If

                               

                               
.Columns(j).Replace What:=.Cells(i, j).Value, _

                                                   
Replacement:=”Obfuscated” & mpAfters(mpIdxChange), _

                                                    LookAt:=xlWhole,
_

                                                   
SearchOrder:=xlByRows

 And finally ‘corrected’
like so

 

        ‘now make the
changes for names, ids, etc.

        ReDim Preserve
mpBefores(1 To mpIdxChange)

        ReDim Preserve
mpAfters(1 To mpIdxChange)

       

        ‘first remove
the tag

       
.UsedRange.Replace What:=”Obfuscated”, _

                      
Replacement:=””, _

                      
LookAt:=xlPart, _

                      
SearchOrder:=xlByRows

                      

        For Each mpWS
In ActiveWorkbook.Worksheets

        

            ‘then
update any associated values

            For i = 1
To mpIdxChange

           

               
mpWS.UsedRange.Replace What:=mpBefores(i), _

                                      
Replacement:=mpAfters(i), _

                                      
LookAt:=xlWhole, _

                                      
SearchOrder:=xlByColumns

            Next i

        Next mpWS

       

       
.Rows(1).Delete

Of course, it can be taken a lot further, adding further
sophistication. For example, it doesn’t explicitly cater for an Excel
spreadsheet that is constructed as a pseudo-database, separate, linked tables. And it
probably needs a decent progress bar  as
it could take some time on large data.

An addin with the code and an example file can be downloaded
via the RSS feed. The addin adds an item to the Data menu.

 

Nostalgia IS What It Used To Be

Excel 2007 has had much comment since its introduction, most
of which has been centred around the ribbon, is it a piece of inspired insight
by MS, or a blunder of enormous proportions? This post will not concern itself
over that issue directly, but will take a look at how some have addressed the
introduction of the ribbon, seeing product opportunity. I am referring of
course to the advent of various applications that provide the old 2003 style
menus within Excel.

 

Over the next few weeks I am going to look at a number of
these applications, cover their main functionality, and say what I think of
them. I have to start by declaring a prejudice against such aplications, as I
believe that if you want classic menus, why not use classic Excel? But of
course, some may want the 1M+ rows, I don’t but some may, and yet
still crave the old style menus, so I guess these products have a place. And of
course, it might help the transition to Excel 2007. Personally I think it is
like smoking, either give up or carry on, trying to do it by stealth is
ultimately pointless.

 

The first Classic menu that I looked at was UBitMenu,
supplied by Ubit
Schweiz
. This can be found at http://www.ubit.ch/software/ubitmenu-languages/

 

Costs

The price for UBitmenu is reasonable, free for private
use, and € 10 base fee +  € 0.65 per user
for commercial use (+ VAT if applicable).

 

Installation

UBitMenu can be installed with standard user rights on any
Windows® Office 2007 / Office 2010 environment. The suppliers suggest that you
may need to save the file to a trusted location on your hard disk before you
run the setup. I had no need to do so.

 

The setup application installs UBitMenu
AddIn-files for
Excel, Word and PowerPoint. I had expected different menus for Excel
and Word and so  on, but oddly on my system, the Word menu was the same
Excel menu. I don’t know if I did something incorrectly, not being a
big Word or PowerPoint user I was not too concerned with it. All changes are registered for uninstallation.

[Update – it has been pointed out to me that they are not the same, they are just very similar, which was a deliberate choice. My only excuse is that I saw things in Word that are not on my Word 2003 toolbars, such as the charting icon, but as Ubit Schweiz seem to have done this as something that would be value added for most users, and it is intended, I will stop digging and accept my fate].

 

UBitMenu is a simple Excel 2007 addin, with the menu items
defined in the CustomUI XML. You can view the XML using CustomUI, and can see
that it just invokes the builtin functions within Excel 2007.

 

When installed, there is a new ribbon tab added called Menu
(why not UBitMenu? [Update – I am told this is to restrict space encroachement in restricted situations, such as a laptop, which given the space grabbing proclivity of the Ribbon, I guess I should applaud this]) which looks like the classic Excel 2003 menu,
Standard and Formatting toolbars, with the Drawing toolbar thrown in
for good measure.

 

One aspect of the installation is unusual is that it installed
into my XLSTART directory, it did not give me the option to direct its
placement.

 

Usage

The tool is very simple to use, works well, and does exactly
what the suppliers suggest. It cannot be customised as Excel 2003 commandbars
can, it is simply a means to use a familiar format within an unfamiliar
environment, a transition tool until one is comfortable with the ribbon.

 

Uninstalling

The suppliers suggest that you uninstall using the
‘Software’ applet in the Windows Control Panel, although as it is just an
addin, you can uninstall it using the Addins dialog, and then delete the file.
Any further selection in the addins dialog throws up a message that allows you
to remove it from the list.

 

In Summary

This may be a good option for corporates that are looking to
install Excel 2007, but are concerned with the effect that the ribbon may have
on their user productivity, as it allows a smoother transition, at a small cost to the potential productivity loss. Its ease of deployment should not create any logistical problems.

A personal user may also find it useful in transitioning to Excel 2007, especially as it is free for personal use.

It is not for the power user who wants to continue
using
classic menus in an Excel 2007 world as it does not support commandbar
customisation. That would require delving into XML, which defeats the
point somewhat.