Sage ERP Accpac Macro Tips and Tricks
Sage ERP Accpac uses Visual Basic for Applications (VBA) (http://en.wikipedia.org/wiki/Visual_Basic_for_Applications) as its macro language. This is the same macro language used by Microsoft Office and many other Windows desktop applications. With VBA macros you can access the Accpac Business Logic layer and enter documents, perform processing like posting batches or print reports. Since every screen in Accpac is an ActiveX control, you can embed any screen in a VBA macro and then manipulate/customize it in a standard uniform manner. Writing VBA macros is a huge topic; there are many books available on the topic along with courses at Sage University on VBA specifically for Accpac. This blog posting just lists a few useful tips and tricks that people find helpful when programming Accpac macros. These assume a general knowledge of writing basic Accpac macros. These aren’t in any particular order and tend to jump all over the place.
Upgrading to a New Version of Accpac
If your macro doesn’t customize an Accpac User Interface Screen then often your macro will just run without any changes. The exception tends to be if you are using a set of Views where their required compositions change. When you run you might get an error saying a required View composition is missing. In this case you should re-macro record the Accpac screen that uses these Views and add the missing View compositions. This tends to especially effect macros that are entering Order Entry documents. The main view compositions added for 5.6A are all to do with Serialized Inventory and Lot Tracking which were fully integrated into the core accounting modules.
The rest of this section applies to macros that customize Accpac User Interface Screens. It would also apply to you receiving a new version of some third party ActiveX control. When you insert an ActiveX control into a VBA macro, VBA reads all the information from the control and caches it in an EXD file. Unfortunately VBA doesn’t check when a new version of the control is installed and keeps using the out of date information. Full VB does something similar with OCA files, but is smart enough to update these when a control is updated. Worse the EXD file is strictly based on the base file name of the control, not its class id in the registry and not on the directory where it’s installed.
When we install a new version of Accpac we install our ActiveX controls with the same control names, but with new class ids. This way macros and other users of these controls can still use either version. When we install Product Updates we overwrite the current control, but have compiled the control with “binary compatibility” so all its interfaces are still compatible. For product updates, it is usually worth deleting the EXD files as we have seen problems with these in spite of binary compatibility.
When we upgrade to a new version, we need to delete the existing control from the macro and then insert the new control, along with deleting all the EXD files. The procedure for this is as follows:
- Delete screen control from form
- Delete icon from toolbar
- Remove reference from Tools – References
- Save macro and exit VBA
- Go to c:\documents and settings\username\Application Data\Microsoft\Forms
- Delete the *.exd files
- Load the macro
- Add the new control to the palette
- Make sure the version is correct (from the path)
- Insert the control back onto the UserForm
- Make sure the name matches what you used before
- Save the macro
References that you add to your macro project must match exactly the references on your client’s computers. For instance if you add a reference for an Office 2007 object like an Excel worksheet and your client has Office 2003, then the macro won’t run and will have to be corrected by removing the Office 2007 reference and adding one for Office 2003. Most COM/ActiveX library references are version specific. Also note the previous section on deleting EXD files. Similarly your version of Accpac must match the client’s to the Service Pack level.
Also beware of components you might have that a client doesn’t. For instance you might have full Visual Basic installed, which provides a large variety of useful COM libraries to use. However your client probably won’t have VB installed, so you must ensure the library use are using is freely distributable and install it on your client’s computer.
Windows has a number of optional components like Windows Scripting Host. If you require this, you will need to provide instructions on how to add this to Windows.
Use Debug.Print to print things out to the intermediates window to trace things (the intermediates window is closed by defaults, so you need to open it to see the output).
The built-in VBA debugger is very powerful and a great way to step through your program to see what is going on. Set a breakpoint just before the problem area and step through your code. Takes a bit of practice to know when to “step in” versus “step over”. Run to cursor is a very useful function.
Always include an error handler in your macro. Any View error will trigger an exception and there will be a text error message that will tell you why the exception happened. Even if you don’t get errors, you still want this error handler in case your clients get errors. For instance if your client makes an optional field required and your macro doesn’t provide it, is very hard to debug if you don’t have an error handler, but an error handler will instantly tell you what is wrong.
If you are running a long process like importing hundreds of order and you just want it to go through and sort out the errors later, then write all the errors to a file with code such as:
Dim lCount As Long
Dim lIndex As Long
If Errors Is Nothing Then
lCount = Errors.Count
If lCount = 0 Then
For lIndex = 0 To lCount – 1
Public lErrorFileHandle As Long
Public Sub WriteToErrorLog(sMessage As String)
If lErrorFileHandle = 0 Then
Dim sFileName As String
sFileName = “c:\popjctest.txt”
lErrorFileHandle = FreeFile(1)
Open sFileName For Output Access Write As #lErrorFileHandle
If Trim(sMessage) = “” Then
lErrorFileHandle = 0
Print #lErrorFileHandle, sMessage
Getting Information From Accpac
Use macro recording to get a starting point. This is especially useful to get the view.open and view.compose calls correct. Often a lot of noise and hard coded values are recorded that need to be cleaned up. The main source of errors we see in macros are due to bad view compositions, so this is really quite important.
Use RVSpy (from the Accpac – Tools start menu). This tool spies on every View call made. Use it to see what Accpac does and use it to see what your macro is doing. Often easier to use if you turn off recording of view functions you aren’t interested in like attribs or presents.
Use the Application Object Model that is posted on our web site. This contains very useful information on the Views and underlying database tables (useful for reporting).
Use the Accpac U.I. Info tools (from the Accpac – Tools start menu). This gives info on UI programs as well as views.
If your macro isn’t working, try doing the same thing in the Accpac UI to ensure you aren’t being blocked because of some sort of settings or something.
Read the macro chapters in the System Manager User’s Guide, these have some good information on how the Accpac Views and macros work.
Keep a Backup of Your Macros
Always keep a backup of your macros, or use a source control system like SubVersion (http://subversion.apache.org/) or Git (http://git-scm.com/). Sometime a macro file might become corrupted, or you might accidentally delete some important code. It’s always nice to go back to a previous version that isn’t too old.
Set global variables to Nothing when the macro completes. This is useful when debugging. If you are running the VBA Editor, then global variables are not cleared when the macro end automatically (they are cleared when VBA exits). So if you re-run the macro it will run with the global variables still having their values from the last run which can cause confusion. Worse even if you assign each variable as you start, this means the old value will be cleared at this point sometimes causing errors if they are objects.
Objects like datasources must be set to Nothing before the macro terminates or VBA may GPF as it shutsdown.
Only One UI
You can only embed one UI in a VBA project at a time. All the Accpac screens use the same variable names and due to bugs in VBA this causes problems. You should be able to refer to them by object name like AccpacCS2000.datasource versus AccpacAP1000.datasource, but VBA gets confused and won’t work properly.
Don’t Use Init
We have replaced Init with RecordGenerate, RecordCreate and RecordClear to make it clear what we are trying to do. Init did all of these and sometimes did the wrong thing. Newer Accpac Views will only accept the Record* functions and will reject Init.
VBA Versus VB
VBA has a number of limitations imposed by Microsoft, so you are motivated to buy full VB. These limitation include:
- Having a menu
- Having an icon (for when minimized and in the task bar)
- Receive events from popup forms
- Resizable windows
In VB you have to define the controls of type VBControlExtender because unlike VBA, VB can’t do the conversion for you. Here’s an example getting the Click event of a notebook in full VB:
Dim WithEvents tabctl As VBControlExtender
Private Sub AccpacBK1600UICtrl1_OnUIAppOpened()
Set tabctl = AccpacBK1600UICtrl1.UIAppControls(“tbOptions”).GetControl
Private Sub tabctl_ObjectEvent(Info As EventInfo)
If Info.Name = “Click” Then
MsgBox (“tab clicked”)
(In VBA we could just access the tabctl directly without using a VBControlExtender).
Well that wraps up my blog posting for this week. Next week I’m attending Sage Insights 2010 in Denver Colorado. If any readers are also attending, make sure you say “Hi”.