255 char cutoff populating cells with a variant array

R

rljenki1

New Member
Joined
Oct 24, 2003
Messages
5
Hello All,
I am having a problem populating an excel (Excel 2k on win2k) worksheet. I am using a two dimensional variant array which contains strings of various sizes, and generally this technique works great.

The code is something like

worksheet(1).Range(cells(1,1), cells(100,100)).resize(100,100) = myVariantArray

The problem I have is that I can't populate a cell with more the 255 chars at a time. The data is very often larger than this and so gets cut off at 255 chars. There is no error message or anything, it works as normal, but all the data isn't loaded.

I have been stuck on this problem for ages (weeks!) and I have developed a pretty clunky work-around but efficiency is key and I would love to be able to use the variant array method.

If anyone has any ideas they would be very appreciated.

Ross J.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Search the xl help on 'Excel specifications and limits' - 255 characters is for column width

HTH
 
Upvote 0
What happens if you run this on a blank worksheet:

Code: 
Sub Test()
    Dim MyArr() As Variant
    Dim WF As WorksheetFunction
    Dim Rng As Range
    Dim c As Range
    Dim Msg As String
    Set WF = WorksheetFunction
    MyArr = Array(WF.Rept("a", 500), WF.Rept("b", 1000), WF.Rept("c", 1500))
    Set Rng = Range(Cells(1, 1), Cells(1, 3))
    Rng.Value = MyArr
    For Each c In Rng
        Msg = Msg & c.Address(False, False) & " is " & Len(c) & " characters" & vbCrLf
    Next c
    MsgBox Msg
End Sub

For me there is no character limit (Excel 2000).
 
Upvote 0
That seems to work fine on my home PC (Excel 2k on XP Pro). The only difference I can see with your code is that you don't use the .Resize(x,y) method.

I'll try it on Monday and let you know how I get on.

Thanks for the help,

Ross J. :biggrin:
 
Upvote 0
I don't know why you are using Resize - it's not doing anything. The Range is already 100x100.

Code: 
Sub Test()
    MsgBox Range(Cells(1, 1), Cells(100, 100)).Count
    MsgBox Range(Cells(1, 1), Cells(100, 100)).Resize(100, 100).Count
End Sub
 
Upvote 0
I finally got around to trying this in work today and I am hitting a character limit of 1823 using this method. The whole string definitely goes into the array but Excel can't write the whole lot out.

Anyone got any ideas about how to get around this one?
 
Upvote 0
From Excel specifications:

Length of cell contents (text): 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

I get a limit of 1823 using the Rept function in VBA too. But on a worksheet the limit is 32767. In A1 enter:

=REPT("a",5000)

and in A2:

=LEN(A1)

returns 5000.

My code was only to prove that there was not a limit of 255 characters.
 
Upvote 0
Thanks for the speedy response.

So what your saying is that there is no way to use the variant method to populate cells with more than 1823 characters?

Have to write a less clunky workaround I suppose.

Thanks again.
 
Upvote 0
rljenki1 said:
So what your saying is that there is no way to use the variant method to populate cells with more than 1823 characters?
Click to expand...

It seems that there is indeed a limit to the length of an item in a variant array that can be transferred to a range.

I got the same result when using the VBA String function. It's not a limit of the function (Rept or String), because this worked:

Code: 
Cells(1, 4).Value = String(2000, "d")
MsgBox Len(Cells(1, 4).Value)

Neither is it a limit of a variant, because this also worked:

Code: 
Sub T2()
    Dim MyArr() As Variant
    Dim x As Integer
    Dim Msg As String
    MyArr = Array(String(500, "a"), String(1000, "b"), String(2000, "c"))
    For x = LBound(MyArr) To UBound(MyArr)
        Msg = Msg & "Item " & x & " is " & Len(MyArr(x)) & " characters" & vbCrLf
    Next x
    MsgBox Msg
End Sub
 
Upvote 0
Sounds like a job for Scooby Doo!

It a pretty big flaw I'd say. In my old job we had a support contract with MS so we could call them up about things like this! Alas no more...

I had a quick google search with no luck. You would think more people would have hit this barrier. This must be a pretty popular method of populating a worksheet.

I have written another workaround anyway. We use defined names a lot so I had to loop around writing those, so I just added an if statement along the lines of 'If this string is bigger than the limit, write out the content.'

Thanks for all your help on this one.

Ross J.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Remove Duplicates with Dynamic Columns Array
Replies
0
Views
283
trough
T
V
  • Solved
Delete column numbers based on dynamic array population
Replies
7
Views
681
Viper147
V
S
  • Question
UDF return variant throws #SPHILL! error inconsistently
Replies
3
Views
161
StephenCrump
S
R
  • Question
Help to sum all cells above until blank cell
Replies
3
Views
167
RemyMoo
R
D
  • Question
Completely cleaning account numbers and storing with a specific format
Replies
2
Views
591
diversification
D

Forum statistics

Threads
1,214,591
Messages
6,120,429
Members
448,961
Latest member
nzskater

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top