Parameter string cannot exceed 255 characters?

[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