Nick Hodge's Excel Blog

COUNTIF SUMIF Limitations

2008-06-27T18:05:00Z

I'm sure many of you knew this but it was new to me and I came across it today, so in an effort to get it out there a bit more, i document it here.

It concerns the fact that COUNTIF and SUMIF (and their 2007 plural counterparts SUMIFS and COUNTIFS) carry forward the limitation that Excel has of 15 significant digits. (As does 1-2-3 and OpenOffice)

http://support.microsoft.com/kb/269370

I knew this limitation, but didn't realise the two functions concerned followed this limit'

I had to build an automated reconciliation model to delete off matching records. It operates through several iterations but my first one was to concatenate Date, Customer Number and Credit/Debit amount and then do a COUNTIF on that 'Key'. If this resulted in 2, then I could presume a 'match' and delete the record.

Imagine this then. I have data as the screenshot below

In D3 is the formula =A3&B3&C3
In E3 (as you can see) is the formula =COUNTIF(D:D,D3)
In F3 is effectively the same formula as in E3 but using the 2007 function COUNTIFS() (using just one criteria)
In G3 is the formula =SUMIF(D:D,D3,C:C)
In H3 is the formula =SUMIFS(C:C,D:D,D3) (Notice how in this 2007 version the parameter order is changed)
In I3 is the formula =SUMPRODUCT(--($D$3:$D$4=D3))
In J3 is the formula =SUMPRODUCT(($D$3:$D$4=D3)*($C$3:$C$4))

What COUNTIF and SUMIF (and their plural Excel 2007 equivalents) are seeing is a string in both cells (D3 and D4) that are the same as they are just taking the first 15 digits (200806101234561). Plainly, in my scenario this is incorrect. SUMPRODUCT however sees all the digits and therefore overcomes this limitation. Correctly seeing a COUNT of 1 each and a SUM of just the single values.

For a complete explanation of SUMPRODUCT you could do worse than look here

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

BTW: As a sideline, the formula using SUMPRODUCT worked about twice as fast

Summit in Seattle

2008-04-14T12:45:00Z

It's been some time since I posted a blog post and this is a little lame when it comes to Excel, but we are all (MVPs) in Seattle at the Global Summit this week and if there is anything we are allowed to post under our NDAs I will try and post it here.

Never know until we get there what will be allowed public domain and we have to be real careful, but I will certainly be pointing the Excel Team to the v.Next forum if you still have some ideas for that.

Looking forward to a great week at the 'Excel coal face'

Playing with OpenAsTextStream

2008-03-13T19:35:00Z

We produce individual text files for new items to go into our catalogue. These are sent to the creative people for laying out on the page. One file contains the Item#, Description, Text and Bullets for one item and is named with that item's number. Of course, for page layout it is good for them to copy and paste the text this way. It looks as below:

12345E

2500PSI 15LPM Petrol-Powered Pressure Washer

This pressure washer, powered by a Honda GX160 engine, will blast away even the most stubborn dirt with ease. Supplied with reinforced rubber hose and lance with four interchangeable nozzles.
* 2500PSI
* 15 LPM
* Comet Axial pump
* Downstream Chemical Injector
* Ship Wt: 68kg

Of course, when it comes to the website, we need a much more formal record/field (row/column) structure. The challenge today was to parse the files (iterating over a folder containing the files), which also have random blank lines into a sensible structure in Excel for uploading onto the web. Whilst not in my job description, it was a challenge I wanted to take as it was a long time since I worked with text files and I don't think I've ever worked with the FileSystem object from the Microsoft Scripting Runtime Library.

Of course, with any parsing job, the data is never straightforward. I've already mentioned the blank lines, but they also wanted the * changed to <li> to create an html bullet and the Ship Wt: line stripped off as that has it's own field. The split was across three rows, containing Item#, Item Title and Web Text.

