Excel: type() and WorksheetFunction.IsText() fail for long strings

user_5803910 0
Viewed 509

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

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

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)

Cells A3:D4 result:

TRUE   TRUE     2   8
TRUE   FALSE   16   8

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".

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?

vba excel
2 Answers

WorksheetFunction.IsText seems fine if vArg.Value is used instead of the Range object directly. =CELL("type", A2) works too.

The TYPE function issue seems like undocumented limitation. It seems to be related to the "Text values in formulas are limited to 255 characters." error, because the documenentation Remarks mentions:

You cannot use TYPE to determine whether a cell contains a formula. TYPE only determines the type of the resulting, or displayed, value. If value is a cell reference to a cell that contains a formula, TYPE returns the type of the formula's resulting value.

user_13831680

A Workaround!? (Nope!)

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

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.

Happens to me all the time.

user_98140690
Related