Passing array of strings to excel

  • Thread starter Thread starter Nikolay Belyh
  • Start date Start date
N

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
 
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
 
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
 
Nikolay,

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

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.
 
Back
Top