Embed and Auto-Run Macros Using Equations

Blog 9.17.2013 15 Comments

This video summarizes the contents of this post and demonstrates the examples listed below.

Ever wanted a macro so tightly integrated with a part or assembly that 1) the macro always ran as long as the model was open, 2) the macro traveled with the model wherever it went? Using a clever, undocumented trick involving equations and the Design Binder, both of these are possible with little initial setup. The result is what I call Equation-Triggered Macros.

Note: As of SolidWorks 2015, Equation-Triggered Macros do not appear to be working at all. They are also not supported by SolidWorks Corporation. Use at your own risk.

Case Study

For our case study, let’s consider a problem I received from a friend a few weeks ago. His parts need to have their custom properties listed in alphabetical order. To reduce time and error, he could create an event notification macro that runs on SolidWorks startup and sorts the custom properties on every rebuild. Starting the macro with SolidWorks requires modifying the SolidWorks shortcut arguments on the user’s computer.

Two problems arise. First, you’ve now created a lot of administrative work for yourself because every user’s computer needs to be set up to automatically run this macro. Second, if the user doesn’t open SolidWorks through the modified shortcut then the macro will not run. Here’s how you can alleviate both problems without creating even more administrative work for yourself.

Step One : Write the Macro

Self-explanatory. In our case, we need to write a macro that sorts the custom properties alphabetically when it is run.

Step Two : Embed or Reference the Macro

Embedding versus referencing is essentially the same idea as embedding a design table within a document versus linking to it at an external location. If you embed, the macro travels with the document. No concerns about the macro getting lost or the link path becoming invalid. But if you need to change the macro code in lots of models? That could be a real chore (though you could write a macro that does this for you—more on that in the last section). If you think you’ll need to change the code regularly or simply want to remain in control of the code at all times, referencing is safer.

Embedding a macro in a document is pretty simple. While you have a part or assembly open, right click on the Design Binder (you might have to make it visible using Hide/Show Tree Items if it isn’t already) and choose Add Attachment. Browse for the .swp file, click OK, and you’re done!

Add attachment to Design Binder

Note: Embedding works with VBA macros only.

Referencing a macro is also simple. Just put the macro in the desired hard-drive / network location and copy the path. We’ll use the path in the next step.

Step Three : Add An Equation That Calls The Macro

This is where it gets interesting. Josh Brady, a long-time SolidWorks API enthusiast, made an incredible discovery back in 2007. Noting that you could use the VBA “iif” function in SolidWorks equations, he decided to see if other VBA equations could be used as well. The answer? Any and all VBA functions can be used as long as you format them properly! He also discovered that the same is true for custom properties. If you want to learn all of the guidelines and caveats for using VBA in equations, you can watch his excellent SolidWorks World 2011 presentation titled “Advanced Equations Using VBA and API Code”. The presentation files can be downloaded here.

Without going into details about formatting, here are the equations you need for embedding and referencing a macro named “macro.swp” with a sub-procedure named “main” in a module named “mMain”. Once you add the appropriate equation, each time your part or assembly rebuilds, the macro will be run.

Embedding
"Var1"= 1::swApp.RunAttachedMacro "macro.swp", "mMain", "main"

Referencing
"Var2"= 1::Dim lngErr As Long::swApp.RunMacro2 "C:\macro.swp", "mMain", "main", swRunMacroDefault, lngErr

Important comments:

  • The variable names are arbitrary and have no effect on the macro itself. If you already have a variable called “Var1” or “Var2”, then use another name.
  • The arguments for ISldWorks::RunAttachedMacro and ISldWorks::RunMacro2 need to match your macro name/path, module, and entry point. See the API Help articles for more details. In other words, unless your macro is named “macro.swp” and contains a sub-procedure called “main” in a module called “mMain”, using the code above verbatim will not trigger your macro!
  • Since SolidWorks 2012, the Equation Manager makes it difficult to add these equations due to the equation checker disliking the syntax of equations containing VBA code. To make sure you’ve added the equations properly, you should put the equation in a text file and then import that text file. As shown in the image below, the resulting equation may not successfully evaluate and the syntax may appear incorrect, but it should still work. If you struggle to get the Equation Manager to accept the equation, use Luke Malpass’ equation editor tool that lets you bypass the syntax checker. Please note that as long as the check next to the equation is gray, the equation has not been accepted and will not work.

