According to Exporting to Microsoft Word, Word tables support a maximum of 32767 rows and 63 columns. Actually, I'm not sure where I saw the 32k rows limit, but 63 seems to be what everybody says is the column limit. If anyone could verify that I'd be glad to hear it.

So here's a procedure that takes a variable amount of columns and rows and either creates or opens an existing Word document, appending the table to the end of the document. If a new document is created, the "end" of the document is obviously the same as the start, so the table becomes the only content in the file. If an existing document is passed, it is opened and the table is appended to the end of the existing content. The code is based on code found at Automating Word Tables for Data Insertion and Extraction.

This code has been tested in Word 2003, please let me know if it works in other versions.

Sub AddTableToWordDoc(entries As Variant, filePath As String, _
    Optional styleToApply As Variant = "Table Simple 3")
' specify existing file to add table to
' existing Word document
  On Error GoTo ErrorHandler

  Dim wordApp As Object ' Word.Application
  Dim wordDoc As Object ' Word.Document
  Dim wordRange As Object ' Word.Range
  Dim wordTable As Object ' Word.Table
  Dim numRows As Long, numCols As Long
  Dim nrRows As Long, nrCols As Long
  Dim myRange As Object ' Word.Range
  ' late bound constants
  Const wdWindowStateMinimize As Long = 2
  Const wdNormalView As Long = 1
  Const wdDoNotSaveChanges As Long = 0
  Const wdWord9TableBehavior As Long = 1
  Const wdAutoFitContent As Long = 1

  ' create word document
  On Error Resume Next
  Set wordApp = CreateObject("Word.Application")
  If wordApp Is Nothing Then
    MsgBox "Could not start Microsoft Word"
    GoTo ProgramExit
  End If
  ' resume normal error handling
  On Error GoTo ErrorHandler

  ' if an existing file is specified, open it, else create new file
  If Len(Dir(filePath)) > 0 Then
    Set wordDoc = wordApp.documents.Open(filePath)
  Else
    Set wordDoc = wordApp.documents.Add
  End If

  ' maximize execution speed
  With wordDoc.Windows(1)
    .WindowState = wdWindowStateMinimize
    .View = wdNormalView
  End With
  With wordApp
    .Options.Pagination = False
    .ScreenUpdating = False
  End With

  ' create table w/ appropriate # of rows & cols
  ' append to existing file, or simply add (if new file)
  numRows = UBound(entries)
  numCols = UBound(entries, 2)
  Set wordRange = wordDoc.Range(wordDoc.Range.Characters.Count - 1)
  Set wordTable = wordRange.Tables.Add(wordRange, numRows, _
    numCols, wdWord9TableBehavior, wdAutoFitContent)

  ' loop through array and populate table cells
  For nrRows = 1 To numRows
    For nrCols = 1 To numCols
      wordTable.cell(nrRows, nrCols).Range.text = _
          entries(nrRows, nrCols)
    Next nrCols
  Next nrRows

  ' format table
  With wordTable
    .style = styleToApply  ' ex: "Table Simple 3"
    .ApplyStyleHeadingRows = True
    .ApplyStyleLastRow = True
    .ApplyStyleFirstColumn = True
    .ApplyStyleLastColumn = True
  End With

  ' save file and close Word
  With wordDoc
    .SaveAs filePath
    .Close wdDoNotSaveChanges
  End With
  wordApp.Quit

ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.number & " - " & Err.Description
  Resume ProgramExit
End Sub

First we create a new instance of Word.Application, then check if the filepath being passed is for an existing file. If the file exists, we open it, otherwise we create a new document. After a few speed optimizations, we create a new table and append it to the end of the document.

Document.Range.Characters.Count is used to determine the end of the file. This value represents the number of characters in the file, and the table is placed after this space. Regardless of whether the file is new or not, the table will be placed after the last character. The fact that a new file has no content is irrelevant; the end of the file just happens to also be the beginning. We are telling VBA to put the table at the "end" of the file, regardless of where it is.

