A similar kind of VLOOKUP

Too lazy to record the macro to switch columns – VLOOKUP() requires the source column to be leftmost – I used INDEX and MATCH instead:

=INDEX('TableArray'!C:C, MATCH(B2, 'TableArray'!D:D))

MATCH() returns the row number and INDEX() returns the value of the intersection between the row number and the column. A neat trick.

Some people have been moving to other jobs. The biggest reason is the new company pays more. I wonder if it’s more for more effort or more for less?

Comparing dates between

Here is code to determine if a day falls between two specific date-times:

Sub Hello()

  Dim now_its
  Dim yest_was
  Dim row_ptr As Long

  now_its = Now
  yest_was = DateAdd("d", -7, Now)
  row_ptr = 2 ' skip header
  While Cells(row_ptr, 1) <> "" ' assumption: no blanks in 1st col
    Dim cur

    cur = Cells(row_ptr, 1)

    Cells(row_ptr, 2) = DateDiff("d", yest_was, cur)
    Cells(row_ptr, 3) = DateDiff("d", now_its, cur)
    Cells(row_ptr, 4) = DateDiff("h", yest_was, cur)
    Cells(row_ptr, 5) = yest_was
    Cells(row_ptr, 6) = now_its
    is_after_yesterday = False
    is_before_cur = False
    If DateDiff("d", yest_was, cur) = 0 And _
      DateDiff("h", yest_was, cur) >= 0 Then
        is_after_yesterday = True
    ElseIf DateDiff("d", yest_was, cur) > 0 Then
        is_after_yesterday = True
    End If
    If DateDiff("d", now_its, cur) = 0 And _
      DateDiff("h", now_its, cur) <= 0 Then
        is_before_cur = True
    ElseIf DateDiff("d", now_its, cur) < 0 Then
        is_before_cur = True
    End If
    If is_after_yesterday And is_before_cur Then
        Cells(row_ptr, 8) = "IS BETWEEN!"
        Cells(row_ptr, 8) = "NOT IS BETWEEN"
    End If

    row_ptr = row_ptr + 1
End Sub

Sample output:


Edit: a working version with more granularity and test output.

Rules-processing template

Here’s one approach to writing up rules for a VBA macro:

Private Enum ColumnNames
  fldGender = 4    ' D
  fldAge = 2       ' B
  fldName = 1      ' A
  fldSSN = 3       ' C
  fldFaveColor = 5 ' E
End Enum

Sub RunModule(args As String)
  Range(ActiveCell, ActiveCell.CurrentRegion).Select

  For Each r In Selection.Rows
    If r.Row > 2 Then ' skip header
      Cells(r.Row, fldGender) = GetGender(Cells(r.Row, _
      Cells(r.Row, fldFaveColor) = GetFaveColor(Cells(r.Row, _
      ' etc...
    End If
End Sub

Private Function GetGender(s As String)
  If s = "12345" Then
    GetGender = "F"
    GetGender = "M"
  End If
End Function

Graphical attentions

I broke and slapped the Excel chart at the top of my reports. Two weeks had passed and no one had commented on the pretty pictures. They had to scroll down to see it; I imagined their faces lighting up as stacked columns aligned themselves under a trend line. Then I would get requests, become semi-famous, &c. Were it only so.

JPG makes blurry pictures. Excel can export to PNG. The numbers and lines are much sharper. Don’t forget the title, and label your horizontal and vertical axis. All that high school stuff, except this time I had to rationalize my yearning for acknowledgement.

A part of me dreads that part, the requests. It’s because the problem will be a slight permutation, and novel attempts must be refactored for maximal time savings. All of these tools shall rarely see the light of day; my years have gone to cloaked artifacts.

Honestly, I don’t think they look at them too closely. A part of me is relieved, while the other part wonders whether browsing Facebook would have been the lesser sin.

Using SQL statements in Excel to query workbooks

If you’re in the interstitial space between needing an Access database but stuck with a large workbook, try this:

Sub QueryWorkbook()
  Dim conn As ADODB.Connection
  Dim rs As ADODB.RecordSet
  Dim cn As String ' connection string

conn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & ActiveWorkbook.FullName & ";" & _
    "Extended Properties=""Excel 12.0 Xml;HDR=No;IMEX=1;"";"
  Set cn = New ADODB.Connection

cn.Open conn

Dim sQuery As String

sQuery = "select F1,F2 from [Sheet1$] where F1='fred';"

Set rs = New ADODB.RecordSet
  rs.Open sQuery, cn, adOpenStatic, adLockReadOnly, adCmdTxt

If rs.recordCount > 0 Then
    While Not rs.EOF
      For i = 0 To rs.Fields.Count - 1
        Debug.Print "field " & i & ": " & rs.Fields(i).Value
  End If

End Sub