VBA in SolidWorks equation

Going To The Next Level

At this point you should see the unique flexibility and power offered by equation-triggered macros (ETMs), possibly in conjunction with the Design Binder. With the “vanilla” ETM setup I’ve just shown you, however, the macro always runs when the part rebuilds, and sometimes you need more control over when the macro runs. To give you that control, I’ve created a class called ETMUtil that you can add to a macro (whether embedded or referenced) that gives you the following options:

  1. Only allow your code to trigger once within a specific time period (e.g., 30 seconds).
  2. Only trigger your code once when the part is opened.
  3. Only trigger your code once the first time a part is opened, but never on subsequent openings. This might be used with part or assembly templates for example, to aid the initial configuration of the model.
  4. Delete the temporary macro files. When a macro is run from the Design Binder, a copy of the macro is created in C:\Users\\AppData\Local\Temp and then run. Due to known bug (SPR 400218) SolidWorks never deletes these files, so they can build up over time. If you think this could become a problem over time and you don’t want to remember to delete them manually, ETMUtil can help. Obviously this problem is avoided entirely if you reference the macro instead of embedding it using the Design Binder.

Sound appealing? ETMUtil, and instructions on how to use it, is available to CADSharp.com premium members in the Macro Library under Automation / Tools. Watch the video at the beginning of this post to see ETMUtil in action or check out one of the examples below.

Note: As of SolidWorks 2014, ETMUtil no longer works, because the ability to suppress equations was removed. An alternative solution exists using event notifications, but I have not had time to implement it.

Examples

Here’s the finished version of the part from the original case study we discussed, along with three other examples.

Sort custom properties – Macro is embedded in a part. Every rebuild, the custom properties are sorted into alphabetical order.

Keep bodies renamed – Macro is embedded in a part. Every rebuild, the solid bodies are renamed Body1, Body2, Body3, etc.

Track part usage in assemblies – Macro is embedded in an assembly. Before the assembly is saved, the part level custom properties are updated to contain the number of times the part is used in an assembly. The custom property is named after the assembly. The code triggers before the assembly is saved instead of during each rebuild to improve performance.

Display user form on part open – (Premium members only) Macro is embedded in a part. When the part is opened, a user form is displayed that allows the user to easily change the part material and modify a custom property called “Edited By”. Uses ETMUtil to allow code to prevent any equation-triggers beyond the first one when the part opens.

Answers to Important Questions

Why can’t I use equation-triggered macros with drawings?

Because you can’t add equations to drawings.

Using equation-triggered macros it is possible run code on every rebuild. Is there any way I can listen for other events?

Yes. The macro you embed or reference simply needs to listen for that event using event notifications (see our Macro Library for examples). In fact, if you wanted to have your code run post-rebuild instead of when the equation recalculates, you could use ETMUtil to turn on a post-rebuild listener as soon as the part opens, prevent any additional equation-triggers, and use that listener to trigger your code.

My macros are in VB.NET / C#. Is there any way I can still use this technique?

Yes. You’ll need to always reference the macros from an external location because .NET macros cannot be run from the Design Binder.

When I edit a macro in the Design Binder and save it, the macro behaves as though I never edited it. Why?

You can’t truly edit a macro that’s in the Design Binder. You need to make edits to a macro that is on your hard-drive or network, then delete out the macro in the Design Binder, and add in the edited macro.

I can’t get the equation editor to accept my equation containing VBA code. What can I do?

First, did you try using Luke Malpass equation editor tool, which bypasses the Equation Manager’s syntax checker? Second, does part of the equation contain VBA keywords like “if”? For example, a macro called “modify fillet.swp” will confuse the Equation Manager because it thinks that the letters “if” in “modify” are the beginning of an if-then statement. Third, have you tried using the referencing equation rather than the embedding equation? In some instances I’ve seen the Equation Editor refuse to accept one but not the other.

