Home Why the Left() Function Stops Working in VBA

Why the Left() Function Stops Working in VBA

This is an interesting problem that someone had recently. In a VBA-based program, the Left function suddenly stopped working with an error along the lines of "type data mismatch". Being that this is a native function to VBA, my first thoughts were that it was caused by some unwary VBA upgrade. However, the truth turned out to be more interesting.

It seems that when VBA is compiled, other libraries may import their own functions into the
global VBA namespace. What that means is if there is another Left function function that is global in some library, it would override the VBA built in function since the VBA native stuff is resolved last. A better description of this problem can be found at this DLL Hell site hosted by Walker Software.

In this instance, it turned out that the MSHTML library was the culprit, in particular the IMarkupPointer.Left function which for some reason was exposed globally. Microsoft has an article describing a similar problem with the Devshl.dll library.

What is the solution? To fully qualify the function name adding the namespace which would mean calling VBA.Left instead of plain Left. Of course, if you have millions lines of code that uses the plain function, you are in trouble. Being that is in VBA 6 which is not .NET and is not really being supported by Microsoft, tough luck.

Maybe it is time to try out Java or PHP?

This post is licensed under CC BY 4.0 by the author.