Easy One - VBA Macro causes Excel to !VALUE# with more than 255 character string

[recarv]

Can anyone tell me how to display more than 255 characters in a particular cell without having Excel convert the string to #VALUE!

I'm trying to display comments submitted to a DB in each cell and the more verbose people are causing problems.

[RobDog888]

You could probably format the cell as text. Then either enable word wrap or merge cell.

[recarv]

It already is....but I think it might have to do with the version of Excel....I'm using 2003 and another person is using Office 2000 and is getting the problem.


Do you know if this is version related and if there is a workaround?

[RobDog888]

I can test it out on Excel 2000 for you to see what I can determine.
I have it on our terminal server. Be back.

[dglienna]

I started with 255 "x"'s and copied into Excel XP, and then nearly doubled it in size. It did not change anything. It took up nearly 4 lines, but fit into the cell until I edited it.

[RobDog888]

His systems are running Office 2000 and 2003 only, not Office 2002.

[RobDog888]

Ok, think I got a possible cause and solution here:

Cause: Entering text when the formula requires a number or a logical value, such as TRUE or FALSE. Microsoft Excel cannot translate the text into the correct data type.

Solution: Make sure the formula or function is correct for the required operand or argument, and that the cells that are referenced by the formula contain valid values. For example, if cell A5 contains a number and cell A6 contains the text "Not available", the formula =A5+A6 will return the error #VALUE!. Use the SUM worksheet function in the formula as follows to add the two values (the SUM function ignores text):
=SUM(A5:A6)


Cause: Entering or editing an array formula, and then pressing ENTER.

Solution: Select the cell or range of cells that contains the array formula, press F2 to edit the formula, and then press CTRL+SHIFT+ENTER.


Cause: Entering a cell reference, a formula, or a function as an array constant.

Solution: Make sure the array constant is not a cell reference, formula, or function. For more information about items that can be used as array constants, click .


Cause: Supplying a range to an operator or a function that requires a single value, not a range.

Solution: Change the range to a single value.
Change the range to include either the same row or the same column that contains the formula.


Cause: Using a matrix that is not valid in one of the matrix worksheet functions.

Solution: Make sure the dimensions of the matrix are correct for the matrix arguments.


Cause: Running a macro that enters a function that returns #VALUE!.

Solution: Make sure the function is not using an incorrect argument.

[recarv]

the offending code is the following:

ws.Range(Cells(i, 4), Cells(i, 4)).Value = VBA.CStr(rst!comments)

where rst!comments is a field of type mediumtext in a mySQL database. rst is an ADODB.Recordset

and the len of Cstr(rst!commments) is > 255.

does this help

[RobDog888]

Maybe try using the explicit property instead of relying on the default. It could be that it is
trying to place the object in the cell instead of the value.

ws.Range(Cells(i, 4), Cells(i, 4)).Value = VBA.CStr(rst.fields("comments").Value)