Assign VBA Array to Worksheet Range
Author Message
Assign VBA Array to Worksheet Range

I have written a function to convert a column vector to a diagonal
matrix (ie matrix of zeros with the original column vector elements on
the diagonal).  I then wrote a subroutine that assigns an excel range
to a VBA array (say 3 rows, 1 col), calls myDiag function to create a
diagonal matrix (3 row, 3 col), and then assigns this matrix back to a
worksheet range.  The problem is that when I assign the matrix back to
a range, for some reason I need to assign the matrix to a range that is
1 row and 1 col larger than the matrix (ie 4 row, 4 col) in order view
the matrix, otherwise the matrix is truncated.

Sub testdiag()
a = Range("a1:a3").Value ' 3 row, 1 col
y = myDiag(a)              ' 3 row, 3 col
Range("b1:e4").Value = y ' should be 3 row, 3 col but has to be 4
row, 4 col
End Sub

Function myDiag(x As Variant)
Dim b() As Variant
r = UBound(x, 1)
c = UBound(x, 2)
ReDim b(r, r)
For i = 1 To r
For j = 1 To r
If i = j Then
b(i, j) = x(i, 1)
Else
b(i, j) = 0
End If
Next
Next
myDiag = b
End Function

Thanks,

Greg van Inwegen

Sent via Deja.com http://www.*-*-*.com/

Wed, 18 Jun 1902 08:00:00 GMT
Assign VBA Array to Worksheet Range
Greg,
Your problem originates from treating the square array as having a lower
bound of 1 in each dimension when it is actually zero.
Dim b(r,r)
is equivalent to Dim b(0 to r, 0 to r)  when you have option base 0 or no
option base specified.

You then hard code the start of you loop with a 1.  This alteration works
regardless of the option base setting:

Sub testdiag()
a = Range("a1:a3").Value ' 3 row, 1 col
y = myDiag(a)              ' 3 row, 3 col
mdim = UBound(a, 1) - LBound(a, 1) + 1
Set rng = Range("B1").Resize(mdim, mdim)
rng.Value = y
End Sub

Function myDiag(x As Variant)
Dim b() As Variant
lr = LBound(x, 1)
ur = UBound(x, 1)
c = LBound(x, 2)
ReDim b(lr To ur, lr To ur)
For i = lr To ur
For j = lr To ur
If i = j Then
b(i, j) = x(i, c)
Else
b(i, j) = 0
End If
Next
Next
myDiag = b
End Function

HTH,
Tom Ogilvy
MVP Excel

Quote:

> I have written a function to convert a column vector to a diagonal
> matrix (ie matrix of zeros with the original column vector elements on
> the diagonal).  I then wrote a subroutine that assigns an excel range
> to a VBA array (say 3 rows, 1 col), calls myDiag function to create a
> diagonal matrix (3 row, 3 col), and then assigns this matrix back to a
> worksheet range.  The problem is that when I assign the matrix back to
> a range, for some reason I need to assign the matrix to a range that is
> 1 row and 1 col larger than the matrix (ie 4 row, 4 col) in order view
> the matrix, otherwise the matrix is truncated.

> Sub testdiag()
>     a = Range("a1:a3").Value ' 3 row, 1 col
>     y = myDiag(a)              ' 3 row, 3 col
>     Range("b1:e4").Value = y ' should be 3 row, 3 col but has to be 4
> row, 4 col
> End Sub

> Function myDiag(x As Variant)
>     Dim b() As Variant
>     r = UBound(x, 1)
>     c = UBound(x, 2)
>     ReDim b(r, r)
>     For i = 1 To r
>         For j = 1 To r
>             If i = j Then
>                 b(i, j) = x(i, 1)
>             Else
>                 b(i, j) = 0
>             End If
>         Next
>     Next
>     myDiag = b
> End Function

> Thanks,

> Greg van Inwegen

> Sent via Deja.com http://www.deja.com/

Wed, 18 Jun 1902 08:00:00 GMT
Assign VBA Array to Worksheet Range
Hi Greg,

I suppose you ended up there because you wrote the function first, but
you can get to the same place with just the subroutine, either without
(Sub test1 below), or with (Sub test2 below) a Variant() array.

Option Base 1

Sub test1()
Set Rng = Range("A1:A3")
rngRank = Rng.Rows.Count
Set rngD = Rng(1, 2).Resize(rngRank, rngRank)
For i = 1 To rngRank
For j = 1 To rngRank
If i = j Then rngD(i, j).Value = Rng(i).Value Else rngD(i, j).Value =
0
Next
Next
End Sub

