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. :(

Answer 1

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

Answer 2

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

It looks like 1023 characters.

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 256 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.