• Excel Web Queries
• Excel Macros
• Excel Annoyances
• Excel Validation

To open the VBA editor in Excel hit Alt-F11

To view the object model, hit F2 while in the VBA editor.

Dim mystring As String

E.g. To concatenate all cell values, display as a message box, and turn all processed cells red.

Dim result As String
For Each MyCell In Selection
  result = result & MyCell.Value
  MyCell.ColorIndex = Red
Next
MsgBox result

  For RowCount = 1 To Selection.Rows.Count
      thisdata = thisdata & "<tr>"
      For ColCount = 1 To Selection.Columns.Count
          If Selection.Cells(RowCount, ColCount).Text = ""
      Next
  Next

A good way to debug Excel macros is to use a message box. This is similar to alert() in Javascript.

MsgBox("Here is my message")

• Open the VBA editor (Tools → Macros → Visual Basic Editor)
• Create a new module (Insert → Module)
• Write the functions

An experienced programmer may expect that the basic object in a work sheet is a Cell, with a Range being a collection of Cell objects. This is not the case. In fact a Cell object is simply a Range object, and it's value can be determined by calling Range.Value If Range.Value is called on an actual Range, this returns the value of the first (top left) cell in the Range.

  Dim msg As String
  For Each Nm In ThisWorkbook.Names
      msg = msg & "    " & Nm.Name
  Next
  MsgBox msg

Function five()
  five = 5;
End Function

Function addone(n As Integer)
  addone = n + 1
End Function

Function join(delimiter As String, r As Range)
  Dim result As String
  Dim thisDelimiter As String
  thisDelimiter = ""
  For Each c In r
    result = result + thisDelimiter + c.Value
    thisDelimiter = delimiter
  Next
  join = result
End Function

Function sc(r As Range)
  s = r.Value
  first = Left(s, 1)
  rest = Mid(s, 2, Len(s) - 1)
  sc = UCase(first) & LCase(rest)
End Function

The following is a simple function I wrote to simplify my continual dirty use of Excel's CONCAT() function. It works in a similar way to C's printf funtion where you pass it a template and a list of variables to substitute. The only placeholders used are strings (%s). This function is a good example of VBA's ability to deal with an arbitrary number of arguments.

Function printf(s As String, ParamArray args())
  For Each a In args
  s = Replace(s, "%s", a, 1, 1)
Next
printf = s
End Function

I use this function a lot to get Excel to write SQL for me:

=printf("INSERT INTO person values ('%s','%s', '%s');", a1, b1, c1)")

Use this function to extract values of cells

Function getValue(c) As Double
  getValue = Val(c)
End Function

Use this function to extract the Hyperlink from a Cell in Excel

Function getLink(r As Range)
  Dim result As String
  result = ""
  Dim link As Hyperlink
  If r.Hyperlinks.Count = 0 Then
    result = ""
  Else
    result = r.Hyperlinks(1).Address
  End If
  getLink = result
End Function

Writes a SQL formatted date to the current cell

Sub InsertTimestamp()
  ActiveCell.FormulaR1C1 = "=TEXT(NOW(),""YYYY-MM-DD hh:mm"")"
  ActiveCell.Select
  Selection.Copy
  Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
      False, Transpose:=False
End Sub

• Excel Objects
• Excel String Functions
• Named ranges