Understanding the Access ColumnWidth VBA Property

Objects in datasheet view–such as tables, queries, or forms–are composed of one or more columns.

An advantage of the datasheet view is that the columns are easily resizable.  They can be resized by the end user.  In this way, they behave much like Excel columns.  The columns can also be resized via the ColumnWidth property in VBA.

This property includes a few values that have special meaning:

Setting the ColumnWidth property is straightforward.  Where things get interesting is when we try to read the property.

Reading ColumnWidth Values

For certain types of code, especially resizing utilities, you need to be able to find the width of a given column.

The problem is that sometimes you get the actual width in twips and other times you get one of the special values listed above.  For a long time, I thought that I was at the mercy of Access's whims.  I thought there was no way to be sure what I would get.  I no longer believe that.

I have discovered why Access sometimes returns a special ColumnWidth value (-1 or -2) and other times returns the actual width in twips.

Default Width: Always Returns -1

If you set the ColumnWidth property to -1 (i.e., the "Default Value") then .ColumnWidth will return -1 until you either:

• Set .ColumnWidth to a value other than -1
• "Touch" the .ColumnHidden property as shown below

Size to Fit: Did the Actual Width Change?

The difference comes down to answering one key question:

Did the actual width of the column change when the ColumnWidth was set to -2 (size to fit)?

If the answer is Yes, then the ColumnWidth will read -2.

If the answer is No, then the ColumnWidth will read the actual width in twips.

Hidden Trick: Forcing the Actual Width

If you want the actual width of the column in twips without having to deal with -1 or -2, you can use the following trick to clear the -1 / -2 values:

To force the actual width to be returned by .ColumnWidth, "touch" the .ColumnHidden property by setting it to itself.

For example:

Debug.Print Col.ColumnWidth  'may return -1 or -2

Col.ColumnHidden = Col.ColumnHidden  ' "touch" the ColumnHidden property

Debug.Print Col.ColumnWidth  'returns actual twips, never -1 or -2

Sample Code

If you want to play around with these values for yourself, here is some sample code you can use to get started:

Sub TestSpecialColumnWidthValues()
    Const DefaultWidth As Integer = -1
    Const SizeToFit As Integer = -2
    
    
    'Close mSysObjects table if already open
    On Error Resume Next
    DoCmd.Close acTable, "mSysObjects", acSaveNo
    On Error GoTo 0
    
    'Open the mSysObjects table in datsheet view
    DoCmd.OpenTable "mSysObjects", acViewNormal
    
    Dim Frm As Form
    Set Frm = Screen.ActiveDatasheet
    
    Dim Col As TextBox
    Set Col = Frm.Controls("DateCreate")
    
    
    'All columns are initially set to a default width of one inch (1440 twips)
    Log Col, "A"
    
    'To get the actual twips count of the column, we just have to
    '   "touch" the ColumnHidden property (i.e., set it to its existing value)
    Col.ColumnHidden = Col.ColumnHidden
    Log Col, "B"
    
    'If we auto-size the column to fit, we will get the actual width in twips
    '   *only if the new width is sufficiently different than the old width*
    Col.ColumnWidth = SizeToFit
    Log Col, "C"
    
    'However, if we set ColumnWidth to the special SizeToFit value of -2 and
    '   *the new width is the same as the old width* then we get the special
    '   value of -2 when we check the ColumnWidth property
    Col.ColumnWidth = SizeToFit
    Log Col, "D"
    
    'We can always get the actual size in twips by "touching" the ColumnHidden property
    Col.ColumnHidden = Col.ColumnHidden
    Log Col, "E"
    
    'We can force the ColumnWidth property to return a -2 by setting
    '   it twice in a row in VBA:
    Col.ColumnWidth = SizeToFit
    Col.ColumnWidth = SizeToFit
    Log Col, "F"

    
    'If you set the ColumnWidth property to the special
    '   `DefaultWidth` value of -1 it will always return -1
    '   regardless of the previous size of the column
    Col.ColumnWidth = 1440
    Col.ColumnWidth = DefaultWidth
    Log Col, "G"
    
    Col.ColumnWidth = 100
    Col.ColumnWidth = DefaultWidth
    Log Col, "H"
    
    'If you set the ColumnWidth property to the special
    '   `SizeToFit` value of -2 it will return
    '   EITHER -2 OR the actual width in twips
    '   depending on the previous size of the column
    
    'If the actual column width does not change...
    Col.ColumnWidth = 2235
    Col.ColumnWidth = SizeToFit
    Log Col, "I"
    
    '...or is substantially similar,
    '   then ColumnWidth will return -2
    Col.ColumnWidth = 2228
    Col.ColumnWidth = SizeToFit
    Log Col, "J"
    
    'If the actual column width changes by just enough...
    Col.ColumnWidth = 2227
    Col.ColumnWidth = SizeToFit
    Log Col, "K"
    
    '...or more than enough,
    '   then ColumnWidth will return the new actual width
    Col.ColumnWidth = 2000
    Col.ColumnWidth = SizeToFit
    Log Col, "L"
    
End Sub

Sub Log(Col As TextBox, Prefix As String)
    Debug.Print Prefix; ": Col Width: "; Col.ColumnWidth
End Sub

Sample Usage

Here's what the above code returns on my computer.  Your results may be slightly different, especially when it comes to the hard-coded column widths at the end of the routine:

Article Inspiration

The inspiration for this article came from the excellent msaccess-vcs-addin GitHub project where maintainer Adam Waller posted this comment the other day:

Aha! The icon column width was returning -2. That's what was throwing off the resizing calculation. This value means the column is automatically sized to fit the text. The trick is to set the ColumnHidden property to False (even though the column is already visible), and then you can read the actual width of the column. Now it is working perfectly!

I remember running into problems over the years with the ColumnWidth property in VBA, so I thought the topic was worth an article.  I did not expect to fall so far down the rabbit hole, though.  

Hopefully, these insights and sample code help you better understand what's going on when working with the ColumnWidth property in Microsoft Access.