More with UserForms Controls

In this article we will discuss following topics:

• Changing properties for a UserForm control
• Viewing the UserForm Code window
• Showing the UserForm
• Using information from a UserForm

Changing properties for a UserForm control

Every control you add to a UserForm has a number of properties that determine how the control looks or behaves. In addition, the UserForm itself also has its own set of properties. You can change these properties with the Properties window. Below figure shows the properties window when a CommandButton control is selected:

Properties for controls include the following:

• Name
• Width
• Height
• Value
• Caption

Each control has its own set of properties (although many controls have some common properties). To change a property using the Properties window:

1. Make sure that the correct control is selected in the UserForm.
2. Make sure the Properties window is visible (press F4 if it’s not).
3. In the Properties window, click on the property that you want to change.
4. Make the change in the right portion of the Properties window.

If you select the UserForm itself (not a control on the UserForm), you can use the Properties window to adjust UserForm properties

Some of the UserForm properties serve as default settings for new controls you drag onto the UserForm. For example, if you change the Font property for a UserForm, controls that you add will use that same font. Controls that are already on the UserForm are not affected.

---

Viewing the UserForm Code window

Every UserForm object has a Code module that holds the VBA code (the event-handler procedures) executed when the user works with the dialog box. To view the Code module, press F7. The Code window is empty until you add some procedures. Press Shift+F7 to return to the dialog box.

Here’s another way to switch between the Code window and the UserForm display: Use the View Code and View Object buttons in the Project window’s title bar. Or right-click the UserForm and choose View Code. If you’re viewing code, double-click the UserForm name in the Project window to return to the UserForm.

---

Showing the UserForm

You display a UserForm by using the UserForm’s Show method in a VBA procedure.

The macro that displays the dialog box must be in a VBA module — not in the Code window for the UserForm.

The following procedure displays the dialog box named UserForm1:

VB: Showing the UserForm

Sub ShowDialogBox()
  UserForm.Show
  'Other statements can go here
End Sub

When Solidworks displays the dialog box, the ShowDialogBox macro halts until the user closes the dialog box. Then VBA executes any remaining statements in the procedure. Most of the time, you won’t have any more code in the procedure.

---

Using information from a UserForm

The VBE provides a name for each control you add to a UserForm. The control’s name corresponds to its Name property. Use this name to refer to a particular control in your code. For example, if you add a CheckBox control to a UserForm named UserForm1, the CheckBox control is named CheckBox1 by default. The following statement makes this control appear with a checkmark:

UserForm1.CheckBox1.Value = True

Most of the time, you write the code for a UserForm in the UserForm’s code module. If that’s the case, you can omit the UserForm object qualifier and write the statement like this:

CheckBox1.Value = True

I recommend that you change the default name the VBE has given to your controls to something more meaningful.

This will sum-up our tutorials on Visual Basic for Application. From now on I will give tutorials on how to use Solidworks commands with the help of VBA Macro.
If you want to know any explanation on any topic related to VBA, please drop a comment and I will try to give it to you. Thank you!!!!

Working with UserForms

Each dialog box that you create is stored in its own UserForm object — one dialog box per UserForm. You create and access these UserForms in the Visual Basic Editor.

Inserting a new UserForm

To insert a UserForm object with the following steps:

1. In the macro, you can insert User form with following 2 ways:


• From “Menu Bar” ⇨ “UserForm”
• From “Standard Toolbar” by clicking “Insert UserForm”

The VBE insert a new UserForm object with an empty dialog box.

2. If “Property window” is not available in your macro, press F4 to display “Property window”.

The VBE inserts a new UserForm object, which contains an empty dialog box.

Below figure shows a UserForm — an empty dialog box with some controls in Toolbox.

---

Adding controls to a UserForm

When you activate a UserForm, the VBE displays the Toolbox in a floating window, as shown in the above figure. You use the tools in the Toolbox to add controls to your UserForm. If the Toolbox doesn’t appear when you activate your UserForm, choose View ⇨ Toolbox.

To add a control, just click the desired control in the Toolbox and drag it into the dialog box to create the control. After you add a control, you can move and resize it by using standard techniques.

Below table indicates the various tools, as well as their capabilities. To determine which tool is which, hover your mouse pointer over the control and read the small pop-up description.

ToolBox Control
Controls	What it does
Label	Shows text
TextBox	Determines which of the file filters the dialog box displays by default.
ComboBox	Display a drop-down list.
ListBox	Display a list of items.
CheckBox	Useful for On/off or Yes/No options.
OptionButton	Used in groups; allows the user to select one of several options.
ToggleButoon	A button that is either on or off.
Frame	A container for other control.
CommandButton	A clickable button.
TabStrip	Display Tabs
MultiPage	A tabbed container for other objects.
ScrollBar	A draggable bar.
SpinButton	A clickable button often used for changing a value.
Image	Contains an image
RefEdit	Allows the user to select a range.

UserForms Basics

A UserForm is useful if your VBA macro needs to get information from a user. For example, your macro may have some options that can be specified in a UserForm. If only a few pieces of information are required (for example, a Yes/No answer or a text string), one of the techniques I describe in previous articles may do the job. But if you need to obtain more information, you must create a UserForm.

To create a UserForm, you usually take the following general steps:

1. Determine how the dialog box will be used and where it will be displayed in your VBA macro.
2. Activate the VBE and insert a new UserForm object. A UserForm object holds a single UserForm.
3. Add controls to the UserForm. Controls include items such as text boxes, buttons, check boxes, and list boxes.
4. Use the Properties window to modify the properties for the controls or for the UserForm itself.
5. Write event-handler procedures for the controls (for example, a macro that executes when the user clicks a button in the dialog box). These procedures are stored in the Code window for the UserForm object.
6. Write a procedure (stored in a VBA module) that displays the dialog box to the user.

Don’t worry if some of these steps seem foreign. I provide more details in the following sections, along with step-by-step instructions for creating a UserForm.

When you are designing a UserForm, you are creating what developers call the Graphical User Interface (GUI) to your application. Take some time to consider what your form should look like and how your users are likely to want to interact with the elements on the UserForm. Try to guide them through the steps they need to take on the form by carefully considering the arrangement and wording of the controls. Like most things VBA-related, the more you do it, the easier it gets.

The GetOpenFilename, The GetSaveAsFilename and The FileDialog methods

If your VBA procedure needs to ask the user for a filename, you could use the InputBox function. An input box usually isn’t the best tool for this job, however, because most users find it difficult to remember paths, backslashes, filenames, and file extensions. In other words, it’s far too easy to make a typographical error when typing a filename.

For a better solution to this problem, use the GetOpenFilename method of the Application object, which ensures that your code gets its hands on a valid filename, including its complete path. The GetOpenFilename method displays the familiar Open dialog box.

The GetOpenFilename method doesn’t actually open the specified file. This method simply returns the user-selected filename as a string. Then you can write code to do whatever you want with the filename.

The syntax for the GetOpenFilename method

The official syntax for the GetOpenFilename method is as follows:

VB: The GetOpenFilename method syntax

object.GetOpenFilename ([fileFilter], [filterIndex], [title],[buttonText], [multiSelect])

The GetOpenFilename method takes the optional arguments shown in below Table.

The GetOpenFilename method Arguments
Arguments	What it does
FileFilter	Determines the types of files that appear in the dialog box (for example, *.TXT). You can specify several different filters from which the user can choose.
FilterIndex	Determines which of the file filters the dialog box displays by default.
Title	Specifies the caption for the dialog box’s title bar.
ButtonText	Ignored
MultiSelect	If True, the user can select multiple files.

A GetOpenFilename example

The fileFilter argument determines what appears in the dialog box’s Files of Type drop-down list. This argument consists of pairs of file filter strings followed by the wild card file filter specification, with commas separating each part and pair. If omitted, this argument defaults to the following:

VB: A GetOpenFilename example

All Files (*.*), *.*

Notice that this string consists of two parts:

All Files (*.*)

And

*.*

The first part of this string is the text displayed in the Files of Type dropdown list. The second part determines which files the dialog box displays. For example, *.* means all files.

The code in the following example brings up a dialog box that asks the user for a filename. The procedure defines five file filters. Notice that I use the VBA line continuation sequence to set up the Filter variable; doing so helps simplify this rather complicated argument.

VB: A GetOpenFilename example

Sub GetImportFileName()
  Dim Finfo As String
  Dim FilterIndex As Integer
  Dim Title As String
  Dim FileName As Variant

  'Set up list of file filters
  If (IsNumeric)NumberOfSheets Then
  FInfo = “Text Files (*.txt),*.txt,” & _
  “Lotus Files (*.prn),*.prn,” & _
  “Comma Separated Files (*.csv),*.csv,” & _
  “ASCII Files (*.asc),*.asc,” & _
  “All Files (*.*),*.*”

  'Display *.* by default
  FilterIndex = 5

  'Set the dialog box caption
  Title = “Select a File to Import”

  'Get the filename
  FileName = Application.GetOpenFilename (FInfo, FilterIndex, Title)

  'Handle return info from dialog box
  If FileName = False Then
    MsgBox “No file was selected.”
  Else
    MsgBox “You selected “ & FileName
  End If
End Sub

Notice that the FileName variable is declared as a Variant data type. If the user clicks Cancel, that variable contains a Boolean value (False). Otherwise, FileName is a string. Therefore, using a Variant data type handles both possibilities.

---

The GetSaveAsFilename Method

The GetSaveAsFilename method works just like the GetOpenFilename method, but it displays the Save As dialog box rather than its Open dialog box. The GetSaveAsFilename method gets a path and filename from the user but doesn’t do anything with it. It’s up to you to write code that actually saves the file.

The syntax for this method follows:

VB: The GetSaveAsFilename method syntax

object.GetSaveAsFilename ([InitialFilename], [FileFilter], [FilterIndex], [Title], [ButtonText])

The GetSaveAsFilename method takes below arguments, all of which are optional.

The GetSaveAsFilename method Arguments
Arguments	What it does
InitialFileName	Specifies a default filename that appears in the File Name box.
FileFilter	Determines the types of files that appear in the dialog box (for example, *.TXT). You can specify several different filters from which the user can choose.
FilterIndex	Determines which of the file filters the dialog box displays by default.
Title	Specifies the caption for the dialog box’s title bar.

---

The Getting a Folder Name

Sometimes, you don’t need to get a filename; you just need to get a folder name. If that’s the case, the FileDialog object is just what the doctor ordered.