Afterwards, we loop through the array and paste values into the table cells and apply some formatting. You can remove or change these values (or parameterize them). Finally we save and close the file.

Because I am not familiar with the Word Object Model, a lot of assumptions are made in the code. For example, the Tables.Add Method assumes you want to autofit the cells. I also assumed a default style and applied it to the table.

If there is a better way to do this, I don't know it.

Create and append Word document tables from VBA is Copyright © JP Software Technologies. All Rights Reserved.

It uses the Asc function to determine the character code for each character in the string, taking advantage of the fact that the letters A-Z and a-z are consecutive. You can see this if you use the character set workbook I use.

One thing it does that I don't like is it explicitly sets the return value to False, and it repeatedly sets the return value to True. A better workflow would be to assume that the input string contains only letters (IsLetter = True), then set IsLetter to False (and immediately exit) if we encounter a non-letter (is that a word?). This way, we only set IsLetter to True or False once, instead of wasting processor time setting it to True N times.

Function IsLetter(strValue As String) As Boolean
  Dim intPos As Integer
  ' assume True
  IsLetter = True

  For intPos = 1 To Len(strValue)
    Select Case Asc(Mid(strValue, intPos, 1))
    Case 65 To 90, 97 To 122

    Case Else
      ' not a letter, return False
      IsLetter = False
      Exit For
    End Select
  Next intPos
End Function

In a comment on that article I posted two additional ways to check if a given string contains only letters. They still loop through each character of the input string, but they use the Like Operator instead of comparing each character to character codes.

RegExp to the Rescue

We can also use Regular Expressions to check if a string contains only letters. This would mean only one pass through a string, instead of looping!

We need two functions from the Regular Expressions page: GetRegEx and TestRegex. Once you copy those functions to a standard module, the following function may be used to check if a given string consists only of letters a-z (case insensitive):

Function IsAlpha(stringToCheck As String) As Boolean
  Dim regex As Object ' VBScript.RegExp
  Set regex = GetRegEx
  If Not regex Is Nothing Then
    IsAlpha = TestRegex(regex, "^[a-zA-Z]+$", stringToCheck)
  End If
End Function

"^[a-zA-Z]+$" roughly translates to from the start of the string, match any number/combination of upper or lower case letters until the end of the string.

RegEx vs. Loop

How does the RegEx perform against a loop?

I used the following extremely un-scientific procedure to test the relative speeds of the IsLetter (loop) and IsAlpha (RegEx) functions.

Sub TestRegexLoop()

  Dim i As Long
  Dim startTimeRegex As Single
  Dim endTimeRegex As Single
  Dim startTimeLoop As Single
  Dim endTimeLoop As Single
  Dim msg As String
  Dim is_a_letter As Boolean

  Const numberOfLoops As Long = 1000
  Const stringToCheck As String = _
    "Thequickbrownfoxjumpsoverthelazydog"

  ' use Regex
  startTimeRegex = Timer
  For i = 1 To numberOfLoops
    is_a_letter = IsAlpha(stringToCheck)
  Next i
  endTimeRegex = Timer

  ' use Loop
  startTimeLoop = Timer
  For i = 1 To numberOfLoops
    is_a_letter = IsLetter(stringToCheck)
  Next i
  endTimeLoop = Timer

  msg = "Number of iterations: " & numberOfLoops & vbCrLf
  msg = msg & "Using Regex: " & _
    Format(endTimeRegex - startTimeRegex, "#.###") & " seconds" & vbCrLf
  msg = msg & "Using Loop: " & _
    Format(endTimeLoop - startTimeLoop, "#.###") & " seconds" & vbCrLf
  MsgBox msg
End Sub

Results

Let's see how both functions performed. At just 1,000 iterations, RegEx is already much slower than a loop.

At 10,000 iterations, this is a Mike Tyson fight. The function that uses a loop is barely breaking a sweat, while RegEx is clocking in at 14 seconds. I quit.

