excel - VBA loop preference

Question

I am currently trying to optimize a VBA code with extreme execution time (56 hours and more on high end PC). The main problem is nested loops (up to 8) due to complicated iterative calculations and referencing ranges in formulas.

I have two questions:

1) What loops are most preferred in VBA (based on their hardware strain for lack of better word)

2) Is there a way to reference variable range of cells in a formula? For example in formula:

=AGGREGATE(15,6,SQRT((R2C10:R500000C10-RC[10])^2+(R2C11:R500000C11-RC[11])^2+(R2C12:R500000C12-RC[12])^2),1)

I am referencing relative cell: "R500000C10". There are two main issues: For one, the half milion cells might not capture all data - though in most cases there is less active cells than that, in same cases that might be an issue. Second, it takes forever to scan through so many cells (same reference is used in multiple of VLOOKUPs).

The first issue could be solved by referencing the whole column, but that makes the second problem worst.

I would like to reference it in a similar way that xlDown works:

Range().End(xlDown).Select

The problem there is I would need to reference it within the Active.Cell.FormulaR1C1. Is there a way to do that?

Thanks!

标签： excelvbaloops

Answer 1

好吧，您写道，您有一个带有 8 个循环的代码并尝试对其进行优化，也许您可​​以编辑并放置该代码的一部分，因为您的描述非常笼统，但是，根据您提供的信息，我推测您的观点 1) 它取决于你想做什么？因为每个“循环”对指定的场景都有自己的用途 2）有一些技巧可以做到这一点，但都取决于您要做什么以及为什么要再次使用 .formulaR1C1 进行推测，但是您可以尝试将循环分开函数，也许你在单元格中放了一个公式，问题是为什么？您可以在您的 VBA 代码中创建 functoins 和 Sub 并在不使用 xtra“资源”的情况下执行“极端执行”