使用VBA内存中的数组并使用向量化避免循环-WorkingwitharraysinVBAmemoryandavoidingloopsusingvectorization

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.

我精通MATLAB，但是现在我发现自己在VBA工作，因为我不太容易使用MATLAB，而且我很难尝试在VBA（如矢量化）中完成我可以在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.

我使用一个简单的工作表函数创建了包含的输出，但我希望能够在VBA中复制该过程，特别是避免循环，至少减少到1个循环而不是嵌入循环。

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

如果我在MATLAB中，我会找到符合条件的逻辑数组，例如：

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.

这将给我一个零和一个逻辑矩阵，我可以与计数向量相乘以得到我的计数，最终得到我的和向量。我相信我甚至可以使用bsxfun在几天内删除循环。

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

请原谅任何潜在的语法错误，因为我现在无法访问MATLAB。

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.

无论如何，我怎样才能在VBA中做类似的事情。是否有等效的冒号表示法来引用数组中的整个列或行范围。我将应用于大型数据集，因此效率至关重要。在粘贴更好之前，我在记忆中可以做的越多。

Thanks in advance.

提前致谢。

1 个解决方案

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