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      amp  Var1  amp             amp  Var2  amp                       amp  Var256        Runat TimeValue  15 00 00   Application OnTime EarliestTime  Runat  Procedure  RunMacros  amp  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   amp     aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa   amp     aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa   that makes 254  a  s Application OnTime EarliestTime  RunAt  Procedure       amp  RunWhat  amp    12    End Sub   Sub aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa m As Integer  MsgBox   it works    amp  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   amp     aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa   amp     aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa   that makes 254  a  s Application OnTime EarliestTime  RunAt  Procedure       amp  RunWhat  amp    123    End Sub   Sub aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa m As Integer  MsgBox   it works    amp  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

User · Accepted Answer

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

User · Answer

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

User · Answer

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   amp   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

User · Answer

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

User · Answer

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     amp  strMsg   End Sub  Sub aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa strMessage As String       MsgBox strMessage  End Sub

User · Answer

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

[ontime] Getting around the Max String size in a vba function?

Examples related to ontime

Examples related to excel

Examples related to vba