RegEx vs. Loop Rematch

We're not being fair. After all, IsAlpha had to instantiate the RegExp object every time it is called. That has to account for some of the slowdown. The original IsAlpha is a function trying to do too much. So we alter the IsAlpha function to take in an existing RegExp object…

Function IsAlpha(regex As Object, stringToCheck As String) As Boolean
  IsAlpha = TestRegex(regex, "^[a-zA-Z]+$", stringToCheck)
End Function

…and we create the RegExp object in our test procedure and pass it to IsAlpha:

Sub TestRegexLoop2()

  Dim i As Long
  Dim startTimeRegex As Single
  Dim endTimeRegex As Single
  Dim startTimeLoop As Single
  Dim endTimeLoop As Single
  Dim msg As String
  Dim is_a_letter As Boolean
  Dim regex As Object  ' RegExp

  Const numberOfLoops As Long = 1000
  Const stringToCheck As String = "Thequickbrownfoxjumpsoverthelazydog"

  ' use Regex
  startTimeRegex = Timer

  Set regex = GetRegEx
  If Not regex Is Nothing Then
    For i = 1 To numberOfLoops
      is_a_letter = IsAlpha(regex, stringToCheck)
    Next i
  End If
  endTimeRegex = Timer

  ' use Loop
  startTimeLoop = Timer
  For i = 1 To numberOfLoops
    is_a_letter = IsLetter(stringToCheck)
  Next i
  endTimeLoop = Timer

  msg = "Number of iterations: " & numberOfLoops & vbCrLf
  msg = msg & "Using Regex: " & _
    Format(endTimeRegex - startTimeRegex, "#.###") & " seconds" & vbCrLf
  msg = msg & "Using Loop: " & _
    Format(endTimeLoop - startTimeLoop, "#.###") & " seconds" & vbCrLf
  MsgBox msg
End Sub

I have a feeling this is going to be more of a fair fight.

Results

That's more like it! Even passing the RegExp object between two functions has barely slowed it down.

At 10,000 iterations the RegEx function is only slightly slower. In my humble non-scientific opinion, it is statistically insignificant.

Even at 100,000 iterations they are both taking nearly 1 second to complete.

Conclusion

Even though RegExp can evaluate the entire string in one go, the loop was still faster. Refactoring the IsAlpha function to create the RegExp object only once, however, and passing it to another function, closed the gap. I like RegEx but don't have a strong preference, so when I use it I will keep in mind that the RegExp object should be created in a parent procedure and passed to a child function for it to use RegExp parsing methods.

Is that a letter? is Copyright © JP Software Technologies. All Rights Reserved.

For example, Ron de Bruin does this in a series of MSDN articles and on his website:

• Using VBA in Excel to Send Workbooks and Ranges Through E-Mail with Outlook (Part 1 of 2)
• Mail Range or Selection

Another example:

Sending Email From IIS with ASP and CDONTS

And yes, even Microsoft does it:

Sending E-Mail Without Installing the SMTP Service

For your review I humbly submit the following as a best practice: Use the Recipients Collection to manipulate Item Recipients.

Before you say anything, I understand the following:

• VBA allows you to use To/CC/BCC properties to set recipients. If you were supposed to use the Recipients Collection instead, these properties would be read-only.
• It is simpler to write ".To = abc@xyz.com" than the way I suggest, which is to set a reference to MailItem.Recipients, create the recipient and assign it to the "To" field.

Instead of (assume msg is a variable of type Outlook.MailItem):

msg.To = "someone@somewhere.com"

you would write

Dim recips As Object ' Outlook.Recipients
Dim currentRecipient As Object ' Outlook.Recipient
Set recips = msg.Recipients
Set currentRecipient = recips.Add "someone@somewhere.com"
currentRecipient.Type = olTo