The following procedure displays a dialog box that allows the user to select a directory. The selected directory name (or “Canceled”) is then displayed by using the MsgBox function.

VB: FileDialog example

Sub GetAFolder()
  With Application.FileDialog(msoFileDialogFolderPicker)
    .InitialFileName = Application.DefaultFilePath & “\”
    .Title = “Please select a location for the backup”
    .Show
    If .SelectedItems.Count = 0 Then
      MsgBox “Canceled”
    Else
      MsgBox .SelectedItems(1)
    End If
  End With
End Sub

The FileDialog object lets you specify the starting directory by specifying a value for the InitialFileName property. In this case, the code uses default file path as the starting directory.

The InputBox Function

The VBA's InputBox function is useful for obtaining a single piece of information from the user. That information could be a value, a text string, or even a range address. This is a good alternative to developing a UserForm when you need to get only one value.

InputBox syntax

Here’s a simplified version of the syntax for the InputBox function:

VB: InputBox syntax

InputBox(prompt[, title][, default])

The InputBox function accepts the arguments listed in below table.

InputBox Function Arguments
Arguments	What it means
prompt	The text displayed in the input box.
Title	The text displayed in the input box’s title bar (optional).
Default	The default value for the user’s input (optional)

An InputBox example

Here’s an example showing how you can use the InputBox function:

VB: InputBox example

TheName = InputBox(“What is your name?”, “Greetings”)

When you execute this VBA statement, the application displays the dialog box shown in below figure. Notice that this example uses only the first two arguments and does not supply a default value. When the user enters a value and clicks OK, the routine assigns the value to the variable TheName.

Please note that VBA’s InputBox function always returns a string, so if you need to get a value, your code will need to do some additional checking. The following example uses the InputBox function to get a number. It uses the IsNumeric function to check whether the string is a number. If the string does contain a number, all is fine. If the user’s entry cannot be interpreted as a number, the code displays a message box.

VB: InputBox example

Sub GetDrawingSheetNumber()
  DDim NumberOfSheets as Stringg
  Prompt = “How many sheets drawing have?”
  NumberOfSheets = InputBox (Prompt)

  If NumberOfSheets = “” Then Exit Sub
  If (IsNumeric)NumberOfSheets Then
    '......[Some code here]....
    Else
    MsgBox “Please enter a number.”
  End If
End Sub

The MsgBox Function

You’re probably already familiar with the VBA MsgBox function — I use it quite a bit in the examples. The MsgBox function, which accepts the arguments shown in below table, is handy for displaying information and getting simple user input. It’s able to get user input because it’s a function. A function, as you recall, returns a value. In the case of the Msgbox function, it uses a dialog box to get the value that it returns. Keep reading to see exactly how it works.

Here’s a simplified version of the syntax for the MsgBox function:

VB: MsgBox Structure

MsgBox(prompt[, buttons][, title])

MsgBox Function Arguments
Arguments	What it does
prompt	The text your application displays in the message box
buttons	A number that specifies which buttons (along with what icon) appear in the message box (optional)
title	The text that appears in the message box’s title bar (optional) displaying a simple message box

You can use the MsgBox function in two ways:

1. To simply show a message to the user. In this case, you don’t care about the result returned by the function.
2. To get a response from the user. In this case, you do care about the result returned by the function. The result depends on the button that the user clicks.

If you use the MsgBox function by itself, don’t include parentheses around the arguments. The following example simply displays a message and does not return a result. When the message is displayed, the code stops until the user clicks OK.

VB: MsgBox function Example

Sub main()
  MsgBox "Hello, world!"
End Sub

Below figure shows how this message box looks:

Getting a response from a message box

If you display a message box that has more than just an OK button, you’ll probably want to know which button the user clicks. The MsgBox function can return a value that represents which button is clicked. You can assign the result of the MsgBox function to a variable.

In the following code, I use some built-in constants that make it easy to work with the values returned by MsgBox:

VB: MsgBox built-in constants Example

Sub GetAnswer()
  Dim Ans as Integer
  Ans = MsgBox (“Did you eat lunch?”, vbYesNo)
  Select Case Ans
    Case vbYes
    '......[Some code here]....
    Case vbNo
    '......[Some code here]....
  End Select
End Sub

Below figure shows how it looks. When you execute this procedure, the Ans variable is assigned a value of either vbYes or vbNo, depending on which button the user clicks. The Select Case statement uses the Ans value to determine which action the code should perform.

You can also use the MsgBox function result without using a variable, as the following example demonstrates:

VB: MsgBox without variable

Sub GetAnswer2()
  If MsgBox (“Continue?”, vbYesNo) = vbYes Then
  '......[Some code here]....
  Else
  '......[Some code here]....
  End If
End Sub

Customizing message boxes

The flexibility of the buttons argument makes it easy to customize your message boxes. You can specify which buttons to display, determine whether an icon appears, and decide which button is the default (the default button is “clicked” if the user presses Enter).

Below table lists some of the built-in constants you can use for the buttons argument. If you prefer, you can use the value rather than a constant (but I think using the built-in constants is a lot easier).

