Visual Basic for Applications/Worksheet Common Utilities

Summary
The procedures on this page are made for Microsoft Excel, and include commonly used worksheet utilities.

Does Worksheet Exist?
Before making a worksheet or referring to one that is assumed to exist, it is best to be certain one way or the the other. This routine returns True if there is already a worksheet with the parameter's name.

Add a Named Worksheet
This routine adds a worksheet with a specified name. First make sure however that the worksheet name is not in use; see SheetExists.

Changing Column References
At times it is useful to have routines to change the column alpha reference style to a numerical one, and vice versa. These procedures to that.

Next Free Row or Column
These procedures find the next free column or row. One set selects the cell in question while the other set simply return its position. Examples exist for both columns and rows, and in the absence of a chosen parameter, column 1 or row 1 is assumed.

Clear Worksheet Cells
This procedure makes a selective clear of the specified worksheet, depending on the parameter nOpt. The options as coded include, clear contents, (that is the text), clear formats, (the fonts and colours), and clear all, a combination of the two.

Move Rows and Columns
At times it is useful to shift entire columns and rows of data by one place on the spreadsheet, and in any case the process can be repeated as often as is necessary. These procedures assume that the user has first placed the cursor in the column or row of interest. The columns feature is of particular use when bringing an external tabulation into the worksheet; the columns will almost certainly need to be rearranged to match those of the resident set. Food databases are notorious for their different formats, none matching the sequence of those on food labels. Hopefully, some day a product's food data could be entered all at once with a scanned image.

Delete Various Worksheet Items
These procedures allow deletion of worksheets, rows, and columns. Before deleting a worksheet, it should first be confirmed to exist.