Stephen Smith's Blog

All things Sage 300…

Sage ERP Accpac Macro Tips and Tricks

with 19 comments

Sage ERP Accpac uses Visual Basic for Applications (VBA) ( 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:

  1. Delete screen control from form
  2. Delete icon from toolbar
  3. Remove reference from Tools – References
  4. Save macro and exit VBA
  5. Go to c:\documents and settings\username\Application Data\Microsoft\Forms
  6. Delete the *.exd files
  7. Load the macro
  8. Add the new control to the palette
  9. Make sure the version is correct (from the path)
  10. Insert the control back onto the UserForm
  11. Make sure the name matches what you used before
  12. 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
WriteToErrorLog Err.Description
lCount = Errors.Count

If lCount = 0 Then
WriteToErrorLog Err.Description
For lIndex = 0 To lCount – 1
WriteToErrorLog Errors.Item(lIndex)

End If
Resume Next

End If

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
End If

If Trim(sMessage) = “” Then
Close #lErrorFileHandle
lErrorFileHandle = 0
Print #lErrorFileHandle, sMessage
End If

End Sub

Getting Information From Accpac

Use macro recording to get a starting point. This is especially useful to get the 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 ( or Git ( 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.

Global Variables

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

End Sub

Private Sub tabctl_ObjectEvent(Info As EventInfo)
If Info.Name = “Click” Then
MsgBox (“tab clicked”)
End If

End Sub

(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”.

Written by smist08

May 14, 2010 at 2:35 am

Posted in sage 300

Tagged with , ,

19 Responses

Subscribe to comments with RSS.

  1. […] This post was mentioned on Twitter by Geek Fetch. Geek Fetch said: ACCPAC Sage ERP Accpac Macro Tips and Tricks: Sage ERP Accpac uses Visual Basic for Applications (VBA) (http://en…. […]

  2. Great post Stephen! These simple to follow posts help Development Partners and regular users alike.

    For partners that are looking for the AOM it is located on the new Sage Partner Portal at


    May 14, 2010 at 6:01 pm

  3. Is VBA sticking around in version 6 or only during the “hybrid” years?

    Kevin Gartly

    June 11, 2010 at 5:09 am

    • Current plan is to leave it in. You won’t be able to customize web screens with it, but we think it will be useful for server type purposes of running processing tasks such as importing data from other systems or converting data for output.


      June 12, 2010 at 2:51 am

  4. […] SAGE ERP ACCPAC MACRO TIPS AND TRICKS May 13th, 2010 Sage ERP Accpac uses Visual Basic for Applications (VBA) ( as a macro language. This is a same macro denunciation used by Microsoft Office as well as most alternative Windows desktop applications. With VBA macros we can entrance a Accpac Business Logic … Go to full contents […]

  5. […] There is a little work to upgrade when you go from version to version. With each version the screen control gets a new class id, so you need to remove the old versions control reference and add the new one. Otherwise the code should remain compatible and continue to work. I documented how to do this in this blog posting. […]

  6. Hi Stephen,
    Are there any code examples available that show good use of an Accpac Finder with a datasource in VBA?
    I use the AccpacFieldEdit control and include the finder but I’m not sire how to set criteria to limit the displayed records. Any help or sample code would be great.


    Dana B. Stidsen

    February 25, 2016 at 3:46 pm

    • Here is some code that runs a finder off a separate button (among some other things). Hope this helps.

      Private Sub CommandButton1_Click()
      Dim fnd As AccpacFinder.ViewFinder

      Set fnd = New AccpacFinder.ViewFinder

      fnd.DataSource = dsGLACCOUNT
      fnd.ReturnFieldIDs = 1
      fnd.Filter = “ACCTID >= 5000 AND ACCTID < 6000"
      If fnd.Finder = True Then
      dsGLACCOUNT.Fields("ACCTID").Value = fnd.ReturnKeyValues(0)
      End If
      Set fnd = Nothing
      End Sub

      Private Sub CommandButton2_Click()
      Dim impexp As AccpacImportExport.ImportExport

      Set impexp = New AccpacImportExport.ImportExport

      impexp.Open mDBLinkCmpRW
      impexp.SetView "GL0001", "", VIEW_FLAT, Null
      End Sub

      Private Sub dsGLACCOUNT_OnKeyChanged(ByVal eReason As tagEventReason, ByVal pField As AccpacDataSrc.IAccpacDSField, ByVal pMultipleFields As AccpacDataSrc.IAccpacDSFields)
      If (eReason = RSN_FIELDCHANGE) And (dsGLACCOUNT.Exists = True) Then
      End If
      End Sub

      Private Sub UserForm_Initialize()
      With dsGLACCOUNT
      If .Active = False Then
      .DBLinkCompany = mDBLinkCmpRW
      .Active = True
      End If
      End With ' dsGLACCOUNT
      fecACCTID.DataSource = dsGLACCOUNT
      fecACCTDESC.DataSource = dsGLACCOUNT
      End Sub

      Private Sub UserForm_Terminate()
      dsGLACCOUNT.Active = False
      End Sub


      February 25, 2016 at 3:57 pm

      • Thanks Stephen – This is exactly what I needed.

        Dana B. Stidsen

        February 25, 2016 at 4:49 pm

      • I’ve done a lot of customization for one of my clients entirely with VBA macros. Basically the screens were modified with custom buttons and underlying code to change or add to the behavior of the program. The goal was to allow the company to process transactions for multiple entities (companies) within a single Sage 300 company with a single GL and keep the ‘books’ of each company in balance.

        As an example, I modified the GL batch list screen by hiding the standard ‘Post’ button and adding my own. When they posted, the underlying code would evaluate each entry in the batch and if it found any entries which were a cross posting between entities it would put up a message saying so and tell the operator to fix the entries before the batch could be posted. Most of the customizations took a similar approach. They were all mods to the screens which removed the possibility of any cross posting. The custom processes have worked remarkably well for many years.

        As you know, the web browser based screens are now available and the client is asking when they will be moving to them. They really do want the new screens and I really do want them to use them as well.

        I’ve invested in the SDK’s for many years and have downloaded the recent one just the other day. Clearly this will be a challenging project for me and it appears I have a lot of learning ahead of me. I’m not one to shy away from a project like this even though there’s lots of different technologies and new languages involved.

        I’m hoping you might offer some guidance on my next steps. I’m guessing I have about 6 calendar months to get it working for them. There are about 15 custom screens including special processing for 1099’s and some other reports.

        Couple of questions – Can I take a similar approach at the screen level like my example above or do I have to focus on the server side? Can I even make changes to the existing screens or do I have to create entirely new ones?

        I’m sorry about the lengthy message. Just not sure who to ask about this stuff.


        Dana B. Stidsen

        February 26, 2016 at 12:16 am

      • Hi Dana,

        The purpose of the current Web UI SDK is to create new Accounting modules. This is most suited to products like from Peresoft, Technisoft, Norming, etc. It doesn’t currently have the hooks for customizing the current screens. That is being rolled out over the next couple of releases. I don’t think you will be able to start doing what you are describing until you get the August release.


        February 26, 2016 at 4:04 pm

      • Thanks Stephen, I’ll pass this along to the client. They will just have to wait for awhile longer.

        Dana B. Stidsen

        February 26, 2016 at 4:29 pm

    • Hi Stephen, this script is great. Can we also specify a file name for the export?


      Nishanth Kothuru

      December 16, 2016 at 9:01 pm

      • I don’t think so. You specify the folder, then there might be multiple files created there (depending on the format selected) based on the detail names.


        December 16, 2016 at 10:20 pm

      • I tried to specify the folder by the after the syntax for export but that didn’t help.

        imp.export “C:\….\”

        It errors out saying that the number of arguments is incorrect. So, I take it that it is not the place to provide the folder name. Is there a specific syntax for providing the folder path? I didn’t find any such syntax in the object explorer.


        Nishanth Kothuru

        December 16, 2016 at 10:30 pm

      • Hi Stephen,
        How to specify the folder name in the above code, please help.


        Nishanth Kothuru

        January 6, 2017 at 2:06 am

      • Have a look at this blog post: There is a sample there.


        January 6, 2017 at 2:28 am

  7. I’m the original author of some macro code being used at a former company. They recently contacted me to update said macro code, but I don’t live in the same city anymore, so on-site access would be difficult. Is there a way I can update the macros without having access to their accpac environment?


    November 7, 2016 at 4:40 pm

    • Sorry, but no there isn’t. The usual way in a case like this would be to use Windows remote desktop to access the client’s machine.


      November 7, 2016 at 5:22 pm

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: