Passing array of strings to excel
 

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 he
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 he Error 1004

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

Any guess?
Kind regards, Nikolay

Passing array of strings to excel
 

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

--
Regards,
Tom Ogilvy


"Nikolay Belyh" wrote:

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 he
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 he Error 1004

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

Any guess?
Kind regards, Nikolay

Passing array of strings to excel
 

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 Belyh" wrote in message
oups.com...
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 he
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 he Error 1004

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

Any guess?
Kind regards, Nikolay

Passing array of strings to excel
 

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

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Nikolay Belyh" wrote:

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 he
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 he Error 1004

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

Any guess?
Kind regards, Nikolay

Passing array of strings to excel
 

On 18 ÍÁÊ, 20:25, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
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.