Passing array of strings to excel

[Nikolay Belyh]

Hello.

I'm trying to pass data array to Excel.

If the data is an array of (short?) strings, it works.
If the data is an array of (long?) strings it fails.
If I pass each item from the array of (long?) strings separately, it
works again!

WTF?

I have deduced the problem to the simplest case:
passing array of two (two!) items. The code is below (Excel VBA)

(The full code is placed code here:
http://nbelyh.googlepages.com/fail.zip)

Dim v(1 To 2, 1 To 1) As String

v(1, 1) = a
v(2, 1) = b

Set rg1 = ws.Range("A1")
rg1.Value2 = v(1, 1) ' works

Set rg2 = ws.Range("A2")
rg2.Value2 = v(2, 1) ' works

Set rg = ws.Range(rg1, rg2)
rg.Value2 = v ' <---- fails here: Error 1004

(values "a" and "b" are rather long, find'em in attached file)

Any guess?
Kind regards, Nikolay

 

[Tom Ogilvy]

Sub abc()
Dim v(1 To 2, 1 To 1) As String

v(1, 1) = "aaa"
v(2, 1) = "bbb"
Set ws = ActiveSheet
Set rg1 = ws.Range("A1")
'rg1.Value2 = v(1, 1) ' works

Set rg2 = ws.Range("A2")
'rg2.Value2 = v(2, 1) ' works

Set rg = ws.Range(rg1, rg2)
rg.Value2 = v
End Sub

worked fine for me.

xl2003

 

[Bernie Deitrick]

Nikolay,

A little testing shows that with strings longer than 911(!) characters, the array pass method fails.
I'm sure it is just one of those strange limitations of Excel.... There probably isn't much of a
performance hit to simply loop through your cells and string array doing the assignment one cell /
string at a time:

Set rg = Range(rg1, rg2)
For i = 1 To rg.cells.count
rg.Cells(i).Value2 = v(i, 1)
Next i


HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

Sorry, missed the the short string/long string part.

 

[Nikolay Belyh]

Nikolay,

A little testing shows that with strings longer than 911(!) characters, the array pass method fails.

Bernie, thank you for response.

Sounds like a perverted joke from Excel developers, does it..?
Have I won something?

Done it with the loop.. Just curious
Regards.