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
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
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 :)
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.