Super Bowl Analysis

Posted on January 26, 2015May 10, 2024 by Dick Kusleika

Every so often my worlds collide like when a football game is so popular that non-football fans are aware of it. This week a bunch of nerds will use Excel to analyze the game and I don’t want to be left out. I’ve isolated what I believe to be the most important factor and data-analyzed the hell out of it.

I think we can all appreciate that the 3D effects really drive the point home. And did you notice the use of color? I know. I’m a genius. Play your favorite games and stand a chance to win incredible prizes at เล่นและลุ้นรางวัลที่ UFABET, where excitement and rewards go hand in hand.

My favorite talking head quote about Super XLIX:

For many people in my family, the advertising shown during the Super Bowl provides as much or more entertainment than the game itself.

You mean a bunch of people only care about the ads? What an insightful thought – had you had it in 1975.

Quick Access Toolbar Usage Update

Posted on January 23, 2015January 19, 2015 by Dick Kusleika

Ever since the great QAT Usage Survey of last year, I’ve had five controls on my QAT. Up from zero. Here’s how they’ve fared.

Borders: Used zero times. I have my two most common border situations (single underline and grand total) available elsewhere. I guess I don’t muck around with borders as much as I thought.
Text to Columns: Used zero times. I use TTC all the time; sometimes one of my own invention, but equally the built-in kind. The built-in kind is good when I have a lot of rows or if I’m tyring to convert a bunch of numbers into text. But I use Alt+A+E without even thinking, so this button has been lonely.
Connections: Used exclusively. Well, kind of. I always start navigating the Ribbon before I remember I have a QAT at all. Then I back out of the Ribbon and Alt+3 to show the external data connections. I think the last couple times I’ve remembered so it’s possible I’ve turned a corner.
Switch Windows: Used zero times. I Ctrl+Tab to switch windows and simply haven’t needed this.
Table Name: Used exclusively. It didn’t take too long to wean myself from Alt+JT+A to Alt+5. This has been the biggest success.

The QAT has awesome Alt+n shortcuts that I need to use. I just don’t know what to put on there. I use Borders and Switch Windows far less than I thought. I need to find some that I’ll actually use.

Inserting a Range into an Outlook Appointment

Posted on January 21, 2015January 17, 2015 by Dick Kusleika

Jesse asks:

In VBA, how do I add a range of cells to the body of an appointment?

Unlike email, the AppointmentItem does not have an HTMLBody property. If it did, then I would convert the range to HTML and use that property. Formatted text in the body of an AppointmentItem is Rich Text Format (RTF). I don’t know of any good ways to convert a range to RTF. Sure, you could learn what all the RTF codes are and build the string to put into the RTFBody property of the AppointmentItem. Then you could go to the dentist for a no-novocaine root canal. I’m not sure which of those would be more fun.

A better way is to programmatically copy the range and paste it into the body of the appointment. Since Office 2007, almost every Outlook object allows you to compose in Word. That’s an option I quickly turn off, but it’s still there under the hood. We’ll use that to our advantage. But first, let’s set up some data. Here I have a Table in Excel with some sample data.

To create a new appointment with this range in the body, I used this code:

Sub MakeApptWithRangeBody()
    
    Dim olApp As Outlook.Application
    Dim olApt As Outlook.AppointmentItem
    
    Const wdPASTERTF As Long = 1
    
    Set olApp = Outlook.Application
    Set olApt = olApp.CreateItem(olAppointmentItem)
    
    With olApt
        .Start = Now + 1
        .End = Now + 1.2
        .Subject = "Test Appointment"
        Sheet1.ListObjects(1).Range.Copy
        .Display
        .GetInspector.WordEditor.Windows(1).Selection.PasteAndFormat wdPASTERTF
    End With
    
End Sub

Sub MakeApptWithRangeBody()

Dim olApp As Outlook.Application

Dim olApt As Outlook.AppointmentItem

Const wdPASTERTF As Long = 1

Set olApp = Outlook.Application

Set olApt = olApp.CreateItem(olAppointmentItem)

With olApt

.Start = Now + 1

.End = Now + 1.2

.Subject = "Test Appointment"

