VbaCompiler

Compile time troubleshooting procedure for VBA Compiler

 

If you have got an error during VBA code compilation, then follow this VBA compiler troubleshooting guide to find the reason of the problem.

 

Verify whether your Windows OS has pending updates

 

Please verify whether your Windows operating system has pending updates. If so, please ensure that you complete the Windows OS update first then reboot your computer.

 

Verify if your VBA code contains VBA syntax errors

 

This is the most common reason for potential problems with VBA code compilation.

To detect a VBA syntax error, you need to go through the following steps:

1. Open your original workbook VBA code.

2. In the main menu of the VBA Project window select the ‘Debug’ menu item and then select the ‘Compile VBAProject’ menu item in the pop-up menu.

3. If you get the VBA syntax error message, then you need to fix the detected error.

4. Repeat from step 2 until the ‘Compile VBAProject’ menu item turns a grey color and becomes unclickable. This means your VBA code doesn’t contain any more VBA syntax errors.

 

Disable synchronization with OneDrive in Excel and Output and Work folders

If you have OneDrive Excel files synchronization enabled – it may affect the behavior of VbaCompiler, because the VbaCompiler can only work with files that are located on the local hard-drive disk. To ensure that the option is disabled and the files exist on the local hard-drive disk, you need to temporarily disable the OneDrive office files synchronization in the following way: OneDrive Settings >> Office Tab >> disable “Use Office applications to sync Office files” checkbox. You should also disable the use of the AutoSave feature in the Cloud options through your Excel Options: Excel Options >> Save >> disable “AutoSave files stored in the Cloud by default in Excel” checkbox. You need to restart Excel after conducting these changes to apply the new settings.

If output folder  or/and work folder  in VbaCompiler options are synchronized with OneDrive it may affect the behavior of the product.  You need to set output path and work path to the folders which are not synchronized with OneDrive. See how to turn off or disable OneDrive.

 

Set target output folder and work folder to the “My Documents” folder or it’s subfolder

 

If your current Windows account doesn’t have enough credentials to allow for the writing of files into the output folder or work folder, then it may lead to the situation in which the VbaCompiler is unable to create a DLL file in the currently set output folder. We recommend that you set your output folder and work folder inside of your account’s “My Documents” folder or any of its subfolders.

 

Verify that the antivirus doesn’t block the normal work of the VbaCompiler for Excel

 

Antivirus software may hinder the normal behavior of our product.
Some antiviruses may treat the creation of the DLL file as malicious behavior and remove the DLL file created by DoneEx VbaCompiler for Excel. So, as a result, the VBA code compilation fails.
To verify whether that is the problem in question, we recommend that you compile your workbook or Excel add-ins on a computer without any antivirus software or temporarily disable the antivirus on your current computer and then try to compile the VBA code.
If the VBA code compilation completes successfully without an antivirus, then you need to report the false positive detection to your antivirus manufacturer.

To reduce possibility of the antivirus false positive detections during compilation you can use the “Work folder” compilation option. When the “Work folder” is defined you need to enter the same path into the antivirus exception list. We also recommend that you add the “Output folder” to the antivirus exception list, along with the Windows Temp folder path. Windows Temp folder is usually located at “c:\Users\CurrentUser\AppData\Local\Temp”.

 

Verify that third party Excel add-ins do not block compilation

Some third party Excel add-ins may block compilation. We recommend to temporarily uninstall other add-ins, if any are present, to ensure that they do not block compilation.

 

Verify if your code has ambiguous syntax inside ‘With’ operator

 

This may appear in cases where you use the new line transfer for a procedure call inside the `With` operator body. The code may look like:

With obj
    .prop _
    .anotherObj _
    .method _
    .parameter
End With

In this case there is no way to properly identify the syntax of this expression without knowledge about the type of each item of the expression ahead of time. This is because the new line transfer adds a space at the end of the line before the ‘.’ dot connection symbol. Our parser doesn’t have any knowledge about each item of such expression during scanning and parsing and cannot detect if it is the next item of a qualified identifier or parameter.

To resolve this problem, you need to fix this expression in your original VBA code before compilation by one of the following ways:

1. The simplest way is to remove all new line transfers in the method call with a qualified identifier expression inside the With operator body.
like this:

With obj
  .prop.AnotherObj.Method .parameter
End With

2. Avoid the situations when a new line after transfer starts with the ‘.’ dot connector. Keep the ‘.’ dot symbol on previous line of expression.
like this:

With obj
  .prop. _
   AnotherObj. _
   Method ._
   parameter
End With

 

Make required changes if your VBA code is using methods of VBA Excel add-ins (.xla or .xlam file)

 

The VbaCompiler for Excel does not have access to the VBA Excel add-in method definitions, so it cannot find those methods’ signatures. It is because of this issue that the compilation may fail. To resolve this problem, you need to implement all Excel add-in method calls through the late binding approach by using the Application.Run() function.

For example, if your VBA code contains the following Excel Add-in method call:

Result = MyAddIn.Method1(param1, param2)

You need to substitute this call by using the following expression:

Result = Application.Run(“MyAddIn.xlam!Method1”, param1, param2)

 

If all of the previous VBA compiler troubleshooting approaches  don’t fix the problem and VBA code compilation still fails

 

In this case it is possible that the reason is an error in VbaCompiler itself. In order to be able to fix the error we need to reproduce the situation on our side. To do this successfully we need your help!

 

Please provide us with the following information:

  • A workbook example with the uncompiled VBA code which leads to problem.
  • A screenshot of the compilation options or the VbaCompiler for Excel project file (.vcprj file) that you use for compilation.
  • A screenshot of the MS Excel About box from the computer where the problem appears.

 

Send this information to support@vbacompiler.com.
Please note that we do not need your original workbook. You can modify the example in any way that you want, but the main requirements are as follows: the VBA code must not contain any of the previously mentioned syntax errors and the error must still be present when VbaCompiler compiles the workbook VBA code that you are sending to us.