Top reasons to upgrade your VBA macros in 2020

Amen Jlili
Converting a SOLIDWORKS VBA macro to an add-in.

VBA macros continue to be the choice of the SOLIDWORKS community for automating repetitive tasks. After years of contributing to the API forum and seeing various examples of VBA macros being stretched to the limits of what VBA can do, it is about time to compile a list of reasons why upgrading a VBA macro to a .NET add-in makes sense.

1. Compatibility issues when upgrading SOLIDWORKS

A macro that was developed with SOLIDWORKS 2014 for example might not open or run properly in SOLIDWORKS 2018. Often times, the issue is related to references. The developer’s version of SOLIDWORKS is behind the end-user’s one. Consequently, the tlb references are not found and are marked as missing in the end-user’s machine. See image below.

This is fixed by going to the macro editor Tools > References, unchecking any missing references and then adding them again. Most of the time, the missing references are sldworks.tlb and swconst.tlb. SOLIDWORKS references are found in the installation directory of SOLIDWORKS.

SOLIDWORKS API and PDM API Training and Services » Fix “Compile ...
Example showing swcommands.tlb reference missing.

The entire issue with references compatibility can be circumvented if the developer’s uses late binding instead of early binding when developing the macro. The developer here puts the burden of resolving the API object types on VBA during runtime.

' this is early binding 
Dim swApp as SldWorks.SldWorks
set swApp = Application.SldWorks

' this is late binding
Dim swApp As object
set swApp = CreateObject("SldWorks.Application")

This works fine but the problem with the said approach is that late binding deprives the developer’s from access to the intellisense.

VBE uses the referenced tlbs to compile a list of suggestions that auto-completes the developer’s intention. 

Any edits to methods, parameters or even variable type declarations can be cumbersome. Let’s be honest, no one is expecting you to memorize the entire arguments list of IFeatureManager.FeatureExtrusion3.

2. Deployment issues

Installing a SOLIDWORKS macro involve adding menu buttons to an existing command tab. This might not seem so obvious to all SOLIDWORKS users. The problem is exacerbated if you have 100 machines. You will have to do this a 100 times. Installing a SOLIDWORKS add-in, on the other hand, should be as easy as installing any program . An MSI installer takes care of everything.

3. UI responsiveness

This is, by the far, the biggest drawback of VBA. VBA falls extremely short of .NET when it comes to multi-threading. Some developers argue that Application.DoEvents can be used to accomplish UI responsiveness. While this is partially true, I would argue that its usage is not pretty. VBA code still runs on the main thread of the host application (SOLIDWORKS) and Application.DoEvents only allows your application to respond to user inputs while the execution of the macro is put onhold and vice versa: Your UI will not be responsive if SOLIDWORKS is busy executing a time-consuming operation, hence loss of UI responsiveness.

A .NET add-in, however, can spin a new instance of UI components in a worker thread allowing the main thread to execute API calls. This approach is unorthodox. .NET best practices actually recommend you execute time-consuming tasks (in our case that would be the API calls) in a worker thread but the host-add-in architecture constraints the use of such best practices – more on this on a latter blogpost. It requires a bit of threading coordination since UI components were coded to run on the main thread but it still possible to achieve complete UI responsiveness this way. I think this is the best selling point.

4. Error handling

In .NET you can simply use a try catch block to catch exceptions. This method of error handling is very superior the archaic approach using On Err GoTo:

5. Boilerplate code

Sorting, reversing, skipping, using Linq to do any data manipulation in .NET has no out-of-the-box equivalent in VBA. Writing a utilities module to do that will set you back a couple thousands of lines of code. This is the reason why most add-ins would count longer lines if rewritten in VBA.

Those are my 5 top reasons and I’m pretty sure there are loads more. Feel free to contribute by sharing your opinion and writing a comment below!

More great articles

Protect macros with a password – Valid for Excel and SOLIDWORKS

TLDR: Protect macros modules from viewing by adding a password from the protection tab at Tools > Macro Name Properties.A…

Read Story

Delete dangling dimensions VBA macro

This macro deletes all dangling dimensions from all sheets in the active drawing document. ' Delete all dangling dimensions '…

Read Story

SOLIDWORKS PDM Professional: You have chosen to load a .Net Add-in? Well, what do you do next ?

SOLIDWORKS PDM Professional employs a server-client architecture. Your files and metadata is stored on the server (Archive server is where…

Read Story