Blogger
Share this content
0
14
12894

Hiding macro code

I would like to hide the VBA code of a macro in an Excel file which will be sent to various clients. The idea is that they could run the macro normally using a macro button, but I'd like them to be unable to see or copy the code, to protect my IP.
Is there a way in Excel to "hide" the VBA code so that it is invisible to a user?

Alan
Alan Brooke

Replies

Please login or register to join the discussion.

By David2e
10th Sep 2008 11:15

Just hiding the code
As Clint points out - Excel really is poor at providing any real protection of data or code in the files.

It is something I failed to mention in my first post as I simply replied to the 'hide the VBA code so it's invisible to a user' and security did need to be pointed out.

Like Alan, our first reason behind "protecting" code is to prevent others from breaking it!

It's just impossible to support people that seem intent on finding every way to break things which is why we also include an ongoing development service.

David Toohey
The Accountants Circle

Thanks (0)
avatar
By ACDWebb
09th Sep 2008 23:35

Clint
sure I understand the reasons why.

Obviously none of that is of much use if the user can just pass on the spreadsheet in the absence of other restrictions in the file to stop its use by someone else.

I like to see what is going on out of interest in how someone has achieved an action - and because I am a little sad ;)

Unless it is of general use a macro is normally set up for a specific task in the book so that copying out for something else will generally be be more time consuming in getting it to work in the new situation than writing from scratch.

No argument with you on this, just my 6p

I have written some fairly substantial macros for use in my firm, so a rather different situation, but the only reason I tend to put passwords on the VBA for those is to stop others getting under the hood & messing things up. The two largest are set up for such specific tasks that generally the code is not relevant outside of what it was written for.

Thanks (0)
avatar
09th Sep 2008 16:21

To Alan Webb
It may not be of concern to the OP, but the most common reason for wishing to hide code is to protect the author's revenue that might be available from the sale or licensing of the workbook for use by others. Not that hiding the code by itself prevents unauthorised redistribution, but it is a first step. In this area Excel appears woefully inadequate.

Thanks (0)
By David2e
09th Sep 2008 13:05

Thanks Bala shan for your kind words.

I suppose I was simply the first to respond, and most of the others simply added to that as there can certainly be other issues to consider.

Accountants can be a cynical bunch, and although you and others may simply want to hide the VBA code as a matter of personal preference for whatever reason... you will get to hear almost every other issue that might be related - even if it's not a concern for you!

David Toohey
The Accountants Circle

Thanks (0)
avatar
04th Sep 2008 23:45

Bala shan ...
... I think that you are deluding yourself if you reckon that the solution to which you refer provides the security that you appear to express.

Thanks (0)
avatar
By ACDWebb
05th Sep 2008 09:53

The question becomes
how many clients will really give a damn what the macro code is so long as it works, and want to go that extra mile & cost to crack a password set to lock the project. Perhaps only those with a bit of VBA knowledge who want to have a "look under the bonnet"

And surely re getting the code off the macro run list all one needs to do is set the macro as private and then assign the macro (now private) to the button - assuming it is all in the same workbook?

Thanks (0)
avatar
04th Sep 2008 11:35

Thanks to David Toohey
I tried the solutions of all of you people but it was only David Toohey's 23rd-October soultion that helped me achieve hiding the VBA code before providing my EXcel to the client.

Thanks a lot David Toohey.

Thanks (0)
By David2e
23rd Oct 2007 16:06

VBA Protection
This can be done easily enough.

Go into the VBA Editor, right click on VBAProject and selectr VBAProject Properties.

From there you will be able to go to Protection and password lock it for viewing.

Do test the code first, as if any errors come up the debug won't work (unless you first unlock the VBAProject).

David Toohey
The Accountants Circle

Thanks (0)
avatar
23rd Oct 2007 17:02

Just to add to David's comment ...
The password protection is trivial to crack, at least in any version of Excel before 2007. It may be more secure in 2007, but may not (I know nothing about the 2007 version). Presumably the workbook needs to be capable of running under earlier versions anyway.

So, if you want it to be truly hidden from the users, do not be lulled into the false sense of security that the protection within Excel provides.

Perhaps a better method would be to include the code in a compiled add-in. Don't ask me how to do that, though.

Thanks (0)
By David2e
24th Oct 2007 08:22

Add-ins
Also unsure in Excel 2007 if the password protection is any better.

Hackers can easily get past password protection in Microsoft Office files, but for most people that have no intention of stealing code it should be ok... it prevents people from looking at it without hacking.

Add-ins are actually no more secure than a normal xls file. So on the security side it wouldn't help. Worth considering depending how you distribute the file, if it is to be updated etc.

David Toohey
The Accountants Circle

Thanks (0)
avatar
By Anonymous
24th Oct 2007 08:55

External dll ...
To expand on Clint's suggestion

see: http://www.dotnet247.com/247reference/msgs/23/117148.aspx

Otherwise one just has to do a Google search on cracking excel macro/vba passwords and see the number of people offering the facility - i.e. just one

http://www.passwordrecoverytools.com/tool_excel.asp

Thanks (0)
avatar
31st Oct 2007 13:43

Thank you!
Thanks very much - that seems to work very well!

Thanks (0)
avatar
31st Oct 2007 17:20

there are two elements to this
One is to password protect the vba module, as already described. (along with a shortish but valid debate about how ineffective that is!)

But users will still be able to see and run the macros from the excel run macro menu item. However, if you include a parameter in each macro then this hides them from the users

e.g.
Sub AWeb()
End Sub

will be visible from the run macro menu, but

Sub AWeb(Flower As Boolean)
End Sub

will not

to call AWeb from Command Button 1:

Private Sub CommandButton1_Click()
Call AWeb(True)
End Sub


hope this helps

Thanks (0)

New Excel Compendium guide
Hi Alan, for a more general discussion of the whys and hows worksheet protection, have a look at Simon Hurst's latest Excel Compendium digest article, Using Hide and Protect.

Thanks for adding to the discussion - and we'll have to update the article with Alastair's routine.

Cheers
John Stokdyk
Technology editor
AccountingWEB.co.uk

Thanks (0)