|
|
Readability - Consistent Look and Feel
Readability is enhanced by
1. indenting control structures,
2. breaking long lines of code into shorter segments,
3. white-space lines between tasks, and
4. comments before tasks.
All the code in a project should follow the same formatting rules. This will help during the original development, and will help a lot when the code is revisited in the future. It’s amazing how much the original developer can forget about code written only a few months before. If it is a different programmer attempting to figure out what the original intent was, inconsistent formatting only makes the problem more difficult.
Indenting
VBA provides <Tab> for increasing indenting, and <Shift><Tab> decreasing it. The default indent is four spaces. This can be changed, but most programmers use it, and code will be more understandable by other programmers if it is not changed.
Code indenting should be:
Space-Underscore Line Extension
Needs vary depending on screen resolution and the programmer’s choice of VBE window size, but lines in general are more readable if horizontal scrolling is minimized. Lines can be broken anywhere, since the “ _” characters and any spaces at the beginning of the continuation line will be removed when the code is compiled. Split lines are more readable if breaks are placed after commas and before operators such as AND, OR, and &. As an aid to indentifying extension lines, indent them by 2 spaces.
Commenting
If lines of code are short, and comments are just a few words, then the comments can be placed on the same line as the code, but should be tabbed out to align with the comment which begins furthest out furthest
Commenting End Statements
If a block of code is longer than five or ten lines, it may be confusing to see an End statement at the top of the code pane without knowing what it is the end of. The text will need to be scrolled down to see the condition and then back up to position for continuing the code edit. A way around this is to comment at the end of the End statements what is being ended. This goes for End of Subs and Functions, and End of If, With, For, Do, While, and Select Case blocks of code, where the enclosed code contains many lines.
End With ' EditButton
End Sub ' EditButton_Click
In UserForms Lesson 5 2007.xlsx, the code for Lesson 5 and later conform to the above coding standards.
UserForm Specific Coding
Three Module Types
The VBAProject pane of VBE can have three types of entries: class modules, UserForm modules, and standard modules.
The UserForm, like most things in Excel, is an object and as so is a member of the UserForms Collection. For more detail see UserForm Object in the VBE help.
Me Keyword and Other Handy Tips
When writing VBA code in a standard module, many references do not need to be completely specific. For example, Range(“A1”) applies to the active worksheet. The worksheet only needs to be specified if it is not the active worksheet, as in Worksheet(“Sheet2”).Range(“A1”). This assumption is not available in the class modules of objects, so VBA has the Me keyword as a shorthand way to refer to the object of the class module. In the ThisWorkbook module, “Me” refers to the workbook containing the code. Likewise, the word “Me” in a UserForm code module refers to the UserForm itself. Besides being handy, this is good practice in that if the UserForm name changes, or the code is transported to another UserForm code module, the keyword “Me” will still apply. An example has been included in UserForm_Initialize() for UserForm_Lesson04b, to change the UserForm width.
Me.Width = 360.75
A suggestion regarding typing of code is to type in lower case. When the cursor is moved to another line, the editor will appropriately capitalize keywords, constants, and variables defined with Dim, Public, and Static. Any mistyped words will immediately be identified, as well as problems with syntax.
Help is easily available for any keyword, property, or method. Click anywhere within the word and push F1. This will pop up the help topic for that word.
Design Time vs Run Time
Design time is when the developer creates the UserForm, adds controls, changes their properties, and writes code. Run time is when the UserForm is active for user input. The UserForm_Initialize procedure runs when the statement UserForm.Load is executed, or when UserForm.Show is executed if the UserForm is not already loaded.
Most tasks performed by the developer at design time can also be performed by code during run time. Unless modified by UserForm_Initialize, properties set during design time are unchanged during run time. One exception would be if an AutoSize property is set, such as for CheckBox. Another exception is when controls are linked by the ControlSource or RowSource properties to cells on a worksheet. These cells can be changed by macros before the UserForm is loaded, and can be changed by the user unless the cells are specifically protected.
Show, Hide, Load, and Unload
A UserForm has two commonly used methods, Show and Unload. Two more methods, Load, and Hide, are used in special circumstances. A UserForm can be launched many ways. A common way is from a CommandButton placed on a Worksheet, but other ways are from a Custom menu bar, from the standard menu bar, by shortcut key, etc. The UserForm can be moved after it is opened, so whatever is underneath it can be seen. A UserForm can be launched either modal or modeless. Modal requires focus to stay on the UserForm until it is hidden or unloaded. Use modeless when the user is to have access to the worksheet cells or another UserForm.
Using macros assigned to buttons, this exercise explores UserForm methods Show, Hide, Load, and Unload, and the modeless variation of Show. Five buttons will be created to execute each task. Four buttons will be Forms buttons, and to show the difference between using Forms controls and the Control Toolbox, the fifth one will be an ActiveX control from the Control Toolbox.
EXERCISE Part 1 - Development
Create UserForm1. In its code module, place this:
Private Sub UserForm_Initialize()
Debug.Print "UserForm is loaded and initialized"
End Sub
In a standard module such as Module1, add the following procedures:
Sub ShowUserFormModal()
UserForm1.Show
Debug.Print "1 count="; UserForms.Count, _
"visible="; UserForm1.Visible
End Sub
Sub HideUserForm()
UserForm1.Hide
Debug.Print "2 count="; UserForms.Count, _
"visible="; UserForm1.Visible
End Sub
Sub LoadUserForm()
Load UserForm1
Debug.Print "3 count="; UserForms.Count, _
"visible="; UserForm1.Visible
End Sub
Sub UnloadUserForm()
Unload UserForm1
Debug.Print "4 count="; UserForms.Count
End Sub
In Excel, click View>Toolbars>Forms. Double-click the Button icon on the Forms Toolbar. This leaves the icon selected after each button placement. Place the first button on the sheet. The Assign Macro window will open. Select ShowUserFormModal and click OK. Repeat three times, assigning macros HideUserForm, LoadUserForm, and UnloadUserForm. Click the Button icon on the Forms Toolbar to deselect it and exit the repeating mode.
Over the first button, Right-click>Edit Text. Highlight the text and type “Show Modal” Hit the Esc button on the keyboard to exit Edit. Stretch the button so the whole text show, and click Esc again to deselect the button. Repeat with the next three buttons, naming them “Hide,” “Load,” and “Unload”
In Excel, View>Toolbars>Control Toolbox
Click Button on the Control Toolbox.
Note that both CommandButton and Design Mode are selected. Design Mode must be set to work with the control. If Design Mode is off, clicking the button will execute the code rather than access the properties.
Click on the worksheet to create a CommandButton. Double-click the button, and the default Click event handler will be created in the sheet class module. Add the two statements below, so that the handler is:
Private Sub CommandButton1_Click()
UserForm1.Show vbModeless
Debug.Print "5 visible="; UserForm1.Visible
End Sub
Over the CommandButton, Right-click>Properties. Change Caption to “Show Modeless.” Resize the button so the caption shows. Click Design Mode on the Control Toolbox to finish the setup.
EXERCISE Part 2 - Testing
Position Excel and VBE so both the buttons on the sheet and the Immediate Window can be seen. Click in Immediate, then <Ctrl>a to select all and <Ctrl>x to clear all the text.
Click the Show Modal button. UserForm will open. Attempting to click on a cell on the worksheet will fail because the UserForm is not modeless. The Hide, Load, and Unload buttons also will not work, since they are also on the worksheet. Note that Immediate has no text, because the modal form of Show stops the macro while UserForm is open. Close the UserForm by clicking the X in the upper right corner. Immediate will now show:
UserForm is loaded and initialized
1 count= 0 visible=False
Click the Show Modeless button. UserForm will open. Attempting to click on a cell on the worksheet will succeed because the UserForm is modeless. Immediate will show:
UserForm is loaded and initialized
5 visible=True
Click Hide, then Show Modeless. UserForm will disappear, then reappear. Immediate will show:
2 visible=False
This time, Show Modeless did not execute Initialize because UserForm was already in memory. The same would be true of Show Modal.
Click Unload. Debug.Print UserForm1.Visible will fail because UserForm is no longer in memory. Click Reset on the toolbar (the square icon) to set VBE back to a runnable state.
Click Load. UserForm will not be visible, and Immediate will show:
UserForm is loaded and initialized
3 visible=False
Click Show Modeless to make UserForm visible. Again, Initialize did not execute. Immediate will show:
5 visible=True
Load UserForm1" is not typically used. A possible exception might be to load a complex UserForm earlier to have it available more quickly when needed.
Use UserForm1.Hide if the UserForm is complex and takes a long time to load, or if the controls on the UserForm contain information needed later.
Passing Values Between Control and Worksheet
There are three ways to pass a values between a control and a worksheet cell: by the cell(s) identified by the ControlSource and RowSource properties, and by code which copies between the control Value property and a cell.
Via Control ControlSource and a Cell Address
The cell specified by ControlSource mirrors the Value property of the control. If the control is changed by the user or by code, the cell value also changes. If the cell is changed by code, the Value property of the control is changed. In the Control Demos file, see the Lesson 2 UserForm “Start Label and TextBox Demos” button for an example.
The valid setting for ControlSource is a one-cell range address. To link ControlSource to cell A1 of the active Worksheet, type A1 into the ControlSource property via the Properties window for that Control. To be perfectly clear, the sheet name can also be included, such as 'Sheet 1'!A1. The apostrophes enclose the sheet name if it has a space. See the properties for UserForm_Lesson02a in the Control Demos file for an example.
A better way to specify ControlSource is to name the cell and then put that range name into the ControlSource property via the Properties window for that Control. This has the advantage that ControlSource will remain valid if rows or columns are inserted before the range.
ControlSource is usually set at design time, as above. It can also be set at run time with:
TextBox1.ControlSource = "B2"
TextBox1.ControlSource = "myRange"
The named range myRange above must be a single cell.
Both properties can be set at design time, and can be changed by running code later. A reason for changing at run time might be to point to a cell within a range.
EXAMPLE
This code finds the maximum value in a range and sets ControlSource to point to it.
Private Sub UserForm_Initialize()
Dim iMax%, rngControlSrc As Range
With Range("myRange")
iMax% = WorksheetFunction.Max(.Cells)
Set rngControlSrc = .Find(What:=iMax%, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False)
TextBox1.ControlSource = rngControlSrc.Address
End With
End Sub
iMax% is set to the highest value within myRange using a WorksheetFunction. The range Find method is used to locate the first occurrence of iMax% in the range. The Find method returns a range. Since ControlSource needs a text value, the range address is used to set it. This code can be used again, perhaps in a Button_Click procedure, to update ControlSource as conditions change.
A drawback of pointing ControlSource to a cell is that the cell value changes when focus leaves the control. If the value the user has typed is invalid, the previous value is gone. There are at least two methods for saving the previous value. One is to store the value in a module-level variable, and another is to store the original value of the ControlSource inside the Tag Property of the Control. The code statements to accomplish the two are:
Dim saveControlSrc ' variable defined at top of module
…
saveControlSrc = rngControlSrc.Value ' set a variable
or TextBox1.Tag = rngControlSrc.Value ' set tag property
Then, on the UserForm, either by the click of a CommandButton or another procedure, restore the value with:
Range(TextBox1.ControlSource) = TextBox1.Tag
or Range(TextBox1.ControlSource) = saveControlSrc
Either method restores the cell to the previous value, which in turn restores the Value property of the control.
Via Code and the Value Property
The second way to put a cell value to a control is with a code statement. The control will display the same value as when ControlSource is set, but the control value would not be linked to the cell. The first following statement is for including in a UserForm procedure, and the second is for including in a standard module procedure or a procedure for a different UserForm. Of course, UserForm1 must be loaded for the second statement to succeed.
TextBox1.Value = Range("myRange").Cells(1,1)
UserForm1.TextBox1.Value = Range("myRange").Cells(1,1)
Via Control RowSource and a Range of Cells
In addition to the single cell identified by ControlSource, two controls can also work with a range of cells. These are ListBox and ComboBox. The control should have multiple columns to benefit from a range.
Selecting from ComboBox and Single-Select ListBox
If the list is a single column list, then both of these statements return the same value.
ListBox1.Value
ListBox1.List(ListBox1.ListIndex, 1)
If the list is a multiple column list, then they will return the same if BoundColumn=1. If the value from the second column is needed, then these two ways return the same value
With ListBox1
.BoundColumn = 2 ' BoundColumn starts from 1
.Value ' returns the BoundColumn value
.List(.ListIndex, 1) ' list indices start from 0
End With
The List property holds the data. The read-only ListCount property is the number of rows of data in List. Rows and columns in List are indexed beginning with 0, so ListIndex = 0 refers to the first List row and ListIndex = ListCount – 1 refers to the last List row. If no item is selected, ListIndex is –1.
Selecting from Multiple-Select ListBox
A powerful ListBox feature is the ability for the user to select multiple entries from the list. To enable the feature, set the MultiSelect property to fmMultiSelectMulti.
Though ListIndex points to a selected row in List, with multiple selections it only points to the row with focus, which may not even be selected, and so is not used for finding multiple selections. Instead, the Selected property is used. To find which rows are selected, check the Selected property inside a loop of the list. As with ListIndex, 0 refers to the first List row and ListIndex = ListCount – 1 refers to the last List Row. In this code snippet, what is passed to the handling procedure depends on how it is written. This example presumes it wants column 1 (index 0). Note that when passing an item from an array to another procedure, the other procedure can’t tell if the passed value is the whole array or an item from it, so the passed parameter must be received with the ByVal keyword. An alternative way would be to pass a pointer to the array, and the index of the item to be processed. Both forms are shown.
Private Sub CheckSelectionButton_Click()
Dim x%
With ListBox1
For x% = 0 To .ListCount - 1
If .Selected(x%) Then
Call selectedItemHandlingProcedure(.List(x%, 0))
Call selectedRowHandlingProcedure(.List, x%)
End If
Next x%
End With
End Sub
Sub selectedItemHandlingProcedure(ByVal itemFromColumn1)
' some code
End Sub
Sub selectedRowHandlingProcedure(ByRef array, arrayIndex%)
' some different code
End Sub
MultiSelect is demonstrated in the Inititialization Demo of Lesson 4. Initializing a ListBox will be covered in Lesson 6.
Reusing Controls and UserForms
Experienced programmers often accomplish projects more quickly because they have a way to re-use prior work. This may include anything programmable, but since these lessons are about UserForms, the recommendations here will focus on controls and UserForms.
There are three common ways to refer to earlier work: exporting to files, collecting in a template file, and making notes about projects containing re-usable work. Remember that controls connect to cells with the ControlSource property, and to ranges for ListBoxes and ComboBoxes, the RowSource property. The purpose of these cells and ranges may not obvious when the control is taken out of context, especially for ListBoxes and ComboBoxes.
Copying a UserForm
The easiest way to copy a UserForm from one file to another is to have both files open. In the VBAProject window of the VBE, select the UserForm, drag it to the receiving filename, and drop it. The same method works for standard code modules.
When copying a UserForm, remember to copy standard Modules containing any procedures called from UserForm code.
Exporting a UserForm
UserForm exporting is described earlier in this lesson, in the paragraph titled Exported Module Naming Convention.
The default export folder is Program Files\ Microsoft Office\OfficeXX, with XX being the version of Office being used. Since that folder is often hard to remember when looking for an exported UserForm, it’s best to make a folder such as My_Exported_UserForms in an often-used folder such as My Documents. Put a shortcut to it in Program Files\ Microsoft Office\OfficeXX to ease navigation back to My_Exported_UserForms.
Two files will be exported, with .frm and .frx extenders. The .frm file can be opened with any text editor. It contains 15 lines or so of header information, and then the body of the code. The .frx file is binary and not readable.
To import a UserForm, drag and drop the .frm file. The .frx file will come along automatically.
Copying a Control From One UserForm to Another
Often controls on a UserForm will have several properties set to give a custom visual look for a client. Setting up these properties can be quite time consuming, so time is saved if controls are copied from one UserForm to another. Right-click the control, and from the drop-down list click Copy. Right-click on the second UserForm and click Paste. The control will be pasted in the middle of the UserForm. The control name does not need to be changed. ControlSource and RowSource will normally be changed, unless the application vision calls for controls on two UserForms to access the same cell or range.
Adding a Control to the Toolbox
Customized controls can be added to the toolbox as follows: right click on the "Control" page tab and select "New Page." Drag onto this page any customized controls. This comes in very handy if the same changes to a control are always needed.
|
|
OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.