Sheet1.ListObjects(1).Range.Copy

.Display

.GetInspector.WordEditor.Windows(1).Selection.PasteAndFormat wdPASTERTF

End With

End Sub

Be sure to set a reference to the Microsoft Outlook 1x.x Object Library (VBE – Tools – References). The code produces this happy customer.

The code creates an appointment and fills in some properties, like Start, End, and Subject. The Excel Table is copied to the clipboard ready to be pasted into the appointment. Before we can get to the AppointmentItem’s WordEditor, we have to display it. That’s why the .Display method comes before the paste operation.

The last bit is to paste the range. Starting with an AppointmentItem, we have to get the Inspector object, then the WordEditor object, then a Window object, and finally we can use the PasteAndFormat method on the Selection object. Gool ol’ Word where everything is a Selection object.

This code simply displays the appointment, you will need the .Save method, .Close method, or .Send method if you want to automate any of that.

Finding the Earliest Time by Day

Posted on January 19, 2015January 17, 2015 by Dick Kusleika

A reader needs to find the difference between the time listed and the earliest time listed for that same day. Here’s the data:

Date	Time	Difference
6/9/2014	14:49:05	0:00:00
6/9/2014	14:49:47	0:00:42
6/9/2014	14:50:33	0:01:28
6/9/2014	14:51:17	0:02:12
6/9/2014	14:51:31	0:02:26
6/9/2014	14:51:56	0:02:51
7/9/2014	6:19:55	0:00:00
7/9/2014	6:21:09	0:01:14
7/9/2014	6:21:31	0:01:36
7/9/2014	6:22:25	0:02:30
7/9/2014	6:22:53	0:02:58
7/9/2014	6:23:23	0:03:28
7/9/2014	6:23:47	0:03:52

The formula in the Difference column, C2, is

{=B2-MIN(IF($A$2:$A$14=A2,$B$2:$B$14,""))}

1	{=B2-MIN(IF($A$2:$A$14=A2,$B$2:$B$14,""))}

, filled down to fit the data.

It’s an array formula, so don’t type the curly braces, but enter with Ctrl+Shift+Enter, not just enter. The array part of the formula, the part subtracted from B2, is the smallest value where the date in column A is a match. By selecting everything in the MIN function in the formula bar and pressing Ctrl+=, you can see how Excel is calculating the minimum.

=B2-MIN({0.617418981481482;0.617905092592593;0.6184375;0.618946759259259;0.619108796296296;0.619398148148148;"";"";"";"";"";"";""})

1	=B2-MIN({0.617418981481482;0.617905092592593;0.6184375;0.618946759259259;0.619108796296296;0.619398148148148;"";"";"";"";"";"";""})

Because we’re dealing with times, the numbers aren’t so easy to read. But the important part is at the end of the array – a bunch of empty strings. When the date doesn’t match, the IF function returns an empty string. MIN ignores any text, so only the smallest of the numbers listed is returned.

How Not to Learn Spanish

Posted on January 16, 2015January 15, 2015 by Dick Kusleika

I’ve been trying to learn Spanish for almost 20 years. Of the seven words I know, four are swear words. So when I tell you how not to learn Spanish, you can take it to the bank.

I signed up for Carlos Muñiz’s RSS feed. It’s in Spanish. Every so often I open the next post and see if I can translate. I’m not very good. I’m also not sure I’m learning anything, but it’s kind of fun like a puzzle. Once I’ve completed my translation (with plenty of guessing), I go to translate.google.com and see how I did.

Post 4

Title: Escribir metros cuadrados con subíndice

DK: To write meters words with subscript

Google: Write square meters with subscript.

I’d like to think I would have got subíndice, but actually the picture helped me out. It didn’t help enough that I thought to look at what was in the cell, because I probably could have got cuadrados instead of punting and guessing it was something related to ‘word’. ‘Word’ is ‘palabra’. I told you my Spanish sucked.

Body: Para escribir metros cuadrados con subíndice es muy fácil del texto en la celda seleccionar sólo el número 2, pulsar click derecho del “mouse” ir a formato de celdas y en efectos seleccionar subíndice.

