Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 4th, 2003, 08:58 AM
Authorized User
 
Join Date: Sep 2003
Posts: 25
Thanks: 0
Thanked 1 Time in 1 Post
Default How to hide VBA code

Hi,

I need to create a spreadsheet that contains a set of special functions for manipulating data. Obviously this can be done with VBA. However, it is vital that users of the spreadsheet are unable to see or edit this code. What options do I have? This is a new area to me, so I can envisage a couple of possibilities:

a) Write a plug-in for Excel that adds some new menu options that run the code behind the scenes. The code spits out the results to cells as required.

If this is possible - how do I do it? How straightforward is it to write a plug-in? How is it installed be the user etc. etc?

b) Write some VBA (as normal) but somehow "protect" it so that it can't even be looked at by the user.

I don't know if such a thing is possible - is it? If so, how do I do it?


There may be other options that I don't know about. I am relatively new to Excel, so am not sure what my options are.

Please let me know what I can do - all suggestions gratefully received a.s.a.p.

Many thanks.

James


 
Old September 4th, 2003, 09:27 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Open the Excel Visual Basic Editor, then in it's menu choose
Tools > VBAProject Properties > Protection
check the box and fill in the password fields.

hth
Phil
 
Old September 4th, 2003, 09:36 AM
Authorized User
 
Join Date: Sep 2003
Posts: 25
Thanks: 0
Thanked 1 Time in 1 Post
Default

OK - thanks.

I can see (from some more reading) that I can write as "add-in" and password protect it.

Next question: How secure is the protection? Are there ways of hacking around the protection so that someone can see the code? It is vital that I prevent the code from ever being seen, as the code will form part of a commercial product.

Hope you can help. Thanks.

James

 
Old September 4th, 2003, 09:49 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Its a fair question to which i don't really know the answer, but as there are people who make a living by selling password recovery software, I guess its not 100% secure.

Have you considered writing a COM object containing the code that you wish to protect? You could then distribute the compiled COM object with your product and there would be no human-readable script.

Excel can use COM (aka ActiveX) objects via the CreateObject function.
 
Old September 4th, 2003, 10:06 AM
Authorized User
 
Join Date: Sep 2003
Posts: 25
Thanks: 0
Thanked 1 Time in 1 Post
Default

Thanks.

Yes - I suspect that an add-in may not be secure for us and that we really need to write a compiled product.

You mention ActiveX objects - please can someone advise how I go about writing one, and how it gets integrated into the spreadsheet at run-time? What development environment do I need to write an ActiveX object?

To repeat - I am pretty new to this area, so please provide the very basics in your answers, with more detail on top of that, if possible.

Many thanks.

James

 
Old September 4th, 2003, 10:19 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Two common options for writing COMs (ActiveX):
1. use Visual C++ and the ATL COM wizard
2. use Visual Basic and create a new ActiveX DLL project

Whichever you choose, you need at least one public class in your project and any methods you need to call from Excel will need to be declared public too.

There are many tutorials out there about creating COM objects - take a look at a few and ask any questions you may have here.

As for integrating it, you will need a setup program that installs the COM object and registers it - again all standard stuff which you find plenty of examples of on the web.

Once the object is registered you can easily get at it from Excel by setting a refernce to it from within the Excel VB editor (Tools > References), then have code like this:
Dim x As YourProject.YourClass
Set x = CreateObject("YourProject.YourClass")
y = x.YourMethod(param1, param2)

etc

hth
Phil
 
Old September 4th, 2003, 11:33 AM
Authorized User
 
Join Date: Sep 2003
Posts: 25
Thanks: 0
Thanked 1 Time in 1 Post
Default

Thanks, Phil.

I assume you mean that a user has to install the AciveX object on their PC, which means they can then call the class methods from a few lines of VBA code within the spreadsheet. Is that right? Have I understood correctly?

If I write an ActiveX object, is that the sort of thing that we can sell commercially? Is packaging and selling AciveX objects something that is commonly done these days?

Forgive my ignorance about this kind of thing. Sometimes the basics have got to be filled in before progressing down a particular route.

Hope you (or someone) can fill in the gaps in my knowledge.

All related posts on this subject gratefully received.

Thanks.

James

 
Old September 5th, 2003, 03:46 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi James

Quote:
quote:I assume you mean that a user has to install the AciveX object on their PC, which means they can then call the class methods from a few lines of VBA code within the spreadsheet. Is that right? Have I understood correctly?
Yep that's exactly what I mean.

Quote:
quote:If I write an ActiveX object, is that the sort of thing that we can sell commercially? Is packaging and selling AciveX objects something that is commonly done these days?
If you take a look at, say, an ASP developer site you'll see loads of people flogging components to do all sorts of things. These components are ActiveX objects.

At the end of the day only you know the ins and outs of what you're trying to achieve and why. I'm just making suggestions ;)

hth
Phil
 
Old September 5th, 2003, 06:52 AM
Authorized User
 
Join Date: Sep 2003
Posts: 25
Thanks: 0
Thanked 1 Time in 1 Post
Default

Thanks Phil.

That's fine.

One further queation: I am going to write the ActiveX DLL with Visual Basic. Do you know if the Visual Basic .NET Standard Edition enables me to do this? (Obviously, it could be done in VB6, but I don't know if the .NET version caters for it - I presume it does. We need to find out before we buy it, but I can't find any reference to this in the marketing info for the .NET product)...

I know these are really fundamental questions...excuse me for being a beginner...thanks for your patience in replying.

James

 
Old September 5th, 2003, 06:58 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

I don't use .NET, so I don't know.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Hide Task bar through vb6 code akhilesh_g Pro VB 6 6 December 28th, 2007 01:23 PM
Code works in Excel VBA but not Access VBA fossx Access VBA 2 May 21st, 2007 08:00 AM
Hide table with VBA mmcdonal Access VBA 5 June 24th, 2005 10:13 AM
Hide Row with VBA Butch Excel VBA 2 November 24th, 2004 09:11 AM
Hide VBA code when forwarding macro sh333384 Excel VBA 7 September 2nd, 2004 10:23 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.