☰

在选定的单元格上手动运行函数

tag-icon • tag-icon microsoft-excel
在选定的单元格上手动运行函数

我该如何解决传递给 ExchangeRatesCalc 的参数（应该是一个字符串）的类型不匹配错误？

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
    If Not Intersect(Target, Range("ExchangeRates[XRT]")) Is Nothing Then
        Call ExchangeRatesCalc("ExchangeRates[Currency]")
    End If
End If
End Sub

ExchangeRates 是一个格式化的表格；XRT 和 Currency 是表格中的列。当我单击 XRT 中的单元格时，我想将相应的 Currency 传递给 ExchangeRatesCalc，并让结果显示在 XRT 中。

Option Explicit

Function ExchangeRatesCalc(DestCur As String) As Variant
    ' Requires Microsoft WinHTTP Services reference
    Dim url As String
    ' http://quote.yahoo.com/d/quotes.csv?s=XXXYYY=X&f=l1
    url = "http://quote.yahoo.com/d/quotes.csv?s=USD" & DestCur & "=X&f=l1"
    Dim myHTTP As New WinHttp.WinHttpRequest
    myHTTP.Open "GET", url, False
    myHTTP.send ""
    If myHTTP.StatusText <> "OK" Then GoTo ServerErrorHandler
    If Not (WorksheetFunction.IsNumber(myHTTP.responseText)) Then ExchangeRatesCalc = 0
    ' This is where the error occurs
    ExchangeRatesCalc = CDbl(myHTTP.responseText)
    Exit Function

ServerErrorHandler:
    MsgBox "Error. Could not convert currency"

End Function

相关内容