SolidWorks is crashing when my equation-triggered macro runs. Why?

Does your macro edit features or perform any other task that might cause a rebuild? Keep in mind that ETMs run during a model’s rebuild. Causing another rebuild during this first rebuild will probably cause a crash. One way you can avoid this situation is by having your ETM start a post-rebuild listener that performs your task once the model rebuild is finished. The rebuild listener should be set to nothing after it has done its work (see example), otherwise that same task will keep running during every rebuild, even if you’re using ETMUtil (or your own code) to prevent subsequent triggerings of the code.

Using equation-triggered macros, couldn’t SolidWorks models carry viruses?

Yes, they could. For that reason you should only accept CAD files from trusted sources. Of course, a virus could be implemented in a macro feature as well, so it is not as though this is a new possibility.

How can equation-triggered macros improve .NET macro features?

Unlike VBA macro features, .NET macro features cannot have code that defines the macro feature embedded in the document. This means that if the path to the DLL is invalid, the macro feature will fail. This makes it tedious when sending files containing .NET macro features to other users, because you have to ensure that they have the defining macro and its supporting DLLs in the right path on their end. Using the equation + Design Binder technique, you can place the defining macro and its supporting DLLs in the Design Binder and a macro that verifies that the defining macro exists at the correct location. If it doesn’t, it saves the defining macro and its supporting DLLs to that location.

Conclusion

I hope you enjoyed learning about equation-triggered macros as much as I did. If you have ideas for ways you could use them, I’d love hear about it in the comments.

Onward into new automation frontiers,
Keith

Want to keep up with future CADSharp.com content, webinars, and special offers? Sign up for our newsletter.


Leave Comment

Using Microsoft Excel with the SolidWorks API

Blog 7.3.2013 11 Comments

Given the ubiquity of Microsoft Excel in engineering departments worldwide, it is no surprise that our “Using Microsoft Excel with the SolidWorks API” tutorial, available to Premium members, is one of our most popular. While I am not going to cover all of the material presented in that lesson, I do want to provide you with a basic run-down of using the Excel API in a SolidWorks macro, and vice versa. As a bonus, I’ll also cover how to include an Excel macro in SolidWorks design table.

It’s All About the References

Why is it so easy to integrate the Excel and SolidWorks APIs using Visual Basic for Applications? Microsoft created VBA as a way for users to program with the APIs of their Office products. They also made VBA available to third-party software developers (like SolidWorks Corporation) to integrate in their software. So that’s what SolidWorks Corporation chose to do—use VBA as one way to create macros in SolidWorks. Consequently, SolidWorks users get to use the same language and development environment to program in both SolidWorks and Microsoft Office. Very convenient!

But here’s an important point that many novice API programmers miss: VBA doesn’t automatically understand SolidWorks API code. Rather, SolidWorks Corporation chose to expose SolidWorks API interfaces and calls and store them in “type libraries”. By default these libraries are located in the root directory of your SolidWorks installation. If you want your VBA macro to understand SolidWorks API code, your macro must REFERENCE these type libraries. Guess what? By default, your SolidWorks VBA macro does references these libraries. You can see them if you go to Tools–>References while in the VB Editor.

What I just wrote about SolidWorks is true for Excel or any other application. VBA can’t “see” the API for an application unless its libraries (.tlb, .dll, or .exe) are referenced. Now here’s the big takeaway: if you want to integrate Excel API code into your SolidWorks macro, it is as simple as referencing the Excel libraries alongside your SolidWorks libraries. Indeed, you can reference as many libraries as you want. You could control Excel, Word, and Access in your SolidWorks macro if you wanted.

Enough technical background. Let’s actually make this happen.

Controlling Excel In A SolidWorks Macro

Setting up the reference to the Excel library is as simple as this:

  1. Open up a new VBA macro in SolidWorks
  2. In the VB Editor, go to Tools–>References
  3. Locate “Microsoft Excel 14.0 Type Library”, click the check box next to it, then click OK on the References dialog box

Now, to connect to Excel we’re going to use this code:

