# 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!"
Else
Cells(row_ptr, 8) = "NOT IS BETWEEN"
End If

row_ptr = row_ptr + 1
Wend
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("A1").Select
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, _
fldSSN))
Cells(r.Row, fldFaveColor) = GetFaveColor(Cells(r.Row, _
fldGender))
' etc...
End If
Next
End Sub

Private Function GetGender(s As String)
If s = "12345" Then
GetGender = "F"
Else
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
Next
rs.MoveNext
Wend
End If

End Sub
```