Получить максимальное количество символов, которые могут содержать ячейки

Спецификации и ограничения Excel говорят:

Общее количество символов, которое может содержать ячейка: 32 767 символов

Есть ли способ получить этот номер программно?

Я спрашиваю, потому что констант жесткого кодирования, в общем, следует избегать, если и когда это возможно. Этот номер может, вероятно, измениться версией Office (он не изменился в период с 2003 по 2013 год , но кто знает, что Microsoft имеет для нас).

Достаточно легко получить максимальное количество строк на листе:

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

но, видимо, получение максимального количества символов, которые может содержать ячейка, не так просто.

Обратите внимание, что запись слишком большого количества символов в ячейку не приведет к ошибке; он будет автоматически терпеть неудачу и усекать строку – поэтому правильная обработка ошибок здесь не является вариантом.

Альтернатива: Loop добавляет кусок до тех пор, пока назначенная длина не будет ожидаться

 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 

Или даже

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

Вот вариант, где мы принимаем экспоненциальные шаги (большие и большие шаги, размер stepFactor каждый раз увеличивается на фактор stepFactor ).

 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 

Пример использования:

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

Выбор stepFactor – это компромисс между:

  • Уменьшение числа итераций (более высокий коэффициент лучше) и
  • Ограничение стоимости последней итерации (неудачной). Если stepFactor слишком велик, вы пишете очень длинную строку в ячейке, и это довольно медленно.
  • Удостоверившись, что последняя итерация никогда не ударит по потолку с памятью (~ 130 миллионов символов в моей системе). (Возможно, обработка ошибок справляется с этой возможностью.)

stepFactor где-то между 2 и 8 должен быть надежным и быстрым.

В цикле добавьте символы один за другим к содержимому ячейки. Каждый раз, прочитав содержимое ячейки, проверьте, присутствует ли последний добавленный символ. Если это не так, это предел.

Потенциал роста: работает и на 100% надежен.

Недостаток: действительно медленный. Это займет 10-15 секунд, из-за большого количества операций чтения-записи на / из листа.

Очевидно, что это можно было бы оптимизировать, используя хорошее предположение (например, 32 767) в качестве начального условия и используя алгоритм поиска поиска и поиска, а не увеличивая на 1. Однако, если ответ достаточно далеко от первоначальной догадки, это может все еще быть возьмите ~ 1 секунду для запуска – не то, что вы хотели бы вызывать повторно.

 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 

Пример использования:

 MsgBox MaximumNumberOfCharactersACellCanContain(Range("A1")) 
Давайте будем гением компьютера.