Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: VBA Array Basics

  1. #1
    Board Regular
    Join Date
    Apr 2008
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Array Basics

    Lets say I have this....


    PHP Code:

    Dim B 
    as Range

    set B 
    activesheet.range("A1:C3")


    Dim a(3) As Double

    a
    (1)= 1.2
    a
    (2)= 3.6
    a
    (3)= .27 
    Note: B is a 3x3 Matrix of values in your sheet

    How would I perform matrix multiplication of B * array "a"?

    My main problem is with how to reference then entire array "a" and how the data is being stored (is the array a 3x1 or a 1x3). And if there is a VBA MMULT command or if building my own or using the worksheet one are my only options.

    Thanks

  2. #2
    Board Regular WaxonWaxov's Avatar
    Join Date
    Apr 2009
    Location
    Charlotte, NC, USA
    Posts
    141
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Array Basics

    Should A be 3x3 as well?
    ~I know more about Excel than I ever wanted to.

  3. #3
    Board Regular
    Join Date
    Apr 2008
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Array Basics

    No. You can multiply a 3x3 Matrix by a 3x1 matrix and the result is another 3x1 matrix.

    If A were also 3x3 then you would have 3x3 times 3x3 resulting in a 3x3.


    I don't really need help with the linear algebra just using arrays in VBA

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Array Basics

    Example:

    Code:
    Sub Test()
        Dim A(1 To 3) As Double
        Dim B As Range
        Dim C
        A(1) = 1.2
        A(2) = 3.6
        A(3) = 0.27
        Set B = Range("A1:C3")
        With Application.WorksheetFunction
            C = .MMult(B.Value, .Transpose(A))
        End With
        Range("E1:E3").Value = C
    End Sub
    Microsoft MVP - Excel

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,714
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA Array Basics

    Or you can declare a as a 3 row array:
    Code:
    Dim B As Range, var
    Set B = ActiveSheet.Range("A1:C3")
    
    Dim a(1 To 3, 1 To 1) As Double
    a(1, 1) = 1.2
    a(2, 1) = 3.6
    a(3, 1) = 0.27
    var = Application.WorksheetFunction.MMult(B, a)

  6. #6
    Board Regular
    Join Date
    Apr 2008
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Array Basics

    Thank you guys. I am so close. Here is what I have....

    The problem is an error I'm getting trying to set c = to the matrix multiplication.


    PHP Code:

    Sub Arry_Code
    ()
    Dim i As Integer
    Dim j 
    As Integer
    Dim N 
    As Integer
    Dim B 
    As Range

    Set B 
    ActiveSheet.Range("A8:C10")


    InputBox("Please enter an integer number of simulations""Simulation Size")

    Dim a(1 To 3) As Double
    Dim c
    (1 To 10000000) As Double
    Dim d
    (1 To 10000000) As Double
    Dim e
    (1 To 10000000) As Double
    Dim f
    (1 To 10000000) As Double


    For 1 To N

        
    For 1 To 3

            a
    (j) = (Rnd() - 0.5) * Sqr(12)

        
    Next
       
        With Application
    .WorksheetFunction
           c 
    = .MMult(B.Value, .Transpose(a))
        
    End With

        d
    (i) = c(1)
        
    e(i) = c(2)
        
    f(i) = c(3)
        
    Next

    Dim O 
    As Range

    Set O 
    ActiveSheet.Range("E3:G5")

    O(11).Value WorksheetFunction.Covar(dd)
    O(21).Value WorksheetFunction.Covar(ed)
    O(22).Value WorksheetFunction.Covar(ee)
    O(31).Value WorksheetFunction.Covar(fd)
    O(32).Value WorksheetFunction.Covar(fe)
    O(33).Value WorksheetFunction.Covar(ff)
     
    End Sub 

  7. #7
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Array Basics

    Is this the problem?

    Dim c(1 To 10000000) As Double

    Try just

    Dim c
    Microsoft MVP - Excel

  8. #8
    Board Regular
    Join Date
    Apr 2008
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Array Basics

    Thanks that fixed that problem, but now the problem is with this part of the code:


    PHP Code:

    d
    (i) = c(1)
    e(i) = c(2)
    f(i) = c(3
    The error is "subscript out of range".

  9. #9
    Board Regular
    Join Date
    Apr 2008
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Array Basics

    ok I fixed that. Here is what I have:

    The problem is with the setting the cells equal to the covar at the very end. its a "type mismatch"

    PHP Code:

    Sub Arry_Code
    ()
    Dim i As Integer
    Dim j 
    As Integer
    Dim N 
    As Integer
    Dim B 
    As Range

    Set B 
    ActiveSheet.Range("A8:C10")


    InputBox("Please enter an integer number of simulations""Simulation Size")

    Dim a(1 To 3) As Double
    Dim c
    Dim g
    Dim d
    (1 To 10000000) As Double
    Dim e
    (1 To 10000000) As Double
    Dim f
    (1 To 10000000) As Double


    For 1 To N

        
    For 1 To 3

            a
    (j) = (Rnd() - 0.5) * Sqr(12)

        
    Next
       
        With Application
    .WorksheetFunction
           c 
    = .MMult(B.Value, .Transpose(a))
           
    = .Transpose(c)
        
    End With

        d
    (i) = g(1)
        
    e(i) = g(2)
        
    f(i) = g(3)
        
    Next

    Dim O 
    As Range

    Set O 
    ActiveSheet.Range("E3:G5")

    O(11).Value Application.WorksheetFunction.Covar(dd)
    O(21).Value Application.WorksheetFunction.Covar(ed)
    O(22).Value Application.WorksheetFunction.Covar(ee)
    O(31).Value Application.WorksheetFunction.Covar(fd)
    O(32).Value Application.WorksheetFunction.Covar(fe)
    O(33).Value Application.WorksheetFunction.Covar(ff)
     
    End Sub 

  10. #10
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,253
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Array Basics

    Hi tscbill5

    Some remarks:

    1 - you declare some variables as Integer. You dimension, however, the arrays with 1 to 10,000,000. The maximum value of an integer is 32K. You should change those variables to Long.

    2 - I believe covar expects a 2D array. You can use the .Transpose to convert your arrays to 2D or declare them as 2D in the first place

    3 - I don't think you can use a worksheet function from vba with arrays with more than 64K. I think you must write your CoVar function. In that case you can make it accept 1D arrays and you can leave the dimensioning of the arrays as they are now
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •