8

I have an Excel addin, developed in VBA, deployed on the network.

The addin reads from an Oracle database, and pastes tables in a new worksheet, which will also contain a button (triangle shape) to refresh the table.

Everything works great, but I must protect code with password.

When i do, the following error appears on some machines:

Compile error in hidden module: Main. This error commonly occurs when code is incompatible with the version, platform, or architecture of this application.

Although it compiles perfectly when unlocked.

On other machines it requires VBA password on close, even though i have nothing of the sort defined under close event.

I am hoping to secure my code, without running into above problems. Any suggestions are appreciated.

Kelaref
  • 408
  • 1
  • 5
  • 23
  • 1
    Though I know is painful and I speak from experience, have you try VSTO and Click once? and signing your code? – Miguel Mar 29 '17 at 12:12
  • I have not. I think VSTO will be much slower compared to the lightweight VBA. – Kelaref Mar 30 '17 at 06:57
  • I beg the differ, but I do understand where you coming from. Microsoft has been pushing VSTO for while now and they encourage this type of work to be done in .Net. – Miguel Mar 30 '17 at 12:39
  • What version of Office is it? I've written a few add-ins with pure VBA code, and password protected the source, and haven't seen this error before. – Josh Mar 31 '17 at 13:47
  • Are you getting the error on all machines, including the one you developed it on? Is it possibly 32bit/64bit differences? – CLR Mar 31 '17 at 14:17
  • Have you tried adding the Network Location as a Trusted Location in the Trust Center? – EEM Mar 31 '17 at 23:58
  • 1
    What you need it better Error handling on your code – 0m3r Apr 01 '17 at 06:24
  • Does the code compile whilst unlocked? – S Meaden Apr 01 '17 at 16:38
  • @Josh i am using Office 13, but some users have different versions, but it still works very well. – Kelaref Apr 03 '17 at 07:01
  • @CLR I do not get an error on the machine i developed on. I will look into the 32-64 bit difference, but i suspect it is not the reason because it works perfectly without the password – Kelaref Apr 03 '17 at 07:03
  • @EEM i have not tried, given that it works perfectly without password, and the addin is still loaded normally loaded on open – Kelaref Apr 03 '17 at 07:04
  • @0m3r there is no error raised without password, this is not about error handling – Kelaref Apr 03 '17 at 07:05
  • @SMeaden Yes it compiles whilst unlocked – Kelaref Apr 03 '17 at 07:05
  • Are you password protecting only the code? Are workbook/ worksheet are also protected? Since we don't know the function of the add-in, it appears your password is preventing the code to do its usual operation. What happens if you dont put it in t he network? – ArindamD Apr 03 '17 at 07:43
  • @ArindamD Yes only the code is password protected. AFAIK workbook and worksheet are not protected. I updated question to describe addin. I cannot remove from network, to deploy updates regularly. – Kelaref Apr 03 '17 at 08:43
  • Long shot, is it related to this? http://stackoverflow.com/questions/14162483/excel-vba-userinterfaceonly-true-not-working –  Apr 04 '17 at 22:56
  • What happens when you debug it on one of the problem machines? – Tim Williams Apr 05 '17 at 00:11

1 Answers1

3

I know of two situations in which this error occurs:

1: 32bit vs. 64bit issues (already mentioned in the comments). If you've developed a 32bit addin and try to deploy/use it within a 64bit Excel, then your quoted error message will appear.

2: Missing references.

I think the second option is more likely to be the cause of the problem, because usually all client PCs in a company will have the identical version of MS Office installed and this error occurs only on some machines.

So I suggest to check your references. In the VBA-Editor (Alt+F11) go to tools --> references and note down any ticked modules. Then compare this to a client where the error is occuring (go to client PC and repeat the procedure).

If the ticked modules don't match between development and client PC, this is most likely the cause of this issue. You might also encounter modules with a "MISSING" written in front of their name on the client PC. In this case, remove the tick from the missing module and try to execute your addin.

If it works flawlessly you should be good to go, if not you'll have to manually install the missing modules on the clients PC.

Hope this will help you.

daZza
  • 1,531
  • 24
  • 50
  • Thank you for helping. I confirm all are 64-bit. I made sure of this when i installed the oracle client. There are no missing references. I generally avoid adding references for this reason. – Kelaref Apr 03 '17 at 09:44
  • Okay. Have you tried running the addin locally on one of the machines where the error is thrown? Just to confirm it's not a network/firewall/... problem. – daZza Apr 03 '17 at 10:45
  • @Kelaref Re: 32bit/64bit - I was referring to the version of **Office**, not the version of *Windows*. Your Oracle comment makes me think you might be referring to *Windows* ? Just wanted to be clear in case we'd made an assumption there. – CLR Apr 03 '17 at 10:54
  • i back this answer, id always compile in 32bit, no matter what version it runs on its very unlikely to cause problems like this. – Danny James Apr 06 '17 at 15:22