How to mask excel macro code

Question

I have an excel macro below: -

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 want to mask the above user id and password, i.e. User Id=****;Password=***** as part of the security.

Is it possible?

Answer 1

The safest option - whether the user should enter a password or not - is to protect your entire macro code.

• Enter Visual Basic Editor (VBE)
• select the project you want to protect in the Project Explorer window
• right cliick then .... VBAProject Properties
• go to the "Protection" tab, and then check the box "Block project from viewing" and confirm your password.
• Save your book, close it and open it to establish protection.

With the exception of the COM addin, this is safe since your code will receive. 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 do you think about?

Here's the deal. No amount of encryption will help you here, because if Excel itself can decrypt the data (and it can, otherwise the connection will never be made), then the malicious type can also do this.

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

Thus, confidential information exists only in the user's head and (temporarily) on the machine that they use (this is probably their machine anyway). It is not in an Excel spreadsheet anywhere that anyone can fall into.

And besides, functional identifiers (shared between different users) are almost always bad ideas as they do an nightmare audit.