Constants Used in the MsgBox Function
Constant	Value	What it does
vbOKOnly	0	Display OK button only.
vbOKCancel	1	Display OK and Cancel buttons
vbAbortRetryIgnore	2	Displays Abort, Retry, and Ignore buttons.
vbYesNoCancel	3	Displays Yes, No, and Cancel buttons.
vbYesNo	4	Displays Yes and No buttons.
vbRetryCancel	5	Displays Retry and Cancel buttons.
vbCritical	16	Displays Critical Message icon.
vbQuestion	32	Displays Warning Query icon.
vbExclamation	48	Displays Warning Message icon.
vbInformation	64	Displays Information Message icon.
vbDefaultButton1	0	First button is default.
vbDefaultButton2	256	Second button is default.
vbDefaultButton3	512	Third button is default.
vbDefaultButton4	768	Fourth button is default.

for using more than one of these constants as an argument, just connect them with a “+” operator. For example, to display a message box with Yes and No buttons and an exclamation icon, use the following expression as the second MsgBox argument:

VB: Using multiple MsgBox built-in constants

vbYesNo + vbExclamation

Or, if you prefer to make your code less understandable, use a value of 52 (that is, 4 + 48).

The following example uses a combination of constants to display a message box with a Yes button and a No button (vbYesNo) as well as a question mark icon (vbQuestion). The constant vbDefaultButton2 designates the second button (No) as the default button — that is the button that is clicked if the user presses Enter. For simplicity, we assign these constants to the Config variable and then use Config as the second argument in the MsgBox function:

VB: Using multiple MsgBox built-in constants

Sub GetAnswer3()
  Dim Config As Integer
  Dim Ans as Integer
  Config = vbYesNo + vbQuestion + vbDefaultButton2
  Ans = MsgBox(“Is part opened?”, Config)
  If Ans = vbYes Then OpenPart
End Sub

Below figure shows the message box application displays when you execute the GetAnswer3 procedure. If the user clicks the Yes button, the routine executes the procedure named OpenPart (which is not shown). If the user clicks the No button (or presses Enter), the routine ends with no action. Because I omitted the title argument in the MsgBox function, our application uses the default title, in my case it is SOLIDWORKS.

Previous examples have used constants (such as vbYes and vbNo) for the return value of a MsgBox function. Besides these two constants, below table lists a few others.

Constants Used as Return Values for the MsgBox Function
Constant	Value	What it does
vbOK	1	User clicked OK.
vbCancel	2	User clicked Cancel.
vbAbort	3	User clicked Abort.
vbRetry	4	User clicked Retry.
vbIgnore	5	User clicked Ignore.
vbYes	6	User clicked Yes.
vbNo	7	User clicked No.

Dialog Boxes

You can’t use VBA very long without being exposed to dialog boxes. They seem to pop up almost every time you select a command. VBA uses dialog boxes to obtain information, clarify commands, and display messages. If you develop VBA macros, you can create your own dialog boxes that work just like those built in. Those custom dialog boxes are called UserForms in VBA. About which we look into next section.

UserForm Alternatives

Some of the VBA macros you create behave the same every time you execute them. For example, you may develop a macro for intermediate steps you do every day. This macro always produces the same result and requires no additional user input.

You might develop other macros that behave differently under various circumstances or that offer the user options. In such cases, the macro may benefit from a custom dialog box. A custom dialog box provides a simple means for getting information from the user. Your macro then uses that information to determine what it should do.

UserForms can be quite useful, but creating them takes time. Before I cover the topic of creating UserForms in the next section, you need to know about some potentially timesaving alternatives.

VBA lets you display several different types of dialog boxes that you can sometimes use in place of a UserForm. You can customize these built-in dialog boxes in some ways, but they certainly don’t offer the options available in a UserForm. In some cases, however, they’re just what you need.

In the following sections you read about

• The MsgBox function
• The InputBox function
• The GetOpenFilename method
• The GetSaveAsFilename method
• The FileDialog method

Bug Reduction Tips

I can’t tell you how to completely eliminate bugs in your programs. Finding bugs in software can be a profession by itself, but I can provide a few tips to help you keep those bugs to a minimum:

• Use an Option Explicit statement at the beginning of your modules. This statement requires you to define the data type for every variable you use. This creates a bit more work for you, but you avoid the common error of misspelling a variable name. And it has a nice side benefit: Your routines run a bit faster.
• Format your code with indentation. Using indentations helps delineate different code segments. If your program has several nested For-Next loops, for example, consistent indentation helps you keep track of them all.
• Use lots of comments. Nothing is more frustrating than revisiting code you wrote six months ago and not having a clue as to how it works. By adding a few comments to describe your logic, you can save lots of time down the road.
• Keep your Sub and Function procedures simple. By writing your code in small modules, each of which has a single, well-defined purpose, you simplify the debugging process.
• Use the macro recorder to help identify properties and methods. When I can’t remember the name or the syntax of a property or method, I often simply record a macro and look at the recorded code

Debugging code is not one of my favorite activities, but it’s a necessary evil that goes along with programming. As you gain more experience with VBA, you spend less time debugging and, when you have to debug, are more efficient at doing so.

About the Debugger

In this section, I discuss the details of using the VBA debugging tools. These tools are much more powerful than the techniques I discuss in the previous section. But along with power comes responsibility. Using the debugging tools takes a bit of setup work.

Setting breakpoints in your code

In earlier sections, I discuss using MsgBox functions in your code to monitor the values of certain variables. Displaying a message box essentially halts your code in mid-execution, and clicking the OK button resumes execution.

