Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing strings as arguments to xll functions. | Excel Programming | |||
Passing array of strings from DLL function to VBA | Excel Programming | |||
passing strings | Excel Programming | |||
passing strings | Excel Programming | |||
passing strings | Excel Programming |