Dim xlApp As Excel.Application
Set xlApp = New Excel.Application

Notice its similarity to the code we use to get ISldWorks. Beyond that, learning how to work with the Excel API is really just as simple as using the Object Browser and Google a lot. Indeed, unlike SolidWorks, there is no “Excel API Help” contained with Excel. As an example, here’s some code that will display a message box for each row in the first column of a spreadsheet. (Place this sample spreadsheet in C:\ to use the following example.) Make sure that Excel is not already open while you run this code.

#If VBA7 Then
Declare PtrSafe Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As Long
#Else
Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
#End If
Const WM_QUIT = &H12
    
Sub main()
    Dim swApp As SldWorks.SldWorks
    Set swApp = Application.SldWorks
    
    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    
    Set xlApp = New Excel.Application
    xlApp.Visible = False
    Set xlWB = xlApp.Workbooks.Open("C:\test.xls")
    
    'find number of rows with data
    Dim row As Integer
    row = 1
    With xlWB.Worksheets(1)
        While Cells(row, 1).Value <> ""
            swApp.SendMsgToUser2 Cells(row, 1).Text, swMbInformation, swMbOk
            row = row + 1
        Wend
    End With
    
    'clean up
    xlApp.Visible = True
    PostMessage xlApp.hwnd, WM_QUIT, 0, 0
    
    Set xlWB = Nothing
    Set xlApp = Nothing
End Sub

Two notes: 1) The Win32 API call is used to properly shut down Excel, otherwise a residual EXCEL.EXE process can remain that interferes with future attempts to use Excel, 2) If you get a compile error that says “User-defined type not defined”, it probably means that you did not set the correct references.

Interesting in learning how to create a 3D point cloud in SolidWorks using point data stored in an Excel spreadsheet? Check out Lesson 7.1 on our Videos page.

Controlling SolidWorks In An Excel Macro

Now that we know how to run Excel from SolidWorks, let’s do just the opposite. First, a note about the differences between SolidWorks and Excel macros. In SolidWorks, the VBA macro is its own file (.swp). You cannot store a SolidWorks macro in a SolidWorks document and then run it from there. In Excel, it’s the exact opposite. Macros cannot be saved as external files. They must be stored in the Excel document, and they must be run from there. To created, edit, run, or delete an Excel macro, follow these steps:

  1. Open Excel
  2. Go to the View tab in the ribbon bar
  3. Click the Macros button on the far right
  4. To create a new macro, start typing a name and then click Create, otherwise select a macro from the list and choose Run, Edit, or Delete

Once you’ve created a new macro, you’ll need to go to Tools–>References and add in “SldWorks 20XX Type Library”. Next, try out this code:

Dim swApp As SldWorks.SldWorks
Sub main()
    Set swApp = CreateObject("SldWorks.Application")
    swApp.Visible = True
    swApp.SendMsgToUser "Hello!"
End Sub

You should have a good idea of where to go from there. But what if you have more that one version of SolidWorks on your computer? How do you specify which one to open? Simply specify the version number like this:

Set swApp = CreateObject("SldWorks.Application.21")

Determine the version number for a major release like this: take the last two digits of the year (e.g., “13” for SolidWorks 2013) and add 8. Therefore, SolidWorks 2009’s version number is 17, 2013’s is 21, and so on.

What if, instead of opening a new instance of SolidWorks, you want to get an existing instance? Instead of calling CreateObject, use GetObject like this:

Set swApp = GetObject(, "SldWorks.Application")

Of course, you can also specify a version if you want:

Set swApp = GetObject(, "SldWorks.Application.21")

Storing An Excel Macro In A Design Table

Finally, for those of you who use design tables, I want to show you a cool trick. If you have an Excel macro that you would prefer to use to generate configurations in your design table, you can keep it embedded in the design table if you take the following steps:

  1. Create the design table in Excel
  2. Very important: save the design table as a .xls, NOT a .xlsm
  3. In your SolidWorks part or assembly, delete out the existing design table if it already has one
  4. Insert the design table you created in step 1, choosing the “From file” option
  5. Edit your design table in a new window and you should have access to your Excel macro in View–>Macros


