Complie Specific lines of VBA Code

XLS Padlock
1

HI All

Sorry in advance, I may not explain myself correctly.

I have downloaded the trial version of the software, and would like to know if there is a way to compile only a specific test string

I have a macro that uses an SQL query to connect to our ERP systems database to bring back specific results for a dashboard. I want to try and use the compiler to hide the specific location of the servers.

For example, to connect to the ADODB.Connection (to another Excel document) I use (parts extracted);

Server_Name = "\test1server\test1.xlsm"
Default_Dir = “\test1server”

“DSN=Excel Files;DBQ=” & Server_Name & “;DefaultDir=” & Default_Dir & “;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;”

Can I use the compiler to hide the server name and directory so when the macro runs it’ll access the server_name and Default_Dir strings from the compiled data?

2

Sure, you can use the compiler to hide your two values, but they can still be accessed in memory (for instance, if someone dumps the Excel process). Maybe you can encode them so that they don’t appear in clear.

3

Can you please explain firstly how I would hide these elements in the compiler so what when the code is run it will find the compiled string.

Also can you please elaborate with respects to encoding them? how would this be done?

4

You let the VBA compiler create the OLE object for you, for instance, the following code opens the connection. You can then return the created object to your original VBA function.

image.png1162×864 49.7 KB

Thus, the two parameters are no more in your Excel workbook VBA project.

For encoding, you can use XOR encryption/decryption.