时间戳

首页 > 解决方案 > Excel VBA 动态数组溢出仅在宏结束后完成

arrays - Excel VBA 动态数组溢出仅在宏结束后完成

问题描述

我的数据集包含 2020 年 3 月的几笔外币交易。A 列包含每笔交易的日期，而 B 列包含外币 (EUR) 的交易价值。

我想使用 VBA 计算 C 列中每笔交易以当地货币（即 ZAR）计算的价值。

首先，我使用 STOCKHISTORY 函数获取 2020 年 3 月的所有汇率：

Dim exchangeRateSummary As Range

Range("I1").Formula2R1C1 = "=STOCKHISTORY(R1C5,R1C6,R1C7)"  'where R1C5 contains EUR/ZAR, R1C6 contains 01-Mar-20, and R1C7 contains 31-Mar-20
Set exchangeRateSummary = Range("I1").CurrentRegion  'save range as variable

然后我在 C 列中添加一个 VLookup 来确定每笔交易的本地货币价值，方法是遍历每一行：

Dim j As Integer

For j = 2 To 100
cells(j,3).value=cells(j,2).value * Application.WorksheetFunction.VLookup(Cells(j,1),exchangeRateSummary,2,False)
Next j

运行代码时，出现以下错误：“无法获取 WorksheetFunction 类的 VLookup 属性。” 当我按下 End 时，汇率的溢出范围（使用 stockhistory 函数创建）出现在我的 Excel 工作表上。当我再次运行代码时，VLookup 工作，并在列 C 中提供正确的本地货币值。

因此，问题在于 VLookup 在同一个宏中不起作用，因为股票历史函数创建的溢出范围仅在宏执行后才出现。

一种选择是使这些代码分开宏，但我想一键自动执行此过程。

有没有一种方法可以指示宏首先计算并溢出该范围，然后继续 VLookup 到该范围？

标签： arraysexcelvbadynamicvlookup

解决方案

溢出范围参考通过#

可以通过“#”后缀引用溢出范围，即，H2#如果包含动态 STOCKHISTORY 公式的顶部单元格位于例如H2. 无法测试，但 VBA 应该可以完成这项工作。

因此，可能会收到一个可能发明的查找结果，例如通过

Dim result: result = [VLOOKUP(100,H2#,2,FALSE)]

或者

result = Evaluate("VLookup(" & mySearch & "," & "H2#" & ",2,False")

推荐阅读