When coding I always prefer to use early binding, where you manually set a reference via the VBE to the library you want to use, in this case the Microsoft Scripting Runtime. I do this as it gives you the advantage of intellisense if you are not sure of the object, methods, etc. So my code at development time would look like this

Sub ParseFile()
Dim fsys As FileSystemObject, fsfile As File, stream As TextStream

    'Set FileSystemObject variables (Early binding)
    Set fsys = New FileSystemObject
    Set fsfile = fsys.GetFile(sFolderName & sParseFile)
    Set stream = fsfile.OpenAsTextStream(1, 0)

End Sub

This way, just typing fsys. in the future code will fire up the properties, methods, etc of the FileSystemObject, very handy when you don't know them. Generally though I find deploying early binding is a pain, as you have to remember to set the reference on each machine before running the code, so at runtime I change the variable types to generic 'Objects' and then use the CreatObject function to create the FileSytemObject as below

Sub ParseFile()
Dim fsys As Object, fsfile As Object, stream As Object

    'Set FileSystemObject variables (Late binding)
    Set fsys = CreateObject("Scripting.FileSystemObject")
    Set fsfile = fsys.GetFile(sFolderName & sParseFile)
    Set stream = fsfile.OpenAsTextStream(1, 0)

End Sub

The OpenAsTextStream method of the File object is very nice to use, you can either read or write the file, you can check each character, complete lines or all the text at once. You get AtEndOfLine and AtEndOfStream boolean properties of the stream object so you can process until one and/or both of these properties are true.

Now I'm making no claims for quality of coding, but the below is the full, deployed code that works for the files we produce. In testing too, it parsed the data from 150 txt files into the correct columns and rows in just a few seconds, much faster than the old copy and paste!

Option Explicit

Sub ParseFile()
'**************************************************************************
' Code written by Nick Hodge 12th March 2008
'V1.0 into production same day. versions below.
'V1.1 added error handling and check for file name ending in 'E' (13/3/2007)
'V1.2 added checking for * as first character of line and change it to <li>
'for an html bullet, also strip out line starting Ship Wt. (13/3/2007)
'**************************************************************************

'Navigate to a folder and the code will iterate over any text files taking the first
'line with text in it and assigning it to the strLine1 variable. It does the same for
'second and third lines and then assigns them to columns 1, 2, 3 in a new workbook
'laying them out in proper field/record format for importing to web
'File format should have item number as first line (no matter if blank lines above
'Product title on second line, no matter if blanks above and rest of copy after that

'File and folder name variables
Dim sFullPathFilename As String, sFolderName As String, sParseFile As String
'New workbook and worksheet variables
Dim wb As Workbook, wks As Worksheet
'Counter to count iterations, used in row placement on sheet and counter for postion
'of slash in path name (before filename)
Dim lCounter As Long, lSlashPosition As Long, iwksCount As Integer
'Filesytem object variables to hold file, textstream, etc.
Dim fsys As FileSystemObject, fsfile As File, stream As TextStream
'Variable to hold lines of text for later assignment to worksheet
Dim strLine1 As String, strLine2 As String, strLine3 As String, strTemp As String
'Booleans to stop loop after line 1 found, etc.
Dim bLine1Fnd As Boolean, bLine2Fnd As Boolean

On Error GoTo errHandler

'Get folder to use by clicking on file
sFullPathFilename = Application.GetOpenFilename("Text Files, *.txt", , "Select any file in the folder")

'Check to see selection made
If sFullPathFilename = "False" Then
    MsgBox "You must click on any file in the folder", vbOKOnly, "No Folder Selected"
    Exit Sub
End If