DK: To write square meters in subscript is very easy. In the cell select only the number two, click the right mouse button to go to cell formats and select subscript.

Google: To write sqm with subscript text easily select only the cell number 2, press right click the “mouse” go to format cells and select subscript effects.

I’m always inserting punctuation into Carlos’ sentences instead of trying to figure out what it really says – my ‘is very easy’ should be ‘easily’. And I glossed over ‘en efectos’ because I didn’t know what it meant. Now I see that the frame in the dialog is captioned Effects.

The posts are a reasonable length so it doesn’t take an hour to translate. And there’s usually a picture which provides some valuable clues. Plus I have a little domain knowledge, which I’m sure doesn’t hurt.

What Is the Most Awesome Microsoft Product

Posted on January 13, 2015 by Dick Kusleika

As if you didn’t know.

As the people talked, we started to get a sense for why they thought Excel is cool – Excel enables people to be rockstars at their jobs and get recognition from their peers. Excel makes people feel powerful. Excel is a work tool that people *love* to use.

You can also read the rest of the answers. Lots of OneNote and Kinnect. Even a Visio and a Minesweeper.

Finding the Next Row in a ListObject in VBA

Posted on January 9, 2015 by Dick Kusleika

ListObjects (Tables in Excel’s UI) are structured ranges. I use them constantly. I love the built-in named ranges and referring to them in VBA without a lot of hullabaloo. It’s as close to a database as you’re going to get in Excel. Recently I decided to automate a process of adding some payroll records to the end of a table. If I were using just a range, I would find the next available row like

Set rStart = wshSalaries.Cells(wshSalaries.Rows.Count,1).End(xlUp).Offset(1,0)

1	Set rStart = wshSalaries.Cells(wshSalaries.Rows.Count,1).End(xlUp).Offset(1,0)

That works most of the time for ListObjects too. It returns the row right below the last row of the ListObject. In most cases, when you add some data to that row, the ListObject expands. In the case where there is no data in the ListObject and there is only a blank row, however, it doesn’t work. The ListObject doesn’t expand, and even if it did, you would have a blank row.

The ListObject object has a InsertRowRange property that returns a Range object. When a ListObject has no data, it has a header row and a blank row[1] ready to accept data.

When you enter something into that row, it doesn’t give you a new insert row, it just sits there.

When I’m trying to write something to the end of a ListObject, I test to see if InsertRowRange is nothing[1]. Here’s a snippet

Set lo = wshSalaries.ListObjects(1)
If lo.InsertRowRange Is Nothing Then
    Set rStart = lo.HeaderRowRange.Cells(1).Offset(lo.ListRows.Count + 1)
Else
    Set rStart = lo.InsertRowRange.Cells(1)
End If

Set lo = wshSalaries.ListObjects(1)

If lo.InsertRowRange Is Nothing Then

Set rStart = lo.HeaderRowRange.Cells(1).Offset(lo.ListRows.Count + 1)

Else

Set rStart = lo.InsertRowRange.Cells(1)

End If

If InsertRowRange is Nothing, then table isn’t empty and I offset down however many rows there are plus one. The old method of End(xlup) works in this situation too. I don’t find top down better or worse than bottom up, so use whatever you like. If InsertRowRange isn’t Nothing, that means there’s no data in the table. In that case, I can insert starting in InsertRowRange.

Here’s the whole procedure, if you’re looking for context.

Public Sub ProcessWageFile()
    
    Dim clsEmployees As CEmployees
    Dim clsActives As CEmployees
    Dim clsEmployee As CEmployee
    Dim aOutput() As Variant
    Dim lCnt As Long
    Dim lo As ListObject
    Dim rStart As Range
    
    Set clsEmployees = New CEmployees
    clsEmployees.FillFromRange wshEmployee.ListObjects(1).DataBodyRange
    clsEmployees.FillCompsFromRange ActiveSheet.UsedRange.Offset(1)
    Set clsActives = clsEmployees.FilterByActive(True).FilterByHasComps
    
    ReDim aOutput(1 To clsActives.Count, 1 To 5)
    
    For Each clsEmployee In clsActives
        lCnt = lCnt + 1
        aOutput(lCnt, 1) = clsEmployee.FullName
        aOutput(lCnt, 2) = clsEmployee.Comps.Period
        aOutput(lCnt, 3) = clsEmployee.Comps.TotalWages
        aOutput(lCnt, 4) = clsEmployee.TotalBenes
        aOutput(lCnt, 5) = clsEmployee.Comps.TotalTaxes
    Next clsEmployee
    
    Set lo = wshSalaries.ListObjects(1)
    If lo.InsertRowRange Is Nothing Then
        Set rStart = lo.HeaderRowRange.Cells(1).Offset(lo.ListRows.Count + 1)
    Else
        Set rStart = lo.InsertRowRange.Cells(1)
    End If
    
    rStart.Resize(UBound(aOutput, 1), UBound(aOutput, 2)).Value = aOutput
    
