Compile Error in Converting Macro to VBA

You can create a macro (An action or set of actions that you can use to automate tasks) to perform a specific series of action and you can create a macro group to perform related series of actions. As macro is very useful but at some time you get sick of macros - they're slow, unwieldy and clutter up your database. It's time, then, to convert your crappy old macros to shiny VBA. Fortunately, this conversion couldn't be easier. VBA standards for Visual Basic for Applications and is the language embedded within your Access database.

You can use VBA whenever you do one of the following:

1. Create a new function.
2. Create a new subroutine.
3. Design a global variable.
4. Execute the RunCode action in a macro.

 

In conversion of macor to VBA when we run VBA code within ArcMap or ArcCatalog that includes references to Network Analyst objects, you get the following error message when you attempt to run the code.

 

"The application can not find the wizard or there is a syntax error in the declarations section of a visual basic module”

 

When you convert a macro to Visual Basic for Applications code using the "Save as Visual Basic Module" command, and the macro contains a RunSQL action that includes a SQL Statement that was copied from a query's SQL View window, you may receive a compile error when you try to compile the module. After you convert macros to Visual Basic for Applications code in a form or report by using the "Convert Macros to Visual Basic" command, you may receive the following error message when you use the form or report.

 

“The expression you entered as the event property setting produced an error: Compile error”

 

Basic causes of these errors are missing macros or references due to corruption in database or it may occur when there is a broken Reference in your Access database and you've tried to use one of the built-in Access functions such as Left, Instr, Len, Year, Mid, etc. Second error comes when RunSQL Statement is not being correctly copied to the module during the conversion and is causing the module to fail that also cause error and damaging database. To resolve these problem we can use following method:

• You can Edit the SQL Statement Action Argument in the macro and remove the space character to the left and right of the equal sign (=).
• Save, close, and convert the macro again.
• Edit the code in the converted macro module, and remove the extra line.

 

 

Or prevent the error, open a form or report class module in Design view and delete the procedure for the event whenever you replace an event procedure with a macro. One more method to correct the problem, you need to open your Access database then open the Microsoft Visual Basic window and select reference tool menu, when the References window appears, check to see if there are any References checked that start with "MISSING:" If there are any, uncheck these Reference.

 

If still you get this error that shows your database is critically damaged that causes missing references and modules. In this case compact and repair utility won’t help you. You must go for access repair utility which can repair your database and recover your missing references and modules.

 

Over Internet, lots of utilities available for repair of access database. One of best tool for access repair is Stellar Phoenix Access Recovery software which provides repair of your mdb and ADP files. It repairs various file versions includes Access 97/2000/2002/2007. Best feature is it recovers missing references and primary key fields, if present. You can check the preview of your repaired database.