Excel version & Row and Column limits

If macros must run on multiple versions of Excel, care is needed not to overrun the older versions limits. Microsoft made a major change to the Row and Column limits in Excel 2007:

Until Excel 2003:

• 65,536 Rows
• 256 Columns

From Excel 2007

• 1,048,576 Rows
• 16,384 Columns

There are other changes, like unlimited number of sheets and the size of content each cell can have. All huge increases from the previous versions. Excel limits full list can be found here:

https://support.office.com/en-us/article/Excel-specifications-and-limits-CA36E2DC-1F09-4620-B726-67C00B05040F

It can be easy to cross the smaller limits, specially the 256 columns. If the macro adds data in columns, and the input is large, it can easily step over 256, crashing the macro in the process.

To avoid this, macros should check the Excel version they are running and the respective limits. This can be used to check if the limit is going to be cross and show a friendly error message to the user, instead of just crashing unexpectedly.

GetExVersion() 

This function takes no parameters and just returns the Excel version running the macro. Notice that Excel versions are numbers, not the commercial naming – Excel 2007 is 12, Excel 2016 is 16.

With this function we can define 2 more to retrieve the Row and Column limits:

GetMaxRows(nVersion”) eg: GetMaxRows (12)

GetMaxColss(nVersion”) eg: GetMaxCols (12)

Both functions receive the version number as a parameter and return the respective max row or column number (note: it is not the number of rows or columns, but the number of the last one – which is 1 less, so 255 for the last of 256 columns):

The easiest way to use this functions it to call them using the GetExVersion() function as a parameter. This way, it is complete self-sufficient – no need to provide any information to get a result:

nMaxRowsVariable = GetMaxRows(GetExVersion())

You can copy the code here:

Public Function GetExVersion() As Long
' Return Excel version number
Dim nOut As Long                            ' Declare the variables

    nOut = Val(Application.Version)         ' Get Version Number from system variable
    
GetExVersion = nOut                         ' Return result to caller

End Function

Public Function GetMaxRows(nVersion As Long) As Long
' Return the maximum number of rows
Dim nOut As Long                            ' Declare the variables

    If nVersion >= 12 Then                  ' If >= Excel 2007
        nOut = (2 ^ 20) - 1                 ' New versions limit
    Else
        nOut = 65535                        ' Older versions limit
    End If
    
GetMaxRows = nOut                           ' Return result to caller

End Function

Public Function GetMaxCols(nVersion As Long) As Long
' Return the maximum number of columns
Dim nOut As Long                            ' Declare the variables

    If nVersion >= 12 Then                  ' If >= Excel 2007
        nOut = 16383                        ' New versions limit
    Else
        nOut = 255                          ' Older versions limit
    End If
    
GetMaxCols = nOut                           ' Return result to caller

End Function