vba - 尽管有值，Excel VBA 单元格仍返回 "0"

Question

我正在处理一项任务，我需要使用 Google 的 oAuth 2.0 集成从 Excel 获取 Google Analytics API 的访问 token 。

初始请求通过传入存储在我的工作表中的单元格中的客户端 ID、 key 和身份验证 token 来工作。

我正在使用的 UDF 如下所示:

Public Function GetGAAuthenticationToken _
                    (ByVal ClientId As String,  _
                    ByVal ClientSecret As String,  _
                    ByVal code As String,  _
                    accessToken As String,  _
                    RefreshToken As String,  _
                    accessTokenExpires As Date)

  'Has token expire? If not, don't renew the access token.
  Dim Token(2, 0)
  Dim Refresh As Boolean: Refresh = True

  If Now < accessTokenExpires Then
    Token(0, 0) = accessToken
    Token(1, 0) = RefreshToken
    Token(2, 0) = accessTokenExpires
    Refresh = False
  End If

  Dim objhttp As Object
  Dim ResponseText As String

  If Refresh Then
      If RefreshToken = "" Or RefreshToken = "0" Then
        'Initial authorization code=GetGAAuthenticationToken(B1, B2, B4, B6, B7, B8)
        Set objhttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
        URL = "https://accounts.google.com/o/oauth2/token"
        objhttp.Open "POST", URL, False
        objhttp.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
        objhttp.setTimeouts 1000000, 1000000, 1000000, 1000000
        objhttp.send ("code=" & code & "&client_id=" & ClientId & "&client_secret=" _
          & ClientSecret _
          & "&redirect_uri=urn%3Aietf%3Awg%3Aoauth%3A2%2E0%3Aoob&grant_type=authorization_code")

        ResponseText = objhttp.ResponseText
      Else
        'Refresh Token
        Set objhttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
        URL = "https://accounts.google.com/o/oauth2/token"
        objhttp.Open "POST", URL, False
        objhttp.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
        objhttp.setTimeouts 1000000, 1000000, 1000000, 1000000
        objhttp.send  _
          ("client_id=" & ClientId  _
           & "&client_secret=" & ClientSecret & "&refresh_token=" _
           & RefreshToken & "&grant_type=refresh_token")

        ResponseText = objhttp.ResponseText
      End If

      Dim XMLResponse:    Set XMLResponse = JSONtoXML(ResponseText)
      Dim XMLResult:    Set XMLResult = XMLResponse.SelectSingleNode("//XML")

      If Not IsNull(XMLResult.GetAttribute("access_token")) Then
        Token(0, 0) = XMLResult.GetAttribute("access_token")
      End If
      If Not IsNull(XMLResult.GetAttribute("refresh_token")) Then
        Token(1, 0) = XMLResult.GetAttribute("refresh_token")
      Else
        Token(2, 0) = RefreshToken
      End If
      If Not IsNull(XMLResult.GetAttribute("expires_in")) Then
        If IsNumeric(XMLResult.GetAttribute("expires_in")) Then
           Token(2, 0) = DateAdd("s", CInt(XMLResult.GetAttribute("expires_in")), Now)
        Else
            Token(2, 0) = DateAdd("s", 3600, Now)
        End If
      Else
        Token(2, 0) = DateAdd("s", 3600, Now)
      End If
  End If

  GetGAAuthenticationToken = Token

End Function

在我的工作表中，我有一组调用该函数的单元格:

=GetGAAuthenticationToken(B1, B2, B4, B5, B6, B7)

正如我上面所说，初始请求工作正常，并将访问 token 、刷新 token 和访问 token 到期日期输出到单元格 B5、B6 和 B7 中。

但是，当我再次运行请求时，我希望 B5、B6 和 B7 中的值传递到我的 UDF，它们以“0”的形式出现。似乎它们在我触发更新和 UDF 执行之间被重置。是否有可能获得这些值(value)？

我遇到过这个页面 http://fastexcel.wordpress.com/2012/01/08/writing-efficient-vba-udfs-part-8-getting-the-previously-calculated-value-from-the-calling-cells/这似乎与我正在尝试做的类似，但这似乎是针对单个字段而不是数组？

我在 Excel 和 VBA 方面完全没有经验，因此欢迎任何帮助。

谢谢!

Answer 1

将您的功能更改为 Range参数，而不是字符串/日期参数。引用“B1”的 UDF 通过 Range目的。

Public Function GetGAAuthenticationToken(ByVal p_ClientId As Range, ByVal p_ClientSecret As Range, ByVal p_code As Range, p_accessToken As Range, _
p_RefreshToken As Range, p_accessTokenExpires As Range)

然后声明你的变量并将它们设置为范围对象的值:

Dim ClientId As String
Dim ClientSecret As String
Dim code As String
Dim accessToken As String
Dim RefreshToken As String
Dim accessTokenExpires As Date

ClientId = p_ClientId.value
ClientSecret = p_ClientSecret.value
code = p_code.value
accessToken = p_accessToken.value
RefreshToken = p_RefreshToken.value
accessTokenExpires = p_accessTokenExpires.value

请注意，我更改了参数的命名，因此您不必修改变量名称。如果你愿意，你当然可以改变它。