Wouldn’t it be nice if you could halt a routine’s execution, take a look at the value of any of your variables, and then continue execution? Well, that’s exactly what you can do by setting a breakpoint. You can set a breakpoint in your VBA code in several ways:

• Move the cursor to the statement at which you want execution to stop; then press F9.
• Click in the gray margin to the left of the statement at which you want execution to stop. This is I used mainly.
• Position the insertion point in the statement at which you want execution to stop. Then use the Debug ⇨ Toggle Breakpoint command.
• Right-click a statement and choose Toggle ⇨ Breakpoint from the shortcut menu.

The results of setting a breakpoint are shown in below figure. VBE highlights the line to remind you that you set a breakpoint there; it also inserts a large dot in the gray margin.

When you execute the procedure, VBE goes into Break mode before the line with the breakpoint is executed. In Break mode, the word [break] is displayed in the VBE title bar. To get out of Break mode and continue execution, press F5 or click the Run Sub/UserForm button in the VBE toolbar.

To quickly remove a breakpoint, click the large dot in the gray margin or move the cursor to the highlighted line and press F9. To remove all breakpoints in the module, press Ctrl+Shift+F9.

What is Break mode? You can think of it as a state of suspended animation. Your VBA code stops running and the current statement is highlighted in bright yellow. In Break mode, you can

• Type VBA statements in the Immediate window.
• Press F8 to step through your code one line at a time to check various things while the program is paused.
• Move the mouse pointer over a variable to display its value in a small pop-up window.
• Skip the next statement(s) and continue execution there (or even go back a couple of statements).
• Edit a statement and then continue.

Below figure shows some debugging action. A breakpoint is set (notice the big dot), and I’m using the F8 key to step through the code line by line (notice the arrow that points to the current statement). I used the Immediate window to print “Hello world”.

---

Using the Immediate window

The Immediate window may not be visible in the VBE. You can display the VBE’s Immediate window at any time by pressing Ctrl+G.

In Break mode, the Immediate window is particularly useful for finding the current value of any variable in your program. For example, if you want to know the current value of a variable, enter the following in the Immediate window and press Enter.

The Immediate window lets you do other things besides check variable values. For example, you can change the value of a variable, try for a new condition. Just make sure that the command you enter is a valid VBA statement.

---

Stepping through your code

While in Break mode, you can also step through your code line by line. One statement is executed each time you press F8 Throughout this line-by-line execution of your code, you can activate the Immediate window at any time to check the status of your variables.

You can use your mouse to change which statement VBA will execute next. If you put your mouse pointer in the gray margin to the left of the currently highlighted statement (which will usually be yellow), your pointer changes to a right-pointing arrow. Simply drag your mouse to the statement you want to be done next and watch that statement turn yellow.

---

Using the Watch window

In some cases, you may want to know whether a certain variable or expression takes on a particular value. For example, suppose that a procedure loops through 1,000 times. You notice that a problem occurs during the 800th iteration of the loop. Well, you could insert a breakpoint in the loop, but that would mean responding to 799 prompts before the code finally get to the iteration you want to see (and that gets boring real fast). A more efficient solution involves setting a watch expression.

For example, you can create a watch expression that puts the procedure into Break mode whenever a certain variable takes on a specific value — for example, AnyNumber=800. To create a watch expression, choose Debug ⇨ Add Watch to display the Add Watch dialog box. See below figure.

The Add Watch dialog has three parts:

• Expression: Enter a valid VBA expression or a variable here. For example, AnyNumber=900 or just AnyNumber.
• Context: Select the procedure and the module you want to watch. Note that you can select All Procedures and All Modules.
• Watch Type: Select the type of watch by clicking an option button. Your choice here depends on the expression you enter. The first choice, Watch Expression, does not cause a break; it simply displays the expression’s value when a break occurs.

Execute your procedure after setting up your watch expression(s). Things run normally until your watch expression is satisfied (based on the Watch Type you specified). When that happens, VBE enters Break mode (you did set the Watch Type to “Break When Value Is True,” didn’t you?). From there, you can step through the code or use the Immediate window to debug your code.

When you create a watch, VBE displays the Watches window shown in below figure. This window displays the value of all watches that you’ve defined. In this figure, when the value hit 800, which caused VBE to enter Break mode.

The best way to understand how these Watch business works are to use it and try various options. Before long, you’ll probably wonder how you ever got along without it.

---

Using the Locals window

Another useful debugging aid is the Locals window. You can show this window by choosing View ⇨ Locals Window from the VBE’s menu. When you are in Break mode, this window will show you a list of all variables that are local to the current procedure (see below figure). The nice thing about this window is that you don’t have to add a load of watches manually if you want to look at the content of many variables. The VBE has done all the hard work for you.

Bug Finding and Debugging Techniques

Bug Finding

A bug is an error in your programming. Here I cover the topic of programming bugs — how to identify them and how to remove them from your module.

Types of Bugs

The term program bug, as you probably know, refers to a problem with software. In other words, if the software doesn’t perform as expected, it has a bug. Fact is, all major software programs have bugs — lots of bugs. A CAD software like SOLIDWORKS itself has hundreds (if not thousands) of bugs. Fortunately, the vast majority of these bugs are relatively obscure and appear in only very specific circumstances.

