绕过vba函数中的最大字符串大小？ [英] Getting around the Max String size in a vba function?

问题描述

您可以在vba函数中的字符串中使用的最大字符数为255.
我正在尝试运行此函数

  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 
  

它在一定时间运行一个过程，传递一堆变量，但字符串太长。

更新：
遗憾的是，我确信这不是手表窗口
另外，它不是我正在处理的字符串的最大大小，它是vba函数中
a字符串的最大大小。

  Sub test（）
 Dim RunAt As Date 
 Dim RunWhat As字符串
 
的Runat =现在+ 0.00001 
 RunWhat = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa与 & _ 
 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa与 & _ 
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaa'，使254'a'的
 Application.OnTime EarliestTime：= RunAt，Procedure：='& RunWhat& 12' 
 
结束子
 
 
子aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa（M为整数）
 MSGBOX（ 它的工程！ &安培;米）
 End Sub 
  

但是，如果将12更改为123，则会中断
示例

  Sub test2（）
 Dim RunAt As Date 
 Dim RunWhat As String 
 
 RunAt = Now + 0.00001 
 RunWhat =aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa& _ 
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa& _ 
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa，这使254'a'的
 Application.OnTime EarliestTime：= RunAt，Procedure：='& RunWhat& 123' 
 
结束子
 
 
子aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa（M为整数）
 MSGBOX（ 它的工程！ &安培;米）
 End Sub 
  

此代码不起作用，我很确定是因为vba函数不能处理超过255个字符的字符串。
即使你在Excel中调用一个函数并给它一个255个字符的字符串也不起作用。

尝试单元格A1 = vlookup（真长字符串，A1：Z10,1），然后将真正长的字符串放在该范围内的某处。 vlookup将失败（不能找到它，但实际上不会这样做）

此外，我知道有一个最大长度子名，我就在它下面。对不起，它看起来很丑陋。

更新2：所以我刚刚结束了将变量打印到一个工作表，并获得了ontime时间调用的函数，从表中读取它们。 ：（

解决方案

这个作品在消息框中显示超过255个字符。

  Sub TestStrLength（）
 Dim s As String 
 Dim i As Integer 
 
s =
 For i = 1到500 
s = s&1234567890
下一个i 
 
 MsgBox s 
 End Sub 
  / pre> 
 
 
它看起来像1023个字符。
 
 
 
我还建议不要使用固定变量名称数字（例如 Var1 ， Var2 ， Var3 ，..  Var255 ）你使用一个数组，这是更短的声明，更容易使用 - 循环。
 
 
 
这里是一个例子：
  Sub StrArray（）
 Dim var（256）As Integer 
 Dim i As Integer 
 Dim s As String 
 
对于i = 1至256 
 var（i）= i 
下一个i 
 
s =Tims_pet_Robot 
对于i = 1至256 
s = s&& var（i） amp;
 Next i 
 
 SecondSub（s）
 End Sub 
 
 Sub SecondSub（s As String）
 MsgBox String length =& Len（s）
 End Sub 
  
更新了这一点，表明一个字符串可以比255个字符，并在子程序/函数中用作参数。这表示字符串长度为1443个字符。 VBA中的实际限制是每个字符串2GB。
 
 
 
或许您正在使用的API有一个问题，并且对该字符串有限制（例如固定长度字符串）。问题不在于VBA本身。
 
 
 
好的，我看到的问题是Application.OnTime方法本身。它的行为类似于Excel函数，因为它们只接受长度为255个字符的字符串。 VBA的程序和功能虽然没有这样的限制，也许这个限制适用于任何内置的Excel对象方法。
 
 
 
 
 
 
 更新： 
 
已更改 ...超过256个字符... 至 ...超过255个字符...  
 
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. :(
解决方案
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 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...

                        
这篇关于绕过vba函数中的最大字符串大小？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！