I'm trying to write a simple UDF that tells me whether a currency code is G10 or not. It works fine when I test it in a sub in VBA but when I try to use it in a spreadsheet I get #REF.
Function IsG10(Cur As Range) As Boolean
Dim G10s As Variant
Dim Rslt As Boolean
Dim Cross As Variant
Cross = Cur.Value
Rslt = False
G10s = Array("USD", "GBP", "EUR", "CHF", "NOK", "SEK", "AUD", "NZD", "CAD", "JPY")
If Not (Application.WorksheetFunction.IsText(Cross)) Or Len(Cross) > 3 Then
IsG10 = CVErr(xlErrNA)
Else
For Each i In G10s
If Cross = i Then Rslt = True
Next i
End If
IsG10 = Rslt
End Function
This is the Sub I'm using to test:
Sub Test()
Dim TC As Range
Dim ans As Variant
Set TC = Range("Y53")
ans = IsG10(TC)
End Sub
I've tried quite a few things to make it work, removed the error handling, returning a string instead of a bool... Nothing works. I can't see any obvious UDF mistakes.
Thanks!
You need to rename your function. ISG10
is a valid cell address, so when you put a formula in a cell like =ISG10(A1)
, Excel can't parse it correctly. It works just fine when I change it to Function Foobar(Cur As Range) As Boolean
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With