如何在VBA公共函数中实现Application.Caller解决公式拖拽不更新问题

嘿，你的问题根源很明确：原来的函数里用了ActiveCell.Row，这个值是当前选中的单元格行号，不是公式所在单元格的行号。所以你从H2往下拖的时候，只要选中的还是F2，函数就一直用F2的货币代码，结果自然都一样啦。

修复后的完整代码 #

Option Explicit

Public Function Oanda() As Variant
    ' 记得先引用这几个库：
    ' - Microsoft XML, v6.0
    ' - Microsoft Internet Controls
    ' - Microsoft HTML Object Library
    
    Dim callerRow As Long
    Dim conversionCurrency As String
    Dim IE As InternetExplorer
    Dim doc As HTMLDocument
    Dim ansText As String
    Dim ansExtract As Variant
    
    ' 加个错误处理，防止出问题时IE进程挂在后台
    On Error GoTo Cleanup
    
    ' 核心修改：用Application.Caller拿到公式所在单元格的行号
    callerRow = Application.Caller.Row
    ' 从对应行的F列取货币代码
    conversionCurrency = ThisWorkbook.Sheets("Employee").Range("F" & callerRow).Value2
    
    ' 先检查下有没有货币代码，避免空请求
    If Trim(conversionCurrency) = "" Then
        Oanda = "请填写货币代码"
        GoTo Cleanup
    End If
    
    Set IE = New InternetExplorer
    IE.Visible = False ' 浏览器后台运行就行，不用显示
    IE.Navigate "https://www.oanda.com/currency/converter?quote_currency=USD&base_currency=" & conversionCurrency
    
    ' 等页面加载完，这里加了IE.Busy判断更稳妥
    Do While IE.ReadyState <> ReadyState_Complete Or IE.Busy
        DoEvents
    Loop
    
    Set doc = IE.Document
    ansText = Trim(doc.getElementsByTagName("tbody")(2).innerText)
    ansExtract = Split(ansText, " ")
    
    ' 确认能拿到有效汇率值再计算
    If UBound(ansExtract) >= 4 Then
        Oanda = Val(ansExtract(4)) * 20
    Else
        Oanda = "汇率获取失败"
    End If
    
Cleanup:
    ' 一定要关闭IE并释放资源，不然后台会一堆IE进程
    If Not IE Is Nothing Then
        IE.Quit
        Set IE = Nothing
    End If
    Set doc = Nothing
End Function

关键改动说明 #

• 去掉了函数参数：现在不需要传参数啦，直接在H列写=Oanda()就行，函数会自动找对应行的F列值。
• 替换ActiveCell.Row为Application.Caller.Row：这是解决问题的核心！Application.Caller就是调用这个函数的单元格，它的Row属性就是公式所在的行，这样每个单元格都能对应自己行的F列数据。
• 加了错误处理和资源清理：之前的代码没关IE，拖几次就会有一堆后台IE进程，现在加了Cleanup段，确保不管成功失败都会关闭IE。
• 增加了输入验证：如果F列是空的，会返回提示，不会瞎请求网页。

使用步骤 #

1. 把原来的函数代码换成上面的版本。
2. 在H2单元格输入=Oanda()，然后往下拖到H3-H10，每个单元格都会自动获取对应行F列的货币代码，算出20美元对应的金额啦。

内容的提问来源于stack exchange，提问作者urdearboy