How to Hide variable declarations and Public functions from users in Excel 2003 VBA (Visual Basic for Applications)

you can add a Security password to the VBA in the EXCEL Spreadsheet:

in the VBA Editor, click on Tools/VBA Project Properties

select the the Protection tab, then CHECK the "Lock Project for Viewing" checkbox and supply a password (along with a repeat of the password in the Confirm Password box.

The nexct time you, or anyone else tries to view the VBA, you will be prompted to enter the password, befor the VBA can be viewed.  But the Spreadsheet will still execute all of the VBA just as normal.

AW

This will not work for me.  I want the user to view VBA code that he writes in my Excel 2003 application. Only any code that he writes can use my fuctions, procedures and variable declarations. BUT HE WILL NOT BE ABLE TO SEE MY FUNCTIONS AND VARIABLE DECLARATIONS.

If you need to hide the underlying code that makes up your public functions, you will need to create a DLL in VB6.

See:
"Protecting Your Solutions VBA Code"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deovrprotectingyoursolutionsvbacode.asp?frame=true

Hi Eric37
Please clarify. If I make a DLL using VB6, can the user still write code freely in Excel 2003 VBA that uses the variables and functions that were encapsulated in DLL.

Once you have made your ActiveX DLL, you can use it in your VBA project by adding a reference to the DLL.  To add a reference, use the menu Tools->References...  A list of all registered components is displayed.  Once you have built and registered your DLL, it will also show up on the list.

Your DLL can provide Functions (methods), Properties (to expose variables), and Events.  The underlying source code will not be visible to the end user.

Here is a tutorial from MS which describes the steps required in making a DLL:

"Creating an ActiveX DLL"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon98/html/vbconcreatinganinprocesscomponent.asp

Another approach you might consider is saving your file as a .xla add-in. You do this from the File...Save As dialog in Excel. You will probably want to password protect your .xla add-in, which you can do just like Arthur_Wood described. In so doing, the user won't be able to view your code or declarations--but he can use the subs and functions in worksheet formulas and VBA code he writes in other workbooks.

If the .xla is stored in the user's XLSTART folder, it will be loaded every time Excel launches. Alternatively, the user could use the Tools...Add-Ins menu item from the worksheet to load the add-in.

The last two replies are very helpful.
Sorry, another clarification that I should have made earlier.
I want to be able to disribute this Excel file. But I want a mechanism such that it cannot be copied freely. After installation, the user should send me something like a .reg file that stores his PC hardware info. And then I should send him a new key that makes this application run on his PC and his PC only.
I donot want a hardware lock. Just a software lock. How can I do this?

psms,
If you want something with bullet-proof anti-piracy protection, you should not build your application using Office programs. Excel worksheet passwords (any length) can be cracked in less than five minutes using software posted by Microsoft Excel MVPs. Excel VBA passwords can be cracked by programs readily found by a Google search. The best you can achieve is to prevent ordinary users from pirating your software.

With that in mind, the .dll and .xll files suggested by Erick37 are a better choice because the code is stored in executable for rather than VBA code.
Brad

byundt, Erick37
Thanks. It is decided that I will develop using .xll and .dll.
How do I prevent people from copying my software and running on other machines. Once properly installed & enabled on one PC. People may copy and try to run on another PC, but it should not work, without my enabling it first.

I have seen the following before.
After installation, the user should send me something like a .reg file that stores his PC hardware info. And then I should send him a new key that makes this application run on his PC and his PC only.
I donot want a hardware lock. Just a software lock. How can I do this?

Can you pl give me 1 link or program name that can achieve this.

psms