在 vba 函数中解决最大字符串大小? [英] Getting around the Max String size in a vba function?

问题描述

您可以在 vba 函数的字符串中使用的最大字符数是 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.

更新:遗憾的是，我确定它不是监视窗口.此外，它实际上并不是我正在处理的字符串的最大大小.这是最大的尺寸vba 函数中的字符串.

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.

例如这个函数有效.

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

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

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

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

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.

在单元格 A1 =vlookup("really long string", A1:Z10, 1) 中尝试，然后将非常长的字符串放在该范围内的某个位置.vlookup 将失败(不是找不到，但您实际上无法做到)

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.

更新 2:所以我只是将变量打印到工作表上，并让 ontime 调用的函数从工作表中读取它们.:(

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

推荐答案

这有效并且在消息框中显示超过 255 个字符.

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

消息框将字符串截断为 1023 个字符，但字符串本身可能非常大.

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

我还建议不要使用带有数字的固定变量名称(例如 Var1、Var2、Var3、... Var255) 使用数组.这是更短的声明，更易于使用 - 循环.

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.

这是一个例子:

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

更新此内容以显示字符串可以长于 255 个字符并以这种方式在子例程/函数中用作参数.这表明字符串长度为 1443 个字符.VBA 中的实际限制是每个字符串 2GB.

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.

也许您使用的 API 存在问题并且对字符串有限制(例如固定长度的字符串).问题不在于 VBA 本身.

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.

好的，我发现问题出在 Application.OnTime 方法本身.它的行为类似于 Excel 函数，因为它们只接受长度不超过 255 个字符的字符串.VBA 过程和函数虽然没有我已经展示的这个限制.也许这个限制适用于任何内置的 Excel 对象方法.

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.

更新:
将 ...longer than 256 characters... 更改为 ...longer than 255 characters...

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