使用 VBA 识别 Excel 单元格中字符串的字数

Question

我有一个单元格，里面有文本“我今天赚了 14 美元，昨天赚了 13 美元”。
使用 Excel 中的 VBA，如何将其转换为“我今天赚了 11.33 欧元，昨天赚了 10.52 欧元”？

想法：根据文本中空格的位置，VBA 可以：

   a) identify the word order of each word within the cell [ex. '14' is word 
      3, 'dollars' is word 4] 
   b) identify whether the following word was 'dollars' and 
   c) if so, multiply the previous word times the conversion factor of .81. 

然而，我不知道如何将其放入代码中。超级用户，帮帮忙！

Answer 1

如果您使用转换率查找表，则不需要 VBA。

如果您要使用 VBA，我的想法是“将其变得更好”，并拉低最新的转换率。或者您可以使用特定日期来做到这一点，具体取决于您从哪里获取数据。

通过这一工作还将扩展您对 VBA 可能性的了解。

以下是使用特定网站最新数据的示例，但还有很多其他数据。对于这个，API 密钥是免费的。您必须自己申请。

'Set reference to Microsoft winHTTP Services 5.0
'You'll need to install a JSON converter, or perhaps parse the csv output
'You could also parse this simple JSON using Regular Expressions
Option Explicit
Option Compare Text
Function ConvertInText(S As String) As String
    Dim V As Variant, W As Variant
    Dim DT As Date
    Dim I As Long

V = Split(S, " ")
For I = 0 To UBound(V)
    If V(I) = "Dollars" Then
        V(I) = "Euros"
        V(I - 1) = Format(USDtoEUR(CCur(V(I - 1))), "0.00")
    End If
Next I

ConvertInText = Join(V)
End Function

Private Function USDtoEUR(DOL As Currency) As Currency
    Const myAPI As String = "apikey=xxxxxxxxxxxxx"
    Const sURL As String = "https://www.alphavantage.co/query?function=CURRENCY_EXCHANGE_RATE&from_currency=USD&to_currency=EUR&"
    Dim httpRequest As WinHttpRequest
    Dim strJSON As String, JSON As Object

Set httpRequest = New WinHttpRequest
With httpRequest
    .Open "Get", sURL & myAPI
    .Send
    .WaitForResponse
    strJSON = .ResponseText
End With
Set httpRequest = Nothing

Set JSON = parsejson(strJSON)

USDtoEUR = JSON("Realtime Currency Exchange Rate")("5. Exchange Rate") * DOL

End Function

Answer 2

您可以通过命令按钮或宏尝试此 VBA 代码。

Private Sub CommandButton1_Click()

i = 8

str1 = Range("A2").Value

str1 = Mid(str1, 1, i - 1) & Replace(str1, "14 dollars today and 13 dollars", "11.33 euros today and 10.52 euros", Start:=i)

Range("A4").Value = str1


End Sub