In nowadays Visual Basic is mainly used in VBA macros format with a minor exception of several legacy applications.
Although VBA macros are usually small applications, those are still playing a major role in millions of organizations worldwide. As macros are applications, they should be considered as such and all the practices for writing reliable, maintainable and stable application should still be applied when developing macros.
Below is the list of guidelines for improving the quality of the macro. These guidelines applies to any type of the macro, e.g. MS Word, MS Excel, SOLIDWORKS, Autodesk Inventor etc.
Visit 5 best practices to make your VBA macro great blog article for the video demonstration of the practical application of the below guidelines.
When developing any application (VBA is not an exception) try to use as descriptive as possible names for variables, functions, procedures, modules, classes and events.
Prefer to use descriptive names instead of comment
Dim dayOfTheWeek As String dayOfTheWeek = "Monday"
'day of the week Dim x As String x = "Monday"
Do not overload code with comments for the obvious snippets. Comment in the snippet below is redundant
Function GetCircleArea(radius As Double) const PI As Double = 3.14 'Finding the area of the circle Dim area As Double area = PI * radius ^ 2 GetCircleArea = area End Function
Avoid Magic String And Numbers
Const PI As Double = 3.14 Dim circArea As Double cirArea = PI * rad ^ 2
Const OUT_FOLDER_PATH As String = "D:\out" Export OUT_FOLDER_PATH
When developing code, avoid 'hiding the problem' as this may potentially cause more damage than crash of the application.
For example, the following snippet checks if the swModel object is not nothing and performs important operation. However, having the swModel equal to Nothing is possible scenario and skipping the export without notifying the user, will keep the problem unnoticed and user will not be able to understand why DoSomeImportantWorkWithModel was not performed on this model.
Dim swModel As SldWorks.ModelDoc2 Set swModel = swComp.GetModelDoc2() If Not swModel Is Nothing Then DoSomeImportantWorkWithModel swModel End If
On Error Resume Next directive should be used with caution. Aim to process all exceptions in your code. I would recommend to handle all errors in the centralized (usually the entry function).
- DoWork() is a main function performing all the operations
- If at any point exception is thrown, code will be redirected to catch_ block, where error can be processed (e.g. logged or message box is displayed)
- Otherwise, code will be redirected to finally_ block and exit the macro once the DoWork routine is completed
Sub main() try_: On Error GoTo catch_ DoWork() GoTo finally: catch_: MsgBox Err.Description finally_: End Sub
'Fail Fast' Approach
To fix the above issue we can go with 'Fail Fast' approach, i.e. terminate the execution immediately, notifying the caller that something went wrong:
If Not swModel Is Nothing Then DoSomeImportantWorkWithModel swModel Else Err.Raise vbError, "", "Model of the component is null. Execution terminated" End If
And it is up to the calling function to decide what to do with this case. Depending on the requirement application can proceed and consider this as safe error or it can log the error or display the message to the user.
Most of the VBA macro enabled applications, such as MS Word, MS Excel, SOLIDWORKS, Autodesk Inventor would consider any parameterless function as a potential entry point of the macro.
The code below has 3 parameterless functions
- main - Actual expected entry function. Running this function will result into expected outcome
- Init - Function which initialized data, required to do work. This function itself doesn't perform the work. Running this as an entry point will not cause errors, but it will not produce any results
- DoWork - Function which performs the work, however, it requires InitData to be initialized. Running this function directly will most likely result into the 'Run time error 91: Object variable block variable set'
Dim InitData As Object Sub main() Init DoWork End Sub Sub Init() 'Initializes InitData End Sub Sub DoWork() 'Consumes InitData to perform the work End Sub
All the above 3 functions can be selected when running the macro
To prevent the potential issues, keep parameterless functions only for entry points. To make the function with parameter, without compromising an existing code, use optional parameter:
Dim InitData As Object Sub main() Init DoWork End Sub Sub Init(Optional dummy As Variant = Empty) 'Initializes InitData End Sub Sub DoWork(Optional dummy As Variant = Empty) 'Consumes InitData to perform the work End Sub
With the setup above, only one function will be available for running:
Independent Functions And Modules
Try to keep functions and modules as independent as possible from another functions, module level variables and environment. This would make a consumption of the function more predictable and function can be reused.
Dependency On Members
In the below code snippet the swModel variable is declared at the module level which makes it accessible from within the ProcessDocument function. Variable is initialized in the main function. It means that ProcessDocument is dependent on the swModel and will be only able to work with active document (or the one assigned to swModel). This function cannot be reused for another purpose, e.g. processing the model of the component in the assembly.
Dim swModel As SldWorks.ModelDoc2 Sub main() Set swModel = ActiveDoc ProcessDocument End Sub Sub ProcessDocument() 'Do work with swModel End Sub
Instead the above code could be rewritten to the following code. Instead of declaring the model level variable, it can be defined as the parameter of the function, thus removing the dependency. Now ProcessDocument function can be used with any pointer to SldWorks.ModelDoc2
Sub main() ProcessDocument ActiveDoc End Sub Sub ProcessDocument(model As SldWorks.ModelDoc2) 'Do work with model End Sub
Dependency On Environment
Another example is dependency on environment. Function below saves the value from the Excel cell into the text file. It takes 2 parameters for cell and the file path. However, this function doesn't perform any Excel specific functionality rather than calling the Value property on the cell to extract the text. But because of this, CreateTextFile function is dependant on the Excel environment and cannot be reused in other scenarios and applications (such as MS Word or Autodesk Inventor).
Sub CreateTextFile(cell As Excel.Range, fileName As String) Dim text As String text = cell.Value 'write text to fileName End Sub
Instead the function can be changed to the following code, where the caller is responsible for preparing the text for writing. This function can then be copied to another macros to perform similar functionality if required.
Sub CreateTextFile(text As String, fileName As String) 'write text to fileName End Sub
Documentation the functionality of your application could be very useful for the users of your software and for other collaborators of your project. The documentation can be as simple as text file, Word or PDF document or complete multi page technical site.
For VBA macros simple header at the top of the macro can be sufficient. Specify the author of the macro, license, contact details and brief description
' ------------------------------------------------- ' Created By Artem Taturevych (email@example.com) ' License: https://www.codestack.net/license/ ' Macro will display the callouts with the diameter values of all selected circular edges in the 3D model ' ------------------------------------------------- Sub main()
Referencing 3rd Party Type Libraries
When just few objects or functions from the 3rd party type library are used (e.g. Microsoft Scripting Runtime, Microsoft Excel, etc.) and this is not a primary target of automation, consider using them with Late Binding instead of an Early Binding
For example Excel VBA macro needs to create a dictionary object to hold key-value pairs. Instead of referring the Microsoft Scripting Runtime reference and using the following code
Dim dict As Scripting.Dictionary Set dict = New Scripting.Dictionary
It is possible to avoid adding the Microsoft Scripting Runtime reference and instead use
Dim dict As Object Set dict = CreateObject("Scripting.Dictionary")
Another example is SOLIDWORKS VBA macro, which primarily automates SOLIDWORKS, while some Excel API invocation might be required. In this case Excel can be created as late bound object and no references to Excel library need to be added to the macro.
This approach allows to simplify the compatibility between different versions of library and avoid missing references issue
Late binding has a limitation and some of the methods cannot be invoked with late binding, in this case early binding is an only option
Debug.Assert is a mechanism of displaying an assert when the condition is not met. Asserts are developer (not user) facing messages and intended to be used to validate conditions which are not the part of the use or misuse of the application. Assert should indicate the error in the code logic, but not the error in the use of the software.
For example the following SOLIDWORKS macro suppose to perform an operation on the active document. However it is a valid scenario where ActiveDoc can return Nothing. If macro runs when no document open (and user can do this), this would result in Nothing. So assert would not be an appropriate here to indicate the error, rather an exception would fit here better.
Dim swModel As SldWorks.ModelDoc2 Set swModel = swApp.ActiveDoc If Not swModel Is Nothing Then 'do work Else Err.Raise vbError, "", "No active document found" End If
On the other hand GetTitle function below, runs a custom code to find the title of the document. It is assumed that regardless of the circumstances, title can never be an empty string, so empty string returned from GetTitle indicates the logic error. Assert would be thrown if its condition is False (title <> "").
Dim title As String title = GetTitle(model); Debug.Assert title <> ""
Use Unit Tests
Visual Basic is not designed for unit tests, there are several 3rd party solutions available for unit tests implementation.
As a workaround, simple validation function can be implemented in the macro to emulate unit testing. This testing function should be run manually.
In the below code there are 2 functions which are the part of the macro logic:
- SortArray - sorts input array in ascending or descending order and returns result
- CountWords - counts the number of words in the specified test
Those functions can be tested independently as units (unit tests)
UnitTests functions defines and validates several test cases.
Sub main() 'main logic of the application which utilizes SortArray and CountWords End Sub Sub UnitTests(Optional dummy As Variant = Empty) 'test1 res1 = SortArray(Array("C", "B", "A"), True) Debug.Assert res1(0) = "A" And res1(1) = "B" And res1(2) = "C" 'test2 res2 = SortArray(Array("C", "B", "A"), False) Debug.Assert res2(0) = "C" And res2(1) = "B" And res2(2) = "A" 'test3 Dim res3 As Integer res3 = CountWords("Hello World") Debug.Assert res3 = 2 End Sub Function SortArray(arr As Variant, asc As Boolean) As Variant 'sorts an array and returns the result End Function Function CountWords(text As String) As Integer 'counts the words in the text End Function
For example, test1 runs the SortArray functions and supplies an array of [C, B, A] requesting the sorting in ascending order. The expected outcome is an array [A, B, C] which is a validated in the Debug.Assert call. If the condition is false, assert will be thrown indicating the failure of the test.
'test1 res1 = SortArray(Array("C", "B", "A"), True) Debug.Assert res1(0) = "A" And res1(1) = "B" And res1(2) = "C"
Control Version Systems (CVS) for source code provide a centralized management of the code in the plain text formats. Such systems include, but not limited to Git, SVN, Mercurial.
Most of VBA macro engine implementations in various applications such as MS Word, MS Excel, SOLIDWORKS, Autodesk Inventor store macro files either as embeded into a file or in the binary format which makes it unpractical to use with Control Version Services.
It is however still beneficial and recommended to extract a text copy of the macro code and add this under the revision control.
Stay Within The Scope
Try to keep your applications within the capacity of the technology and programming language. For a simple applications, scripts and VBA macros can be sufficient, but for more complicated functionalities where database connection, web service calls, data processing and analyzing etc. is required consider switching to more sophisticated environments (add-ins, stand-alone applications, web services) and OOP languages, such as VB.NET, C#, C++, Java etc.