Joining Two Dimensional Arrays

The Join function takes an array and smushes it together into a String. I love the Join function. The only thing I don’t like about it is when I forget that it doesn’t work on 2d arrays. Join only works with 1-dimensional arrays. The last time my memory failed me, I decided to write my own. And here it is.

Public Function Join2D(ByVal vArray As Variant, Optional ByVal sWordDelim As String = " ", Optional ByVal sLineDelim As String = vbNewLine) As String
   
    Dim i As Long, j As Long
    Dim aReturn() As String
    Dim aLine() As String
   
    ReDim aReturn(LBound(vArray, 1) To UBound(vArray, 1))
    ReDim aLine(LBound(vArray, 2) To UBound(vArray, 2))
   
    For i = LBound(vArray, 1) To UBound(vArray, 1)
        For j = LBound(vArray, 2) To UBound(vArray, 2)
            'Put the current line into a 1d array
            aLine(j) = vArray(i, j)
        Next j
        'Join the current line into a 1d array
        aReturn(i) = Join(aLine, sWordDelim)
    Next i
   
    Join2D = Join(aReturn, sLineDelim)
   
End Function

It’s pretty simple. It loops through the first dimension (the row dimension) and joins each line with sLineDelim. Inside that loop, it joins each element in the second dimension with sWordDelim. What this function doesn’t do is automatically insert itself into only the projects I want. That requires me to remember that I wrote it and where I put it. In reality, I’ll probably reinvent the wheel the next time I need it.

Here’s my extensive testing procedure.

Sub TEST_Join2d()
   
    Dim a(1 To 2, 1 To 2) As String
   
    a(1, 1) = "The"
    a(1, 2) = "Quick"
    a(2, 1) = "Brown"
    a(2, 2) = "Fox"
   
    Debug.Print Join2D(a)
    Debug.Print
    Debug.Print Join2D(a, ",")
    Debug.Print
    Debug.Print Join2D(a, , "|")
    Debug.Print
    Debug.Print Join2D(a, ";", "||")
   
End Sub

Super Bowl Analysis

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.

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

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.

  1. 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.
  2. 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.
  3. 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.
  4. Switch Windows: Used zero times. I Ctrl+Tab to switch windows and simply haven’t needed this.
  5. 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.

The Amsterdam Excel Summit 2015

Hi Excel lovers!

Last year we had a terrific Excel event in Amsterdam in May. This year we’re in for a repeat!

I have just opened registration for what is going to be the place to be for anyone Excel-minded. We have two days full of excellent subjects. An impression:

  • Three in-depth Power Query sessions
  • Two sessions on improving your spreadsheet quality
  • Two sessions on charting, making your life easier and enabling you to build charts you didn’t even know you could
  • Two sessions on pivot tables and formulas
  • A session on how to build UDFs

So why don’t you book your flights and hotels and join us on April 13th and 14th for an unsurpassed Excel experience!

Regards,

Jan Karel Pieterse

Inserting a Range into an Outlook Appointment

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

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

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,""))}, 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;"";"";"";"";"";"";""})

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

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.

First bug of the year

And it’s a funny one. Save all your work, then make up a simple datasource that has a formula in the header:

 
SomeFormula
 
 
Now, turn the Macro Recorder on, and while it’s recording, turn that datasource into an Excel Table. (I use the Ctrl T shortcut for that)
 
Create Table
 
 
Excel will warn you that the formula will be converted to static values. Click No.
 
Continue
 
 
Beeeeep.
 
Restarting

Happens for me using Office 365. Pretty obscure, granted.