In Working with the Outlook Recipients collection in VBA I explained my rationale for why you should use MailItem.Recipients. The article includes methods for adding, counting and removing recipients, which I will update shortly and should leave you with no excuse not to use the Recipients Collection. But I will repeat my reasons here anyway, in order to try and convince you.

1. The Recipients Collection is the interface designed for interacting with Recipients
2. The Recipients Collection is a more robust interface than .To, .CC, .BCC
3. To, CC and BCC only contain the display names
4. Microsoft wants you to use the Recipients Collection – I know this is a weak argument

If you didn't already know, I am a big fan of encapsulation. As a result, I am a big fan of using interfaces designed for interacting with an object. So I repeat: The Recipients Collection is the interface designed for interacting with Recipients.

If you use the To, CC or BCC properties in code you post publicly in order to set their values, I will correct you.

Let's put it this way: if you downloaded a XML file from the web, would you use Instr and Mid to find what you want, or would you use an interface (the MSXML DOM) that includes methods specifically designed to parse it?

If you have code that uses .To, .CC, .BCC, I encourage you to rewrite it using the methods I posted in Working with the Outlook Recipients collection in VBA. Let's collectively agree to use the methods designed for the purpose of manipulating Recipients. Your comments welcome.

Use the Recipients collection to add recipients to emails is Copyright © JP Software Technologies. All Rights Reserved.

But we have no matching function for going back in time to calculate business days prior to a given date. So I've written a function that does so, as well as improved the previous function for calculating the next business day.

Previous Business Day Function

The following function accepts a date and (optional) the number of days to go back. If no amount is specified, it is assumed that you want the previous business day. No adjustment is made for holidays.

Function PreviousBusinessDay(dateFrom As Date, _
    Optional daysBack As Long = 1) As Date
  Dim currentDate As Date
  Dim previousDate As Date

  ' convert pos to neg
  If daysBack > 0 Then
    daysBack = -daysBack
  End If

  ' determine previous date
  currentDate = dateFrom
  previousDate = DateAdd("d", daysBack, currentDate)

  ' was previous date a weekend day?
  Select Case Weekday(previousDate, vbUseSystemDayOfWeek)
  Case vbSunday
    previousDate = DateAdd("d", -2, previousDate)
  Case vbSaturday
    previousDate = DateAdd("d", -1, previousDate)
  End Select

  PreviousBusinessDay = CDate(Int(previousDate))

End Function

To specify a date for the function, use any native function, variable or constant that returns a Date type.

Usage

