#value错误，但在VBA中有效 [英] #value error, but works in VBA

问题描述

以下函数从Bloomberg获取报价。

当从子过程调用时，该函数可以工作，但不能作为工作表公式。有谁知道为什么？

The following function gets a quote from Bloomberg.
The function works, when called from a Sub procedure, but not as a worksheet formula. Does anyone know why?

Public Function GetRatesSTATIC() As Variant
   Application.DisplayAlerts = False
   Dim objBK As Workbook
   Dim objRng As Range
   'Open the page as a workbook.
   Set objBK = Workbooks.Open("http://www.bloomberg.com/quote/EURUSD:CUR")
   'Find the Rate
   Set objRng = objBK.Worksheets(1).Cells.Find("EURUSD:CUR")
   'Retrieve the exchange rate.
   GetRatesSTATIC = objRng.Offset(1, 0).Value
   objBK.Close savechanges:=False
   Application.DisplayAlerts = True
End Function

推荐答案

根据这个知识库文章，功能的限制是它们不能执行大多数方法，因此可能不允许调用Workbook.Open方法。在我手中，您的功能在VBA中工作不正常，从工作表中调用时，并不完全正常。

According to this knowledge base article, a limitation of functions is that they cannot "execute most methods", so calling the Workbook.Open method may not be permitted. In my hands your function worked erratically within VBA and not at all when called from a worksheet.

这篇关于#value错误，但在VBA中有效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！