I have searched from google but I still can't find a clear answer. I have written code in excel vba. -How to restrict the program users access to the code? Adding a password in VBA project properties is easy to crack so what is the best and easiest way to hide the vba code from program users?
Another post suggest VB6 but it's obsolete and I can't find where to download it.
The Access error message when trying to save in ACCDE. The Access error message when trying to save in ACCDE. Part 2
I have verified that it is possible to write VBA code in MS Access project, compile to an accde file. So after OP had some issue, editing this post to make my steps more explicit...
Create an Excel Workbook file which we will call back into because we want to prove we can interact with the Excel session. I called my workbook N:\WorkbookControlledByAccess.xlsm and in a module I wrote some code that can be called into using Application.Run This code is trivial but exemplary
Option Explicit
Public Function SomeExportedFunction() As String
SomeExportedFunction = "Hello 47"
End Function
Then I opened MS Access and went File->New->Blank desktop database and pressed Create which accepted the given default database filename, for me 'Database5', this will create Database5.accdb. And the default window is a new Table, Table1, close that. On the ribbon, go Create->Blank Form.
On the Form create a command button, this throws an annoying wizard, cancel the wizard. Right-click newly created button to get context menu and then click Build Event, from the Choose Builder dialog choose Code Builder, this gives an error "To add a code module to a form or report, you must switch to Design view and set the HasNModule property of the form or report to Yes." Click OK to that error and switch to design view by using the View button on the Design ribbon. Once in design view try the context menu step again. You should get a VBA IDE window now which has the following code
Option Compare Database
Option Explicit
Private Sub Command0_Click()
End Sub
We will test the button by changing this to
Option Compare Database
Option Explicit
Private Sub Command0_Click()
MsgBox "Just access at the moment"
End Sub
One runs the form by pressing F5, the form runs, click the button and our new message box appears. Ok, now to do some Excel Access interop. Add Tools->References and add Microsoft Excel 15.0 and change the code to be
Option Compare Database
Option Explicit
Public Sub Command0_Click()
'* Use GetObject to verify the workbook is loaded and accessible via
'* RunningObjectTable
Dim wb As Excel.Workbook
Set wb = GetObject("n:\WorkbookControlledByAccess.xlsm")
'* If we got here without error then we should be able to call back into Excel session.
MsgBox wb.Parent.Run("WorkbookControlledByAccess.xlsm!SomeExportedFunction")
'MsgBox SomeExportedFunction
End Sub
Now if you run the form and click the button the message is retrieved from the Excel workbook we created earlier. So this proves Access can reach Excel.
That was in a normal accdb file and one needs to password protect the code.
Then when you want to create the accde file you go File->Save As->Make ACCDE and follow the file dialog. You can then open the accde file and the code will be locked.
I have yet to do exhaustive search for any hacks into an accde file yet but we know the standard Excel VBA is not safe.