Want to share any other tips or tricks for using Excel with SolidWorks? Please leave a comment!

Keith

Want to keep up with future CADSharp.com content, webinars, and special offers? Sign up for our newsletter.


Leave Comment

How to make your VBA macros x64 compatible

Blog 6.3.2013 1 Comment

In a hurry? Just watch the video above. It covers the most important points. If your scenario isn’t covered in the video, then I probably cover it in this article.

Overview

SolidWorks 2013 includes VBA 7—the latest version of Microsoft’s Visual Basic for Applications. This is great news for several reasons, the most significant being: 1) VBA user forms no longer pop-up behind the SolidWorks application window, and 2) it proves to us that both Microsoft and SolidWorks Corporation are serious about maintaining the VBA language, which is the language of most SolidWorks API code.

VBA7 is 100% compatible with VBA6 (the version included in SolidWorks 2012 and earlier). For this reason, all SolidWorks API code that you write in pre-2013 should work in 2013, and vice versa. Nothing about the SolidWorks API itself has changed that requires modification.

That being said, you may have discovered that some of your macros no longer work in SolidWorks 2013. When you try to run the macro, you may get the following error.

VBA7 DLL error

Why is this? Were we not told that VBA7 is 100% compatible with VBA6? The issue isn’t with VBA. The reason your macros are failing is because they are not set up to reference 64 bit DLL files, or the DLL files you are trying to reference are not 64 bit compatible. This includes ActiveX controls, which have the extension .ocx but are actually still DLLs underneath. So if you are using any of the Microsoft Common Controls in your user form (e.g., slider, calendar, web browser) then you are using ActiveX controls that will fail in VBA7.

First I’m going to give you the quick-and-dirty steps on how to (hopefully) fix your dilemma. Later, if you’re so inclined, you can keep reading and learn the technical “why” behind the dilemma.

Fixing the Problem

Fixing the problem comprises three steps: 1) obtaining a 64 bit version of the DLL that is causing problems, 2) re-declaring its functions using new VBA7 keywords, 3) including conditional code that allows for backward compatibility on VBA6.

1. Get a 64 bit version of the DLL

If you wrote the DLL yourself, then re-compile the DLL to work on “Any CPU”. This will allow the DLL to run on 32 or 64 bit systems.

If someone else wrote the DLL, you will need to search online or contact them to determine if a 64 bit version available. An example is the SolidWorks Document Manager (SwDocumentMgr.dll). With the advent of 64 bit architecture, SolidWorks Corporation recompiled this DLL to work on 64 bit computers. If, for some reason, you cannot obtain a 64 bit version of this DLL then your only options are 1) find another 64 bit compatible DLL containing a function that does the same thing, 2) write your own DLL with the desired function and compile it for 64 bit, 3) if the DLL works on Microsoft’s COM framework (as opposed to .NET) then you can actually create a custom 32 bit “server” DLL to act as an interface between it and VBA7. For more information on the last solution, consult the excellent SolidWorks World 2013 presentation on VBA7 by Frank Lindeman, available here.

If the DLL is a Microsoft common library (Win32), then you can rest assured that the DLL has been recompiled for use on 64 bit. An easy way to determine whether the DLL is a Win32 DLL is to google it. If there is an article describing it at MSDN then it is probably a Win32 API function. Four of the most common Win32 DLLs are kernel23.dll, gdi32.dll, user32.dll, and shell32.dll.

As mentioned in the Overview, ActiveX controls are technically just wrappers for an underlying DLL. The most popular ActiveX controls are Microsoft Common Controls (mscomctl.ocx). Unfortunately, Microsoft has decided not to update the underlying DLLs to support 64 bit, so you will definitely need a workaround. If you can’t find an alternative DLL and don’t mind writing your own, keep in mind that since these controls are really just wrappers for underlying Win32 API calls, it is certainly possible to write a DLL that accesses those Win32 API calls directly.

2. Modify the function declarations and associated variables

Once you have a 64 bit version of the DLL, all you need to do is modify the function declaration to include the PtrSafe keyword after Declare and change all Long variables used in the function to LongPtr. For example, examine the difference in declaration with this Win32 API function called “SHGetPathFromIDList”:

