Get maximum number of characters that a cell can contain

Question

Excel specifications and limits says:

Total number of characters that a cell can contain: 32,767 characters

Is there a way to get this number programatically?

I'm asking because hardcoding constants should, in general, be avoided if and when feasible. This number may conceivably change by Office version (It hasn't changed between 2003 and 2013, but who knows what Microsoft has in store for us).

It's pretty easy to get the maximum number of rows in a worksheet:

Sheet1.Rows.Count ' returns 65,536 in Office 2003 and 1,048,576 in Office 2007-2013

but apparently, getting the maximum number of characters that a cell can contain isn't as straightforward.

Note that writing too many characters to a cell will not result in an error; it will silently fail and truncate the string — so proper error handling isn't an option here.

Answer 1

Alternative: Loop appending a chunk until the assigned length is not whats expected

Const INT_MAX As Integer = 32767
Dim i As Long

ActiveCell.Value = ""

Dim buff As String: buff = Space$(INT_MAX)

Do
    i = i + 1
    ActiveCell.Value = ActiveCell.Value & buff
    If Len(ActiveCell.Value) <> (i * INT_MAX) Then
        MaxLen = Len(ActiveCell.Value)
        Exit Function
    End If
Loop

Or even

 ActiveCell.Value = Space$(A_BIG_NUMBER)
 MaxLen = Len(ActiveCell.Value)

Here's a variant where we take exponential steps (larger and larger steps whose size increases by a factor stepFactor each time).

Function MaximumNumberOfCharactersACellCanContain(r As Range, _
    Optional ByVal stepFactor As Double = 2)

    Dim n As Double
    Dim nActual As Long
    Dim l As Long
    n = 1
    Do
        n = n * stepFactor
        nActual = CLng(n)
        r.Cells(1, 1).Value = Space$(nActual)
        l = Len(r.Cells(1, 1).Value)
        If l <> nActual Then
            MaximumNumberOfCharactersACellCanContain = l
            Exit Function
        End If
    Loop
End Function

Example usage:

Debug.Print MaximumNumberOfCharactersACellCanContain(Range("A1"), 8)

The choice of stepFactor is a compromise between:

• Reducing the number of iterations (larger factor is better), and
• Limiting down the cost of the last iteration (the one that fails). If stepFactor is too large, then you're writing a very long string to the cell and this is quite slow.
• Making sure the last iteration will never hit the out of memory ceiling (~130 million characters on my system). (Could add error handling do deal with this eventuality.)

stepFactor somewhere between 2 and 8 should be robust and quick.

Answer 2

In a loop, append characters one by one to the cell contents. Each time, read cell contents, check if the last character added is present. If it isn't then that's the limit.

Upside: Works and is 100% reliable.

Downside: Really slow. It takes 10-15 seconds to complete, due to the many read-writes to/from sheet.

Obviously, this could be optimised by using a good guess (e.g. 32,767) as the initial condition, and using a hunt & bisect search algorithm rather than incrementing by 1. However if the answer is far enough away from the initial guess, this might still take ~1 second to run — not something you would want to call repeatedly.

Function MaximumNumberOfCharactersACellCanContain(r As Range)
    'NB: Range r will be overwritten.
    Dim sIn As String
    Dim sOut As String
    Dim i As Long
    Application.ScreenUpdating = False
    Do
        i = i + 1
        sIn = sIn & Chr(97 + (i - 1) Mod 26)
        r.Cells(1, 1).Value = sIn
        sOut = r.Cells(1, 1).Value
        If Right(sOut, 1) <> Right(sIn, 1) Then Exit Do
        'If Len(sOut) <> Len(sIn) Then Exit Do
    Loop
    Application.ScreenUpdating = True
    MaximumNumberOfCharactersACellCanContain = i - 1
End Function

Example usage:

MsgBox MaximumNumberOfCharactersACellCanContain(Range("A1"))