How to insert array into range

[olsen.newsaccount]

Hi

I have a Visual Basic program (Visual basic.NET 2005) where I automate
Excel (2003).
In the program, I have a two dimensional array of strings, that I need
to paste into a range in Excel.
How do I do it?

Best regads, Lars Olsen
Denmark

 

[Guest]

Sub Macro1()
Dim s(10, 10) As String
For i = 1 To 10
For j = 1 To 10
Cells(i, j).Value = s(i, j)
Next
Next
End Sub

Use the above for a range of the same dimensions as the string array.
Or use an Offset if you need it.

 

[Robert ap Rhys]

Sub Macro1()
Dim s(10, 10) As String
For i = 1 To 10
For j = 1 To 10
Cells(i, j).Value = s(i, j)
Next
Next
End Sub

Hi Gary,

It much easier, and a whole lot faster to create a reference to a range
object with the same dimensions as the array, and then use the Value
property:

Private Sub Test()
Dim str(1 To 7, 1 To 2) As String
Dim i As Integer
For i = 1 To 7
str(i, 1) = Format(i, "dddd")
str(i, 2) = Format(i * 29, "mmmm")
Next
With ThisWorkbook.Worksheets(1)
.Range(.Cells(14, 3), _
.Cells(14 + 6, 3 + 1)).Value = str
End With
End Sub

The OP will have to translate to VB.NET - the range reference and the
dimensions of the array (all arrays are zero based in .NET).

Robert

 

[olsen.newsaccount]

Hi Robert

I tried this and it worked nicely. Thanks )

/Lars

 

[Dana DeLouis]

Another option to this excellent idea is to use Resize. This lets Excel do
the hard math...

Private Sub Test()
Dim str(1 To 7, 1 To 2) As String
Dim r As Integer
For r = 1 To 7
str(r, 1) = Format(r, "dddd")
str(r, 2) = Format(r * 29, "mmmm")
Next

Range("A14").Resize(UBound(str, 1), UBound(str, 2)) = str
End Sub

 

[Robert ap Rhys]

Hi Dana,

Another option to this excellent idea is to use Resize.

A very good option. Much more intuitive to those not ingrained in doing it
the other way. You just need to be aware that the Resize property doesn't
actually /Resize/ anything, but returns a completely different range object.
Why use a verb for a property name, MS?

This lets Excel do
the hard math...

What values of 'hard' are you using? ;-)

Rob

 

[Dana DeLouis]

Hi. Well, maybe that wasn't a good example. :>)
What I was thinking was something along this line...

Dim v(1 To 189, 1 To 127)

1: Range("AF143:FA330") = v
2: Range("AF143").Resize(189, 127) = v

Both options put the array 'v' into the spreadsheet.
For me, I have a "hard" time looking at option 1 as telling if it is
correct.
In fact, it's off by 1 in both dimensions.
Again, it was just another option for the op.

 

[Dave Peterson]

And if you had:

dim v(-12 to 32, 372 to 424)

it would be even more difficult for lots of humans, well, at least me.