在 vba 函数中解决最大字符串大小？

Getting around the Max String size in a vba function?

提问by Ommit

The max number of characters you can use in string in a vba function is 255. I am trying to run this 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

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.

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

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

但是，如果您将 12 更改为 123，它会破坏示例

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.

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

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)

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

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

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

采纳答案by Kevin Brock

This works and shows more than 255 characters in the message box.

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

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.

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

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.

我还建议您使用数组而不是使用带有数字的固定变量名称（例如Var1, Var2, Var3, ... Var255）。这是更短的声明，更易于使用 - 循环。

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.

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

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.

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

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.

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

---

---

Update:
changed ...longer than 256 characters...to ...longer than 255 characters...

更新：
改...longer than 256 characters...到...longer than 255 characters...

回答by DrOli

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:

我可能在这里遗漏了一些东西，但你为什么不能用所需的大小声明你的字符串？例如，在我的 VBA 代码中，我经常使用以下内容：

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.

它提供了一个 1k 的字符串。显然，您可以在 Excel 和可用内存等的更大范围内使用您喜欢的任何声明。

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.

在某些情况下，这可能有点低效，您可能希望使用 Trim(AString) 之类的构造来消除任何多余的尾随空白。尽管如此，它很容易超过 256 个字符。

回答by Prutswonder

Are you sure? This forum threadsuggests it might be your watch window. Try outputting the string to a MsgBox, which can display a maximum of 1024 characters:

你确定吗？这个论坛帖子表明它可能是您的观察窗口。尝试将字符串输出到 MsgBox，它最多可以显示 1024 个字符：

MsgBox RunMacros

回答by Przemyslaw Remin

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.

这个测试表明 VBA 中的字符串至少可以有 10^8 个字符长。但是如果你把它改成 10^9 你就会失败。

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.

所以不要被中间窗口编辑器或 MsgBox 输出误导。

回答by ArranSpencer

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

回答by Eduardo Recoder

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

Excel 只显示 255 个字符，但实际上如果保存的字符超过 255 个，要查看完整的字符串，请在立即窗口中查询

Press Crl + Gand type ?RunWhatin the immediate window and press Enter

按Crl + G并?RunWhat在即时窗口中键入并按Enter