Microsoft Dynamics GP Customization – Object Binding in VBA: Better Late Than Never
Recently, I started work on a project which involved the modification of a legacy customization containing a sizable amount of VBA code. As I went through the process of importing the package and compiling, I was faced with some familiar adversaries – Active X Cannot Create Object, and User Defined Type Not Defined. Wait a minute – I thought you were both defeated in 2004. This can’t be happening!
Does anyone else think that this article is caught in a time warp and would feel much more comfortable about ten years ago? Granted, this would be a valid point if it were not for the multitude of VBA customizations that have been created over the years. In this particular case I was thrust into what Active X era developers affectionately termed DLL Hell. The original (well meaning, I’m sure) developer included a reference to an Active X control in their project and declared and instantiated a host of ADO objects using early binding.
I know the Active X purists swear by this method and are likely rallying to have me silenced as we speak. It is however not without its pitfalls. In this example, a reference to the appropriate Active X object was not provided with the package file.
In another equally frustrating instance, a reference may be provided but the referenced instance of the Active X object does not exist on the destination work station. Veterans of the VB6 days (me included) know that attempting to fix errors of this nature lead down an ever narrowing path of despair! So, taking the path of least resistance, I removed the early binding code and replaced it with its versatile but sometimes frowned upon relative – late binding.
What I have done is cause VBA to obtain a reference to whichever instance of the requested object happens to be available. Subsequently, this project compiled successfully and the old Active X nemesis was once again defeated.
Not so fast – what about constants? Excellent point! When an instance of an Active X object is created, it will expose enumerated constant values which are tremendously helpful when writing code. No such luck with late binding as we are now working with generic Object types. Luckily we live in an age of information at our fingertips and Wikipedia bestows brilliance on us all. So, a quick work around may be had by inserting code similar to the following at the beginning of the module.
That, in a nutshell, is the technique of late binding. Use it wisely and conquer your own Active X nightmares!
By Eric Verebelyi, BDO Solutions Senior Consultant, with a great sense of humour.