Parameter string cannot exceed 255 characters?

[jute]

Hi,

I was having trouble with a string manipulation function that I wrote in Excel. While it worked fine with some strings, it returned "#VALUE!" errors. Took me hours until I finally figured out what was wrong.

I found out that if you write a custom VBA function in Excel that accepts strings as parameters, the length of the string cannot exceed 255 characters. I tried this simple function:

Function UC(str As String) As String
    UC = UCase(str)
End Function

Then in any cell, type, say uc(Rept("a"),255) returns 255 A's, but uc(Rept("a"),256) would return an error.

And I thought Strings can hold up to 2^31 characters. Is there a workaround to this?

[alex_read]

Works fine for me with Excel 2000 - don't know if the byval call makes any difference, but this code works for me:

Private intLoopCounter As Integer
 
Private Sub CommandButton1_Click()
    Dim strTemp As String
    
    For intLoopCounter = 1 To 300
        strTemp = strTemp & "1"
    Next intLoopCounter
    
    strTemp = Change1To2(strTemp)
    Worksheets(1).Range("A1").Value = CStr(strTemp)
    MsgBox strTemp
End Sub
 
Private Function Change1To2(ByVal strTemp As String) As String
    strTemp = Replace(strTemp, "1", "2")
    Change1To2 = strTemp
End Function

[jute]

Thanks alex_read, your code works fine in my Excel (XP) as well. but if I write up another string-manipulating function in VBA and call the function from within a cell using "A1" (the result of Change1To2()) as parameter, I'd get the same result.

It appears that this limitation only applies to custom VBA functions called within cells. There is no such problem if you use Excel's built-in function or call your custom function from within the code (like your example).

I gave "A1" another try with another simple function:

Function Length(str As String) As Long
' If Len(Str) > 255 Then MsgBox "String too long"
Length = Len(str)
End Function

Then I compared Excel's built-in Len() function with my Length(), which is supposed to yield the same result.
In cell #1 I typed "=len(A1)" ==> returned "300"
And in cell 2 I entered "=length(A1)" ==> returned "#VALUE!"

Even if I uncommented the If statement I'd still get "#VALUE!" without any message box. The function simply won't run with >255 strings.

Is this a common issue or is it just me?

P.S. For my purpose, I need to run my function from cells.

[jute]

Anyone?

[WorkHorse]

Originally posted by jute
Anyone?

Hm....

I haven't encountered this before, and I couldn't really find any anything on the Internet where other people have had the same problem. I also couldn't find any documentation on this. But here's what seems to be happening...

I try this:

Sub UCTest()
    x = UC(WorksheetFunction.Rept("a", 256))
End Sub

Works just fine.

I try entering a cell formula "=UC(REPT("a", 256))". It fails. So the problem is with calling the function from a cell formula.

Now, we debug the code:

Function UC(str) As String
    
    Debug.Print str
    
    On Error GoTo ErrorHandle
 
    UC = UCase(str)
    
    Exit Function
   
ErrorHandle:
    
    Debug.Print Err.Number & " " & Err.Description
    
End Function

I didn't declare str As String (or any other data type so that the function will always run and pick up whatever value is passed as str. I added a debug to print the value of str and an error trap. Now we enter cell formula "=UC(REPT("a", 256))". The immediate windows shows our results:

Error 2015
13 Type mismatch

So the UCase function is failing as a type mismatch. Why? Because the value of str is Error 2015, and not a string value. Error 2015 is an "Application-defined or object-defined error". Why wasn't a value passed to str?

Excel function parameters will accept a reference to a string with length of up to 32767 (32k or 2^15 total possible lengths). So if I set A1 "=REPT("a", 32767)" and A2 "=UC(A1)", there is no problem. But what if we pass a value instead of a reference? For a cell we enter "=UC("aaaaaa...a)" so that we just type a string that is 256 characters. If the string we enter is more than 255 characters, the Excel application raises the error "Formula is too long". Hey, there's our Application-defined error!

So we can pass a reference to a string up to 32k, but we can only pass a string value up to 255. So "=UC(REPT("a", 256))" doesn't raise the "Formula is too long" error because it has to run the REPT function to know what the return value is, but when REPT returns a value longer than 255, Excel can't evaluate the value just as if we had typed in "a" 256 times.

The text entered for the cell formula is just text that Excel must evaluate. It appears Excel resolves the inner functions and replaces those value into a new formal string and just keeps working functions out until it gets to he main function called.

In my opinion this is a bug. I can understand a 255 limit on typing strings in formula to keep the Excel file reasonably manageable, but I see no reason why a function that results in a 255+ length string should also cause an error. To me it seems MS left in the “raise error if parameter length is more than 255 check for “Formula is too long” without thinking that the string length may be the result of Excel internally evaluating functions to long strings rather than a user entered long string.

How to work around it? Only thing I can think is for any cases where your string will be 255+, use a separate cell to create the parameter string, then pass that cell value in the formula like we did with "=UC(A1)".

Hope this at least gives some clues about what appears to be going on.

Here's some related MSDN articles. It looks like MS has a number of problems with 255 string limits in Excel.MSDN: Excel string 255

[jute]

Thanks WorkHorse. You cleared the whole thing for me and that workaround was great (such a simple solution and it didn't even occur to me )

I guess I'll just have to use additional cells as reference to those that contain long strings.
You saved my day.