VBA unexpected reach of string size limit

Question

I am facing an "out of string space" error while trying to assign large strings in vba:

Dim MyData As String
MyData = Space$(321262258)

The number of characters is clearly under 2^31 expected limit. What could be the reason of such an error ?

Thanks for your help !

Answer 1

This seems to be a somewhat opaque error -- opaque in the sense that it would require detailed understanding of the internals of the VBA interpreter to answer. MSDN says about this error:

Visual Basic permits you to use very large strings. However, the requirements of other programs and the way you manipulate your strings may cause this error.

https://msdn.microsoft.com/en-us/library/aa264524(v=vs.60).aspx

I can't find anything in the documentation which suggests a limit of around 2^27 for many built-in functions such as Space() or String(), as well as concatenation operators like + or &, but these limits seem to exist. There seems to be a poorly documented discrepancy between the maximum possible size of a string in memory (say read in from a text file) and the maximum possible size of string which can be built up from built-in VBA functions and operators. Odd. I would be interested if anyone knows of a kludge which can in pure VBA construct the string which Space$(321262258) or even Space(2^31 -1 ) is trying to construct.