?
Solved

prevent viewing of/access to VBA code

Posted on 2007-08-04
14
Medium Priority
?
1,182 Views
Last Modified: 2013-11-27
how can I prevent anyone from being able to view any modules or VBA code in my spreadsheets and in my databases?
0
Comment
Question by:kaosyeti
  • 5
  • 4
  • 3
  • +2
14 Comments
 
LVL 39

Accepted Solution

by:
thenelson earned 700 total points
ID: 19632577
Database: convert to MDE file.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 19632617
0
 
LVL 58

Assisted Solution

by:harfang
harfang earned 700 total points
ID: 19632654
Who's "anyone"?

Most people won't get around a simple password: from VB, use (Tools | <project name> properties), [Protection], "lock project for viewing". This is the same for Excel, Word, Access, ...

If "anyone" is ready to spend $50 and a couple hours of time, that will not be enough.

This is about as good as it gets for Excel. Nelson already mentioned the MDE format for Access databases, which is a much stronger protection.

If you really want to protect your source code, VB is probably not a very good choice.

Cheers!
(°v°)
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 600 total points
ID: 19633419
Move your code logic that you want to protect to an external application or web server...

Discussion (between myself & 'zorvek'):
[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_21424558.html ]

Continued:
[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_21663911.html ]


Suggested methods of making VBA code reside on a (web) server (by myself & other 'Top 10' Experts of MS-Excel Zone):
[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_21424559.html ]

Or...

