Computers were designed to make our lives easier, right?
They like doing the exact opposite as most humans do: they like to be told to the same simple thing over and over again.
So you wrote a macro to give your pc something to do. But now you find yourself looking at the screen and waiting for your digital buddy to do his work. It’s like waiting for water to boil, it just doesn’t seem to happen. Can’t this go a little faster?
By combining a few macro commands I was able to improve the macro speed enormously, the running time on one macro down from close to five minutes to 8 seconds! It is now 35 times faster! In this post I’ll share with you how I got there and what I learned along the way.
As always, there are many factors. We already treated a few of them in our most popular blog post so far: Slow drawing in SOLIDWORKS? Here are 12 possible reasons. Many items that affect drawing speed affect macro performance as well: CPU speed matters, model complexity matters, laptop power settings matter.
You are not in control of those when you are writing a macro though. Fortunately, you can turn a few dials to tweak performance from within the macro. I have found five that have a real impact, plus a bonus one at the end.
You can get the biggest boost if you put speed above anything else. That’s why some race cars look like this:
You need to answer a few questions to create the proper approach:
The more often you can answer NO, the more you can improve your macro speed.
I wrote a few macros to test these commands first. I’ll share the macro speed at each stage at the end of the post. My ultimate goal is to get a macro that now takes 24 hours (yup) to, say, about ten minutes. That way I wouldn’t have to plan my day around running the macro once.
The first test case creates 100 sketches and fills each sketch with a horizontal line of 100 sketch points. I then do a normal rebuild. The resulting model has 10,000 equidistant grid with sketch points and takes, at its worst, 4 minutes and 37 seconds to run on my 2016 workstation laptop:
To find out how long my macros and individual functions run, I created a function to time them. When I suspect a group of functions of running too slow, a throw in a few PrintTime calls in there to find the culprit. It show the current time up to hundredths of a second, which is the accuracy of the Timer function.
Function PrintTime(comment As String)
Debug.Print Format(DateTime.Now, "hh:mm:ss") & "." & Right(Format(Timer, "#0.00"), 2) & " - " & comment
When I did some digging in another macro this morning, I timed all of my functions. What I found was was that the bulk of the running time was spent on changing views, not actual modeling work. Turned out the view transition for a ‘Zoom to fit’ action took half a second, which was 85% of the time my function took to complete. I turned off these animations and BAM, instant speed gain.
Your computer performs many tasks, even when you are just staring at the screen wondering how the hell you are going to get the cost of you design down by 30%. It checks your keyboard and mouse for inputs for example. You have probably noticed that when your computer is really busy and you click a few times. The clicks seem to go unnoticed until they are suddenly handled in a quickfire way after the computer is done crunching its heavy task. All of these things are called events, and Windows has a lot of them.
You can add the DoEvents command to your macro to keep a form responsive, even when the computer is really busy. It just carves out some time to repaint the form at the cost of slowing down execution. Look how nicely everything is being kept up to date, right until Windows decides to set the screen to white:
For certain macros, adding the DoEvents line is a blessing because it no longer looks like the computer has crashed. In other cases, like drawing these ten thousand sketch items, the program gets interrupted 10,000 times as well. Imagine getting interrupted 10,000 times in a workday, damn. So be careful when you use it and where you place it. In this test case, I removed the line and the running time immediately went from 04:37 to 01:26. The macro speed already improved by 3.2 times. The screen now stops responding after 5 to 15 sketches until the task is done.
Creating sketch items is one of the most basic things you can do in SOLIDWORKS. That’s probably why the content of the sketch toolbar hasn’t really changed since 2000, when SOLIDWORKS still looked as dandy as this:
A lot of fancy and user-friendly things are happening under the hood by now. Many of which affect macro speed. Line ends snap to origins and midpoints, lines get tagged as being horizontal or vertical and they snap at 45 degrees. And that’s only the things that you can actually see. When you want to import 100,000 points into a 3D scan, you don’t need SOLIDWORKS to check each points if it may coincide with another one. So you turn it off.
By invoking the AddToDB command, you can skip all of those smart things and it becomes blazingly fast. You can now add sketch entities directly to the database by setting AddToDB to true. The running time in my 100*100 grid went from 1 minute 26 to just 21 seconds, a 4.1 times speed increase on top of the 3.2 times in section 1. It’s perfect for sketch-heavy macros. It’s just less then ideal for macros that create fancy sketches, because at those times you could really use a line to snap to the origin.
Make sure you disable the command after you’re done with the SOLIDWORKS session.
There is another reason for using AddToDB. The image below shows my initial result. It doesn’t look like 100 points because there are two at each position. Hence the coincident relation icon. When AddToDB is not used, by default two sketch entities at around one millimeter apart will snap together. You could turn off snapping programmatically or manually in the settings or enable AddToDB to prevent this from happening.
There are two sections of the screen that keep the user in the loop with what your code is doing. These are the feature tree and the graphics area and you can prevent both from updating. Each of those takes up some of the available resources and turning both off does wonders for the macro speed and thus the waiting times.
This improvement is basically a no-brainer when the user doesn’t need to see everything that is happening. I haven’t noticed any disadvantages at all with this command. When you have a model object in the variable swModel (like most of us do), this is the line you need to speed up your code:
swModel.FeatureManager.EnableFeatureTree = False
I reduced the running time by 25-35% after changing this setting. Just set the updating back to true when you’re done with that model.
When you don’t need the user to see everything that’s going on while running the macro, you can stop the graphics part of the screen from updating. You could also use this trick to prevent others from reverse-engineering your code because you are hiding the steps to get to the end result.
We need a few extra lines here because we need to figure out which part of the screen to keep from updating. Since the user can have multiple files open and in view
dim modView as ModelView
set modView = swModel.ActiveView
modView.EnableGraphicsUpdate = False
This feature alone allowed my macros to run about 30% faster. Just as with the previous function, be sure to flip the switch again when you’re done.
I did run into one drawback however. When you save files from a macro that has no graphics updating, the preview images in Windows are not available. I noticed a drop in file size because of that as well. That is why I chose not to use this function in my final project.
This is one of the most drastic measures to reduce waiting times and improve macro speed. You can actually hide the complete SOLIDWORKS window. I think this feature was designed mainly to prevent the user from noticing the program is used at all. In my case the program was open already and I start the macro from the VBA editor. When I hit go, the SOLIDWORKS window disappears and only the editor window remains.
I actually combine the Visible function with the UserControlBackground function to get this done. The first one hides the window, the second one prevents the user from having control over the application. They are somehow wired together under the hood in a way that I don’t really understand yet.
The result: I had to wait 11 seconds instead of 20. Another improvement of nearly two times.
As explained above, I created this macro to create 100 sketches with 100 sketch points each. It’s pretty similar to importing 3D points into SOLIDWORKS, a topic that I have seen more than a few topics on in the SOLIDWORKS forums. I expected massive speed gains by using AddToDB and didn’t know what to expect from the other improvements.
Conclusion: awesome, I got the time from 4 minutes and 37 seconds to 8 seconds! That means the macro velocity is nearly 35 times greater. Every function did its work and was able to shave off some time. Only at the very end did I not see a measurable improvement. You might still be able to see a difference when you start measuring milliseconds.
This time I have added a ‘Save As’ instruction to save a copy of the file after each sketch creation. It still creates 100×100 sketch points.
The results are pretty similar to case 1, the extra time is pretty consistent at 10 to 16 seconds. Apparently that is the time required to write a hundred small files to an SSD. Writing many tiny files to a hard drive word certainly worsen performance.
This test case is even more similar to the macro that I want ultimately want to improve the speed of. It creates fewer sketch entities (100 sketches, 10 points per sketch), then saves a copy of the file after each sketch creation. I expect AddToDB to have less of an effect here.
Conclusion: there were fewer sketch entities, so fewer event interruptions. That is why test 1 took considerably less time then in the previous case and that is why there is less of a difference between test 1 and 2. In the end there still is a difference of a factor 5 in execution speed.
I originally didn’t plan to include anything on Excel in this post. That was until I ran a few test to optimize the macro speed of an Excel-heavy SOLIDWORKS macro. Since Excel keeps roughly half of the world’s businesses afloat, I decided to include my results.
I created two XLSX files with 30 columns, one with 100 rows, one with 1000 rows. I wrote a simple macro that reads these cells in three different ways:
lastRow = .Cells(.Rows.Count, columnLetter).End(xlUp).Row
The differences where once again incredible. Reading 1000 lines went from 20 seconds to 0.2 seconds, 100 times faster. So if you’re working with massive Excel file, make sure you read as much of it in one go and use internal variables to process the data. The same goes for writing to and Excel file. Try to do it once, not cell by cell.
Here’s a short list to summarize the five method I’ve talked about in this post to improve macro speed:
These have been a pretty fruitful couple of days. I created macros that could bring my laptop to its knees for minutes, only to help lowering the loads after that. I was surprised to see that each of the methods that SOLIDWORKS offers could improve the macro speed by a factor of two or three. When you combine them all, the result is at least an order of magnitude faster, maybe even two. I can’t wait to add all of these improvements to my macro that takes 24 hours to run.
Did these tips help you improve your macro speed as well? Do you have a method to add to this list? Please let me know at firstname.lastname@example.org.