I'm a beginner at vba but have created a custom Add In in Excel with several User Defined Functions (UDF)...they are all working for multiple users but I can't figure out how to update the reference data in ThisWorkbook. Background: I have several clients within the same industry that each have a custom spreadsheets, however, the general excel functions and reference data (statistical rates etc) are the same so I have built UDF to automatically pull the rates/calculate results using index/match. What I can't figure out is how to update the reference data that I have saved in the Add In - "Rates" Sheet - I need to input a new rate on an annual basis (and adjust range) but I can't open/view the excel sheets in my Add In. Here is my code:
Function RATE1(Year As Double)
Dim WBT As Workbook
Dim WSD As Worksheet
Set WBT = ThisWorkbook
Set WSD = WBT.Worksheets("Rates")
RATE1 = Application.WorksheetFunction.Index(WSD.Range("R3:r25"), Application.WorksheetFunction.Match(Year, WSD.Range("N3:n25"), 0), 1)
End Function
Any help/suggestions are greatly appreciated. Thanks.