CODEWITHSUNDEEP
arraysmatlabvbavectorization
Keith D
Keith D

Reputation: 413

Working with arrays in VBA memory and avoiding loops using vectorization

I am versed in MATLAB but find myself working in VBA these days as MATLAB is less accessible to me and I struggle with trying to do stuff in VBA (like vectorization) that I could easily handle in MATLAB.

Lets say I have a data table in excel of the following form:

record  startDate   endDate count
1   100 103 10
2   98  102 5
3   101 104 4

I would like to do all my processing in memory (avoiding loops) and then output results file that looks like this:

    1   2   3   Sum
98  0   5   0   5
99  0   5   0   5
100 10  5   0   15
101 10  5   4   19
102 10  5   4   19
103 10  0   4   14
104 0   0   4   4

Basically, I start with earliest date and loop through the latest date and then check to see if each date is included in the date window for each record and if it is I apply the record count to that day and then sum them up.

I created the included output using a simple worksheet function, but I would like to be able to replicate the process in VBA specifically avoiding looping at least reducing to 1 loop instead of embedded loops.

If I were in MATLAB I would find the logical array that meets a condition, for example:

numDays = 7;
numRecords = 3;
startDate = [100; 98; 101];
endDate = [103; 102; 104];
dateVector = [98; 99; 100; 101; 102; 103; 104];
count = [10; 5; 4];
dateLogic = logical(numDays,numRecords);
for d = 1:numDays
  dateLogic(d,:) = dateVector(d) >= startDate(:,1) &  dateVector(d) <= endDate(:,1)
end
countMatrix = dateLogix * count';
Sum = sum(countMatrix,2);

This would give me a logical matrix of zeros and ones that I can cross multiply with count vector to get my counts and ultimately my Sum vector. I believe I could even use a bsxfun to remove the loop on days.

Please excuse any potential syntax errors as I do not have access to MATLAB right now.

Anyway, how can I do something similar in VBA. Is there an equivalent colon notation to reference the entire range of columns or rows in an array. I will be applying to large data set so efficiency is of the essence. The more I can do in memory before pasting the better.

Thanks in advance.

Upvotes: 0

Views: 2030

Answers (1)

lori_m
lori_m

Reputation: 5567

Here's one possibility, try with sampe data in A1:A4 of a new workbook.

Sub NewTable()

Set Table = Sheet1.[a2:d4]

With Application

    Record = .Transpose(.Index(Table, , 1))
    FirstDate = .Transpose(.Index(Table, , 2))
    LastDate = .Transpose(.Index(Table, , 3))
    Count = .Transpose(.Index(Table, , 4))

    Dates = .Evaluate("row(" & .Min(FirstDate) & ":" & .Max(LastDate) & ")")
    Values = .PV(, Count, .PV(, .GeStep(Dates, FirstDate), .GeStep(LastDate, Dates)))
    Sum = .MMult(Values, .Power(.Transpose(Record), 0))

End With

Sheet1.[F1].Offset(, 1).Resize(, UBound(Values, 2)) = Record
Sheet1.[F2].Resize(UBound(Dates)) = Dates
Sheet1.[G2].Resize(UBound(Values), UBound(Values, 2)) = Values
Sheet1.[G2].Offset(, UBound(Values, 2)).Resize(UBound(Dates)) = Sum

End Sub

Upvotes: 1

Related Questions