Passing array of strings to excel

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
 
T

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
 
B

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
 
N

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top