Running macros using command buttons with Add-In

[glbcpa]

I have been working on this problem for several days almost non-stop.

Is it possible to have a spreadsheet where all the VBA code is contained in an Add-In (.xlam)? The code contains both Functions and Subroutines. The Subroutines are invoked using command buttons on the original spreadsheet (.xlsx or .xlsm).

When I test this setup, the functions work but when I click the command button I receive the standard macro not found or disabled error. Tried Application.Run ("xxx.xlam!subname") but no joy.

Any suggestions? Thanks in advance.

BTW, the reason is to distribute the spreadsheet and have the code in add-in to make it less vulnerable to be able to view and therefore change the code, which would botch everything.

Spreadsheet has 15 tabs, many functions and about 3000 lines of VBA.

 

[RoryA]

You would need:

Application.Run "'xxx.xlam'!subname"

if the real add-in name contains spaces.

 

[glbcpa]

Yeah, that's what I thought too as I described above (no spaces in .xlam file) but it didn't work.

The code associated with the macro:

Sub macro1()
Application.Run ("test.xlam!macro1")
End Sub

Just tried something else though

Sub MacroTest1
Application.Run ("test.xlam!macro1")
End Sub

Then assigned the command button in the original spreadsheet (.xlsm) to run MacroTest1 instead of Macro1 and it worked. Maybe the double reference was confusing it?

Anyway, thanks for the quick reply. Is my theory of using an add-in to hide code workable, or at least better than just password protecting the VBAProject in the .xlsm file?

 

[RoryA]

It's probably marginally better at deterring a casual user.