Sub test2()
Set Rng = Range("A1:A3")
rngRank = Rng.Rows.Count
Dim arr()
ReDim arr(rngRank, rngRank)
For i = 1 To rngRank
For j = 1 To rngRank
If i = j Then arr(i, j) = Rng(i).Value Else arr(i, j) = 0
Next
Next
Rng(1, 2).Resize(rngRank, rngRank).Value = arr
End Sub

Quote:

> I have written a function to convert a column vector to a diagonal
> matrix (ie matrix of zeros with the original column vector elements on
> the diagonal).  I then wrote a subroutine that assigns an excel range
> to a VBA array (say 3 rows, 1 col), calls myDiag function to create a
> diagonal matrix (3 row, 3 col), and then assigns this matrix back to a
> worksheet range.  The problem is that when I assign the matrix back to
> a range, for some reason I need to assign the matrix to a range that is
> 1 row and 1 col larger than the matrix (ie 4 row, 4 col) in order view
> the matrix, otherwise the matrix is truncated.

> Sub testdiag()
>     a = Range("a1:a3").Value ' 3 row, 1 col
>     y = myDiag(a)              ' 3 row, 3 col
>     Range("b1:e4").Value = y ' should be 3 row, 3 col but has to be 4
> row, 4 col
> End Sub

> Function myDiag(x As Variant)
>     Dim b() As Variant
>     r = UBound(x, 1)
>     c = UBound(x, 2)
>     ReDim b(r, r)
>     For i = 1 To r
>         For j = 1 To r
>             If i = j Then
>                 b(i, j) = x(i, 1)
>             Else
>                 b(i, j) = 0
>             End If
>         Next
>     Next
>     myDiag = b
> End Function

> Thanks,

> Greg van Inwegen

> Sent via Deja.com http://www.deja.com/

Wed, 18 Jun 1902 08:00:00 GMT
Assign VBA Array to Worksheet Range
testing

----------

Quote:

> Greg,
> Your problem originates from treating the square array as having a lower
> bound of 1 in each dimension when it is actually zero.
> Dim b(r,r)
> is equivalent to Dim b(0 to r, 0 to r)  when you have option base 0 or no
> option base specified.

> You then hard code the start of you loop with a 1.  This alteration works
> regardless of the option base setting:

> Sub testdiag()
>     a = Range("a1:a3").Value ' 3 row, 1 col
>     y = myDiag(a)              ' 3 row, 3 col
>     mdim = UBound(a, 1) - LBound(a, 1) + 1
>     Set rng = Range("B1").Resize(mdim, mdim)
>     rng.Value = y
> End Sub

> Function myDiag(x As Variant)
>     Dim b() As Variant
>     lr = LBound(x, 1)
>     ur = UBound(x, 1)
>     c = LBound(x, 2)
>     ReDim b(lr To ur, lr To ur)
>     For i = lr To ur
>         For j = lr To ur
>             If i = j Then
>                 b(i, j) = x(i, c)
>             Else
>                 b(i, j) = 0
>             End If
>         Next
>     Next
>     myDiag = b
> End Function

> HTH,
> Tom Ogilvy
> MVP Excel

> > I have written a function to convert a column vector to a diagonal
> > matrix (ie matrix of zeros with the original column vector elements on
> > the diagonal).  I then wrote a subroutine that assigns an excel range
> > to a VBA array (say 3 rows, 1 col), calls myDiag function to create a
> > diagonal matrix (3 row, 3 col), and then assigns this matrix back to a
> > worksheet range.  The problem is that when I assign the matrix back to
> > a range, for some reason I need to assign the matrix to a range that is
> > 1 row and 1 col larger than the matrix (ie 4 row, 4 col) in order view
> > the matrix, otherwise the matrix is truncated.

> > Sub testdiag()
> >     a = Range("a1:a3").Value ' 3 row, 1 col
> >     y = myDiag(a)              ' 3 row, 3 col
> >     Range("b1:e4").Value = y ' should be 3 row, 3 col but has to be 4
> > row, 4 col
> > End Sub

> > Function myDiag(x As Variant)
> >     Dim b() As Variant
> >     r = UBound(x, 1)
> >     c = UBound(x, 2)
> >     ReDim b(r, r)
> >     For i = 1 To r
> >         For j = 1 To r
> >             If i = j Then
> >                 b(i, j) = x(i, 1)
> >             Else
> >                 b(i, j) = 0
> >             End If
> >         Next
> >     Next
> >     myDiag = b
> > End Function

> > Thanks,

> > Greg van Inwegen

> > Sent via Deja.com http://www.deja.com/