When you write non-trivial VBA programs, your code probably will have bugs. This is a fact of life and not necessarily a reflection of your programming ability. The bugs may fall into any of the following categories:

• Logical flaws in your code: You can often avoid these bugs by carefully thinking through the problem your program addresses.
• Incorrect context bugs: This type of bug surfaces when you attempt to do something at the wrong time. For example, you may try to update the sketch dimension and there are no sketch is activated.
• Extreme-case bugs: These bugs rear their heads when you encounter data you didn’t anticipate, such as very large or very small numbers.
• Wrong data types bugs: This type of bug occurs when you try to process data of the wrong type, such as attempting to take the square root of a text string.

Debugging is the process of identifying and correcting bugs in your program. Developing debugging skills takes time, so don’t be discouraged if this process is difficult at first.

It’s important to understand the distinction between bugs and syntax errors. A syntax error is a language error. For example, you might misspell a keyword, omit the Next statement in a For-Next loop, or have a mismatched parenthesis. Before you can even execute the procedure, you must correct these syntax errors. A program bug is much subtler. You can execute the routine, but it doesn’t perform as expected.

---

Identifying Bugs

Before you can do any debugging, you must determine whether a bug actually exists. You can tell that your macro contains a bug if it doesn’t work the way it should. Usually, but not always, you can easily discern this.

A key fact known to all programmers is that bugs often appear when you least expect them. For example, just because your macro works fine with one data set doesn’t mean you can assume it will work equally as well with all data sets. Or your macro runs fine in your system but not working properly in your friend's system. Such cases happened all the time and are part of debugging.

The best debugging approach is to start with thorough testing, under a variety of real-life conditions. And because any changes made by your VBA code cannot be undone, it is always a good idea to use a backup copy of your CAD files that you use for testing. I usually copy some files into a temporary folder and use those files for my testing.

---

Debugging Techniques

In this section, I discuss the some of the most common methods for debugging VBA code:

• Examine your code
• Inserting MsgBox functions at various locations in your code
• Inserting Debug.Print statement

Examine your code

Perhaps the most straightforward debugging technique is simply taking a close look at your code to see whether you can find the problem. If you’re lucky, the error jumps right out, and you can fix the problem.

Notice I said, “If you’re lucky.” That’s because often you discover errors when you have been working on your program for long hours and you are running on caffeine and willpower. At times like that, you are lucky if you can even see your code. Thus, don’t be surprised if simply examining your code isn’t enough to make you find and expunge all the bugs it contains.

---

Using the MsgBox function

A common problem in many programs involves one or more variables not taking on the values you expect. In such cases, monitoring the variable(s) while your code runs is a helpful debugging technique. One way to do this is by inserting temporary MsgBox functions into your routine. For example, I used MsgBox function to check conditions. Whenever I use If-Else statement, I put one message in If condition and put another message in Else condition. By this way, I make sure condition which I want is working correctly or not.

Feel free to use MsgBox functions frequently when you debug your code. Just make sure that you remove them after you identify and correct the problem.

---

Inserting Debug.Print Statement

As an alternative to using MsgBox functions in your code, you can insert one or more temporary Debug.Print statements. Use these statements to print the value of one or more variables in the Immediate window. Here’s an example that displays a message of "This condition is working fine".

VB: Debug.Print Statement

If swPart Is Nothing Then
  Debug.Print “This condition is working fine.”

If VBE’s Immediate window is not visible, press Ctrl+G.

Unlike MsgBox, Debug.Print statements do not halt your code. So you’ll need to keep an eye on the Immediate window to see what’s going on. After you’ve debugged your code, be sure to remove all the Debug.Print statements.

Do-While Loop, Do-Until Loop & Looping through a Collection

Do-While Loop

VBA supports another type of looping structure known as a Do-While loop. Unlike a For-Next loop, a Do-While loop continues until a specified condition is met. Here’s the Do-While loop syntax:

VB: Do-While Structure

Do [While condition]
  [statements]
  [Exit Do]
  [statements]
Loop

The following example uses a Do-While loop. This routine uses 1 as a starting point and runs through next numbers. The loop continues until the routine encounter the condition of i = 8.

VB: Do-While Example

Sub ShowNumbers4()
  Dim i As Integer
  Do While i <> 8
    MsgBox i
    i = i + 1
  Loop
End Sub

Some people prefer to code a Do-While loop as a Do-Loop While loop. This example performs exactly as the previous procedure but uses different loop syntax:

VB: Do-Loop While Example

Sub ShowNumbers5()
  Dim i As Integer
  Do
    MsgBox i
    i = i + 1
  Loop While i <> 8
End Sub

Here’s the key difference between the Do-While and Do-Loop While loops: The Do-While loop always performs its conditional test first. If the test is not true, the instructions inside the loop are never executed. The Do-Loop While loop, on the other hand, always performs its conditional test after the instructions inside the loop are executed. Thus, the loop instructions are always executed at least once, regardless of the test. This difference can sometimes have a big effect on how your program functions.

---

Do-Until Loop

The Do-Until loop structure is similar to the Do-While structure. The two structures differ in their handling of the tested condition. A program continues to execute a Do-While loop while the condition remains true. In a Do-Until loop, the program executes the loop until the condition is true. Here’s the Do-Until syntax:

VB: Do-Until Structure

