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. :(
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.
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
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
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.
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
Source: Stackoverflow.com