32 bit
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long

64 bit
Private Declare PtrSafe Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" (ByVal pidl As LongPtr, ByVal pszPath As String) As LongPtr

A few additional comments:

1. In your macro, you may have many more Long variables associated with the function that are declared on separate lines. If you do not convert these to LongPtr, then you may either get a “Type mismatch” compile error, or SolidWorks will crash entirely. Watch the video at the beginning of the article for an example of this. Conversely, you might also notice that it is not necessary to change all Long variables to LongPtr. This is because the only variables that need converting are those that hold pointers. If you don’t know whether a variable corresponds to pointer (and therefore should be converted to LongPtr), simply verify by using this Microsoft-published text file of PtrSafe Win32 API calls. Alternatively, you can indiscriminately convert all Long variables to LongPtr, since it doesn’t hurt anything.

2. The inclusion of the PtrSafe keyword simply means you think the DLL is safe for 64 bit use. This doesn’t mean it actually is safe! If you aren’t certain if a DLL is 64 bit, you can determine this using tools like Process Explorer or Dependency Walker.

3. Even if your DLL is compiled for 64 bit and you are using PtrSafe and LongPtr properly, your macro will still fail if your DLL has any dependent DLLs that are not compiled for 64 bit.

3. Make your code compatible with VBA6 and VBA7

The PtrSafe and LongPtr keywords that we used just a moment ago are unrecognized by VBA6. Does this mean we have to create a version of our macros for use on 64 bit and another version for 32 bit? Thankfully, no. This is where Conditional Compilation Constants come in. Using these constants, we can actually tell the VBA pre-processor to exclude certain lines of code during compilation. Specifically, we’ll use the “VBA7” constant to test for the version of VBA we’re using. If it is VBA7, we’ll use our declaration involving PtrSafe and LongPtr. Otherwise, we’ll use the VBA6 declaration. The syntax looks like this:

#If VBA7 Then
Private Declare PtrSafe Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" (ByVal pidl As LongPtr, ByVal pszPath As String) As LongPtr
#Else
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
#EndIf

Not too bad, is it? Note that you can use conditional compilation constants anywhere in your code, including LongPtr declarations within other functions and sub-procedures.

Technical Background

I hope by this point you know what you need to do to correct any 64 bit compatibility issues you’re having with your macros. For some of you, however, this isn’t enough. You want to know the “why” behind all of this. This next section should answer of that for you.

To answer the “why” question, we need to begin by understanding why 64 bit computers exist in the first place. As anyone who’s taken a basic course in digital logic knows, the smallest unit of information in a computer is a bit, which represents a 1 or a 0. The smallest amount of information that your computer can write to and read from, however, is not a bit but a byte, which is composed of 8 bits. Next, what you need to understand is that when data is stored in RAM (memory), each byte has an address. Just like a postal address, your computer needs to know where data is being stored in memory in order to read it or write to it. On a 32 bit system, these addresses are composed of 4 bytes. Since each byte is composed of 8 bits, that means we have 32 bits total to work with. In binary, that means our total number of possible bytes we can address with a 32 bit memory address is 2^32, or 4,294,967,296. As you know, that’s equal to 4 GiB.

Now, as we all know, 4GB just isn’t good enough! We want more memory… lot’s more! But with only 32 bits, we can’t address all of that memory. The solution? Start making addresses that are 64 bits in length. This will allow for a whopping 16 Exabytes of possible memory. (Note: No doubt it will only be a couple of decades before I look back and laugh at myself for finding 16 Exabytes so astounding!)

Continuing on, we need to know that addresses are also called “pointers”. Since these pointers are composed 4 bytes, we need store them in a data type of this size. If you open up the Microsoft VBA Help in the VB Editor and locate the “Data Type Summary” article, you’ll see that Long and Object data types listed as containing 4 bytes. Not surprisingly, these are the data types used for pointers. In the case of the SolidWorks API, the Object data type is used to store the pointer for our SolidWorks object interfaces like ISldWorks, IModelDoc2, etc. In the case of DLL functions, the pointers are often times declared as Long.