Do [Until condition]
  [statements]
  [Exit Do]
  [statements]
Loop

The following example is the same one presented for the Do-While loop but recoded to use a Do-Until loop:

VB: Do-Until Example

Sub ShowNumbers6()
  Dim i As Integer
  Do Until i <> 8
    MsgBox i
    i = i + 1
  Loop
End Sub

Just like with the Do-While loop, you may encounter a different form of the Do-Until loop — a Do-Loop Until loop. The following example, which has the same effect as the preceding procedure, demonstrates an alternate syntax for this type of loop:

VB: Do-Loop Until Example

Sub ShowNumbers7()
  Dim i As Integer
  Do
    MsgBox i
    i = i + 1
  Loop Until i <> 8
End Sub

There is a subtle difference in how the Do-Until loop and the Do-Loop Until loop operate. In the former, the test is performed at the beginning of the loop, before anything in the body of the loop is executed. This means that it is possible that the code in the loop body will not be executed if the test condition is met. In the latter version, the condition is tested at the end of the loop. Therefore, at a minimum, the Do-Loop Until loop always results in the body of the loop being executed once.

Another way to think about it is like this: The Do-While loop keeps looping as long as the condition is true. The Do-Until loop keeps looping as long as the condition is False.

---

Looping through a Collection

VBA supports yet another type of looping — looping through each object in a collection of objects. Please note that I have not covered Object topic so far. For your understanding I give a brief explanation about collection.

A collection is a group of same type of objects. For example, a drawing file in any CAD application is a collection of Sheets, and each sheet is a collection of drawing views and so on.

When you need to loop through each object in a collection, use the For Each-Next structure. The syntax is

VB: For Each-Next Structure

For Each element In collection
  [statements]
  [Exit For]
  [statements]
Next [element]

The following example loops through each drawing sheet in the active drawing and shows name of each active drawing sheet:

VB: For Each-Next Example

Option Explicit
Dim swApp As SldWorks.SldWorks
Dim swPart As SldWorks.ModelDoc2
Dim swDwg As SldWorks.DrawingDoc
Dim BoolStatus As Boolean
Dim SheetNamesList As Variant
Sub ShowSheetName()
  Set swApp = Application.SldWorks
  Set swPart = swApp.ActiveDoc
  Set swDwg = swPart
  SheetNamesList = swDwg.GetSheetNames
  Dim SheetName As Variant
  For Each SheetName In SheetNamesList
    MsgBox SheetName
  Next SheetName
End Sub

In this example, first we get the list of all sheet names in the opened drawing, then we loop through each sheet name in the collection and show sheet name in a message box. For this example please notes that we did not need to load all sheet, this code can work on non-activate and non-loaded sheets also.

Looping of your Code

The term looping refers to repeating a block of VBA statements numerous times. VBA provides various looping command for repeating code to make correct decision making. We will go through them in following topics:

For -Next Loop

The simplest type of loop is a For-Next loop. Here’s the syntax for this structure:

Structure

  For counter = start To end [Step stepval]
    [statements]
    [Exit For]
    [statements]
  Next [counter]

The looping is controlled by a counter variable, which starts at one value and stops at another value. The statements between the For statement and the Next statement are the statements that get repeated in the loop. To see how this works, keep reading.

For-Next example

The following example shows a For-Next loop that doesn’t use the optional Step value or the optional Exit For statement. This routine loops 10 times and uses the VBA MsgBox function to show a number from 1 to 10:

Example

Sub ShowNumbers1()
  Dim i As Integer
  For i = 1 to 10
    MsgBox i
  Next i
End Sub

In this example, I (the loop counter variable) starts with a value of 1 and increases by 1 each time through the loop. Because I didn’t specify a Step value the MsgBox method uses the value of I as an argument. The first time through the loop, i is 1 and the procedure shows a number. The second time through (i = 2), the procedure shows a number, and so on.

---

For-Next example with a step

You can use a Step value to skip some values in a For-Next loop. Here’s the same procedure as in the preceding section, rewritten to insert random numbers

Example

Sub ShowNumbers2()
  Dim i As Integer Step 2
  For i = 1 to 10
    MsgBox i
  Next i
End Sub

The count starts out as 1 and then takes on a value of 3, 5, 7, and 9. The final Count value is 9. The Step value determines how the counter is incremented. Notice that the upper loop value (9) is not used because the highest value of Count after 9 would be 11, and 11 is larger than 10.

---

For-Next example with an Exit For statement

A For-Next loop can also include one or more Exit For statements within the loop. When VBA encounters this statement, the loop terminates immediately. Here’s the same procedure as in the preceding section, rewritten to insert random numbers.

Example

Sub ShowNumbers3()
  Dim i As Integer Step 2
  For i = 1 to 10
    If i = 5 Then
      MsgBox “This is a mid value”
      Exit For
    End If
    MsgBox i
  Next i
End Sub

This routine performs the as earlier but when the variable I reached to 5, it shows a message, stating that this is a mid value and exit from the loop.

Select Case structure

The Select Case structure is useful for decisions involving three or more options (although it also works with two options, but using If-Then-Else structure is more efficient for that).

The syntax for the Select Case structure follows:

Example

  Select Case testexpression
  [Case expressionlist-n
    [statements-n]]
  [Case Else
    [elsestatements]]
  End Select

