How to mask excel macro code

Question

I have the below stored in excel macro:-

Sub Sales()

Dim StrSQl As String

Con = "Provider=IBMDA400;Data Source=192.168.2.2;User Id=boss;Password=1we56"

Set Db = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.recordset")
Db.connectionSTring = Con
Db.Open
StrSQl = "select myuc, sum (myac) as Amount from myabc.myqwerty where mydt >= 20100101 and mydt <= 20100831 group by (mycl)"
rs.Open StrSQl, Db, 3, 3
Sheet1.Cells(10, 1).CopyFromRecordset rs
rs.Close
Set rs = Nothing
Set cn = Nothing
End Sub

I wish to mask the above User ID & Password ie. User Id=****;Password=***** as part of security.

Is that possible?

Answer 1

Your most secure option - regardless of whether the user needs to type a password in or not - is to protect your entire macro code

• Enter the Visual Basic Editor (VBE)
• select the project you wish to protect in the Project Explorer window
• right cliick then ....VBAProject Properties
• click the Protection tab and then check "Lock project from viewing" and verify your password
• Save your workbook, close it and re-open it to establish the protection

Short of writing a COM addin this is secure as your code will get. Be warned that there are products available that will crack VBA code

Answer 2

Con = "Provider=IBMDA400;Data Source=192.168.2.2;User Id=boss;Password=1we56"

AARRGGHH!! What are you thinking?

Here's the deal. No amount of encryption is going to help you out here because, if Excel itself can unencrypt the data (and it can, otherwise the connection could never be made), then a malicious type can also do it.

The right way to do this is to ask the user for the user ID and password and use that information to dynamically construct the connection string.

That way, the sensitive information exists only in the head of the user and (temporarily) on the machine they're using (which is probably their machine anyway). It's not in an Excel spreadsheet somewhere that anyone can get at.

And, on top of that, functional IDs (shared amongst different users) are almost always a bad idea since it makes auditing a nightmare.