Hello Andy,
I didn't develop any commercial Add-In, but I developed several Add-Ins for single clients on top of a few hundred other completed VBA projects and I intend to release commercial macros in the future, so I am rather well informed in this area. I'm familiar with storing data in Add-Ins, listening to workbook or worksheet events, generating interface icons in the Add-Ins tab and other things that differ from working on regular macro files. I'm proficient in building UserForms (can be used for settings).
If by a local Excel sheet you mean not even on the same network as the main Excel file with existing macros, then what you want to do is technically impossible with pure VBA.
If both files are on the local network, what I think is not the case with the intent to sell the Add-In, tracking the location of each file is highly problematic and prone to invoke problems.
Also, if one VBA file is supposed to calculate requests from multiple Add-Ins, it better be highly optimized, what is not the case for 95% of the code that I encounter on the web or in existing macros in files from clients.
The only really good solution is to incorporate all the macros within an Add-In. The source code can be protected with dedicated paid software Unviewable Plus.
I code only for Windows operating system. I don't have a MAC to make it work on it. Also, VBA for MAC is limited and requires adding lines of Apple Script as a replacement for some VBA methods.
Best regards,
Ryszard