locked
How to hide Excel VBA module?

    General discussion

  • Hi everyone, please allow me to pick your brains and hopefully gather some useful pointers on the way.

    I'd like to sell an Excel workbook, that works in the Excel environment as a normal spreadsheet. So, new tabs can be added and edited by the user, calculations performed as per normal, etc. It is shipped with one tab only, with my VBA hot stuff in it. I'd like the workbook to prevent users from accessing the VBA code of this one tab. Besides the hot stuff, the code in the one tab would require activation keys to run, allow trial periods, etc.

    All this would be possible if I could simple select one worksheet and lock it (here, excel would encript and recript the worksheet's VBA one million times with a billion character key, then once more, just for good measure) and that would be it, so no one, not even myself could ever get to it. 

    It turns out that this is much harder to do than it initially appeared to me. I've researched ways to hide Excel VBA code and found that most ways discussed are either absurdly insecure (password) or insecure and cumbersome for the end user (add-ins).

    From my research, it seems that I could build an ActiveX DLL, put the hot stuff there, then call it from the one worksheet. I'd need the DLL to then control the workbook, create new worksheets, and update cells in them, etc., as if it was running as VBA in Excel. Once finished, control would be returned to Excel.

    Is this possible and can it be built from Visual Basic 2008 Express? Is it secure, or can it be easily hacked? How would you tackle the problem?
    Monday, February 22, 2010 4:07 AM

All replies

  • I don't know but I do know one. This is not a VBA forum.

    Renee
    Monday, February 22, 2010 4:46 AM
  • Hi Renee, thanks for your reply. Perhaps I can rephrase the question thus: it it possible to create in Visual Basic 2008 Express an ActiveX DLL which is called from an Excel workbook and then gains control over the workbook inserting new worksheets and updating existing worksheets as needed? If so, can the DLL be easily reverse engineered and is it possible to prevent it?
    Monday, February 22, 2010 5:02 AM
  • The answer is I doubt it. I think active X is wriiten in c++. BUT I never knew what made active X, active X. I just knew it was an aging technology but it always worked.

    Renee
    Monday, February 22, 2010 5:16 AM
  • There are much better tools available with VB 2008 to work with Excel.

    You may want to start here for more information:

    http://msdn.microsoft.com/en-us/vsto/default.aspx

    Hope this helps.
    www.insteptech.com ; msmvps.com/blogs/deborahk
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!
    Monday, February 22, 2010 5:26 AM
  • This question is one that i don't think will ever truly be answered with the ultimate secure solution.

    I think overall the short answer to can you secure your workbook is no.

    many i see are creating dlls in .net to use in their vba project which atleast doesn't give them direct access to the code even if they break your password.  i am not sure if you obfuscate the code and still use it in vba, using dotfuscator anyway.  i just have not played with it.  but i don't think it really affects the code in a way that vba would not be able to read it.  From what i have seen it really is just a renaming convention to make the code very confusing.

    you can use a third party tool called spreadsheetgear for .net which has some features to hide the sheets better called (very hidden) but the sheets can still be shown using vba code if they can get in.

    There is an estimator for a commercial plumbing company which i used to work with at another company.  at this other company he had been using a workbook he had created on his own to make estimating easier.  it was a very detailed set of sheets but overall very simple.  it was very good, but had a flaw being that it was an excel workbook.  it was so easy for others to take and use.  he an i have not gotten together on it yet but i am going to help him combine his workbook into a .net application to make it a little more secure.  excel just doesn't seem to cut it in this area.

    i wish i had the answer for you.  i think really the best thing is to just do what you can to make those will take, work for it.  i still believe that if they really want it, they will get it.  you just have to be ok with that because at some point you may spend so much time on trying to secure it that you lose track of what is important about what you created.

    i am trying to get more involved with web "stuff" which makes it a little better not having to send your files to someone who will then have all the time they need to break it.  i have a tool that i developed to check database table and column names against a database or data provider reserved keywords.  i didn't want to send my database out to everyone since i spent a good while creating it, so i integrated it into my website.  i don't have to worry about it as much that way.  really i don't care about it in the end, but it was a fun little project to do on the web.  it was somewhat more of a learning experience than to really try and prevent others from getting it.

    well, hope my rambling helped you some.
    FREE
    DEVELOPER TOOLS     CODE     PROJECTS

    DATABASE CODE GENERATOR
    DATABASE / GENERAL  APPLICATION TUTORIAL
    Upload Projects to share or get help on and post the generated links here in the forum
    www.srsoft.us
    Monday, February 22, 2010 2:09 PM
  • Hi Deborah, thanks for your reply. Would you have any specific tools in mind, for me to explore? Everything there seems targeted at Visual Studio proper. I did initially start at that URL and ended up somewhere around the present neighbourhood. But the fact is that I am not familiar with the available tools, and there seems to be a lot of them.
    Monday, February 22, 2010 3:43 PM
  • Hi Jeff. Thanks for your reply. Yes, I'm looking for a solution that would remove the VBA code from Excel altogether, to a component outside of Excel; so obfuscator is not an option (though HandyOfuscator seems to do a good job of it).

    SpreadsheetGear seems to be able to do something along the lines I need. For example, they say "Create, read, modify, view, edit, format, calculate, print and write Microsoft Excel 97-2003 (xls) and Excel 2007 Open XML (xlsx) workbooks without Excel.", although I'd really like a way of doing it with Excel.

    Your suggestion of hiding the stuff on the backend of a site is a good one, if the functionality it provides is ok for that sort of batch access. In my case, the whole thing is about Excel workbooks working in Excel and updating them on the go within Excel itself, just reading some parameters from a sheet, retrieving data from the internet, them creating new sheets and updating existing sheets. So, I have to look for a different solution.

    In further readings and searches I came across this description, which seems to validate the whole concept: EMAGENIT. If anyone could point me to good references for learning how to build ActiveX DLLs in Visual Basic or how to interface from them into Excel, I'd very much appreciate it to avoid going through endless possible sources to find the really useful one.
    Monday, February 22, 2010 4:20 PM
  • Hello,

    You can simply password protect the source code: http://www.databasedev.co.uk/vba_password.html

    Edit: After reviewing other responses, please do not attempt to pioneer a new form of security. The only way to truly protect your code from prying fingers is to never release it.

    Adam
    Dibble and dabble but please don't babble.
    • Edited by Adam_Turner Monday, February 22, 2010 5:00 PM
    Monday, February 22, 2010 4:33 PM
  • Hi Adam, thanks for you reply. If you Google "excel password remover" right now, you'll get around 7 million hits. According to this guy, one can simply download a hexadecimal editor, and replace the three lines in the Excel file that encode the password (CMG=...., DPB=..., GC=...) with the same three lines from a file whose password you know. If that fails, you can send the workbook to him and he will send you back the unprotected version. So, I'm looking for something else, eheh.
    Monday, February 22, 2010 4:50 PM
  • bmwb,

    Honestly, get copyrights and patents to protect your design. That is the 'only' way to protect your code if it's worth it. I can tell you that most software applications typically cost around $20,000 per application based on the complexity and that still doesn't guarantee a patent.

    Remember that any code can be reverse engineered.

    Adam
    Dibble and dabble but please don't babble.
    Monday, February 22, 2010 5:08 PM
  • Hi Adam, yes that is a valid general point you make, though in practice the results rarely justify that effort unless you're a large corporation, or target a very tiny market niche.

    The problem here is that registering copyrights and patents are lengthy, burdensome, bureaucratic and expensive processes. The expected return from this effort does not warrant such an investment in the least.

    Yet, I find the end product so useful for its particular purpose that, if sold unprotected, one can easily guess the expected sales to be a very, very, tiny small fraction of its potential users. The investment of learning how to program ActiveX DLLs and rewriting the entire program if necessary, in order to increase the fraction of paying users, is much more appealing. And besides, learning how to build DLLs is a curiosity I've always had.

    Currently investigating how easy it is to reverse engineer those things; only 480,000 Google hits on that one, so not looking very good right now.
    Monday, February 22, 2010 6:17 PM
  • Why do questions like this appear primarily on the express forums?  Naivete.
    Monday, February 22, 2010 6:30 PM
  • bmwmb,

    First, ActiveX is outdated mainly due to the often malicious code that was tucked in to it from a many mischievous programmers, it doesn't run a MAC at all, and SQL Server 2008 doesn't support it. I'd discard this avenue quickly.

    Secondly, .dll's need reference calls from your VBA code. If you can get to your VBA code and  you have the .dll that's shipped with your solution, you'll have just as easy access as you would if you password protected the code. Another lost cause here.

    Lastly, and I've already touched on the subject, trying to reinvent the wheel is a dangerous path. Tradition has proven much more reliable than ad hoc. The only true way to protect your code without a lawful document is to never write it. Honesly, it's really that simple.

    Adam




    Dibble and dabble but please don't babble.
    Monday, February 22, 2010 6:34 PM
  • John,
      That question has a simple answer. That is because the people  that uses VB express are mostly beginners or intermediate developers and have a hard time finding information. They go around in circles without finding an answer from the help files.

    Curtis
    Always Lost in Code,
    Monday, February 22, 2010 6:56 PM
  • Hi John, I was rather surprised by your reply and will leave it at that.
    Monday, February 22, 2010 6:57 PM
  • Adam,

    You've been thrown off these boards by Microsoft Management. You really ought to leave now.

    Renee
    Monday, February 22, 2010 7:04 PM
  • Even though Adam has been thrown off the boards by Microsoft Management, I agree with him even though he knows he should be leaving.
    Monday, February 22, 2010 7:11 PM
  • First, ActiveX is outdated mainly due to the often malicious code that was tucked in to it from a many mischievous programmers, it doesn't run a MAC at all, and SQL Server 2008 doesn't support it. I'd discard this avenue quickly.
    Point taken.

    Secondly, .dll's need reference calls from your VBA code. If you can get to your VBA code and  you have the .dll that's shipped with your solution, you'll have just as easy access as you would if you password protected the code. Another lost cause here.
    Yes, I understand. The purpose was just to hide the VBA code itself. Access would be granted by the usual licencing scheme, specific to the machine where the application is running, in order to prevent precisely that form of abuse.

    Lastly, and I've already touched on the subject, trying to reinvent the wheel is a dangerous path. Tradition has proven much more reliable than ad hoc. The only true way to protect your code without a lawful document is to never write it. Honesly, it's really that simple. [...]
    Yes, the point was initially taken. But as also pointed out, surely you agree that even with a lawful document in hand, one has little or no hope from preventing unwarranted general use of an unprotected application. The difficulty starts right at identifying such use, then making the legal document stand anywhere in the world where that use is identified. In other words, unless you are a large corporation you're out of that game.

    We are in agreement that there's no sure proof way of protecting anything. The point is in making it sufficiently hard for most people to just simply give up.
    Monday, February 22, 2010 7:28 PM
  • bmwmb,

    I understand your point-of-view as do others which is why service-oriented-architecture is catching on quickly.

    Developing efficient abstraction layers 'will' protect your code through well thought-out authentication methods.

    One 'possible' but overly simple solution to catch and handle Alt-F11 or disable/hide the menu options to navigate to the code. I know the end-users may gripe, but the idea here is to find more practical methods to meet your need.

    Adam
    Dibble and dabble but please don't babble.
    Monday, February 22, 2010 7:36 PM
  • Trying to protect code is naive.  If you implement a concept, you have shown that it can be done.  For someone else to implement it is usually trivial. 
    Monday, February 22, 2010 8:01 PM
  • i think the first question i might ask is who are you trying to protect this workbook from?  are the users smart enough to understand programming and find a way to get into your code?  what other aspects about your users do you need to consider.

    back to the estimator i am going to help, simply taking excel out of the picture will protect his program well enough because his users are not advanced computer people.  i really don't expect them to have even the slightest clue how to get into .net code.  the good thing about it is he already knows others have gotten it for free so he is comfortable with the idea that he can't control it completely.

    just some other thoughts to keep in mind.  starting this year i am completely rethinking my entire ideals on compensation for my work.  i have decided to work with the piracy issues and make it work for me.  i will be giving all my software away for free and selling advertising instead.

    spreadsheetgear, to keep in the back of your mind for future reference will also allow you to work with excel workbooks in a web application and stream the workbook back to the client.  there is not graphical interface for web (unless they added it recently) but you can still work with the data.  inside of a windows forms app however, it may give you a start to atleast get your code out of vba.  you have quite a few options now with .net because you can automate excel and work with your workbooks inside your app using spreadsheetgear.  you might look at using a combination of both that will give you better control over it.  and i think deborahk mentioned vsto so that is another option, but is not available in the express editions.

    if you want to play with spreadsheetgear to see if you can make it work for you in some way you can get the free version in your vb express registration benefits portal.

    no matter what you do, making the code hard to understand is defintely a good thing.  i have considered before to write a little app that will generate a great deal of functions that will pass data around to make it very time consuming to understand.  i was thinking maybe 100 or even 1000 functions.  there are still some flaws there because there is still a start method and an end method,  i think if they had to look at all those functions it would deter them enough.  depends on how in demand your application is and how determined they are.  some of these hackers and crackers have nothing but time.



    maybe another thing to consider is also the language ability of your users.  possibly they have vb experience and could understand vba and even vb.net code but do they know c# or c++.  i don't have any experience with c++ but i would imagine you could create a dll to add to your vb.net project.  c# for sure.

    well, hope this helps
    FREE
    DEVELOPER TOOLS     CODE     PROJECTS

    DATABASE CODE GENERATOR
    DATABASE / GENERAL  APPLICATION TUTORIAL
    Upload Projects to share or get help on and post the generated links here in the forum
    www.srsoft.us
    Monday, February 22, 2010 9:23 PM
  • I understand your point-of-view as do others which is why service-oriented-architecture is catching on quickly.

    Developing efficient abstraction layers 'will' protect your code through well thought-out authentication methods.

    One 'possible' but overly simple solution to catch and handle Alt-F11 or disable/hide the menu options to navigate to the code. I know the end-users may gripe, but the idea here is to find more practical methods to meet your need.
    Thanks for being constructive, Adam. Your previous point on ActiveX set me thinking along lines similar to disabling access to VBA, but as you rightly point out it's a nasty solution.

    So dropping the ActiveX idea, I went looking for Office Automation resources and found How to automate Microsoft Excel from Visual Basic .NET, which appears to allow moving the functionality to an external application.

    The one thing I cannot find stated clearly is whether it can interface with Excel on the go, to gather and update data? Or is it simply a way of using the Excel object model to create Excel objects such as workbooks?

    In other words, using Office Automation, can I open a workbook in Excel, press a button that triggers a method in a Visual Basic application, which then is able to trigger a few worksheet creations and updates in the original workbook which is open in Excel? Anyone?
    Monday, February 22, 2010 9:29 PM
  • Well yes,

    Office automation is a great way to hide your code. Not sure why I didn't think of that...lol

    You've just answered your own question.

    Adam
    Dibble and dabble but please don't babble.
    Monday, February 22, 2010 9:36 PM
  • Another option is to create an Excel add-on through .net: http://www.managedxll.com/

    Adam
    Dibble and dabble but please don't babble.
    Monday, February 22, 2010 9:40 PM
  • i also wanted to mention something about patents and copyright, etc...

    i have a contract with two clients that are basically two companies in one.  thoughout my dealings with them i have found that they like to treat emplyees like slaves and bully them.  they also are very shady in their business deals making deals with employees in their clients companies using kickbacks for specail treatment and apparently may even be taking money off the books to not pay taxes; all cash of course.

    they tried to use their influence with a big company they get work from to put pressure on them to get rid of a bunch of small companies they compete with so they could liine their pockets with more money.  that deal fell through but after all this coming to light i had decided some time back to not do any work for them anymore.  i don't feel comfortable being involved in such a thing and it's not right.

    so what does this have to do with copyright, etc...  i have a contract with them that includes the EULA for the software i created for their use.  this protects me as far as a legal stand point for reverse engineering because i know they are already looking someone to replace me.  But, i know they will most likely show this another developer what i have done and let them copy it for them.  so the dilemma is, how can i really stop this?  truthfully i can't without a lot of trouble and cost.  and how will i prove it when i do not have access to see what they are doing.

    so what can i do about this? well, not a lot that's worth my time.  goog thing about it is the software is based on specific paper sheets that i recreated in the software, and these sheets change periodically so my software is only good to them for a short while since under my contract i can't just pull it from them.  the satisfaction i have is that they will have to pay for someone to reproduce my work all over again.

    so in short i can sit here and be angry about it but it's not worth the wasted stress.  it will catch up wtih them at some point.  i will do what's right on my end and hope they learn a lesson.

    not sure this helps you any really but i think it may put into perspective some of the whoes of development and "protection".

    Peace
    FREE
    DEVELOPER TOOLS     CODE     PROJECTS

    DATABASE CODE GENERATOR
    DATABASE / GENERAL  APPLICATION TUTORIAL
    Upload Projects to share or get help on and post the generated links here in the forum
    www.srsoft.us
    Monday, February 22, 2010 9:41 PM
  • Office automation is a great way to hide your code. [...]

    Great! But can I open a workbook in Excel, press a button that triggers a method in a Visual Basic application, which then is able to trigger a few worksheet creations and updates in the original workbook while it is still open in Excel? Do you know? Anyone?
    Monday, February 22, 2010 9:41 PM
  • i think the first question i might ask is who are you trying to protect this workbook from?  are the users smart enough to understand programming and find a way to get into your code?  what other aspects about your users do you need to consider.

    [...]

    maybe another thing to consider is also the language ability of your users.  possibly they have vb experience and could understand vba and even vb.net code but do they know c# or c++.  i don't have any experience with c++ but i would imagine you could create a dll to add to your vb.net project.  c# for sure.
    You make some interesting points here, Jeff, and it certainly helps. My issue with the users is that this thing will be out there in the open, and open to a wide range of personalities and their friends. All it takes is for one such personality to make the thing generally available in forums and such, and game over.

    I like the idea of free software distribution with advertising attached and would be interested to know about your results with it. One would need to give some thought to effectiveness issues, but there is a world of possibilities to explore in terms of how and when to deliver the advertising that are, efectively, closed to Google Adworks. In other words, Google is there on page presentation and that's it, whereas your idea can be at specific places, at specific times. The results of the next program action can be dependent on viewing such-and-such and that's why it's free. Similar to the initial seconds of videos on news sites. Very interesting.

    And yes, I will be evaluating some points in SpreadsheetGear. Thanks for this input.
    Monday, February 22, 2010 9:58 PM
  • Office automation is a great way to hide your code. [...]

    Great! But can I open a workbook in Excel, press a button that triggers a method in a Visual Basic application, which then is able to trigger a few worksheet creations and updates in the original workbook while it is still open in Excel? Do you know? Anyone?

    Yes use an excel add-on. See post below.

    Adam
    Dibble and dabble but please don't babble.
    Monday, February 22, 2010 10:01 PM
  • [...] so what does this have to do with copyright, etc...  i have a contract with them that includes the EULA for the software i created for their use.  this protects me as far as a legal stand point for reverse engineering because i know they are already looking someone to replace me.  But, i know they will most likely show this another developer what i have done and let them copy it for them.  so the dilemma is, how can i really stop this?  truthfully i can't without a lot of trouble and cost.  and how will i prove it when i do not have access to see what they are doing.

    so what can i do about this? well, not a lot that's worth my time.  goog thing about it is the software is based on specific paper sheets that i recreated in the software, and these sheets change periodically so my software is only good to them for a short while since under my contract i can't just pull it from them.  the satisfaction i have is that they will have to pay for someone to reproduce my work all over again.
    Yes, I'm quite familiar with those issues. If your potential market is fairly small (ie. you're really in a niche) the problem is even worse, because you will want and need their business in the future. So, no amount of copyright or license infridgement law suits will get you through the burned bridges on the way.

    These things are on a different stand if you are a large corporation with a legal department and have a large worldwide customer base to stand on. In some European countries the approach to intelectual property of software is quite different also, which creates other problems.

    You seem to be fairly protected by the paper sheets feature, which is good. But, at the end of the day, one's goal should be for customers to never be adversely affected. By your description of the two clients you probably want to be far from them, also, eheh. Best of luck with that, Jeff.
    Monday, February 22, 2010 10:19 PM
  • Hi folks, I'm facing exactly the same dilema as rgds securing code and I am thinking along the line....

    As no one method would be absolutely safe, I intend to prevent the ordinary user from using one of the many password cracking tools to open VBA project, by completely hiding the VBA code .. and was thinking if its possible to prevent the VBE from being visible altogether? or atleast the password interface to it? there must be a dll for it surely and if that can be set to be invisible or disabled on Workbook_Close it should add a layer of security to the project (sufficient for a non tech person to NOt be able to use and deploy hex editors etc) ?? Any thoughts? if someone could point me in the right direction by advising the the right dlls to be looking at ?

    What exactly is the msvbprjt.dll?? Could that be the answer?

    Cheers!


    Tom
    Sunday, June 19, 2011 5:43 PM