multiplying ranges in array

    Hi,


    I am working on a workbook in which I have to do some rather complicated formulas with arrays and ranges, it slowed excel down to the point that calculations took over half an hour. Hence, I wanted to do the calculations in vba.


    I have an array, which I fill with ranges. so say:


    Throughput Out = {A1:B2 ; A4:B5; A7:B8}


    Now I want to multiply this with a range in another sheet (A1:B2). I'm getting a type mismatch when I try to multiply the ranges in the array with the range in the other sheet:

    Code
    ThroughputOut(x) = ThroughputOut(x) * DailyOutflow


    Below my full code:


    What are you expecting as the output? Actual matrix multiplication, or just multipying each element by the corresponding element in the other array?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Quote from rory

    What are you expecting as the output? Actual matrix multiplication, or just multipying each element by the corresponding element in the other array?


    I would expect the outcome to be an array with 24 elements (0 to 23). every element of the array should have the multiplication of the cell values of the ranges.


    So if you would take the example I described earlier. i would expect:


    ThroughputOut(0) = throughput(0).A1 * range.A1, throughput(0).B1 * range.B1, throughput(0).A2 * range.A2, throughput(0).B2 * range.B2
    ThroughputOut(1) = throughput(1).A4 * range.A1, throughput(1).B4 * range.B1, throughput(1).A5 * range.A2, throughput(1).B5 * range.B2

    Other than seemingly ruling out matrix multiplication, that doesn't really clarify to be honest. Is ThroughputOut(0) supposed to be a 4 element array? If so, should it be 2*2 or some other alignment.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Quote from rory

    Other than seemingly ruling out matrix multiplication, that doesn't really clarify to be honest. Is ThroughputOut(0) supposed to be a 4 element array? If so, should it be 2*2 or some other alignment.


    The elements should be 2x2 for both the Throughput array, as well as the range.


    array{2x2, 2x2, 2x2} * range(2x2) = array_after_multiplication{2x2, 2x2, 2x2}

    It's probably simplest to just loop through the arrays and multiply each element then, especially as they aren't large.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    I'm using the 2x2 as an an example. Real range is variable (depending on amount of days in month).


    Example is 30x58 for one range. The array will contain 24 of such ranges.


    Those 24 ranges should be multiplied with one other range which has the same dimensions (30x58)

    That's still not large and I think a loop will be faster than any alternative, such as Evaluate.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    How do you mean a loop?


    I'm using the array approach because the output array will be manipulated a few times after as well.

    I mean a basic loop construction like this:


    Code
    Dim x as long, y as long
for x = lbound(array1) to ubound(array1)
   for y = lbound(array1, 2) to ubound(array1, 2)
      array1(x, y) = array1(x, y) * array2(x, y)
   next y
next x

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Quote from rory

    I mean a basic loop construction like this:


    Code
    Dim x as long, y as long
for x = lbound(array1) to ubound(array1)
for y = lbound(array1, 2) to ubound(array1, 2)
array1(x, y) = array1(x, y) * array2(x, y)
next y
next x


    I tried the following:


    Code
    For x = LBound(ThroughputOut) To UBound(ThroughputOut)
        For a = LBound(ThroughputOut(x), 1) To UBound(ThroughputOut(x), 1)
            For b = LBound(ThroughputOut(x), 2) To UBound(ThroughputOut(x), 2)
                ThroughputOut(x(a, b)) = ThroughputOut(x(a, b)) * DailyOutflow(a, b)
            Next b
        Next a
    Next x


    but i get a type mismatch error on

    Code
    ThroughputOut(x(a, b)) = ThroughputOut(x(a, b)) * DailyOutflow(a, b)

    It would need to be:


    Code
    ThroughputOut(x)(a, b)


    to access elements of an array within an array.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    That seems to work perfectly thanks!



    BELOW: Never mind, found the solution, I'm an idiot and forgot to redim the array.



    Now I have one other question regarding arrays. Don't know if it should be in this topic, Please let me know if I need to move the question to a new topic.


    So I multiplied the 2 arrays into ThroughputIn:


    Code
    For x = LBound(ThroughputOut) To UBound(ThroughputOut)
        For a = LBound(ThroughputOut(x), 1) To UBound(ThroughputOut(x), 1)
            For b = LBound(ThroughputOut(x), 2) To UBound(ThroughputOut(x), 2)
                ThroughputOut(x)(a, b) = ThroughputOut(x)(a, b) / 100 * DailyOutflow(a, b)
            Next b
        Next a
    Next x



    I now want to sum the rows within each sub array. For this I use the following code:



    I am getting a subscript out of range error in the public function on

    Code
    strArray(a) = Application.Sum(Application.Index(Arr(x), a))


    the weird thing is that if I use the immediate window to see

    Code
    Application.Sum(Application.Index(Arr(x), a))

    it gives me 1 value. But for some reason it will not be put into the

    Code
    strArray(a)

    ?

    Glad to help. :)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login