Excel:类型()和WorksheetFunction.IsText()对于长字符串失败 [英] Excel: type() and WorksheetFunction.IsText() fail for long strings

问题描述

其他人是否发现以下行为?我正在Windows 7下运行Excel 2007.

Do other people find the following behavior? I'm running Excel 2007 under Windows 7.

在Excel VBA中:

In Excel VBA:

Public Function bTest_IsText(vArg As Variant) As Boolean
bTest_IsText = WorksheetFunction.IsText(vArg)
End Function

Public Function nTest_VarType(vArg As Variant) As Integer
nTest_VarType = VarType(vArg)
End Function

在Excel工作表中:

In Excel worksheet:

A1: =REPT("-", 255)
A2: =REPT("-", 256)
A3: =ISTEXT(A1)
B3: =bTest_IsText(A1)
C3: =TYPE(A1)
D3: =nTest_VarType(A1)
A4: =ISTEXT(A2)
B4: =bTest_IsText(A2)
C4: =TYPE(A2)
D4: =nTest_VarType(A2)

单元格A3:D4结果:

Cells A3:D4 result:

TRUE   TRUE     2   8
TRUE   FALSE   16   8

type()的返回值2和16具有 VarType()的返回值8(= vbString )具有

The return values 2 and 16 of type() have the meaning "Text" and "Error value", respectively. The return value 8 (= vbString) of VarType() has the meaning "String".

因此，如果传递给它们的参数超过255个字符，则Excel中的 type()和Excel VBA中的 WorksheetFunction.IsText()似乎不起作用.Excel中的 IsText()没有此限制. WorksheetFunction.IsText()失败的一种解决方法是测试 VarType(...)= vbString (反之可能更好).

So it appears that type() in Excel and WorksheetFunction.IsText() in Excel VBA do not work if the argument passed to them has more than 255 characters. IsText() in Excel does not have this limitation. A workaround for the failure of WorksheetFunction.IsText() is to test for VarType(...) = vbString (which may be better anyway).

这些合法的错误还是我做错了什么?

Are these legitimate bugs or am I doing something wrong?

是否有编译经过验证的错误的地方，所以当我看到类似的东西时，我不必怀疑自己是否会发疯?

Is there a place where verified bugs are compiled so I don't have to wonder if I'm going crazy when I see something like this?

推荐答案

一种解决方法！?(不！)

Public Function bTest_IsText(vArg As Variant) As Boolean
  Dim tArg As Variant
  tArg = vArg
  bTest_IsText = WorksheetFunction.IsText(tArg)
End Function

使用此方法后，请弄清答案:

Came up with the answer after using this:

Public Function bTest_IsText(vArg As String) As Boolean
  bTest_IsText = WorksheetFunction.IsText(vArg)
End Function

无论您在单元格"A2"中放置什么，总会得出TRUE.

which always resulted in TRUE, whatever you put in cell 'A2'.

因此，我得出的结论是您的功能没有按照您的预期做.

So I would conclude that your function wasn't doing what you thought it would do.

一直在我身上

这篇关于Excel:类型()和WorksheetFunction.IsText()对于长字符串失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！