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:
|0||Hides the column.|
|-1||(Default) Sizes the column to the default width.|
|-2||Sizes the column to fit the width of the displayed text.|
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:
.ColumnWidthto a value other than -1
- "Touch" the
.ColumnHiddenproperty 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
.ColumnHiddenproperty by setting it to itself.
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
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
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:
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
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.