Function works until I make it an ADD IN

[Certified]

I created two functions (located below) located in one workbook. The two codes convert foreign currency to USD. Both codes contain an array populated by data from a two separate worksheets.

I wanted to access the function in all of my workbooks so I saved the workbook as an ADD-IN, and installed the ADD-IN.

My issue is when I try to run the function in another workbook, the function shows, but it does not work. I keep on getting a #value! error.

Does anyone have an idea what the issue could be?

Function FX4q14(FCC As String, Amount As Double) As Double




Dim Myarray() As Variant
Dim i As Integer


'Myarray = Range("b2", Range("b1").End(xlDown).End(xlToRight))


Sheets("4q14").Activate
Myarray = Range("b2:e147")


For i = 1 To UBound(Myarray, 1)
    If FCC = Myarray(i, 1) Then
        FX4q14 = (Amount) * CDbl(Myarray(i, 4))
    End If
    
Next i


End Function

Function FX1q15(FCC As String, Amount As Double) As Double




Dim Myarray() As Variant
Dim i As Integer


'Myarray = Range("b2", Range("b1").End(xlDown).End(xlToRight))


Sheets("1q15").Activate


Myarray = Range("b2:e149")


For i = 1 To UBound(Myarray, 1)
    If FCC = Myarray(i, 1) Then
        FX1q15 = (Amount) * CDbl(Myarray(i, 4))
    End If
    
Next i


End Function

 

[RoryA]

You can't activate a sheet in an add-in since they're all hidden, but you don't need to - replace this:

Sheets("4q14").Activate
Myarray = Range("b2:e147")

With this:

Myarray = ThisWorkbook.Sheets("4q14").Range("b2:e147").Value

 

[Certified]

IT WORKED!!!

Thanks!!!!

 

[RoryA]

Glad to help.