Help with creating Excel Add-Ins

[Twi78]

I have created an excel workbook with many defined functions. These functions are calculated by looping through an array (that is populated from an excel sheet in the same file) and where user defined parameters are entered. For example MachineAOutput(Year,Month,ProductType). Everything works in the workbook fine but entering the function in another workbook (both before and after creating the Add-In) doesnt work, it just retunrs a zero. This suggests the function is recognised but the array that the function reads from is not populated.

I have created the Add-In from the original file which has all the data etc. but I can't run the Sub procedure that populates the array as I get a 'Subscript out of range' error?

 

[bertie]

Without seeing your code I can only offer some general advice.

Make sure your code is in a standard module, i.e., Insert=>Module.

The add-in is effectively on a separate workbook. Be aware of the scope of your function, i.e., Public or Private.

Your subscript out of range error indicates that you are not referencing the correct workbook when assigning ranges.

The ThisWorkbook object is the workbook containing the add-in.

'Theis s the add-in workbook
Set wb = ThisWorkbook

'this refers to the user's workbook
Set wb = ActiveWorkbook

See if this link help you to debug your code:
Creating an Excel Add-in. How To Create an Add-in To Store Your Excel VBA Macros