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/
Before you buy.



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/
> Before you buy.



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/
> Before you buy.



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/
> > Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Assign values from Excel Range to VBA Array??

2. assigning a range to a VBA array

3. ARRAY PROBLEM: ASSIGNING ARRAY TO A RANGE

4. Assigning variant array returned by function to a range

5. Assign an array to a range

6. Assign range of cells to array and inspect contents

7. How to assign range to array?

8. assign range value to array

9. Assigning (column)range values to one dimensional array?

10. assigning ranges to variants, creating an array


 
Powered by phpBB® Forum Software © phpBB Group