Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2005-04-07
10
Medium Priority
?
788 Views
Last Modified: 2006-11-18
I have a VBA application that runs in Excel 2003. I have a list of variable declarions and public functions and procedures that I donot want any user to see.
The code that the user writes in VBA should be be able to use these public functions and variable declarations.

I can use VB 6.0, but would prefer not to use it.

psmsis
0
Comment
Question by:psms
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 13725845
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
0
 

Author Comment

by:psms
ID: 13725936
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.

0
 
LVL 32

Expert Comment

by:Erick37
ID: 13729550
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:psms
ID: 13737464
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.
0
 
LVL 32

Expert Comment

by:Erick37
ID: 13737836
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
0
 
LVL 81

Expert Comment

by:byundt
ID: 13742146
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.
0
 

Author Comment

by:psms
ID: 13743166
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?
0
 
LVL 81

Expert Comment

by:byundt
ID: 13743667
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
0
 

Author Comment

by:psms
ID: 13743732
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
0
 
LVL 32

Accepted Solution

by:
Erick37 earned 1000 total points
ID: 13744953
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question