Please note: This page is imported from my wiki, which hasn't been updated in over 10 years. Some of the formatting was lost during the import. I'll try to get around to fixing it someday.
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”)
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
Andrew McDonough is a consultant CTO and software developer, currently based between Berlin and London.
Follow Andrew on Twitter or Connect on LinkedIn