PreviousBusinessDay(#5/9/2012#)
PreviousBusinessDay(CDate("5/9/2012"))
PreviousBusinessDay(Now)

You can specify either a positive or negative number to go back. In case you forget, the function changes positive to negative numbers for you.

No assumption is made about the first day of your week — the work week as specified locally is used to determine if the previous business days falls on the "weekend". (If you are having trouble falling asleep you can read more about the NLS API here). I have not tested this, and I have no idea where to change the local work-week setting, but it is supposed to be locale-independent so I would love to know if it works properly in other locales. You can of course change this from vbUseSystemDayOfWeek to whatever is the first day of your work week.

A better Next Business Day Function

Following is my improved version of the next business day function I linked to earlier.

Function NextBusinessDay(dateFrom As Date, _
    Optional daysAhead As Long = 1) As Date
  Dim currentDate As Date
  Dim nextDate As Date

  ' convert neg to pos
  If daysAhead < 0 Then
    daysAhead = Abs(daysAhead)
  End If

  ' determine next date
  currentDate = dateFrom
  nextDate = DateAdd("d", daysAhead, currentDate)

  ' is next date a weekend day?
  Select Case Weekday(nextDate, vbUseSystemDayOfWeek)
  Case vbSunday
    nextDate = DateAdd("d", 1, nextDate)
  Case vbSaturday
    nextDate = DateAdd("d", 2, nextDate)
  End Select

  NextBusinessDay = CDate(Int(nextDate))

End Function

This function has several advantages over the previous, er, next function:

• You can specify number of business days to advance, or call the function with just a date to get the very next business day
• Uses DateAdd consistently instead of waffling between DateAdd and simple math
• Should automatically adjust to local settings
• Drops that silly variable prefixing habit

Usage

NextBusinessDay(#5/9/2012#)
NextBusinessDay(CDate("5/9/2012"))
NextBusinessDay(Now)

One thing I am not happy with, however, is the hardcoding of the date math, if adjustment be necessary. I feel like there should be some way to programmatically calculate how many days to add, but I can't figure out how. I'll settle for hardcoding for now.

Get Previous Business Day in VBA is Copyright © JP Software Technologies. All Rights Reserved.

Can there be such a thing as "bad" programming code?

We have all encountered code that made us say something like

• "What was he thinking?"
• "Why did he do that instead of using X method?" (X being a built-in method that already does what the code does manually)

But is it bad programming?

We all know there are usually a dozen ways to do any given task in VBA. Some are more efficient than others. Some ways result in less code. Some ways follow best practice. These usually overlap. What do you consider a "good" piece of code?

• Short?
• Most efficient?
• Outside the box?

Just because a piece of code is long or inefficient or reinvents the wheel, it may still work. Some people might say there are only two kinds of programming code: good code and code that doesn't work. If the code works, can it ever be called bad?

If the code is a little bit slow or does some looping, can it be called bad?

For example, if I write this statement:

Dim workbookName, worksheetName As String

Is this bad code if I use both variables to hold strings? I mean, the code will still work, it may be a few milliseconds slower, it may not be the King's VBA, so what's the problem? I may have copied this from another website without knowing that it really means

Dim workbookName As Variant, worksheetName As String

At what point does code technically become bad? Is it possible, simply by looking at the above line, to determine whether the writer is honestly ignorant about how to properly declare variables? Or is the code just prima facie bad?

I have come to the belief that there is such a thing as bad code. Ex:

• Multiple hardcoded values (whether it is magic numbers or version-specific hardcoding)
• Writing ("creating") code that does what built-in methods already do
• Looping through worksheet ranges instead of arrays
• Localized code

One of my pet peeves is code that is tightly coupled with a particular workbook. The code is typically localized and will not work in any other environment without extensive modification.

This annoys me because the code is almost useless. It is only being shown to show off or demonstrate some marginal coding technique that almost nobody will ever need to use. The code relies on a very specific workbook setup, and the slightest deviation will cause the code to fail. The chances of anyone (other than the author) being able to actually use it as-is are low. We've all been to training classes where the trainer only shows you what he wants you to see and can't answer the simplest question that falls outside of the predetermined agenda. (psst: you're not supposed to ask those kinds of questions)

If your code only works for you, keep it to yourself. I consider localized code "bad" not because it doesn't work, but because it's been posted in public yet has a severely limited scope. When I see this kind of code, I have trouble keeping my fingers off the keyboard.

Although my instinct is to immediately attribute this type of coding to ignorance, after reading Why You’re a Bad PHP Programmer I've realized that I probably have a self-serving bias. I understand that there are a lot of circumstances other than bad intent that could lead someone to write bad code. I know I've written plenty of code that is pretty scary looking to others, without having bad intentions. It may be an innocent series of concessions that leads to the abominations I've given birth to.

Do you do any of the things that could land you in the programming doghouse? Things like

• writing poor comments — you literally explain what the code is doing (which should be obvious from the code itself) instead of sectional commenting that explains the purpose of the code?
• writing "Dim workbookName, worksheetName As String" instead of "Dim workbookName As String, worksheetName As String" or similar things which, while technically correct, are inefficient?
• publishing code that only works under very limited conditions and can't be cut and pasted elsewhere?

Do you think there is such a thing as bad code? If so, what do you consider bad?

Is there such a thing as bad programming code? is Copyright © JP Software Technologies. All Rights Reserved.