-1

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.

Community
  • 1
  • 1
BJW
  • 1
  • 1
    Welcome to SO, I could not find a clear question, can you modify your post and define a question to solve? Please read> [How to Ask](https://stackoverflow.com/help/how-to-ask) –  Dec 12 '17 at 17:11

1 Answers1

0

Open a spreadsheet that has a link to the add in. Open the VBA Editor. In project explorer you will see the add in shown. Click on this and then open up to ThisWorkbook. Click on this and then look in Properties explorer for the property "IsAddin". Set this to false and your add in will appear. Make your changes, then set the IsAddin Property back to True. Then save your changes by clicking on the add in in project explorer and then on the Save icon in the toolbar in the VB Editor.

Harassed Dad
  • 4,631
  • 1
  • 8
  • 12