To convert your Excel workbook to an application:
[ http://www.turboexcel.com ]

Note that securing your VBA code with a VBProject password is not very secure.  It will stop the inexperienced users but simply searching Google for a cracking algorithm for Excel Project passwords will allow the unprotection relatively quickly.

You could convert your VBA code to a library routine (in a Dynamic Linked Library, or ActiveX control), so that any "clever" routines you do not wish anybody to view can be pre-compiled and loading by your workbook on demand.

BFN,

fp.
0
 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 600 total points
ID: 19633451
PS.
Discussion of conversion of an MDB database file to an MDE file (and alternatives for MS-Access):
[ http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21969095.html ]

BFN,

fp.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 19633462
PPS.
I have not used this, nor am I connected with the organisation offering this product that offers code protection for Visual Basic, C#, VB.NET &/or Delphi applications; so I am not sure it will cope with VBA code, but I'll leave that to you to discover if you have the time...

Code-Lock
Secure software protection system

License:       Free to try, $99(US) to buy

[ http://www.perfectdownloads.com/software-development/management-distribution/code-lock.htm ]

"Are you losing sales to software pirates? Stop using your weak software protections now. Code-Lock is an advance secure software protection system that will protect your Visual Basic applications by stopping crackers in their tracks. It is very easy to install and only ONE line of code is required to protect your software. Code-Lock featuring CodeSecure Technology, has never been cracked to date. It is time to keep crackers awake while you sleep. A lot of sharewares are cracked by crackers mainly because they uses weak protection schemes. Most rely on registration codes that can be cracked by experienced crackers. Other protection schemes might include time checks or keyfiles. But the sharewares are in effect registered versions and once the cracker patches certain places, the sharewares will behave like registered versions. The better form of protection involves only distributing demos that has limited functions. Registered users are sent the registered version or are told of the URL to download the registered version. Such methods are more secure but it is still possible for crackers to use fake credit cards to register and get the registered version and distribute it. Legitimate users might just register a copy of it, download it and then share with friends. Code-Lock prevents the creation of the registered version of your program unless the correct registration serial is used. The registration serial will not be valid on another computer. The registered version will only run on the registered user's computer. There is no need to download a registered version by the registered user. Furthermore, the registered version is watermarked and can be traced to the registered user. Attempts to remove the watermark will render your program useless. To date, programs protected by Code-Lock have not been cracked or warezed by unethical crackers!!"


Author's site:
[ http://www.rtsoftware.org ]
[ http://www.chosenbytes.com ]

BFN,

fp.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 19633472
Ditto... no connection/not tried personally...

TheScarms AppSentinel Copy Protection Component

[ http://www.thescarms.com/appsentinel/default.aspx ]

===
TheScarms(tm) AppSentinel lets you quickly and easily create evaluation versions of your software and stop unauthorized copying and unregistered use of your programs!

Create Safe and Secure Trial, Demo, Evaluation or Rental Versions of Your Software

    *      Evaluation versions will have trialware functionality like the number of days or number of runs1 left and checking to see if the system clock was turned back!

    *      Evaluation versions of your applications can be turned into the full, non-expiring version by entering a Registration ID. This functionality can be added to your Windows software whether it is an .EXE, .DLL, COM component, Excel application...

    *      AppSentinel simplifies application rental! Your application can expire at regular intervals. Users will have to re-register with you to renew their subscription and use your application for another period.

Copy Protect Your Software and Prevent Sharing of Your Software

    *      Reliably prevent the unlicensed use of your application! Users will not be able to copy and run your program on another computer.

    *      If your application is copied to another PC it will expire when its trial period ends. The user must contact you and officially register the application on that computer.

    *      Attempts to use registration information from a licensed copy of your application in another copy of your program will fail! The special Registration ID required to unlock your application is generated from an Evaluation ID and Machine ID. Because these values are unique to a specific computer, Registration IDs cannot be copied!

What is AppSentinel?

    *      AppSentinel is a COM component2 (ActiveX DLL) that can easily be incorporated into your application regardless of the programming language used.

    *      AppSentinel is an insurance policy for your programs. AppSentinel provides software copy protection to eliminate illegal and unauthorized use of your programs. AppSentinel protects your software by forcing unregistered copies to expire and stop running.

    *      AppSentinel is an add in that let's you easily create evaluation or rental versions of your applications. AppSentinel contains all necessary date logic and functionality.

Why Use AppSentinel?

    *      So you don't lose sales of your applications to software pirates! You worked hard developing your software and don't want people to use it without paying for it! AppSentinel copy protects your programs so only registered versions will run on registered computers. Applications using AppSentinel that are copied but not registered will stop running after a specified trial period.

    *      Use AppSentinel to let people try your program before they buy it. Adding AppSentinel to your application adds all the typical trialware features. Users can evaluate your software for a limited time before it expires. They can then unlock it once they register it.

    *      Using AppSentinel let's you focus on developing your application! No need to write your own trialware or rental logic or worry about protecting your software. AppSentinel contains all the logic necessary to create evaluation and rental versions of your applications and to stop unlicensed use of your programs.
===

BFN,

fp.
0
 
LVL 3

Author Comment

by:kaosyeti
ID: 19648053
wow.  this was a LOT to read through, as every link seemed to have 3 or 4 related links of its own.  let me give you a little background before i attempt to explain what i've been able to absorb here.

     for the majority of my life (i'm 33 now), i have had little direct experience with computers.  i had a commodore 64 once, but who didnt, honestly.  It wasnt until 1995 or so when I got my first windows pc, a machine used almost exclusively for printing poorly photoshopd pictures on regular 8 ½ x 11 paper and hours upon hours of minesweeper.   The only computer class i have ever had was basic in high-school (no, i'm not joking).  i was a biology major in college which i did nothing with as i didn't complete more than 2 years of higher-level education.  with no degree, i went out into the world as a schlub and wound up in retail.  in 2000, was hired by a car dealer and a year or so later was amazed by the simplest of calculations that could be done with excel  including averages (wow!).

     Over the next couple of years i more or less did nothing more with excel than calculate simple mathematical sums and averages, and never, ever, did anything behind-the-scenes (ie, VB related).  i have since been able to put together a few modest projects that are very useful for my job (and, as it happens, for other managers of dealerships owned by my company).  It was in the fall of 2005 that i was given the task of tracking customer traffic with an entirely too cumbersome spreadsheet which was 13 pages down by 8 pages across (12 months of the year plus a page for ytd totals and 8 different salespeople).  each page had a grid of 1-31 for the columns and about 10 different categories for the rows.  basically, each customer fit into a particular category on a particular day for a particular salesperson and i had to put a 1, 2, 3 or whatever into the cell that corresponded to how many of that type of customer each salesperson had each day.  to boot, they wanted to keep a separate spreadsheet for new car customers as opposed to used car customers.  after about 1 week of this, i was convinced that it was entirely too stupid to be done that way but had no idea of what else to do -- until some google searching pointed me towards access.

     Starting in September of 2005, i played with these things called 'tables' and 'forms' and 'queries' until i had the most basic idea of what they were for.  by the 1st of the year, i had a working database that tracked everything for me, complete with reports that can display the data when needed for any date-range or salesperson or department.  it was a tremendous breakthrough for my company because it literally saved me hours of work each week.  in fact, it was so revolutionary that not a whole lot of our other dealerships wanted to have anything to do with it, as they didn't understand how it worked.  now, i've integrated sales tracking and inventory control into the thing and there are several other stores using the thing.  i'm constantly tweaking and adding to the original program and have many features and designs that are of enormous benefit to our dealership group.

     now, i'm sure some of you are wondering why i'm telling you all of this.  it's because i haven't the faintest clue what some of your suggestions are, but i want to understand them in the worst way.  for instance, i don't know what a web server exactly is, other then what it sounds like -- a computer that hosts a web site.  i'm still not clear on what the difference is between VB and VBA.  i don't know how to define any kind of relationships betwen tables in my db without having the resulting query return odd results (like multiple entries or not enough entries), yet it has about 30 tables, 40 or so queries, a few dozen forms and reports and works more or less without issue or error.  now, given all of that, can you understand that i feel like the merest speck of computer literacy compared to all of you and yet i completely DWARF most of my peers at my company.  i'm not trying to be insulting, it's just that most of these guys don't care to know how the systems work, as long as they work -- a pretty universal norm for the non-programming world i figure.  so i'm not looking to keep out the hackers of the world, just the occasional, oddly curious person that wants to see how i made the text magically disappear and reappear as conditions are met.
i can't possibly see myself getting this thing out into the public domain but there have been a couple of hints/suggestions that it may go that route with my company as a part of the process.  i now want to at least lock the code from a cursory view, anyway.  I was not aware of how easy it is to lock the vb code away as harfang suggested.  This is a perfect solution for me at this point.  As for my database  a far more valuable program, I will definitely consider the MDE conversion as a best place to start.  The database is already split and when the front half is opened, it opens with limited menus to a launcher form (not a switchboard per se, as I didnt know about them when I wrote the thing.  The launcher just goes visible = false when you click a button to open another form and then comes back through that forms onclose event).  I have to say that you all have me a little nervous about how much security is enough.

    It sounds like MDE files and excel/VBA level encryption isnt going to cut it eventually so how does the whole web server thing work?  Would my database be stored on a remote server for backup purposes or would the forms and reports only run once someones logged in to the proper web site or what?

thanks for taking the time to answer this kind of basic level question.  remember, it's ok for you to talk to me as if speaking to a dull-witted child.  i don't mind -- really.

greg
0
 
LVL 58

Expert Comment

by:harfang
ID: 19650265
Hello greg,

I enjoyed reading your post. It's not often that we get such detailed feedback, and insight in who the person asking the question really is.

If you "dwarf" the users of your application and you didn't know of simple password protection, this will probably be enough. In the same vein, Excel sheets or workbooks can be protected using (Tools | Protection). And that is used very widely.

You cannot protect forms and reports easily, i.e. prevent your users from accessing design view, so that the MDE format does look attractive (it protects the VB code, but also all objects that can contain VB code: Forms and Reports).

As long as you remember that locking a project or protecting a sheet is like a padlock, you will be fine. I protect my bicycle from theft every day with a simple ¬100 padlock, don't I?

We can further assume (no offense intended) that your code isn't worth anything on the software market, so that theft isn't a risk. I think that an experienced database developer wouldn't keep a single line of your code if he was asked to rewrite the entire application. He would use the implicit logic, study the data structure, observe the workflow, but that is all apparent in the application and cannot be protected.

Finally, you apparently developed the code while being an employee, even if you did some of the training and some of the coding on your own time. That means the intellectual property is yours (by definition) but that the copyright is your employees. So it's not even really your problem in the end. However, as a nice touch, do write down all passwords you use, and put the in an envelope somewhere, so that they will be available should something happen to you.

Cheers!
(°v°)
0
 
LVL 3

Author Comment

by:kaosyeti
ID: 19684674
i've been testing different solutions and find that i have several observations about the common user that may be helpful to know for those of you with WAY more expertise than myself.  a perspective from a beginner-expert, if you will.

1. password protection is definitely enough to keep most of my users out of the excel formulas, let alone the VB code.  while i was grateful to learn you can protect the code in this way, it caused me a little aggravation/confusion when i went to adjust the properties of a control on the worksheet with said code and all i saw was an empty properties box.  had i went to make this adjustment some months from now, i may not have remembered that it was protected and wouldn't have put 2 and 2 together (locking the module/vb code means the properties won't show up in the excel screen either).

2.  and MDE file seems like the absolute best solution to do what i was asking for... except that there are times (for me, anyway) that i can log in to their PC by remote access and see whatever problem that needs correcting -- and i can't get behind the form/report/query whatever if it's an MDE file.  however, if i had sent my db to more locations than just 4, i would consider this to be the best option and therefore, the best answer to my question.

3.  the last is more of a question to harfang than an observation -- you mentioned that the intellectual property is mine but the copyright is my employers.  would this be the case if i developed this db as a means to make my life easier and it has not one lick to do with my job description?  as a part of my job, i keep track of customer traffic and was given a spreadsheet to use to do just that.  if i go out and create this db on my own 'down-time' (not while off the clock, however), does that mean that it's still considered 'work-for-hire' and therefore, not copyrightable by me?  i was about an inch away from paying about $150 to do just that while reading through the links of this post but i held off until i had the time to make sure i was dealing with a reliable/reputable company.  should i even bother?  i certainly don't think my code is worth stealing but the whole db is probably worth something to any number of other dealers out there and i'd rather get my name on this thing now, before something like that happens and i have to go back and prove anything.  your thoughts?

ps -- at heart, i'm still a salesman, not a programmer, so i can be a little wordy (if you didn't notice).  thanks for your understanding/support of my mini-life-story here.


greg
0
 
LVL 58

Expert Comment

by:harfang
ID: 19685225
As you say, even the mere protection of a sheet or a simple password protection of the VB project has its price. You will have to see for yourself which is the cheapest in the long run.

About intellectual property and copyright, I'm not an expert at all, and the little I know is obviously biased towards Swiss law (where it doesn't follow international law).

Intellectual property cannot be revoked or sold. Whatever you created remains your creation. In other words, you can say here that you wrote the application, and you can say it to future employees. The copyright holder cannot say you didn't write it, for example. So put your name in the code, in the properties, in the "about" box, in the documentation, etc.

Copyright is biased towards your employer, because you used company resources to create the application. Even the need for it stems from your job; you did not identify the need by yourself nor interview potential customers to establish what the application needs to do.

Since you created it answering a job-related need, mostly on paid time, using company owned equipment, software, furniture, and space, consuming company-provided power, paper, printer, and coffee machine, there is no question. The copyright is theirs.

You can't take it with you when you leave, you can't give or sell it, and you can't even upload it somewhere without their consent. Technically, you can't even take a copy on a USB key as backup. If your job description contained programming, it would be worse. There would be a clause stating that you will not develop anything similar after leaving the company, typically in the following three years. If you did, the company could still claim at least a partial copyright, just enough to prevent you from selling the entirely new application to competitors.

On the upside, you also have zero responsibility; this goes hand in hand. If the application stops working today, nobody can lay any sort of claim to you. If you leave the company, you have no obligation to maintain or debug your application. If the application is believed to have destroyed client data or to contain malicious code, the company will be held responsible, not you (unless they can prove criminal intent, naturally).

If this is important to you, take a second opinion. Ask a lawyer specializing in copyright, there might be important differences between countries.

Cheers!
(°v°)
0
 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 600 total points
ID: 19690555
Some useful PAQs, that I have contributed to, perhaps?...

"Still waiting for a solution... Protect VBA Code in a Workbook (continued 2)"
[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_21424558.html ]
(I provided some links on copyright law here)

"Expire license after period of time"
[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_21163112.html ]
( Discussion on password protection in MS-Excel)

BFN,

fp.
0
 
LVL 3

Author Comment

by:kaosyeti
ID: 19713462
this has been a tremendous help, guys.  i've come to accept that i don't know enough about copyright protection to try to copyright it myself, if that's even possible.

while i'm 100% sure that my code isn't worth squa-doosh anywhere (in fact, i'm sure that a good bit of it is quite ugly) it's the whole db that 'may' be worth something.  that could be pure arrogance on my part but there are a number of people who are not affiliated with my company who have seen it and suggested it's marketability.  the thought of someone else selling it w/o my knowledge makes me more than a little uncomfortable.  what, then, is the best way to hide my name all over it to protect what rights i have?

(at this point, i'm going to close the question as a 35/35/30 split with thenelson/harfang/fanpages.  i think i've stretched the boundaries of the original question about as far as i'm comfortable with as of this post.)
0
 
LVL 58

Expert Comment

by:harfang
ID: 19713986
Thanks. Let me add that even if your database isn't very "professional", it does work and is field tested. It also contains a certain amount of know how, business rules, and procedures. And as such it might very well be the ground for a marketable solution.

For now, make sure every module is commented (a standard block at the beginning with name, author, creation date, project name, last revision with your name again). Once you are sure which is which, you should also add a copyright line.

That's all you can really do. That and saving private copies regularly, if that is OK.

Success with your project!
(°v°)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 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