Hiding VBA code

[Haroon]

Hi,

I'm distributing a workbook with one worksheet which has some VBA
inc.connection to a password protected Access database, using ADO. All the
code is in a Sheet Module.

I can protect the code from viewing by password protecting the project.
However, if the user moves or copies the sheet to another workbook I see
that View Code will open the module for the connection code to be seen!

How can I make my code un-viewable?

Thanks,
Haroon

 

[gimme_this_gimme_that]

Hey Haroon,

For starters, store the connection information in an AddIn and only
distribute the AddIn to Administrators.

If the data is important and your business depends upon your data
AddIns are the ONLY approach to use.

But if you're doing an in-house thing you can try going down the
obfuscation road (or both the Addin AND the obfuscation road) ...

In the AddIn:
Store the connection information in password protected - hidden sheet.
Store the connection parameters in cells that have to be scrolled to -
so they aren't visible when you view the sheet. e.g. SS:3000.
Set the font color of the cells having the connection information to
white
Similarly, size the font as small as possible and change the zoom to
also be as small as possible. (That way, if a hacker knows this trik
he still might not see anything.)
Alternatively, for the entire sheet, set both the pattern and the font
all to white.
In VBE Properties give the Sheet an innocous name, like
ProjectionUtils, discouraging anyone from looking for connection
information there. But some data in there that looks like it might be
used for "Projecting".

An experienced VBA programmer will be able to find the connection
information after about 10 minutes, less if they've seen the trick
before, but most non developers will never figure it out.

Also, create a separate database account that has only read only
access, so if a user discovers the connection information they can't
attack your system.

Your SQL statements should mostly execute against VIEWs instead of
against tables. If you're using an AddIn, store the modules having the
SQL statements in the Addin as well.

Yet another option is not to use a database connection at all. Make it
so the data is fetched hrough an XML web service.
Incidentally, you can read XML from a URL using ADO almost as easily
as reading data from a database using ADO.

 

[Peter T]

All the code is in a Sheet Module.

Move all sensitive code out of Sheet modules, eg

Private Sub Worksheet_Change(ByVal Target As Range)
Call ProcInNormalModule(rng:=Target)
End Sub

Password protect your project will deter most non-inquisitive non-developers
from prying.

Regards,
Peter T

 

[Haroon]

Thanks both of you for your thoughts.

Gimme said:
<<Incidentally, you can read XML from a URL using ADO almost as easily
as reading data from a database using ADO.<<
Yes, this is the way I would go eventually. But wouldn't I still need to
send a connection string, this time in text (as it's XML) so will it not be
readable ?

Haroon