Maximum size of String Variable

[tools]

Hi all

I have an array of 40 elements
I am assigning all the elements to a string variable.
But the problem is the string variable does not contain all the elements in it

This is the code that i have written

Dim arr()
Dim x As Integer
x = 40
ReDim arr(x)
For i = 0 To UBound(arr)
    arr(i) = "Tools Team "
Next
Dim str As String
For i = 0 To UBound(arr)
    str = str & arr(i) & ","
Next

Thanks

[mdmackillop]

String Data Type
String variables are stored as sequences of unsigned 16-bit (2-byte) numbers ranging in value from 0 through 65535. Each number represents a single Unicode character. A string can contain up to approximately 2 billion (2 ^ 31) Unicode characters.

BTW,
It is preferable not to use function names such as Str as variable names.
You have not dimmed str as a string.

[Bob Phillips]

You have not dimmed str as a string.

Yeah he did, half way down.

[mdmackillop]

[tools]

This is what I have in the str variable after i run my macro

"Tools Team ,Tools Team ,Tools Team ,Tools Team ,Tools Team ,Tools Team ,Tools Team ,Tools Team ,Tools Team ,Tools Team ,Tools Team ,Tools Team ,Tools Team ,Tools Team ,Tools Team ,Tools T"

Can anyone find out the error?

[tools]

I have attached an excel sheet which reproduces the actual error

If u check the data in the validation list from A1 to A10 the values
inside the list are incomplete.
The macro is present inside the sheet.

Thanks and Regards

[mdmackillop]

I change this line for clarity, but found no problem with your macro
[vba]
st = st & arr(i) & i & ","

[/vba]

[tools]

I changed that line in my macro and saw that it was displaying data only till the 19th row.

Any idea why is it acting a little weird.?

Thanks and Regards

[rory]

Your problem is not the string, it's the limit on the length of a Data Validation formula (255).

[tools]

Thanks rory

Is there a solution to this, other than creating a temporary sheet and storing my values in it and refering to that location.

Thanks and Regards

[rory]

No, not that I know of. Bear in mind that you will need to use a defined name if your list is stored on a sheet other than the one where the data validation is set.

[tools]

I had tried doing the same but had faced few problems. I will ask for any help if needed

Thanks and Regards

[tools]

Hi all,

This is what i tried

Sub TEST()
Dim arr()
Dim x As Integer
Dim st As String
x = 40
ReDim arr(x)
For i = 0 To UBound(arr)
    arr(i) = "Tools Team" & i
Next
For i = 0 To UBound(arr)
    st = st & arr(i) & i & ","
Next
col = "C1:C10"
Sheet2.Select
For i = 0 To UBound(arr)
    Range("C" + CStr(i + 1)).Value = arr(i)
Next
ActiveWorkbook.Names.Add Name:="NewName", _
         RefersTo:="Sheet2!$C$1:$C$41", Visible:=True
Sheet1.Select
With Range(col).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Operator:=xlBetween, Formula1:=NewName
    .IgnoreBlank = True
    .InCellDropdown = True
End With
End Sub

When i check the validation list it shows me #N/A .
Is this code correct.?
I have uploaded the excel sheet for reference

Thanks and Regards

[Bob Phillips]

Sub TEST()
' TEST Macro
' Macro recorded 4/29/2008 by Amey M
col = "C1:C10"
If (Len(st) > 255) Then
    Sheet2.Select
    For i = 0 To UBound(arr)
        Range("C" + CStr(i + 1)).Value = arr(i)
    Next
    ActiveWorkbook.Names.Add Name:="NewName", _
    RefersToR1C1:="=Sheet2!R1C3R41C3", Visible:=True
    Sheet1.Select
    With Range(col).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:=NewName
        .IgnoreBlank = True
        .InCellDropdown = True
    End With
Else
    With Range(col).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="=NewName"
        .IgnoreBlank = True
        .InCellDropdown = True
   End With
End If
End Sub

[tools]

Hi XLD

It throws an error saying that the formula you typed contains error.

it throws an error at the following line

ActiveWorkbook.Names.Add Name:="NewName", _
        RefersToR1C1:="=Sheet2!R1C3R41C3", Visible:=True

The error vanishes when if i change it to

ActiveWorkbook.Names.Add Name:="NewName", _
        RefersToR1C1:="Sheet2!R1C3R41C3", Visible:=True
        Sheet1.Select

But then the listbox contains the value #N/A

Thanks and Regards

[Bob Phillips]

Sorry left lots of garbage in there. This is all that you need

Public Sub TEST()
' TEST Macro
' Macro recorded 4/29/2008 by Amey M
Sheet2.Select
   ActiveWorkbook.Names.Add Name:="NewName", _
   RefersToR1C1:="=Sheet2!R1C3:R41C3", Visible:=True
   Sheet1.Select
With Range("C1:C10").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Operator:=xlBetween, Formula1:="=NewName"
    .IgnoreBlank = True
    .InCellDropdown = True
   End With
End Sub

[tools]

Thanks a lot

[rory]

If you need to populate Sheet2 first, something like this will work:

Sub TEST()
' TEST Macro
' Macro recorded 4/29/2008 by Amey M
Dim arr()
   Dim x As Integer
   Dim st As String
   x = 40
   ReDim arr(x)
   For i = 0 To UBound(arr)
    arr(i) = "Tools Team" & i
   Next
For i = 0 To UBound(arr)
    st = st & arr(i) & i & ","
   Next
col = "C1:C10"
For i = 0 To UBound(arr)
    Sheet2.Range("C" + CStr(i + 1)).Value = arr(i)
   Next
ActiveWorkbook.Names.Add Name:="NewName", _
   RefersTo:="=Sheet2!$C$1:$C$41", Visible:=True
With Sheet1.Range(col).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Operator:=xlBetween, Formula1:="=NewName"
    .IgnoreBlank = True
    .InCellDropdown = True
   End With
End Sub