VBA6 is the version of VBA used in the late 90’s and the following decade. It can only handle 32 bit addresses. Now that computers are using 64 bit addresses, a new version of VBA was needed to handle addresses of this length. Thus VBA7 was created. Since VBA7 uses 64 bit memory addresses, any DLLs that it references must be 64 bit compatible. This is where the problem comes in for so many users: the DLLs referenced in their macros are still compiled for 32 bit computers. For many DLLs, this issue is solved by recompiling the DLL for 64 bit.

When you call a function from a DLL in a VBA macro, you need to use the Declare statement. For example, let’s say you have a DLL called “diskspace.dll” that resides in C:\somepath\. Here is how you would declare it in your module:

Declare Function getdiskinfo Lib "c:\somepath\diskinfo.dll"
(ByVal mydrive As String, ByVal myvolume As String, free As Long) As Long

On 64 bit computers, however, this will not work because our Long variables, which are being used as pointers, cannot handle 64 bit addresses. Basically, we need a new version of Long that can hold a 64 bit memory type. In VBA7, Microsoft created this new variable for us. It is called “LongLong”, and it only works in 64 bit applications. Converting our Long variables to LongLong isn’t the only modification we have to make, however. In order to indicate that the DLL is safe for 64 bit use, we also need to insert the PtrSafe keyword after the Declare keyword. So all together we have this:

Declare PtrSafe Function getdiskinfo Lib "c:\somepath\diskinfo.dll"
(ByVal mydrive As String, ByVal myvolume As String, free As LongLong) As LongLong

Keep in mind that this will ONLY work on 64 bit versions of our application. For applications like Microsoft Office, however, you can actually have a 32 bit version of Office running on a 64 bit computer. In this case, LongLong wouldn’t work, so Microsoft created a data type that transforms into Long or LongLong depending on the application. This data type is called “LongPtr”. SolidWorks x86 (32 bit) cannot be installed on a 64 bit computer, but you might as well use LongPtr anyway:

Declare PtrSafe Function getdiskinfo Lib "c:\somepath\diskinfo.dll"
(ByVal mydrive As String, ByVal myvolume As String, free As LongPtr) As LongPtr

To summarize: once your DLL is compiled for 64 bit, all you need to do is change the pointers to LongPtr and insert the PtrSafe keyword after the Declare keyword. Moreover, you can use what are called “Conditional Compilation Constants” to allow for backward compatibility of your code, which was demonstrated in any earlier section of the post.

Before I finish up, I want to share something interesting regarding the SolidWorks API and 64 bit. Even though 64 bit computers have been supported by SolidWorks since SolidWorks 2006, SolidWorks 2013 is the first version to contain VBA7 (since Microsoft did not make VBA7 available to third-party vendors until recently). That means that for SolidWorks 2006-2012 64 bit, VBA6 was still being used. How did that work? It worked because SolidWorks Corporation created an out-of-process COM server called swVBAserver.exe to handle the interaction between 64 bit SolidWorks and 32 bit VBA. (You may have noticed swVBAserver.exe in your Task Manager.) Normally, if VBA and SolidWorks were both 32 bit or 64 bit, VBA can run inside the SLDWORKS.EXE process. When this isn’t the case, however, VBA must run out of process, which also causes the API to run slightly slower. The out of process scenario is also what caused the infamous issue of user forms appearing behind the SolidWorks application: it is the swVBAserver.exe that owned the form, not SLDWORKS.EXE. In SolidWorks 2013, VBA does run inside the SLDWORKS.EXE process, so the correct owner is identified and no additional code is necessary. (Note: swVBAserver.exe is still used in SolidWorks 2013 and later, which is why you will see it in the Task Manager, but not as a proxy server for 32 bit VBA. It is also used to drive equation updates using IEquationMgr.)

If you want more resources on the technical aspects of the move to 64 bit, I’d encourage you to check out the following:

Thanks for reading!
Keith

Want to keep up with future CADSharp.com content, webinars, and special offers? Sign up for our newsletter.


Leave Comment