#value error, but works in VBA

Question

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

It doesn't work as a Formula because with a Formula you do =FuncTest(A1) it runs the function provided with the value from Cell A1 your function doesn't receive anything it only provides back. — DragonSamu
– DragonSamu, Commented Sep 4, 2015 at 12:34
What does it look like when you make it a function? Do you have it taking a value and using it in the macro part? — BruceWayne
– BruceWayne, Commented Sep 4, 2015 at 14:24
@BruceWayne the code you see is the function wether used in VBA only or called as a Formula. The OP's problem is that he doesn't receive any value because the function doesn't request it while this is mandatory for formula functions — DragonSamu
– DragonSamu, Commented Sep 4, 2015 at 14:49
Well, if you try to add a variable in the function it is the same #value error. That's why I created the static function without a variable to check what's the problem. — Ivo
– Ivo, Commented Sep 4, 2015 at 14:56

xidgel · Accepted Answer · 2015-09-04 21:43:21Z

3

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.

answered Sep 4, 2015 at 21:43

xidgel

3,1452 gold badges15 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

#value error, but works in VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related