Results 1 to 7 of 7

Thread: Splitting a Multidimensional Array

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Dec 2016
    Posts
    100

    Splitting a Multidimensional Array

    So I'm trying to write a function that can do Dot Product calculations on arrays. I want to use the SumProduct worksheet function to do this. I demonstrated what I'm trying to do on the worksheet with the attached image:


    Name:  sumprodutcs.jpg
Views: 658
Size:  23.8 KB

    I want to do this using VBA, and for each column of W. The issue is that W is a two dimensional array and I need to split each column into its own array. I'm not sure how to do this efficiently. Or how to do it period.

    Any thoughts on how I can split W column wise?

    Thanks

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Splitting a Multidimensional Array

    The issue is that W is a two dimensional array
    without seeing any code it is a bit difficult to tell, but if the 2d array is a single column values, you can use worksheetfunction.transpose of the array twice to return a single dimension array
    otherwise i am not understanding the the problem correctly, post a sample workbook (zip first) that demonstrates the problem
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Dec 2016
    Posts
    100

    Re: Splitting a Multidimensional Array

    Here is a specific VBA example of what I am trying to accomplish, and the work I have done so far:

    Code:
    Dim w() As Double: ReDim w(3, 3)
    For i = 0 To UBound(w)
        For ii_ = 0 To UBound(w, 2)
            w(i, ii) = 0.01
        Next
    Next
    
    Dim a() As Double: ReDim a(3)
    For i = 0 To UBound(a)
        a(i) = 0.03
    Next
    
    Dim b() As Double: ReDim b(3)
    For i = 0 To UBound(b)
        b(i) = 0.03
    Next
    
    'assign each column of w to L 
    Dim L() As Double: ReDim L(UBound(a))
    For i = 0 To UBound(w, 2)
        L(i) = Application.Index(w, 0, 1)
    Next
    I am getting a type mismatch error on Application.Index(). The idea is to use L() like so:

    Code:
    'linear activation
    Dim Z() as Double: redim Z(ubound(a))
    for i = 0 to ubound(Z)
        Z(i) = Worksheetfunction.SumProduct(L, a) + b(i)
    next

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Splitting a Multidimensional Array

    Syntax

    expression.Index(Arg1, Arg2, Arg3, Arg4)

    expression A variable that represents a WorksheetFunction object.

    Parameters

    Name Required/Optional Data Type Description
    Arg1 Required Variant Array or Reference - a range of cells or an array constant. For references, it is the reference to one or more cell ranges.
    Arg2 Required Double Row_num - selects the row in array from which to return a value. If row_num is omitted, column_num is required. For references, the number of the row in reference from which to return a reference
    Arg3 Optional Variant Column_num - selects the column in array from which to return a value. If column_num is omitted, row_num is required. For reference, the number of the column in reference from which to return a reference.
    Arg4 Optional Variant Area_num - only used when returning references. Selects a range in reference from which to return the intersection of row_num and column_num. The first area selected or entered is numbered 1, the second is 2, and so on. If area_num is omitted, INDEX uses area 1.

    Return Value
    Variant
    from help, if i am looking at the code correctly, i can see why you wold get a type mismatch
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Dec 2016
    Posts
    100

    Re: Splitting a Multidimensional Array

    So maybe I need to make w() a Variant instead? I gave it a try and I still have the same error. Is that what you were referring to?

  6. #6
    Member
    Join Date
    Jan 2021
    Location
    Kepler-452b
    Posts
    37

    Re: Splitting a Multidimensional Array

    Your array w(,) is two dimensional as you said there are columns so each column might have different length of elements .what I is that

    -you are initiating array L() on the basis of Ubound of array a()

    Dim L() As Double: ReDim L(UBound(a))

    Now you are initiating the loop basis on UBound w (,) but array a()

    For i = 0 To UBound(w, 2)
    L(i) = Application.Index(w, 0, 1)
    Next

    I think you should initialize the for loop till the UBound of array a()


    that may work ->
    < / L R. . . . >

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Splitting a Multidimensional Array

    So maybe I need to make w() a Variant instead?
    also L by the looks of it
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width