Sage 300 ERP Macros
We’ve had macros in Sage 300 ERP since version 1.0A. In the early days we used CABLE (the CA Basic Language Engine) as our macro language. This was a macro language version of CA-Realizer which we used for UI development back then. It was fun creating the development environment with debugging capabilities and such. Amazingly CABLE macros are still supported in Sage 300 ERP and if you run a CABLE macro (*.mac) you will get this environment:
With version 4.0A we introduced Visual Basic for Applications (VBA) as our macro language. We did this hand in hand with introducing our first COM interface a4wcom. This interface is still around, but generally we use the newer Sage 300 ERP COM interface a4wcomex.
We provide macros as a method of customizing the product which doesn’t require the SDK. While it does require programming, the Basic language used is simpler than say using C, Java, C# or C++. So hopefully more people can provide meaningful coded customizations that are largely upgrade safe. Generally VBA is a very powerful development environment and we’ve seen some amazing pieces of work implemented as macros. Plus VBA is the macro language used by Microsoft Office, so there are many technical resources, books, courses and such to help you with your development. Further you can use macro recording to help you with some starting code.
For many of our customers, the ERP package handles their financial accounting needs, due to regulations on using Generally Accepted Accounting Practices (GAAP) these are pretty standard. However especially in the operations modules a lot of businesses want custom calculations and procedures to more exactly match their particular business. Whether this is enforcing additional government regulations, implementing custom pricing models or whatever. These are very varied and we need to provide a powerful framework so that these can be accommodated whatever they may be.
At the same time we need these customizations to easily migrate from version to version so that customizations don’t then lock a customer into a particular version and prevent them from ever upgrading.
A powerful macro language with deep hooks into the product is an ideal way to accomplish these goals.
Both CABLE and VBA macros are fundamentally based on our Sage 300 ERP Business Logic Objects or Views. The API for all our Business Logic Objects is the same, so once you learn one, to some degree you learn them all. For a bit more info on our Business Logic, have a look at this blog posting. For an example of creating Orders have a look at this posting.
This can be a great mechanism for say importing data from an external system. VBA can access the API of the external system, extract the data and then feed it into our Business Logic to do things like import G/L Journal Entries or O/E Orders.
Using the VBA Forms capability you can create your own screens that interact with our business logic and perform your custom tasks. The VBA forms library/system is a very powerful but easy to use system for creating potentially quite sophisticated UIs.
The API to our business logic that the macros use is the same as the API used by our UIs, so you know that anything you can do in a UI, you can also do in a macro. It also guarantees that this layer is heavily tested and supported.
Generally the main interfaces to our business logic stays the same. As we add features we add fields, but as long as these aren’t required fields and you don’t need to use these features then your macro can remain the same from version to version.
With version 5.0A, we gave VBA the ability to customize our product’s User Interface Forms. We accomplished this by re-writing all our UIs from CA-Realizer to VB. The new VB UIs were created as ActiveX controls themselves and hence could be hosted on standard VBA forms. Then each UI contained a uniform set of methods, properties and events to all VBA macros to interact and customize them.
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.
Another great use of macros is to automate recurring tasks. Besides business logic we give you full access to printing reports, including setting all the report parameters. These can be either Crystal Reports of Financial Reports. I blogged on printing through macros here and a bit more information on customizing reports here. Plus from the Business Logic you have access to all processing functions like Posting Batches or running Day End.
So you can write a macro to print out all your month end reports. You can write a macro to go through and process un-posted batches. Or whatever other recurring process you want automated.
Customization through macros is a powerful technology to personalize your ERP and to allow you to achieve greater efficiency. VBA is an industry standard macros language and gives you great power to customize Sage 300 ERP.