Getting around the Max String size in a vba function?

Question

The max number of characters you can use in string in a vba function is 255. I am trying to run this function

Var1= 1
Var2= 2
.
.
.
Var256 =256

RunMacros= "'Tims_pet_Robot """ & Var1 & """ , """ & Var2 & """ , """   ... """ & Var256 """ '"
Runat=TimeValue("15:00:00")
Application.OnTime EarliestTime:=Runat, Procedure:=RunMacros & RunMacros2 ', schedule:=True

It runs a procedure at a certain time and passes a bunch of variables to it. but the string is too long.

Update: Regrettably I am sure it is not the watch window. Also, it isn't really the max size of a string that I'm dealing with. It's the max size of a string in a vba function.

For example this function works.

Sub test()
Dim RunAt As Date
Dim RunWhat As String

RunAt = Now + 0.00001
RunWhat = "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" & _
"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" & _
"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" 'that makes 254 'a''s
Application.OnTime EarliestTime:=RunAt, Procedure:="'" & RunWhat & " 12'"

End Sub


Sub aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa(m As Integer)
MsgBox ("it works!" & m)
End Sub

But if you change the 12 to 123 it breaks Example

Sub test2()
Dim RunAt As Date
Dim RunWhat As String

RunAt = Now + 0.00001
RunWhat = "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" & _
"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" & _
"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" 'that makes 254 'a''s
Application.OnTime EarliestTime:=RunAt, Procedure:="'" & RunWhat & " 123'"

End Sub


Sub aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa(m As Integer)
MsgBox ("it works!" & m)
End Sub

This code does not work, I'm pretty sure it's because a vba function cannot handle a string with more than 255 chars. Even if you're in Excel and call a function and give it a string longer that 255 chars it doesn't work.

