PDA

View Full Version : [SOLVED] Maximum size of String Variable



tools
04-28-2008, 11:24 PM
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
04-29-2008, 12:40 AM
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
04-29-2008, 12:46 AM
You have not dimmed str as a string.

Yeah he did, half way down.

mdmackillop
04-29-2008, 12:52 AM
footinmout

tools
04-29-2008, 01:16 AM
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
04-29-2008, 04:30 AM
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
04-29-2008, 05:34 AM
I change this line for clarity, but found no problem with your macro

st = st & arr(i) & i & ","

tools
04-29-2008, 05:43 AM
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
04-29-2008, 05:49 AM
Your problem is not the string, it's the limit on the length of a Data Validation formula (255).

tools
04-29-2008, 06:05 AM
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
04-29-2008, 06:08 AM
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
04-29-2008, 06:12 AM
I had tried doing the same but had faced few problems. I will ask for any help if needed :)

Thanks and Regards

tools
04-29-2008, 09:07 PM
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
04-30-2008, 12:46 AM
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
04-30-2008, 03:02 AM
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
04-30-2008, 03:25 AM
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
04-30-2008, 03:39 AM
Thanks a lot :)

rory
04-30-2008, 04:20 AM
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