End Sub

Public Sub ProcessWageFile()

Dim clsEmployees As CEmployees

Dim clsActives As CEmployees

Dim clsEmployee As CEmployee

Dim aOutput() As Variant

Dim lCnt As Long

Dim lo As ListObject

Dim rStart As Range

Set clsEmployees = New CEmployees

clsEmployees.FillFromRange wshEmployee.ListObjects(1).DataBodyRange

clsEmployees.FillCompsFromRange ActiveSheet.UsedRange.Offset(1)

Set clsActives = clsEmployees.FilterByActive(True).FilterByHasComps

ReDim aOutput(1 To clsActives.Count, 1 To 5)

For Each clsEmployee In clsActives

lCnt = lCnt + 1

aOutput(lCnt, 1) = clsEmployee.FullName

aOutput(lCnt, 2) = clsEmployee.Comps.Period

aOutput(lCnt, 3) = clsEmployee.Comps.TotalWages

aOutput(lCnt, 4) = clsEmployee.TotalBenes

aOutput(lCnt, 5) = clsEmployee.Comps.TotalTaxes

Next clsEmployee

Set lo = wshSalaries.ListObjects(1)

If lo.InsertRowRange Is Nothing Then

Set rStart = lo.HeaderRowRange.Cells(1).Offset(lo.ListRows.Count + 1)

Else

Set rStart = lo.InsertRowRange.Cells(1)

End If

rStart.Resize(UBound(aOutput, 1), UBound(aOutput, 2)).Value = aOutput

End Sub

[1]: Now you get the disclaimer. There’s a lot you can do with Tables in Excel. You can have a header row or now header row. You can have a totals row or not. And you can have a bunch of other stuff that makes this code not work. I use Tables a lot from a UI perspective and sometimes I have various features on or off. But the way I’m using a ListObject in this example is as a datastore. It’s not meant to be messed with – only for the VBA to read from and write to. In those cases, I make the Table the only thing on the sheet, it always has a header, and it never has a total row. If you want to use Tables differently, you’ll have to modify the code to accommodate the differences.

Tidying Up the Project Explorer

Posted on January 8, 2015January 6, 2015 by Dick Kusleika

You know that pressing Ctrl+R in the VBE takes you to the Project Explorer. Of course you do. I’m not going to sit here and insult your intelligence by pretending that you don’t. But that means you are probably also just as confused as I am about which projects the VBE “chooses” to expand and which stay closed.

I have a lot of add-ins and that makes my Project Explorer very messy. Ideally I want the active project to be expanded and everything else collapsed. But I can’t have that. I decided I’ve had enough and added the following hotkey to my AutoHotKey VBE.ahk file.

^+r:: Send ^r Send {End} Loop 20 { Send {Left 2}{Up} }

When I’m in the VBE (an IfWinActive condition in VBE.ahk) and press Ctrl+Shift+R, the focus goes to the Project Explorer, goes to the last project, then closes up to 20 of them. Two left arrows will collapse an expanded project and has no effect on a collapsed project. The up arrow goes to the next project. Genius, you say? I humbly agree.

I don’t keep my modules in Folders (Toggle folders using the button at the top of the Project Explorer). If you do, you’ll need to change the {Left 2} to {Left 4}.

Daily Dose of Excel

Haphazardly Posted Excel Information and Other Stuff

Author: Dick Kusleika