'Get position of last slash and parse folder from this
lSlashPosition = InStrRev(sFullPathFilename, "\", -1)
If lSlashPosition = 0 Then
    MsgBox "We cannot recognise the folder name, exiting procedure", vbOKOnly + vbExclamation, "Error"
    Exit Sub
End If

'Open workbook and set variables
Set wb = Workbooks.Add
Set wks = wb.Worksheets("Sheet1")

'Change sheet name
wks.Name = "Data"

'Switch off application events
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
   
'Delete surplus worksheets
With wb
    For iwksCount = .Worksheets.Count To 1 Step -1
        If .Worksheets(iwksCount).Name <> "Data" Then
        .Worksheets(iwksCount).Delete
        End If
    Next iwksCount
End With

'Set headings and bold
With wks
    .Range("A1").Value = "ItemNo"
    .Range("B1").Value = "Title"
    .Range("C1").Value = "WebText"
    .Rows("1:1").Font.Bold = True
End With

sFolderName = Left(sFullPathFilename, lSlashPosition)

'Start iteration of folder using folder name and files ending in E.txt
sParseFile = Dir(sFolderName & "*E.txt")

'Intialise counter to 2 which starts it on second row, below titles
lCounter = 2

'Do for each file Dir function is called recursively and returns "" when no more files match
Do While sParseFile <> ""

'Initialise booleans
bLine1Fnd = False
bLine2Fnd = False

    'Set FileSystemObject variables (Late binding)
    Set fsys = CreateObject("Scripting.FileSystemObject")
    Set fsfile = fsys.GetFile(sFolderName & sParseFile)
    Set stream = fsfile.OpenAsTextStream(1, 0)

    'Do so long as the endoffile marker is false, sets first line of text
    'Accounts for blank lines
    Do While stream.AtEndOfStream = False
            Do While bLine1Fnd = False
            strTemp = stream.ReadLine
                If strTemp <> "" Then
                    strLine1 = strTemp
                    bLine1Fnd = True
                    strTemp = ""
                End If
            Loop
            'sets second line of text, accounts for blank lines
            Do While bLine2Fnd = False
            strTemp = stream.ReadLine
                If strTemp <> "" Then
                    strLine2 = strTemp
                    bLine2Fnd = True
                    strTemp = ""
                End If
            Loop
            'Once 1st and 2nd lines are full then take averything else, line by line
            strTemp = stream.ReadLine
            'Check if line is ship wt., if so discard
                If Left(UCase(strTemp), 6) = "* SHIP" Then strTemp = ""
                'Build 3rd lines changing * to <li> for html bullet, else just add the next line to the copy
                If strTemp <> "" Then
                    If Left(strTemp, 1) = "*" Then
                        strTemp = "<li>" & Mid(strTemp, 2, Len(strTemp) - 1)
                        strLine3 = strLine3 & strTemp & Chr(10)
                    Else
                        strLine3 = strLine3 & strTemp & Chr(10)
                    End If
                End If
                
    Loop
    
    'Close stream and release object variables
    stream.Close

    Set stream = Nothing
    Set fsfile = Nothing
    Set fsys = Nothing
    
    'Assign the strings to worksheet
    With wks
        .Cells(lCounter, 1).Value = strLine1
        .Cells(lCounter, 2).Value = strLine2
        .Cells(lCounter, 3).Value = Left(strLine3, Len(strLine3) - 1)
        .Columns.AutoFit
        .Columns(3).ColumnWidth = 200
        .Rows.AutoFit
    End With
    
    'Clear strings for next iteration
    strLine1 = ""
    strLine2 = ""
    strLine3 = ""
    
    'Increment counter so text is placed on next row
    lCounter = lCounter + 1

    'Start next iteration of files in folder
    sParseFile = Dir()

Loop
    
    'Turn application events back on
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

MsgBox " Successfully parsed " & lCounter - 2 & " *.txt files" & Chr(13) & "From " & sFolderName, _
    vbInformation + vbOKOnly, "Parsed Successfully"

Exit Sub

'Error handling routine to exit gracefully
errHandler:

MsgBox "There has been an error, please advise add-in creator" & Chr(13) & _
    "The routine failed on: " & sParseFile, vbOKOnly + vbCritical

If Not wb Is Nothing Then
    wb.Close SaveChanges:=False
End If

With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
End With

Exit Sub

End Sub

If you want to download and try the file (Excel 2007 Only) I have attached the working xlsm file to this post (It is actually deployed as an xlam add-in) and 20 demo text files. Just place the text files in a folder and then go to the Marketing tab (Yes, I even wrote a ribbon customisation for it!) and press the button. It will ask you to navigate to any file in the folder and the will take each file in turn using the Dir() function and parse the text into a new Excel workbook.

Questions on customising the ribbon

2008-03-02T10:52:00Z

I have, via the blog contact form received three questions from Milan, these were

Can you remove the Office Button?
Can you link a combobox (on the ribbon) to a cell/range?
Can you link a label (on the ribbon) to a cell?

The answer to No.1 was answered in the 2nd of my series on customising the ribbon. In the section under Full Dictator Application.

No. 2 is yes you very much can but it is not a simple process like having a 'link' property or similar. The write-up below demonstrates how you would take a range of cells (BoxList @ $A$!:$A$10) to fill the combobox and then display the selected item in a cell on the worksheet ($D$1).

A comboBox has a DropDown (so is similar to a dropDown ribbon control), but allows extra entries to be typed in at runtime.

The first thing we need to do is set up the RibbonX in the file (for detail, see here). For this demo I am setting startFromScratch="true", so all we should have from the below RibbonX is a single tab, with one group and one comboBox. Notice too, that at this stage the comboBox is empty. This is because we will be building this when the workbook opens, using a Workbook_Open() event

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="true"> 
        <tabs> 
            <tab id="myTab1" label="Demo Tab" visible="true">
                    <group id="Group1" label="Combobox" visible="true">
                    <comboBox id="Combobox1" label="Cell Link" getItemID="cmb_getItemID"
                        getItemLabel="cmb_getItemLabel" getItemCount="cmb_itemCount" 
                            onChange="cmb_onChange"/>
                    </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

Notice that we are using four 'callback' attributes to populate the comboBox and to 'link' it to a cell. These are used for the following reasons:

getItemID() = Used to 'set' the unique ID necessary for every control on the ribbon (It is tough to think that the names 'getxxx' are actually better though of as 'set'. It is meaning the Ribbon needs to 'get' rather than you need to 'set' if that makes sense?)
getItemLabel() = Used to 'set' the text of each item displayed in the control
getItemCount() = Used to 'set' the number of items the control should expect to display.
onChange()= Used to provide the 'link' to a cell on the worksheet. This fires each time the control is changed.

Next we generate the callback signatures for these by pressing the button in the Custom UI Editor. You will notice below that the UI editor does not generate a getItemID callback. this is a bug, the signature is given below to help you.

'Callback for Combobox1 getItemID (NOT GIVEN BY CALLBACK GENERATOR)
Sub cmb_getItemID(control As IRibbonControl, index As Integer, ByRef ID)
End Sub

'Callback for Combobox1 getItemLabel
Sub cmb_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
End Sub

'Callback for Combobox1 getItemCount
Sub cmb_itemCount(control As IRibbonControl, ByRef returnedVal)
End Sub

'Callback for Combobox1 onChange
Sub cmb_onChange(control As IRibbonControl, text As String)
End Sub

We will use a Workbook_Open() event to build the array of items to load into the control. This event, as the name implies, fires when the workbook is opened. It is placed in the 'ThisWorkbook" module.

Option Explicit
'There are two global variables at the top of a standard module
'iItemcount and vRngValues

Private Sub Workbook_Open()
'Counter variable
Dim x As Integer
'Counts the cells in the range, used later to set no. of Items in control.
iItemcount = Worksheets("Sheet1").Range("BoxList").Cells.Count - 1
'Re-Dimension the dynamic array to it's actual size.
ReDim vRngValues(iItemcount)
'Build the array
For x = 0 To iItemcount
    vRngValues(x) = Worksheets("Sheet1").Cells(1 + x, 1).Value
Next x
End Sub

Now we will have the range 'BoxList' assigned to a one-dimensional array and the count of items, in public variables available to all code in the project. (remember this for later)

First let's advise the control how many items we will need in the control, using the global integer variable iItemcount (built in the Workbook_Open() event) and the getItemCount() callback, as below. (The comments explain what the procedure does, in fact we are using just one line of code!). These callbacks are all placed in a standard module.

'Callback for Combobox1 getItemCount
Sub cmb_itemCount(control As IRibbonControl, ByRef returnedVal)
'Use the global variable collected in the Workbook_Open()
'assigning it to the returnedVal parameter
    returnedVal = iItemcount
End Sub

Next, as each item in the control (and the control itself for that matter) need a unique ID we will build this next using the getItemID callback, as below. Again the comments explain what is happening.

'Callback for Combobox1 getItemID
Sub cmb_getItemID(control As IRibbonControl, index As Integer, ByRef ID)
'Set the ID parameter in the callback signature to each 'index'
'in the signature. We are adding cmbBox text to make it unique
'as using just a number may clash with other controls/items
'indexes in controls are zero based
    ID = "cmbBox" & index
End Sub

Next we will write the last piece of code to actually build it (the fourth will be used as we use the control). This is the getItemLabel callback (below) that 'sets' the actual text that will be displayed in the combobox. (Again, just one line of code!)

'Callback for Combobox1 getItemLabel
Sub cmb_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
'Sets the returnVal parameter, which carries the text back to the control
'This fires for each control 'item', so the 'index' parameter
'provides a zero based index for the current item, which we use in the code
    returnedVal = vRngValues(index)
End Sub

Lastly, we will use the onChange() callback to assign the selected option from the comboBox, to a cell (D1) on our worksheet.

'Callback for Combobox1 onChange
Sub cmb_onChange(control As IRibbonControl, text As String)
'Set the 'text' parameter to a cell value
    Worksheets("Sheet1").Range("D1").Value = text
End Sub

That's all you need, job done! The image below shows the result.

Lastly, question No.3. I am hoping that the questioner was referring to the labelControl. This is used simply as a textual control that can act as a label to anything else on a group. If so then yes, all we need to do is set the control's getLabel attribute using it's callback. I have added the ribbonX to the previous example, (it's just one line) and the callback, added to a standard module is below that, which picks up the value from D2.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="true"> 
        <tabs> 
            <tab id="myTab1" label="Demo Tab" visible="true">
                    <group id="Group1" label="Combobox" visible="true">
                    <comboBox id="Combobox1" label="Cell Link" getItemID="cmb_getItemID"
                        getItemLabel="cmb_getItemLabel" getItemCount="cmb_itemCount" onChange="cmb_onChange"/>
                    <labelControl id="myLabel" getLabel="lbl_getLabel"/>
                    </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

The getLabel VBA callback

'Callback for myLabel getLabel
Sub lbl_getLabel(control As IRibbonControl, ByRef returnedVal)
'Assigns the value in cell D2 to the returnVal parameter
    returnedVal = Worksheets("Sheet1").Range("D2").Value
End Sub

The resulting label from cell D2

Hopefully this goes someway to answering the OP questions and helps to further everyone's understanding of customising the Ribbon in Office 2007.

More to come.

Maybe faulting add-ins are a thing of the past?

2008-02-22T09:12:00Z

I was intrigued to see, under the threat from competition commissions around the World, that Microsoft are opening up parts of their code-base and engineering to competitors to make the whole operating system and main applications (Office is mentioned), work better with 3rd party products.

I for one hope people like Adobe gets involved with this as a good proportion of the posts in the public GrashesGPFs group on MSFT's servers, relates to faulting add-ins opening with Excel.

Hopefully good news?

Here is the article from Reuters.

http://uk.reuters.com/article/technologyNews/idUKWNAS191120080222?feedType=nl&feedName=ukmorningdigest