print barcode labels in c# Creating Sub and Function Procedures in C#

Drawer QR in C# Creating Sub and Function Procedures

3 4 5 6

Note There are other options available to you in the Add Procedure dialog box you ll learn about those possibilities a little later in this chapter. After you finish the preceding procedure, the outline of a procedure appears in the active

code module, as shown in Figure 5-1.

Figure 5-1. As soon as you click OK in the Add Procedure dialog box, the skeleton of your new procedure appears in the active code module.

5

Microsoft Office Excel 2003 Programming Inside Out You can then fill in the details of your procedure using the Visual Basic Editor to pick objects, built-in functions, properties, events, and so on using the Object Browser. The following code listing contains a procedure that checks the contents of the active cell and, when the value matches any of the tests in the If Then statement, changes the cell s font color to the named color.

Sub AvailableCredit() With ActiveCell If .Value = "" Then Exit Sub If .Value <= 1000 Then .Font.Color = vbRed If .Value > 1000 Then .Font.Color = vbBlack If .Value > 4999 Then .Font.Color = vbBlue If .Value > 9999 Then .Font.Color = vbGreen End With End Sub

The colors listed in the preceding code are represented by VBA constants, but there are many millions of specific colors available to you. For more information on using colors to format the contents of items in your workbook, see 10, Formatting, Excel Objects.

It s interesting to notice that the seemingly equivalent procedure that follows, which uses a Select Case statement to test the values in the active cell, actually generates an incorrect result.

Sub AvailableCreditCase()

Case ""

Exit Sub Case Is >= 10000 ActiveCell.Font.Color = vbGreen Case Is <= 9999 ActiveCell.Font.Color = vbBlue Case Is <= 4999 ActiveCell.Font.Color = vbBlack Case Is <= 1000 ActiveCell.Font.Color = vbRed End Select End Sub

5

The Pitfalls of Case Statements and Conditional Formats If you compare the If Then and Select Case versions of the AvailableCredit routines side by side, you might notice that the If Then statements check for values greater than some other value (for example, If .Value > 5000 Then .Font.Color = vbBlue), whereas all but the last Case statement checks for values in a definite range. You should use definitive rules in a Select Case statement because the instant Excel finds a case that s true, it exits the Select Case statement. So, if you were to evaluate a cell value of 5500 using the If Then statement listed in the preceding example, the procedure would go through the following steps:

1 Is the cell blank No, so take no action. 2 Is the value less than 1000 No, so take no action. 3 Is the value greater than 1000 Yes, so change the font color to black. 4 Is the value greater than 5000 Yes, so change the font color to blue. 5 Is the value greater than 10,000 No, so take no action.

The routine changed the font color an extra time (first to black, and then to blue), but you got the right result and the extra step is not a problem for a simple program on a computer that can perform millions of calculations per second. However, because the rules in the fol lowing Select Case statement are constructed in the same order, the cell s contents would be displayed in black type, not blue.

Select Case Remaining Case "" Exit Sub Case Is < 1000 ActiveCell.Font.Color = vbRed Case Is >= 1000 ActiveCell.Font.Color = vbBlack Case Is >= 5000 ActiveCell.Font.Color = vbBlue Case Is >= 10000 ActiveCell.Font.Color = vbGreen End Select

You get incorrect results because the routine quits when it finds the cell value is less than or equal to 9999. You ll run into the same problem when you create conditional formats, which you do by clicking Format, Conditional Formatting and using the controls in the Conditional Formatting dialog box to create your rules. The rules in the following graphic correspond to the incorrect order noted earlier and would also result in an improperly formatted cell value.