Try in cell A1 =vlookup("really long string", A1:Z10, 1) and then put the really long string somewhere in that range. The vlookup will fail (not fail to find it, but you won't actually be able to do it)

Also I am aware that there is a max length to a sub name, I'm just under it. Sorry that it look so ugly.

Update 2: so I just ended up printing the variable to a sheet and getting the function called by ontime to read them off the sheet. :(

Ambassadoratlarge

From Joel Spolsky (who led much of the early Excel development): "Excel uses Pascal strings internally which is why strings in many places in Excel are limited to 255 bytes, and it's also one reason Excel is blazingly fast." That probably applies to the VLookup issue that you mention, though as others have stated, VBA has no such limit. – Kursk

You can use a String(number_of_repeats, char_to_repeat) to make a string on the fly instead of aaaaaaaaaaaaaaaaaaaaaaaaaaa... – Panamerican

Answer 1

This works and shows more than 255 characters in the message box.

Sub TestStrLength()
    Dim s As String
    Dim i As Integer

    s = ""
    For i = 1 To 500
        s = s & "1234567890"
    Next i

    MsgBox s
End Sub

The message box truncates the string to 1023 characters, but the string itself can be very large.

I would also recommend that instead of using fixed variables names with numbers (e.g. Var1, Var2, Var3, ... Var255) that you use an array. This is much shorter declaration and easier to use - loops.

Here's an example:

Sub StrArray()
Dim var(256) As Integer
Dim i As Integer
Dim s As String

For i = 1 To 256
    var(i) = i
Next i

s = "Tims_pet_Robot"
For i = 1 To 256
    s = s & " """ & var(i) & """"
Next i

    SecondSub (s)
End Sub

Sub SecondSub(s As String)
    MsgBox "String length = " & Len(s)
End Sub

Updated this to show that a string can be longer than 255 characters and used in a subroutine/function as a parameter that way. This shows that the string length is 1443 characters. The actual limit in VBA is 2GB per string.

Perhaps there is instead a problem with the API that you are using and that has a limit to the string (such as a fixed length string). The issue is not with VBA itself.

Ok, I see the problem is specifically with the Application.OnTime method itself. It is behaving like Excel functions in that they only accept strings that are up to 255 characters in length. VBA procedures and functions though do not have this limit as I have shown. Perhaps then this limit is imposed for any built-in Excel object method.

---

Update:
changed ...longer than 256 characters... to ...longer than 255 characters...

Broz

Thanks for the response. You are correct. Functions in vba can handle strings up to 2 GB! thanks. But as you realized my real problem is with the Application.OnTime method behaving like an Exel function, in that it can only handle a string of size 255. I guess I should just accept that I am going to have to work with this limitation? I was hoping there was some way around it. – Ambassadoratlarge

Answer 2

I may have missed something here, but why can't you just declare your string with the desired size? For example, in my VBA code I often use something like:

Dim AString As String * 1024

which provides for a 1k string. Obviously, you can use whatever declaration you like within the larger limits of Excel and available memory etc.

This may be a little inefficient in some cases, and you will probably wish to use Trim(AString) like constructs to obviate any superfluous trailing blanks. Still, it easily exceeds 256 chars.

Hardwood

Buffer overflow. – Jonathanjonathon

Answer 3

Are you sure? This forum thread suggests it might be your watch window. Try outputting the string to a MsgBox, which can display a maximum of 1024 characters:

MsgBox RunMacros

Ssw

255 objects/characters is the max for the watch window. I ran into the same problem w/ a Collection Object in the past. – Terrorize

No dice. it's not the watch window. The error is with the ontime function. See update. Thanks for the comment though – Ambassadoratlarge

Answer 4

This test shows that the string in VBA can be at least 10^8 characters long. But if you change it to 10^9 you will fail.

Sub TestForStringLengthVBA()
    Dim text As String
    text = Space(10 ^ 8) & "Hello world"
    Debug.Print Len(text)
    text = Right(text, 5)
    Debug.Print text
End Sub

So do not be mislead by Intermediate window editor or MsgBox output.

Messily

I can confirm that the Watches window will truncate the display of a string variable at 252 chars, while using the Immediate window (with Debug.Print myvariable) will display > 252 characters. Although the OP's problem may be unrelated to the Watches window, this truncation sent me down a path of much wasted effort (as well as landing here!) so I thought I would chime in. Truncating a var like this can be confusing for someone new to VBA and it can imply the problem is elsewhere where its really a quirk of the Watches UI. – Streak

Answer 5

Couldn't you just have another sub that acts as a caller using module level variable(s) for the arguments you want to pass. For example...

Option Explicit
Public strMsg As String

Sub Scheduler()

    strMsg = "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"
    Application.OnTime Now + TimeValue("00:00:01"), "'Caller'"

End Sub

Sub Caller()

    Call aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa("It Works! " & strMsg)

End Sub

Sub aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa(strMessage As String)

    MsgBox strMessage

End Sub

Incisure

If you're just looking for a workaround, this is probably your best bet. If you have multiple sub locations you want to abbreviate, you could even pass an index of the sub you want, and have a "Select Case". – Script

Answer 6

Excel only shows 255 characters but in fact if more than 255 characters are saved, to see the complete string, consult it in the immediate window

Press Crl + G and type ?RunWhat in the immediate window and press Enter

Deledda

Answer 7

One of the main causes of this problem that I’ve run into is the fact that the entire procedure-plus-arguments string is limited to 255 characters, including the procedure's containing Excel file specification (automatically added by Excel), which is aggravated by Excel’s brain-dead default specification of the file's full-path\name. So, if you have very deep folder structures (like I do) combined with long-ish, descriptive file names and descriptive folder names (like I often use), then that factor can be a frequent problem when using OnTime.

WORKAROUND: I always (!) explicitly include the containing workbook’s name, which apparently alleviates Excel from having to automatically do so (in its nonsensical manner):

Sub CallOnTime()

    Application.OnTime Now + TimeSerial(0, 0, 1), _
                       "'" & ThisWorkbook.Name & "'!'TargetMacro 37,""Some really long String parameter…""'"
End Sub

Sub TargetMacro(I As Integer, S As String)

    MsgBox "I=" & I & ", S=" & S
End Sub

That buys me a lot of string-length real estate to use for the called procedure's arguments.

IMPORTANT: note the inclusion and position of the single-quoted bang delimiter ('!') in the above example.

My guess is that the original designer chose to include the full file-path along with the procedure's containing file name to avoid identification ambiguity with other workbooks running under the same instance of the Excel application. But that's a nonsensical rationale because Excel doesn't allow multiple workbooks with the same name to be opened under a single instance, even if they exist under different folder paths (which, of course, they would have to).

Some additional space-saving tips:

• If the parameters include a worksheet specification, use its numeric sheet Index property instead of its name.
• Eliminate any space characters around the comma delimiters.

Reconstructionism

Answer 8

To get around the line limits in the VBE you must load the string in parts from somewhere else. Reading an external file is ideal but sometimes you don't want to ship external files, you need your solution to be self-contained.

In these cases I normally dump the large text into a cell in a hidden sheet. Note that Excel cells have a 32K limit on size. Sometimes I will concat a bunch of cells together that each have 32K of text in them. It's not ideal but it will get you the large string data you need without external files.

At some point you may have to come to terms with the fact that Excel isn't the best tool for every job.

Dim reallyLongString As String
reallyLongString = MyHiddenSheet.Range("A1").Value & MyHiddenSheet.Range("A2").Value & MyHiddenSheet.Range("A3").Value & MyHiddenSheet.Range("A4").Value

Jonathanjonathon