Excel Tips and Tricks

Some handy tips and tricks for using Excel, which can make life a lot easier and allow you a lot more functionality in your spreadsheet apps.

Comboboxes: Yes you can use a combobox control to display data dropdowns and code it in VBA, but sometmes it just isn't necessary. You can use Excel's built-in combo equivalent, by setting a validation on a cell.

Usage:

Select a cell and go to Data, Validation menu. On the Settings tab, choose Allow: List and then specify your source range. Voila, an in-cell dropdown. You could combine this with...

Detecting cell changes: Each worksheet has Change and SelectionChange events which pass to the code a range called Target. This allows you to determine which cell has been changed (in the case of the former) or selected (in the case of the latter).

Usage:
VB Code:
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox (Target.Row & ", " & Target.Column)
End Sub
Tells you the row and column of the cell that was changed.

The SpecialCells method: Matches cells according to specific conditions, eg empty cells, cells with numbers or formulae, last used cell etc.

Usage:
VB Code:
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate
Activates the last used cell.

The WorksheetFunction object: Allows access to all those handy Excel formula functions that you type in a cell function. All the maths functions, statistics, etc can now be accessed through your code so that, for example, you don't have to use an extra cell or a big loop to calculate the average of a range, just use the WorksheetFunction to put it in a variable.

Usage:
VB Code:
Dim MyRangeAverage as Double
MyRangeAverage = WorksheetFunction.Average(Range("A1:A10"))
Averages the numbers in the range A1 to A10.

The Application object: Very useful to find out information about the current application, such as the current user ID, the start up path, the templates path etc.

Usage:
VB Code:
Msgbox (Application.UserName & ", " & Application.StartUpPath)
Displays the Office username and startup path of the current user.

Dialog boxes: VBA supports all sorts of dialog boxes, accessed using the Dialog object. These run built-in dialog boxes as though you'd clicked the button yourself. So the Open dialog box will actually open a file, the Chartwizard box will set up a graph etc.

Usage:
VB Code:
dlgAnswer = Application.Dialogs(xlDialogOpen).Show
Runs the Open dialog box, and opens the selected file. Returns True.

Sometimes, however, you'd like to present your user with a file dialog that acts as a picker rather than as an actual Open / Save box. For this you can use the FileDialog, and then code the action to take on the basis of the selection.

Usage:
VB Code:
Dim dlgOpen As FileDialog
Set dlgOpen = Application.FileDialog(FileDialogType:=msoFileDialogOpen) 
With dlgOpen
 .AllowMultiSelect = True
 .Show
 End With
 For Each i in dlgopen.selecteditems
 MsgBox (i)
 Next i
Opens a file picker dialog and returns the filenames of all the selected files.

The Find method: You can use the Find method of a range to pick out the data that you want. The Find method returns a cell if the data is found, or Nothing if not.

Usage:
VB Code:
With Worksheets(1).Range("A1:B500")
    Set c = .Find(2, lookin:=xlValues)
    If Not c Is Nothing Then
        MsgBox (c.Row & ", " & c.Column)
    End If
End With
Finds the number 2 in the range A1 to B500 and returns the row and column where it first occurs.

Excel Graphing: Rather than spend hours generating multiple graphs by hand, or for situations in which you might want to select from a list of parameters and update a graph accordingly, you can simply generate the graph through code and then change the SeriesCollections to display whatever data you might want.

Usage:
See the link in my sig :)

Setting sheet visibility: Not quite the same as actually setting permissions, because this doesn't involve locking the sheet but it can be a handy way to determine what users can and cannot see.

Usage:
Add a worksheet and call it Settings. In column A, list all the Office usernames of your admin users. Then place some code in the Workbook Open event:
VB Code:
Set c = Worksheets("Settings").Range("A:A").Find(Application.UserName)
If Not (c Is Nothing) Then
    Worksheets("Sheet2").Visible = True
    Worksheets("Settings").Visible = True
Else
    Worksheets("Sheet2").Visible = False
    Worksheets("Settings").Visible = False
End If
Matches the current user to a list on the Settings worksheet. If the user is not on the list, then neither the Settings sheet nor Sheet2 are visible.