Don’t be scared off by this official syntax. Using the Select Case structure is quite easy.

---

Select Case example

The following example shows how to use the Select Case structure. This also shows another way to code the examples presented in the previous section:

Example

Sub SelectPartLength()
  Dim PartNumber As Integer
  Dim PartLength As Integer
  PartNumber = InputBox(“Please Enter part number:”)
  Select Case PartNumber
    Case Part001
      PartLength = 1
    Case Part002
      PartLength = 2
    Case Part003
      PartLength = 3
  End Select
  MsgBox “Part Length for this” & PartNumber & “is” & PartLength
End Sub

In this example, the PartNumber variable is being evaluated. The routine is checking for three different cases.

If-Then-Else structure

If-Then is VBA’s most important control structure. You’ll probably use this command on a daily basis. As in many other aspects of life, effective decision-making is the key to success in writing CAD or any other macros.

The If-Then structure has this basic syntax:

Example

  If condition Then statements [Else elsestatements]

Use the If-Then structure when you want to execute one or more statements conditionally. The optional Else clause, if included, lets you execute one or more statements if the condition you’re testing is not true. Sound confusing? Don’t worry; a few examples make this crystal clear.

If-Then examples

The following routine demonstrates the If-Then structure without the optional Else clause:

Example

Sub GoodMorning()
  If Time < 0.5 Then MsgBox “Good Morning.”
End Sub

The GoodMorning procedure uses VBA’s Time function to get the system time. If the current system time is less than .5 (in other words, before noon), the routine displays a friendly greeting. If Time is greater than or equal to .5, the routine ends and nothing happens.

To display a different greeting if Time is greater than or equal to 0.5, add another If-Then statement after the first one:

Example

Sub GoodMorning2()
  If Time < 0.5 Then MsgBox “Good Morning.”
  If Time >= 0.5 Then MsgBox “Good Afternoon.”
End Sub

Notice that I used >= (greater than or equal to) for the second If-Then statement. This ensures that the entire day is covered. Had I used > (greater than), then no message would appear if this procedure were executed at precisely 12:00 noon.

If-Then-Else examples

Another approach to the preceding problem uses the Else clause. Here’s the same routine recoded to use the If-Then-Else structure:

Example

Sub GoodMorning3()
  If Time < 0.5 Then MsgBox “Good Morning.” Else _
  MsgBox “Good Afternoon.”
End Sub

Notice that I use the line continuation character (underscore) in the preceding example. The If-Then-Else statement is actually a single statement. VBA provides a slightly different way of coding If-Then-Else constructs that use an End-If statement. Therefore, the GoodMorning procedure can be rewritten as:

Example

Sub GoodMorning4()
  If Time < 0.5 Then
    MsgBox “Good Morning.”
  Else
    MsgBox “Good Afternoon.”
  End If
End Sub

In fact, you can insert any number of statements under the If part, and any number of statements under the Else part. I prefer to use this syntax because it’s easier to read and makes the statements shorter.

What if you need to expand the GoodMorning routine to handle three conditions: morning, afternoon, and evening? You have two options: Use three If-Then statements or use a nested If-Then-Else structure. Nesting means placing an If-Then-Else structure within another If-Then-Else structure. The first approach, the three statements, is simplest:

Example

Sub GoodMorning5()
  If Time < 0.5 Then Msg = “Morning.”
  If Time >= 0.5 And Time < 0.75 Then Msg = “Afternoon.”
  If Time >= 0.75 Then Msg = “Evening.”
  MsgBox “Good” & Msg
End Sub

The Msg variable gets a different text value, depending on the time of day. The final MsgBox statement displays the greeting: Good Morning, Good Afternoon, or Good Evening.

The following routine performs the same action but uses an If-Then-End If structure:

Example

Sub GoodMorning6()
  Dim Msg As String
  If Time < 0.5 Then
    Msg = “Morning.”
  If Time >= 0.5 And Time < 0.75 Then
    Msg = “Afternoon.”
  If Time >= 0.75 Then
    Msg = “Evening.”
  End If
  MsgBox “Good” & Msg
End Sub

If-ElseIf-Else examples

In the previous examples, every statement in the routine is executed — even in the morning. A more efficient structure would exit the routine as soon as a condition is found to be true. In the morning, for example, the procedure should display the Good Morning message and then exit — without evaluating the other superfluous conditions.

With a tiny routine like this, you don’t have to worry about execution speed. But for larger applications in which speed is important, you should know about another syntax for the If-Then structure. The ElseIf syntax follows:

Example

  If condition Then
    [statements]
  [Else condition-n Then
    [elseifstatements]]
  [Else
    [elsestatements]]

Here’s how you can rewrite the GreetMe routine by using this syntax:

Example

Sub GoodMorning7()
  Dim Msg As String
  If Time < 0.5 Then
    Msg = “Morning.”
  ElseIf Time >= 0.5 And Time < 0.75 Then
    Msg = “Afternoon.”
  Else
    Msg = “Evening.”
  End If
  MsgBox “Good” & Msg
End Sub

When a condition is true, VBA executes the conditional statements and the If structure ends. In other words, VBA doesn’t waste time evaluating the extraneous conditions, which makes this procedure a bit more efficient than the previous examples. The trade-off (there are always trade-offs) is that the code is more difficult to understand. (Of course, you already knew that.)